MySQL's Standard Dump/Backup Utility
mysqldump is the client backup program used to dump a database or a collection of databases for backup or transfer to another SQL server.
The output of a mysqldump typically contains SQL statements to create the database schema, only the data, stored procedures, triggers, subsets of data using where clauses, or combinations of them all. Additionally, mysqldump can also be used to generate files in CSV, other delimited text, or XML format.
By default, mysqldump will dump all databases alphabetically, and table alphabetically within each database. If you use your imagination, you can find more creative ways to mysqldump data faster using shell scripting in conjunction with background processes. Also by default, mysqldump uses --opt, which specifies
- --add-drop-table
- --add-locks
- --create-options
- --disable-keys
- --extended-insert
- --lock-tables
- --quick
- --set-charset
There are many options that control mysqldump's behavior when dumping MyISAM and InnoDB data, plus accommodations for making the mysqldump's output perform such things as:
- Binary Log Rotation on the Master prior to dumping
- Preparing Replication Slaves to change replication coordinates (master Log file,master log position) during reload
- Adding
FLUSH PRIVILEGES;to update user credentials on the server being loaded - Make all InnoDB tables dump at the same point-in-time (
--single-transaction)
Another feature of mysqldump is the dumping of individual tables in conjunction with a WHERE clause (--where). This allows for dumping portions of a table. If the --where clause involves "EXISTS IN other tables" conditions via JOINs or sub-SELECTs, you must use --lock-all-tables (normally the default, with the exception of dumping an individual table).
