Gtid master slave replication

1. Concept of gtid

  • Global transaction id, one transaction of Gtid corresponds to one id, and during master-slave synchronization, the transaction id submitted on each master generates a unique transaction id when copied to the cluster.
  • Instead of using master log file and master log POS to start replication, Gtid is used to replace the traditional replication method. Instead, the master position method is used for replication, but it is only supported from MySQL-5.6.6
  • In the traditional slave side, binlog does not need to be enabled, but the binlog function needs to be enabled on Gtid to record the executed Gtid

2. Working principle of gtid

  1. When a transaction is executed and committed on the primary database side, it will generate a GTID and record it in the binlog log;

  2. After the binlog is transferred to the slave and stored in the slave's relalog log, read the GTID set by the GTID value_ The next variable tells the slave the GTID value to be executed next;

  3. The sql thread obtains the GTID in the relalog log, and then compares whether the binlog log on the slave side has the GTID;

  4. If there is a record indicating that the GTID transaction has been executed, the slave will be ignored;

  5. If there is no record, the slave will execute the GTID transaction and record it in the binlog log of the GTID itself. Before reading the executed transaction, it will check that other session s hold the GTID to ensure that it will not be executed repeatedly;

6. During the parsing process, we will judge whether there is a primary key. If not, we will use the secondary index. If not, we will use all scans.

3. Advantages of gtid

  • To implement a simpler failover, you don't need to find log file and log POS as before
  • Can more easily build master-slave
  • Safer than traditional replication
  • GTID is continuous without holes, ensuring data consistency and zero loss.

4. Master slave synchronization of gtid

4.1 deploying MySQL database

The following operations are the same as master and slave

download rpm Package and unzip
[root@slave local]# wget 
https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz
[root@slave local]# tar -xf mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz
 establish MySQL user
[root@master local]# useradd -s -M -r mysql
 Create a soft connection and modify the owner and group
[root@master local]# ln -s mysql-5.7.32-linux-glibc2.12-x86_64/ mysql
[root@master local]# chown -R mysql
 Configure environment variables
[root@master local]# echo 'export PATH=/usr/local/mysq/bin:$PATH' > /etc/profile.d/mysql.sh
[root@master local]# source /etc/profile.d/mysql.sh
 Create a directory to store database data and modify the owner and group
[root@master local]# mkdir /opt/data/
[root@master local]# chown -R mysql.mysql /opt/data/
Write the configuration file and add two lines
[root@master ~]# cd /usr/local/mysql/support-files/
[root@master support-files]# ls
magic  mysqld_multi.server  mysql-log-rotate  mysql.server
[root@master support-files]# cp mysql.server /etc/init.d/mysqld
[root@master support-files]# vim /etc/init.d/mysqld
basedir=/usr/local/mysql
datadir=/opt/data
 Initialize the database, then log in to the database with the generated random password, and then modify the new password
[root@master local]# mysqld --initialize --user mysql --datadir /opt/data/
[root@master local]# service mysqld start 
Starting MySQL........ SUCCESS! 
[root@master local]# mysql -uroot -p
mysql> set password=password('New password');

4.2 preparation of configuration files

Configuration file of master library

[mysqld]
port = 3306
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
pid-file = /opt/data/mysqld.pid
skip-name-resolve

server-id = 10
log-bin = mysql_bin
gtid-mode = on
enforce-gtid-consistency = on
binlog-format = row
log-slave-updates = 1
skip-slave-start = 1

//Profile from library

[root@slave ~]# vim /etc/my.cnf
[mysqld]
port = 3306
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
pid-file = /opt/data/mysqld.pid
skip-name-resolve

server-id = 20
log-bin = mysql_bin
binlog_format = row
skip-slave-start = 1
gtid-mode = on
log-slave-updates = 1
enforce-gtid-consistency = on

4.3 restart the MySQL service after modifying the configuration file

[root@master ~]# service mysqld restart 
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!

[root@slave ~]# service mysqld restart 
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@slave ~]#

4.4 check the status of gtid mode

mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | OFF       |
| gtid_executed_compression_period | 1000      |
| gtid_mode                        | OFF       |
| gtid_next                        | AUTOMATIC |
| gtid_owned                       |           |
| gtid_purged                      |           |
| session_track_gtids              | OFF       |
+----------------------------------+-----------+
8 rows in set (0.00 sec)

mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | ON        |
| gtid_executed_compression_period | 1000      |
| gtid_mode                        | ON        |
| gtid_next                        | AUTOMATIC |
| gtid_owned                       |           |
| gtid_purged                      |           |
| session_track_gtids              | OFF       |
+----------------------------------+-----------+
8 rows in set (0.01 sec)

//Authorize a user on the master library

mysql> grant replication slave on *.* to 'test'@'192.168.182.%' identified by'123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

//Configure GTID based replication on the slave library

mysql> change master to
    -> master_host='192.168.182.138',
    -> master_user='test',
    -> master_password='123',
    -> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.05 sec)

//Start slave

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.182.138
                  Master_User: test
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000002
          Read_Master_Log_Pos: 154
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql_bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

//Create a database on the main library for testing

mysql> create database box;
Query OK, 1 row affected (0.02 sec)

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

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

Turn off master-slave synchronization first, and then turn on synchronization. Can you synchronize the data after closing
This can be used when the main database has problems that need to be solved manually before operation.

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> use box
Database changed

mysql> create table me (id int not null auto_increment primary key,name varchar(100)not null,age tinyint);
Query OK, 0 rows affected (0.01 sec)

mysql> desc me
    -> ;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | NO   |     | NULL    |                |
| age   | tinyint(4)   | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.33 sec)

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

mysql> show tables;
+---------------+
| Tables_in_box |
+---------------+
| me            |
+---------------+
1 row in set (0.00 sec)

mysql> desc me
    -> ;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | NO   |     | NULL    |                |
| age   | tinyint(4)   | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.21 sec)

5. One master and many slaves

5.1 installing MySQL database

download rpm Package and unzip
[root@slave local]# wget 
https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz
[root@slave local]# tar -xf mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz
 establish MySQL user
[root@master local]# useradd -s -M -r mysql
 Create a soft connection and modify the owner and group
[root@master local]# ln -s mysql-5.7.32-linux-glibc2.12-x86_64/ mysql
[root@master local]# chown -R mysql
 Configure environment variables
[root@master local]# echo 'export PATH=/usr/local/mysq/bin:$PATH' > /etc/profile.d/mysql.sh
[root@master local]# source /etc/profile.d/mysql.sh
 Create a directory to store database data and modify the owner and group
[root@master local]# mkdir /opt/data/
[root@master local]# chown -R mysql.mysql /opt/data/
Write the configuration file and add two lines
[root@master ~]# cd /usr/local/mysql/support-files/
[root@master support-files]# ls
magic  mysqld_multi.server  mysql-log-rotate  mysql.server
[root@master support-files]# cp mysql.server /etc/init.d/mysqld
[root@master support-files]# vim /etc/init.d/mysqld
basedir=/usr/local/mysql
datadir=/opt/data
 Initialize the database, then log in to the database with the generated random password, and then modify the new password
[root@master local]# mysqld --initialize --user mysql --datadir /opt/data/
[root@master local]# service mysqld start 
Starting MySQL........ SUCCESS! 
[root@master local]# mysql -uroot -p
mysql> set password=password('New password');

5.2 write the configuration file of the main library

[mysqld]
port = 3306
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
pid-file = /opt/data/mysqld.pid
skip-name-resolve

server-id = 10
log-bin = mysql_bin
gtid-mode = on
enforce-gtid-consistency = on
binlog-format = row
log-slave-updates = 1
skip-slave-start = 1

5.3 then authorize a user on the main library

mysql> grant replication slave on *.* to 'test'@'192.168.182.%' identified by'123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

5.4 when writing the configuration file / etc/my.cnf from the slave library, you need to do both slave libraries

[root@slave ~]# vim /etc/my.cnf
[mysqld]
port = 3306
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
pid-file = /opt/data/mysqld.pid
skip-name-resolve

server-id = 20
log-bin = mysql_bin
binlog_format = row
skip-slave-start = 1
gtid-mode = on
log-slave-updates = 1
enforce-gtid-consistency = on

[root@slave ~]# vim /etc/my.cnf
[mysqld]
port = 3306
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
pid-file = /opt/data/mysqld.pid
skip-name-resolve

server-id = 21
log-bin = mysql_bin
binlog_format = row
skip-slave-start = 1
gtid-mode = on
log-slave-updates = 1
enforce-gtid-consistency = on

5.5 finally restart MySQL database

[root@master ~]# service mysqld restart 
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!

[root@slave ~]# service mysqld restart 
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@slave ~]#

mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | ON        |
| gtid_executed_compression_period | 1000      |
| gtid_mode                        | ON        |
| gtid_next                        | AUTOMATIC |
| gtid_owned                       |           |
| gtid_purged                      |           |
| session_track_gtids              | OFF       |
+----------------------------------+-----------+
8 rows in set (0.00 sec)

mysql> change master to
    -> master_host='192.168.182.138',
    -> master_user='test',
    -> master_password='123',
    -> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

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

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.182.138
                  Master_User: test
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000002
          Read_Master_Log_Pos: 545
               Relay_Log_File: slave2-relay-bin.000002
                Relay_Log_Pos: 758
        Relay_Master_Log_File: mysql_bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

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

mysql> use box;
mysql> desc me;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | NO   |     | NULL    |                |
| age   | tinyint(4)   | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

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

mysql> use box;
mysql> desc me;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | NO   |     | NULL    |                |
| age   | tinyint(4)   | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

6. Two masters and one slave

6.1 installing MySQL database

download rpm Package and unzip
[root@slave local]# wget 
https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz
[root@slave local]# tar -xf mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz
 establish MySQL user
[root@master local]# useradd -s -M -r mysql
 Create a soft connection and modify the owner and group
[root@master local]# ln -s mysql-5.7.32-linux-glibc2.12-x86_64/ mysql
[root@master local]# chown -R mysql
 Configure environment variables
[root@master local]# echo 'export PATH=/usr/local/mysq/bin:$PATH' > /etc/profile.d/mysql.sh
[root@master local]# source /etc/profile.d/mysql.sh
 Create a directory to store database data and modify the owner and group
[root@master local]# mkdir /opt/data/
[root@master local]# chown -R mysql.mysql /opt/data/
Write the configuration file and add two lines
[root@master ~]# cd /usr/local/mysql/support-files/
[root@master support-files]# ls
magic  mysqld_multi.server  mysql-log-rotate  mysql.server
[root@master support-files]# cp mysql.server /etc/init.d/mysqld
[root@master support-files]# vim /etc/init.d/mysqld
basedir=/usr/local/mysql
datadir=/opt/data
 Initialize the database, then log in to the database with the generated random password, and then modify the new password
[root@master local]# mysqld --initialize --user mysql --datadir /opt/data/
[root@master local]# service mysqld start 
Starting MySQL........ SUCCESS! 
[root@master local]# mysql -uroot -p
mysql> set password=password('New password');

6.2 compiling database configuration files

Main library 1
[mysqld]
port = 3306
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
pid-file = /opt/data/mysqld.pid
skip-name-resolve

server-id = 10
log-bin = mysql_bin
gtid-mode = on
enforce-gtid-consistency = on
binlog-format = row
log-slave-updates = 1
skip-slave-start = 1

Main library 2
[mysqld]
port = 3306
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
pid-file = /opt/data/mysqld.pid
skip-name-resolve

server-id = 11
log-bin = mysql_bin
gtid-mode = on
enforce-gtid-consistency = on
binlog-format = row
log-slave-updates = 1
skip-slave-start = 1

6.3 write configuration file of slave Library

[root@slave ~]# vim /etc/my.cnf
[mysqld]
port = 3306
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
pid-file = /opt/data/mysqld.pid
skip-name-resolve

server-id = 20
log-bin = mysql_bin
binlog_format = row
skip-slave-start = 1
gtid-mode = on
log-slave-updates = 1
enforce-gtid-consistency = on

6.4 restart MySQL database

[root@master ~]# service mysqld restart 
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!

[root@master ~]# service mysqld restart 
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!

[root@slave ~]# service mysqld restart 
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@slave ~]#

6.5 authorizing a user

Main library 1
mysql> grant replication slave on *.* to 'test1'@'192.168.182.140' identified by '123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

Main library 2
mysql> grant replication slave on *.* to 'test1'@'192.168.182.140' identified by '123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

Configure on slave Library GTID Replication of
mysql> change master to
    -> master_host='192.168.182.139',
    -> master_user='test1',
    -> master_password='123',
    -> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.04 sec)

mysql> change master to
    -> master_host='192.168.182.138',
    -> master_user='test1',
    -> master_password='123',
    -> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.04 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.182.139
                  Master_User: test1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000003
          Read_Master_Log_Pos: 154
               Relay_Log_File: slave2-relay-bin.000002
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql_bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

Create a library on the second master library, and the slave library can also be synchronized, box Is the library created above the first main library
mysql> create database x;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| box                |
| mysql              |
| performance_schema |
| sys                |
| x                  |
+--------------------+
6 rows in set (0.00 sec)

Keywords: Database MySQL

Added by anxiety on Fri, 12 Nov 2021 15:19:30 +0200

Popular Keywords

©2024 Programming VIP