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

《Mysql实例使用mysqldump对MySQL的数据进行备份的操作教程》要点：
本文介绍了Mysql实例使用mysqldump对MySQL的数据进行备份的操作教程，希望对您有用。如果有疑问，可以联系我们。

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

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

MYSQL必读mydumper 特性

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

MYSQL必读mydumper 备份机制

MYSQL必读mydumper 工作流程图

MYSQL必读

MYSQL必读主要步骤概括

• 主线程 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 表
• 事物结束
• 备份所生成的文件

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

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

MYSQL必读每个表有两个备份文件：

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

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

MYSQL必读
安装使用实例

MYSQL必读假设现有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

MYSQL必读
# 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

MYSQL必读 
参数如下：

MYSQL必读

MYSQL必读由于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必读
# mysql -uroot -e "grant all privileges on *.* to 'backup'@'10.137.143.156' identified by 'backup2015';"
# mysql -uroot -e "flush privileges;"

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

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

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

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

MYSQL必读 
4、主从关系建立：10.137.143.151(主)、10.137.143.152(从)
在“10.137.143.151”建立授权账号：

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

MYSQL必读在“10.137.143.156”查看记录下的binlog信息：

MYSQL必读

MYSQL必读在“10.137.143.152”如下操作：

MYSQL必读
# 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;"

MYSQL必读出现如下信息：

MYSQL必读

MYSQL必读看来是存在主键冲突,导致主从复制失败.
 
问题分析：
在主DB(10.137.143.151)上执行：

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

MYSQL必读

MYSQL必读大概的意思是,在主DB上存在对t_evil_detect_uin_blacklist表的insert操作时,发生了主键冲突,当在从端进行同步的时候,也出现了主键冲突,从而导致主从同步失败.
 
临时的解决办法：
导出从端的表TaeOss.t_evil_detect_uin_blacklist

MYSQL必读
# mysqldump -uroot --opt TaeOss t_evil_detect_uin_blacklist > TaeOss.t_evil_detect_uin_blacklist.sql

MYSQL必读 
去掉TaeOss.t_evil_detect_uin_blacklist.sql其中的主键语句：

MYSQL必读

MYSQL必读然后再导入：

MYSQL必读
# 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;"

MYSQL必读

转载请注明本页网址：
http://www.vephp.com/jiaocheng/1745.html