Configure MySQL Gtid master-slave replication detailed parsing and steps

The concept of Gtid

1. Global Transaction ID:global transaction identifiers
2. Gtid is a transaction one by one correspondence, and the globally unique ID
3. Gtid is performed only once on a server to avoid duplication of data or master/slave inconsistencies
4. Gtid is used instead of the traditional copy method, and no longer uses Master_log_file+master_log_pos to turn on replication. Instead, use Master_auto_postion=1 to start copying.
5. MYSQL-5.6.5 began to support the MySQL-5.6.10 after the start of perfection.
6. On the traditional slave side, the Binlog is not open, but the binlog in the slave end of the gtid must be turned on to record the Gtid (mandatory) performed.

Composition of the Gtid

 GTID = source_id:transaction_id source_id，用于鉴别原服务器，即mysql服务器唯一的的server_uuid，由于GTID会传递到slave，所以也可以理解为源ID。transaction_id，为当前服务器上已提交事务的一个序列号，通常从1开始自增长的序列，一个数值对应一个事务。 示例：          3E11FA47-71CA-11E1-9E33-C80AA9429562:23前面的一串为服务器的server_uuid，即3E11FA47-71CA-11E1-9E33-C80AA9429562，后面的23为transaction_id        

Advantages of Gtid

1、更简单的实现failover，不用以前那样在需要找log_file和log_pos。2、更简单的搭建主从复制。3、比传统的复制更加安全。4、GTID是连续的没有空洞的，保证数据的一致性，零丢失。

How the Gtid works

1、当一个事务在主库端执行并提交时，产生GTID，一同记录到binlog日志中。2、binlog传输到slave,并存储到slave的relaylog后，读取这个GTID的这个值设置gtid_next变量，即告诉Slave，下一个要执行的GTID值。3、sql线程从relay log中获取GTID，然后对比slave端的binlog是否有该GTID。4、如果有记录，说明该GTID的事务已经执行，slave会忽略。5、如果没有记录，slave就会执行该GTID事务，并记录该GTID到自身的binlog，   在读取执行事务前会先检查其他session持有该GTID，确保不被重复执行。6、在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有就用全部扫描。

To configure the Gtid-based replication Environment Description:

Database Roles	IP	Applications and Systems
Primary database	192.168.24.128	Centos7 myslq-5.7
From the database	192.168.24.130	Centos7 myslq-5.7

Configuring the installation of two MYSLQ services

Detailed configuration View "MySQL Advanced Simple Analysis"

In the primary database server-side operation, edit the configuration file as follows

[[email protected] ~]# vim /etc/my.cnf[[email protected] ~]# cat /etc/my.cnf[mysqld]basedir = /usr/local/mysqldatadir = /opt/datasocket = /tmp/mysql.sockport = 3306pid-file = /opt/data/mysql.piduser = mysqlskip-name-resolve//添加以下内容# GTIDserver-id=128  //主服务器idgtid-mode=on //开启gtid模式enforce-gtid-consistency=on //强制gtid一致性，开启后对于特定create table不被支持# binloglog_bin=master-binloglog-slave-updates=1binlog_format=row //强烈建议，其他格式可能导致数据不一致# relay  logskip_slave_start=1

Restart MYSLQ Service

Create a synchronization account on the primary database to be authorized to use from the database

[[email protected] ~]# mysql -uroot -pEnter password:Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 4Server version: 5.7.22 MySQL Community Server (GPL)Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql> create user ‘repl‘@‘192.168.24.130‘ identified by ‘123456‘;Query OK, 0 rows affected (0.06 sec)mysql> grant replication slave on *.* to ‘repl‘@‘192.168.24.130‘;Query OK, 0 rows affected (0.01 sec)mysql> flush privileges;Query OK, 0 rows affected (0.03 sec)

Read locks to the primary database

Open another terminal to read the lock on the database to avoid inconsistencies in the data synchronization caused by other people writing during the backup

  mysql> flush tables with read lock;Query OK, 0 rows affected (0.17 sec) ////此锁表的终端必须在备份完成以后才能退出（退出锁表失效）  

Edit the configuration file from the database server operation as follows

[[email protected] ~]# cat /etc/my.cnf[mysqld]basedir = /usr/local/mysqldatadir = /opt/datasocket = /tmp/mysql.sockport = 3306pid-file = /opt/data/mysql.piduser = mysqlskip-name-resolve# GTIDgtid_mode=onenforce_gtid_consistency=onserver_id=130# binloglog-bin=slave-binloglog-slave-updates=1binlog-format=row //强烈建议，其他格式可能导致数据不一致# relay logskip-slave-start=1

Restarting the MySQL server

Configure and start master-slave replication

mysql> change master to    -> master_host=‘192.168.24.128‘,    ->  master_user=‘repl‘,    ->  master_password=‘123456‘,    -> master_port=3306,    

To view the status from the server:

    Mysql> Show slave status\g;*************************** 1. Row *************************** slave_io_state:waiting for master to send event Master_hos              t:192.168.24.128 Master_user:repl master_port:3306 connect_retry:60 master_log_file:master-binlog.000001 read_master_log_pos:154 Relay_log_file:linfan-r elay-bin.000002 relay_log_pos:375 relay_master_log_file:master-binlog.000001 Slave_io_ Running:yes//Must be yes here Slave_sql_running:yes//must be yes here Replicate_do_db:replicate_igno Re_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Ta ble:last_errno:0 last_error:skip_counter:0 Exec_master_lo  g_pos:154 relay_log_space:583 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:0master_ssl_verify_server_cert:no last_io_errno:0 Last_io_er ror:last_sql_errno:0 Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server _id:128 master_uuid:3396fe35-b1e1-11e8-81bb-000c292340f6 master_info_file:/opt/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: ExecuTed_gtid_set:auto_position:1 Replicate_Rewrite_DB:Channel_Name:Master _tls_version:1 row in Set (0.00 sec)

Unlock the lock table State of the main library and exit the interactive interface directly

mysql> quitBye

Validation: Creating a library in the primary database doudou

mysql> create database doudou;Query OK, 1 row affected (0.01 sec)mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || doudou             || mysql              || performance_schema || sys                |+--------------------+5 rows in set (0.03 sec)

To see if the synchronization exists from the database server doudouLibrary

mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || doudou             || mysql              || performance_schema || sys                |+--------------------+5 rows in set (0.01 sec)

Configure MySQL Gtid master-slave replication detailed parsing and steps