mysqlGTID master-slave configuration

Introduction to GTID master slave

GTID is a transaction ID generated based on mysql, which consists of server ID and transaction ID.
This ID is unique on both the master and slave databases.
This feature can make the master-slave replication of mysql easier and more reliable.

GTID advantage

  1. It is simpler to realize synchronization, and no need to find log file and log POS
  2. GTID is continuous without interruption, high data consistency and loss
  3. Easier to build master-slave
  4. Safer than drive master slave

GTID disadvantage

  1. Non transactional database in master-slave relationship is not supported
  2. CREATE TABLE is not supported SELECT statement
  3. Operations on temporary tables are not supported:
  4. After GTID is enabled, the original traditional replication method can no longer be used;

Configure GTID master slave

Environmental Science:

  • Master database
    • CentOS7/RedHat7
    • IP-192.168.233.129
    • Hostname-Lynk
    • Data available
  • Sub database
    • CentOS7/RedHat7
    • IP-192.168.233.247
    • Hostname-Hyrule
    • No data

Authorize from library

#The following operations are performed in the main library
mysql> CREATE USER 'repl'@'192.168.233.247' IDENTIFIED BY 'repl123';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.233.247';
Query OK, 0 rows affected (0.00 sec)

Ensure data consistency

#Open a new terminal to lock the table of the main database to prevent other people from writing during configuration. During locking the table, you cannot close the terminal or exit the mysql interactive command line
mysql> FLUSH TABLES WITH READ LOCK;

#Master repository
[root@lynk ~]# mysqldump -uroot -plynk123~ --all-databases > /opt/all-201902271749.sql
#Copy backup files to from library
[root@lynk ~]# scp /opt/all-201902271749.sql root@192.168.233.247:/opt/

#Restore the backup of the master database from the database
[root@Hyrule ~]# mysql -uroot -plynk123~ < /opt/all-201902271749.sql 

Configure main library

[root@lynk ~]# vim /etc/my.cnf
#Add the following
#Unique identifier of the database server. The server ID value of the master database must be larger than that of the slave database
server_id=1
gtid_mode=on 
#Force gtid consistency. It is not supported for a specific create table after it is enabled
enforce_gtid_consistency=on 
log_bin=master-binlog
log-slave-updates=1
binlog_format=row 
skip_slave_start=1

#To end the lock table status of the main database, just exit the mysql interactive command line in another terminal
mysql> quit
#Restart main library
[root@lynk ~]# systemctl restart mysqld

Configuration slave Library

[root@Hyrule ~]# vim /etc/my.cnf
#Add the following
gtid_mode=on
enforce_gtid_consistency=on
server_id=2
log-bin=slave-binlog
log-slave-updates=1
binlog_format=row 
skip_slave_start=1

#Restart slave Library
[root@Hyrule ~]# systemctl restart mysqld

#Configure master-slave replication
mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.233.129',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='repl123',
    -> MASTER_PORT=3306,
    -> MASTER_AUTO_POSITION = 1;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

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

mysql> show slave status \G
#Make sure the following two are Yes
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

Tags: Linux MySQL Database SQL vim

Posted on Sun, 01 Dec 2019 17:56:02 -0800 by aosmith