MySQL集群读写分离方案A-(MySQL+GTID+ProxySQL+Keepalive)之4/7：无损复制GTID配置

2023年12月19日 435点热度 0人点赞 0条评论

一、项目需求
- 1、客户需求
- 2、数据库架构
二、备份工具Xtrabackup准备
三、配置主从复制

一、项目需求

1、客户需求

利用GTID，配置主从同步。

2、数据库架构

主从复制，基于主库搭建一个从库，采用Xtrabackup+GTID+无损同步复制技术。

二、备份工具Xtrabackup准备

1、MySQL5.7版本的xtrabackup

（1）官网下载：

官网可以下载MySQL5.7系列的安装包；也可以下载8.0系列的安装包。

https://www.percona.com/software/mysql-database/percona-xtrabackup
cd /mysql/soft
wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.27/binary/tarball/percona-xtrabackup-2.4.27-Linux-x86_64.glibc2.12.tar.gz

（2）私网下载

wget https://download.topunix.com/MySQL/Software-backup/Xtrabackup/percona-xtrabackup-2.4.27-Linux-x86_64.glibc2.12.tar.gz

（3）百度云盘下载

链接：https://pan.baidu.com/s/15BZuEZ2tHUhGkijOQpfyvA
提取码：5syu

2、MySQL8.0版本的xtrabackup

（1）官网下载：

https://downloads.percona.com/downloads/Percona-XtraBackup-8.0/Percona-XtraBackup-8.0.32-25/binary/tarball/percona-xtrabackup-8.0.32-25-Linux-x86_64.glibc2.17.tar.gz

（2）百度云盘

链接：https://pan.baidu.com/s/1aCSGr0Q_Bz3eHDWge6qGuw
提取码：hrsj

（3）内网下载：

http://192.168.20.3/soft-database/mysql/linux-percona/percona-xtrabackup-8.0.32-25-Linux-x86_64.glibc2.17.tar.gz

3、安装前准备(主备库）

（1）安装依赖项

yum install perl-DBD-MySQL.x86_64 -y

（2）my.cnf中的client需要配置socket

[client]
socket=/mysql/data/3306/mysql.sock

4、安装(主备库）

（1）主库下载安装

cd /mysql/soft
wget https://download.topunix.com/MySQL/Software-backup/Xtrabackup/percona-xtrabackup-2.4.27-Linux-x86_64.glibc2.12.tar.gz
cd /mysql/app
tar zxvf /mysql/soft/percona-xtrabackup-2.4.27-Linux-x86_64.glibc2.12.tar.gz
ln -sf percona-xtrabackup-2.4.27-Linux-x86_64.glibc2.12 xtrabackup
echo "PATH=\$PATH:/mysql/app/mysql/bin:/mysql/app/xtrabackup/bin:$HOME/bin" >> /etc/profile
source /etc/profile
xtrabackup --version

（2）备库

cd /mysql/soft
scp -r 192.168.21.151:/mysql/soft/percona-xtrabackup-2.4.27-Linux-x86_64.glibc2.12.tar.gz /mysql/soft
cd /mysql/app
tar zxvf /mysql/soft/percona-xtrabackup-2.4.27-Linux-x86_64.glibc2.12.tar.gz
ln -sf percona-xtrabackup-2.4.27-Linux-x86_64.glibc2.12 xtrabackup 
echo "PATH=\$PATH:/mysql/app/mysql/bin:/mysql/app/xtrabackup/bin:$HOME/bin" >> /etc/profile
source /etc/profile

xtrabackup --version

三、配置主从复制

1、主库创建用户

mysql -uroot -prootroot
create user 'repuser'@'%' identified by 'repuser123';
grant replication slave on *.* to 'repuser'@'%';
flush privileges;
select user,host from mysql.user;
exit;

2、主库配置-192.168.21.151(master)

注意：如下配置直接贴到配置文件最后即可。文档是后面的配置覆盖前面的数据，最终赋值的参数生效。

vi /mysql/data/3306/my.cnf

##################################replication Settings##################################
########################################################################################
########################################################################################

#master add parameter
log_bin=/mysql/log/3306/binlog/llldb-binlog
log_bin_index=/mysql/log/3306/binlog/llldb-binlog.index
binlog_format=row
binlog_rows_query_log_events=on
#master modify parameter
bind-address=0.0.0.0
server_id=513306
skip_name_resolve = on
expire_logs_days = 7
innodb_support_xa =1
binlog_cache_size = 1M
max_binlog_size = 2048M
log_bin_trust_function_creators = 1
innodb_flush_log_at_trx_commit =1
sync_binlog = 1
transaction-isolation = read-committed
gtid_mode = on
enforce_gtid_consistency = 1
log-slave-updates = 1
binlog_gtid_simple_recovery=1
#--------------------------
#slave parameter
#--------------------------
#relay_log = /mysql/log/3306/relaylog/llldb-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

--重启数据库

systemctl restart mysqld

3、从库配置-192.168.21.152/153/154/155(slave)

注意：如下配置直接贴到配置文件最后即可。文档是后面的配置覆盖前面的数据，最终赋值的参数生效。

vi /mysql/data/3306/my.cnf

##################################replication Settings##################################
########################################################################################
########################################################################################
#slave add parameter
log_bin=/mysql/log/3306/binlog/llldb-binlog
log_bin_index=/mysql/log/3306/binlog/llldb-binlog.index
binlog_format=row
binlog_rows_query_log_events=on
#slave modify parameter
bind-address=192.168.21.152/153/154/155
server_id=523306
skip_name_resolve = on
expire_logs_days = 7
innodb_support_xa =1
binlog_cache_size = 1M
max_binlog_size = 2048M
log_bin_trust_function_creators = 1
innodb_flush_log_at_trx_commit =1
sync_binlog = 1
transaction-isolation = read-committed
gtid_mode = on
enforce_gtid_consistency = 1
log-slave-updates = 1
binlog_gtid_simple_recovery=1
#------------------------------------
#slave parameter
#------------------------------------
relay_log = /mysql/log/3306/relaylog/llldb-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

--重启数据库

systemctl restart mysqld

4、主库创建测试数据

mysql -uroot -prootroot
create database llldb1;
create table llldb1.testbak11 (id int,name varchar(40));
insert into llldb1.testbak11 values(1,'LLL0111'),(2,'LLL0112'),(3,'LLL0113'),(4,'LLL0114'),(5,'LLL0115');
commit;
select * from llldb1.testbak11;
exit;

5、主库备份

xtrabackup --defaults-file=/mysql/data/3306/my.cnf --user=root --password=rootroot --backup --stream=tar --parallel=2 --target-dir=/mysql/backup/fullback20231221 | gzip >/mysql/backup/fullback20231221.tar.gz

6、备库恢复(192.168.21.152-157)

（1）传输文档到备库

scp -r 192.168.21.151:/mysql/backup/fullback20231221.tar.gz /mysql/backup/

（2）解压备份

cd /mysql/backup
mkdir -p /mysql/backup/fullback20231221
tar zxvf /mysql/backup/fullback20231221.tar.gz -C /mysql/backup/fullback20231221

（3）恢复数据一致性

xtrabackup --defaults-file=/mysql/data/3306/my.cnf --prepare --target-dir=/mysql/backup/fullback20231221 --parallel=2

（4）备库停库，并删除当前data目录

systemctl stop mysqld
cd /mysql/data/3306/
rm -rf data_bak
mv data data_bak
mkdir data
chown -R mysql:mysql data

（5）还原备库

rsync -avrP /mysql/backup/fullback20231221/* --exclude='xtrabackup_*' /mysql/data/3306/data/

（6）授权备库数据目录

chown -R mysql:mysql data

（7）启动数据库

systemctl start mysqld

7、测试数据

mysql -uroot -prootroot
show databases;
select * from llldb1.testbak11 limit 2;

8、在从库（152-157）上，将slave与master建立连接，从而实现同步

从xtrabackup拷贝过来的数据，不能直接同步，需要手动指定终点。

（1）查看xtrabackup_info

 less /mysql/backup/fullback20231221/xtrabackup_binlog_info

（2）查看备份的文件xtrabackup_info

binlog_pos = filename 'llldb-binlog.000002', position '960', GTID of the last change '70084d57-9f9b-11ee-b8e5-00505687a0b7:1-3'

（3）查看主库状态的同步点

mysql> show master status \G;

（4）查看备库状态的同步点

mysql> show slave status \G

--查看从库状态

如果从库状态为空，则不需要执行下述命令
如果从库状态不为空，则需要执行下述命令

stop slave;
reset master;
reset slave;

（5）备库跳过1-3状态及之前的同步点

mysql -uroot -prootroot
mysql>
set @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
set @@SESSION.SQL_LOG_BIN= 0;
set @@GLOBAL.GTID_PURGED='70084d57-9f9b-11ee-b8e5-00505687a0b7:1-3'; --这个事务已经做过了，需要跳过
set @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;

（6）备库执行同步

mysql>change master to
master_host='192.168.21.151',
master_port=3306,
master_user='repuser',
master_password='repuser123',
master_auto_position=1; --GTID下，无需指定具体的postion.

（7）启动从库并查看状态

start slave;
show slave status \G

9、测试数据

（1）主库新创建数据库及用户

create database llldb2;
create table llldb2.testbak21 (id int,name varchar(40));
insert into llldb2.testbak21 values (1,'test0211'),(2,'test0212'),(3,'test0213'),(4,'test0214'),(5,'test0215');
commit;
select * from llldb2.testbak21;

（2）主库查看从库信息

（3）备库测试

mysql -uroot -prootroot
select * from llldb2.testbak21;

（4）备库信息

show slave status \G
show processlist;

本作品采用知识共享署名 4.0 国际许可协议进行许可