MySQL数据库备份&恢复（备份恢复）【备份策略二：mysqldump备份数据库】

【完全备份+增加备份，速度相对较慢，适合中小型数据库）（MyISAM是温备份，InnoDB是热备份】

语法样式

Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]

常用参数

• –help
  显示帮助信息并退出。
  mysqldump --help

• –user, -u
  指定连接的用户名。

• –password, -p
  连接数据库密码

• –port, -P
  连接数据库端口号

• –host, -h（指定目的主机IP，可实现跨服务器备份）
  需要导出的主机信息
  mysqldump -uroot -p --host=localhost --all-databases

• –socket,-S（通常使用在多实例MySQL环境下区分不同数据库实例）
  指定连接mysql的socket文件位置，默认路径/tmp/mysql.sock
  mysqldump -uroot -p --host=localhost --all-databases --socket=/tmp/mysqld.sock

• –pipe（windows系统可用）
  使用命名管道连接mysql
  mysqldump -uroot -p --host=localhost --all-databases --pipe

• –all-databases , -A
  导出全部数据库。
  mysqldump -uroot -p --all-databases

• –databases, -B（备份文件中带有创建数据库和切换对应数据库的语句，恢复时省去创建数据库和切换数据库的步骤）
  导出几个数据库。参数后面所有名字参量都被看作数据库名。
  mysqldump -uroot -p --databases test mysql

• –single-transaction（通常启用保证备份的一致性）
  该选项在导出数据之前提交一个BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于多版本存储引擎，仅InnoDB。本选项和–lock-tables 选项是互斥的，因为LOCK TABLES 会使任何挂起的事务隐含提交。要想导出大表的话，应结合使用–quick 选项。
  mysqldump -uroot -p --host=localhost --all-databases --single-transaction

• –flush-logs（常用保证数据完整性）
  开始导出之前刷新日志。
  请注意：假如一次导出多个数据库(使用选项–databases或者–all-databases)，将会逐个数据库刷新日志。除使用–lock-all-tables或者–master-data外。在这种情况下，日志将会被刷新一次，相应的所有表同时被锁定。因此，如果打算同时导出和刷新日志应该使用–lock-all-tables 或者–master-data 和–flush-logs。
  mysqldump -uroot -p --all-databases --flush-logs

• –default-character-set
  设置默认字符集，默认值为utf8
  mysqldump -uroot -p --all-databases --default-character-set=latin1

• –tables
  覆盖–databases (-B)参数，指定需要导出的表名。
  mysqldump -uroot -p --host=localhost --databases test --tables test

• –ignore-table
  不导出指定表。指定忽略多个表时，需要重复多次，每次一个表。每个表必须同时指定数据库和表名。例如：–ignore-table=database.table1 --ignore-table=database.table2 ……
  mysqldump -uroot -p --host=localhost --all-databases --ignore-table=mysql.user

• –login-path=#
  从登录文件中读取此路径。

• –comments
  附加注释信息。默认为打开，可以用–skip-comments取消
  mysqldump -uroot -p --all-databases (默认记录注释)
  mysqldump -uroot -p --all-databases --skip-comments (取消注释)

• –events, -E
  导出事件。
  mysqldump -uroot -p --all-databases --events

• –lock-all-tables, -x
  提交请求锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局读锁，并且自动关闭–single-transaction 和–lock-tables 选项。
  mysqldump -uroot -p --host=localhost --all-databases --lock-all-tables

• –lock-tables, -l
  开始导出前，锁定所有表。用READ LOCAL锁定表以允许MyISAM表并行插入。对于支持事务的表例如InnoDB和BDB，–single-transaction是一个更好的选择，因为它根本不需要锁定表。
  请注意当导出多个数据库时，–lock-tables分别为每个数据库锁定表。因此，该选项不能保证导出文件中的表在数据库之间的逻辑一致性。不同数据库表的导出状态可以完全不同。
  mysqldump -uroot -p --host=localhost --all-databases --lock-tables

• –no-create-db, -n
  只导出数据，而不添加CREATE DATABASE 语句。
  mysqldump -uroot -p --host=localhost --all-databases --no-create-db

• –no-create-info, -t
  只导出数据，而不添加CREATE TABLE 语句。
  mysqldump -uroot -p --host=localhost --all-databases --no-create-info

• –no-data, -d
  不导出任何数据，只导出数据库表结构。
  mysqldump -uroot -p --host=localhost --all-databases --no-data

• –opt
  等同于–add-drop-table, --add-locks, --create-options, --quick, --extended-insert, --lock-tables, --set-charset, --disable-keys 该选项默认开启, 可以用–skip-opt禁用.
  mysqldump -uroot -p --host=localhost --all-databases --opt

–skip-opt
禁用–opt选项.
mysqldump -uroot -p --host=localhost --all-databases --skip-opt

使用示范（备份）

备份所有数据库

[root@localhost ~]# mysqldump -uroot -pMySql@123 -A > all.sql
[root@localhost ~]# cat all.sql | grep "Current Database:"
-- Current Database: `mysql`
-- Current Database: `db`
-- Current Database: `test`

备份单个数据库

[root@localhost ~]# mysqldump -uroot -pMySql@123 db > db_1.sql
[root@localhost ~]# cat db_1.sql | grep "Database:"
-- Host: localhost    Database: db

备份多个数据库

[root@localhost ~]# mysqldump -uroot -pMySql@123 -B test1 test2 > test1-2.sql
[root@localhost ~]# cat test1-2.sql | grep "Current Database:"
-- Current Database: `test1`
-- Current Database: `test2`

备份单张表

[root@localhost ~]# mysqldump -uroot -pMySql@123 db student > db_student.sql

备份多张表

[root@localhost ~]# mysqldump -uroot -pMySql@123 db student course > db_student-course.sql

备份数据结构

[root@localhost ~]# mysqldump -uroot -pMySql@123 -d db > db_nodata.sql

使用示范（恢复）

[root@localhost ~]# mysql -uroot -pMySql@123 < *.sql 

mysql> source ~/*.sql

【注：路径和文件名按照实际】