mysql主从配置及搭建(gtid方式)_mysql gtid

一、搭建主从-gtid方式

搭建步骤查看第一篇。bin-log方式。可以进行搭建
1

1.1 gtid和二进制的优缺点

使用 GTID 的主从复制优点：
1、简化配置：使用 GTID 可以简化主从配置，不需要手动配置每个服务器的二进制日志文件和位置。
2、自动故障转移：GTID 可以在主从切换时自动识别和处理已复制和未复制的事务，使主从切换更加可靠和快速。
3、避免重复复制：GTID 可以避免主从复制中的重复复制问题，确保每个事务只被复制一次。

使用 GTID 的主从复制缺点：
1、兼容性：GTID 是从 MySQL 5.6 版本开始引入的特性，如果使用旧版本的 MySQL，无法使用 GTID 进行主从复制。
2、系统资源占用：启用 GTID 可能会增加一些系统资源的占用，包括存储和计算资源。

使用二进制日志的主从复制优点：
1、兼容性：二进制日志是 MySQL 自带的特性，从较早的版本开始就支持，可以在各个版本之间进行主从复制。
2、灵活性：使用二进制日志可以根据需要进行更精细的配置，如指定复制的数据库、表，过滤不需要复制的操作等。

使用二进制日志的主从复制缺点：
1、配置复杂：相对于 GTID，使用二进制日志需要手动配置主库和从库的二进制日志文件和位置，配置过程相对复杂。
2、容易出错：配置不当或操作失误可能导致主从复制出现问题，如数据不一致、延迟等。
3、复制延迟：在高写入负载的情况下，从库可能会有一定的复制延迟，导致从库数据相对于主库稍有滞后
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

1.2 配置master

1.#配置基础数据
mysql> create database master1db;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE `master1db`.`master1tab`( `id` INT(2) ZEROFILL NOT NULL AUTO_INCREMENT COMMENT '编号', `name` VARCHAR(255) NOT NULL COMMENT '公司名称', `location` VARCHAR(255) NOT NULL COMMENT '所在地区', PRIMARY KEY (`id`) ) CHARSET=utf8; 

mysql> INSERT INTO `master1db`.`master1tab` (`name`, `location`) VALUES ('强强科技集团', '中国-北京'); 
mysql> INSERT INTO `master1db`.`master1tab` (`name`, `location`) VALUES ('随梦科技集团', '中国-上海'); 
mysql> INSERT INTO `master1db`.`master1tab` (`name`, `location`) VALUES ('阿里巴巴', '中国-杭州');

2.#配置my.cnf文件
[root@mysql-master ~]# cat /etc/my.cnf | tail -n 4			#可以去掉log-bin和serverid只通过gtid的方式进行主从
log_bin=mysql-bin				#开启二进制日志
server-id=1						#用于表示mysql实例在集群中的标识符。必须唯一
gtid_mode=ON					#GTID 是一个全局唯一的事务标识符，用于标识数据库集群中的事务。启用 GTID 后，每个事务都会被分配一个唯一的 GTID。
enforce_gtid_consistency=1		#当启用 GTID 后，该选项用于强制要求从库只能复制具有与主库一致的 GTID 链的事务，以确保数据一致性。

[root@mysql-master ~]# systemctl restart mysqld				#重启mysql配置文件生效
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

1.3 创建复制用户

#master操作
mysql> grant replication slave, replication
    -> client on *.* to 'slave'@'192.168.17.%'   identified by 'SqlBack@123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
1
2
3
4

1.4 备份主库

[root@mysql-master ~]# mysqldump -uroot -p123456  master1db --single-transaction --master-data=2 --flush-logs  > `date +%F`-mysql-all.sql
#针对数据库进行备份
#因为是开启了gtid方式进行备份所有sql中没有Master_Log_File和pos位置
[root@mysql-master ~]# scp 2023-06-27-mysql-all.sql root@192.168.17.130:/root
1
2
3
4

1.5 配置slave

1.配置my.cnf配置文件
[root@mysql-slave ~]# cat /etc/my.cnf | tail -n 4
#log_bin=mysql-bin					#从库可以不开启二进制日志
server-id=2
gtid_mode=ON
enforce_gtid_consistency=1
2.重启mysqld
[root@mysql-slave ~]# systemctl restart mysqld
3.导入备份数据
mysql> set sql_log_bin=0;
mysql> create database master1db;			#因为是恢复单个库。所以需要提前创建好恢复的库
Query OK, 1 row affected (0.00 sec)
mysql> use master1db
mysql> source /root/2023-06-27-mysql-all.sql;		#恢复数据库

4.配置主从
mysql> change master to
    -> master_host='192.168.17.129',
    -> master_user='slave',
    -> master_password='SqlBack@123456',
    -> master_auto_position=1;							#自动获取二进制的位置
Query OK, 0 rows affected, 2 warnings (0.53 sec)
mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.17.129
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 457
               Relay_Log_File: mysql-slave-relay-bin.000002
                Relay_Log_Pos: 670
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
5.验证主从{主库操作}
mysql> INSERT INTO `master1db`.`master1tab` (`name`, `location`) VALUES ('字节跳动集团', '中国-北京'); 
mysql> select * from master1tab;
+----+--------------------+---------------+
| id | name               | location      |
+----+--------------------+---------------+
| 01 | 强强科技集团       | 中国-北京     |
| 02 | 随梦科技集团       | 中国-上海     |
| 03 | 阿里巴巴           | 中国-杭州     |
| 04 | 京东集团           | 中国-北京     |
| 05 | 字节跳动集团       | 中国-北京     |
+----+--------------------+---------------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

二、模拟主从不同步(gtid)

1.1 故障描述

#开始模拟：这里我们模拟一个主从复制架构中，从服务器中途异常宕机，不再同步主服务器的场景，假设该情况
#宕机时间较长，数据量较大，GTID无法短时间恢复，并要求不停业务进行数据同步修复，恢复一致
1
2

1.2 配置测试库

1、#master操作
mysql> create database demon;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE `demon`.`t1`( `id` INT(2) NOT NULL AUTO_INCREMENT, `name` VARCHAR(255) NOT NULL, `age` INT(255) NOT NULL, PRIMARY KEY (`id`) ); 

2、编写脚本实现重复插入数据
#!/usr/bin/bash
values=`find /usr/ -type d | awk -F '/' '{print $NF}' | sort -u`

while true
do
	age=$(($RANDOM % 100))		# random随机数
	name=$(echo "$values" | shuf -n 1)			#随机获取find查到的值
	mysql -uroot -p123456 -e "insert into demon.t1(name,age) values ('$name',$age)"		#插入到mysql中
	sleep $(($RANDOM%5))			#睡眠小于5s之内的数字
done

3、后台运行脚本模拟客户数据
[root@mysql-master ~]# nohup /bin/bash 1.sh &
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

1.3 模拟从库宕机

#从库操作
mysql> stop slave;						#手动停止mysql主从复制
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM demon.`t1`;
160 rows in set (0.00 sec)				#此时从库有160条数据。主库由于还在执行脚本,数据还在不断插入
1
2
3
4
5
6

1.4 恢复数据

#主库操作
思路：
先通过mysqldump全量备份当前的数据，由于不能影响业务，所以在mysqldump数据时不能造成锁表。要保持数据写入，由于mysqldump时数据还在写入，所以有一部分数据还是会同步不全，所以导入mysqldump的数据后，跳过dump中包含的GTID事务，再重新建立一次主从配置，开启slave线程，恢复数据并同步。

1、主库备份现在的数据。并且不锁表进行备份
mysql> SELECT * FROM demon.`t1`;
322 rows in set (0.00 sec)				#备份前有322条数据
[root@mysql-master ~]# mysqldump -uroot -p123456  demon --single-transaction --master-data=2 > `date +%F`-mysql-demon.sql
#备份测试库demon
#--master-data=2：生成的备份文件中包含用于主从复制的二进制日志文件名和位置的注释。值为 2 表示将 CHANGE MASTER TO 语句和位置信息添加到导出文件的注释中。
mysql> SELECT * FROM demon.`t1`;
428 rows in set (0.00 sec)					#备份后数据没有影响写入。存在428条记录。


2、从库恢复数据主库依然在写入数据
[root@mysql-master ~]# scp 2023-06-28-mysql-demon.sql root@192.168.17.130:/root				#把数据传给从库

#从库操作
mysql> use demon			#因为是备份的单个库需要先use到库
mysql> source /root/2023-06-28-mysql-demon.sql		#恢复数据	
#但是查看主从的结果还是不一致的。需要重新设定主从


source过程中会出现ERROR 1840 (HY000): @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
这种报错不用管,不会影响数据的写入。接着往下走。数据正常写入了就OK！！！！！
#这个问题是因为备份的时候可能加入了--flush-logs。不影响主从重做和数据写入
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

1.5 恢复主从

#从库操作
mysql> start slave;			#先直接开启主从看看
mysql> show slave status\G
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
               Last_SQL_Error: Could not execute Write_rows event on table demon.t1; Duplicate entry '161' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000002, end_log_pos 27863
#存在报错。需要重新配置主从


1、当前gtid_purged不为空，所以我们要先设置它为空
mysql> show global variables like '%gtid%';
+----------------------------------+--------------------------------------------+
| Variable_name                    | Value                                      |
+----------------------------------+--------------------------------------------+
| binlog_gtid_simple_recovery      | ON                                         |
| enforce_gtid_consistency         | ON                                         |
| gtid_executed                    | ee194423-1405-11ee-a122-000c29b3572a:1-163 |
| gtid_executed_compression_period | 1000                                       |
| gtid_mode                        | ON                                         |
| gtid_owned                       |                                            |
| gtid_purged                      | ee194423-1405-11ee-a122-000c29b3572a:1-163 |
| session_track_gtids              | OFF                                        |
+----------------------------------+--------------------------------------------+
mysql> reset master;					
#将gtid_purged参数设为空字符
#将gtid_executed设为空字符
#清空mysql.gtid_executed表
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like '%gtid%';
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| binlog_gtid_simple_recovery      | ON    |
| enforce_gtid_consistency         | ON    |
| gtid_executed                    |       |
| gtid_executed_compression_period | 1000  |
| gtid_mode                        | ON    |
| gtid_owned                       |       |
| gtid_purged                      |       |
| session_track_gtids              | OFF   |
+----------------------------------+-------+
8 rows in set (0.00 sec)

2、重置salve
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> reset slave all;
Query OK, 0 rows affected (0.01 sec)

3、查看主库当前mysqldump导出数据的GTID号
[root@mysql-slave ~]# grep GLOBAL.GTID 2023-06-28-mysql-demon.sql 
SET @@GLOBAL.GTID_PURGED='ee194423-1405-11ee-a122-000c29b3572a:1-401';


4、重置后，设置跳过的GTID，并重新同步MASTER
mysql> SET @@GLOBAL.GTID_PURGED='ee194423-1405-11ee-a122-000c29b3572a:1-401';
#从库将跳过指定范围内的 GTID，确保不会重复执行已经在主库上执行过的事务，从而避免数据的重复修改。这样，主从复制可以从当前的 GTID 位置继续进行
Query OK, 0 rows affected (0.00 sec)
mysql> change master to
    -> master_host='192.168.17.129',
    -> master_user='slave',
    -> master_password='SqlBack@123456',
    -> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

5、恢复主从线程
mysql> start salve;
mysql> show slave status\G
            Slave_IO_Running: Yes			#线程正常
            Slave_SQL_Running: Yes
            
6、查看数据是否正常
mysql> SELECT * FROM demon.`t1`;
595 rows in set (0.01 sec)				#数据和此时的主库条目一致。完美恢复
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76

1.6 清空主从配置

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)
mysql> reset slave all;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status;
Empty set (0.00 sec)
1
2
3
4
5
6
7
8