一, 背景
工作中有需求数据库需要迁移, 但是不能停服, 不能锁库锁表影响业务的正常运行, 所以使用 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-HiRes perl-DBD-MySQL libev numactl
- [root@host1 ~]# rpm -Uvh percona-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 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 | grep mysql
- tcp 0 0 192.168.0.131:3307 0.0.0.0:* LISTEN 35205/mysqld
- tcp 0 0 192.168.0.131:3308 0.0.0.0:* LISTEN 37161/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.000001 32399093
- 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: Waiting for master to send event
- Master_Host: 192.168.0.131
- Master_User: slaveuser
- Master_Port: 3307
- ............
- ............
- Slave_IO_Running: Yes
- Slave_SQL_Running: Yes
- ............
- ............
- 1 row in set (0.00 sec)
二,
- # 主: 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:
- 1 row in set (0.00 sec)
- # 从: 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: Waiting for master to send event
- Master_Host: 192.168.0.131
- Master_User: slaveuser
- Master_Port: 3308
- ............
- ............
- Slave_IO_Running: Yes
- Slave_SQL_Running: Yes
- ............
- ............
- 1 row in set (0.00 sec)
至此互为主从已配置好, 两个库均可执行读写操作, 且互相同步
如果是两个不同 IP 的服务器做主从, 只需改下 IP 就行, 操作方式一样
来源: https://www.cnblogs.com/01-single/p/9210347.html