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,
offb6fe9feb9d4c7afa9e35009a988b8cc2OFF_PERMISSIVEb6fe9feb9d4c7afa9e35009a988b8cc2ON_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!