基于GTID的主从复制
什么是GTID
全局事务标识符GTID的全称为Global Transaction Identifier，是在整个复制环境中对一个事务的唯一标识。
它是MySQL 5.6加入的一个强大特性，目的在于能够实现主从自动定位和切换，而不像以前需要指定文件和位置。
使用GTID复制时，主库上提交事务时创建事务对应的GTID，从库在应用中继日志时用GTID识别和跟踪每个事务。在启动新从库或因故障转移到新主库时可以使用GTID来标识复制的位置，极大地简化了这些任务。 ---->
在新的主从切换的时候，新的从服务器知道哪些事务已经做了，哪些没有做。
-
1、全局唯一，一个事务对应一个GTID
-
2、替代传统的binlog+pos复制；使用master_auto_position=1自动匹配GTID断点进行复制
-
3、MySQL5.6开始支持
-
4、在传统的主从复制中，slave端不用开启binlog；但是在GTID主从复制中，必须开启binlog
-
5、slave端在接受master的binlog时，会校验GTID值
-
6、为了保证主从数据的一致性，多线程同时执行一个GTID
GTID长什么样子
ceb0ca3d-8366-11e8-ad2b-000c298b7c9a:1-4
前面是机器的uuid号
uuid号：每个MySQL实例的唯一编号
1-几是事务的范围
一个事务就是一个操作，或者多个操作
有个问题，从服务器什么时候产生二进制日志
当从服务器去sql线程去访问relay log，执行relay log的时候 会产生
工作原理
-
1、master更新数据时，会在事务前产生GTID，一同记录到binlog日志中。
-
2、slave端的i/o 线程将变更的binlog，写入到本地的relay log中。
-
3、sql线程从relay log中获取GTID，然后对比slave端的binlog是否有记录。
-
4、如果有记录，说明该GTID的事务已经执行，slave会忽略。
-
5、如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlog。
-
6、在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有就用全部扫描
配置基于GTID的半同步主从复制
先清空一下二进制日志
root@(none) 16:30 mysql>reset master;
Query OK, 0 rows affected (0.01 sec)
root@(none) 16:30 mysql>show master status;
+---------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+-------------------+
| sc-slave-bin.000001 | 154 | | | |
+---------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
- 在 master中的 /etc/my.cof 里边加入
gtid-mode=ON
enforce-gtid-consistency=ON
- 在slave上也记得加
gtid-mode=ON
enforce-gtid-consistency=ON
记得3，4刷新服务
- 在master建立授权用户(拿以前的贴一下，注意)
用来给slave复制二进制命令
root@(none) 15:51 mysql>grant replication slave on *.* to 'renxj'@'192.168.153.%' identified by 'Sanchuang123#';
Query OK, 0 rows affected, 1 warning (0.00 sec)
root@(none) 15:56 mysql>
- 在slave上配置master info的信息(拿以前的贴一下，注意,先执行6，再执行5)
CHANGE MASTER TO MASTER_HOST='192.168.153.132',
MASTER_USER='renxj',
MASTER_PASSWORD='Sanchuang123#',
MASTER_PORT=3306,
master_auto_position=1;
操作:
root@(none) 16:54 mysql>stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
root@(none) 16:55 mysql>reset slave all;
Query OK, 0 rows affected (0.00 sec)
root@(none) 16:55 mysql>CHANGE MASTER TO MASTER_HOST='192.168.153.132',
-> MASTER_USER='renxj',
-> MASTER_PASSWORD='Sanchuang123#',
-> MASTER_PORT=3306,
-> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
root@(none) 16:55 mysql>
- 清楚slave中之前填写的master info信息
reset slave all; --->从
reset master; --->主
操作：
root@(none) 16:48 mysql>stop slave;
Query OK, 0 rows affected (0.00 sec)
root@(none) 16:51 mysql>reset slave all;
Query OK, 0 rows affected (0.00 sec)
root@(none) 16:51 mysql>
7.看是否成功
root@(none) 16:56 mysql>stop slave;
Query OK, 0 rows affected (0.00 sec)
root@(none) 16:57 mysql>reset slave all;
Query OK, 0 rows affected (0.00 sec)
root@(none) 16:57 mysql>CHANGE MASTER TO MASTER_HOST='192.168.153.132',
-> MASTER_USER='renxj',
-> MASTER_PASSWORD='Sanchuang123#',
-> MASTER_PORT=3306,
-> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
root@(none) 16:57 mysql>start slave;
Query OK, 0 rows affected (0.00 sec)
root@(none) 16:57 mysql>show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.153.132
Master_User: renxj
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: sc-slave-bin.000002
Read_Master_Log_Pos: 154
Relay_Log_File: sc-master-relay-bin.000002
Relay_Log_Pos: 373
Relay_Master_Log_File: sc-slave-bin.000002
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: 154
Relay_Log_Space: 584
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: 2
Master_UUID: af0068be-7ae2-11ee-af43-000c292abddd
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:
Executed_Gtid_Set:
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
root@(none) 16:57 mysql>
Auto_Position: 1
Master_UUID: af0068be-7ae2-11ee-af43-000c292abddd
- 查看半同步功能是否开启
主:
root@(none) 16:59 mysql>show variables like "%semi_sync%";
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 1000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_trace_level | 32 |
+-------------------------------------------+------------+
8 rows in set (0.00 sec)
root@(none) 17:02 mysql>
从：
root@(none) 16:57 mysql>show variables like "%semi_sync%";
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | OFF |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+-------------------------------------------+------------+
8 rows in set (0.00 sec)
root@(none) 17:01 mysql>
- 验证GTID的半同步主从复制是否成功
主服务器:
root@(none) 17:02 mysql>create database tanxue;
Query OK, 1 row affected (0.01 sec)
root@(none) 17:03 mysql>use tanxue;
Database changed
root@tanxue 17:04 mysql>create table t1(id int);
Query OK, 0 rows affected (0.00 sec)
root@tanxue 17:04 mysql>insert into t1(id) values(1),(2);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
root@tanxue 17:04 mysql>
从服务器:
root@(none) 17:01 mysql>use tanxue;
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
root@tanxue 17:05 mysql>show tables;
+------------------+
| Tables_in_tanxue |
+------------------+
| t1 |
+------------------+
1 row in set (0.01 sec)
root@tanxue 17:05 mysql>select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
root@tanxue 17:05 mysql>
root@tanxue 17:05 mysql>show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.153.132
Master_User: renxj
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: sc-slave-bin.000002
Read_Master_Log_Pos: 747
Relay_Log_File: sc-master-relay-bin.000002
Relay_Log_Pos: 966
Relay_Master_Log_File: sc-slave-bin.000002
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: 747
Relay_Log_Space: 1177
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: 2
Master_UUID: af0068be-7ae2-11ee-af43-000c292abddd
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: af0068be-7ae2-11ee-af43-000c292abddd:1-3
Executed_Gtid_Set: af0068be-7ae2-11ee-af43-000c292abddd:1-3
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
root@tanxue 17:06 mysql>
可以看到gtid号：
Retrieved_Gtid_Set: af0068be-7ae2-11ee-af43-000c292abddd:1-3 Executed_Gtid_Set: af0068be-7ae2-11ee-af43-000c292abddd:1-3
须知
业务量小: 推荐使用gtid的半同步主从复制，只要2台服务器
业务量大 : 推荐使用组复制，至少3，5，7，9台服务器
扫一扫
1027
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
