赞
踩
什么是GTID?
全局事务标识符GTID的全称为Global Transaction Identifier,是在整个复制环境中对一个事务的唯一标识。
它是MySQL 5.6加入的一个强大特性,目的在于能够实现主从自动定位和切换,而不像以前需要指定文件和位置。
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长什么样子?
de35d2ef-cd37-11ed-93d8-000c2954bc91:1-4
uuid号:每个MySQL实例的唯一编号
使用GTID复制时,主库上提交事务时创建事务对应的GTID,从库在应用中继日志时用GTID识别和跟踪每个事务。在启动新从库或因故障转移到新主库时可以使用GTID来标识复制的位置,极大地简化了这些任务。
简而言之,在新的主从切换的时候,新的从服务器知道哪些事务已经做了,哪些没有做。
工作原理:
1、master更新数据时,会在事务前产生GTID,一同记录到binlog日志中。
2、slave端的i/o线程将变更的binlog,写入到本地的relay log中。
3、sql线程从relayog中获取GTID,然后对比slave端的binlog是否有记录。
4、如果有记录,说明该GTID的事务已经执行,slave会忽略。
5、如果没有记录,slave就会从relay log中执行该GTID的事务,并记录到binlog。
6、在解析过程中会判断是否有主键,如果没有就用二级索引,如果没有就用全部扫描
查看master上的配置
[root@jd-mysql ~]# cat /etc/my.cnf [mysqld_safe] [client] socket=/data/mysql/mysql.sock [mysqld] socket=/data/mysql/mysql.sock port = 3306 open_files_limit = 8192 innodb_buffer_pool_size = 512M character-set-server=utf8mb4 #skip-grant-tables #二进制日志开启 log_bin server_id = 1 #开启半同步,需要提前安装半同步的插件 rpl_semi_sync_master_enabled=1 rpl_semi_sync_master_timeout=1000 # 1 second #gtid功能 gtid-mode=ON enforce-gtid-consistency=ON [mysql] auto-rehash prompt=\u@\d \R:\m mysql>
改了配置,首先一定记得刷新服务!
[root@jd-mysql mysql]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
配置slave的配置
[root@jd-mysql-2 ~]# cat /etc/my.cnf [mysqld_safe] [client] socket=/data/mysql/mysql.sock [mysqld] socket=/data/mysql/mysql.sock port = 3306 open_files_limit = 8192 innodb_buffer_pool_size = 512M character-set-server=utf8 #log bin 二进制日志 log_bin server_id = 2 #开启gtid功能 gtid-mode=ON enforce-gtid-consistency=ON log_slave_updates=ON #开启半同步,需要提前安装半同步的插件 rpl_semi_sync_slave_enabled=1 [mysql] auto-rehash prompt=\u@\d \R:\m mysql>
记得刷新服务!
[root@jd-mysql-2 mysql]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
在master上新建一个授权用户,给slave来复制二进制日志
root@(none) 16:41 mysql>grant replication slave on *.* to 'jiangda'@'192.168.1.%' identified by 'Jiangda123#';
Query OK, 0 rows affected, 1 warning (1.01 sec)
在slave上配置master info的信息
CHANGE MASTER TO MASTER_HOST=‘192.168.1.150’ ,
MASTER_USER=‘jiangda’,
MASTER_PASSWORD=‘Jiangda123#’,
MASTER_PORT=3306,
master_auto_position=1;
root@(none) 16:43 mysql>stop slave; Query OK, 0 rows affected (0.00 sec) root@(none) 16:43 mysql>reset slave all; Query OK, 0 rows affected (0.01 sec) root@(none) 16:43 mysql>CHANGE MASTER TO MASTER_HOST='192.168.1.150' , -> MASTER_USER='jiangda', -> MASTER_PASSWORD='Jiangda123#', -> MASTER_PORT=3306, -> master_auto_position=1; Query OK, 0 rows affected, 2 warnings (0.01 sec) root@(none) 16:43 mysql>start slave; Query OK, 0 rows affected (0.00 sec) root@(none) 16:43 mysql>show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.150 Master_User: jiangda Master_Port: 3306 Connect_Retry: 60 Master_Log_File: jd-mysql-bin.000003 Read_Master_Log_Pos: 450 Relay_Log_File: jd-mysql-2-relay-bin.000002 Relay_Log_Pos: 669 Relay_Master_Log_File: jd-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: 450 Relay_Log_Space: 881 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: de35d2ef-cd37-11ed-93d8-000c2954bc91 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: de35d2ef-cd37-11ed-93d8-000c2954bc91:1 Executed_Gtid_Set: de35d2ef-cd37-11ed-93d8-000c2954bc91:1 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) ERROR: No query specified
在master上查看
root@(none) 16:42 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 |
+-------------------------------------------+------------+
6 rows in set (0.00 sec)
在slave上查看
root@(none) 16:43 mysql>show variables like "%semi_sync%";
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.00 sec)
验证GTID的半同步主从复制
在master上操作:
root@(none) 16:37 mysql>create database dada;
Query OK, 1 row affected (0.03 sec)
root@(none) 16:37 mysql>use dada;
Database changed
root@dada 16:38 mysql>create table t1(id int);
Query OK, 0 rows affected (0.02 sec)
root@dada 16:38 mysql>insert into t1(id) values(1),(2);
Query OK, 2 rows affected (0.10 sec)
Records: 2 Duplicates: 0 Warnings: 0
在slave上查看:
root@(none) 16:36 mysql>use dada; 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@dada 16:38 mysql>show tables; +------------------+ | Tables_in_dada | +------------------+ | t1 | +------------------+ 1 row in set (0.01 sec) root@dada 16:39 scmysql>select * from t1; +------+ | id | +------+ | 1 | | 2 | +------+ 2 rows in set (0.00 sec)
root@dada 16:44 mysql>show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.150 Master_User: jiangda Master_Port: 3306 Connect_Retry: 60 Master_Log_File: jd-mysql-bin.000003 Read_Master_Log_Pos: 1043 Relay_Log_File: jd-mysql-2-relay-bin.000002 Relay_Log_Pos: 1262 Relay_Master_Log_File: jd-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: 1043 Relay_Log_Space: 1474 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: de35d2ef-cd37-11ed-93d8-000c2954bc91 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: de35d2ef-cd37-11ed-93d8-000c2954bc91:1-4 Executed_Gtid_Set: de35d2ef-cd37-11ed-93d8-000c2954bc91:1-4 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) ERROR: No query specified
业务量小:推荐使用gtid的半同步主从复制,只要2~3台服务器
业务量大: 推荐使用组复制,至少3,5,7,9台服务器
但是当我们需要实现级联同步时,即以这样的一个模式,A>B>C实现三级同步时,AB库除了需要设置log-bin参数还需要添加一个参数:log-slave-updates
log-slave-updates参数默认时关闭的状态,如果不手动设置,那么bin-log只会记录直接在该库上执行的SQL语句,由replication机制的SQL线程读取relay-log而执行的SQL语句并不会记录到bin-log,那么就无法实现上述的三级级联同步。
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。