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

This article mainly introduces how to use GTID when MySQL makes master-slave replication. I hope I can add and update some knowledge to you. If you have any other questions you need to know, you can continue to follow my updated articles in industry information.

A new feature of MySQL is to use GTID in master-slave replication after 5.6. compared with the traditional master-slave replication that uses relaylog to specify log_pos+log_file, you can use GTID as master-slave replication without specifying which binlog and offset in master need to be read by slave. In the traditional MySQL master-slave replication, once the offset of the wrong master is specified, it will cause the master to be inconsistent, which will not occur in the master-slave replication based on GTID. GTID is actually the ID generated after master commits a transaction, so the configuration parameter of enforce_gtid_consistency (forcing transaction consistency) is generally enabled during configuration to ensure the security of GTID. However, it should be noted that if enforce_gtid_consistency is enabled, temporary tables cannot be created and deleted in the transaction, which should be noted, such as creating temporary tables:

Create temporary table suggested changing it to create table.

In addition, log_slave_updates will be enabled, and this variable will be enabled in both master and slave. In addition, there is no doubt that binlog must be enabled. As for other master-slave replication similar to log_pos+log_file, let's talk about the differences here. As for other master-slave replication needs of log_pos+log_file previously mentioned, please see: http://jim123.blog.51cto.com/4763600/1862808. Enable the relevant variables of GTID in master and slave. Of course, if restart is allowed, it is better to configure both my.cnf first, in which both need to be added under "mysqld" of my.cnf:

Log_slave_updates = ongtid_mode = onenforce_gtid_consistency = on it is recommended to enable read-only under slave: read_only = onrelay_log_info_repository=TABLE# records the master and slave information in the table. By default, the entry master_info_repository=TABLE# is written in the file as in the old version and is written on the master.

In the process of configuration, it is best to turn off the write of master and enable read-only:

Mysql > set global read_only = ON;Query OK, 0 rows affected (0.00 sec)

After the corresponding variable parameters are turned on, the master-slave copy of GTID on slave will find log_pos+log_file according to GTID using master_auto_position, which is more convenient than the traditional method.

Mysql > stop slave;Query OK, 0 rows affected (0.08 sec) mysql > change master to master_auto_position=0;Query OK, 0 rows affected (0.01 sec) mysql > change master to master_host='192.168.168.253',master_user='test_backup',master_password='test_backup',master_auto_position = 1 politics query OK, 0 rows affected, 2 warnings (0.01 sec) mysql > start slave;Query OK, 0 rows affected (0.01 sec)

When enabled, you can view the GTID parameter variable:

Mysql > show global variables like'% GTID%'

Of course, in order to achieve high service availability, MySQL will also enable log_pos+log_file when using GTID, but we do not need to manually point to log_pos+log_file as before, so the method of using GTID for master-slave replication repair is not the same as before:

Mysql > stop slave;Query OK, 0 rows affected (0.08 sec) mysql > change master to master_auto_position=0;Query OK, 0 rows affected (0.01 sec) mysql > change master to master_host='192.168.168.253',master_user='test_backup',master_password='test_backup',master_log_file='mysql-bin.000014',master_log_pos=405051906;Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql > start slave;Query OK, 0 rows affected (0.01 sec)

Of course, this recovery method is based on the combination of GTID and binlog. When you choose the master-slave replication method, you only need to choose one of them to recover. If there are only a few or no transactions when the business is not busy, you can also try to skip transactions to solve the error:

Mysql > STOP SLAVE;Query OK, 0 rows affected (0.01 sec) mysql > SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;Query OK, 0 rows affected (0.01 sec) mysql > START SLAVE;Query OK, 0 rows affected (0.01 sec)

In addition, it should be noted that the direct dynamic shutdown of GTID is not supported when using MySQL's GTID. If you need to close, you need to use the four states of GTID:

ON: enable OFF: close OFF_PERMISSIVE: close preparation ON_PERMISSIVE: enable preparation

The normal opening and closing process is OFF OFF_PERMISSIVE ON_PERMISSIVE ON, that is,

SET GLOBAL gtid_mode = 'OFF_PERMISSIVE';SET GLOBAL gtid_mode =' ON_PERMISSIVE';SET GLOBAL enforce_gtid_consistency = ON;# Open SET GLOBAL gtid_mode = 'ON' during opening preparation

Finally, it is important to note that when GTID replication is turned on, multiple threads will only use one transaction. In fact, if multi-thread replication is turned on, it is still multi-threaded replication, but only one is shown when viewing the list of threads.

Read the above about MySQL master-slave replication when how to use GTID, I hope it can give you some help in practical application. Due to the limited space in this article, it is inevitable that there will be deficiencies and need to be supplemented. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

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.