Backup and recovery parameters for MySQL database

Common options:

--all-databases,-A: Backing Up all databases
--databases,-B: For backing up multiple databases, if there is no option, Mysqldump takes the first name parameter as the database name, followed by the table name. With this option, Mysqldum takes each name as the database name.

--force,-F: Continue backup even if a SQL error is found
--host=host_name,-H host_name: Backup host name, localhost by default
--no-data,-D: Export table structure only
--password[=password],-p[password]: password
--port=port_num,-P Port_num: Port number when making TCP/IP connections
--quick,-Q: Quick Export
--tables: Overwrite--databases or-b option, followed by parameter is considered as table name
--user=user_name,-u user_name: User name
--xml,-X: Export as XML file

Backup:

1. Back up data and structure of all databases

Mysqldump-uroot-p123456-a >/tmp/all.sql

-A: Back Up all databases =--all-databases

2, backup the structure of all databases (plus-d parameter)

Mysqldump-uroot-p123456-a-D > F:\all_test.sql

-A: Back up all databases =--all-databases--no-data,-D: Export only table structure

3. Backing up the data and structure of a single database (, database name MyDB)

mysqldump-uroot-p123456 Mydb>f:\mydb.sql

4. Back up the table structure of a single database (without backing up actual data) quickly

mysqldump-uroot-p123456 mydb-d > F:\mydb.sql

--no-data,-D: Export table structure only

5, backup multiple tables of data and structure (data, the structure of a separate backup method and the same) (back up more than one table at a time)

mysqldump-uroot-p123456 mydb T1 T2 > F:\multables.sql

6. Back up multiple databases at once

mysqldump-uroot-p123456--databases db1 DB2 > F:\muldbs.sql

Mysqldump-uroot-pjsb-b bk0 BK1 > Bk01.sql (with equal results above)

--databases,-B: For backing up multiple databases, if there is no option, Mysqldump takes the first name parameter as the database name, followed by the table name. With this option,

Command Operation instructions:

1. Export structure does not export data

mysqldump-d database name-uroot-p > Xxx.sql

2. Export data does not export structure

MYSQLDUMP-T database name-uroot-p > Xxx.sql

3. Export Data and table structure

Mysqldump database name-uroot-p > Xxx.sql

4. Export the structure of a specific table

Mysqldump-uroot-p-B database name--table table name > Xxx.sql

Restore-Restore:

Restore all Databases 2 methods: (Requires a backup set of the database)

(1) MySQL command line source method

(1) MySQL command line: Source backup set path/backup set name. sql

(2) System command line method

(2) System command line: Mysql-u account-P password < backup set path/backup set name. sql

1. Restore a single database (Specify a backup set for the database)

MySQL command line source method: The following

mysql-uroot-p123456

CD into your store recovery script (or backup set directory) under

Use test

SOURCE f:\ backup name. sql

System command Line method: as follows (recommended)

mysql-uroot-p123456 MyDB < F:\mydb.sql

2. Restore multiple tables for a single database (backup set of multiple tables that require a single database)

MySQL command line source method: The following

Use MyDB

CD into your store recovery script (or backup set directory) under

Source Backup set path/backup set name. sql

System command Line method: as follows (recommended)

mysql-uroot-p123456 MyDB < F:\tables.sql

3. Restore multiple databases (a backup file with multiple databases, no database required at this time)

MySQL command line:

SOURCE F:\dbs1.sql

System command line: (recommended)

mysql-uroot-p123456 < Backup set path/backup set name. sql

mysql-uroot-p123456 < Bk01.sql

Backup and recovery parameters for MySQL database