xtrabackup mysql迁移_xtrabackup 在线迁移 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

xtrabackup 下载地址: https://www.percona.com/downloads/XtraBackup/LATEST/

三, 安装 xtrabackup[root@host1~]#yum-y install perl perl-devel libaio libaio-devel perl-Time-HiResperl-DBD-MySQLlibev numactl

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

四, 备份和恢复

1, 使用环境: 本人测试使用的是为环境新搭建的 mysql_multi 多实例方式, 具体安装步骤见: https://www.cnblogs.com/01-single/p/9051412.html

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=3307

user=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=3307001

innodb_buffer_pool_size=256M

log_error=/alidata1/mysql_multi/mysql3307/log/log-err

expire_logs_days=5

bind-address=192.168.0.131

skip-name-resolve

[mysqld3308]

socket=/tmp/mysql3308.sock

port=3308

user=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=3308001

innodb_buffer_pool_size=256M

log_error=/alidata1/mysql_multi/mysql3308/log/log-err

expire_logs_days=5

bind-address=192.168.0.131

skip-name-resolve

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

通常一般都直接使用 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/#保持事务一致性

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

[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]#mv mydata mydatabak#备份原有的数据库

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

# 恢复数据库:

[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 stop3308--user=root--password=123456

[root@host1 bin]#./mysqld_multi--defaults-file=/usr/local/mysql/my.cnf start3308

[root@host1 bin]#netstat-nlpt|grep mysql

tcp00192.168.0.131:33070.0.0.0:*LISTEN35205/mysqld

tcp00192.168.0.131:33080.0.0.0:*LISTEN37161/mysqld

五, 开启主从同步:

一,# 主: 192.168.0.131 3307

mysql>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.00000132399093

mysql>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 sendevent

Master_Host:192.168.0.131

Master_User:slaveuser

Master_Port:3307

............

............

Slave_IO_Running:Yes

Slave_SQL_Running:Yes

............

............

1rowinset(0.00sec)

二,# 主: 192.168.0.131 3308

mysql>GRANT REPLICATION SLAVE ON*.*TO'slaveuser'@'%'IDENTIFIED BY'slave123';

mysql>show master status \G

***************************1.row***************************

File:binlog.000003

Position:592

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1rowinset(0.00sec)

# 从: 192.168.0.131 3307

mysql>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 sendevent

Master_Host:192.168.0.131

Master_User:slaveuser

Master_Port:3308

............

............

Slave_IO_Running:Yes

Slave_SQL_Running:Yes

............

............

1rowinset(0.00sec)

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

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

来源: https://www.cnblogs.com/01-single/p/9210347.html