An example of changing traditional replication to GTID replication without stopping business in MySQL 5.7

An example of changing traditional replication to GTID replication without stopping business in MySQL 5.7

Due to the advantages of GTID, we need to change the traditional file-pos-based replication to GTID-based replication. How to change it online becomes a point of concern. The following is a specific method:

Currently we have a MS structure under traditional replication:

port 3301 master

port 3302 slave

On master (3301):
[zejin] 3301>select * from t_users;
+----+------+
| id | name |
+----+------+
| 1 | hao |
| 2 | zhou |
+----+------+
rows in set (0.00 sec)
 
 
On slave (3302):
[zejin] 3302>show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.240
Master_User: repl
Master_Port: 3301
Connect_Retry: 60
Master_Log_File: binlog57.000002
Read_Master_Log_Pos: 417
Relay_Log_File: zejin240-relay-bin.000004
Relay_Log_Pos: 628
Relay_Master_Log_File: binlog57.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: 417
Relay_Log_Space: 884
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: 0
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: 3301
Master_UUID: a97983fc-5a29-11e6-9d28-000c29d4dc3f
Master_Info_File: /home/mysql/I3302/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay logs; 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:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
row in set (0.00 sec)
 
[zejin] 3302>select * from t_users;
+----+------+
| id | name |
+----+------+
| 1 | hao |
| 2 | zhou |
+----+------+
rows in set (0.00 sec)

The following are the specific steps for online changes:

premise:

1. Requires all MySQL versions 5.7.6 or higher.

2. The gtid_mode value of all MySQL in the current topology is off.

3. The following steps are in order, do not skip them.

Supplement the description of the global system variable GTID_MODE variable value:

OFF New transactions are non-GTID. Slave only accepts transactions without GTID. Transactions with GTID will report an error.

OFF_PERMISSIVE New transactions are non-GTID, and the slave accepts both transactions without GTID and transactions with GTID

ON_PERMISSIVE New transactions are GTIDs, and the slave accepts both transactions without and with GTIDs.

ON New transactions are GTID, Slave only accepts transactions with GTID

It should be noted that the changes of these values ​​are in order, namely

off<--->OFF_PERMISSIVE<--->ON_PERMISSIVE<--->ON

Cannot jump to execute, will report an error.

Step 1: On each MySQL instance, set ENFORCE_GTID_CONSISTENCY to warning. It does not matter which instance executes first.

[zejin] 3302>set @@global.enforce_gtid_consistency=warn;
Query OK, 0 rows affected (0.00 sec)
[zejin] 3301>set @@global.enforce_gtid_consistency=warn;
Query OK, 0 rows affected (0.00 sec)

Note: After executing this statement, if there is any GTID-incompatible statement usage, the error log will record relevant information. Then you need to adjust the program to avoid incompatible writing until no incompatible statements are generated. You can use the program to check all SQL statements, or you can set it up and observe the error log for a period of time. This step is very important.

Step 2: On each MySQL instance, set ENFORCE_GTID_CONSISTENCY to ON. The result will not be affected by which instance executes first.

After the first step is completed, the value can be set to on.

[zejin] 3301>set @@global.enforce_gtid_consistency=on;
Query OK, 0 rows affected (0.03 sec)
 
[zejin] 3302>set @@global.enforce_gtid_consistency=on;
Query OK, 0 rows affected (0.00 sec)

Step 3: On each MySQL instance, set GTID_MODE to off_permissiv; which one executes first does not affect the result

[zejin] 3301>SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
Query OK, 0 rows affected (0.00 sec)
 
[zejin] 3302>SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
Query OK, 0 rows affected (0.00 sec)

Step 4: On each MySQL instance, set GTID_MODE to on_permissive; which one executes first does not affect the result.

[zejin] 3302>SET @@GLOBAL.GTID_MODE = on_permissive;
Query OK, 0 rows affected (0.00 sec)
[zejin] 3301>SET @@GLOBAL.GTID_MODE = on_permissive;
Query OK, 0 rows affected (0.01 sec)

Step 5: Check the variable ONGOING_ANONYMOUS_TRANSACTION_COUNT on each MySQL instance

[zejin] 3301>SHOW STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT';
+-------------------------------------+-------+
| Variable_name | Value |
+-------------------------------------+-------+
| Ongoing_anonymous_transaction_count | 0 |
+-------------------------------------+-------+
row in set (0.02 sec)
 
 
[zejin] 3302>SHOW STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT';
+-------------------------------------+-------+
| Variable_name | Value |
+-------------------------------------+-------+
| Ongoing_anonymous_transaction_count | 0 |
+-------------------------------------+-------+
row in set (0.02 sec)

You need to wait until this variable is 0

Step 6: Ensure that all anonymous transactions (non-GTID transactions) have been fully replicated to all servers.

Inspection method:

On the master:
[zejin] 3301>show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| binlog57.000005 | 154 | | | |
+-----------------+----------+--------------+------------------+-------------------+
row in set (0.00 sec)
 
 
On the slave,
 
[zejin] 3302>show slave status\G
*************************** 1. row ***************************
…
  Relay_Master_Log_File: binlog57.000005
   Exec_Master_Log_Pos: 154
…

Check that the value of Relay_Master_Log_File is greater than binlog57.000005.

Or it is equal to Relay_Master_Log_File equal to binlog57.000005 and the value of Exec_Master_Log_Pos is greater than or equal to 154

Or the slave uses the function directly:

[zejin] 3302>SELECT MASTER_POS_WAIT('binlog57.000005', 154);
+-----------------------------------------+
| MASTER_POS_WAIT('binlog57.000005', 154) |
+-----------------------------------------+
| 0 |
+-----------------------------------------+
row in set (0.00 sec)

If the returned result is greater than or equal to 0, it means that all anonymous transactions have been copied.

Step 7: Confirm that there are no anonymous transactions in the entire topology. For example, all anonymous transactions generated before have been executed, and there should be no anonymous transactions in the binary log. You can flush logs and let MySQL automatically clean up the old binary log files.

Step 8: On each MySQL instance, set GTID_MODE to on.

[zejin] 3301>SET @@GLOBAL.GTID_MODE = ON;
Query OK, 0 rows affected (0.04 sec)
 
[zejin] 3302>SET @@GLOBAL.GTID_MODE = ON;
Query OK, 0 rows affected (0.04 sec)

Step 9: Add gtid-mode=ON to the my.cnf configuration file of each MySQL instance.

verify:

[zejin] 3301>insert into t_users values(3,'chen');
Query OK, 1 row affected (0.02 sec)
[zejin] 3301>update t_users set name='li' where id=1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
[zejin] 3301>select * from t_users;
+----+------+
| id | name |
+----+------+
| 1 | li |
| 2 | zhou |
| 3 | chen |
+----+------+
rows in set (0.00 sec)
 
 
[zejin] 3302>show slave status\G
*************************** 1. row ***************************
    Slave_IO_State: Waiting for master to send event
     Master_Host: 192.168.1.240
     Master_User: repl
     Master_Port: 3301
    Connect_Retry: 60
    Master_Log_File: binlog57.000006
   Read_Master_Log_Pos: 462
    Relay_Log_File: zejin240-relay-bin.000012
    Relay_Log_Pos: 673
  Relay_Master_Log_File: binlog57.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: 462
    Relay_Log_Space: 969
    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: 0
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: 3301
     Master_UUID: a97983fc-5a29-11e6-9d28-000c29d4dc3f
    Master_Info_File: /home/mysql/I3302/master.info
     SQL_Delay: 0
   SQL_Remaining_Delay: NULL
  Slave_SQL_Running_State: Slave has read all relay logs; 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: a97983fc-5a29-11e6-9d28-000c29d4dc3f:1-2
   Executed_Gtid_Set: a97983fc-5a29-11e6-9d28-000c29d4dc3f:1-2
    Auto_Position: 0
   Replicate_Rewrite_DB: 
     Channel_Name: 
   Master_TLS_Version: 
row in set (0.00 sec)

This completes the online conversion from traditional replication to GTID replication.

The above example of changing traditional replication to GTID replication without stopping MySQL 5.7 business is all I want to share with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.

You may also be interested in:
  • Detailed introduction to GTID mode of MySQL master-slave replication
  • MYSQL database GTID realizes master-slave replication (super convenient)
  • Solution to the problem that synchronous replication errors cannot be skipped in MySQL5.6 GTID mode
  • Detailed explanation of MySQL master-slave replication practice - GTID-based replication
  • MySQL 5.6 master-slave replication based on GTID
  • Tutorial on using GTIDs replication protocol and outage protocol in MySQL
  • Specific use of GTID replication in MySQL replication

<<:  SSH port forwarding, local port forwarding, remote port forwarding, dynamic port forwarding details

>>:  JavaScript to achieve fixed sidebar

Recommend

Use javascript to create dynamic QQ registration page

Table of contents 1. Introduction 1. Basic layout...

Summary of the differences between global objects in nodejs and browsers

In Node.js, a .js file is a complete scope (modul...

Sample code for a simple seamless scrolling carousel implemented with native Js

There are many loopholes in the simple seamless s...

About IE8 compatibility: Explanation of the X-UA-Compatible attribute

Problem description: Copy code The code is as fol...

Introduction to Linux system swap space

Swap space is a common aspect of computing today,...

Using System.Drawing.Common in Linux/Docker

Preface After the project is migrated to .net cor...

docker compose idea CreateProcess error=2 The system cannot find the specified file

Docker Compose Introduction to Compose Compose is...

How to use SessionStorage and LocalStorage in Javascript

Table of contents Preface Introduction to Session...

How to solve the problem of zabbix monitoring causing Chinese garbled characters in the graphical interface due to PHP problems

Solve the problem of Chinese garbled characters i...

Native JavaScript to achieve skinning

The specific code for implementing skinning with ...

How to install MySql in CentOS 8 and allow remote connections

Download and install. First check whether there i...

How to import and export Cookies and Favorites in FireFox

FireFox is a commonly used browser with many exte...

Detailed explanation of JS memory space

Table of contents Overview 1. Stack and Heap 2. V...

Detailed explanation of Docker container network port configuration process

Exposing network ports In fact, there are two par...

js to implement file upload style details

Table of contents 1. Overview 2. Parameters for c...