MySQL 5.7 GTID Replication steps

-

MySQL 5.7 GTID(Global Transactions Identifiers) replication setup
So far we are using binlog replication to sync data from master to slave. Here the problem comes. what if we want to do some slave topology changes. this includes much work to re-order slaves in a replication chain. More complex topology changes could require an admin to stop replication on the hosts involved.Now with the GTID configuratio,n we can easily move slaves in replication chain without stopping the replication.

Here I'm setting up replication between a master and 4 slaves.
master.0                  10.0.0.100
slave.1                    10.0.0.101
slave.2                    10.0.0.102
slave.3                    10.0.0.103
slave.4                    10.0.0.104

I'm using  MySQL 5.7.24 on the master server. So installed the same in slave servers to avoid compatibility issues.
(while setting up slaves you should first ensure that all slaves of this master are the same version ).

Note: this blog will not cover installation of MySQL .for MySQL 5.7 installation please check here.

master.0

root@master.0 $ ps -ef|grep mysql
mysql     2560 1  0 03:36 ?     00:00:01 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
root@master.0 $ mysqld --version
mysqld  Ver 5.7.24-27 for Linux on x86_64 (Percona Server (GPL), Release 27, Revision bd42700)

slave.1

root@slave.1 $ ps -ef|grep mysql
mysql     2553 1  0 03:57 ?     00:00:01 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
root@slave.1 $ mysqld --version
mysqld  Ver 5.7.24-27 for Linux on x86_64 (Percona Server (GPL), Release 27, Revision bd42700)

slave.2

root@slave.2 $ ps -ef|grep mysql
mysql     2558 1  0 03:58 ?     00:00:01 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
root@slave.2 $ mysqld --version
mysqld  Ver 5.7.24-27 for Linux on x86_64 (Percona Server (GPL), Release 27, Revision bd42700)

slave.3

root@slave.3 $ ps -ef|grep mysql
mysql     2547 1  0 03:59 ?     00:00:01 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
root@slave.3 $ mysqld --version
mysqld  Ver 5.7.24-27 for Linux on x86_64 (Percona Server (GPL), Release 27, Revision bd42700)

slave.4

root@slave.4 $ ps -ef|grep mysql
mysql     2422 1  0 04:00 ?     00:00:01 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
root@slave.4 $ mysqld --version
mysqld  Ver 5.7.24-27 for Linux on x86_64 (Percona Server (GPL), Release 27, Revision bd42700)

Below are few important variables which can be used in GTID replication :

  1. enforce_gtid_consistency: (OFF|ON|WARN).
  • OFF: non-transactionally safe statements are OK.
  • WARN: non-transactionally safe statements will raise a warning but also OK.
  • ON: non-transactionally safe statements will raise errors and not be executed.
  1. gtid_mode: (OFF|OFF_PERMISSIVE|ON_PERMISSIVE|ON)
  • OFF: Master will not generate GTIDs, slave will not replicate GTIDs.
  • OFF_PERMISSIVE: same like OFF but the slave can replicate both GTID and non GTID transactions.
  • ON: Master generates GTIDs only, slave replicates GTIDs only.
  • ON_PERMISSIVE: same like ON but the slave can replicate both GTID and non GTID transactions.

Master configuration:
  • Set configuration on master MySQL to enable GTID.

[mysqld]
server-id = 100
log-bin = mysql-bin
binlog_format = ROW
gtid_mode = on
enforce_gtid_consistency
log_slave_updates

Restart master mysql to apply the configuration changes:

root@master.0 $ service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service
  • 2 .   Create a replication user in master mysql for slave .

GRANT REPLICATION SLAVE ON *.* TO 'slave_gtid_user'@'10.0.0.102' IDENTIFIED BY 'S3cr3t@123'
GRANT REPLICATION SLAVE ON *.* TO 'slave_gtid_user'@'10.0.0.102' IDENTIFIED BY 'S3cr3t@123';
GRANT REPLICATION SLAVE ON *.* TO 'slave_gtid_user'@'10.0.0.103' IDENTIFIED BY 'S3cr3t@123';
GRANT REPLICATION SLAVE ON *.* TO 'slave_gtid_user'@'10.0.0.104' IDENTIFIED BY 'S3cr3t@123';
FLUSH PRIVILEGES;


Slave Configuration:
  • Set configuration on slave MySQL to enable GTID.

 slave.1

[mysqld]
server_id = 101
log_bin = mysql-bin
binlog_format = ROW
skip_slave_start
gtid_mode = on
enforce_gtid_consistency
log_slave_updates

Restart slave.1 mysql to apply the configuration changes:

root@slave.1 $ service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service

slave.2

[mysqld]
server_id = 102
log_bin = mysql-bin
binlog_format = ROW
skip_slave_start
gtid_mode = on
enforce_gtid_consistency
log_slave_updates

Restart slave.2 mysql to apply the configuration changes:

root@slave.2 $ service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service

slave.3

[mysqld]
server_id = 103
log_bin = mysql-bin
binlog_format = ROW
skip_slave_start
gtid_mode = on
enforce_gtid_consistency
log_slave_updates

Restart slave.3 mysql to apply the configuration changes:

root@slave.3 $ service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service

slave.4

[mysqld]
server_id = 104
log_bin = mysql-bin
binlog_format = ROW
skip_slave_start
gtid_mode = on
enforce_gtid_consistency
log_slave_updates

Restart slave.4 mysql to apply the configuration changes:

root@slave.4 $ service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service

Once the configuration is completed on both master and slaves .take the backup of master and restore it in slaves .

root@master.0 $ mysqldump -uroot -p --all-databases --flush-privileges  --single-transaction \
--flush-logs --triggers --routines --events --hex-blob>Backup_latest-`date +"%m-%d-%y"`.sql
Enter password:
root@master.0 $
root@master.0 $
root@master.0 $ ls -lhrt Backup_latest-02-08-19.sql
-rw-r--r--. 1 root root 4.1M Feb  8 06:42 Backup_latest-02-08-19.sql

root@master.0 $ grep GTID_PURGED Backup_latest-02-08-19.sql
SET @@GLOBAL.GTID_PURGED='5a682978-2b07-11e9-a1c4-5254008481d5:1-6';


Note: As we enabled GTID on master .Backup file records GTID_PURGED value once backup is successful.This is used by slave to start replication .



Copy backups to slave servers.

scp Backup_latest-02-08-19.sql root@10.0.0.101:/backup
scp Backup_latest-02-08-19.sql root@10.0.0.102:/backup
scp Backup_latest-02-08-19.sql root@10.0.0.103:/backup
scp Backup_latest-02-08-19.sql root@10.0.0.104:/backup

 Restore backups in all the slave servers :

root@slave.1 $ mysql -uroot -p <Backup_latest-02-08-19.sql
root@slave.2 $ mysql -uroot -p <Backup_latest-02-08-19.sql
root@slave.3 $ mysql -uroot -p <Backup_latest-02-08-19.sql
root@slave.4 $ mysql -uroot -p <Backup_latest-02-08-19.sql

         
Execute the CHANGE MASTER TO command on all slaves and then start slaves. Here it uses the GTID which we got after backup restoration.

slave.1

mysql> CHANGE MASTER TO  
MASTER_HOST='10.0.0.100',
MASTER_PORT=3306,
MASTER_USER='slave_gtid_user',
MASTER_PASSWORD='S3cr3t@123',
MASTER_AUTO_POSITION=1;
mysql>START SLAVE;

slave.2

mysql> CHANGE MASTER TO  
MASTER_HOST='10.0.0.100',
MASTER_PORT=3306,
MASTER_USER='slave_gtid_user',
MASTER_PASSWORD='S3cr3t@123',
MASTER_AUTO_POSITION=1;
mysql>START SLAVE;

slave.3

mysql> CHANGE MASTER TO  
MASTER_HOST='10.0.0.100',
MASTER_PORT=3306,
MASTER_USER='slave_gtid_user',
MASTER_PASSWORD='S3cr3t@123',
MASTER_AUTO_POSITION=1;
mysql>START SLAVE;

slave.4

mysql> CHANGE MASTER TO  
MASTER_HOST='10.0.0.100',
MASTER_PORT=3306,
MASTER_USER='slave_gtid_user',
MASTER_PASSWORD='S3cr3t@123',
MASTER_AUTO_POSITION=1;
mysql>START SLAVE;

 Here is the slave status :


mysql> show slave status\G;
*************************** 1. row ***************************
                 Slave_IO_State: Waiting for master to send event
                 Master_Host: 10.0.0.100
                 Master_User: slave_gtid_user
                 Master_Port: 3306
                 Connect_Retry: 60
                 *
                 *
                 *
                 Master_UUID: 5a682978-2b07-11e9-a1c4-5254008481d5
                 Master_Info_File: /var/lib/mysql/master.info
                 SQL_Delay: 0
                 Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
                 Master_Retry_Count: 86400
                  *
                  *
                  *
                 Executed_Gtid_Set: 5a682978-2b07-11e9-a1c4-5254008481d5:1-6
                 Auto_Position: 1
                 Replicate_Rewrite_DB:
                 Channel_Name:
                 Master_TLS_Version:

1 row in set (0.00 sec)


  • Test your replication setup with some test values from master.

Comments

Post a Comment

Popular posts from this blog

MySQL [ERROR] Can't start server: Bind on TCP/IP port: Permission denied

-
Image
Below is the error when I am trying to change the port of a MySQL server from 3306 to 3308. MySQL [ERROR] Can't start server: Bind on TCP/IP port: Permission denied [ERROR] Do you already have another mysqld server running on port: 3308? I have not seen any MySQL instance already running with port 3308 . After doing some troubleshooting i found that SELinux enabled on my server which is blocking this non-standard MySQL port 3308. root@slave.1 $ cat /etc/sysconfig/selinux # This file controls the state of SELinux on the system. # SELINUX= can take one of these three values: #     enforcing - SELinux security policy is enforced. #     permissive - SELinux prints warnings instead of enforcing. #     disabled - No SELinux policy is loaded. SELINUX=enforcing # SELINUXTYPE= can take one of three values: #     targeted - Targeted processes are protected, #     minimum - Modification of targeted policy. Only selected processes are protected. #     mls - Multi Le
Read more