Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.

Gtid-based replication

Introduction to the

Gtid-based replication is new in MySQL 5.6.

The Global Transaction Identifier (GTID) is a global transaction ID that ensures that each transaction committed on the master database has a unique ID in the cluster.

In the original log-based replication, the slave needed to tell the master which offset to perform incremental synchronization from, and if specified incorrectly, the data would be missed, resulting in inconsistencies.

In a GTID-based replication, the slave tells the master the VALUE of the GTID for the transactions that have been executed, and the master returns the list of GtiDs for all unexecuted transactions to the slave. And the same transaction can be guaranteed to be executed only once in the specified slave library.

The GTID configuration is as follows:

SHOW VARIABLES LIKE '%GTID%';
Copy the code

Results:

 

 

In actual combat

Create a replication account on the master database and grant permissions

Gtid-based replication will automatically replay transactions that are not executed in the slave library, so do not set up the same account on other slave libraries. If the same account is set up, replication link errors may occur.

Note that passwords on production must conform to specifications to achieve a certain password strength and specify that the master library can only be accessed on a specific network segment on the slave library.

To view the user

Check the authorization

2. Configure the master library server

NOTE: It’s a good practice to separate logs from data, preferably in separate data partitions

Enforce_gtid_consistency enforces the CONSISTENCY of the GTID. The following commands are no longer available when enforce_gtid_consistency is enabled:

1、create table … select …

Because they are really two independent events, you can only split them up and create the table first, then insert the data into the table.

2. Cannot create a temporary table inside a transaction

mysql> begin; Query OK, 0 rows affected (0.00 SEC) mysql> create temporary table dept(id int); Error code:  1787 When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, the statements CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can be executed in a non-transactional context only, and require that AUTOCOMMIT = 1. These statements are also not allowed in a function or trigger because functions and triggers are also considered to be multi-statement transactions.Copy the code

As mentioned in the above error, when a transaction is committed automatically, it can be executed as follows:

Update transaction tables and non-transaction tables in the same transaction (MyISAM)

mysql> CREATE TABLE `dept_innodb` (id INT(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT); Query OK, Mysql > CREATE TABLE 'dept_myISam' (id INT(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT) ENGINE = `MyISAM`; Query OK, 0 rows affected (0.03 SEC) mysql> begin; Query OK, 0 rows affected (0.00 SEC) mysql> insert into dept_innodb(id) value(1); Query OK, 1 row affected (0.00 SEC) mysql> insert into dept_myisam(id) value(1); ERROR 1785 (HY000): Statement violates GTID consistency: Updates to non-transactional tables can only be done in either autocommitted statements or single-statement transactions, and never in the same statement as updates to transactional tables.Copy the code

Therefore, Innodb is recommended as the default database engine.

Log_slave_updates This option is required for GTId-based replication in MySQL 5.6, but increases the IO load of the slave server, and is not required in MySQL 5.7.

3. Configure the slave server

Master_info_repository and relay_log_info_repository

Before MySQL 5.6.2, slave master information and slave application binlog information were stored in files, namely master.info and relay log.info. After version 5.6.2, recording to a table is allowed. The corresponding tables are mysql.slave_master_info and mysql.slave_relay_log_info, and these two tables are innoDB engine tables.

4. Initialize data from library – [optional]

Start by backing up data on the primary repository

mysqldump --single-transaction --master-data=2 --triggers --routines --all-databases --events -u root -p > backup.sql
Copy the code

– master-data=2 This option appends the current server’s binlog location and file name to the output file (show master status). If 1, concatenate the offset to the CHANGE MASTER command. If 2, the output offset information will be commented out.

Databases Because gTID-based replicates record all transactions, it is recommended to build a full dump

Common mistakes

Occurs when SQL is imported from the library

ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
Copy the code

MySQL Command Line reset master MySQL Command Line reset master

5. Start replication based on GTID

[email protected] and [email protected] are available, and data has been synchronized to slave via mysqldump. Now configure the replication link on slave

Start replication

Check the slave status after the slave is successfully started

When Slave_IO_Running, Slave_SQL_Running is YES,

Slave_SQL_Running_State is Slave has read all relay log. Replication links are successfully created after waiting for more updates.

6, summary

advantages

  1. Because the log offset is not manually set, failover is easy
  2. If log_slave_updates is enabled, the slave library will not lose any changes to the master library

disadvantages

    1. There are restrictions on the SQL that can be executed
    2. Only versions later than MySQL 5.6 are supported, and earlier versions are not recommended