MySQL备份恢复：单线程mysqldump工具使用

一、mysqldump介绍

对于搞数据库的应该都知道，mysqldump是MySQL官方提供的用于逻辑备份的工具。

mysqldump优点：mysqldump的优点就是逻辑备份，把数据生成sql形式保存，在单库，单表数据迁移，备份恢复等场景方便，sql形式的备份文件通用，也方便在不同数据库之间移植。对于innodb表可以在线备份。
mysqldump缺点：mysqldump是单线程，数据量大的时候，备份时间长，甚至有可能在备份过程中非事务表长期锁表对业务造成影响(sql形式的备份恢复时间也比较长)。mysqldump备份时会查询所有的数据，这可能会把内存中的热点数据刷掉。

通常，使用mysqldump备份期间，为了使得数据库中加锁时间尽量短。在使用mysqldump时基本都会用到这两个关键的参数，第一个是--single-transaction，加上这个参数，表示在开始备份前开启一个一致性快照事务，以此来获取一致性备份，该参数仅对InnoDB存储引擎有效；第二个是--master-data=2，主要用于记录一致性备份的binlog position位点。理解mysqldump工作原理，一定要将事务表（InnoDB）和非事务表（比如myisam）区别对待，因为备份的流程与此息息相关。而且，到目前为止，我们也无法规避myisam表，即使我们的所有业务表都是InnoDB，因为mysql库中系统表仍然采用的myisam表。

关于mysqldump工作原理，详看：MySQL备份恢复：单线程mysqldump原理

二、mysqldump常用选项

下面说一下mysqldump所支持的常用选项，在使用mysqldump备份数据时经常会使用到。

1. 连接选项

-h, --host

指定备份连接的主机地址。

-P, --port

指定备份连接的主机端口。

-u, --user

指定备份用户。

-p, --password

指定备份用户密码。

-S, --socket

指定备份socket。

--compress, -C

在客户端和服务器之间启用压缩传递所有信息，前提是客户端和服务端都支持压缩。

2. 过滤选项

--all-databases, -A

备份所有数据库的所有表。

--databases, -B

备份指定的数据库（以及库中的表），不指定该参数情况下，mysqldump认为第一个名称是数据库名，后面跟的都是表名，指定该参数，该参数后面的都认为是数据库名。

--events, -E

导出事件调度。

--ignore-table=db_name.tbl_name

不导出指定表。指定忽略多个表时，需要重复多次，每次一个表。每个表必须同时指定数据库和表名。例如：--ignore-table=database.table1 --ignore-table=database.table2 ……，该选项也可用来忽略视图。

-n, --no-create-db

导出时不创建库信息。

-t, --no-create-info

导出时不创建表信息。

-d, --no-data

导出时不导出行数据，只导出数据库表结构。

--routines, -R

导出存储过程以及存储函数。注意：该参数并不导出属性信息如：存储过程创建和修改的时间戳。导入时创建的时间戳和导入时时间相同。假如要使用原始的时间戳，不要使用参数--routines，而是直接备份mysql.proc的内容（需要用户有相关权限）。

--tables

覆盖–databases (-B)参数，指定需要导出的表名，后面可直接跟多个表。

--triggers

导出触发器。该选项默认启用，用--skip-triggers禁用它。

--where='where_condition', -w 'where_condition'

只转储给定的WHERE条件选择的记录。请注意如果条件包含命令解释符专用空格或字符，一定要将条件引用起来。

3. DDL选项

--add-drop-database

在CREATE DATABASE语句前增加DROP DATABASE语句，一般配合--all-databases或--databases使用，因为只有使用了这二者其一，才会记录CREATE DATABASE语句。

--add-drop-table

在CREATE TABLE语句前增加CREATE TABLE语句。

--add-drop-trigger

在CREATE TRIGGER语句前增加DROP TRIGGER语句。

--all-tablespaces, -Y

导出全部表空间，该参数目前仅用在MySQL Cluster表上（NDB引擎）。

--no-create-db, -n

只导出数据，而不添加CREATE DATABASE 语句。除非加上参数--databases或--all-databases。

--no-create-info, -t

只导出数据，而不添加CREATE TABLE语句。

--no-tablespaces, -y

不导出任何表空间信息。

--replace

使用REPLACE INTO取代INSERT INTO。

4. 调试选项

--debug-info

mysqldump退出时打印调试信息及cup，内存的使用统计。

--dump-date

添加DUMP时间到输出末尾，默认开启，禁用的话--skip-dump-date。

--log-error=file_name

记录warning及error信息岛指定文件，默认不输出。

--force, -f

在导出过程中忽略出现的SQL错误。

5. 字符集选项

--character-sets-dir=path

字符集文件的安装目录。

--default-character-set=charset_name

设置默认字符集，若不设置默认为utf8mb4，早期版本默认为latin1，导出tab格式文件时最好显示指定，方便通过LOAD DATA INFILE方式导入。

--set-charset

添加’SET NAMES default_character_set’到输出文件。默认为打开状态，使用--skip-set-charset关闭选项。

--no-set-names, -N

关闭--set-charset选项，也可用--skip-set-charset代替。

6. 复制选项

--master-data=value

通过该参数产生的备份转存文件主要用来建立一个slave replication。当value等于0时，不记录二进制日志文件及路径位置；为1时，以CHANGE MASTER TO的方式在db.sql文件中记录二进制日志的位置，可用于恢复后直接启动服务器。为2时，以CHANGE MASTER TO的方式在db.sql文件中记录二进制日志的位置，但默认为被注释，如：CHANGE MASTER TO MASTER_LOG_FILE=’mysql-bin.000015′, MASTER_LOG_POS=107;。一般都设置为2。

另外单独使用--master-data（没有--single-transaction选项）选项时会自动开启--lock-all-tables，备份过程中整个实例全程锁表，不会发生备份数据与获取的binlog pos点不一致的问题。

--dump-slave=value

在从库上导出数据时（一般都是在从库备份，但是需要获取到主库的position点），同--master-data功能类似（测试中发现这两个参数无法同时生效，这点不如mydumper，mydumper可以获取主从的position），value值为1或2。默认为1，即mysqldump出来的文件就会包括CHANGE MASTER TO这个语句，如果为2，mysqldump出来的文件包含的CHANGE MASTER TO这个语句会被注释掉，但同时会关闭slave SQL线程，备份完成后会开启。该选项可配合--apply-slave-statements和--include-master-host-port参数使用。

--include-master-host-port

在--dump-slave产生的’CHANGE MASTER TO..’语句中增加’MASTER_HOST=<host>，MASTER_PORT=<port>’ 。

--apply-slave-statements

在--dump-slave产生的’CHANGE MASTER TO..’前增加STOP SLAVE命令，并在文件末尾增加START SLAVE命令。

--set-gtid-purged

控制是否向备份文件写入gtid信息，即添加SET @@global.gtid_purged，参数包括ON、OFF、AUTO，默认为AUTO。值为ON表示添加（数据库没启用GTID时会报错），OFF不添加，AUTO(如果数据库启用GTID便自动添加)。

7. 事务选项

--single-transaction

该参数适用于InnoDB表，与--lock-tables参数互斥，备份期间不锁表（但会有一个瞬间需执行FLUSH TABLE WITH READ LOCK），即热备份。此参数在备份开始前，会开启事务，并且会设置隔离级别为RR，利用可重复读特性来获得备份的一致性。

当启用该参数并进行备份时，为确保得到有效的备份文件，使用该参数备份期间应避免使用DDL（ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE.）语句，因为连续性的读并没有对这些语句进行隔离，备份期间使用这些DDL语句会导致潜在的select获取到的返回的数据不一致或错误，因为一旦表结构被改变（在事务开启到读取数据这个之间被改变），之后的查询会报异常，导致你导出的数据不一致。

--lock-tables

在备份单库或单表时，会锁定每个库下的所有表（对非事物存储引擎使用），一般用于MyISAM存储引擎，备份时只能对数据库进行读取操作，不过备份依然可以保证一致性。对于Innodb存储引擎不需要使用此参数，用--single-transaction即可，并且--lock-tables和--single-transaction是互斥的，不能够同时使用。默认会加锁，通过指定--skip-lock-tables选项可以跳过锁，但会导致数据不一致。

--lock-all-tables

在备份过程中，对所有库中的所有表上锁。这可以避免之前提及的--lock-tables参数不能同时锁住所有表的问题。

--flush-logs

执行日志文件刷新（帮助恢复二进制日志）。

--flush-privileges

在导出[mysql]数据库之后，发出一条FLUSH PRIVILEGES 语句。为了正确恢复，该选项应该用于导出[mysql]数据库或依赖[mysql]数据库数据的任何时候。

--no-autocommit

每一个导出表的所有insert语句作为一个整体，在其前后加上SET autocommit = 0 和 COMMIT，设置为非自动提交模式，即全部insert执行完，在执行COMMIT（通过验证）。

--order-by-primary

如果存在主键，或者第一个唯一键，对每个表的记录进行排序。在导出MyISAM表到InnoDB表时有效，但会使得导出工作花费很长时间。

--extended-insert

表示使用长 INSERT ，多行合并一起批量 INSERT，提高导入效率。默认值。

--skip-extended-insert

表示开启每一行一个的短 INSERT，小事务操作，但产生更多的网络交互。insert 次数越多，交互次数就越多，性能越低。

--net-buffer-length

用于拆分 mysqldump 备份文件的SQL大小的。mysqldump 默认会按照参数 net-buffer-length 来自动切分 SQL 语句。默认值是 1M 大小。最大可设置为 16777216 字节，可以提升导出导入性能。如果为了避免大事务，那就不建议调整这个参数，使用默认值即可。

不过，在导入的时候，会受到服务器参数 max_allowed_packet 影响，它控制了服务器能接受的数据包的最大大小，默认值是 4194304，即 4M。所以如果设置 net-buffer-length 值大于 max_allowed_packet 时，需要调整参数 max_allowed_packet 的值。

8. 其他

--quick

会使用 mysql_use_result 方法，不缓冲查询，直接导出到标准输出。默认为打开状态，使用--skip-quick取消该选项。

这个方法是读一行处理一行，不要缓存结果，逐行打印。你可以想象一下，假设有一个业务的逻辑比较复杂，每读一行数据以后要处理的逻辑如果很慢，就会导致客户端要过很久才会去取下一行数据，那么你在服务端 show processlist 就会看见线程状态为 sending to client。

因此，对于正常的线上业务来说，如果一个查询的返回结果不会很多的话，我都建议你使用 mysql_store_result 这个接口，直接把查询结果保存到本地内存。当然前提是查询返回结果不多，不然就需要改用 mysql_use_result 方法，避免程序 OOM。

NOTE

mysqldump 默认导出是开启了--quick参数，所以你很少碰见导出导致 OOM 的情况。但是 mysql 命令行客户端默认--quick参数为关闭状态，如果你使用-e参数一次导出很多数据的话，就会导致 OOM，使用的时候需要注意。

--opt

等同于--add-drop-table，--add-locks，--create-options，--quick，--extended-insert，--lock-tables， --set-charset，--disable-keys，该选项默认开启，可以用--skip-opt禁用。

--hex-blob

使用十六进制格式导出二进制字符串字段。如果有二进制数据就必须使用本选项。影响的字段类型包括 BINARY、VARBINARY、BLOB。

--skip-tz-utc

为了防止导出部分数据是 timestamp 类型会根据时区进行转换。

三、mysqldump基本用法

以下操作都可以带上这些必选项，防止一些意外情况。

mysqldump --default-character-set=utf8mb4 --opt --hex-blob --master-data=2 --single-transaction --skip-tz-utc --events --triggers --routines

1	mysqldump --default-character-set=utf8mb4 --opt --hex-blob --master-data=2 --single-transaction --skip-tz-utc --events --triggers --routines

1. 备份指定库

$ mysqldump -uroot -p --single-transaction --master-data=2 --databases DB_NAME_1 DB_NAME_2 ... > /tmp/db.sql

1	$ mysqldump -uroot -p --single-transaction --master-data=2 --databases DB_NAME_1 DB_NAME_2 ... > /tmp/db.sql

2. 备份指定表

$ mysqldump -uroot -p --single-transaction --master-data=2 DB_NAME TABLE_NAME_1 TABLE_NAME_2 ... > /tmp/db.sql

1	$ mysqldump -uroot -p --single-transaction --master-data=2 DB_NAME TABLE_NAME_1 TABLE_NAME_2 ... > /tmp/db.sql

3. 备份所有库

$ mysqldump -uroot -p --single-transaction --master-data=2 --all-databases > /tmp/db.sql

1	$ mysqldump -uroot -p --single-transaction --master-data=2 --all-databases > /tmp/db.sql

4. 根据指定条件备份

我们在用mysqldump备份数据时，有个选项是 -where/-w，可以指定备份条件，使用方式如下：

$ mysqldump -uroot -p --single-transaction --master-data=2 -w ' id < 10000 ' DB_NAME TABLE_NAME > /tmp/db.sql

1	$ mysqldump -uroot -p --single-transaction --master-data=2 -w ' id < 10000 ' DB_NAME TABLE_NAME > /tmp/db.sql

这时候就可以备份出mytable表中 id< 10000 的所有记录了。假设我们还想加一个时间范围条件，例如：

$ mysqldump -uroot -p --single-transaction --master-data=2 -w " id < 10000 and logintime < unix_timestamp('2016-06-01')" DB_NAME TABLE_NAME > /tmp/db.sql

1	$ mysqldump -uroot -p --single-transaction --master-data=2 -w " id < 10000 and logintime < unix_timestamp('2016-06-01')" DB_NAME TABLE_NAME > /tmp/db.sql

在这里，一定注意单引号和双引号问题，避免出现这种情况：

$ mysqldump -uroot -p --single-transaction --master-data=2 -w ' id < 10000 and logintime < unix_timestamp('2016-06-01') ' DB_NAME TABLE_NAME > /tmp/db.sql

1	$ mysqldump -uroot -p --single-transaction --master-data=2 -w ' id < 10000 and logintime < unix_timestamp('2016-06-01') ' DB_NAME TABLE_NAME > /tmp/db.sql

这样一来，解析就会出问题。

5. 备份表结构

$ mysqldump -uroot -p --single-transaction --master-data=2 -d --databases DB_NAME > /tmp/db.sql

1	$ mysqldump -uroot -p --single-transaction --master-data=2 -d --databases DB_NAME > /tmp/db.sql

6. 只备份存储过程、存储函数、触发器

$ mysqldump -uroot -p --single-transaction --master-data=2 --triggers -R -t -n -d --databases DB_NAME > /tmp/db.sql

1	$ mysqldump -uroot -p --single-transaction --master-data=2 --triggers -R -t -n -d --databases DB_NAME > /tmp/db.sql

上面是介绍怎么使用mysqldump备份，下面就是针对mysqldump备份出来的.sql的文件如何恢复了，最常用的有两种方法：

第一种：在Linux命令行导入

$ mysql -uroot -p < /tmp/db.sql

1	$ mysql -uroot -p < /tmp/db.sql

使用-o的参数可以只恢复某个库，极大地方便了我们恢复数据的灵活性。

$ mysql -uroot -p -o DB_NAME < /tmp/db.sql

1	$ mysql -uroot -p -o DB_NAME < /tmp/db.sql

如果需要恢复指定表，就需要写脚本过滤匹配了。

第二种：在MySQL交互式模式下导入

mysql> source /tmp/db.sql

1	mysql> source /tmp/db.sql

注意：在恢复数据时，如果在没有库的情况下恢复单个表就需要提前创建库。

四、mysqldump执行完整备份加增量备份数据恢复实例

1）创建数据(最好将数据文件和二进制日志文件分开在不同的磁盘上)

mysql> flush logs;
mysql> create database aa;
mysql> use aa
mysql> create table bb(id INT NOT NULL);
mysql> insert into bb(id) value(1),(2),(3);

mysql> flush logs;

mysql> create database aa;

mysql> use aa

mysql> create table bb(id INT NOT NULL);

mysql> insert into bb(id) value(1),(2),(3);

2）完整热备份

2.1 对于InnoDB存储引擎

$ mysqldump -uroot -p --single-transaction --master-data=2 --flush-logs --databases aa > /root/aa.sql

1	$ mysqldump -uroot -p --single-transaction --master-data=2 --flush-logs --databases aa > /root/aa.sql

表示执行完整热备份一次，并将二进制日志文件滚动一次，并把日志文件位置记录在备份文件中。

但是如果 MySQL 开启了 GTID 的话，使用 mysqldump 备份时会出现如下警告。

$ mysqldump -uroot -p --single-transaction --master-data=2 --flush-logs --databases aa > /tmp/bb.sql
Warning: Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, 
even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. 
To make a complete dump, pass --all-databases --triggers --routines --events.

$ mysqldump -uroot -p --single-transaction --master-data=2 --flush-logs --databases aa > /tmp/bb.sql

Warning: Using a password on the command line interface can be insecure.

Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions,

even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF.

To make a complete dump, pass --all-databases --triggers --routines --events.

MySQL提示：当前数据库实例中开启了 GTID 功能，在开启有 GTID 功能的数据库实例中，导出其中任何一个库，如果没有显示地指定--set-gtid-purged参数，都会提示这一行信息，不然是无法恢复的。意思是默认情况下，导出的库中含有 GTID 信息，如果不想导出包含有 GTID 信息的数据库，需要显示地添加--set-gtid-purged=OFF参数。

当源库使用了 GTID 模式时，在 dump 出来的文件中为了保持目标库和源库 GTID 值相同，增加了两个语句，SET @@SESSION.SQL_LOG_BIN= 0 和 SET @@GLOBAL.GTID_PURGED='xxxx'。

而实际上增加这两个语句会有诸多问题：

关闭 binlog 首先需要 super 权限，如果目标库只能使用普通账号，则会导致执行失败；
即使有 super 权限，也会导致这些操作不记录到 binlog，会导致主备不一致。当然也可以说，这就要求同一份 dump 要 restore 到目标库的主库和所有备库才能保持主备一致；
SET @@GLOBAL.GTID_PURGED='xxxx'这个命令要求目标库的gtid_executed值是空。若非空，这个命令执行失败；
reset master 可以清空gtid_executed值，也需要 super 权限。

因此在导出数据时，有两种可选方案：

在有目标库的 super 权限时，用默认 dump 参数，在导入到目标库之前，先执行 reset master；这样需要在主库和所有备库都执行相同个导入动作，风险很大；
mysqldump 需要增加参数--set-gtid-purged=off，这样不会生成上述两个语句，数据能够直接导入。但是目标库的 gtid set 就与源库就不同了，无法直接拿来做基于 Gtid 的主从复制。

NOTE

在 MySQL 5.7 版本中加入了多源复制的特性，一台数据库实例可以同时与多个主库同步，实现多主一从架构，但是假如现在数据库实例中开启了 GTID，并以 GTID 的方式与 A 主库和 B 主库同步，那么现在的 slave 中就记录有两条 GTID 信息。在导入带有新 GTID 信息的库时，会报错，要求你清除掉目标数据库实例中所有的 GTID 信息。在这种情况下，问题就比较严重了，因为我的这台数据库已经和两台主库建立主从关系，现在为了导入一个新库，需要 reset master 掉所有同步信息（GTID 信息）。如果你数据导入到多源库不是为了做多源复制通道，这个时候最好使用--set-gtid-purged=OFF的参数禁止导出 GTID 信息，再导入进目标数据库；如果是为了做多源复制通道，可以看一下关于基于 GTID 的多源复制相关文章，还是有很多操作是需要注意的。

2.2 对于myisam存储引擎（执行温备份）

$ mysqldump -uroot -p --lock-all-tables --master-data=2 --flush-logs --databases aa >/root/aa.sql

1	$ mysqldump -uroot -p --lock-all-tables --master-data=2 --flush-logs --databases aa >/root/aa.sql

3）添加一些新的数据表

mysql> insert into bb(id) value(100);
mysql> insert into bb(id) value(200);

1 2	mysql> insert into bb(id) value(100); mysql> insert into bb(id) value(200);

4）删除数据库（模拟数据故障）

mysql> drop database aa;

1	mysql> drop database aa;

5）恢复备份数据

5.1 利用二进制日志文件提取完整备份后改动的数据

由于备份时使用--master-data=2，所以查看备份文件aa.sql会记录有完整备份后二进制文件记录的文件名和数据位置，如下:

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=107;

1	-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=107;

使用mysqlbinlog /mydata/mysql-bin.000002（实际中是最后一次操作失误的二进制文件）进行查看，找到最后一次失误操作的时间点，我这里是：

$ mysqlbinlog /mydata/mysql-bin.000002
# at 356
#150421 19:05:20 server id 1  end_log_pos 446       Query         thread_id=42     exec_time=0
SET TIMESTAMP=1429614320/*!*/;
insert into bb(id) value(200)
# at 446
#150421 19:05:20 server id 1  end_log_pos 473       Xid = 511
COMMIT/*!*/;
# at 473
#150421 19:05:26 server id 1  end_log_pos 550       Query         thread_id=42     exec_time=0
SET TIMESTAMP=1429614326/*!*/;
drop database aa

$ mysqlbinlog /mydata/mysql-bin.000002

# at 356

#150421 19:05:20 server id 1 end_log_pos 446 Query thread_id=42 exec_time=0

SET TIMESTAMP=1429614320/*!*/;

insert into bb(id) value(200)

# at 446

#150421 19:05:20 server id 1 end_log_pos 473 Xid = 511

COMMIT/*!*/;

# at 473

#150421 19:05:26 server id 1 end_log_pos 550 Query thread_id=42 exec_time=0

SET TIMESTAMP=1429614326/*!*/;

drop database aa

最后根据最后一次失误的时间点，使用mysqlbinlog工具对二进制日志文件进行SQL命令提取

$ mysqlbinlog --start-position=107 --stop-position=473 /mydata/mysql-bin.000002 > /root/aaback.sql

1	$ mysqlbinlog --start-position=107 --stop-position=473 /mydata/mysql-bin.000002 > /root/aaback.sql

5.2 关闭当前会话二进制日志记录功能，因为二进制日志不需要记录完整还原（以下操作只能对当前会话生效）

mysql> set sql_log_bin=0;
mysql> show master status;

1 2	mysql> set sql_log_bin=0; mysql> show master status;

5.3 还原第一次完整备份加二进制提取的数据（注意由于sql_log_bin=0只对当前会话生效，所以只能使用source进行还原）

mysql> source /root/aa.sql;
mysql> select * from aa.bb;
mysql> source /root/aaback.sql;
mysql> select * from aa.bb;
mysql> show master status;
mysql> set sql_log_bin=1;

mysql> source /root/aa.sql;

mysql> select * from aa.bb;

mysql> source /root/aaback.sql;

mysql> select * from aa.bb;

mysql> show master status;

mysql> set sql_log_bin=1;

五、mysqldump针对多实例备份脚本

#!/bin/bash
#

# create variables
today=`date +'%Y-%m-%d-%H'`
sevenday=`date -d '7 day ago' +'%Y-%m-%d-%H'`

# create backup directory
dir="backstage meta_message notice weiphoto"
backup_dir=/backup
for i in $dir;do
  mkdir $backup_dir/$i/binlog -p
done

# backup data
for i in $dir;do
  sock="/apps/mysql/$i/mysql.sock"
  mysql -S $sock -e "show databases;" | egrep -v "(Database|information_schema|mysql|performance_schema|sys)" | xargs mysqldump -S \ 
  $sock --single-transaction --set-gtid-purged=OFF --master-data=2 --databases > $backup_dir/$i/$today.sql
  sleep 60
done

# backup binlog
# rsync -vzrtopg /data/mysql//data/master-bin* /backup/binlog

# delete 7 days before
for i in $dir;do
  rm -rf $backup_dir/$i/${sevenday}.sql
done