XtraBackup不停机不锁表搭建MySQL主从同步实践

前言

Percona XtraBackup可以说是一个相对完美的免费开源数据备份工具,支持在线无锁表同步复制和可并行高效率的安全备份恢复机制相比mysqldump来说确实让人眼前一亮,与MySQL Enterprise Backup(InnoDB Hot Backup)的功能对比可以参考扩展阅读。当然我们在实际运维过程中都应针对不同的业务需求分析和选择合适的备份恢复方案,这篇文章就是针对MySQL多实例且一个实例对应多个database的情况,实现MySQL在线不停机不锁表的主从同步,日后再继续更新分享基于XtraBackup的其它实用技能。

XtraBackup是目前首选的备份方案之一


更新历史

2015年08月07日 - 初稿

阅读原文 - http://wsgzao.github.io/post/xtrabackup/

扩展阅读

Percona XtraBackup - https://www.percona.com/software/mysql-database/percona-xtrabackup
MySQL 5.6 Reference Manual :: 17 Replication - http://dev.mysql.com/doc/refman/5.6/en/replication.html
基于Xtrabackup的物理备份解决方案预研 - http://tencentdba.com/blog/pre-research-on-physical-backup-using-xtrab...
xtrabackup 详解 - http://www.cnblogs.com/gomysql/p/3650645.html
LTMP索引 - http://wsgzao.github.io/index/#LTMP


原理

MySQL主从同步原理

MySQL主从同步是在MySQL主从复制(Master-Slave Replication)基础上实现的,通过设置在Master MySQL上的binlog(使其处于打开状态),Slave MySQL上通过一个I/O线程从Master MySQL上读取binlog,然后传输到Slave MySQL的中继日志中,然后Slave MySQL的SQL线程从中继日志中读取中继日志,然后应用到Slave MySQL的数据库中。这样实现了主从数据同步功能。

XtraBackup备份原理

innobackupex在后台线程不断追踪InnoDB的日志文件,然后复制InnoDB的数据文件。数据文件复制完成之后,日志的复制线程也会结束。这样就得到了不在同一时间点的数据副本和开始备份以后的事务日志。完成上面的步骤之后,就可以使用InnoDB崩溃恢复代码执行事务日志(redo log),以达到数据的一致性。
备份分为两个过程:
1. backup,备份阶段,追踪事务日志和复制数据文件(物理备份)。
2. preparing,重放事务日志,使所有的数据处于同一个时间点,达到一致性状态。

XtraBackup的优点

  1. 可以快速可靠的完成数据备份(复制数据文件和追踪事务日志)
  2. 数据备份过程中不会中断事务的处理(热备份)
  3. 节约磁盘空间和网络带宽
  4. 自动完成备份鉴定
  5. 因更快的恢复时间而提高在线时间

配置

准备工作

MySQL步骤和my.cnf配置参考LTMP - http://wsgzao.github.io/post/ltmp/

sql
#原有主数据库版本 mysql -V mysql Ver 14.14 Distrib 5.5.31, for Linux (x86_64) using readline 5.1 #迁移从数据库版本 mysql -V mysql Ver 14.14 Distrib 5.6.25, for linux-glibc2.5 (x86_64) using EditLine wrapper #检查数据库引擎 show engines; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ #主从数据库同步注意点 [mysqld] #主从之间的id不能相同 server-id #启用二进制日志 log-bin #一般在从库开启(可选) read_only #推荐使用InnoDB并做好相关配置 #检查主从数据库状态 mysql -S /tmp/mysql.sock -e "show global variables like 'server_id';" +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 1 | +---------------+-------+ mysql -S /tmp/mysql.sock -e "show global variables like 'log_bin';" +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | +---------------+-------+

安装percona-xtrabackup

一般推荐rpm安装 - https://www.percona.com/downloads/XtraBackup/LATEST/

bashyum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL
#rpm -ivh percona-xtrabackup-2.2.12-1.el6.x86_64.rpm 
rpm -Uvh percona-xtrabackup-2.2.12-1.el6.x86_64.rpm 

备份和恢复

通常一般都直接使用innobackupex,因为它能同时备份InnoDB和MyISAM引擎的表
重点关注Slave_IO_Running和Slave_SQL_Runningd的状态是否为YES

sql
#备份 innobackupex --socket=/usr/local/var/mysql2/mysql2.sock --user=root --password --defaults-file=/etc/mysqld_multi.cnf --parallel=4 --database=passport /tmp/backup #保持事务一致性 innobackupex --socket=/usr/local/var/mysql2/mysql2.sock --user=root --password --defaults-file=/etc/mysqld_multi.cnf --database=passport --apply-log /tmp/backup/2015-08-05_16-08-14 #传输 scp -r /tmp/backup/2015-08-05_16-08-14 10.10.16.24:/tmp/backup/ #恢复 innobackupex --socket=/tmp/mysql.sock --user=root --password --defaults-file=/app/local/mysql/my.cnf --copy-back /tmp/backup/2015-08-05_16-08-14/ #还原权限 chown -R mysql:mysql /app/data/mysql/data service mysqld start /app/local/mysql/scripts/mysql_install_db --basedir=/app/local/mysql --datadir=/app/data/mysql/data --no-defaults --skip-name-resolve --user=mysql #主库授权同步帐号 SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user; GRANT REPLICATION SLAVE ON *.* TO 'slave_passport'@'10.10.16.24' IDENTIFIED BY 'slave_passport'; FLUSH PRIVILEGES; #从库开启同步 cat /tmp/backup/2015-08-05_16-08-14/xtrabackup_binlog_info mysql-bin.002599 804497686 CHANGE MASTER TO MASTER_HOST='10.10.16.51', MASTER_USER='slave_passport', MASTER_PASSWORD='slave_passport', MASTER_PORT=3307, MASTER_LOG_FILE='mysql-bin.002599', MASTER_LOG_POS=804497686; #开启主从同步 start slave; #查看从库状态 show slave status\ G #从库的检查参数 Slave_IO_Running=Yes Slave_SQL_Running=Yes #主库的检查参数 show master status \G +------------------+-----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+-----------+--------------+------------------+ | mysql-bin.002600 | 454769337 | | | +------------------+-----------+--------------+------------------+ 1 row in set (0.00 sec) show processlist; Master has sent all binlog to slave; waiting for binlog to be updated

MySQL主从切换

切换前断开主库访问连接观察进程状态,无写操作后再停止从库IO_THREAD进行切换

sql
#查看主库状态 show processlist; Master has sent all binlog to slave; waiting for binlog to be updated show master status \G #从库停止 IO_THREAD 线程 stop slave IO_THREAD; show processlist; Slave has read all relay log; waiting for the slave I/O thread to update it show slave status \G #从库切换为主库 stop slave; reset master; reset slave all; show master status \G #激活帐户 SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user; GRANT REPLICATION SLAVE ON *.* TO 'slave_passport'@'10.10.16.51' IDENTIFIED BY 'slave_passport'; FLUSH PRIVILEGES; #切换原有主库为从库 reset master; reset slave all; CHANGE MASTER TO MASTER_HOST='10.10.16.24', MASTER_USER='slave_passport', MASTER_PASSWORD='slave_passport', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=804497686; #检查主库 SHOW PROCESSLIST; show master status \G #启动从库 SHOW PROCESSLIST; start slave; show slave status \G

常见问题

Slave_SQL_Running:No

sql#一般是事务回滚造成的
stop slave;
set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
start slave;

wsgzao
[链接]

[链接]

1.9k 声望
86 粉丝
0 条评论
推荐阅读
System Design 学习路径
2022年尾比较热门的话题莫过于Elon Musk收购推特后大刀阔斧的改革,加速下滑还是涅槃重生大家一起拭目以待,做时间的朋友。借这个机会我们也可以通过Alex Xu分享的《Twitter Architecture 2022 vs 2012》来一探究...

王奥OX阅读 429

分布式高可用Mysql数据库Percona XtraDB Cluster 8.0 与 Proxysql 史上最详尽用法指南
PXC是Percona XtraDB Cluster的缩写,是 Percona 公司出品的免费MySQL集群产品。PXC的作用是通过mysql自带的Galera集群技术,将不同的mysql实例连接起来,实现多主集群。在PXC集群中每个mysql节点都是可读可写的...

apollo0084阅读 7.1k评论 2

一次偶然机会发现的MySQL“负优化”
今天要讲的这件事和上述的两个sql有关,是数年前遇到的一个关于MySQL查询性能的问题。主要是最近刷到了一些关于MySQL查询性能的文章,大部分文章中讲到的都只是一些常见的索引失效场合,于是我回想起了当初被那个...

骑牛上青山5阅读 1k评论 3

MongoDB 插入时间与更新时间(create_time/update_time)
MongoDB 在数据库层面不能像 MySQL 一样设置自动创建 create_time/update_time,自动更新 update_time

qbit阅读 13.8k评论 2

Mysql索引覆盖
通常情况下,我们创建索引的时候只关注where条件,不过这只是索引优化的一个方向。优秀的索引设计应该纵观整个查询,而不仅仅是where条件部分,还应该关注查询所包含的列。索引确实是一种高效的查找数据方式,但...

京东云开发者2阅读 650

封面图
SegmentFault 思否技术周刊 Vol.70 — 深入 MySQL 实战
MySQL 软件采用了 GPL( GNU 通用公共许可证),由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,许多中小型网站为了降低网站总体拥有成本而选择了 MySQL 作为网站数据库。

Beverly2阅读 1.3k

封面图
MySQL 数据库索引技术原理初探
一本书 500 页的书,如果没有目录,直接去找某个知识点,可能需要找一会儿,但是借助前面的目录,就可以快速找到对应知识点在书的哪一页。这里的目录就是索引。

mylxsw1阅读 1.2k

[链接]

1.9k 声望
86 粉丝
宣传栏