GTID 主从 log_slave_updates

转载

小蝌蚪 2024-07-16 12:22:35

一、GTID介绍

1、GTID复制：（global transaction id 全局事务标识符） MySQL5.6版本开始支持，GTID复制不像传统的复制方式（异步复制、半同步复制）需要找到binlog和POS点，只需知道master的IP、端口、账号、密码即可。开启GTID后，执行change master to master_auto_postion=1即可，它会自动寻找同步
2、GTID作用
1)、Gtid采用了新的复制协议旧协议是，首先从服务器上在一个特定的偏移量位置连接到主服务器上一个给定的二进制日志文件，然后主服务器再从给定的连接点开始发送所有的事件。
2)、新协议有所不同，支持以全局统一事务ID (GTID)为基础的复制。当在主库上提交事务或者被从库应用时，可以定位和追踪每一个事务。GTID复制是全部以事务为基础，使得检查主从一致性变得非常简单。如果所有主库上提交的事务也同样提交到从库上，一致性就得到了保证。

3、GTID工作原理
①当一个事务在master执行并提交时，产生GTID，一同记录到binlog日志中。
②binlog传输到slave,并存储到slave的relaylog后，读取这个GTID的这个值设置gtid_next变量，即告诉Slave，下一个要执行的GTID值。
③sql线程从relay log中获取GTID，然后对比slave端的binlog是否有该GTID。
④如果有记录，说明该GTID的事务已经执行，slave会忽略。
⑤如果没有记录，slave就会执行该GTID事务，并记录该GTID到自身的binlog，在读取执行事务前会先检查其他session持有该GTID，确保不被重复执行。
⑥在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有就用全部扫描。

二、基于mysql 5.7的GTID复制

先实现master

[root@master ~]# tar xvf mysql-5.7.35-el7-x86_64.tar.gz -C /usr/local/ 2、创建用户和组
[root@master ~]# groupadd mysql[root@master ~]# useradd -r -s /bin/bash mysql mysql

3、配置环境变量
[root@master local]# mv mysql-5.7.35-el7-x86_64 mysql[root@master local]# echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh[root@master local]# . /etc/profile.d/mysql.sh[root@master local]# echo $PATH /usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

4、生成配置文件，密码
[root@master local]# mysqld --initialize --user=mysql --datadir=/data/mysql 2021-11-21T05:29:07.097551Z 1 [Note] A temporary password is generated for root@localhost: f!sBc-RC%4SD 登录密码为：f!sBc-RC%4SD

5、mysql配置文件
[root@master data]# vim /etc/my.cnf
[mysqld]
server-id=1
log-bin=/data/mysql_back/mysql_bin
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock

6、创建mysql_back目录并修改权限
[root@master ~]# mkdir /data/mysql_back/[root@master ~]# chown -R mysql.mysql /data[root@master ~]# chmod 2755 /data 2755:表示在该目录下创建文件将继承/data/

7、开机启动
[root@master ]# chkconfig --add mysqld

`[root@master ~]# chkconfig --list

mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off`

[root@master ]# service mysqld start[root@master ~]# ps axu | grep mysqld root 3591 0.0 0.0 11816 1608 pts/1 S 13:37 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/m
ysql.pidmysql 3777 0.0 10.3 1121220 169696 pts/1 Sl 13:37 0:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-d
ir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/mysql.log --pid-file=/data/mysql/mysql.pid --socket=/data/mysql/mysql.sockroot 3978 0.0 0.0 112704 972 pts/1 S+ 13:46 0:00 grep --color=auto mysqld

8、修改密码
[root@master ~]# mysqladmin -uroot -p'f!sBc-RC%4SD' password 123456 mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

9、登录
[root@master ~]# mysql -uroot -p123456mysql> create database xzcdc; mysql> create table xzcdc.xz(id int(10) NOT NULL PRIMARY KEY )

10、给用户授权
mysql> grant replication slave on *.* to xzcdc@'192.168.100.%' identified by '123456';

实现slave

1、创建用户组
[root@centos_9 ~]# groupadd mysql
[root@centos_9 ~]# useradd -r -s /bin/bash -g mysql mysql
2、解压
[root@centos_9 ~]# tar xvf mysql-5.7.35-el7-x86_64.tar.gz -C /usr/local/
[root@centos_9 local]# mv mysql-5.7.35-el7-x86_64/ mysql

3、配置环境变量
[root@centos_9 ~]# echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@centos_9 ~]# . /etc/profile.d/mysql.sh

4、生成数据库文件及密码
[root@centos_9 local]# mysqld --initialize --user=mysql --datadir=/data/mysql
codU/rEdv1xM
[root@centos_9 mysql]# ll
total 122960
-rw-r----- 1 mysql mysql 56 Nov 21 19:39 auto.cnf
-rw------- 1 mysql mysql 1680 Nov 21 19:39 ca-key.pem
-rw-r--r-- 1 mysql mysql 1112 Nov 21 19:39 ca.pem
-rw-r--r-- 1 mysql mysql 1112 Nov 21 19:39 client-cert.pem
-rw------- 1 mysql mysql 1680 Nov 21 19:39 client-key.pem
-rw-r----- 1 mysql mysql 436 Nov 21 19:39 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Nov 21 19:42 ibdata1
-rw-r----- 1 mysql mysql 50331648 Nov 21 19:42 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Nov 21 19:39 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Nov 21 19:42 ibtmp1
drwxr-x--- 2 mysql mysql 4096 Nov 21 19:39 mysql
-rw-r----- 1 mysql mysql 3773 Nov 21 19:42 mysql.log
-rw-r----- 1 mysql mysql 5 Nov 21 19:42 mysql.pid
srwxrwxrwx 1 mysql mysql 0 Nov 21 19:42 mysql.sock
-rw------- 1 mysql mysql 5 Nov 21 19:42 mysql.sock.lock

5、从库修改自己的配置文件
vim /etc/my.cnf [mysqld]
server-id=2
read-only
gtid_mode=ON
enforce_gtid_consistency
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock

6、启动脚本
[root@centos_9 local]# cp mysql/support-files/mysql.server /etc/init.d/mysqld

7、启动：
[root@centos_9 local]# service mysqld start
Starting MySQL.Logging to '/data/mysql/mysql.log'.
SUCCESS!

8、修改登录密码
[root@centos_9 mysql]# mysqladmin -uroot -p'codU/rEdv1xM' password 123456
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

三、复制实现

上面所有的步骤实现完成后，开始实现GTID复制，

2、创建一个数据库
mysql> create database xzcdc;
Query OK, 1 row affected (0.00 sec)

4、在slave进行复制
5、查看CHANGE MASTER TO用法
mysql> help CHANGE MASTER TO;
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.100.8',
-> MASTER_USER='xzcdc',
-> MASTER_PASSWORD='123456',
-> MASTER_PORT=3306,
-> MASTER_AUTO_POSITION=1; #副本尝试使用基于 GTID 的复制的自动定位功能而不是基于二进制日志文件的位置连接到源
Query OK, 0 rows affected, 2 warnings (0.01 sec)

在master上新建的库没有复制过来，那是因为还没有启动线程

注意：不能配置该值：skip-grant-tables(免密码登录)，如果配置将在GTID复制master库的时候报错如下：

mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.100.8
Master_User: xzcdc
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_bin.000003
Read_Master_Log_Pos: 194
Relay_Log_File: centos_9-relay-bin.000002
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql_bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1290
Last_Error: Error 'The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement' on query. Defa
ult database: ''. Query: 'ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'' Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 2221
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1290
Last_SQL_Error: Error 'The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement' on query. Defa
ult database: ''. Query: 'ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'' Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: f1dd566f-4a8b-11ec-8198-000c294ac8c3
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 211121 20:07:59
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: f1dd566f-4a8b-11ec-8198-000c294ac8c3:1-3
Executed_Gtid_Set:
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.100.8
Master_User: xzcdc
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_bin.000003
Read_Master_Log_Pos: 194
Relay_Log_File: centos_9-relay-bin.000006
Relay_Log_Pos: 407
Relay_Master_Log_File: mysql_bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 194
Relay_Log_Space: 657
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: f1dd566f-4a8b-11ec-8198-000c294ac8c3
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: f1dd566f-4a8b-11ec-8198-000c294ac8c3:1-3
Executed_Gtid_Set: f1dd566f-4a8b-11ec-8198-000c294ac8c3:1-3
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

ERROR:
No query specified

进行创建表，库的操作

mysql> use xzcdc;
Database changed
mysql> show tables;
Empty set (0.00 sec)

创建表
mysql> create table xz(id int(10) NOT NULL PRIMARY KEY, name VARCHAR(11));
Query OK, 0 rows affected (0.13 sec)

查看
mysql> show tables;
+-----------------+
| Tables_in_xzcdc |
+-----------------+
| xz |

查看表结构
mysql> desc xz;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(10) | NO | PRI | NULL | |
| name | varchar(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+

在从上查看该表是否同步过来
mysql> show tables;
+-----------------+
| Tables_in_xzcdc |
+-----------------+
| xz |
+-----------------+