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

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-xtrabakcup/
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/

#原有主数据库版本
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/

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

#备份
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进行切换

#查看主库状态
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

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

 

MariaDB主从复制

MariaDB主从复制允许在多个服务器上实现数据的异步复制,可有效提升数据库的负载能力,也是实现读写分离方案的基础,是一种常用的HA方案。

环境说明

主库服务器: 192.168.71.151,CentOS 7,MariaDB 10已安装,无应用数据。
从库服务器1: 192.168.71.152,CentOS 7,MariaDB 10已安装,无应用数据。
从库服务器2: 192.168.71.153,CentOS 7,MariaDB 10已安装,无应用数据。
3个服务器的MariaDB都正常运行。
MariaDB 10服务器的常规安装配置可参照《MariaDB 10安装说明》

各个服务器的共同配置

以下操作在各个数据库服务器配置文件的[mysqld]部分下执行,数据库配置文件路径假定为 /etc/my.cnf 。
/etc/my.cnf 中有关于主从配置的一些说明,见my.cnf中# Replication Master Server (default)# Replication Slave (comment out master section to use this)部分。
打开各个数据库服务器的配置文件 my.cnf

  1. 检查确保各个服务器的skip-networking这行是注释掉的。主从复制需要数据库服务器使用IP监听的方式,不然使用UNIX socket方式监听,其他服务器访问不到。
  2. 把bind-address指定为各个服务器网卡的绑定IP上。即在配置文件的 #skip-networking行后面添加bind-address=192.168.71.x,在192.168.71.151上配置为bind-address=192.168.71.151,在192.168.71.152上配置为bind-address=192.168.71.152,在192.168.71.153上配置为bind-address=192.168.71.153
  3. 配置server_id。server_id值为1到2的32次方-1的整数,每个服务器都需要添加server_id配置,各个服务器的server_id需要保证唯一性互不相同,实践中通常设置为服务器IP地址的最后一位,即分别设置为server_id=151server_id=152server_id=153
    上述配置完后调用service mysql reload重新加载配置文件。

配置 主服务器

以下操作在主服务器192.168.71.151的/etc/my.cnf上进行。

确保log-bin是启用的

log-bin=mysql-bin是非注释状态的,log-bin没指定存储目录,则是默认datadir指向的目录,可登录MariaDB shell通过如下命令查看:

MariaDB [(none)]> show variables like 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+

创建帐号并赋予replication的权限

从库 从主库复制数据时需要使用这个帐号进行

MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'192.168.71.%' IDENTIFIED BY 'bigs3cret';
Query OK, 0 rows affected (0.00 sec)

查看主库binary log的文件位置

  • 主库锁表操作,不让数据库进行写入操作
MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
  • 记录主库log文件及其当前位置
MariaDB [(none)]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      326 |              |                  |
+------------------+----------+--------------+------------------+

记住File和Position的部分,后面会用到

  • 保持当前MariaDB shell终端处于打开状态
    即保持主库处于锁定状态,如果关闭MariaDB shell会导致主库恢复非锁定状态
  • 备份主库已有数据并导入从库
    如果主库中有数据需要先备份并导入到从库中。使用新的终端窗口或终端模拟器Tab ssh登录192.168.71.151服务器,执行如下语句进行数据库备份操作
[root@localhost ~]# mysqldump -uroot -p --all-databases > databases.sql

解锁 主库

数据备份完成后,就可以释放主库上的锁:

MariaDB [(none)]> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

在 从服务器 上的操作

以下操作需要在从库192.168.71.152和192.168.71.153上执行。

导入备份的主库数据

[root@localhost ~]# mysql -uroot -p < databases.sql

设置relay-log

my.cnf文件中添加一行relay_log=relay-bin
如果不设置,默认是按主机名 + “-relay-bin”生成relay log。

设置主从复制

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.71.151',MASTER_USER='slave_user', MASTER_PASSWORD='bigs3cret', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS= 326;
Query OK, 0 rows affected (0.24 sec)

这个命令完成以下几个任务:

  1. 设置当前服务器为192.168.71.151的从库
  2. 提供当前数据库(从库)从主库复制数据时所需的用户名和密码,即上面的GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'192.168.71.%' IDENTIFIED BY 'bigs3cret';设置的
  3. 指定从库开始复制主库时需要使用的日志文件和文件位置,即上面主库执行SHOW MASTER STATUS;显示结果中的File和Position

开启主从复制

MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

查看从库状态

MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.71.151
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 326
               Relay_Log_File: relay-bin.000001
                Relay_Log_Pos: 306
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 565
              Relay_Log_Space: 826
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 151
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
                   Using_Gtid: No
                  Gtid_IO_Pos: 
1 row in set (0.00 sec)

结果中Slave_IO_RunningSlave_SQL_Running必须为Yes,如果不是,需要根据提示的错误修改。

测试主从复制是否正常

在主库192.168.71.151的MariaDB shell上创建表或修改数据,看是否从库也跟着更新,如果跟着更新则说明正常。
例如,假定主库上有数据库 newdatabase,在主库上执行

MariaDB [(none)]> use newdatabase;
Database changed
MariaDB [newdatabase]> create table test (id int unsigned auto_increment primary key);
Query OK, 0 rows affected (1.07 sec)

在每个从库上执行

MariaDB [(none)]> use newdatabase;
Database changed
MariaDB [newdatabase]> show tables;
+-----------------------+
| Tables_in_newdatabase |
+-----------------------+
| test                 |
+-----------------------+
1 rows in set (0.00 sec)

如上则说明主从配置成功。

参考文献

[1] https://mariadb.com/kb/en/mariadb/setting-up-replication/
[2] https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-in-mysql