MySQL--GTID

Gtid concept
GTID, global transaction ID globaltransaction identifiers
Gtid is a transaction that corresponds to a globally unique ID that is used to identify the transaction as it is logged to Binlog.
GTID counterpart transactions are performed only once on a server, avoiding duplication of data.
Instead of using traditional master_log_file+ master_log_pos, GTID used the traditional method of copying, instead of using the Master_auto+postion=1 method.
Each transaction has a gtid_log_event

The composition of the Gtid
Uuid+sequence number
Sequence number is a transaction order code inside the MySQL server. A transaction on a MySQL server does not have a duplicate sequential number (guaranteed to be unique within the server).
Each MySQL server has a globally unique UUID.
?

Gtid Advantages

1. Simplifies the use of replication and reduces the difficulty of maintaining replication clusters.
2. Better data consistency than traditional replication

How the Gtid works

1. When a transaction is executed and committed on the main library side, the Gtid is generated and recorded in the Binlog log. When
2. slave connects to master, the Gtid in gtid_executed is sent to master. Master automatically skips these transactions and sends only those things that are not copied to the slave. After the
3. Binlog is transferred to slave and stored to slave relaylog, this gtid value is read to set the Gtid_next variable, which tells slave the next Gtid value to execute. The
4. SQL thread obtains gtid from relay log and then compares binlog on the slave side for that gtid.
5. If there is a record stating that the transaction for the Gtid has been performed, slave is ignored.
6. If there is no record, slave executes the Gtid transaction and logs the Gtid to its own binlog, and
7. checks the other session to hold the Gtid before reading the execution transaction, ensuring that it is not repeated. The
8. will determine if there is a primary key during parsing, and if it does not use a two-level index, then all scans are used.

The role of Gtid
The use of GTID not only replaces the old binary log file and location with a separate identifier, it also employs a new replication protocol. The old protocol is often straightforward, which is to first connect to a given binary log file from the server at a specific offset location, and then the primary server sends all events from the given connection point.
The new protocol is slightly different: supports replication based on global Unified transaction ID (GTID). Each transaction can be located and traced when a transaction is committed on the main library or is applied from the library. GTID replication is all transaction-based, making it very easy to check master-slave consistency. If the transactions committed on all the main libraries are also submitted to the Slave library, consistency is guaranteed.
GTID related actions: By default, when a transaction is logged into a binary file, its GTID is recorded first, and the GTID and transaction-related information is sent to the slave server, which is applied locally from the server, but will never change the original transaction ID number. Therefore, in the GTID architecture, even if there is an n-tier architecture, replication is an n-level architecture, the transaction ID will remain unchanged, effectively guaranteeing the integrity and security of the data.
You can use statement-based or row-based replication with GTID, but for best results, we recommend that you use row-based formatting.

The specific induction of Gtid function mainly has the following two points:
According to GTID, you can tell which instance the transaction was originally committed on.
GTID's presence facilitates Replication's Failover.
We can look at the operation of Replication Failover before the GTID of MySQL 5.6 appears. Let's say we have an environment like this:

At this point, server A's servers are down and the business needs to be switched to server B. At the same time, we need to change the replication source for server C to server B. The command syntax for copying source modifications is simple:
Change MASTER to master_host= ' xxx ', master_log_file= ' xxx ', master_log_pos=nnnn
The difficulty with this approach is that because the same transaction is located on each machine, the Binlog name and location are different, then how to find the current synchronization stop of server C corresponds to the location of Master_log_file and Master_log_pos on server B as Problem.
This is why M-S replication clusters require the use of additional management tools such as MMM and MHA. This problem is very simple after the appearance of GTID in 5.6.
Because the GTID of the same transaction is consistent across all nodes, the GTID on the current stop of server C can uniquely locate GTID on server B. Even because of the master_auto_position function, we do not need to know the specific value of GTID. Directly use the following command to complete the work of Failover directly.
Change MASTER to master_host= ' xxx ', master_auto_position== ' xxx '
Environment
System: redhat6.5?
Firewall: Keep it off?
Selinux=disabled
MySQL Host: Server1 172.25.60.1/24?
MySQL slave machine: server2 172.25.60.2/24

MySQL5.7 Installation Start
(1) installation package?

mysql-community-client-5.7.17-1.el6.x86_64.rpm?
mysql-community-common-5.7.17-1.el6.x86_64.rpm?
mysql-community-libs-5.7.17-1.el6.x86_64.rpm?
mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm?
mysql-community-server-5.7.17-1.el6.x86_64.rpm

(2) Turn on MySQL and change password

[Email protected] ~]#/etc/init.d/mysqld start # #开启mysql

grep Password/var/log/mysqld.log # #查看密码

[Email protected] ~]# mysql-p Enter Password: # #修改mysql的管理用户密码

mysql> ALTER USER [email protected] identified by ' NewPassword '; # #输入查看到的密码

Configuring Master-slave Replication (common)
MySQL Main library (master)
(1) Configuring the master configuration file (/etc/my.cnf)

[Email protected] ~]# VIM/ETC/MY.CNF
Server-id=1 # #服务器ID
Log-bin=mysql-bin # #开启二进制日志
Binlog-do-db=test # #需要同步的数据库名
Binlog-ignore-db=mysql # #禁止同步的数据库名
[Email protected] ~]#/etc/init.d/mysqld Restart # #重启服务

(2) configuration database?

# #在主库上建立帐户并授权
# #为了方便我将密码全部设置一致
mysql> grant replication Slave on . to ' rep ' @ ' 192.168.32.129 ' identified by '
Authorization password ';
mysql> flush Privileges;

# #查看主库的状态

Mysql> mysql> Show Master status;
File= master-bin.000003
position=154

MYSQ from library (slave)

(1) configuration file (/ETC/MY.CNF)

[Email protected] ~]# VIM/ETC/MY.CNF
# #配置server-id,slave Server-id must be different from master, that is, if the binary log is turned on, all servers must have a different Server-id
server-id=2
Log-bin=mysql-bin
[Email protected] ~]# systemctl restart mysqld

(2) Configuration database

mysql> Change Master to master_host= ' 192.168.32.140 ', master_user= ' rep ', master_password= ' 123456 ', master_log_file = ' master-bin.000002 ', master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
Mysql> show Slave status\g;

1. Row
Slave_io_state:waiting for Master to send event
master_host:192.168.32.140
Master_user:rep
master_port:3306
Connect_retry:60
master_log_file:master-bin.000002
read_master_log_pos:154
relay_log_file:192-relay-bin.000002
relay_log_pos:321
relay_master_log_file:master-bin.000002
Slave_io_running:yes
Slave_sql_running:yes
replicate_do_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:154
relay_log_space:526
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
When you see Slave_io_running:yes and slave_sql_running:yes, it means that the slave library is working properly.

There are three reasons why the main library and the library are not connected when the slave_io_running:connecting prompt appears:
1. Network problem: Check if the network connection is able to connect
2. Password or POS number error: see if the POS number and the main library number correspond
3. Firewall issues: View the policy of the main library firewall, whether the database rejects the foreign connection, and then make the corresponding changes

Use mysql5.7 's gtid to achieve master-slave replication

GTID (global Transaction ID) is the number of a committed transaction and is a globally unique number. Its official definition is as follows:?
? GTID = source_id:transaction_id?
Each of the GTID represents a database transaction?
View binary logs in MySQL data directory (/var/lib/mysql)
# #mysql-bin.000007 for MySQL binary log, followed by log code
Mysqlbinlog mysql-bin.000007
GTID is session.gtid_next= ' b1b6e236-0171-11e8-b3b9-525400c7a2a4:1?

Implement master-slave replication with Gtid

1. Configure the configuration file (/etc/my.cnf) and restart MySQL (the same as the master-slave configuration)

Gtid_mode=on
Enforce-gtid-consistency=true

mysql> stop Slave;
mysql> Change Master to master_host= ' 192.168.32.140 ', master_user= ' rep ', master_password= ' 123456 ', master_log_file = ' master-bin.000003 ', master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

Mysql> show Slave status\g;
1. Row
Slave_io_state:waiting for Master to send event
master_host:192.168.32.140
Master_user:rep
master_port:3306
Connect_retry:60
master_log_file:master-bin.000003
read_master_log_pos:154
relay_log_file:192-relay-bin.000002
relay_log_pos:321
relay_master_log_file:master-bin.000003
Slave_io_running:yes
Slave_sql_running:yes
replicate_do_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:154
relay_log_space:526
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:1
master_uuid:c371c386-7a9c-11e8-9015-000c29524962
Master_info_file:/usr/local/mysql/data/master.info
sql_delay:0
Sql_remaining_delay:null
Slave_sql_running_state:slave have read all relay log; Waiting for more updates
master_retry_count:86400
Master_bind:
Last_io_error_timestamp:
Last_sql_error_timestamp:
MASTER_SSL_CRL:
Master_ssl_crlpath:
Retrieved_gtid_set:c371c386-7a9c-11e8-9015-000c29524962:1-3
Executed_gtid_set:c371c386-7a9c-11e8-9015-000c29524962:1-3 auto_position:0
replicate_rewrite_db:
Channel_name:
Master_tls_version:
1 row in Set (0.00 sec)

3. Testing
Main MySQL:

Mysql> CREATE TABLE Test.caiwu (ID int,name varchar (10));
Query OK, 0 rows affected (0.03 sec)

mysql> use test;
Reading table information for completion of table and column names
Can turn off this feature to get a quicker startup with-a

Database changed
mysql> INSERT INTO Caiwu values (1, ' name1 ');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO Caiwu values (2, ' name2 ');
Query OK, 1 row affected (0.02 sec)
Mysql> select * from Caiwu;
+------+-------+
| ID | name |
+------+-------+
| 1 | name1 |
| 2 | name2 |
+------+-------+
2 rows in Set (0.00 sec)

From MySQL:

mysql> use test;
Reading table information for completion of table and column names
Can turn off this feature to get a quicker startup with-a

Database changed
Mysql> Show tables;
+----------------+
| Tables_in_test |
+----------------+
| Caiwu |
+----------------+
1 row in Set (0.00 sec)

Mysql> select * from Caiwu;
+------+-------+
| ID | name |
+------+-------+
| 1 | name1 |
| 2 | name2 |
+------+-------+
2 rows in Set (0.00 sec)

MySQL--GTID