Detailed explanation of MySQL master slave replication replication based on GTID
- 2021-07-22 11:45:34
- OfStack
Replication based on GTID
Brief introduction
GTID-based replication is a new replication mode after MySQL 5.6.
GTID (global transaction identifier) is the global transaction ID, which guarantees that every transaction committed on the main library has a uniquely 1 ID in the cluster.
In the original log-based replication, the slave repository needs to tell the master repository which offset to synchronize incrementally. If the error is specified, the data will be omitted, resulting in data inconsistency.
In GTID-based replication, the slave repository tells the master repository the GTID value of the transactions that have been executed, and then the master repository returns the list of GTID of all transactions that have not been executed to the slave repository, and can guarantee that the same transaction is executed only once in the specified slave repository.
Actual combat
1. Establish a replication account on the main library and grant permissions
Replication based on GTID automatically replays transactions that are not executed in the slave repository, so do not create the same account on other slave repositories. If the same account is created, it may cause replication link errors.
mysql> create user 'repl'@'172.%' identified by '123456';Note that the production password must be in accordance with the relevant specifications to achieve a certain password strength, and it is stipulated that the master library can only be accessed on a specific network segment on the slave library.
mysql> grant replication slave on *.* to 'repl'@'172.%';View Users
mysql> select user, host from mysql.user;
+-----------+-----------+
| user | host |
+-----------+-----------+
| prontera | % |
| root | % |
| mysql.sys | localhost |
| root | localhost |
+-----------+-----------+
4 rows in set (0.00 sec)View Authorization
mysql> show grants for repl@'172.%';
+--------------------------------------------------+
| Grants for repl@172.% |
+--------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.%' |
+--------------------------------------------------+
1 row in set (0.00 sec)2. Configure the master library server
[mysqld]
log_bin = /var/log/mysql/mysql-bin
log_bin_index = /var/log/mysql/mysql-bin.index
binlog_format = row
server_id = 101
gtid_mode = ON
enforce_gtid_consistency = ON
#log_slave_updates = ON
NOTE: It is a good habit to separate logs from data, preferably in different data partitions
enforce_gtid_consistency Force GTID1 Attempt, the following command can no longer be used when enabled
create table ... select ...
mysql> create table dept select * from departments;
ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.Because they are actually two independent events, you can only split them to create a table and then insert the data into the table
create temporary table
Temporary table cannot be created inside a transaction
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> create temporary table dept(id int);
ERROR 1787 (HY000): Statement violates GTID consistency: CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can only be executed outside transactional context. These statements are also not allowed in a function or trigger because functions and triggers are also considered to be multi-statement transactions.
Update transaction table and non-transaction table in the same 1 transaction (MyISAM)
mysql> CREATE TABLE `dept_innodb` (id INT(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT);
Query OK, 0 rows affected (0.04 sec)
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.
Therefore, it is recommended to choose Innodb as the default database engine.
log_slave_updates This option is required for GTID-based replication in MySQL 5.6, but it increases the IO load on the slave server, which is no longer required in MySQL 5.7
3. Configure the slave library server
master_info_repository and relay_log_info_repository
Before MySQL 5.6. 2, master information recorded by slave and slave information applied to binlog were stored in files, i.e. master. info and relay-log. info. After version 5.6. 2, table is allowed to be recorded. The corresponding tables are mysql.slave_master_info and mysql.slave_relay_log_info, respectively, and both tables are innodb engine tables.
[mysqld]
log_bin = /var/log/mysql/mysql-bin
log_bin_index = /var/log/mysql/mysql-bin.index
server_id = 102
# slaves
relay_log = /var/log/mysql/relay-bin
relay_log_index = /var/log/mysql/relay-bin.index
relay_log_info_file = /var/log/mysql/relay-bin.info
enforce_gtid_consistency = ON
log_slave_updates = ON
read_only = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE
4. Initialize from library data-[optional]
Back up the data on the main library first
mysqldump --single-transaction --master-data=2 --triggers --routines --all-databases --events -u root -p > backup.sql
-master-data=2 This option appends the location and filename of the current server's binlog to the output file (show master status). If 1, splice the offset to the CHANGE MASTER command. If 2, the output offset information will be annotated.
--all-databases This option is recommended to build a full dump because replication based on GTID logs all transactions
Common errors
Occurs when SQL is imported from a library
ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
Enter MySQL Command Line of the slave library and use reset master
5. Start replication based on GTID
master @ 172.20. 0.2 and slave @ 172.20. 0.3 exist, and data has been synchronized to slave library slave via mysqldump. Replication links are now configured on slave server slave
mysql> change master to master_host='master', master_user='repl', master_password='123456', master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.06 sec)Start replication
mysql> grant replication slave on *.* to 'repl'@'172.%';View the status of slave after successful startup
mysql> grant replication slave on *.* to 'repl'@'172.%';When Slave_IO_Running and Slave_SQL_Running are YES,
And Slave_SQL_Running_State is Slave has read all relay log; waiting for more updates indicates successful replication link construction
6. Summary
Advantages
Because you don't have to manually set the log offset, you can easily fail over If log_slave_updates is enabled, the slave library will not lose any modifications on the master library
Disadvantages