mysql在线迁移_xtrabackup在线迁移mysql并搭建为主主同步

一、背景

工作中有需求数据库需要迁移，但是不能停服，不能锁库锁表影响业务的正常运行，所以使用XtraBackup

二、环境：

操作系统：CentOS Linux release 7.4.1708 (Core)

mysql：5.6.35

IP：192.168.0.131

xtrabackup版本：percona-xtrabackup-24-2.4.2-1.el7.x86_64.rpm

根据linux系统选择不同版本！

三、安装xtrabackup

[root@host1 ~]# yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev numactl

[root@host1~]# rpm -Uvh percona-xtrabackup-24-2.4.2-1.el7.x86_64.rpm

【优缺点比较】

Xtrabackup备份的优势

备份和恢复非常快

增量备份

流式备份

(解)压缩算法相比逻辑备份的zlib库更快

自身多线程支持

Xtrabackup备份的劣势

不够灵活，无法单独备份指定表、指定库

无法同逻辑备份那样grep特定业务存在的字符串

空间损耗，且不支持逻辑备份恢复时的碎片整理功能

依赖于备份过程中生成的redo log大小，若过大会极大的影响备份和恢复速度

逻辑备份在不同mysql版本中，兼容性较好；而Xtrabackup作为物理备份方案，对mysql server的版本依赖性较强，扩展性较差

四、备份和恢复

my.cnf配置：

[root@host1 ~]# cat /usr/local/mysql/my.cnf

[client]

[mysqld]

[mysqld_multi]

user=mysql

password=mysql

mysqld= /usr/local/mysql/bin/mysqld_safe

mysqladmin= /usr/local/mysql/bin/mysqladmin

[mysqld3307]

socket= /tmp/mysql3307.sock

port= 3307user=mysql

pid-file = /alidata1/mysql_multi/mysql3307/mysqld.pid

datadir= /alidata1/mysql_multi/mysql3307/mydata

log-bin= /alidata1/mysql_multi/mysql3307/log/binlog

server-id = 3307001innodb_buffer_pool_size=256M

auto_increment_increment=2auto_increment_offset=1log_error= /alidata1/mysql_multi/mysql3307/log/log-err

expire_logs_days= 5bind-address = 192.168.0.131skip-name-resolve

[mysqld3308]

socket= /tmp/mysql3308.sock

port= 3308user=mysql

pid-file = /alidata1/mysql_multi/mysql3308/mysqld.pid

datadir= /alidata1/mysql_multi/mysql3308/mydata

log-bin= /alidata1/mysql_multi/mysql3308/log/binlog

server-id = 3308001innodb_buffer_pool_size=256M

auto_increment_increment=2auto_increment_offset=2log_error= /alidata1/mysql_multi/mysql3308/log/log-err

expire_logs_days= 5bind-address = 192.168.0.131skip-name-resolve

一定注意需要做主从的server-id不能是一样的，否则会报错，

在主主同步配置时，需要将两台服务器的auto_increment_increment增长量都配置为2，而要把auto_increment_offset分别配置为1和2，

这样才可以避免两台服务器同时做更新时自增长字段的值之间发生冲突。

通常一般都直接使用innobackupex，因为它能同时备份InnoDB和MyISAM引擎的表

重点关注Slave_IO_Running和Slave_SQL_Runningd的状态是否为YES

2、备份：

[root@host1 ~]# innobackupex --socket=/tmp/mysql3307.sock --user=root --password=123456 --defaults-file=/usr/local/mysql/my.cnf /mysqlbackup

[root@host1~]# innobackupex --defaults-file=/usr/local/mysql/my.cnf --socket=/tmp/mysql3307.sock --user=root --password=123456 --apply-log /mysqlbackup/2018-06-21_10-35-09/　　#保持事务一致性

一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态；

“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态；

innobakupex命令的–apply-log选项可用于实现上述功能。

如果使用另外一台服务器做主从，需要传输备份的数据：

[root@host2 ~]# scp -r 192.168.0.131:/mysqlbackup/2018-06-21_10-35-09 /tmp/backup

3、恢复：

[root@host1 mysql3308]# pwd

/alidata1/mysql_multi/mysql3308

[root@host1 mysql3308]#mvmydata mydatabak　　#备份原有的数据库

[root@host1 mysql3308]#mkdirmydata　　　　　　#新建数据库目录

#恢复数据库：

[root@host1 mysql3308]# innobackupex--defaults-file=/usr/local/mysql/my.cnf --datadir=/alidata1/mysql_multi/mysql3308/mydata/ --socket=/tmp/mysql3308.sock --user=root --password=123456 --copy-back /mysqlbackup/2018-06-21_10-35-09/[root@host1 mysql3308]#chown -R mysql:mysql mydata　　#还原权限

[root@host1 mysql3308]# cd/usr/local/mysql/bin/

#重新启动3308数据库

[root@host1 bin]# ./mysqld_multi --defaults-file=../my.cnf stop 3308 --user=root --password=123456[root@host1 bin]# ./mysqld_multi --defaults-file=/usr/local/mysql/my.cnf start 3308[root@host1 bin]# netstat-nlpt | grepmysql

tcp0 0 192.168.0.131:3307 0.0.0.0:* LISTEN 35205/mysqld

tcp0 0 192.168.0.131:3308 0.0.0.0:* LISTEN 37161/mysqld

五、开启主从同步：

一、

#主：192.168.0.131 3307mysql> GRANT REPLICATION SLAVE ON *.* TO 'slaveuser'@'%' IDENTIFIED BY 'slave123';

mysql>show master status \G

#从：192.168.0.131 3308[root@host1~]# cat /mysqlbackup/2018-06-21_10-35-09/xtrabackup_binlog_info

binlog.000001 32399093mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.131', MASTER_USER='slaveuser', MASTER_PASSWORD='slave123', MASTER_PORT=3307, MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=32399093;

mysql>start slave;

mysql>show slave status\G*************************** 1. row ***************************Slave_IO_State: Waitingformaster to send event

Master_Host:192.168.0.131Master_User: slaveuser

Master_Port:3307…………

…………

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

…………

…………1 row in set (0.00sec)

二、

#主：192.168.0.131 3308mysql> GRANT REPLICATION SLAVE ON *.* TO 'slaveuser'@'%' IDENTIFIED BY 'slave123';

mysql>show master status \G*************************** 1. row ***************************File: binlog.000003Position:592Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:1 row in set (0.00sec)

#从：192.168.0.131 3307mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.131', MASTER_USER='slaveuser', MASTER_PASSWORD='slave123', MASTER_PORT=3308, MASTER_LOG_FILE='binlog.000003', MASTER_LOG_POS=592;

mysql>start slave;

mysql>show slave status\G*************************** 1. row ***************************Slave_IO_State: Waitingformaster to send event

Master_Host:192.168.0.131Master_User: slaveuser

Master_Port:3308…………

…………

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

…………

…………1 row in set (0.00 sec)

至此互为主从已配置好，两个库均可执行读写操作，且互相同步

如果是两个不同IP的服务器做主从，只需改下IP就行，操作方式一样

PS：innobackupex备份数据库的各文件说明：

(1) backup-my.cnf —— 备份命令用到的配置选项信息；

(2) ibdata —— 备份的表空间文件；

(3) xtrabackup_binary —— 备份中用到的xtrabackup的可执行文件；

(4) xtrabackup_binlog_info —— mysql服务器当前正在使用的二进制日志文件及至备份这一刻为止

(5) xtrabackup_checkpoints —— 备份类型(如完全或增量)、备份状态(如是否已经为prepared状态)和LSN(日志序列号)范围信息；

(6) xtrabackup_logfile —— 备份的重做日志文件。

在使用innobackupex进行备份时，还可以使用–no-timestamp选项来阻止命令自动创建一个以时间命名的目录；如此一来，innobackupex命令将会创建一个BACKUP-DIR目录来存储备份数据。

参考文档：