In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-24 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)05/31 Report--
This article mainly introduces "how to understand the GTID replication of MySQL". In the daily operation, I believe that many people have doubts about how to understand the GTID replication of MySQL. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts of "how to understand the GTID replication of MySQL"! Next, please follow the editor to study!
What is GTID?
What is GTID, in short, is the global transaction ID (global transaction identifier), which was originally implemented by google and was not added by the official MySQL until 5. 6.
GTID is the unique identifier that creates the allocation when the transaction commits, and all transactions map to GTID one by one.
The format of GTID is similar to:
5882bfb0-c936-11e4-a843-000c292dc103:1
This string, separated by ":", represents the server's server_uuid, which is a 128-bit random string generated on the first startup (function generate_server_uuid), corresponding to the read-only variable server_uuid. It can guarantee the global uniqueness with a very high probability and save to the file.
In DATADIR/auto.cnf. Therefore, pay attention to protect this file from being deleted or modified.
The second part is a self-increasing transaction ID number, transaction ID number + server_uuid to uniquely identify a transaction.
Mysql > show global variables like'% gtid%' +-- +-+ | Variable_name | Value | +- -+-+ | enforce_gtid_consistency | ON | | gtid_executed | 5882bfb0-c936-11e4-a843-000c292dc103:1-6 | | gtid_mode | ON | | gtid_owned | gtid_purged | | + -+ 5 rows in set (0.00 sec) mysql > show global variables like'% uuid%' +-+-+ | Variable_name | Value | +-+- -+ | server_uuid | 5882bfb0-c936-11e4-a843-000c292dc103 | +-- + 1 row in set (0.00 sec) shell > cat auto.cnf [auto] server-uuid=5882bfb0-c936-11e4-a843-000c292dc103 set GTID replication
Synchronize master-slave data
Mysql > SET @ @ global.read_only = ON;Query OK, 0 rows affected (0.01sec)
Stop all databases
Shell > mysqladmin-u root-p shutdown
Set the development GTID mode and start all databases
Shell > vi my.cnf add the following content = [mysqld] gtid_mode=ONlog-slave-updates=ONenforce-gtid-consistency=ON # force GTID consistency =
Specify the master library from the library
Mysql > CHANGE MASTER TO-> MASTER_HOST = host,-> MASTER_PORT = port,-> MASTER_USER = user,-> MASTER_PASSWORD = password,-> MASTER_AUTO_POSITION = 1 × MySQL > START SLAVE;Query OK, 0 rows affected (0.04 sec)
Disable read-only mode
Mysql > SET @ @ global.read_only = OFF;Query OK, 0 rows affected (0.00 sec) restrictions on GTID replication
GTID schema instances and non-GTID schema instances cannot be replicated, and the requirements are very strict, either GTID or none.
Gtid_mode is read-only. To change the state, you must 1) close the instance, 2) modify the configuration file, 3) restart the instance
Update non-transactional engine table
Updating transactional and non-transactional tables in the same transaction will result in multiple GTIDs being assigned to the same transaction
Mysql > cretea table tt (id int) engine=myisam;mysql > insert into tt values (1), (2); mysql > cretea table t (id int) engine=innodb;mysql > insert into t values (1), (2); mysql > set autocommit = 0 MySQL > begin Mysql > update t set id = 3 where id = 2 row affected query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql > update tt set id = 3 where id = 2 error 1785 (HY000): When @ @ GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, updates to non-transactional tables can only be done in either autocommitted statements or single-statement transactions, and never in the same statement as updates to transactional tables.
CREATE TABLE... SELECT statements
Unsafe statement-based replication is actually two separate events, one for creating a table and one for inserting source table data into a new table.
Mysql > create table t engine=innodb as select * from tt;ERROR 1786 (HY000): CREATE TABLE. SELECT is forbidden when @ @ GLOBAL.ENFORCE_GTID_CONSISTENCY = 1.
Temporary watch
Creation of delete temporary table statements cannot be performed within a transaction, but can be executed outside the transaction, but must be set to set autocommit = 1
Mysql > create temporary table tttt (id int); ERROR 1787 (HY000): When @ @ GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, the statements CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can be executed in a non-transactional context only, and require that AUTOCOMMIT = 1.mysql > set autocommit = 1 politics query OK, 0 rows affected (0.00 sec) mysql > create temporary table tttt (id int); Query OK, 0 rows affected (0.04 sec)
Do not execute unsupported statements
Enable the-- enforce-gtid-consistency option to start GTID mode, and the above unsupported statements will return an error.
Operation and maintenance operation
a. Ignore replication errors
When there is an error in the replication of the standby library, the traditional way to skip the error is to set sql_slave_skip_counter and then START SLAVE.
However, if GTID is opened, the setting will fail:
Mysql > stop slave;Query OK, 0 rows affected (0.03 sec) mysql > set global sql_slave_skip_counter = 1 error 1858 (HY000): sql_slave_skip_counter can not be set when the server is running with @ @ GLOBAL.GTID_MODE = ON.Instead, for each transaction that you want to skip, generate an empty transaction with the same GTID as the transaction
The error message tells us that we can skip the wrong transaction by generating an empty transaction.
We manually generate a standby replication error:
[slave] mysql > alter table t add primary key pk_id (id); Query OK, 2 rows affected (0.12 sec) Records: 2 Duplicates: 0 Warnings: 0mysql > insert into t values (1); mysql > insert into t values (4); mysql > insert into t values (5); mysql > show master status +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +- -+ | mysql-info.000004 | 914 | 5882bfb0-c936-11e4-a843-000c292dc103:1-17 | +- -+-+ 1 row in set (0.00 sec) mysql > show slave status\ row * *. Slave_IO_Running: Yes lave_SQL_Running: No Last_Errno: 1062 Last_Error: Could not execute Write_rows event on table db_test.t; Duplicate entry'1' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY The event's master log mysql-info.000004, end_log_pos 401 Retrieved_Gtid_Set: 5882bfb0-c936-11e4-a843-000c292dc103:1-15 Executed_Gtid_Set: 5882bfb0-c936-11e4-a843-000c292dc103:1-14, f1e6584a-c935-11e4-a840-000c29348dbe:1 Auto_Position: 11 row in set (0.00 sec) mysql > SET @ @ SESSION.GTID_NEXT= '5882bfb0-c936-11e4-a843-000c292dc103V15' Query OK, 0 rows affected (0.00 sec) mysql > begin;Query OK, 0 rows affected (0.00 sec) mysql > commit;Query OK, 0 rows affected (0.00 sec) mysql > SET SESSION GTID_NEXT = AUTOMATIC;mysql > start slave Mysql > show slave status\ gateway * 1. Row * * Slave_IO_Running: Yes Slave_SQL_Running: Yes Last_Errno: 0 Retrieved_Gtid_Set: 5882bfb0-c936-11e4-a843 -000c292dc103:1-17 Executed_Gtid_Set: 5882bfb0-c936-11e4-a843-000c292dc103:1-17 F1e6584a-c935-11e4-a840-000c29348dbe:1 Auto_Position: 11 row in set (0.00 sec)
If you look at the show slave status again, you will see that the error transaction has been skipped. The principle of this method is simple. The GTID generated by an empty transaction is added to the GTID_EXECUTED.
This is equivalent to telling the standby database that the transaction corresponding to this GTID has been executed, and the master-slave data is inconsistent.
At this point, the study on "how to understand the GTID replication of MySQL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!
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.
Views: 263
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.