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

Editor to share with you how MySQL turns on / off GTID online, I believe most people don't know much about it, so share this article for your reference. I hope you can learn a lot after reading this article. Let's learn about it together.

A preface

MySQL DBA is mostly familiar with MySQL version 5.6. master-slave replication based on GTID mode is provided, which simplifies replication and reduces the difficulty of master-slave replication maintenance, improves replication operation and maintainability, and no longer depends on binlog file name and location in the file. However, it has many limitations, and version 5.7 of MySQL supports the following improvements to GTID:

There is no need to restart the MySQL server.

The configuration process is online, and the whole replication cluster still provides read and write services.

C there is no need to change the replication topology.

D GTID can be enabled online in any structured replication cluster.

When modifying GTID online, it must be in the following order

OFF OFF_PERMISSIVE ON_PERMISSIVE ON

You can't skip the link, for example, gtid_mode cannot be changed from off to on directly, otherwise MySQL will prompt you.

ERROR 1788 (HY000): The value of @ @ GLOBAL.GTID_MODE can only be changed one step at a time: OFF OFF_PERMISSIVE ON_PERMISSIVE ON. Also note that this value must be stepped up or down simultaneously on all servers. See the Manual for instructions.

Before we practice online upgrades, we need to understand the meaning of GTID_MODE version 5.7 of MySQL:

OFF: no GTID,Slave is generated, only transactions without GTID are accepted

OFF_PERMISSIVE: accept transactions without GTID and transactions with GTID without generating GTID,Slave

ON_PERMISSIVE: generate GTID,Slave to accept transactions without GTID and transactions with GTID

ON: the generating GTID,Slave can only accept transactions with GTID. Open GTID online

It is important to note that only the database version 5.7.6 and later can support opening GTID online. Simulate the read and write test of the main library when GTID is enabled in the test:

Sysbench-test=oltp.lua-oltp-tables-count=1-oltp-table-size=500000-mysql-db=sysbench-mysql-user=sysbench-mysql-password=sysbench-mysql-socket=/srv/my3316/run/mysql.sock-max-time=600 num-threads=1-oltp-test-mode=complex run

2.1 execute in all instances in the master-slave replication structure

Set global1. Non-transaction engine is not supported.

two。 Create table is not supported. Select statement (an error is reported directly during the execution of the main library).

3. A SQL is not allowed to update both transaction engine and non-transaction engine tables.

4. Create temporary table and drop temporary statements are not supported.

If no warning appears, execute on all instances:

Set global ENFORCE_GTID_CONSISTENCY = ON

2.2 execute in all instances in the master-slave replication structure:

Set global GTID_MODE = OFF_PERMISSIVE

Let the main library accept transactions without GTID as well as transactions with GTID without generating GTID,Slave instances. Make sure that you execute the command in all instances before performing the next steps.

2.3 execute in all instances in the master-slave replication structure:

Set global GTID_MODE = ON_PERMISSIVE

The main library begins to generate GTID,Slave that accepts transactions without GTID as well as transactions with GTID.

2.4 execute in all instances in the master-slave replication structure:

Execute the following command on each instance node to check whether the anonymous transaction has been consumed, preferably a few more times to confirm that the value of the parameter is 0.

[RW] [TEST:3316] > SHOW STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT'

+-+ +

| | Variable_name | Value |

+-+ +

| | Ongoing_anonymous_transaction_count | 0 | |

+-+ +

1 row in set (0.00 sec)

If you check on the slave library, you only need to meet 0 once.

Ensure that all binlog prior to step 4 are applications.

Make sure that all the binlog before the operation has been applied by other servers, because the anonymous GTID must ensure that the application has been successfully replicated before you can proceed to the next step. How to check it? In fact, the easiest way is to check the application site by performing show slave status from the library. If you catch up, you can continue. Otherwise, you need to wait for the next step after the binlog has been applied to the slave library.

2.5 execute in all instances in the master-slave replication structure:

Set global GTID_MODE = ON

The function of this parameter is to let the system generate GTID, and Slave can only accept transactions with GTID.

2.6 execute on the slave library:

Sets the MASTER_AUTO_POSITION=1 in slave replication.

[RO] [TEST:3316] > stop slave

[RO] [TEST:3316] > CHANGE MASTER TO MASTER_AUTO_POSITION = 1

[RO] [TEST:3316] > [RW] [TEST:3316] > start slave

At this point, the site-based replication relationship is upgraded to GTID mode. Is it over? Not yet. Remember to modify my.cnf and add it.

Gtid_mode = on

Enforce_gtid_consistency = on three online shut down GTID

The step of shutting down GTID is actually the opposite of the step of turning on:

3.1 close MASTERAUTOPOSITION [RO] [TEST:3316] > stop slave in slave replication

[RO] [TEST:3316] > CHANGE MASTER TO MASTER_LOG_FILE = file

MASTER_LOG_POS = position MASTER_AUTO_POSITION = 0

[RO] [TEST:3316] > [RW] [TEST:3316] > start slave;3.2 executes on all instances:

Set global GTID_MODE = ON_PERMISSIVE

3.3 execute on all instances:

Set global GTID_MODE = OFF_PERMISSIVE

Wait until the value of @ @ GLOBAL.GTID_OWNED is an empty string.

SELECT @ @ GLOBAL.GTID_OWNED

3.5 check that the logs in binlog on master have been applied by slave 3.6 set GTID_MODE to off on all instances

Set global GTID_MODE = OFF

3.7 execute on all instances:

SET global GTID_MODE = OFF

SET global ENFORCE_GTID_CONSISTENCY = OFF

The above is all the contents of the article "how to turn on / off GTID online by MySQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.