Shulou(Shulou.com)05/31 Report--

This article mainly explains "the analysis of operation and maintenance changes brought about by Gtid in Mysql 5.7". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn the analysis of the changes brought about by Gtid in Mysql 5.7.

How to skip a thing

Unlike the traditional location-based master-slave, if we report an error from the library, we need to get the last thing we need to execute from the library, as follows:

Executed_Gtid_Set in show slave status\ G.

Gtid_executed in show global variables like'% gtid%';.

Executed_Gtid_Set in show master status;.

Then build an empty thing as follows:

Stop slave; set gtid_next='4a6f2a67-5d87-11e6Mui a6bdly000c29a879a3pur34The begincross cross cross set gtid_next='automatic';start slave

If more than one is as follows:

Stop slave; set gtid_next='89dfa8a4-cb13-11e6-b504-000c29a879a3 stop slave; 2. The change of mysqldump export behavior

The use of mysqldump is affected by the option set-gtid-purged=AUTO, if we use the following statement to export data with Gtid on and off:

Mysqldump-single-transaction-master-data=2-R-E-triggers-all-databases

When Gtid is enabled, the following settings will be added:

SET @ MYSQLDUMP_TEMP_LOG_BIN = @ @ SESSION.SQL_LOG_BIN;SET @ @ SESSION.SQL_LOG_BIN= 0 *-GTID state at the beginning of the backup-- SET @ @ GLOBAL.GTID_PURGED='ec9bdd78-a593-11e7-9315-5254008138e4:1-105'

Why is it set up like this? Because if you are a Gtid-based master and slave, whether or not to generate binlog means whether to generate new Gtid things based on the local database when importing data, which is obviously unreasonable, so setting SQL_LOG_BIN to 0 is necessary. Then GTID_PURGED is set to the Gtid transactions that have been executed at the time of backup, such as the source code profiling setting GTID_PURGED in section 5 above. The Gtid in three places is set as follows:

Mysql.gtid_ executed table

Gtid_purge variable

Gtid_executed variable

It seems reasonable, but if it is ignored here that the entire mysql.gtid_ executed table is an innodb table, some versions (known as percona 5.7.14 innodb 5.7.17) will be re-deleted and established during the import process, so the mysql.gtid_executed table set through GTID_PURGED will be changed again. Reading the mysql.gtid_ executed table after restarting the database may lead to replication errors. This also lays the groundwork for my failure case, which is described in detail.

Of course, you can also use the-- set-gtid-purged=OFF option to tell mysqldump that you don't need to join SQL_LOG_BIN= 0 and GTID_PURGED, but initialize the Gtid-based master and slave must not be set to OFF. Here is the meaning of this option.

-set-gtid-purged [= name] Add 'SET @ @ GLOBAL.GTID_PURGED' to the output. Possible values for this option are ON, OFF and AUTO. If ON is used and GTIDs are not enabled on the server, an error is generated. If OFF is used, this option does nothing. If AUTO is used and GTIDs are enabled on the server, 'SET @ @ GLOBAL.GTID_PURGED' is added to the output. If GTIDs are disabled, AUTO does nothing. If no value is supplied then the default (AUTO) value will be considered. Build a master-slave system based on Gtid in

There is a note here, which will be mentioned in my case. Let's just talk about the steps.

Note that Gtid must be enabled and server_id must be set up in the master and standby libraries.

Enforce_gtid_consistency = ON gtid_mode = ON server_id = 9910 binlog_format = row

At the same time, both master and slave libraries enable binlog. If cascading slave libraries are not set, do not set the log_slave_updates parameter for slave libraries.

This is the most reasonable setting.

Establish replication user

CREATE USER 'repl'@'%' IDENTIFIED BY' test123';GRANT REPLICATION SLAVE ON *. * TO 'repl'@'%'

Export data

Mysqldump-single-transaction-master-data=2-R-E-triggers-- all-databases > test.sql

Import data from the library

Source is fine.

Execute reset master statements from the library

This step mainly prevents gtid_executed from being changed. This problem exists in percona 5.7.14 5.7.17 but not in percona 5.7.15 5.7.19. So for the sake of safety, perform the following two steps.

Reset master

Extract the GTID_PURGED and execute

Using the head-n 40 command, you can quickly get, for example, my

-GTID state at the beginning of the backup-- SET @ @ GLOBAL.GTID_PURGED='ec9bdd78-a593-11e7-9315-5254008138e4:1-21'

Execution

SET @ @ GLOBAL.GTID_PURGED='ec9bdd78-a593-11e7-9315-5254008138e4:1-21'

Statement, and the mysql.gtid_executed table will be refactored when you complete this part.

Use MASTER_AUTO_POSITION to establish synchronization

Change master to master_host='192.168.99.41',master_user='repl',master_password='test123',master_port=3310,MASTER_AUTO_POSITION = 1

Start slave

Master-Slave switching of Gtid in start slave IV.

During the switch, you must make sure that the slave library (the new master library) has not done anything local, if so, otherwise the switching master library (new slave library) needs to pull this part of the Gtid transactions, if these binlog no longer exist, then it is bound to report an error. In this case, it is better to build from the library. So let's talk about the normal switching steps.

Slave library (new master library)

Stop slave;reset slave all

Master library (new slave library)

Change master to master_host='192.168.99.40',master_user='repl',master_password='test123',master_port=3310,MASTER_AUTO_POSITION = 1 * * start slave

In fact, it is as simple as that. The slave library (the new master library) will generate its own Gtid transactions, which can be executed after the new master library is accepted. The following Gtid with two server_uuid will appear and the following gtid_executed will appear

Mysql > show global variables like'% gtid%' + -+ | Variable_name | Value | +-- -+ | binlog_gtid_simple_recovery | ON | | enforce_gtid_consistency | ON | | gtid_executed | 31704d8a-da74-11e7-b6bf-525400a7d243:1-9 | Ec9bdd78-a593-11e7-9315-5254008138e4:1-25 | | gtid_executed_compression_period | 1000 | | gtid_mode | ON | | Gtid_owned | | gtid_purged | ec9bdd78-a593-11e7-9315-5254008138e4:1-25 | | session_track_gtids | OFF | | +-- -- +

In general, if you want to switch to the slave library, don't do anything locally from the library. If you do want to do operations that do not affect the data, such as indexing, you can use the following:

Mysql > set sql_log_bin=0;Query OK, 0 rows affected (0.00 sec) mysql > create index test_jjj on jjj (id); Query OK, 0 rows affected (0.42 sec) Records: 0 Duplicates: 0 Warnings: 0

This will not increase the local Gtid.

Fifth, modify the Gtid mode online

This is a function implemented after 5.7.6, which mainly depends on the Previous gtid Event we analyzed earlier and the two new values added to the parameter gtid_mode. Let's take a look at the meaning of each value of gitd_mode:

OFF (0): Both new and replicated transactions must be anonymous. (anonymous transactions are generated, and slave can only apply anonymous transactions.)

OFF_PERMISSIVE: (1) New transactions are anonymous. Replicated transactions can be either

Anonymous or GTID transactions. (anonymous transactions are generated, and slave can apply anonymous and GTID transactions)

ON_PERMISSIVE (2): New transactions are GTID transactions. Replicated transactions can be either

Anonymous or GTID transactions. (GTID things are generated, and slave can apply anonymous and GTID things)

ON (3): Both new and replicated transactions must be GTID transactions (GTID things are generated, and slave can only apply GTID things)

Note that each modification of the value will inevitably lead to a binlog switch, and if binlog deletion occurs, you can also rely on Previous gtid Event to quickly and accurately find the gtid_purged (Gtid_state.lost_gtids).

Online startup

Master library / slave library execution

SET @ @ GLOBAL.ENFORCE_GTID_CONSISTENCY = WARN

Make sure that everything supports gtid, and the warning does not appear in err log as follows:

2017-02-26T22:35:24.322055Z 55 [Warning] Statement violates GTID consistency: CREATE TABLE. SELECT.

Master library / slave library execution

SET @ @ GLOBAL.ENFORCE_GTID_CONSISTENCY = ON

Master library / slave library execution

SET @ @ GLOBAL.GTID_MODE = OFF_PERMISSIVE

Anonymous transactions are generated, and slave can apply anonymous and GTID transactions

Master library / slave library execution

SET @ @ GLOBAL.GTID_MODE = ON_PERMISSIVE

GTID transactions are generated, and slave can apply anonymous and GTID transactions

Master library / slave library execution

Make sure there is nothing anonymous.

SHOW GLOBAL STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT'

At the same time, confirm the slave library

Retrieved_Gtid_Set

Executed_Gtid_Set

Normal growth

At this point, gtid things are actually already in use.

Master library / slave library execution

SET @ @ GLOBAL.GTID_MODE = ON

Execute from the library

Stop slave;CHANGE MASTER TO MASTER_AUTO_POSITION = 1 * * start slave

Master library / slave library execution

Modify the configuration file my.cnf to add changes to the parameters to the profile

Online shutdown

Execute from the library

Stop slave

Record the execution status value from the library

Exec_Master_Log_Pos: 7631438Relay_Master_Log_File: bin_log.000016

Execution

CHANGE MASTER TO MASTER_AUTO_POSITION = 0 master log file = 'bin_log.000016', MASTER_LOG_POS = 7631438start slave

Master library / slave library execution

SET @ @ GLOBAL.GTID_MODE = ON_PERMISSIVE

GTID transactions are generated, and slave can apply anonymous and GTID transactions

Master library / slave library execution

SET @ @ GLOBAL.GTID_MODE = OFF_PERMISSIVE

Anonymous transactions are generated, and slave can apply anonymous and GTID transactions

Execute from the library

Waiting for slave library

Retrieved_Gtid_Set

Executed_Gtid_Set

No more changes.

To complete this step, in fact, GTID things have not been generated and applied.

Master library / slave library execution

SET @ @ GLOBAL.GTID_MODE = OFF

Master library / slave library execution

Modify the configuration file my.cnf to add changes to the parameters to the profile

At this point, I believe you have a deeper understanding of the "Analysis of Operation and maintenance changes brought about by Gtid in Mysql 5.7". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.