浅谈mysqldump

Ⅰ、mysqldump的简单使用与注意点

1.1 基本参数

只备份innodb,用不了几个参数,记住下面几个即可,其他的没什么卵用

-A 备份所有的database-B 备份哪几个数据库-R 备份存储过程(-- routines)-E 备份定时任务(-- events)-d 只备份表结构-w 备份过滤数据-t 只备份数据-q 直接读数据,绕过缓冲池,默认已加,防止污染缓冲池--triggers 备份触发器--master-data=2 在备份文件中以注释的形式记录备份开始时binlog的position,默认值是1,不注释

tips：
①--set-gtid-purged=OFF 如果实例开了gtid最好加上这个参数,不然备份时候会报warning,且备份出来的数据恢复到其他版本的实例上会报错：A partial dump from a server that has GTIDs is not allowed.另外，若实例开启gtid使用mysqldump时不加这个参数，备份文件中会设置不记录binlog
②--dump-slave,该参数可以用作在从库做备份获取主库的位置点,来做一个新从库,避免在主库做备份影响业务,带该参数备份时,从上sql线程会被kill,备份完再拉起

常见用法：

mysqldump --single-transaction -B test a > backup.sql    备份test库和a库mysqldump --single-transaction test a > backup.sql       备份test库下的a表mysqldump --single-transaction test a -w "c=12"> backup.sql

1.2 其他参数

--lock-tables(-l)在备份中依次锁住所有表,一般用于myisam备份,备份时数据库只能提供读操作,以此来保证数据一致性,该参数和--single-transaction是互斥的,所以实例中既存在myisam又存在innodb则,只能使用该参数--lock-all-tables(-x)比上面的参数力度更大,备份时将整个实例锁住

1.3 重点

--single-transaction必须加（一个事务中导出数据,确保产生一致性的备份数据）

my.cnf中配上下面配置

[mysqldump]single-transactionmaster-data=2

Ⅱ、实现原理剖析

2.1 开glog嗖哈一把(^__^)

session 1:(root@localhost) [(none)]> truncate mysql.general_log;Query OK, 0 rows affected (0.02 sec)(root@localhost) [(none)]> set global general_log = 1;Query OK, 0 rows affected (0.00 sec)(root@localhost) [(none)]> set global log_output = 'table';Query OK, 0 rows affected (0.00 sec)session 2:[root@VM_0_5_centos src]# mysqldump --single-transaction --master-data=2 -B dump_test > /tmp/back.sqlsession 1:(root@localhost) [(none)]> set global general_log = 0;Query OK, 0 rows affected (0.00 sec)(root@localhost) [(none)]> set global log_output = 'file';Query OK, 0 rows affected (0.00 sec)#拿到mysqldump线程对应操作的线程id(root@localhost) [(none)]> select thread_id,left(argument,64) from mysql.general_log;(root@localhost) [(none)]> select argument from mysql.general_log where thread_id=239 order by event_time;FLUSH /*!40101 LOCAL */ TABLES# 先把表刷一把,减少ftwrl锁的等待时间FLUSH TABLES WITH READ LOCK# 把当前整个实例锁成只读SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ# 设置备份线程事务隔离级别为rrSTART TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */# 开启事务SHOW VARIABLES LIKE 'gtid\_mode'                                                                    SHOW MASTER STATUS# 获得当前二进制日志位置UNLOCK TABLES# 释放实例级别的只读锁SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('dump_test'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAMESELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('dump_test')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAMESHOW VARIABLES LIKE 'ndbinfo\_version'dump_testSHOW CREATE DATABASE IF NOT EXISTS `dump_test`SAVEPOINT sp# 使用savepoint sp,便于回滚,用于快速释放metadata数据共享锁show tables                                                                                        show table status like 'dump\_inno'SET SQL_QUOTE_SHOW_CREATE=1SET SESSION character_set_results = 'binary'show create table `dump_inno`SET SESSION character_set_results = 'utf8'show fields from `dump_inno`show fields from `dump_inno`SELECT /*!40001 SQL_NO_CACHE */ * FROM `dump_inno`SET SESSION character_set_results = 'binary'use `dump_test`select @@collation_databaseSHOW TRIGGERS LIKE 'dump\_inno'SET SESSION character_set_results = 'utf8'# 以上部分备份数据ROLLBACK TO SAVEPOINT sp                                                                            RELEASE SAVEPOINT sp# 回到savepoint sp,释放metadata的锁# 每取一张表的数据,就rollback to savepoint sp（一个savepoint就够了）root@localhost on  using Socket/*!40100 SET @@SQL_MODE='' *//*!40103 SET TIME_ZONE='+00:00' */

2.2 mysqldump流程小结

2.3 关键点细说

• start transaction with consistent snapshot

位置点问题：事务隔离级别是rr,开始事务,并且马上创建一个read_view,所以mysqldump备份的数据是备份开始时候的数据而不是备份结束时的数据(备份了30min,整个过程实例一直可读可写,备份的是30min之前的数据而不是30min之后的数据)

执行start transaction同时（而不是等到执行第一条sql）建立与本事务一致性读的snapshot

• --single-transaction

所有的数据都是在一个事务里面读出来,而且事务隔离级别是如rr的,所以读到的数据是一致的

一致性备份：整个备份从start transaction开始,备份所有的表,所有的表的数据都是在一个事务里面,通过select导出来

• savepoint保存点（4.1还是5.0加进来的）

savepoint很少用,真正用的最多就是备份的时候,一张表备份完,会回滚到对应保存点,此时对应备份的表上面的元数据锁都释放,这时候可以这个表可以做ddl操作

否则在一个事务里,持有元数据锁,要做ddl(比如其他线程想对这里一个表创建索引),即使备份完了也做不了,要等所有备份结束才能动

没有savepoint时,只读锁要持有整个事务时间,而不是表备份的时间

Ⅲ、实战

3.1 备份并且压缩

mysqldump --single-transaction --master-data=1 --triggers -R -E -B sbtest | pv | gzip -c > sbtest.backup.tgz

压缩过的备份恢复

gunzip < sbtest.backup.tgz |mysql

3.2 压缩到远程服务器

mysqldump --single-transaction --master-data=1 --triggers -R -E -B sbtest |gzip -c |ssh root@test-3 'cat > /tmp/sbtest.sql.gz'

备份校验,另行考虑

3.3 备份文件使用

mysql < xxx.sql;

tips:
备份占用带宽很大,需要调度算法确保同一个集群中同时只有一个机器做备份,或者不每天做备份从而错开备份时间