Shulou(Shulou.com)06/01 Report--

Switching from traditional replication to GTID replication

Master / slave library execution

1.SET @ @ GLOBAL.ENFORCE_GTID_CONSISTENCY = WARN

Keep watching the MySQL error log for a period of time to see if there are any errors that violate gtid consistency, such as create table. The statement of select is not supported. At this time, we can only make business adjustments to meet the requirements of gtid consistency, and only when all the statements are met can we continue to take a step.

Master / slave library execution

2.SET @ @ GLOBAL.ENFORCE_GTID_CONSISTENCY = ON

Master / slave library execution

3.SET @ @ GLOBAL.GTID_MODE = OFF_PERMISSIVE

The new generation is anonymous transaction, and slave can recognize anonymous transaction and gtid transaction.

Master / slave library execution

4.SET @ @ GLOBAL.GTID_MODE = ON_PERMISSIVE

The new generation is gtid transaction. Slave can identify anonymous transaction and gtid transaction. To make sure that no anonymous transaction is generated, check the following methods:

SHOW STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT'

A value of 0 indicates that the operation can be performed.

Master / slave library execution

5.SET @ @ GLOBAL.GTID_MODE = ON

The new generation is the gtid transaction, and the slave only recognizes the gtid transaction. After this step, the master / slave opens the gtid. To configure the restart to continue to take effect, write two parameters to the configuration file of MySQL:

Enforce_gtid_consistency=on

Gtid_mode=on

Execute from the library

STOP SLAVE [FOR CHANNEL 'channel']

CHANGE MASTER TO MASTER_AUTO_POSITION = 1 [FOR CHANNEL 'channel']

START SLAVE [FOR CHANNEL 'channel']

II. Switching from GTID replication to traditional replication

Execute from the library

1. STOP SLAVE [FOR CHANNEL 'channel']

After waiting for this step, you need to check the files and locations currently read by slave, and traditional replication needs to continue to synchronize based on these two values. The way to view it is to look at the two parameters in show slave status\ G:

Relay_Master_Log_File:file # indicates which file sql_thread has been applied to

Exec_Master_Log_Pos:position # indicates where the sql_thread has been executed in the file

2.CHANGE MASTER TO MASTER_AUTO_POSITION = 0, MASTER_LOG_FILE = file,\

MASTER_LOG_POS = position [FOR CHANNEL 'channel']

3.START SLAVE [FOR CHANNEL 'channel']

Master-slave library execution

4.SET @ @ GLOBAL.GTID_MODE = ON_PERMISSIVE;.

Master-slave library execution

5.SET @ @ GLOBAL.GTID_MODE = OFF_PERMISSIVE

After performing this step, you need to confirm that no new gtid transactions have been generated.

The method on the official documentation: SELECT @ @ GLOBAL.GTID_OWNED; just make sure that a value on the master and slave is empty.

You can also judge whether the two parameters above are no longer updated:

Retrieved_Gtid_Set: # the gtid collection that has been obtained

Executed_Gtid_Set: # collections of gtid that have been executed

Master-slave library execution

6.SET @ @ GLOBAL.GTID_MODE = OFF

Master-slave library execution

7. Write the configuration to the configuration file

Enforce_gtid_consistency=off

Gtid_mode=off

Third, how to skip a transaction based on GTID replication

GTID-based replication cannot skip errors using sql_slave_skip_counter and can be handled by replacing empty transactions.

Execute from the library:

1. Stop slave and inject an empty transaction

STOP SLAVE [FOR CHANNEL 'channel']

SET GTID_NEXT='aaa-bbb-ccc-ddd:N'; # gtid of the transaction to be skipped

BEGIN

COMMIT

SET GTID_NEXT='AUTOMATIC'

START SLAVE [FOR CHANNEL 'channel']

Execute from the library

two。 Refresh and clean up binlog files

FLUSH LOGS; # generates a new binlog file

The PURGE BINARY LOGS TO 'master-bin.00000N'; # N table is the latest binlog file

The intention is to prevent the problem transaction from propagating when slave is upgraded to master in the future.

4. Switch slave to Master based on GTID replication

1. Ensure that there are no more data updates on the master

Flush table with read lock; # session level, disconnect the session is invalid.

Or

Set global read_only=1; # restart failure

Set global super_read_only=1

two。 Check whether the master and slave are completely synchronized.

Master:show master status\ G

Slave:show slave status\ G

3. Slave library (new master library)

STOP SLAVE

RESET SLAVE ALL

4. Master library (new slave library)

CHANGE MASTER TO MASTER_AUTO_POSITION = 0, MASTER_LOG_FILE = file,\

MASTER_LOG_POS = position [FOR CHANNEL 'channel']

Write at the end:

This article is just a summary of the official documents and the process you are using. If you are good at English, you can read the official documents directly. If you write something wrong, you are welcome to complain. Gtid-based replication is easy to maintain, and you no longer need to care about binlog file and position. Slave will report to Master that the gtid,Master it has executed will send the remaining gtid to Slave for execution. In order to prevent the data inconsistency between master and slave caused by misoperation on slave, it is best to set it to read-only, and add two parameters to the configuration file of Slave:

Super_read_only=1

Read_only=1

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.