MySql GTID-Based Master Slave Replication – Single Database

In previous blog we saw How to create MySql File-based Master Slave Replication – All Database Click here to read more.

GTID Replication:

In this post, we will see How to create MySql GTID-based Master Slave Replication – Single DB.

The newer method based on global transaction identifiers (GTIDs) is transactional and therefore does not require working with log files or positions within these files.

Replication victimization GTIDs guarantees consistency between master and slave as long as all transactions committed on the master have conjointly been applied on the slave.

It is possible to spot a transaction uniquely across the replication servers. Make the automation of the failover process much easier. There is no need to do calculations, inspect the binary log, and so on. Just MASTER_AUTO_POSITION=1.

At the application level, it is easier to do a WRITE/READ split. After a write on the MASTER, you have a GTID so just check if that GTID has been executed on the SLAVE that you use for reads.

To fulfill this demonstration, we have already configured two MySql servers on two different virtual machines. We will use repl_test schema for replication.

Host name	IP	Role	Installation Link
test-machine01	192.168.114.170	Master	Click Here
test-machine02	192.168.114.176	Slave	Click Here

Step 1: Configure Master Server for Replication: Add the below parameter in Master my.cnf file and restart mysqld service. Please note we are using server_id = 1. Use command show master status to check current GTID position.

[root@test-machine01 ~]# vi /etc/my.cnf
[mysqld@inst1]
#General
user = mysql
port = 3306
server_id = 1
socket=/var/lib/mysql/mysql.sock
pid_file=/var/run/mysqld/mysqld.pid

# Data Storage
datadir=/var/lib/mysql
innodb_directories="/u01/mysql/"

#Logging
log_bin = /var/lib/mysql/binlog
log_error = /var/lib/mysql/mysqld.log
expire_logs_days = 7
relay_log = /var/lib/mysql/relay_bin01
relay_log_index = /var/lib/mysql/relay_bin.index
relay_log_recovery = on
master_info_repository = TABLE
relay_log_info_repository = TABLE

# GTID Based Replication Parameter
gtid-mode=on
enforce-gtid-consistency=1
log_slave_updates= 1

[root@test-machine01 etc]# systemctl restart mysqld@inst1
[root@test-machine01 etc]#

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+---------------+----------+--------------+------------------+-------------------------------------------+
| binlog.000025 |     8234 |              |                  | 13ecba9c-444a-11eb-a397-000c29f9d9e6:1-32 |
+---------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)

mysql>

Step 2: Create Replication User: Login into Master MySQL Instance as root user and create the slave user and grant privileges for replication.

mysql> CREATE USER IF NOT EXISTS 'repluser'@'%' IDENTIFIED BY 'Password123#@!';
Query OK, 0 rows affected (0.33 sec)

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repluser'@'%';
Query OK, 0 rows affected (0.07 sec)

mysql> ALTER USER 'repluser'@'%' IDENTIFIED WITH mysql_native_password BY 'Password123#@!';
Query OK, 0 rows affected (0.04 sec)

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

Step 3: Take a backup of repl_test schema: Use the below command to take a backup of repl_test schema and scp to test-machine02 server. Please note we are using option –set-gtid-purged=ON to capture GTID information from the master server.

[root@test-machine01 test1]# mysqldump -hlocalhost -uroot -p --master-data  --set-gtid-purged=ON repl_test > /u01/repl_bkp/repl_test.sql
Enter password:
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
[root@test-machine01 test1]#
[root@test-machine01 test1]#


[root@test-machine01 repl_bkp]# scp repl_test.sql [email protected]:/u01/repl_bkp
[email protected]'s password:
repl_test.sql                                                                                                                                               100% 6879     5.2MB/s   00:00
[root@test-machine01 repl_bkp]#

Step 4: Configure Slave Server for Replication: Add the below parameter in Master my.cnf file and restart mysqld service. Please note we are using server_id = 2.

[root@test-machine02 ~]# vi /etc/my.cnf
[mysqld@inst1]
#General
user = mysql
port = 3306
server_id = 2
socket=/u01/mysql-8/mysql.sock
pid_file=/u01/mysql-8/mysqld.pid

# Data Storage
basedir=/u01/mysql-8
datadir=/u01/mysql-8/data
innodb_directories="/u01/mysql-8;/u01/mysql"

#Logging
log_bin = /u01/mysql-8/data/binlog
log_error = /u01/mysql-8/data/mysqld.log
expire_logs_days = 7
relay_log = /u01/mysql-8/data/relay_bin01
relay_log_index = /u01/mysql-8/data/relay_bin.index
relay_log_recovery = on
master_info_repository = TABLE
relay_log_info_repository = TABLE

# GTID Based Replication Parameter
gtid-mode=on
enforce-gtid-consistency=1
log_slave_updates= 1
read-only=1
replicate-do-db=repl_test
#super_read_only = 1

[root@test-machine02 ~]# systemctl restart mysqld@inst1
[root@test-machine02 ~]#

Alert: After a restart of the slave instance please avoid running any transaction in the slave instance otherwise GTID_PURGED & GTID_EXECUTED variable will be updated and you will receive an error while import.

[root@test-machine02 repl_bkp]# mysql -hlocalhost -uroot -p -S/u01/mysql-8/mysql.sock repl_test  < repl_test.sql
Enter password:
ERROR 3546 (HY000) at line 24: @@GLOBAL.GTID_PURGED cannot be changed: the added gtid set must not overlap with @@GLOBAL.GTID_EXECUTED
[root@test-machine02 repl_bkp]#

Step 5: Import repl_test schema in Slave instance: Use the below command to import repl_test schema in slave mysql instance. After the import is finished you will notice that variables GTID_PURGED & GTID_EXECUTED are updated.

mysql> create database repl_test;
Query OK, 1 row affected (0.05 sec)

mysql>


[root@test-machine02 repl_bkp]# mysql -hlocalhost -uroot -p -S/u01/mysql-8/mysql.sock repl_test  < repl_test.sql
Enter password:
[root@test-machine02 repl_bkp]#


mysql> show global variables like 'GTID_PURGED';
+---------------+-------------------------------------------+
| Variable_name | Value                                     |
+---------------+-------------------------------------------+
| gtid_purged   | 13ecba9c-444a-11eb-a397-000c29f9d9e6:1-32 |
+---------------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> show global variables like 'GTID_EXECUTED';
+---------------+-------------------------------------------+
| Variable_name | Value                                     |
+---------------+-------------------------------------------+
| gtid_executed | 13ecba9c-444a-11eb-a397-000c29f9d9e6:1-32 |
+---------------+-------------------------------------------+
1 row in set (0.00 sec)

mysql>

Step 6: Update master info in Slave instance: Use the below command to update Slave mysql instance with Master details and start slave.

mysql> CHANGE MASTER TO MASTER_HOST='192.168.114.170',MASTER_USER='repluser',MASTER_PASSWORD='Password123#@!',MASTER_PORT=3306,MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql>  START SLAVE;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>  SHOW SLAVE STATUS \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.114.170
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000025
          Read_Master_Log_Pos: 7503
               Relay_Log_File: relay_bin01.000002
                Relay_Log_Pos: 409
        Relay_Master_Log_File: binlog.000025
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: repl_test
          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: 7503
              Relay_Log_Space: 614
              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: 13ecba9c-444a-11eb-a397-000c29f9d9e6
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has 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: 13ecba9c-444a-11eb-a397-000c29f9d9e6:1-32
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set, 1 warning (0.00 sec)

mysql>

Step 7-1: Test the replication: Add some transactions in the Master instance.

Master
----------------------------------
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

mysql>

mysql>  create table repl_test7 select * from repl_test2;
Query OK, 14 rows affected (0.01 sec)
Records: 14  Duplicates: 0  Warnings: 0

mysql> show tables;
+---------------------+
| Tables_in_repl_test |
+---------------------+
| repl_test1          |
| repl_test2          |
| repl_test3          |
| repl_test4          |
| repl_test5          |
| repl_test6          |
| repl_test7          |
+---------------------+
7 rows in set (0.01 sec)

mysql>

Step 7-2: Check-in Slave Side: Check if the above transaction updated in Slave Side.

Slave
----------------------------------
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)

mysql>


mysql> select count(*) from repl_test7;
+----------+
| count(*) |
+----------+
|       14 |
+----------+
1 row in set (0.01 sec)

mysql>

This document is only for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the latest update. Click here to know more about our pursuit.