如何使用mysqldump对MySQL的数据进行备份的操作好代码教程

借口腾不出时间去健身的人,迟早会腾出时间去看病!善待自己,幸福无比,善待别人,快乐无比,善待生命,健康无比。梦想,就是这样一个词,不在于拥有,而在于追求。

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

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

mydumper 特性

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

mydumper 备份机制

mydumper 工作流程图

主要步骤概括

  • 主线程 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


参数如下:

由于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信息:

在“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;"

出现如下信息:

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

问题分析:
在主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

大概的意思是,在主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其中的主键语句:

然后再导入:

# 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;"

以上就是如何使用mysqldump对MySQL的数据进行备份的操作好代码教程。任何打击都不应该成为你堕落的借口。更多关于如何使用mysqldump对MySQL的数据进行备份的操作好代码教程请关注haodaima.com其它相关文章!

您可能有感兴趣的文章
centos7 mysql5.7主从同步配置

centos7安装MySQL教程

达梦数据库DM8表空间管理之删除表空间

达梦数据库DM8表空间管理之创建表空间

国产达梦数据库常用SQL语句(上)