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

This article mainly introduces the relevant knowledge of "how to apply GTID replication of MySQL". The editor shows you the operation process through an actual case. The operation method is simple, fast and practical. I hope this article "how to apply GTID replication of MySQL" can help you solve the problem.

A new GTID-based replication method has been added since MySQL 5.6.5. GTID ensures that each transaction committed on the main library has a unique ID in the cluster. This method strengthens the main and standby consistency, fault recovery and fault tolerance of the database.

What is GTID?

GTID (Global Transaction ID) is the number for a committed transaction and is a globally unique number. GTID is actually made up of UUID+TID. Where UUID is the unique identity of a MySQL instance. TID represents the number of transactions that have been committed on this instance and increases monotonously as the transaction commits.

Here is a concrete form of GTID: 3E11FA47-71CA-11E1-9E33-C80AA9429562:23, with the colon split preceded by uuid and followed by TID.

The GTID collection can contain transactions from multiple MySQL instances, separated by commas.

If the transaction sequence number from the same MySQL instance has multiple ranges, the ranges are separated by colons. For example: e6954592-8dba-11e6-af0e-fa163e1cf111:1-5:11-18meme6954592-8dba-11e6-af0e-fa163e1cf3f2:1-27.

What are the improvements in GTID?

In the original binary log-based replication, the slave library needs to tell the master database which offset to synchronize incrementally. If you specify an error, the data will be omitted, resulting in data inconsistency. With the help of GTID, other slave libraries of MySQL can automatically find the correct replication location on the new master database in case of master / slave switching, which greatly simplifies the maintenance of clusters under complex replication topologies and reduces the risk of misoperation of artificial replication locations. In addition, GTID-based replication can ignore transactions that have already been executed, reducing the risk of data inconsistencies.

Based on gtid set, the master library can know exactly what data is missing from the slave database, and will not give more or less data to the slave database, so as to avoid the waste of network bandwidth.

In the case of one master and one slave, the master-slave structure of mysql has no advantage for GTID, but for more than two masters, the structural advantage is obvious, and the new master can be switched without data loss.

Note: before building master-slave replication, perform some operations (such as data cleaning, etc.) on an instance that will become a master. Through GTID replication, these operations before master-slave replication will also be replicated to the slave server, causing replication failure. That is, replication via GTID starts with the transaction log that starts first, even if these operations are performed before replication. For example, performing some drop and delete cleanup operations on server1, and then performing change operations on server2 will make server2 also perform server1 cleanup operations.

How GTID works

When a transaction is executed and committed on the main database side, a GTID is generated and recorded in the binlog log.

After the binlog is transferred to slave and stored in slave's relaylog, read the value of this GTID to set the gtid_next variable, that is, tell Slave the next GTID value to execute.

The sql thread gets the GTID from the relay log and then compares whether the binlog on the slave side has the GTID.

If there is a record, the transaction for the GTID has been executed and slave will ignore it.

If there is no record, slave executes the GTID transaction and records the GTID to its own binlog, checking that other session holds the GTID before reading the execution transaction to ensure that it is not repeated.

The construction of one master and one slave GTID replication

Host Planning:

Master:docker, port 3312

Slave:docker, port 3313

Configuration of master

The configuration file my.cnf is as follows:

$cat / home/mysql/docker-data/3313/conf/my.cnf# For advice on how to change settings please see# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html[mysqld]## Remove leading # and set to the amount of RAM for the most important data# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M## Remove leading # to turn on a very important data integrity option: logging# changes to the binary log between backups.# log_bin## Remove leading # to set options mainly useful for reporting servers.# The server defaults are faster for transactions and fast SELECTs.# Adjust sizes as needed Experiment to find the optimal values.# join_buffer_size = 128M# sort_buffer_size = 2M# read_rnd_buffer_size = 2M#datadir=/home/mysql/docker-data/3307/data#socket=/home/mysql/docker-data/3307/mysql.sockcharacter_set_server=utf8init_connect='SET NAMES utf8'# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0#log-error=/home/mysql/docker-data/3307/logs/mysqld.log#pid-file=/home/mysql/docker-data / 3307/mysqld.pidlower_case_table_names=1server-id=1403311log-bin=mysql-binbinlog-format=ROWauto_increment_increment=1auto_increment_offset=1# enable gtidgtid_mode=ONenforce-gtid-consistency=true#rpl_semi_sync_master_enabled=1#rpl_semi_sync_master_timeout=10000

Create a docker instance:

$docker run-- name mysql3312-p 3312 ti 3306-- privileged=true-ti-e MYSQL_ROOT_PASSWORD=root-e MYSQL_DATABASE=order-e MYSQL_USER=user-e MYSQL_PASSWORD=pass-v / home/mysql/docker-data/3312/conf:/etc/mysql/conf.d-v / home/mysql/docker-data/3312/data/:/var/lib/mysql-v / home/mysql/docker-data/3312/logs/:/var/log/mysql-d mysql:5.7

Add a user for replication and authorize:

Mysql > GRANT REPLICATION SLAVE,FILE,REPLICATION CLIENT ON *. * TO 'repluser'@'%' IDENTIFIED BY' 123456 configuration of query OK, 0 rows affected, 1 warning (0.01 sec) mysql > FLUSH PRIVILEGES;Query OK, 0 rows affected (0.01 sec) slave configuration

The content of the configuration file my.cnf is the same as that of master. Be careful to modify the server-id to keep it unique.

Create a docker instance:

$docker run-- name mysql3313-p 3313VR 3306-- privileged=true-ti-e MYSQL_ROOT_PASSWORD=root-e MYSQL_DATABASE=order-e MYSQL_USER=user-e MYSQL_PASSWORD=pass-v / home/mysql/docker-data/3313/conf:/etc/mysql/conf.d-v / home/mysql/docker-data/3313/data/:/var/lib/mysql-v / home/mysql/docker-data/3313/logs/:/var/log/mysql-d mysql:5.7

Enable GTID synchronization:

Mysql > change master to master_host='172.23.252.98',master_port=3310,master_user='repluser',master_password='123456',master_auto_position=1;Query OK, 0 rows affected, 2 warnings (0.02 sec) mysql > start slave;Query OK, 0 rows affected (0.02 sec)

View status:

Mysql > show master status +-+ | File | Position | Binlog_Do_ DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-- -+ | mysql-bin.000008 | 154th | cd2eaa0a-7a59-11ec-b3b4-0242ac110002:1 | +- -- + 1 row in set (0.00 sec) mysql > show slave status\ G * * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 172.23.252.98 Master_User: repluser Master_Port: 3312 Connect_Retry: 60 Master_Log_File: mysql-bin.000006 Read_Master_Log_Pos: 419 Relay_Log_File: 5dfbef024732-relay-bin.000003 Relay_Log_Pos: 632 Relay_Master_Log_File: mysql-bin.000006 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: 419 Relay_Log_Space: 846 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: 1403311 Master_UUID: cd2eaa0a-7a59-11ec-b3b4-0242ac110002 Master_Info_File: / var/lib/mysql/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: cd2eaa0a-7a59-11ec-b3b4-0242ac110002:1 Executed_Gtid_Set: cd2eaa0a-7a59-11ec-b3b4-0242ac110002:1 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version:1 row in set (0.00 sec)

Insert data into the master.order table:

Mysql > insert into t_order values (4, "V")

It is found that the data has been synchronized to slave:

Mysql > select * from order.t_order;+-+-+ | id | name | +-+-+ | 4 | V | +-+-+ 3 rows in set (0.00 sec)

Stop slave first, and then insert data into the master.order table:

Mysql > insert into t_order values (5, "X")

Then start slave and find that the data has been automatically synchronized:

Mysql > stop slave;Query OK, 0 rows affected (0.01 sec) mysql > select * from order.t_order;+-+-+ | id | name | +-+-+ | 4 | V | +-+-+ 3 rows in set (0.00 sec) mysql > start slave;Query OK, 0 rows affected (0.02 sec) mysql > select * from order.t_order +-+-+ | id | name | +-+-+ | 4 | V | | 5 | X | +-+-+ 4 rows in set (0.00 sec)

On the slave server show slave status:

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

First, check whether the server_id of master and slave are the same, and if they agree to modify the server_id field in the my.cnf file:

Mysql > show variables like 'server_id'

Then check whether the uuid of master and slave are the same:

Mysql > show variables like'% uuid%'

If uuid unanimously modifies the auto.cnf file in the data directory, copies the entire data directory, and copies the auto.cnf file, which records the uuid of the database, the uuid of each library should be different.

This is the end of the content about "how to apply GTID replication of MySQL". Thank you for reading. If you want to know more about the industry, you can follow the industry information channel. The editor will update different knowledge points for you every day.

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.