2.6. Backing up and restoring MySQL data with logical dumps

• Back up one or more selected databases
• Back up all databases
• Back up a subset of tables from one database

Procedure

• To dump a single database, run:

  # mysqldump [options] --databases db_name > backup-file.sql

• To dump multiple databases at once, run:

  # mysqldump [options] --databases db_name1 [db_name2 ...] > backup-file.sql

• To dump all databases, run:

  # mysqldump [options] --all-databases > backup-file.sql

• To load one or more dumped full databases back into a server, run:

  # mysql < backup-file.sql

• To load a database to a remote MySQL server, run:

  # mysql --host=remote_host < backup-file.sql

• To dump a subset of tables from one database, add a list of the chosen tables at the end of the mysqldump command:

  # mysqldump [options] db_name [tbl_name ...​] > backup-file.sql

• To load a subset of tables dumped from one database, run:

  # mysql db_name < backup-file.sql

  注意

  The db_name database must exist at this point.

• To see a list of the options that mysqldump supports, run:

  $ mysqldump --help

Prerequisites

• The mysqld service is running.
• You have credentials with permissions to restore data, for example, the root account.

Verification

• Connect to a MySQL database and query data, for example:

  # mysql -u root -p <database> -e "*SELECT * FROM <table>;"