[大数据]mysql主从复制(异步复制、半同步复制、GTID复制）

环境说明(安装好mysql8.0.30后打快照mysql-ok)

三台配置? /etc/hosts

192.168.3.161? ? stu1

192.168.3.162? ? stu2

192.168.3.163? ?stu3

三台设置防火墙

firewall-cmd --add-port=3306/tcp  --permanent
firewall-cmd --reload

一、基于binlog异步复制

主：192.168.3.161? stu1

从： 192.168.3.162? ? stu2? ?192.168.3.163? ?stu3

stu1:

1、增加数据

mysql
create database testdb;
use testdb
DROP TABLE IF EXISTS `tb1`;
 
CREATE TABLE `tb1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `dep_id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `salary` decimal(10,2) DEFAULT NULL,
  `cus_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=109 DEFAULT CHARSET=utf8;
 
INSERT INTO `tb1` VALUES ('1', '鲁班', '1', '10', '1000.00', '1');
INSERT INTO `tb1` VALUES ('2', '后裔', '1', '20', '2000.00', '1');
INSERT INTO `tb1` VALUES ('3', '孙尚香', '1', '20', '2500.00', '1');
INSERT INTO `tb1` VALUES ('4', '凯', '4', '20', '3000.00', '1');
INSERT INTO `tb1` VALUES ('5', '典韦', '4', '40', '3500.00', '2');
INSERT INTO `tb1` VALUES ('6', '貂蝉', '6', '20', '5000.00', '1');
INSERT INTO `tb1` VALUES ('7', '孙膑', '6', '50', '5000.00', '1');
INSERT INTO `tb1` VALUES ('8', '蔡文姬', '30', '35', '4000.00', '1');
 
select * from tb1;

2、数据备份

mkdir /backup
mkdir /backup-dir
mysqlbackup -uroot -pAdmin@123 --backup-image=/backup/fulldb.mbi --backup-dir=/backup-dir backup-to-image

3、在备份之后新增数据至主库

mysql
use testdb
DROP TABLE IF EXISTS `tb2`;
CREATE TABLE `tb2` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `tb2` VALUES ('1', 'zs');

select * from tb2;

4、拷贝fulldb.mbi至? stu2? stu3 并在两台从库上恢复数据

?stu2:

mkdir /backup
scp stu1:/backup/fulldb.mbi /backup

systemctl stop mysqld
rm -rf /var/lib/mysql/*
mysqlbackup --backup-image=/backup/fulldb.mbi --backup-dir=/tmp/backup copy-back-and-apply-log
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld

mysql
use testdb
select *  from tb1;

5、修改stu1配置文件，并创建用户

vim /etc/my.cnf
在[mysqld]下加入
server_id=161

systemctl restart mysqld

创建同步用户

mysql
set sql_log_bin=0;
create user repl@'192.168.3.%' identified by 'Admin@123';
grant replication slave on *.* to repl@'192.168.3.%';
set sql_log_bin=1;

6、修改stu2? ?stu3配置文件，并配置主从

stu1:

cat /backup-dir/meta/backup_variables.txt 

stu2:

vim /etc/my.cnf
在[mysqld]下加入
server_id=162
skip_slave_start

systemctl restart mysqld

mysql
change master to
master_host='stu1',
master_user='repl',
master_password='Admin@123',
master_ssl=1,
master_log_file='binlog.000002',
master_log_pos=3341;

start slave;

注：

master_log_file='binlog.000002',? ?查看备份时的binlog
master_log_pos=3341;? ? ? ? ? ? ? ? ??查看备份时的binlog

stu3:

vim /etc/my.cnf
在[mysqld]下加入
server_id=163
skip_slave_start

systemctl restart mysqld

mysql
change master to
master_host='stu1',
master_user='repl',
master_password='Admin@123',
master_ssl=1,
master_log_file='binlog.000002',
master_log_pos=3341;

start slave;

注：

master_log_file='binlog.000002',? ?查看备份时的binlog
master_log_pos=3341;? ? ? ? ? ? ? ? ??查看备份时的binlog

在stu2、stu3? 上去掉? ?/etc/my.cnf? 中? ?skip_slave_start? 重启mysqld

systemctl restart mysqld

7、验证

stu1:

mysql
show master status;
use testdb
create table tb3(id int,name char);
insert into tb2 values ('2','cz');
insert into tb3 values ('1','a');

show processlist;

stu2? stu3:

mysql
show slave status \G;
use testdb
select * from tb2;
select * from tb3;

?注：

show slave status 信息

Master_Log_File: binlog.000003? ?正在读取的master binlog

Read_Master_Log_Pos: 936? ? ?I/O线程已读取master binlog的位置

Relay_Log_File: stu2-relay-bin.000004? ?sql线程正在读取和执行的relay log

Relay_Log_Pos: 1146? ? ? ? ??sql线程已读取relay log的位置

Relay_Master_Log_File: binlog.000003? ? ?sql线程最近执行的操作所对应的master? binlog

Exec_Master_Log_Pos: 936? ?sql线程最近执行的操作所对应的master? binlog的位置

binlog同步缺点：

1、只能slave同步master

2、若master二进制日志临时关闭，并创建表，开启二进制日志后，slave报错

3、不能保证数据实时一致，无法控制从库的延迟时间

二、mysql半同步复制

在搭建好异步复制环境后（binlog)

检查变量

mysql
show variables like 'have_dynamic_loading';

变量必需为yes，若为NO,设置变量为yes?

检查插件

rpm -ql mysql-commercial-server | grep semisync

?1、安装插件(操作系统需安装libimf)

stu1:

mysql
install plugin rpl_semi_sync_master soname 'semisync_master.so';

stu2? stu3:

mysql
install plugin rpl_semi_sync_slave soname 'semisync_slave.so';

验证插件安装

select plugin_name,plugin_status from information_schema.plugins where plugin_name like '%sem%';

stu1:

?stu2? ?stu3

?stu1:

show variables like '%sem%';

rpl_semi_sync_master_enabled? ? ? 是否启用半同步复制? ?默认为OFF

rpl_semi_sync_master_timeout? ? ? ?等待从库的ACK回复时间，默认为10S

rpl_semi_sync_master_trace_level? ?半同步复制时主库的调试级别

rpl_semi_sync_master_wait_for_slave_count? ? ?主库在超时时间内收到多少个ACK回复才认为此次提交成功，否则降为异步复制

rpl_semi_sync_master_wait_no_slave? ?默认为ON，降级为异步复制后为OFF

2、启用半同步复制

stu1

set global rpl_semi_sync_master_enabled = 1;
show variables like '%sem%';

stu2? ?stu3?

set global rpl_semi_sync_slave_enabled = 1;
show variables like '%sem%';

或写入配置文件

stu1:

vim? ?/etc/my.cnf

plugin-load="rpl_semi_sync_master=semisync_master.so"?
rpl_semi_sync_master_enable=1

stu2? ?stu3

vim? ?/etc/my.cnf

plugin-load="rpl_semi_sync_slave=semisync_slave.so"?
rpl_semi_sync_slave_enable=1

3、重启从库I/O线程

stu2? stu3:

mysql
stop slave io_thread;
start slave io_thread;

4、验证

stu1:

show status like 'Rpl_semi_sync_master_status';
show status like 'rpl_semi_sync%';

Rpl_semi_sync_master_status? ?为? on? 时表示为半同步复制? ? off时表示为异步复制

Rpl_semi_sync_master_clients? ? 当前连接半同步从库个数

Rpl_semi_sync_master_net_waits? ?主库等待从库回复总次数

Rpl_semi_sync_master_no_times? 主库关闭半同步复制的次数

Rpl_semi_sync_master_no_tx? ? 从库未确认的事务数

Rpl_semi_sync_master_tx_waits? 主库等待事务的总次数

Rpl_semi_sync_master_wait_sessions? ?当前等待从库回复的会话数

Rpl_semi_sync_master_yes_tx? ?从库成功确认的事务数

stu2? stu3?

show status like 'Rpl_semi_sync_slave_status';

?5、测试

1)正常提交事务

mysql
use testdb
insert into tb3 values ('2','b');
select * from tb3;

show status like 'rpl_semi_sync%';

2）回滚事务

set session autocommit=0;
insert into tb3 values ('3','c');
create table tb4 (id int) engine=myisam;
insert into tb3 values ('4','d');
insert into tb4 values (1);
rollback;

show warnings;
select * from tb3;
select * from tb4;
show status like 'rpl_semi_sync%';

?stu2? ?stu3

show variables like 'autocommit';
select * from tb3;
select * from tb4;

?从测试可得出结论

1、set session autocommit=0;? 不被复制

2、DDL语句会自动触发commit，自动提交DDL语句本身及之前未提交的事务

3、myisam? 非事务表不能回滚

?stu2? ?stu3关闭从库slave

stop slave;

stu1:

set session autocommit=1;
insert into tb3 values ('5','e');

show status like 'rpl_semi_sync%';

等待10秒后正确提交

??stu2? ?stu3开启从库slave

start slave;
select * from testdb.tb3;

?stu1:? 恢复正常

show status like 'rpl_semi_sync%';

?注：

1、主库启用半同步复制，主库上执行事务提交的线程将等待，直到至少一个半同步从库确认收到事务的所有事件（此时会向主库发送ACK确认信息），或者直到发生超时才提交事务。

2、只有将事件写入中继日志并刷新到磁盘后，从库才会确认收到事务的事件，即向主库发送ACK

3、在没有任何从库确认事务发生超时，则主库退化为异步复制。当至少有一半同步从库正常时，主库恢复同步复制。

4、主库和从库都启用半同步复制，否则使用异步复制

二、GTID的基本操作

1、验证自动跳过（还原快照至mysql-ok）

vim /etc/my.cnf
在[mysqld]下填加
server_id=161
sync_binlog=1
gtid_mode=on
enforce_gtid_consistency=on

systemctl restart mysqld

1）准备数据

mysql
create database testdb;
use testdb
DROP TABLE IF EXISTS `tb1`;
 
CREATE TABLE `tb1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `dep_id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `salary` decimal(10,2) DEFAULT NULL,
  `cus_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=109 DEFAULT CHARSET=utf8;
 
INSERT INTO `tb1` VALUES ('1', '鲁班', '1', '10', '1000.00', '1');
INSERT INTO `tb1` VALUES ('2', '后裔', '1', '20', '2000.00', '1');
INSERT INTO `tb1` VALUES ('3', '孙尚香', '1', '20', '2500.00', '1');
INSERT INTO `tb1` VALUES ('4', '凯', '4', '20', '3000.00', '1');
INSERT INTO `tb1` VALUES ('5', '典韦', '4', '40', '3500.00', '2');
INSERT INTO `tb1` VALUES ('6', '貂蝉', '6', '20', '5000.00', '1');
INSERT INTO `tb1` VALUES ('7', '孙膑', '6', '50', '5000.00', '1');
INSERT INTO `tb1` VALUES ('8', '蔡文姬', '30', '35', '4000.00', '1');
 
select * from tb1;

CREATE TABLE `tb2` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `tb2` VALUES ('1', 'zs');

select * from tb2;

2）查看当前GTID

show master status \G

?3)将GTID设置为执行过的值，再执行事务

set gtid_next = 'fbf074fe-342d-11ed-a330-000c295bd558:17';
truncate table tb2;
select * from tb2;

?truncate table tb2;? 语句未报错，但未执行，数据无变化

4）设置gtid为新值

show master status \G

set gtid_next='fbf074fe-342d-11ed-a330-000c295bd558:18';begin;update tb1 set salary=3080 where id=1;select sleep(10);commit;set gtid_next=automatic;

?开启另一个终端在10秒内执行

set gtid_next='fbf074fe-342d-11ed-a330-000c295bd558:18';begin;insert into tb2 values('3','wk');commit;set gtid_next=automatic;

?发现语句2未执行

三、基于GTID复制同步

环境同上，还原快照为mysql-ok

?三台配置? /etc/hosts

192.168.3.161? ? stu1

192.168.3.162? ? stu2

192.168.3.163? ?stu3

三台设置防火墙

firewall-cmd --add-port=3306/tcp  --permanent
firewall-cmd --reload

主：192.168.3.161? stu1

从： 192.168.3.162? ? stu2? ?192.168.3.163? ?stu3

stu1:

1、增加数据

mysql
create database testdb;
use testdb
DROP TABLE IF EXISTS `tb1`;
 
CREATE TABLE `tb1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `dep_id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `salary` decimal(10,2) DEFAULT NULL,
  `cus_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=109 DEFAULT CHARSET=utf8;
 
INSERT INTO `tb1` VALUES ('1', '鲁班', '1', '10', '1000.00', '1');
INSERT INTO `tb1` VALUES ('2', '后裔', '1', '20', '2000.00', '1');
INSERT INTO `tb1` VALUES ('3', '孙尚香', '1', '20', '2500.00', '1');
INSERT INTO `tb1` VALUES ('4', '凯', '4', '20', '3000.00', '1');
INSERT INTO `tb1` VALUES ('5', '典韦', '4', '40', '3500.00', '2');
INSERT INTO `tb1` VALUES ('6', '貂蝉', '6', '20', '5000.00', '1');
INSERT INTO `tb1` VALUES ('7', '孙膑', '6', '50', '5000.00', '1');
INSERT INTO `tb1` VALUES ('8', '蔡文姬', '30', '35', '4000.00', '1');
 
select * from tb1;

?2、数据备份

mkdir /backup
mkdir /backup-dir
mysqlbackup -uroot -pAdmin@123 --backup-image=/backup/fulldb.mbi --backup-dir=/backup-dir backup-to-image

3、拷贝fulldb.mbi至? stu2? stu3 并在两台从库上恢复数据

?stu2:

mkdir /backup
scp stu1:/backup/fulldb.mbi /backup

systemctl stop mysqld
rm -rf /var/lib/mysql/*
mysqlbackup --backup-image=/backup/fulldb.mbi --backup-dir=/tmp/backup copy-back-and-apply-log
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld

mysql
use testdb
select *  from tb1;

4、修改stu1配置文件，并创建用户

vim /etc/my.cnf
在[mysqld]下加入
server_id=161
sync_binlog=1
gtid_mode=on
enforce_gtid_consistency=on

systemctl restart mysqld

创建用户

mysql
set sql_log_bin=0;
create user repl@'192.168.3.%' identified by 'Admin@123';
grant replication slave on *.* to repl@'192.168.3.%';
set sql_log_bin=1;

5、增加新数据至主库

use testdb
DROP TABLE IF EXISTS `tb2`;
CREATE TABLE `tb2` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `tb2` VALUES ('1', 'zs');

select * from tb2;

6、修改SLAVE

stu2:

vim /etc/my.cnf
在[mysqld]下加入
server_id=162
gtid_mode=on
enforce_gtid_consistency=on
skip_slave_start

systemctl restart mysqld

stu3:

vim /etc/my.cnf
在[mysqld]下加入
server_id=163
gtid_mode=on
enforce_gtid_consistency=on
skip_slave_start

systemctl restart mysqld

stu2? ? ? stu3:?

mysql
change master to
master_host='stu1',
master_user='repl',
master_password='Admin@123',
master_ssl=1,
master_auto_position=1;

start slave;

在stu2、stu3? 上去掉? ?/etc/my.cnf? 中? ?skip_slave_start? 重启mysqld

systemctl restart mysqld

7、验证

stu1:

create table tb3(id int,name varchar(30));
insert into tb3 values('1','lc');
show processlist;
show variables like 'gtid_executed';
select * from tb2;
select * from tb3;

?stu2? ?stu3:

show variables like 'gtid_executed';
select * from tb2;
select * from tb3;

?

8、延迟复制

stu3: (只在stu3上配置延迟复制）

mysql
stop slave sql_thread;
change master to master_delay=60;
start slave sql_thread;

stu1:

mysql
use testdb
insert into tb3 values('2','cl');
select * from tb3;

stu2:? ?立即同步

mysql
use testdb
select * from tb3;

stu3:

mysql
use testdb
select * from tb3;

60秒后查看

?

select desired_delay from performance_schema.replication_applier_configuration;

恢复

stop slave sql_thread;
change master to master_delay=0;
start slave sql_thread;

9、部分复制

stu3:

stop slave sql_thread;
change replication filter replicate_wild_do_table=('testdb.%');
start slave sql_thread;

stu1:

use testdb
insert into tb3 values('3','cy');
use mysql
create database test;
use test
create table tb10(id int);
insert into tb10 values(1);
select * from test.tb10;

stu3:

select * from testdb.tb3;
select * from test.tb10;

只同步了testdb数据库中的表，未新建test数据库

stu2:?

select * from testdb.tb3;
select * from test.tb10;

?