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

原文：

https://wsgzao.github.io/post/xtrabackup/

前言

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

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

更新历史

2015 年 08 月 07 日 - 初稿

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

扩展阅读

Percona XtraBackup - https://www.percona.com/software/mysql-database/percona-xtrabackupMySQL 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-xtrabakcup/xtrabackup 详解 - http://www.cnblogs.com/gomysql/p/3650645.htmlLTMP 索引 - https://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），以达到数据的一致性。备份分为两个过程：

backup，备份阶段，追踪事务日志和复制数据文件（物理备份）。

preparing，重放事务日志，使所有的数据处于同一个时间点，达到一致性状态。

XtraBackup 的优点

可以快速可靠的完成数据备份（复制数据文件和追踪事务日志）

数据备份过程中不会中断事务的处理（热备份）

节约磁盘空间和网络带宽

自动完成备份鉴定

因更快的恢复时间而提高在线时间

配置

准备工作

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

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152

# 原有主数据库版本mysql -Vmysql Ver 14.14 Distrib 5.5.31, for Linux (x86_64) using readline 5.1# 迁移从数据库版本mysql -Vmysql 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/

123

yum -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

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354

# 备份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/dataservice 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 804497686CHANGE MASTER TOMASTER_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=YesSlave_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 进行切换

12345678910111213141516171819202122232425262728293031323334353637383940414243

# 查看主库状态show processlist;Master has sent all binlog to slave; waiting for binlog to be updatedshow 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 itshow 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 TOMASTER_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

1234

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

查看原文