Configure MySQL GTID master-slave Replication

Configure MySQL GTID master-slave Replication

GTID is a global transaction ID generated based on the original mysql server that has been successfully executed. It is composed of the server ID and transaction ID. This global transaction ID is not only unique on the original server, but also unique on all mysql servers with master-slave relationships. It is precisely because of this feature that mysql master-slave replication becomes simpler and Database Consistency becomes more reliable. This article describes how to quickly configure a master-slave replication Architecture Based on GTID for your reference.

1. Concept of GTID

1. global transaction identifier: global transaction identifiers. 2. GTID is a globally unique ID that corresponds to a transaction. 3. a gtid can be executed only once on a server to avoid data confusion or inconsistency between the master and the slave. 4. GTID is used to replace the traditional replication method and does not use MASTER_LOG_FILE + MASTER_LOG_POS to enable replication. Instead, use MASTER_AUTO_POSTION = 1 to Start copying. 5, MySQL-5.6.5 began to support, MySQL-5.6.10 began to improve. 6. In traditional slave, binlog is not enabled, but in GTID, binlog of slave must be enabled to record the GTID that has been executed (forced ).

Ii. Composition of GTID

GTID = source_id: transaction_idsource_id, used to identify the original server, that is, the unique server_uuid of the mysql server. Because GTID is passed to slave, it can also be understood as the source ID. Transaction_id is the serial number of the committed transactions on the current server. It is generally a sequence that increases from 1. A value corresponds to a transaction. Example: 3E11FA47-71CA-11E1-9E33-C80AA9429562: the first string is the server_uuid of the server, that is, 3E11FA47-71CA-11E1-9E33-C80AA9429562, and the following 23 is transaction_id.

Iii. Advantages of GTID

1. failover is easier to implement. You do not need to find log_file and log_pos as you did before. 2. Set up master-slave replication more easily. 3. It is safer than traditional replication. 4. GTID is continuous without holes, ensuring data consistency and Zero loss.

Iv. Working Principle of GTID

1. When a transaction is executed and committed on the master database, GTID is generated and recorded in the binlog together. 2. After the binlog is transmitted to the slave and stored to the slave relaylog, read the value of the GTID and set the gtid_next variable, which tells Slave the next GTID value to be executed. 3. the SQL thread obtains the GTID from the relay log and compares whether the binlog of the slave end has the GTID. 4. If there is a record, it indicates that the GTID transaction has been executed, and slave will ignore it. 5. If no record exists, slave will execute the GTID transaction and record the GTID to its own binlog. before reading the transaction, it will check that other sessions hold the GTID, make sure that the execution is not repeated. 6. During the parsing process, the system determines whether a primary key exists. If no primary key exists, it uses a secondary index. If no primary key exists, it uses a full scan.

5. Configure GTID

For GTID configuration, it is mainly to modify several important parameters in the configuration file related to GTID features (recommended to use mysql-5.6.5 or a later version), as follows: 1, Master: [mysqld] # GTID: server_id = 135 # server idgtid_mode = on # enable gtid mode enforce_gtid_consistency = on # force gtid consistency, it is not supported for specific create table after enabling # binlog_bin = master-binloglog-slave-updates = 1 binlog_format = row # It is strongly recommended that, other formats may cause data inconsistency # relay logskip_slave_start = 1 2. Slave: [mysqld] # GTID: gtid_mode = login = onserver_id = 143 # binloglog-bin = slave-binloglog-slave-updates = 1binlog_format = row # It is strongly recommended that data inconsistency be caused by other formats # relay logskip_slave_start = 1

6. Configure GTID-based Replication

1. For the newly configured mysql server, follow the configuration parameter file described at the fifth point in this article to perform the following operations on the slave side (root @ localhost) [(none)]> change master to-> MASTER_HOST = '2017. 168.1.135 ',-> MASTER_USER = 'repl',-> MASTER_PASSWORD = 'xxx',-> MASTER_PORT = 3306,-> MASTER_AUTO_POSITION = 1; Query OK, 0 rows affected, 2 warnings (0.01 sec) (root @ localhost) [(none)]> start slave; Query OK, 0 rows affected (0.01 sec) (root @ localhost) [(none)]> show slave status \ G ### you can see that the replication process has started and is normal ************************ * ** 1. row ************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.135 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-binlog.000001 Read_Master_Log_Pos: 151 Relay_Log_File: slave-relay-log.000002 Relay_Log_Pos: 369 runtime: master-binlog.000001 runtime: Yes Slave_ SQL _Running: yes2. You have run the classic copy mysql server and switched to GTID copy a. Configure the parameter file according to the fifth point in this article. B. set global on all servers. read_only parameter, waiting for the master-slave server to complete synchronization; mysql> SET @ global. read_only = ON; c. Restart the master-slave server in sequence; d. Use change master to update the master-slave configuration; mysql> change master to> MASTER_HOST = host,> MASTER_PORT = port,> MASTER_USER = user,> MASTER_PASSWORD = password,> MASTER_AUTO_POSITION = 1; e. Copy mysql from the database> start slave; f. Verify master-SLAVE Replication

MySQL5.6 master-slave Replication Based on GTID

Load Nginx in Ubuntu for high-performance WEB Server 5 --- MySQL master/Master Synchronization

Production Environment MySQL master/Master synchronization primary key conflict handling

MySQL Master/Slave failure error Got fatal error 1236

MySQL master-slave replication, implemented on a single server

Build a MySQL proxy server for read/write splitting + Master/Slave Synchronization

MySQL 5.5 master-slave bidirectional Synchronization

MySQL 5.5 master-slave synchronization troubleshooting

MySQL master-slave replication asynchronous semi-sync instance

This article permanently updates the link address: