xtrabackup-8.0.27-做mysql主从

Xiyun@

已于 2023-09-02 11:50:12 修改

阅读量3.1k

点赞数

文章标签： mysql

于 2023-09-02 11:47:47 首次发布

本文链接：https://blog.csdn.net/weixin_38554915/article/details/132636349

版权

10.MySQL 真正的生产高可用复制环境 4-基于 GTID 无损同步项目（Xtrabackup+GTID+无损同步复制技术）（这种项目用得比较多，5.7必用）

10.1 项目需求

2.项目技术
操作系统:两台RHEL/CentOS/OEL Linux 7.5
数据库版本:MySQL 8.0.23
数据库架构:主从复制，基于主库搭建一个从库，采用Xtrabackup + GTID + 无损同步复制技术。
主库IP:192.168.1.51 3306
从库IP:192.168.1.52 3306

10.2 数据库环境准备


mysql -uroot -proot < /soft/xiyundb.sql

10.3 配置启动主从复制

# 1.主从库创建复制用户并授权
mysql -uroot -proot
create user 'repuser'@'%' identified by 'repuser123';
grant replication slave on *.* to 'repuser'@'%';
flush privileges;
select user,host from mysql.user;
exit;

#2.修改主从配置文件(my.cnf),如果涉及到切换，如果相同，则注释，建议主从都加相关参数;
## 192.168.1.51(master)
vi /mysql/data/fof/my.cnf
#master add parameter
log_bin=//mysql/data/fof/log/xiyundb-binlog
log_bin_index=//mysql/data/fof/log/xiyundb-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
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/data/fof/log/xiyundb-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

## 192.168.1.52(slave)
vi /mysql/data/fof/my.cnf
#slave add parameter
log_bin=/mysql/data/fof/log/xiyundb-binlog
log_bin_index=/mysql/data/fof/log/xiyundb-binlog.index
binlog_format=row
binlog_rows_query_log_events=on
#slave modify parameter
bind-address=192.168.1.52
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/data/fof/log/xiyundb-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 -proot
create database xiyundb1;
create table xiyundb1.xiyunbak11 (id int,name varchar(40)) ENGINE=MyISAM;
insert into xiyundb1.xiyunbak11 values(1,'xiyun111'),(2,'xiyun112'),(3,'xiyun113'),(4,'xiyun114'),(5,'xiyun115');
commit;
select * from xiyundb1.xiyunbak11;

# 4.主库备份并恢复到从库（使用xtrabackup）:
[mysql@mysql80-1-20 dumpdir]$ which xtrabackup
/mysql/soft/xtrabackup/bin/xtrabackup
[mysql@mysql80-1-20 dumpdir]$ xtrabackup -v
xtrabackup version 8.0.27-19 based on MySQL server 8.0.27 Linux (x86_64) (revision id: 50dbc8dadda)


# mysql用户执行
xtrabackup --defaults-file=/mysql/data/fof/my.cnf --user=root --password=root --backup --compress --compress-threads=2 --target-dir=/mysql/backup/fullback_20230901 

# 从库 停库
cd /mysql/data/fof/
mv data data_bak

# 主库
scp -rv /mysql/backup/fullback_20230901 192.168.1.52:/mysql/data/fof/data
chown mysql: -R /mysql/

# mysql用户执行 解压
xtrabackup --defaults-file=/mysql/data/fof/my.cnf --decompress --target-dir=/mysql/data/fof/data 
# 报错
sh: qpress: command not found
# 安装
tar -xvf qpress-11-linux-x64.tar
cp qpress /usr/bin/
chmod u+x /usr/bin/qpress

find /mysql/data/fof/data -name '*qp'|xargs rm -f
#
xtrabackup --defaults-file=/mysql/data/fof/my.cnf --prepare --target-dir=/mysql/data/fof/data --parallel=2


# 停库


# 启库
show databases;
select * from xiyundb.yg limit 2;

# 6.在从库上使slave与master建立连接，从而同步: 
mysql -uroot -proot
stop slave;
reset master;
reset slave;
mysql> show slave status \G
Empty set (0.00 sec)

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

# 备份的信息里面找下面的信息
[mysql@mysql80-1-21 data]$ cat xtrabackup_info|grep pos
binlog_pos = filename 'xiyundb-binlog.000004', position '196', GTID of the last change 'e9d9b390-cd5c-11ed-8ec6-000c298d6a6f:1-6'


# 二进制先关掉
set @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
set @@SESSION.SQL_LOG_BIN= 0;
# 要跳过的，去掉已经执行过的GTID
set @@GLOBAL.GTID_PURGED='e9d9b390-cd5c-11ed-8ec6-000c298d6a6f:1-6';
set @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;


mysql> set @@GLOBAL.GTID_PURGED='e9d9b390-cd5c-11ed-8ec6-000c298d6a6f:1-6';
ERROR 3546 (HY000): @@GLOBAL.GTID_PURGED cannot be changed: the added gtid set must not overlap with @@GLOBAL.GTID_EXECUTED

mysql> SELECT @@GLOBAL.GTID_EXECUTED;
+------------------------------------------+
| @@GLOBAL.GTID_EXECUTED                   |
+------------------------------------------+
| e9d9b390-cd5c-11ed-8ec6-000c298d6a6f:1-6 |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT @@GLOBAL.GTID_PURGED;
+----------------------+
| @@GLOBAL.GTID_PURGED |
+----------------------+
|                      |
+----------------------+
1 row in set (0.00 sec)


# gtid model
change master to
master_host='192.168.1.51',
master_port=3306,
master_user='repuser',
master_password='repuser123',
master_auto_position=1;
start slave;
show slave status \G


# 7.主从测试数据
create database xiyundb2;
create table xiyundb2.xiyunbak21 (id int,name varchar(40));
insert into xiyundb2.xiyunbak21 values(1,'xiyun211'),(2,'xiyun212'),(3,'xiyun213'),(4,'xiyun214'),(5,'xiyun215');
commit;

select * from xiyundb2.xiyunbak21;
mysql -uroot -proot
show slave status \G
show processlist;
#
create database xiyundb3;
create table xiyundb3.xiyunbak31 (id int,name varchar(40));
insert into xiyundb3.xiyunbak31 values(1,'xiyun311'),(3,'xiyun313'),(3,'xiyun313'),(4,'xiyun314'),(5,'xiyun315');
commit;

select * from xiyundb3.xiyunbak31;

10.4 正常的主从切换测试

10.4.1 主从切换

# 01.主从都要开二进制日志，切换的时候从库的数据是最新的。
# 在业务低峰期，将主库51设置为只读，让slave同步完数据：
set global read_only=1;
flush logs;   # 相当于新的日志
show master status;

# 02.确保从库52，已经更新完成所有的中继日志：
show slave status\G
or:
select master_pos_wait('xiyundb-binlog.000002',194); # 用主库的文件在备库上查询，差距为0。
show processlist;
stop slave;
reset master;
reset slave;
show master status;

# 03.上面可以保证主从的数据都是一致
# 如果从库52上没有复制帐号，需要创建.

# 04.主库51上。
reset master;
reset slave;
show master status;

# 05.主库 51：
cp /mysql/data/fof/my.cnf /mysql/data/fof/my.cnf.master
vi /mysql/data/fof/my.cnf

# 从库 52：
cp /mysql/data/fof/my.cnf /mysql/data/fof/my.cnf.slave
vi /mysql/data/fof/my.cnf

主从库 51/52:
systemctl restart mysqld

# 06. 从库 51：
mysql -uroot -proot
change master to
master_host='192.168.1.52',
master_user='repuser',
master_password='repuser123',
master_auto_position=1;
# 
start slave;
show slave status\G

#07. 新主库 52：
mysql -uroot -proot
create user 'xiyun02'@'%' identified by 'xiyun02';
grant all privileges on xiyundb2.* to 'xiyun02'@'%';
flush privileges;
exit;

mysql -uxiyun02 -pxiyun02
insert into xiyundb2.xiyunbak21 values (7,'xiyun227');
commit;
select * from xiyundb2.xiyunbak21;

# 新备库 51 检查：
mysql -uxiyun02 -pxiyun02
select * from xiyundb2.xiyunbak21;
insert into xiyundb2.xiyunbak21 values (7,'xiyun227');

10.4.2 主从回切

# 主库 52：
mysql -uroot -proot
set global read_only=1;
flush logs;
show master status;
# 从库 51：
mysql -uroot -proot
show slave status\G
or:
select master_pos_wait('xiyundb-binlog.000003',194); # 用主库的文件在备库上查询，差距为 0。
show processlist;
stop slave;
reset master;
reset slave;
show master status;
# 主库 52 上。
reset master;
reset slave;
show master status;

# 05. 主库 52：
cp /mysql/data/fof/my.cnf /mysql/data/fof/my.cnf.master
cp /mysql/data/fof/my.cnf.slave /mysql/data/fof/my.cnf
more /mysql/data/fof/my.cnf
从库 51：
cp /mysql/data/fof/my.cnf /mysql/data/fof/my.cnf.slave
cp /mysql/data/fof/my.cnf.master /mysql/data/fof/my.cnf
more /mysql/data/fof/my.cnf
主从库 51/52:
systemctl restart mysqld

# 06. 从库 52：
mysql -uroot -proot
change master to
master_host='192.168.1.51',
master_user='repuser',
master_password='repuser123',
master_auto_position=1;
start slave;
show slave status\G

# 07.数据测试
## 新主库 51：
mysql -uroot -proot
create user 'xiyun03'@'%' identified by 'xiyun03';
grant all privileges on xiyundb2.* to 'xiyun03'@'%';
flush privileges;
exit;
mysql -uxiyun03 -pxiyun03
insert into xiyundb2.xiyunbak21 values (8,'xiyun228');
commit;
select * from xiyundb2.xiyunbak21;
## 新备库 52 检查：
mysql -uxiyun03 -pxiyun03
select * from xiyundb2.xiyunbak21;
insert into xiyundb2.xiyunbak21 values (8,'xiyun228');
show slave status\G