mysqldump 命令

常用示例

说明

mysqldump creates logical backups of MySQL databases. Output is SQL statements that can recreate the database structure and data when executed. The --single-transaction option provides consistent backups for InnoDB tables without locking. It starts a transaction and dumps data at that point in time, allowing other operations to continue. For MyISAM or mixed storage engines, --lock-tables prevents writes during dump. This ensures consistency but blocks writes. Routines (stored procedures, functions) and events are not included by default. Use --routines and --events explicitly to back up these objects. The --master-data option records binary log coordinates, essential for setting up replication slaves or point-in-time recovery. Output is plain SQL text, easily compressed with gzip. For very large databases, consider mysqlpump (parallel) or physical backup tools like Percona XtraBackup.

参数

-u _USER_, --user _USER_

MySQL username.

-p_PASSWORD_, --password=_PASS_

Prompt for or specify password.

-h _HOST_, --host _HOST_

Server hostname.

-P _PORT_, --port _PORT_

Server port.

--all-databases, -A

Dump all databases.

--databases, -B

Dump multiple named databases.

--no-data, -d

Don't dump table data.

--no-create-info, -t

Don't dump CREATE TABLE.

--single-transaction

Consistent snapshot for InnoDB.

--routines, -R

Include stored procedures/functions.

--triggers

Include triggers (default on).

--events, -E

Include events.

--add-drop-table

Add DROP TABLE before CREATE.

--add-drop-database

Add DROP DATABASE before CREATE.

--master-data=_VALUE_

Include binary log position.

--quick, -q

Don't buffer, write directly.

--lock-tables, -l

Lock tables during dump.