MySQL主从复制【基于GTID复制】
MySQL主从复制【基于GTID复制】1、GTID复制简介2、GTID复制运行过程3、GTID复制开启参数4、示例1）环境准备2）初始化数据【重置所有数据（方便实验）】3）配置主库4）配置从库5）测试6）查看GTID1、GTID复制简介在主从同步时 GTID_Event 和事务的 Binlog 会一起传递到从库，由中继日志接收，从库在执行的时候使用对应的 GTID 写 binlog；主从同步以后，
·
MySQL主从复制【基于GTID复制】
1、GTID复制简介
在主从同步时 GTID_Event 和事务的 Binlog 会一起传递到从库，由中继日志接收，从库在执行的时候使用对应的 GTID 写 binlog；主从同步以后，可以通过 GTID 确定从库目前同步的位置了。
【简单来说：可以通过 GTID 自动找点，无需像之前那样通过 binlog 名 和 position 号找点】
2、GTID复制运行过程
1、master 更新数据时，会在事务前产生 GTID 并一同记录到 binlog 日志中；
2、slave 端的 IO 线程将变更的 binlog写入到本地的 relay-log（中继日志）中；
3、sql 线程从 relay-log 中获取对应的 GTID，对比 slave 端的 binlog 的记录 ；
4、如果有记录，说明该 GTID 的事务已执行，slave 会忽略该 GTID；
5、如果没有记录，slave 会从 relay-log 中执行该 GTID 的事务，并记录到 binlog 中；
3、GTID复制开启参数
//在MySQL配置文件中添加
[mysqld]
gtid-mode=on //启用GTID
enforce-gtid-consistency=true //强制GTID的一致性
log-slave-updates=1 //slave更新是否记入日志（1表示记入、0表示不记入）
4、示例
1）环境准备
三台主机：一主、两从
主库（MySQL master）[ip为192.168.25.131]
从库（MySQL slave1）[ip为192.168.25.133]
从库（MySQL slave2）[ip为192.168.25.134]
2）初始化数据【重置所有数据（方便实验）】
//清空MySQL数据目录，否则无法初始化
[root@localhost ~]# rm -rf /var/lib/mysql/\*
//上方语句末尾\*表示将*注释，否则CSDN会视为注释，复制至Linux系统需要删除“\”
[root@localhost ~]# mysqld --initialize-insecure --user=mysql --basedir=/usr --datadir=/var/lib/mysql/
3）配置主库
[root@localhost ~]# systemctl stop firewalld //关闭防火墙，否则主从无法建立连接（有能力者设定防火墙规则放行）
[root@localhost ~]# vim /etc/my.cnf //修改配置文件（开启binlog参数、设置server-id值）
[mysqld]
log_bin=/var/lib/mysql/mysql-bin //bin_log指定文件名和文件路径
server_id=131 //MySQL5.5版本后要开启bin_log必须给定一个唯一的服务器id（一般为IPV4地址主机位）
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt="db01 [\\d] > "
[root@localhost ~]# systemctl restart mysqld
db01 [(none)] > grant replication slave on *.* to "rep"@"192.168.25.%" identified by '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)
db01 [(none)] > show grants for "rep"@"192.168.25.%";
+--------------------------------------------------------+
| Grants for rep@192.168.25.% |
+--------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'rep'@'192.168.25.%' |
+--------------------------------------------------------+
1 row in set (0.00 sec)
4）配置从库
db02 [(none)] > change master to
-> master_host="192.168.25.131", #主库IP
-> master_user="rep", #同步账号
-> master_password="123456", #同步账号的密码
-> master_auto_position=1; #自动 position 号（偏移值）【不用填写binlog & position】
Query OK, 0 rows affected, 2 warnings (0.01 sec)
db02 [(none)] > system systemctl stop firewalld #关闭防火墙
db03 [(none)] > change master to
-> master_host="192.168.25.131",
-> master_user="rep",
-> master_password="123456",
-> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
db02 [(none)] > system systemctl stop firewalld #关闭防火墙
#启动从库同步开关
db02 [(none)] > start slave;
Query OK, 0 rows affected (0.00 sec)
db02 [(none)] > show slave status\G #查看结果
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.25.131
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 707
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 920
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes #此处必须为yes（表示成功）
Slave_SQL_Running: Yes #此处必须为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: 707
Relay_Log_Space: 1131
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: 131
Master_UUID: 4a4ec55f-d078-11ec-b9e8-000c29c1f77e
Master_Info_File: /var/lib/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: 4a4ec55f-d078-11ec-b9e8-000c29c1f77e:1-2 //表示收到的事务（重点）
Executed_Gtid_Set: 4a4ec55f-d078-11ec-b9e8-000c29c1f77e:1-2,
594ea1be-d078-11ec-96d7-000c2967ad99:1 //表示已执行的事务（重点）
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
db03 [(none)] > start slave;
Query OK, 0 rows affected (0.00 sec)
db03 [(none)] > show slave status\G #查看结果
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.25.131
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 707
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 920
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes #此处必须为yes（表示成功）
Slave_SQL_Running: Yes #此处必须为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: 707
Relay_Log_Space: 1131
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: 131
Master_UUID: 4a4ec55f-d078-11ec-b9e8-000c29c1f77e
Master_Info_File: /var/lib/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: 4a4ec55f-d078-11ec-b9e8-000c29c1f77e:1-2 //表示收到的事务（重点）
Executed_Gtid_Set: 4a4ec55f-d078-11ec-b9e8-000c29c1f77e:1-2,
5fe99a5e-d078-11ec-956b-000c294d6b8d:1 //表示已执行的事务（重点）
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
5）测试
db01 [(none)] > create database test;
Query OK, 1 row affected (0.00 sec)
db02 [(none)] > show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
db03 [(none)] > show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
db01 [test] > create table t1(id int);
Query OK, 0 rows affected (0.01 sec)
db01 [test] > insert into t1 values(1),(2);
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
db02 [(none)] > use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
db02 [test] > select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
db03 [(none)] > use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
db03 [test] > select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
db01 [test] > drop database test;
Query OK, 1 row affected (0.00 sec)
db02 [test] > show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
db03 [test] > show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
通过上方测试，通过查看发现所有数据均已同步，主从复制成功
6）查看GTID
db01 [(none)] > show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000002 | 1443 | | | 4a4ec55f-d078-11ec-b9e8-000c29c1f77e:1-6 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
db02 [(none)] > show master status;
+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------+
| mysql-bin.000002 | 1678 | | | 4a4ec55f-d078-11ec-b9e8-000c29c1f77e:1-6,
594ea1be-d078-11ec-96d7-000c2967ad99:1 |
+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------+
1 row in set (0.00 sec)
db03 [(none)] > show master status;
+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------+
| mysql-bin.000002 | 1678 | | | 4a4ec55f-d078-11ec-b9e8-000c29c1f77e:1-6,
5fe99a5e-d078-11ec-956b-000c294d6b8d:1 |
+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------+
1 row in set (0.00 sec)
可以看出三台服务器的GTID是相同的，同时保留自身GTID
为开发者提供学习成长、分享交流、生态实践、资源工具等服务，帮助开发者快速成长。
更多推荐
2
0- 0
已为社区贡献4条内容
所有评论(0)