当前位置:Gxlcms > mysql > 使用mysqldump对MySQL的数据进行备份的操作教程_MySQL

使用mysqldump对MySQL的数据进行备份的操作教程_MySQL

时间:2021-07-01 10:21:17 帮助过:26人阅读

MySQL 自身的 mysqldump 工具支持单线程工作, 依次一个个导出多个表,没有一个并行的机 ,这就使得它无法迅速的备份数据。

mydumper 作为一个实用工具,能够良好支持多线程工作, 可以并行的多线程的从表中读入数据并同时写到不同的文件里 ,这使得它在处理速度方面快于传统的 mysqldump 。其特征之一是在处理过程中需要对列表加以锁定,因此如果我们需要在工作时段执行备份工作,那么会引起 DML 阻塞。但一般现在的 MySQL 都有主从,备份也大部分在从上进行,所以锁的问题可以不用考虑。这样, mydumper 能更好的完成备份任务。

mydumper 特性

  • 多线程备份
  • 因为是多线程逻辑备份,备份后会生成多个备份文件
  • 备份时对 MyISAM 表施加 FTWRL (FLUSH TABLES WITH READ LOCK), 会阻塞 DML 语句
  • 保证备份数据的一致性
  • 支持文件压缩
  • 支持导出binlog
  • 支持多线程恢复
  • 支持以守护进程模式工作,定时快照和连续二进制日志
  • 支持将备份文件切块

mydumper 备份机制

mydumper 工作流程图

2015129171812012.png (600×521)

主要步骤概括

  • 主线程 FLUSH TABLES WITH READ LOCK , 施加全局只读锁,以阻止 DML 语句写入,保证数据的一致性
  • 读取当前时间点的二进制日志文件名和日志写入的位置并记录在 metadata 文件中,以供即使点恢复使用
  • N 个(线程数可以指定,默认是 4 ) dump 线程 START TRANSACTION WITH CONSISTENT SNAPSHOT ; 开启读一致的事物
  • dump non-InnoDB tables , 首先导出非事物引擎的表
  • 主线程 UNLOCK TABLES 非事物引擎备份完后,释放全局只读锁
  • dump InnoDB tables , 基于事物导出 InnoDB 表
  • 事物结束
  • 备份所生成的文件

所有的备份文件在一个目录中,目录可以自己指定
目录中包含一个 metadata 文件
记录了备份数据库在备份时间点的二进制日志文件名,日志的写入位置,

如果是在从库进行备份,还会记录备份时同步至主库的二进制日志文件及写入位置

每个表有两个备份文件:

  1. database.table-schema.sql 表结构文件
  2. database.table.sql 表数据文件

如果对表文件分片,将生成多个备份数据文件,可以指定行数或指定大小分片


安装使用实例

假设现有2台DB服务器,分别用于A业务与B业务,其中A业务比较重要,需要对A业务的1个DB(TaeOss)进行热备,大概有40G的数据,并用业务B的DB服务器作为备机,服务器分布如下:
10.137.143.151 A业务
10.137.143.152 B业务

假设要达到的要求是:
在导出A业务的DB(TaeOss)时,不能对A业务有影响。同时在B业务的DB服务器上进行恢复时,也不能有较大影响,尽量控制在1分钟以内。

采取的方案:
1、mysqldump:属于逻辑备份,会存在锁表,但考虑到数据量比较大,锁表的时间会比较长,业务不允许,pass掉;
2、xtrabackup:属于物理备份,不存在锁表,但考虑到2台DB使用的都是共享表空间,同时在业务B的数据库进行恢复时,一是时间比较长,二是数据肯定不正确,pass掉(测试过);
3、mydumper:属于逻辑备份,是一个多线程、高性能的数据逻辑备份、恢复的工具,且锁表的时间很短(40G数据,10分钟以内),同时会记录binlog file和pos,业务可以接受。

mydumper主要有如下特性:
(1)、任务速度要比mysqldump快6倍以上;
(2)、事务性和非事务性表一致的快照(适用于0.2.2以上版本);
(3)、快速的文件压缩;
(4)、支持导出binlog;
(5)、多线程恢复(适用于0.2.1以上版本);
(6)、以守护进程的工作方式,定时快照和连续二进制日志(适用于0.5.0以上版本)。

mydumper安装:
https://launchpad.net/mydumper/0.6/0.6.2/+download/mydumper-0.6.2.tar.gz

# yum install glib2-devel mysql-devel zlib-devel pcre-devel
# tar zxvf mydumper-0.6.2.tar.gz
# cd mydumper-0.6.2
# cmake .
# make
# make install


参数如下:

2015129171903260.jpg (426×283)

由于DB是部署在比较老的SuSE Linux 10服务器上,安装mydumper时依赖的库比较多,会比较繁琐,同时采用本地备份的话,也会占用大量的磁盘I/O,所以我们选择在同网段的另一台centos 6.4(10.137.143.156)服务器进行备份。

步骤如下:
1、在“10.137.143.151、10.137.143.152”上对“10.137.143.156”进行临时授权

# mysql -uroot -e "grant all privileges on *.* to 'backup'@'10.137.143.156' identified by 'backup2015';"
# mysql -uroot -e "flush privileges;"


2、在“10.137.143.156”上对“10.137.143.151”的DB(TaeOss)进行备份

# mydumper -h 10.137.143.151 -u backup -p backup2015 -B TaeOss -t 8 -o /data/rocketzhang


3、将备份数据恢复到“10.137.143.152”

# myloader -h 10.137.143.152 -u backup -p backup2015 -B TaeOss -t 8 -o -d /data/rocketzhang


4、主从关系建立:10.137.143.151(主)、10.137.143.152(从)
在“10.137.143.151”建立授权账号:

# mysql -uroot -e "grant replication slave on *.* to 'repl'@'10.137.143.152' identified by 'repl123456';"
# mysql -uroot -e "flush privileges;"
 

在“10.137.143.156”查看记录下的binlog信息:

2015129171947125.jpg (429×96)

在“10.137.143.152”如下操作:

# vim /etc/my.cnf
……
replicate-do-table = TaeOss.%
replicate-wild-do-table = TaeOss.%
……
 
# service mysqld reload
 
# mysql -uroot -e "change master to master_host='10.137.143.151',master_user='repl',master_password='repl123456',master_log_file='mysql-bin.002205',master_log_pos=456584891;"
# mysql -uroot -e "start slave;"
 
# mysql -uroot -e "show slave status\G;"

出现如下信息:

2015129172013546.jpg (428×106)

看来是存在主键冲突,导致主从复制失败。

问题分析:
在主DB(10.137.143.151)上执行:

# mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.002205 > mysql-bin.002205.txt
# grep -C 8 529864938 mysql-bin.002205.txt

2015129172034758.jpg (429×233)

大概的意思是,在主DB上存在对t_evil_detect_uin_blacklist表的insert操作时,发生了主键冲突,当在从端进行同步的时候,也出现了主键冲突,从而导致主从同步失败。

临时的解决办法:
导出从端的表TaeOss.t_evil_detect_uin_blacklist

# mysqldump -uroot --opt TaeOss t_evil_detect_uin_blacklist > TaeOss.t_evil_detect_uin_blacklist.sql


去掉TaeOss.t_evil_detect_uin_blacklist.sql其中的主键语句:

2015129172057143.jpg (433×181)

然后再导入:

# mysql -uroot TaeOss < TaeOss.t_evil_detect_uin_blacklist.sql
 
# mysql -uroot -e "stop slave;"
# mysql -uroot -e "start slave;"
# mysql -uroot -e "show slave status\G;"

2015129172123185.jpg (430×240)

人气教程排行