• 进入"运维那点事"后,希望您第一件事就是阅读“关于”栏目,仔细阅读“关于Ctrl+c问题”,不希望误会!

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

MySQL 彭东稳 8年前 (2016-04-09) 24169次浏览 已收录 0个评论

一、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基本用法

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

1. 备份指定库

2. 备份指定表

3. 备份所有库

4. 根据指定条件备份

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

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

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

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

5. 备份表结构

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

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

第一种:在Linux命令行导入

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

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

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

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

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

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

2)完整热备份

2.1 对于InnoDB存储引擎

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

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

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'

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

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

因此在导出数据时,有两种可选方案:

  1. 在有目标库的 super 权限时,用默认 dump 参数,在导入到目标库之前,先执行 reset master;这样需要在主库和所有备库都执行相同个导入动作,风险很大;
  2. 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存储引擎(执行温备份)

3)添加一些新的数据表

4)删除数据库(模拟数据故障)

5)恢复备份数据

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

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

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

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

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

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

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

<参考>

http://mysql.taobao.org/monthly/2016/02/10/

https://mp.weixin.qq.com/s/6c-OoWQ1fQwOu8V5Jcrcqg


如果您觉得本站对你有帮助,那么可以支付宝扫码捐助以帮助本站更好地发展,在此谢过。
喜欢 (2)
[资助本站您就扫码 谢谢]
分享 (0)

您必须 登录 才能发表评论!