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

This article mainly introduces the MySQL5.7 traditional fast switching GTID method, the content of the article is carefully selected and edited by the author, has a certain pertinence, and is of great significance to everyone's reference, so let's learn about the traditional fast switching GTID method of MySQL5.7 with the author.

Current scene:

GTID service groups have not been enabled in some business scenarios. In the latest version, BINLOG group submission is also based on GTID, so how to detect whether the conditions for enabling GTID are met, how to switch to use GTID online, and how to roll back quickly:

New options for gtid_mode parameters: MySQL provides two additional options off_permissive and on_permissive

Several status descriptions of gtid-mode:

Off: no gtid is generated, and gtid logs cannot be accepted based on binlog+position,slave

Off_permissive: does not generate gtid, but as a slave can identify gtid transactions as well as non-gtid transactions

On_permissive: generate gtid transactions, and slave can handle gtid transactions and non-gtid transactions

On: generate gtid transactions, and slave only accepts gtid transactions

Three restrictions on GTID

When enforce-gtid-consistency=ON, the following three types of statements are not supported

CREATE TABLE... SELECT statements

CREATE TEMPORARY TABLE or DROP TEMPORARY TABLE statements inside transactions

Transactions or statements that update both transactional and nontransactional tables. There is an exception that nontransactional DML is allowed in the same transaction or in the same statement as transactional DML, if all nontransactional tables are temporary.

In fact, this restriction is not necessary to be so strict.

CREATE TABLE... SELECT statements

For binlog_format=row, you can release the restrictions when you gtid_next='automatic'.

The generated binlog contains two GTID, one is a table statement, and the other is a transaction containing multiple insert.

Transactions contain transaction tables and non-transaction tables

For gtid_next='automatic', you can let go of the restrictions.

The generated binlog contains two GTID, one for all non-transactional tables and one for all transactional tables.

Additional binlog_format=row requirements are required for update multiple tables (including transactional and non-transactional tables) at this time.

The process of enabling GTID online is as follows:

Execute on all server of ①

Set @ @ global.enforce_gtid_consistency = warn; pay special attention: this step is a step for Guanjian to use without warning. Statements that do not match will be recorded in the error log

Execute on all server of ②:

Set @ @ global.enforce_gtid_consistency = on

Execute on all Server of ③ (don't care about first and last, but finish):

Set @ @ global.gtid_mode = off_permissive

Execute on all server of ④:

Set @ @ global.gtid_mode=on_permissive

In fact, the logs generated in this step are all logs with GTID. This step is claimed to care nothing about any node, but it is recommended to execute it on slave first and then on master.

⑤ confirms that the traditional binlog has been copied, with a value of 0

Show status like 'ongoing_anonymous_transaction_count'

All nodes judge that show status like 'ongoing_anonymous_transaction_count'; is zero.

⑥ all nodes execute: flush logs; is used to switch logs.

All nodes in ⑦ enable gtid_mode

Set @ @ global.gtid_mode=on

⑧ enables automatic lookup node replication for Gtid:

Stop slave

Change master to master_auto_position=1

Start slave

⑨ writes the configuration of gtid_mode = on to the configuration file

Gtid_mode=on

Enforce_gtid_consistency=on

Finally, you can see a change in traditional replication, observed through BINLOG:

# 171116 19:16:57 server id 2 end_log_pos 16188 CRC32 0x75584212 Anonymous_GTIDlast_committed=50sequence_number=51

# 171116 19:17:03 server id 2 end_log_pos 16522 CRC32 0x3b42637e Anonymous_GTIDlast_committed=51sequence_number=52

# 171116 19:19:54 server id 2 end_log_pos 16851 CRC32 0x08383e43 Anonymous_GTIDlast_committed=52sequence_number=53

# 171116 19:19:59 server id 2 end_log_pos 17185 CRC32 0xd3445edc Anonymous_GTIDlast_committed=53sequence_number=54

# 171116 19:21:06 server id 2 end_log_pos 17514 CRC32 0xcfd6ac97 Anonymous_GTIDlast_committed=54sequence_number=55

# 171116 19:21:11 server id 2 end_log_pos 17802 CRC32 0xee11dc12 Anonymous_GTIDlast_committed=55sequence_number=56

GTID replication data:

# 171117 13:40:00 server id 2 end_log_pos 456322 CRC32 0xf7074b53 GTIDlast_committed=1036sequence_number=1132

# 171117 13:40:00 server id 2 end_log_pos 456725 CRC32 0xcb86f0b2 GTIDlast_committed=1036sequence_number=1133

# 171117 13:40:00 server id 2 end_log_pos 457128 CRC32 0x8b14303e GTIDlast_committed=1036sequence_number=1134

# 171117 13:40:00 server id 2 end_log_pos 457533 CRC32 0x4f492e18 GTIDlast_committed=1036sequence_number=1135

# 171117 13:40:00 server id 2 end_log_pos 457940 CRC32 0xf0b078b6 GTIDlast_committed=1036sequence_number=1136

# from GTID mode to traditional mode:

① at SLAVE:

Stop slave

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State:

Master_Host: 192.168.1.130

Master_User: dlan

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000010

Read_Master_Log_Pos: 458282

Execute simultaneously:

Change master to master_auto_position=0,MASTER_LOG_FILE='mysql-bin.000010',MASTER_LOG_POS=458282

Start slave

② executes on MS:

SET @ @ GLOBAL.GTID_MODE = ON_PERMISSIVE

③ executes on MS

SET @ @ GLOBAL.GTID_MODE = OFF_PERMISSIVE

④ executes on MS

SELECT @ @ GLOBAL.GTID_OWNED; # # is all empty.

⑤ shuts down GTID

SET @ @ GLOBAL.GTID_MODE = OFF

Finally, the observation is both GTID and anonymous:

# 171117 14:32:38 server id 2 end_log_pos 319730 CRC32 0xc26f13a7 Anonymous_GTID last_committed=699sequence_number=793

# 171117 14:32:38 server id 2 end_log_pos 320133 CRC32 0xf480c415 Anonymous_GTIDlast_committed=699sequence_number=794

# 171117 14:32:38 server id 2 end_log_pos 320538 CRC32 0xa65ed8f1 Anonymous_GTIDlast_committed=699sequence_number=795

# 171117 14:32:38 server id 2 end_log_pos 320945 CRC32 0xb7330b5e Anonymous_GTIDlast_committed=699sequence_number=796

# 171117 14:32:38 server id 2 end_log_pos 321348 CRC32 0x619e04bb Anonymous_GTIDlast_committed=699sequence_number=797

# 171117 14:32:38 server id 2 end_log_pos 321751 CRC32 0x250f903c Anonymous_GTIDlast_committed=699sequence_number=798

# 171117 14:32:38 server id 2 end_log_pos 322156 CRC32 0x247a96f9 Anonymous_GTIDlast_committed=699sequence_number=799

After reading the above about MySQL5.7 traditional fast switching GTID method, many readers must have some understanding. If you need to get more industry knowledge and information, you can continue to follow our industry information column.

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.