MySQL - Database BackUp

The most common use of mysqldump is probably for making a backup of an entire database:

shell> mysqldump --opt db_name > backup-file.sql

You can read the dump file back into the server like this:

shell> mysql db_name <> mysql -e "source /path-to-backup/backup-file.sql" db_name

mysqldump is also very useful for populating databases by copying data from one MySQL server to another:

shell> mysqldump --opt db_name | mysql --host=remote_host -C db_name

It is possible to dump several databases with one command:

shell> mysqldump --databases db_name1 [db_name2 ...] > my_databases.sql

If you want to dump all databases, use the --all-databases option:

shell> mysqldump --all-databases > all_databases.sql

If tables are stored in the InnoDB storage engine, mysqldump provides a way of making an online backup of these (see command below). This backup just needs to acquire a global read lock on all tables (using FLUSH TABLES WITH READ LOCK) at the beginning of the dump. As soon as this lock has been acquired, the binary log coordinates are read and lock is released. So if and only if one long updating statement is running when the FLUSH... is issued, the MySQL server may get stalled until that long statement finishes, and then the dump becomes lock-free. So if the MySQL server receives only short (in the sense of "short execution time") updating statements, even if there are plenty of them, the initial lock period should not be noticeable.

shell> mysqldump --all-databases --single-transaction > all_databases.sql

For point-in-time recovery (also known as “roll-forward”, when you need to restore an old backup and replay the changes which happened since that backup), it is often useful to rotate the binary log (see Section 5.11.3, “The Binary Log”) or at least know the binary log coordinates to which the dump corresponds:

shell> mysqldump --all-databases --master-data=2 > all_databases.sql
or
shell> mysqldump --all-databases --flush-logs --master-data=2 > all_databases.sql