前提环境:
操作系统:Oracle Linux 7.9 两台
数据库版本:5.7.36
主库IP:192.168.16.51 3306
从库IP:192.168.16.52 3306
MySQL5.7安装教程地址:Oracle Linux 7.9安装MySQL5.7.36_零Ⅰ的博客-CSDN博客
xtrabackup下载地址:percona-xtrabackup-2.4.11-Linux-x86_64.libgcrypt145.tar-MySQL文档类资源-CSDN下载
xtrabackup上传目录:/soft/
前言
需求及数据库架构:目前数据为100g,MySQL单机。为了数据集的安全和高可用冗余,基于主库搭建一个从库,采用Xtrabackup+GTID+无损同步方式,需要主从切换演练(手工切换),验证数据是否同步且一致。同时模拟一些常见的故障,是否得到修复
一、搭建主从
1、主库创建复制用户并授权
mysql -uroot -p
create user 'repuser'@'%' identified by 'repuser123';
grant replication slave on *.* to 'repuser'@'%';
flush privileges;
select user,host from mysql.user;
exit;
2、修改主从配置文件(my.cnf)
主库:
vi /mysql/data/3306/my.cnf
log_bin=/mysql/log/3306/binlog/00db-binlog
log_bin_index=/mysql/log/3306/binlog/00db-binlog.index
binlog_format=row
binlog_rows_query_log_events=on
.
gtid_mode = on
enforce_gtid_consistency = 1
log-slave-updates = 1
binlog_gtid_simple_recovery=1
#--------------------------
#slave parameter
#--------------------------
#relay_log = /mysql/log/3306/relaylog/00db-relay.log
#read_only=1
#slave-parallel-type=LOGICAL_CLOCK
#slave-parallel-workers=4
#master_info_repository=TABLE
#relay_log_info_repository=TABLE
#relay_log_recovery=1
#slave_skip_errors = ddl_exist_errors
#slave_preserve_commit_order=1
#------------------------------------
#semi sync replication settings
#------------------------------------
plugin_dir=/mysql/app/mysql/lib/plugin/
plugin_load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000
rpl_semi_sync_master_wait_point = AFTER_SYNC
rpl_semi_sync_master_wait_for_slave_count = 1
从库:
vi /mysql/data/3306/my.cnf
log_bin=/mysql/log/3306/binlog/00db-binlog
log_bin_index=/mysql/log/3306/binlog/00db-binlog.index
binlog_format=row
binlog_rows_query_log_events=on
gtid_mode = on
enforce_gtid_consistency = 1
log-slave-updates = 1
binlog_gtid_simple_recovery=1
#------------------------------------
#slave parameter
#------------------------------------
relay_log = /mysql/log/3306/relaylog/00db-relay.log
log-slave-updates = 1
read_only=1
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=4
master_info_repository=table
relay_log_info_repository=table
relay_log_recovery=1
slave_skip_errors = ddl_exist_errors
slave_preserve_commit_order=1
#------------------------------------
#semi sync replication settings
#------------------------------------
plugin_dir=/mysql/app/mysql/lib/plugin/
plugin_load ="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_
slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000
rpl_semi_sync_master_wait_point = AFTER_SYNC
rpl_semi_sync_master_wait_for_slave_count = 1
3、分别重启两台MySQL服务并在主库写入数据产生GTID
systemctl restart mysqld
主库:
mysql -uroot -p
create database 00db1;
create table 00db1.00bak11 (id int,name varchar(40));
insert into 00db1.00bak11 values(1,'00111'),(2,'00112'),(3,'00113'),(4,'00114'),(5,'00115');
commit;
select * from 00db1.00bak11;
4、主库备份并恢复到从库:
主库操作,上传xtrabackup到从库192.168.16.52
scp -r /soft/percona-xtrabackup-2.4.11-Linux-x86_64.libgcrypt145.tar.gz 192.168.16.52:/soft
主从库一样操作:
cd /mysql/app
tar zxvf /soft/percona-xtrabackup-2.4.11-Linux-x86_64.libgcrypt145.tar.gz
ln -sf percona-xtrabackup-2.4.11-Linux-x86_64 xtrabackup
做个软链接
写入环境变量
echo "PATH=\$PATH:/mysql/app/mysql/bin:/mysql/app/xtrabackup/bin:$HOME/bin" >> /etc/profile
source /etc/profile
xtrabackup --version
主库备份,并传到备库:
xtrabackup --defaults-file=/mysql/data/3306/my.cnf --user=root --password=root --backup --stream=tar --parallel=2 --target-dir=/mysql/backup/fullback180520 | gzip > /mysql/backup/fullback180520.tar.gz
scp /mysql/backup/fullback180520.tar.gz 192.168.16.52:/mysql/backup/
从库操作:
cd /mysql/backup
mkdir -p /mysql/backup/fullback180520
tar zxvf /mysql/backup/fullback180520.tar.gz -C /mysql/backup/fullback180520
xtrabackup --defaults-file=/mysql/data/3306/my.cnf --prepare --target-dir=/mysql/backup/fullback180520 --parallel=2
systemctl stop mysqld
cd /mysql/data/3306/
rm -rf data_bak
mv data data_bak
mkdir data
chown -R mysql:mysql data
rsync -avrP /mysql/backup/fullback180520/* --exclude='xtrabackup_*' /mysql/data/3306/data/
测试一下数据是否恢复过来
chown -R mysql:mysql data
systemctl start mysqld
mysql -uroot -proot
show databases;
select * from 00db1.00bak11 limit 2;
5、在从库上使slave与master建立连接,从而同步
mysql -uroot -p
------------------------------------
查看从库是否启动,如果启动,需要执行下面操作
mysql> show slave status \G
Empty set (0.00 sec)
stop slave;
reset master;
reset slave;
--------------------------------------------
查看GLOBAL.GTID_PURGED值
cd /mysql/backup/fullback180520/
cat xtrabackup_info
看下面这条信息
binlog_pos = filename '00db-binlog.000001', position '86374074', GTID of the last change '857d06e9-a091-11ec-b901-000c29d0e59f:1-119'
set @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
set @@SESSION.SQL_LOG_BIN= 0;
set @@GLOBAL.GTID_PURGED='3addb98f-7ea1-11e8-8f56-000c294eccb3:1-119';
set @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;
change master to
master_host='192.168.16.51',
master_port=3306,
master_user='repuser',
master_password='repuser123',
master_auto_position=1;
start slave;
show slave status \G
6、主从数据测试:
主库创建1个数据库和表的数据:
create database 00db2;
create table 00db2.00bak21 (id int,name varchar(40));
insert into 00db2.00bak21 values(1,'00211'),(2,'00212'),(3,'00213'),(4,'00214'),(5,'00215');
commit;
select * from 00db2.00bak21;
备库检查:
mysql -uroot -p
select * from 00db2.00bak21;
show slave status \G ------主从库查看一下gtid是否一致
show processlist;
二、主从切换测试
1、主库192.168.16.51:
set global read_only=1;
flush logs;
show master status; ------记录一下当前的日志点
.
2、 从库192.168.16.52:
确保从库已经执行所有的更新,应用完所有的中继日志
show slave status\G
再检查一边线程是否正确:show processlist;
stop slave; --------停止从库
reset master; ------删除主库所有的二进制文件,并重新开始新的二进制日志
reset slave; -------删除从库所有的中继日志文件,并重新开启新的中继日志
show master status; ------记录一下当前的日志点
3、主库192.168.16.51
reset master; ------删除主库所有的二进制文件,并重新开始新的二进制日志
reset slave; -------删除从库所有的中继日志文件,并重新开启新的中继日志
show master status; ------记录一下当前的日志点
4、修改主从库配置文件my.cnf
主库51:
cp /mysql/data/3306/my.cnf /mysql/data/3306/my.cnf.master
vi /mysql/data/3306/my.cnf
把一部分2小段中主库,注释掉的参数,把#去掉就ok。
从库52:
cp /mysql/data/3306/my.cnf /mysql/data/3306/my.cnf.slave
vi /mysql/data/3306/my.cnf
把一部分2小段中从库,参数注释掉就ok。
5、 主从库重启MySQL服务
systemctl restart mysqld
6、原主库51连接接原从库52
原主库192.168.16.51执行:
mysql -uroot -proot
查看原主库192.168.16.51中slave是否启动,如果启动则关闭:stop slave;
change master to
master_host='192.168.16.52',
master_user='repuser',
master_password='repuser123',
master_auto_position=1;
start slave;
show slave status\G
7、测试
原从库192.168.16.52:
mysql -uroot -proot
create user '0002'@'%' identified by '0002';
grant all privileges on 00db2.* to '0002'@'%';
flush privileges;
exit;
mysql -u0002 -p0002
insert into 00db2.00bak21 values (7,'00227');
commit;
select * from 00db2.00bak21;
原主库192.168.16.51检查:
mysql -u0002 -p0002 ------模拟业务用户
select * from 00db2.00bak21;
insert into 00db2.00bak21 values (7,'00227'); ---插入一条数据,无法插入,代表正常
三、主从回切
主从回切按照二部分做就可以。
特别说明:修改配置主从配置文件时,因为在做主从切换的时候,修改配置文件,用的cp方式,只需要把配置文件重名即可。
主库192.168.16.52:
cp /mysql/data/3306/my.cnf /mysql/data/3306/my.cnf.master
cp /mysql/data/3306/my.cnf.slave /mysql/data/3306/my.cnf
从库192.168.16.51:
cp /mysql/data/3306/my.cnf /mysql/data/3306/my.cnf.slave
cp /mysql/data/3306/my.cnf.master /mysql/data/3306/my.cnf
主从回切这边就不再做演示了。