The mysqldump Utility and an Options Cheat Sheet · English

The mysqldump utility lets you dump the contents of a database or a collection of databases to create a backup or transfer data to another SQL server (not necessarily a MySQL server). The dump will contain a set of SQL commands for creating and/or populating tables.

mysqldump also provides the ability to restore databases from the resulting SQL file.

Creating a Dump

Let’s look at a very simple usage example: dumping the database “database” by redirecting the output to the file “database.sql”:

mysqldump -uroot -h82.82.82.82 -p database > database.sql

where:

-u or -–user=… - username
-h or --host=… - remote host (you can omit this option for the local host)
-p or --password - prompt for a password
database - database name
database.sql - dump file

To dump several databases, use the --databases option (or -B for short), for example:

mysqldump -uroot -h82.82.82.82 -p -B database1 database2 database3 > databases.sql

And to dump all databases, use the --all-databases option (or -A for short), for example:

mysqldump -uroot -h82.82.82.82 -p -A > all-databases.sql

Restoring a Dump

Redirect the stream in the opposite direction and restore the database:

mysql -uroot -h82.82.82.82 -p database < database.sql

Or through the MySQL console:

mysql> use database;
mysql> source database.sql

And if we have a gz archive, for example, then:

zcat database.sql.gz | mysql -uroot -h82.82.82.82 -p database

Examples of Using Some Options

Suppose we need data from the “production version of the database” for the “developer version”—in other words, we need a “sandbox.” Select no more than 100 records:

mysqldump -uroot -h82.82.82.82 -p --where="true limit 100" database > database.sql

Or perhaps we need only the structure, without the data:

mysqldump -uroot -h82.82.82.82 -p --no-data database > database.sql

These examples were inspired by a post by Alexander Makarov - http://rmcreative.ru/blog/post/ljogkiy-damp-mysql

Dump only triggers, procedures, and events:

mysqldump --no-create-info --no-data --triggers --routines --events -uroot -p database | gzip > ~/database.sql.gz

Options Cheat Sheet

Here are some options you may need when working with the mysqldump utility.

--add-drop-database
Adds a DROP DATABASE statement before each CREATE DATABASE statement.
--add-drop-table
Adds a DROP TABLE statement before each CREATE TABLE statement.
--add-locks
Adds a LOCK TABLES statement before executing each table dump and UNLOCK TABLE afterward (to speed up MySQL access).
--all-databases, -A
Saves all tables from all databases managed by the current server.
--allow-keywords
Allows column names that match keywords. Conflicts are avoided by prefixing each column name with the table name.
--comments, -i
This option adds extra information to the dump, such as the mysqldump version, MySQL version, and the hostname of the machine running the MySQL server.
--compact
This option tells mysqldump to create a dump using as compact a format as possible. It is the opposite of --comments.
--compatible=name
Generates output compatible with other DBMSs or older versions of MySQL. In place of the keyword name, you can use: “ansi”, “mysql323”, “mysql40”, “postgresql”, “oracle”, “mssql”, “db2”, “maxdb”, “no_key_options”, “no_table_options”, “no_field_options”. You can use multiple values, separated by commas.
--complete-insert, -c
Uses the complete form of the INSERT statement (with column names).
--create-options
Adds extra information to CREATE TABLE statements. This may include the table type, the initial AUTO_INCREMENT value, and other options.
--databases, -B
Lets you specify the names of several databases to dump.
--delayed
Uses the INSERT DELAYED command when inserting rows.
--delete-master-logs
Automatically deletes binary logs (logbin) on the replication master server after a dump has been successfully created using mysqldump. This option automatically enables “–master-data”.
--disable-keys, -K
For each table, surrounds the INSERT statement in the dump output with /*!40000 ALTER TABLE tbl_name DISABLE KEYS */; and /*!40000 ALTER TABLE tbl_name ENABLE KEYS */; statements. This speeds up loading data onto the server for MyISAM tables, since indexes are created after all the data has been inserted.
--extended-insert, -e
Uses the INSERT command with the new multiple-row syntax (makes insert statements more compact and faster).
--flush-logs, -F
Writes system log data from the MySQL server’s buffer to disk before starting the dump.
--force, -f
Continues even if an error occurs while creating the dump.
--hex-blob
Represents binary data in BINARY, VARBINARY, BLOB, and BIT fields in hexadecimal format. For example, the sequence “abc” will be replaced with 0x616263.
--ignore-table=db_name.tbl_name
Ignores the table tbl_name in the database db_name when creating the dump. To exclude several tables from the dump, use multiple “–ignore-table” options, specifying one table in each.
--insert-ignore
Adds the IGNORE keyword to the INSERT statement.
--lock-all-tables, -x
Locks all tables in all databases while a complete dump of all databases is being created.
--lock-tables, -l
Locks the tables in the database being dumped.
--no-autocommit
Wraps all INSERT statements for a single table in one transaction, increasing data loading speed.
--no-create-db, -n
Suppresses the generation of CREATE DATABASE statements in the dump, which are automatically added when using --databases and --all-databases.
--no-data, -d
Suppresses the generation of INSERT statements in the dump, which can be useful when dumping the database structure without the data itself.
--opt
This option is intended to optimize backup speed and is shorthand for the following options: --quick –add-drop-table –add-locks –create-options –disable-keys –extended-insert –lock-tables –set-charset. Starting with MySQL 4.1, --opt is used by default, meaning that all the options listed above are enabled by default even if they are not specified. To disable this behavior, use --skip-opt
--order-by-primary
Sorts each table by its primary key or first unique index.
--port, -P
The TCP port number used to connect to the host.
--protocol={TCP|SOCKET|PIPE|MEMORY}
Specifies the protocol for connecting to the server.
--quick, -q
Starts generating the dump without waiting for all the data to be loaded from the server, thereby saving memory.
--quote-names, -Q
Encloses database, table, and column names in backticks `. Starting with MySQL 4.1, this option is enabled by default.
--replace
Adds the REPLACE keyword to the INSERT statement. This option first appeared in MySQL 5.1.3.
--result-file=/path/to/file, -r /path/to/file
Directs the dump to the file file. This option is especially convenient on Windows, without using the command line, where you can redirect the output to a file using > and >>.
--routines, -R
Dumps stored procedures and functions. Available since MySQL 5.1.2.
--single-transaction
Creates the dump as a single transaction.
--skip-comments
Suppresses the output of extra information in the dump.
--socket=/path/to/socket, -S /path/to/socket
The socket file for connecting to localhost.
--tab=/path/, -T /path/
Creates two separate files for each table in the path directory: tbl_name.sql, containing the CREATE TABLE statement, and tbl_name.txt, containing the table data separated by tab characters. The data format can be explicitly overridden using --fields-xxx and –lines-xxx.
--tables
Overrides --databases (-B). All arguments following this option are treated as table names.
--triggers
Dumps triggers. This option is enabled by default. To disable it, use --skip-triggers.
--events, -E
Dumps events. See MySQL Event Scheduler, or the Built-in Event Scheduler in MySQL.
--tz-utc
Adds a statement of the form SET TIME_ZONE=’+00:00’ to the dump, allowing dumps to be exchanged across different time zones.
--verbose, -v
Verbose output mode. Outputs more detailed information about the program’s operation.
--version, -V
Displays program version information.
--where=‘where-condition’, -w ‘where-condition’
Dumps only the selected records. Note that quotation marks are required.
--xml, -X
Represents the database dump as XML.
--first-slave, -x
Locks all tables in all databases.
--debug=…, -#
Traces program execution (for debugging).
--help
Displays help information and exits the program.

A Few More Words About Backups in MySQL

mysqlhotcopy for MyISAM

For quick backups of databases with ISAM and MyISAM tables, you can use “mysqlhotcopy”, which copies the *.frm, *.MYD, and *.MYI files:

# mysqlhotcopy db_name /path/to/dir

This method is not suitable for InnoDB, because with this type, not all files are necessarily stored in the database directory.

xtrabackup for InnoDB

For InnoDB, there is xtrabackup—I recommend taking a look!
UPDATE: XtraBackup - Backups for innoDB

Binary Logs and Replication

“mysqldump” is not intended for replication; that is what the binary log (–log-bin) is for:

# mysqlbinlog binlog.[0-9]* | mysql

For full replication, you need to keep a binary log from the very beginning of the database’s operation, even before creating structures and data.

Data Backups in MySQL 6.x

Online backup is available starting with MySQL 6.x. Here is a slide explaining the new features:

$online\_backup$