Shulou(Shulou.com)05/31 Report--

It is believed that many inexperienced people have no idea about how to build master and slave based on GTID semi-synchronous in MySQL. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

1. Background

* GTID: global transaction ID (Global Transaction ID). In the whole transaction architecture, each transaction ID number is globally unique. Not only on one node, but also in the whole master-slave replication architecture, the ID number of every two transactions will not be the same.

* GTID consists of the UUID of the current node (a 128bit random number) and the self-increment (TID) generated for the current node.

* GTID can ensure data consistency in distributed architecture. Thus, the high availability of mysql is realized.

* MySQL 5.6 is supported. GTID replaces the original binlog file and file posistion replication location in replication.

two。 Environment

* Master server environment

Mysql > system cat / etc/redhat-releaseCentOS release 6.8 (Final) mysql > system ifconfig eth0 | sed-rn '2s# ^. * addr: (. *) Bca.*$#\ 1#gp'172.18.0.1mysql > show variables like' version' +-+-+ | Variable_name | Value | +-+-+ | version | 5.7.18-log | +-+-+ 1 row in set (0.00 sec)

* Slave server environment

Mysql > system cat / etc/redhat-releaseCentOS release 6.8 (Final) mysql > system ifconfig eth0 | sed-rn '2s# ^. * addr: (. *) Bca.*$#\ 1#gp'172.18.4.1mysql > show variables like' version' +-+-+ | Variable_name | Value | +-+-+ | version | 5.7.18-log | +-+-+ 1 row in set (0.00 sec)

* Master my.cnf configuration file

[mysqld] # basic settings# Master / Slave server-id must set different server-id = 110port = 3306user = mysqlbind_address = 0.0.0.0 character_set_server=utf8mb4skip_name_resolve = 1datadir = / data/mysql_datalog_error = error.log#replication settings# enable gtidgtid_mode = on# force gtid consistency enforce-gtid-consistency = Truemaster_info_repository = TABLErelay_log_info_repository = TABLE# MySQL replication is based on binlog log log_bin = bin.logsync_binlog = 1log_slave_updates# MySQL binlog format to build master / slave must be set to rowbinlog_format = rowrelay_log = relay.logrelay_log_recovery = 1slave_skip_errors = ddl_exist_errors#semi sync replication settings# set plug-in directory path plugin_dir=/usr/local/mysql / lib/plugin# load plug-in plugin_load = "rpl_semi_sync_master=semisync_master.so Rpl_semi_sync_slave=semisync_slave.so "# enable master semisync replicationloose_rpl_semi_sync_master_enabled = enable slave semisync replicationloose_rpl_semi_sync_slave_enabled = wait 5 seconds without ack reply automatically switch to asynchronous mode loose_rpl_semi_sync_master_timeout = 5000

* Slave my.cnf configuration file

[mysqld] # basic settings#gtid_mode = onenforce-gtid-consistency = trueserver-id = 210port = 3306user = mysqlbind_address = 0.0.0.0character_set_server=utf8mb4skip_name_resolve = 1datadir = / data/mysql_datalog_error = error.log#replication settings#master_info_repository = TABLErelay_log_info_repository = TABLElog_bin = bin.logsync_binlog = 1log_slave_updatesbinlog_format = row# slave enable read only Avoid inconsistency between master and slave data caused by application miswriting read_only = onrelay_log = relay.logrelay_log_recovery = 1binlog_gtid_simple_recovery = 1slave_skip_errors = ddl_exist_errors#semi sync replication settings#plugin_dir=/usr/local/mysql/lib/pluginplugin_load = "rpl_semi_sync_master=semisync_master.so Rpl_semi_sync_slave=semisync_slave.so "loose_rpl_semi_sync_master_enabled = 1loose_rpl_semi_sync_slave_enabled = 1loose_rpl_semi_sync_master_timeout = 5000rpl_semi_sync_master_wait_point = AFTER_SYNCrpl_semi_sync_master_wait_for_slave_count = 1

3. Building semi-synchronous replication Master-Slave based on GTID

* the user used by master to create replication [here ip is set to slave service IP or%]

Mysql > grant replication slave on *. * to 'rpl'@'172.18.4.1' identified by' 123 investors query OK, 0 rows affected, 1 warning (0.00 sec)

* configure connection master information on slave server

When the slave service is not enabled, the status of Slave_IO_Running and Slave_SQL_Running becomes No

When mysql > show slave status; # does not enable replication, the slave status is empty Empty set (0.00 sec) mysql > change master to master_host='172.18.0.1',master_user='rpl',master_password='123',master_auto_position=1 Query OK, 0 rows affected 2 warnings (0.02 sec) mysql > show slave status\ gateway * 1. Row * * Slave_IO_State: Master_Host: 172.18.0.1 Master_User: rpl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: Read_Master_Log_Pos: 4 Relay_Log_File: relay.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: Slave_IO_Running: No 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: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 0 Relay_Log_Space: 154 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: NULLMaster_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: 0 Master_UUID: Master_Info_File: mysql.slave_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: Master_SSL_Crl: Master_ SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: e5b2d96a-7047-11e7-b39c-00163e028c02:1 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)

* enable the slave service and check the status

After the slave service is enabled normally, the status of Slave_IO_Running and Slave_SQL_Running becomes Yes

Mysql > show slave status\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 172.18.0.1 Master_User: rpl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: bin.000002 Read_Master_Log_Pos: 689 Relay_Log_File: relay.000002 Relay_Log_Pos: 890 Relay_Master_Log_File: 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: 689 Relay_Log_Space: 1087 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: 0Master_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: 110 Master_UUID: aaa45482-7047 -11e7-a7b3-00163e0432c5 Master_Info_File: mysql.slave_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: aaa45482-7047-11e7-a7b3-00163e0432c5:1-2 Executed_Gtid_Set: aaa45482-7047-11e7-a7b3-00163e0432c5:1-2 E5b2d96a-7047-11e7-b39c-00163e028c02:1 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)

* master to view slave connection information

Mysql > show slave hosts +-+ | Server_id | Host | Port | Master_id | Slave_UUID | +-+- -+-- + | 3306 | 3306 | e5b2d96a-7047-11e7-b39c-00163e028c02 | +- -+-+ 1 row in set (0.00 sec)

* create databases and tables and insert data on Master

Mysql > create database mytest character set utf8mb4;Query OK, 1 row affected (0.02 sec) mysql > use mytest;Database changedmysql > create table a (data INT PRIMARY KEY NOT NULL AUTO_INCREMENT) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;Query OK, 0 rows affected (0.02 sec) mysql > insert into a select null;Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0mysql > insert into a select null;Query OK, 1 row affected (0 sec) Records: 1 Duplicates: 0 Warnings: 0mysql > insert into a select null Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0mysql > select * from rows in set + | data | +-+ | 1 | | 2 | 3 | +-+ 3 sec)

* check on slave

Mysql > select * from mytest.a;+-+ | data | +-+ | 1 | | 2 | | 3 | +-+ 3 rows in set (0.00 sec)

4. Slave error writing, gtid error resolution

* View master data

Mysql > select * from mytest.a;+-+ | data | +-+ | 1 | | 2 | | 3 | +-+ 3 rows in set (0.00 sec)

* View slave data

Mysql > select * from mytest.a;+-+ | data | +-+ | 1 | | 2 | | 3 | +-+ 3 rows in set (0.00 sec)

* miswritten slave data

Mysql > insert into mytest.a select null;Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0mysql > select * from mytest.a;+-+ | data | +-+ | 1 | 2 | 3 | 4 | +-+ 4 rows in set (0.00 sec)

* master same data insertion

Mysql > insert into a select null;Query OK, 1 row affected (0.01sec) Records: 1 Duplicates: 0 Warnings: 0mysql > select * from atheft rows in set + | data | +-+ | 1 | | 2 | | 3 | 4 | +-+ 4 rows in set (0.00 sec)

* slave server status check reported a 1062 error, and the SQL thread stopped working. The skip error cannot be set due to enabling gtid.

Mysql > show slave status\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 172.18.0.1 Master_User: rpl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: bin.000002 Read_Master_Log_Pos: 5768 Relay_Log_File: relay.000005 Relay_Log_Pos: 1354 Relay_Master_Log_File: bin.000002 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: 1062 Last_Error: Could not execute Write_rows event on table mytest.a Duplicate entry'4' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY The event's master log bin.000002 End_log_pos 5737 Skip_Counter: 0 Exec_Master_Log_Pos: 5512 Relay_Log_Space: 2601 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: NULLMaster_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1062 Last_SQL_Error: Could not execute Write_rows event on table mytest.a Duplicate entry'4' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY The event's master log bin.000002 End_log_pos 5737 Replicate_Ignore_Server_Ids: Master_Server_Id: 110Master_UUID: aaa45482-7047-11e7-a7b3-00163e0432c5 Master_Info_File: mysql.slave_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: 170724 17:15:51 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: aaa45482-7047-11e7-a7b3-00163e0432c5:10-23 Executed_Gtid_Set: aaa45482-7047-11e7-a7b3-00163e0432c5:1-22 E5b2d96a-7047-11e7-b39c-00163e028c02:1-5 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)

* slave server stops replication

Mysql > stop slave;Query OK, 0 rows affected (0.00 sec)

* set the gtid next execution to get the unexecuted gtid through Retrieved_Gtid_Set and Executed_Gtid_Set

Mysql > set gtid_next = 'aaa45482-7047-11e7-a7b3-00163e0432c5 11e7-a7b3 23 questions OK, 0 rows affected (0.00 sec)

* execute empty things

Mysql > begin;Query OK, 0 rows affected (0.00 sec) mysql > commit;Query OK, 0 rows affected (0.00 sec)

* set gtid to find gtid things automatically next time

Mysql > set gtid_next = 'automatic';Query OK, 0 rows affected (0.00 sec)

* slave server enables replication

Mysql > start slave;Query OK, 0 rows affected (0.01 sec)

* the slave server checks the status and the SQL thread starts to work normally.

Mysql > show slave status\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 172.18.0.1 Master_User: rpl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: bin.000002 Read_Master_Log_Pos: 5768 Relay_Log_File: relay.000006 Relay_Log_Pos: 436 Relay_Master_Log_File: 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: 5768 Relay_Log_Space: 2089 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: 0Master_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: 110 Master_UUID: aaa45482-7047 -11e7-a7b3-00163e0432c5 Master_Info_File: mysql.slave_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: aaa45482-7047-11e7-a7b3-00163e0432c5:10-23 Executed_Gtid_Set: aaa45482-7047-11e7-a7b3-00163e0432c5:1-23 E5b2d96a-7047-11e7-b39c-00163e028c02:1-5 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)

5. Summary

In order to demand-driven technology, there is no difference in technology itself, only in business.

After reading the above, have you mastered the method of building master and slave semi-synchronously based on GTID in MySQL? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.