Configuring MySQL GTID master-slave replication

Gtid is a global transaction ID that has been successfully executed based on the original MySQL server, combined with the server ID and transaction ID. This global transaction ID is unique not only on the original server, but also on all MySQL servers where the master-slave relationship exists. It is this feature that makes MySQL's master-slave replication easier and database consistency more reliable. This paper mainly describes the rapid configuration of a master-slave replication architecture based on Gtid for your reference.

First, the concept of Gtid

1. Global Transaction ID: Transaction identifiers. 2, Gtid is a transaction one by one correspondence, and a globally unique ID. 3. A gtid is executed only once on a server, avoiding duplication of data or the fact that the master never agrees. 4, Gtid to replace the traditional method of replication, no longer use Master_log_file+master_log_pos to open replication. Instead, use Master_auto_postion=1 to start copying. 5, MySQL-5.6.5 began to support, after MySQL-5.6.10 began to improve. 6, in the traditional slave end, Binlog is not open, but in gtid slave end of the binlog is must be opened, the purpose is to record the execution of Gtid (mandatory).

Ii. Composition of Gtid

GTID = source_id:transaction_idsource_id, used to identify the original server, the MySQL server is the only Server_uuid, because GTID will be passed to slave, so it can also be understood as the source ID.        TRANSACTION_ID, which is a sequence number of committed transactions on the current server, usually starting from 1 for a self-growing series, a value corresponding to a transaction. Example: A string in front of 3e11fa47-71ca-11e1-9e33-c80aa9429562:23 is the server's Server_uuid, which is 3e11fa47-71ca-11e1-9e33-c80aa9429562, The back 23 is transaction_id.

Third, the advantages of Gtid

1, more simple implementation of failover, do not need to find log_file and Log_pos before. 2, more simple to build master-slave replication. 3, more secure than the traditional copy. 4, Gtid is continuous without empty, to ensure the consistency of data, 0 lost.

Iv. working principle of Gtid

1. When a transaction is executed and committed at the main library side, the Gtid is generated and recorded in the Binlog log together. 2, Binlog transmission to slave, and stored to slave relaylog, read this gtid value set gtid_next variable, that is, tell slave, the next Gtid value to execute. 3. SQL thread gets gtid from relay log and then compares Binlog on slave side for that gtid. 4, if there is a record, indicating that the Gtid transaction has been executed, slave will be ignored. 5, if there is no record, slave will execute the Gtid transaction, and record the Gtid to its own binlog, before reading the execution of the transaction will first check the other session holding the Gtid, to ensure that it is not repeated execution. 6, in the parsing process will determine whether there is a primary key, if not a two-level index, if not the full scan.

V. Configuration Gtid

For GTID configuration, it mainly modifies several important parameters related to the GTID feature in the configuration file (recommended use of mysql-5.6.5 or above), as follows: 1, main: [Mysqld] #GTID: server_id=135 #服务器idgtid_mode =on #开启gtid模式enforce_gtid_consistency =on #强制gtid一致性 is not supported for a specific create table after it is turned on #binloglog_bin=master-binloglo G-slave-updates=1 Binlog_format=row #强烈建议, other formats may result in inconsistent data #relay Logskip_slave_start=1 2, from: [mysqld]# Gtid:gtid_mode=onenforce_gtid_consistency=onserver_id=143#binloglog-bin=slave-binloglog-slave-updates=1binlog_ Format=row #强烈建议, other formats may result in inconsistent data #relay logskip_slave_start=1

Vi. Configuring Gtid-based replication

1, the new configuration of the MySQL server for the newly configured MySQL server, according to the 5th described in this article configuration parameter file, on the slave side to do the following ([email protected])  [(none)]>  Change master to      ->  master_host= ' 192.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) ([email protected ])  [(none)]> start slave; query ok, 0 rows affected  (0.01 sec) ([email protected])  [(none)]>  show slave status \g ## #可以看到复制工作已经开始且正常 *************************** 1. row  ***************************     &Nbsp;         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        relay_master_log_file: master-binlog.000001              Slave_IO_Running: Yes             slave_sql_running: yes2, Has run Classic copy MySQL server to gtid copy a, according to the 5th description of this article configuration parameter file; b, all server settings global.read_only parameters, waiting for master and slave server synchronization is complete;         mysql> set @ @global. Read_only = on; c, restart the master-slave server in turn, D, use change  master  Update master-slave configuration;         mysql> change master to         > MASTER_HOST = host,         > MASTER_PORT = port,         > master_user = user,        > master_password = password,         > master_auto_position = 1;e, start copy from library          mysql> start slave;f, verifying master-slave replication

Configuring MySQL GTID master-slave replication