MySQL Based on Gtid replication

Source: Internet
Author: User
Tags unique id uuid

GTID Replication:
In MySQL 5.6.5 or more, MySQL has added a Gtid-based replication method. The GTID ensures that each transaction committed on the primary database has a unique ID in the cluster, which strengthens the consistency of the database master, failback, and fault tolerance.

What is Gtid? :
GTID (global Transaction ID) is the number of a committed transaction and is a globally unique number. GTID is made up of Uuid+tid. The UUID is a unique identifier for a MySQL instance, and TID represents the number of transactions that have been committed on that instance and is monotonically incremented as the transaction commits.
What is a UUID?
MySQL 5.6 replaced the original 32-bit server-id with a 128-bit server-uuid. Server-id is a manual configuration based on my.cnf, which is prone to conflict, and the UUID guarantees that the generated server-uuid avoids conflict issues.
To view the UUID:
Show variables like "%server_uuid%";
3ff69404-0b11-11e8-a5c9-000c29713b71
View Gtid:
3ff69404-0b11-11e8-a5c9-000c29713b71:1
Gtid form:
3ff69404-0b11-11e8-a5c9-000c29713b71:1
3ff69404-0b11-11e8-a5c9-000c29713b71:5 #一组连续的事物

How the Gtid replication implementation works

    1. When master updates the data, it generates Gtid before the transaction, which is recorded in the Binlog log together;
    2. The slave IO thread writes the changed binlog to the local relay log;
    3. SQL thread obtains gtid from relay log, and then compares slave binlog for records;
    4. If there is a record stating that the Gtid's transaction has been performed, slave will ignore it;
    5. If there is no record, slave executes the Gtid transaction from the relay log and logs it to binlog;
    6. In the parsing process will determine whether there is a primary key, if not a two-level index, if not the full scan.

Show Binlog events in ' mysql-bin.000003 ';
| mysql-bin.000003 | 2152 | Gtid | 1283306 | 2217 | SET @ @SESSION. gtid_next= ' 3ff69404-0b11-11e8-a5c9-000c29713b71:12 ' |
| mysql-bin.000003 | 2217 | Query | 1283306 | 2290 | BEGIN |
| mysql-bin.000003 | 2290 | Table_map | 1283306 | 2339 | table_id:145 (GTIDB.GTITB) |
| mysql-bin.000003 | 2339 | Write_rows | 1283306 | 2379 | table_id:145 Flags:stmt_end_f |
| mysql-bin.000003 | 2379 | Xid | 1283306 | 2410 | COMMIT/* xid=588 */|


Configure Gtid Master-slave replication:
Master configuration:

    • Gtid-mode:gtid-mode=on
    • Enforce-gtid-consistency:enforce-gtid-consistency=on
    • Log-slave-updates:log-slave-updates:on
    • Server-id 1003306, (recommended IP after two digits plus database port number)
    • Binlog_format:binlog_format=row
    • Log-bin:log-bi=on

To create a copy account:
Grant Replication Slave on * * to ' repl ' @ '% ' identified by ' password ';
Flush privileges;
To view the master status:
Show Master Status\g;

Slave configuration:

    • Gtid-mode:gtid-mode=on
    • Enforce-gtid-consistency:enforce-gtid-consistency=on
    • Log-slave-updates:log-slave-updates:on
    • Server-id 1013306, (recommended IP after two digits plus database port number)
    • Binlog_format:binlog_format=row
    • Log-bin:log-bi=on

Connect Master data:
Connect to Main library:
Change master to
master_host= ' 192.168.100.100 ',
master_user= ' Repl ',
Master_ Password= ' PASSWORD ',
master_port=3306,
Master_auto_position=1;
Turn on sync:
start slave;
View slave status:
show slave status\g;
Slave_io_running:yes
Slave_sql_running:yes
Retrieved_gtid_set:3ff69404-0b11-11e8-a5c9-000c29713b71:1-3
Executed_gtid_set:3ff69404-0b11-11e8-a5c9-000c29713b71:1-3

Gtid Related parameters:
Show global variables like '%gtid% ';
Show variables like '%gtid_next% ';
Gtid_executed: The Gtid collection executed on the current instance; It actually contains all the transactions that were recorded in the Binlog. Therefore, a transaction executed after setting set sql_log_bin=0 does not generate a Binlog event and is not recorded in gtid_executed. Perform reset master to empty the variable.
Gtid_purged:binlog cannot reside permanently on the service and needs to be cleaned up periodically (through Expire_logs_days can control the periodic cleanup interval), or it will run out of disk sooner or later. The gtid_purged is used to record a collection of binlog transactions that have been cleared, which is a subset of the gtid_executed. The variable can be set manually only if gtid_executed is empty, and the same value gtid_executed to and gtid_purged are updated at the same time. Gtid_executed is null means either Gtid-based replication has not been initiated before, or reset MASTER has been executed. When you perform reset master, the gtid_purged is also empty, that is, always keep gtid_purged as a subset of gtid_executed.

Gtid_next: A session-level variable that indicates how the next gtid is generated.

    • AUTOMATIC: Automatically generates the next Gtid, which is assigned a minimum number of gtid that have not been executed on the current instance.
    • ANONYMOUS: Executing a transaction after setting does not produce gtid.
    • Explicitly specified gtid: You can specify an arbitrary form of a valid Gtid value, but not the gtid that is already contained in the current gtid_executed, or the next time the transaction is executed.

MySQL Based on Gtid replication

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.