Shulou(Shulou.com)06/01 Report--

1. What is GTID?

1. Global uniqueness: a transaction corresponds to a GTID2 to replace traditional binlog+pos replication; use master_auto_position=1 to automatically match GTID breakpoints for replication 3, and MySQL5.6 starts to support 4. In traditional master-slave replication, binlog; does not have to be enabled on the slave side, but in GTID master-slave replication, binlog5 must be enabled. When the slave side accepts the binlog of master, it will verify the GTID value 6. In order to ensure the consistency of master-slave data, multi-threads execute a GTID at the same time.

2. Composition

Master_UUID: serial number

For example:

Ceb0ca3d-8366-11e8-ad2b-000c298b7c9a:1-5

Ceb0ca3d-8366-11e8-ad2b-000c298b7c9a is actually the UUID value of master; 1-5 is the sequence number, and each transaction completes will be incremented by 1, that is, 1-6 next time.

3. Working principle

1. When master updates data, GTID will be generated before the transaction and recorded in the binlog log. 2. The iUnip thread on the slave side writes the changed binlog to the local relay log. 3. The SQL thread gets the GTID from the relay log, and then compares whether the binlog on the slave side has a record. 4. If there is a record, the transaction of the GTID has been executed, and slave will ignore it. 5. If there is no record, slave will execute the transaction of the GTID from relay log and record it to binlog. 6. in the parsing process, it will determine whether there is a primary key, if not, use a secondary index, if not, use a full scan.

4. GTID master-slave configuration

Version: MySQL5.7

Configure master

Vim / etc/my.cnf [client] socket=/usr/local/mysql/mysql.sock [mysqld] basedir=/usr/local/mysql datadir=/usr/local/mysql/data user=mysql pid-file=/usr/local/mysql/data/mysqld.pid log-error=/usr/local/mysql/data/mysql.err socket=/usr/local/mysql/mysql.sock port=3306 Server-id=1 gtid-mode=ON enforce-gtid-consistency=ON server-id=1 binlog_format=row log-bin=/usr/local/mysql/data/mysql-binsystemctl restart mysqldfirewall-cmd-add-port=3306/tcp-permanentfirewall-cmd-reload

Configure slave

Vim / etc/my.cnf [client] socket=/usr/local/mysql/mysql.sock [mysqld] basedir=/usr/local/mysql datadir=/usr/local/mysql/data user=mysql pid-file=/usr/local/mysql/data/mysqld.pid log-error=/usr/local/mysql/data/mysql.err socket=/usr/local/mysql/mysql.sock port=3306 Server-id=2 gtid-mode=ON enforce-gtid-consistency=ON server-id=2 binlog_format=ROW log-bin=/usr/local/mysql/data/mysql-bin log_slave_updates=ON skip-slave-start=1systemctl restart mysqldfirewall-cmd-add-port=3306/tcp-permanentfirewall-cmd-reload

Master authorization configuration

Mysql-uroot-pmysql > grant replication slave on *. * to 'rep'@'10.0.0.%' identified by' 123 shares MySQL > flush privileges

Slave configuration synchronization

Mysql-uroot-pmysql > change master to master_host='10.0.0.132', master_user='rep',master_password='123',master_port=3306,master_auto_position=1;mysql > start slave

View the status of slave

Mysql > show slave status\ G * * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 10.0.0.132 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 635 Relay_Log_File: slave-relay-bin.000005 Relay_Log_Pos: 848 Relay_Master_Log_File: 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: 635 Relay_Log_Space: 1308 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: 1 Master_UUID: ceb0ca3d -8366-11e8-ad2b-000c298b7c9a Master_Info_File: / usr/local/mysql/data/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: ceb0ca3d-8366-11e8-ad2b-000c298b7c9a:1-4 Executed_Gtid_Set: ceb0ca3d-8366-11e8Murad2b- 000c298b7c9a:1-4 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)

The appearance of these two yes indicates that the synchronization is successful

Through the status information of slave, you can see the value of GTID, Matser_UUID and other information.

View master status

Mysql > show master status +-+ | File | Position | Binlog_ Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-- -+ | mysql-bin.000003 | 635 | ceb0ca3d-8366-11e8-ad2b-000c298b7c9a:1-4 | +-- -- + 1 row in set (0.00 sec)

Note that compared to the slave side, the value of Executed_Gtid_Set should be the same.

5. Verify the master and slave

On master

Mysql > create database test01;Query OK, 1 row affected (0.00 sec) mysql > show master status +-+ | File | Position | Binlog_ Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-- -+ | mysql-bin.000003 | 800 | ceb0ca3d-8366-11e8-ad2b-000c298b7c9a:1-5 | + -- + 1 row in set (0.00 sec)

On slave

Mysql > show databases;+-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | sys | | test01 | +-+ 5 rows in set (0.07 sec) mysql > show slave status\ G * * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 10.0.0.132 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 800 Relay_Log_File: slave-relay-bin.000005 Relay_Log_Pos: 1013 Relay_Master_Log_File: 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: 800 Relay_Log_Space: 1473 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: 1 Master_UUID: ceb0ca3d -8366-11e8-ad2b-000c298b7c9a Master_Info_File: / usr/local/mysql/data/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: ceb0ca3d-8366-11e8-ad2b-000c298b7c9a:1-5 Executed_Gtid_Set: ceb0ca3d-8366-11e8Murad2b- 000c298b7c9a:1-5 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)

It should be noted that after completing a transaction, the value of GTID becomes ceb0ca3d-8366-11e8-ad2b-000c298b7c9a:1-5 (self-increment 1)

6. Troubleshooting

Train of thought

A. Make sure master opens port 3306.

B. It's best to close selinux.

C. Synchronization is authorized on master. The change master command on slave specifies that the information of master should not be misspelled.

D, UUID problem

If you have the problem shown in the figure above, it means that your master and slave's UUID are the same, which usually occurs in cloning virtual machines.

Solution:

Find the auto.cnf file under the MySQL data directory on slave (this file is actually the automatically generated UUID value of the mysql server), delete it, restart MySQL, and MySQL will regenerate a UUID. Then stop slave and reopen it (the data directory of my mysql is under / usr/local/mysql/data. See the my.cnf configuration file for details)

Cd / usr/local/mysql/datarm-f auto.cnfsystemctl restart mysql [root@slave data] # cat auto.cnf [auto] server-uuid=020c7f26-be57-11e8-8e2d-000c29b63bad

View the file through the cat command and find that UUID has changed

Mysql-uroot-pmysql > stop slave;mysql > start slave

E. Summary

During troubleshooting, note that you need to stop slave and turn it on after making changes, otherwise your changes may not take effect.

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.