MySQL 5.6 uses GTIDs to build a master-slave Database

MySQL 5.6 uses GTIDs to build a master-slave Database

Concept: What is GTIDS?

(Global Transactions Identifier) is a new feature of MySQL5.6.5.

• When GTIDS is used, everything can be identified and tracked, whether committed on the Master or applied on the Slave;
• Adding a new Server Load balancer instance or migrating the Master identity to the Server Load balancer instance in case of a fault does not require any binary log or position, which greatly simplifies the operation;
• GTIDs is completely transaction-based. Therefore, the MYISAM storage engine is not supported;

[About GTID] GTID consists of source_id and transaction_id.

1. Source_id is from server_uuid, which can be viewed in the auto. cnf file;
2. Tranction_id is a sequence number automatically generated from small to small;

[Root @ t-db01 mysql] # cat auto. cnf
[Auto]
Server-uuid = 268e23d1-2216-11e5-abcc-000c296ecd05

Mysql> show global variables like 'gtid _ executed ';
+ --------------- + ----------------------------------------------------- +
| Variable_name | Value |
+ --------------- + ----------------------------------------------------- +
| Gtid_executed | 268e23d1-2216-11e5-abcc-000c296ecd05: 1-28 |
+ --------------- + ----------------------------------------------------- +

[Build a master-slave database]

Environment Description:

	Master database information	Slave database information
Database Version	5.6.23	5.6.23
IP address	192.168.47.169	192.168.47.186
Synchronize Databases	JOHN_DB
Synchronize users	Repl

1. master database parameter settings

Server_id = 1
Binlog-format = ROW # The ROW format is recommended.
Log-bin = mysql-bin # Open binlog
Report-port = 3306
Gtid-mode = on
Enforce-gtid-consistency = true
Log-slave-updates = true
Replicate_do_db = JOHN_DB

2. Slave database parameter settings

Server_id = 2
Log-bin = mysql-bin
Report-port = 3306
Gtid-mode = on
Enforce-gtid-consistency = true
Log-slave-updates = true
Replicate_do_db = JOHN_DB
Skip-slave-start # automatically enable replication at startup

Check whether gtid is Enabled: show global variables like '% gtid % ';

3. Create a user on the master database

Grant replication slave on JOHN_DB. * to 'repl' @ '192. 168.47.186 'identified by 'repl ';

4. initialize slave database data

The operation steps are the same as those in step 5.5. Here, we will be lazy and will not repeat it;

5. Configure the slave database to connect to the master database

Connect the slave database to the master database:
Change master to master_host = '192. 168.47.169 ', master_user = 'repl', master_password = 'repl', master_auto_position = 1;

Start slave database:
Start slave;

Check status:
Show slave status \ G;

Mysql> show slave status \ G;
* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.47.169
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000009
Read_Master_Log_Pos: 485
Relay_Log_File: t-db02-relay-bin.000012
Relay_Log_Pos: 695
Relay_Master_Log_File: mysql-bin.000009
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
Replicate_Do_DB: JOHN_DB
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 485
Relay_Log_Space: 1150
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_ SQL _Errno: 0
Last_ SQL _Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 31
Master_UUID: 268e23d1-2216-11e5-abcc-000c296ecd05
Master_Info_File:/data/mysql/master.info
SQL _Delay: 0
SQL _Remaining_Delay: NULL
Slave_ SQL _Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_ SQL _Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 268e23d1-2216-11e5-abcc-000c296ecd05: 1-29
Executed_Gtid_Set: 268e23d1-2216-11e5-abcc-000c296ecd05: 1-29
Auto_Position: 1
1 row in set (0.02 sec)

ERROR:
No query specified

The binlog location is automatically located and synchronized;
 

After the above operations, the mysql master-slave architecture is established;

[Troubleshooting methods]

1. Scenario Simulation

Step 1: Create the table john on the master database and insert three rows of data. (The data in the slave database and master database is consistent at this time)

Mysql> select * from john;
+ ------ +
| Id |
+ ------ +
| 1 |
| 2 |
| 3 |
+ ------ +
3 rows in set (0.19 sec)

Step 2: Disable the slave status from the slave Database

Mysql> stop slave;

Step 3: Disable binlog writing in the master database

Mysql> set SQL _log_bin = off; Disable
Query OK, 0 rows affected (0.03 sec)

Step 4: Insert a value to the master database 4

Mysql> insert into john values (4 );
Query OK, 1 row affected (0.04 sec)

Step 5: Enable binlog for the master database

Mysql> set SQL _log_bin = on;
Query OK, 0 rows affected (0.00 sec)

Step 6: the value of the primary database is 5.

Mysql> insert into john values (5 );
Query OK, 1 row affected (0.00 sec)

After the preceding steps, the values of john in the master database and slave database are inconsistent;

The master database is as follows:

The slave database is as follows:

Step 6: Modify the row whose master database id is 4, and an error will be reported from the slave database.

 

Retrieved_Gtid_Set: a989adc2-2a8e-11e5-a308-000c296ecd05: 1-30 Gtid serial number of the master database
Executed_Gtid_Set: a989adc2-2a8e-11e5-a308-000c296ecd05: 1-29 execute Gtid serial number from the database
 

2. Cause: Because id = 4 does not write logs, id = 4 is not synchronized to the slave database. When the master database deletes the record id = 4, this record cannot be found from the database, so an error is reported;

3. Solution: Master/Slave issues in GTIDs mode. The operations are as follows:

Mysql> stop slave;
Mysql> set GTID_NEXT = '268e23d1-2216-11e5-abcc-000c296ecd05: 1-30 '; # Skip the serial number of the current slave Database
Mysql> BEGIN;
Mysql> COMMIT;
Mysql> SET GTID_NEXT = 'automatically ';
Mysql> start slave;

After the above operations, the configuration of the master-slave Database Based on GTIDs and the handling of common problems are completed. The speed of building a master-slave database is indeed improved from a convenient perspective;

-------------------------------------- Split line --------------------------------------

Load Nginx in Ubuntu for high-performance WEB Server 5 --- MySQL master/Master Synchronization

Production Environment MySQL master/Master synchronization primary key conflict handling

MySQL Master/Slave failure error Got fatal error 1236

MySQL master-slave replication, implemented on a single server

Build a MySQL proxy server for read/write splitting + Master/Slave Synchronization

MySQL 5.5 master-slave bidirectional Synchronization

MySQL 5.5 master-slave synchronization troubleshooting

MySQL master-slave replication asynchronous semi-sync instance

-------------------------------------- Split line --------------------------------------

This article permanently updates the link address: