Detailed description of MySQL master-slave replication practices-GTID-based replication, mysqlgtid

Detailed description of MySQL master-slave replication practices-GTID-based replication, mysqlgtid

GTID-based Replication

Introduction

GTID-based replication is a new replication method after MySQL 5.6.

GTID (global transaction identifier) is the global transaction ID, which ensures that each transaction committed on the master database has a unique ID in the cluster.

In log-based replication, the slave database must inform the master database of the Offset from which to perform incremental synchronization. If an error is specified, data omission may occur, resulting in data inconsistency.

In GTID-based replication, the slave database informs the master database of the GTID value of the transaction that has been executed, and then the master database returns the GTID list of all unexecuted transactions to the slave database. the same transaction can be executed only once in the specified slave database.

Practice

1. Create a copy account on the master database and grant permissions

GTID-based replication automatically replays transactions not executed in the slave database, so do not create the same account on other slave databases. if the same account is created, the replication link may be incorrect.

mysql> create user 'repl'@'172.%' identified by '123456';

Note that the password in production must comply with the relevant specifications to achieve a certain password strength, and requires that the master database can be accessed on a specific network segment of the slave database.

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 database server

[mysqld]log_bin = /var/log/mysql/mysql-binlog_bin_index = /var/log/mysql/mysql-bin.indexbinlog_format = rowserver_id = 101gtid_mode = ONenforce_gtid_consistency = ON#log_slave_updates = ON

NOTE: separating logs from data is a good habit. It is best to put logs in different data partitions.

Enforce_gtid_consistencyEnforce GTID consistency. The following command cannot be used after it is enabled

Create table... select...

mysql> create table dept select * from departments;ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.

Because it is actually two independent events, you can only split it, create a table, and then insert data into the table.

Create temporary table

A temporary table cannot be created in the 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 the transaction table and non-transaction table (MyISAM) in the same transaction)

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, Innodb is recommended 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. in MySQL 5.7, this option is no longer required.

3. Configure the slave Database Server

Master_info_repository and relay_log_info_repository

Before MySQL 5.6.2, the master information recorded by the slave and the binlog information of the slave application are stored in the file, that is, master.info and relay-log.info. versions 5.6.2 and later are allowed to be recorded in the table. the corresponding tables are mysql. slave_master_info and mysql. slave_relay_log_info. Both tables are innodb Engine tables.

[mysqld]log_bin = /var/log/mysql/mysql-binlog_bin_index = /var/log/mysql/mysql-bin.indexserver_id = 102# slavesrelay_log  = /var/log/mysql/relay-binrelay_log_index = /var/log/mysql/relay-bin.indexrelay_log_info_file = /var/log/mysql/relay-bin.infoenforce_gtid_consistency = ONlog_slave_updates = ONread_only = ONmaster_info_repository = TABLErelay_log_info_repository = TABLE

4. initialize slave database data-[optional]

Back up data on the master database first

Copy codeThe Code is as follows:
Mysqldump -- single-transaction -- master-data = 2 -- triggers -- routines -- all-databases -- events-u root-p> backup. SQL

-Master-data = 2 this option adds the location and file name of the binlog of the current server to the output file (show master status ). if the value is 1, the offset is spliced to the change master command. if it is 2, the output offset will be commented out.

-- All-databases because GTID-based replication records all transactions, it is recommended to build a complete dump option.

Common Errors

When the SQL statement is imported from the database

Copy codeThe Code is as follows:
ERROR 1840 (HY000) at line 24: @ GLOBAL. GTID_PURGED can only be set when @ GLOBAL. GTID_EXECUTED is empty.

Enter the MySQL Command Line of the slave database and use reset master.

5. Start GTID-based Replication

Existing master@172.20.0.2 and slave@172.20.0.3, and data has been synchronized to slave database slave via mysqldump. Now configure replication link 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> start slave;

View the status of slave after successful startup

mysql> show slave status\G*************************** 1. row ***************************    Slave_IO_State: Queueing master event to the relay log     Master_Host: master     Master_User: repl     Master_Port: 3306    Connect_Retry: 60    Master_Log_File: mysql-bin.000002   Read_Master_Log_Pos: 12793692    Relay_Log_File: relay-bin.000002    Relay_Log_Pos: 1027  Relay_Master_Log_File: mysql-bin.000002    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: 814    Relay_Log_Space: 12794106    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: 5096Master_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: 101     Master_UUID: a9fd4765-ec70-11e6-b543-0242ac140002    Master_Info_File: mysql.slave_master_info     SQL_Delay: 0   SQL_Remaining_Delay: NULL  Slave_SQL_Running_State: Reading event from the relay log   Master_Retry_Count: 86400     Master_Bind:  Last_IO_Error_Timestamp:  Last_SQL_Error_Timestamp:    Master_SSL_Crl:   Master_SSL_Crlpath:   Retrieved_Gtid_Set: a9fd4765-ec70-11e6-b543-0242ac140002:1-39   Executed_Gtid_Set: a9fd4765-ec70-11e6-b543-0242ac140002:1-4    Auto_Position: 1   Replicate_Rewrite_DB:     Channel_Name:   Master_TLS_Version:1 row in set (0.00 sec)

When Slave_IO_Running, Slave_ SQL _Running is YES,

The Slave_ SQL _Running_State is Slave has read all relay log. waiting for more updates indicates that the replication link is successfully constructed.

6. Summary

Advantages

1. Because you do not need to manually set the log offset, you can easily perform failover
2. If log_slave_updates is enabled, the slave database will not lose any modifications to the master database.

Disadvantages

1. Restrictions on SQL Execution
2. Only Versions later than MySQL 5.6 are supported, and earlier versions 5.6 are not recommended.

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.