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

How to completely uninstall Docker Toolbox

Docker Toolbox is a solution for installing Docke...

A detailed tutorial on master-slave replication and read-write separation of MySQL database

Table of contents Preface 1. MySQL master-slave r...

5 ways to quickly remove the blank space of Inline-Block in HTML

The inline-block property value becomes very usef...

Web2.0: Causes and Solutions of Information Overload

<br />Information duplication, information o...

Before making a web page, let’s take a look at these so-called specifications

This article has compiled some so-called specific...

Native js to implement form validation function

Table of contents When developing, analyzing the ...

Detailed tutorial on how to compile and install mysql8.0.29 in CentOS8 deployment LNMP environment

1. Prerequisites Since I have installed it severa...

Explanation of MySQL index types Normal, Unique and Full Text

MySQL's index types include normal index, uni...

HTML blockquote tag usage and beautification

Blockquote Definition and Usage The <blockquot...

20 excellent foreign web page color matching cases sharing

This article collects 20 excellent web page color ...

Summary and practice of javascript prototype chain diagram

Table of contents Prototype chain We can implemen...

A brief discussion on whether CSS animation will be blocked by JS

The animation part of CSS will be blocked by JS, ...

Solution to the IP address not being displayed under Linux

Table of contents Preface Solution: Step 1 Step 2...

mysql5.7.17.msi installation graphic tutorial

mysql-5.7.17.msi installation, follow the screens...

React example showing file upload progress

Table of contents React upload file display progr...