MySQL master-slave replication practice-GTID-based replication code sharing

This article mainly introduces MySQL Master-slave replication practice - GTID-based replication. GTID-based replication is a new replication method after MySQL 5.6. Those who are interested can learn more.

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 submitted on the main database has a unique ID in the cluster.

In the original log-based replication , the slave library needs to inform the master library from which offset to perform incremental synchronization. If the specified error is specified, data will be omitted, resulting in data inconsistency.

In GTID-based replication, the slave library will inform The GTID value of the transaction that has been executed by the main library, and then the main library will return the list of GTIDs of all unexecuted transactions to the slave library. And it can ensure that the same transaction is only executed once in the specified slave library.

Practice

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

GTID-based replication will automatically copy the data from the slave database to the slave database. The executed transaction is replayed, so do not create the same account on other slave libraries. If the same account is created, it may cause replication link errors.

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

Note that the password in production must be in accordance with the relevant Standardize to achieve a certain password strength, and stipulate that the main database can only be accessed on a specific network segment on the slave database.

mysql> grant replication slave on *.* to 'repl'@'172.%';

View user

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 main database 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 and data. It is best to put them in different data partitions

## enforce_gtid_consistency Force GTID consistency, after enabling, the following commands can no longer be used

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, so you can only split it to create a table first, and then insert the data into the table

create temporary table

Temporary tables cannot be created within 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.

In the same transaction

UpdateTransaction table and non-transaction table (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.

So 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 version. However, it increases the IO load of the slave server, and this option is no longer required in MySQL 5.7

3. Configure the slave server

master_info_repository and relay_log_info_repository

Before MySQL 5.6.2, the master information recorded by the slave and the slave application binlog information were stored in files, namely master.info and relay-log.info. After version 5.6.2, recording to the table is allowed . The corresponding tables are mysql.slave_master_info and mysql.slave_relay_log_info, 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. Initialization of slave data - [optional]

Back up the data on the master library first

The 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 appends the location and file name of the binlog of the current server to the output file (show master status). If it is 1, the offset will be spliced ​​into the CHANGE MASTER command. If it is 2, the output offset information will be commented.

--all-databases Because GTID-based replication will record all transactions, this option is recommended to build a complete dump

Common mistakes

When importing SQL from the database, the code appears as follows:

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

At this time, enter the MySQL Command Line from the database and use reset master

5. Start GTID-based replication

Existing master@172.20.0.2 and slave@172.20.0.3, and the data has been synchronized to the slave database slave through mysqldump. Now on the slave server slave Configure the replication link

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;

After successful startup, check the

status of the slave

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: 5096
Master_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,

And Slave_SQL_Running_State is Slave has read all relay log; waiting for more updates, it means the replication link is successfully built

6. Summary

Advantages

Because there is no need to manually set the log offset, failover can be easily performed

1. If log_slave_updates is enabled, the slave database will not Any modifications on the main database will be lost
Disadvantages

##There are certain restrictions on the executed SQL

1. Only supports versions after MySQL 5.6, and it is not recommended to use earlier 5.6 versions

The above is the detailed content of MySQL master-slave replication practice-GTID-based replication code sharing. For more information, please follow other related articles on the PHP Chinese website!