Step-by-step MySQL GTID Replication Setup
I use MySQL replication extensively, It’s simple and robust, It’s also one main reason I love MySQL lot compared to other database systems. I basically use MySQL replication for performance, scalability, high availability, reliability, failover, fault tolerance etc. I have written blogs in the past on MySQL Master-Slave ( https://minervadb.com/index.php/2018/01/24/mysql-master-slave-replication-on-ubuntu-xenial-16-04/ & https://minervadb.com/index.php/2018/01/24/mysql-master-slave-replication-on-ubuntu-xenial-16-04-2/ ), Master-Master replication (https://minervadb.com/index.php/2018/01/24/mysql-master-slave-replication-on-ubuntu-xenial-16-04-2/) , delayed replication (https://minervadb.com/index.php/2018/01/29/how-to-lag-a-mysql-slave-behind-to-avoid-a-data-corruption/) etc. MySQL introduced global transaction identifiers (GTIDs) in MySQL 5.6.5 to make failover better and easy, GTID is a global transaction identifier which consists of two entities separated by a colon (:) , GTID looks like this – {source_id:transaction_id} / {c03c4200-0659-11e8-94ab-0800279747b5:1-2}, The source_id is server’s UUID and transaction_id is a sequence number . I have explained below step-by-step GTID based chained replication, One master and two slaves
- Master – 192.168.56.15
- Salve 1 – 192.68.56.16
- Slave 2 – 192.168.56.17
Table of Contents
MySQL My.CNF in the master – 192.168.56.15
[mysqld] !includedir /etc/mysql/conf.d/ !includedir /etc/mysql/mysql.conf.d/ server-id=900 log-bin=mysql-bin bind-address=192.168.56.15 binlog-do-db=employees gtid-mode = on enforce-gtid-consistency log-slave-updates
In this blog I have used mysqldump for backup so enabled system variable by setting “read_only” to ON
mysql> set global read_only=on; Query OK, 0 rows affected (0.00 sec)
root@VBOX110:/home/shiv# mysqldump --all-databases --single-transaction --triggers --routines --events --user=root --password=HardPassword/1947 > backup.sql
Once successfully completed back-up please disable system variable by setting “read_only” to OFF
mysql> set global read_only=off; Query OK, 0 rows affected (0.00 sec)
MySQL My.CNF in the slave – 192.168.56.16
[mysqld] !includedir /etc/mysql/conf.d/ !includedir /etc/mysql/mysql.conf.d/ server-id=910 log-bin=mysql-bin skip-slave-start gtid-mode=on enforce-gtid-consistency log-slave-updates
MySQL My.CNF in the slave – 192.168.56.17
!includedir /etc/mysql/conf.d/ !includedir /etc/mysql/mysql.conf.d/ server-id=930 log-bin=mysql-bin skip-slave-start gtid-mode=on enforce-gtid-consistency log-slave-updates
We need to create two pseudo-users in the master ( 192.168.56.15), This user will be used for replicating data between master and two slaves (192.168.56.16 and 192.168.56.17) .
mysql> create user 'repl_usr_16'@'192.168.56.16' identified by 'repl_usr_16' ; Query OK, 0 rows affected (0.02 sec) mysql> grant replication slave on *.* to 'repl_usr_16'@'192.168.56.16'; Query OK, 0 rows affected (0.00 sec)
mysql> create user 'repl_usr_17'@'192.168.56.17' identified by 'repl_usr_17' ; Query OK, 0 rows affected (0.02 sec) mysql> grant replication slave on *.* to 'repl_usr_17'@'192.168.56.17'; Query OK, 0 rows affected (0.00 sec)
Restore mysql backup from master (192.168.56.15) on slaves (192.168.56.16 and 192.168.56.17)
root@VBOX125:/home/shiv# mysql -u root -p < backup.sql Enter password:
Connect slaves (192.168.56.16 and 192.168.56.17) to the master (192.168.56.15)
Run “change master to” command in 192.168.56.16
mysql> change master to -> master_host='192.168.56.15', -> master_user='repl_usr', -> master_password='repl_usr', -> master_auto_position=1; Query OK, 0 rows affected, 2 warnings (0.01 sec)
start slave in 192.168.56.16
mysql> start slave; Query OK, 0 rows affected (0.01 sec)
Run “change master to” command in 192.168.56.17
mysql> change master to -> master_host='192.168.56.15', -> master_user='repl_usr', -> master_password='repl_usr', -> master_auto_position=1; Query OK, 0 rows affected, 2 warnings (0.01 sec) start slave in 192.168.56.17
mysql> start slave; Query OK, 0 rows affected (0.01 sec)
Confirm replication is successful from both slaves (192.168.56.16 and 192.168.56.17)
mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.56.15 Master_User: repl_usr Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 529 Relay_Log_File: VBOX-112-relay-bin.000002 Relay_Log_Pos: 742 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: 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: 529 Relay_Log_Space: 952 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: 900 Master_UUID: c03c4200-0659-11e8-94ab-0800279747b5 Master_Info_File: /var/lib/mysql/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: c03c4200-0659-11e8-94ab-0800279747b5:1-2 Executed_Gtid_Set: c03c4200-0659-11e8-94ab-0800279747b5:1-2 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) ERROR: No query specified
mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.56.15 Master_User: repl_usr_17 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 1008 Relay_Log_File: VBOX125-relay-bin.000002 Relay_Log_Pos: 893 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: 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: 1008 Relay_Log_Space: 1102 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: 900 Master_UUID: c03c4200-0659-11e8-94ab-0800279747b5 Master_Info_File: /var/lib/mysql/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: c03c4200-0659-11e8-94ab-0800279747b5:3-4 Executed_Gtid_Set: c03c4200-0659-11e8-94ab-0800279747b5:1-4 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)
