MySQL Operations management-mysqldump Backup and Recovery database 20

Mysqldump Backup and Recovery database

Backup:

1. Back up data and structure of all databases

Mysqldump-uroot-pjsb-a >/bk/all.sql

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

2.

Backing up the structure of all databases (plus-d parameter)

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

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

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

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

5. Backing 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

7. Backup multiple tables data and structure (data, the structure of a separate backup method with the same) (back up multiple tables at a time)

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

8. Back up multiple databases at a time

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,

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-pjsb

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

Use MyDB

SOURCE F:\ backup set 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:\multables.sql

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

MySQL command line:

SOURCE F:\muldbs.sql

System command line: (recommended)

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

MYSQL-UROOT-PJSB < Bk01.sql

Sister article:

http://blog.csdn.net/u010098331/article/details/50896175

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

Extract:

Import data:

Since mysqldump exports a complete SQL statement, it is easy to import the data using the MySQL client program:

MySQL stored procedure moved to another Linux operating system under the MySQL database, because the table structure, etc. already exist in the target database, do not want to copy the database in the past, so the use of the MySQL stored procedure export and import, through the Internet search found using the Mysqldump tool can be achieved, The specific usage is:

Mysqldump-uroot-p-hlocalhost-p3306-n-d-t-R DBName > Procedure_name.sql

MySQL Operations management-mysqldump Backup and Recovery database 20