使用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 文件
记录了备份数据库在备份时间点的二进制日志文件名,日志的写入位置,
如果是在从库进行备份,还会记录备份时同步至主库的二进制日志文件及写入位置
每个表有两个备份文件:
- database.table-schema.sql 表结构文件
- 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;"
相关文章
- 这篇文章主要介绍了MySQL性能监控软件Nagios的安装及配置教程,这里以CentOS操作系统为环境进行演示,需要的朋友可以参考下...2015-12-14
- mysqldump命令的用法1、导出所有库系统命令行mysqldump -uusername -ppassword --all-databases > all.sql 2、导入所有库mysql命令行mysql>source all.sql; 3、导出某些库系统命令行mysqldump -uusername -ppassword...2015-10-21
- 这篇文章主要为大家详细介绍了linux下源码安装mysql5.6.20教程的相关资料,具有一定的参考价值,感兴趣的小伙伴们可以参考一下...2017-01-15
通过node-mysql搭建Windows+Node.js+MySQL环境的教程
这篇文章主要介绍了通过node-mysql搭建Windows+Node.js+MySQL环境的教程,node-mysql是JavaScript编写的一个Node的MySQL驱动,需要的朋友可以参考下...2016-03-03- 本篇文章主要介绍了Mac下安装mysql5.7 完整步骤,具有一定的参考价值,有兴趣的可以了解一下,...2017-01-26
- 这篇文章主要为大家详细介绍了Linux CentOS MySQL数据库的安装配置教程,具有一定的参考价值,感兴趣的小伙伴们可以参考一下...2017-05-19
- 如果我们使用Xampp服务器自带数据库mysql,就必须先修改mysql的密码,下面小编给大家分享如何修改Xampp服务器上的mysql密码,需要的朋友参考下吧...2017-04-26
- 这篇文章主要为大家分享了win7下MySql 5.7安装配置方法图文教程,感兴趣的小伙伴们可以参考一下...2016-05-20
- 这篇文章主要介绍了在windows10上安装mysql详细图文教程,本文介绍的非常详细,具有参考借鉴价值,感兴趣的朋友一起看看吧...2016-09-18
- 今天工作中需要对一台ubantu的系统安装mysql,因为以前一直使用的是centos,虽然它也是类unix但是和redhat或centos命令上还是有点差别。所以通过网上查阅资料,终于安装成功了,现在将步骤分享给大家,有需要的朋友们可以参考借鉴。...2016-10-20
- 这篇文章主要为大家详细介绍了linux上mysql安装详细教程,具有一定的参考价值,感兴趣的小伙伴们可以参考一下...2017-02-08
centos 6.5下 mysql-community-server. 5.7.18-1.el6安装
这篇文章主要介绍了centos 6.5下 mysql-community-server. 5.7.18-1.el6安装,需要的朋友可以参考下...2017-05-09Hibernate4在MySQL5.1以上版本创建表出错 type=InnDB
本文主要介绍解决Hibernate4在MySQL5.1自动创建表出错的方法,简单实用,需要的朋友可以参考下。...2016-06-12- 这篇文章主要介绍了MAC版修改MySQL初始密码的方法,非常不错,具有参考借鉴价值,需要的朋友可以参考下...2017-02-08
- 应一个朋友要求写了这个批处理程序,主要用途就是在win32系统中方便停止、开启、重启IIS、MySQL服务。 ...2016-01-27
- 这篇文章主要介绍了jQuery+PHP+MySQL实现无限级联效果的相关资料,具有一定的参考价值,感兴趣的小伙伴们可以参考一下...2016-02-21
thinkphp3.x连接mysql数据库的方法(具体操作步骤)
这篇文章主要介绍了thinkphp3.x连接mysql数据库的方法,详细分析了thinkPHP3.x操作数据库的具体步骤,包括惯例配置文件设置,sql语句创建表以及针对数据库的数据插入操作等,需要的朋友可以参考下...2016-05-20- 这篇文章主要介绍了Centos下 修改mysql密码的方法,需要的朋友可以参考下...2017-03-14
IIS 环境下配置PHP5+MySql+PHPMyAdmin
虽然主要是做.net开发的,但是,时不时的还要搞一下php,但是,php在windows下的配置,总是走很多弯路,正好前几天又配置了一下,因此总结在这里,做为自己的备忘,也希望给遇到问题的朋友们提供一些帮助。...2016-01-27个人网站留言页面(前端jQuery编写、后台php读写MySQL)
这篇文章主要为大家介绍了个人网站的留言页面,前端使用jQuery编写、后台利用php简单读写MySQL数据库,感兴趣的小伙伴们可以参考一下...2016-05-05