mysqldump — A Database Backup Program •The  --compact  option turns off several settings that control whether optional statements and comments appear in the output. Again, you can follow this option with other options that re-enable certain settings, or turn all the settings on by using the  --skip-compact  form. When you selectively enable or disable the effect of a group option, order is important because options are processed first to last. For example,  --disable-keys --lock-tables --skip-opt  would not have the intended effect; it is the same as  --skip-opt  by itself. To make a backup of an entire database: To load the dump file back into the server: Another way to reload the dump file: mysqldump  is also very useful for populating databases by copying data from one MySQL server to another: You can dump several databases with one command: To dump all databases, use the  --all-databases  option: For  InnoDB  tables,  mysqldump  provides a way of making an online backup: This backup acquires 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 the lock is released. If long updating statements are running when the  FLUSH  statement is issued, the MySQL server may get stalled until those statements finish. After that, the dump becomes lock free and does not disturb reads and writes on the tables. If the update statements that the MySQL server receives are short (in terms of execution time), the initial lock period should not be noticeable, even with many updates. For point-in-time recovery (also known as “roll-forward,”  when you need to restore an old backup and replay the changes that happened since that backup), it is often useful to rotate the binary log (see Section 5.4.4, “The Binary Log” ) or at least know the binary log coordinates to which the dump corresponds: The  --master-data  and  --single-transaction  options can be used simultaneously, which provides a convenient way to make an online backup suitable for use prior to point-in-time recovery if tables are stored using the  InnoDB  storage engine. Or: mysqldump --all-databases --flush-logs --master-data=2 > all_databases.sql mysqldump --all-databases --master-data=2 > all_databases.sql mysqldump --all-databases --master-data --single-transaction > all_databases.sql mysqldump --all-databases > all_databases.sql mysqldump --databases  db_name1  [ db_name2  ...] > my_databases.sql mysqldump --opt  db_name  | mysql --host= remote_host  -C  db_name mysql -e "source  /path-to-backup/backup-file.sql "  db_name mysql  db_name  <  backup-file.sql mysqldump  db_name  >  backup-file.sql Examples 500