Detailed explanation of the sample code for changing traditional replication to GTID replication in MySQL 5.7 non-stop business

The following editor will bring you an example of MySQL5.7 changing traditional replication to GTID replication for non-stop business. The editor thinks it is quite good, so I will share it with you now and give it as a reference for everyone. Let’s follow the editor and take a look.

Due to the advantages of GTID, we need to change the traditional file-pos-based replication to GTID-based replication. How to change online has become a point of concern to us. The details are as follows Method:

Currently we have an M-S structure under traditional replication:

port 3301 master

port 3302 slave

master上（3301）：
[zejin] 3301>select * from t_users;
+----+------+
| id | name |
+----+------+
| 1 | hao |
| 2 | zhou |
+----+------+
rows in set (0.00 sec)


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 log; 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:

Prerequisite :

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 operation steps are all in order, do not jump ahead.

Add global System variablesGTID_MODE variable value description:

OFF The new transaction is non-GTID, Slave Only transactions without GTID are accepted. Transactions sent with GTID will report an error.

OFF_PERMISSIVE The new transaction is non-GTID. Slave accepts both transactions without GTID and transactions with GTID.

ON_PERMISSIVE New transaction is GTID, Slave accepts both transactions without GTID and transactions with GTID

ON The new transaction is GTID, Slave only accepts transactions with GTID

Need to pay attention What's interesting is that the changes in these values ​​are in order, that is,

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

cannot jump to execution and will report an error.

#step1:On each mysql instance, set ENFORCE_GTID_CONSISTENCY to warning. Which one is executed first does not affect the result.

[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 a GTID incompatible statement usage, the relevant information will be recorded in the error log, then the program needs to be adjusted to avoid incompatible writing, Until no incompatible statements are generated, You can check all SQLs through the program, or you can observe the error log for a period of time after setting. This step is very important.

step2: On each mysql instance, set ENFORCE_GTID_CONSISTENCY to ON. Which one is executed first does not affect the result.

Complete in the first step After that, you can set the value 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)

step3:On each mysql instance, set GTID_MODE to off_permissiv; which one is executed 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)

step4:In On each mysql instance, set GTID_MODE to on_permissiv;; which one is executed 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)

step5:Check the variable ONGOING_ANONYMOUS_TRANSACTION on each mysql instance _COUNT

[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

step6: Ensure that all anonymous transactions (non-GTID transactions) have been completely replicated to all servers.

Checking method:

在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)


在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 these two Relay_Master_Log_File is greater than binlog57.000005,

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

Or the slave directly uses the function:

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

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

step7: Confirm that there are no anonymous transactions in the entire topology. For example, all previously generated anonymous transactions have been completed. Even if there are no anonymous transactions in the binary log, you can pass flush logs and let mysql to automatically clean up old binary log files.

#step8: 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)

#step9: On each mysql instance In the configuration file my.cnf, add gtid-mode=ON

Verification:

[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 log; 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 is the detailed content of Detailed explanation of the sample code for changing traditional replication to GTID replication in MySQL 5.7 non-stop business. For more information, please follow other related articles on the PHP Chinese website!