MySQL5.7 legacy replication to Gtid replication

Current scene:

Some business scenarios have not yet turned on the Gtid Service group, and in the latest version, Binlog Group submissions are also based on Gtid, so how to detect compliance with the on-gtid condition, online switching using Gtid, and how to quickly rollback:

Gtid_mode parameters new option: MySQL offers two additional options off_permissive and on_permissive

Several status descriptions for Gtid-mode:
off: Does not produce Gtid, based on Binlog+position,slave also can not accept Gtid log;
off_permissive: Does not produce gtid, but as slave can identify Gtid transactions can also identify non-gtid transactions;
on_permissive: Generates Gtid transactions, slave can handle gtid and non-gtid transactions;
On : Generates Gtid transactions, slave only accepts Gtid transactions

Gtid Three Limits

When Enforce-gtid-consistency=on, the following three types of statements are not supported

• create TABLE ... Select statements

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

and actually this restriction is not necessarily so strict,

• CREATE TABLE ... SELECT statements

  For Binlog_format=row, gtid_next= ' automatic ' can release the limit.
  The generated binlog contains two Gtid, one for the table statement and one for the multiple insert transactions.

• Transaction tables and non-transactional tables are included in transactions

  You can release the limit for gtid_next= ' automatic '.
  The generated binlog contains two Gtid, one for all non-transactional tables and one for all transaction tables.
  Additional requirements are required for update multiple tables (including transactional and non-transactional tables) at this time Binlog_format=row.    

The process of opening Gtid online is as follows:

① all servers are executed on

SET @ @global. Enforce_gtid_consistency = Warn; Special NOTE: This step is key one step use cannot appear warning. Non-conforming statements are logged in the error log

② all servers are executed:

SET @ @global. enforce_gtid_consistency = on;

③ is performed on all servers (does not care about the first and last, but is done):

SET @ @global. Gtid_mode = off_permissive;

④ all servers are executed:

SET @ @global. gtid_mode=on_permissive;

In essence, the log generated in this step is a log with Gtid, this step is claimed to be not concerned about any node, but from the actual management recommended on the slave to execute first, and then go to master.

⑤ confirms that the traditional Binlog copy is complete, and the value is 0

Show status like ' Ongoing_anonymous_transaction_count ';

All nodes are judged by show status like ' Ongoing_anonymous_transaction_count '; to zero

⑥ all node execution: flush logs; Used to switch logs.

⑦ all nodes are enabled Gtid_mode

SET @ @global. Gtid_mode=on;

⑧ enable Gtid Auto-discovery node replication:

Stop slave;

Change Master to Master_auto_position=1;

Start slave;

⑨ Write Gtid_mode = on related configuration to 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=50 sequence_number=51

#171116 19:17:03 Server ID 2 end_log_pos 16522 CRC32 0x3b42637e anonymous_gtidlast_committed=51 sequence_number=52

#171116 19:19:54 Server ID 2 end_log_pos 16851 CRC32 0x08383e43 anonymous_gtidlast_committed=52 sequence_number=53

#171116 19:19:59 Server ID 2 end_log_pos 17185 CRC32 0xd3445edc anonymous_gtidlast_committed=53 sequence_number=54

#171116 19:21:06 Server ID 2 end_log_pos 17514 CRC32 0xcfd6ac97 anonymous_gtidlast_committed=54 Sequence_number=55

#171116 19:21:11 Server ID 2 end_log_pos 17802 CRC32 0xee11dc12 anonymous_gtidlast_committed=55 sequence_number=56

Gtid Copying data:

#171117 13:40:00 Server ID 2 end_log_pos 456322 CRC32 0xf7074b53 GTID last_committed=1036 sequence_number=1132

#171117 13:40:00 Server ID 2 end_log_pos 456725 CRC32 0xcb86f0b2 GTID last_committed=1036 sequence_number=1133

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

#171117 13:40:00 Server ID 2 end_log_pos 457533 CRC32 0x4f492e18 GTID last_committed=1036 sequence_number=1135

#171117 13:40:00 Server ID 2 end_log_pos 457940 CRC32 0xf0b078b6 GTID last_committed=1036 sequence_number=1136

# # # #从GTID模式到传统模式过程:

① in 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

Simultaneous execution:

Change Master to master_auto_position=0,master_log_file= ' mysql-bin.000010 ', master_log_pos=458282;

Start slave;

② is performed on MS:

SET @ @GLOBAL. Gtid_mode = on_permissive;

③ is performed on MS

SET @ @GLOBAL. Gtid_mode = off_permissive;

④ is performed on MS

SELECT @ @GLOBAL.  gtid_owned; # #都为空就可以

⑤ Close Gtid

SET @ @GLOBAL. Gtid_mode = OFF;

The last observation and Gtid are anonymous:

#171117 14:32:38 Server ID 2 end_log_pos 319730 CRC32 0xc26f13a7 anonymous_gtid last_committed=699sequence_numb er=793

#171117 14:32:38 Server ID 2 end_log_pos 320133 CRC32 0xf480c415 anonymous_gtidlast_committed=699 sequence_number=794

#171117 14:32:38 Server ID 2 end_log_pos 320538 CRC32 0xa65ed8f1 anonymous_gtidlast_committed=699 sequence_number=795

#171117 14:32:38 Server ID 2 end_log_pos 320945 CRC32 0xb7330b5e anonymous_gtidlast_committed=699 sequence_number=796

#171117 14:32:38 Server ID 2 end_log_pos 321348 CRC32 0x619e04bb anonymous_gtidlast_committed=699 sequence_number=797

#171117 14:32:38 Server ID 2 end_log_pos 321751 CRC32 0x250f903c anonymous_gtidlast_committed=699 sequence_number=798

#171117 14:32:38 Server ID 2 end_log_pos 322156 CRC32 0x247a96f9 anonymous_gtidlast_committed=699 sequence_number=799

This article is from the "Dbaspace" blog, make sure to keep this source http://dbaspace.blog.51cto.com/6873717/1982765

MySQL5.7 legacy replication to Gtid replication