A global transaction identifier (GTID) is a unique identifier created and associated with each transaction committed on the server of origin (master). This identifier is unique not only to the server on which it originated, but is unique across all servers in a given replication setup. There is a 1-to-1 mapping between all transactions and all GTIDs. A GTID is represented as a pair of coordinates, separated by a colon character (:), as shown here:
GTID = source_id:transaction_idMySQL_gtid-master-slave
Advantages :
● Setting up MySQL replication is simple
● Consistency is guaranteed between master and slaves.
● Simple to determine inconsistency
● Fail-over process become much easier.
step.1. stop the both master and slave servers.
systemctl stop mysqld
step.2. configure the master server.
To enable GTID-based replication, each server must be started with GTID mode enabled by setting the gtid_mode variable to ON, and with the enforce_gtid_consistency variable enabled to ensure that only statements which are safe for GTID-based replication are logged.
vi /etc/my.cnf
add the following paramete under [mysql] section of my.cnf file
#master
server-id = 1
log-bin = mysql-bin
binlog_format = row
gtid-mode=ON
enforce-gtid-consistency
log-slave-updates
ESc:wq ENTER
step.3.Now start mysqld services
systemctl start mysqld
step.4. Checking master status
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 154
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
OR
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
and check gtid_executed display out blank
mysql> show global variables like 'gtid_executed';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_executed | |
+---------------+-------+
1 row in set (0.53 sec)
step.5. now create a replication user for slave servers
mysql> create user 'rpl_user'@'%' identified by 'Rpluser@123';
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to 'rpl_user'@'%';
Query OK, 0 rows affected (0.00 sec)
step.6.After create user checking master status,I am getting Executed_Gtid_Set
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 763
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 1e9e14b8-5479-11ea-ab42-000c29ba8011:1-3
1 row in set (0.00 sec)
mysql> show global variables like 'gtid_executed';
+---------------+------------------------------------------+
| Variable_name | Value |
+---------------+------------------------------------------+
| gtid_executed | 1e9e14b8-5479-11ea-ab42-000c29ba8011:1-3 |
+---------------+------------------------------------------+
1 row in set (0.00 sec)
step.7. Now taking full backup with mysqldump with master server
mysql> mysqldump --all-databases -flush-privileges --single-transaction --flush-logs --triggers --routines --events --user=root --password=XXXXXXXX > mysqlbackup_dump.sql;
step.8.Transfer backup master to slave server
[root@master ~]# scp mysqlbackup_dump.sql pankaj@192.168.145.128:.
pankaj@192.168.145.128's password:
mysqlbackup_dump.sql 100% 830KB 2.3MB/s 00:00
After that working on salve servers
step.9. stop mysqld services
systemctl stop mysqld
step.10.configure the slave server
[root@slave ~]# vi /etc/my.cnf
Add the following parameter under [mysqld] section of my.cnf file
#slave
server-id = 2
log-bin = mysql-bin
relay-log = relay-log-server
read-only = ON
gtid-mode=ON
enforce-gtid-consistency
log-slave-updates
ESC:wq ENTER
step.11.start mysqld services
systemctl start mysqld
step.12.Checking status for the slave server and gtid_executed.
mysql> show slave status\G
Empty set (0.00 sec)
mysql> show global variables like 'gtid_executed';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_executed | |
+---------------+-------+
1 row in set (0.03 sec)
step.13.Load the mysqldump in slave server.
mysql> source /home/pankaj/mysqlbackup_dump.sql ;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
after checking gtid_executed
mysql> show global variables like 'gtid_executed';
+---------------+------------------------------------------+
| Variable_name | Value |
+---------------+------------------------------------------+
| gtid_executed | 1e9e14b8-5479-11ea-ab42-000c29ba8011:1-4 |
+---------------+------------------------------------------+
1 row in set (0.00 sec)
step.14.Execute the Change Master
Tell the slave to use the master with GTID based transactions as the replication data source, and to use GTID-based auto-positioning rather than file-based positioning.
mysql>CHANGE MASTER TO
MASTER_HOST = '54.89.xx.xx',
MASTER_PORT = 3306,
MASTER_USER = 'repl_user',
MASTER_PASSWORD = 'XXXXXXXXX',
MASTER_AUTO_POSITION = 1;
step.15.start slave server
start slave;
step.16.checking slave status
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.X.X
Master_User: repl_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 184224424
Relay_Log_File: relay-log-server.000006
Relay_Log_Pos: 182845119
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: 184224424
Relay_Log_Space: 183090775
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: ffb64763-5ed6-11ea-b7d9-525400615ff4
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: ffb64763-5ed6-11ea-b7d9-525400615ff4:3-246839
Executed_Gtid_Set: 72b3a920-5ee7-11ea-b87b-e4434b359f7a:1-1121914,
f231b58b-3370-11e9-a458-8cec4b7a7fe5:1-10029,
ffb64763-5ed6-11ea-b7d9-525400615ff4:1-246839
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
note:- you can suucessfull step master slave replication
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Thank You
GTID = source_id:transaction_idMySQL_gtid-master-slave
Advantages :
● Setting up MySQL replication is simple
● Consistency is guaranteed between master and slaves.
● Simple to determine inconsistency
● Fail-over process become much easier.
step.1. stop the both master and slave servers.
systemctl stop mysqld
step.2. configure the master server.
To enable GTID-based replication, each server must be started with GTID mode enabled by setting the gtid_mode variable to ON, and with the enforce_gtid_consistency variable enabled to ensure that only statements which are safe for GTID-based replication are logged.
vi /etc/my.cnf
add the following paramete under [mysql] section of my.cnf file
#master
server-id = 1
log-bin = mysql-bin
binlog_format = row
gtid-mode=ON
enforce-gtid-consistency
log-slave-updates
ESc:wq ENTER
step.3.Now start mysqld services
systemctl start mysqld
step.4. Checking master status
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 154
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
OR
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
and check gtid_executed display out blank
mysql> show global variables like 'gtid_executed';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_executed | |
+---------------+-------+
1 row in set (0.53 sec)
step.5. now create a replication user for slave servers
mysql> create user 'rpl_user'@'%' identified by 'Rpluser@123';
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to 'rpl_user'@'%';
Query OK, 0 rows affected (0.00 sec)
step.6.After create user checking master status,I am getting Executed_Gtid_Set
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 763
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 1e9e14b8-5479-11ea-ab42-000c29ba8011:1-3
1 row in set (0.00 sec)
mysql> show global variables like 'gtid_executed';
+---------------+------------------------------------------+
| Variable_name | Value |
+---------------+------------------------------------------+
| gtid_executed | 1e9e14b8-5479-11ea-ab42-000c29ba8011:1-3 |
+---------------+------------------------------------------+
1 row in set (0.00 sec)
step.7. Now taking full backup with mysqldump with master server
mysql> mysqldump --all-databases -flush-privileges --single-transaction --flush-logs --triggers --routines --events --user=root --password=XXXXXXXX > mysqlbackup_dump.sql;
step.8.Transfer backup master to slave server
[root@master ~]# scp mysqlbackup_dump.sql pankaj@192.168.145.128:.
pankaj@192.168.145.128's password:
mysqlbackup_dump.sql 100% 830KB 2.3MB/s 00:00
After that working on salve servers
step.9. stop mysqld services
systemctl stop mysqld
step.10.configure the slave server
[root@slave ~]# vi /etc/my.cnf
Add the following parameter under [mysqld] section of my.cnf file
#slave
server-id = 2
log-bin = mysql-bin
relay-log = relay-log-server
read-only = ON
gtid-mode=ON
enforce-gtid-consistency
log-slave-updates
ESC:wq ENTER
step.11.start mysqld services
systemctl start mysqld
step.12.Checking status for the slave server and gtid_executed.
mysql> show slave status\G
Empty set (0.00 sec)
mysql> show global variables like 'gtid_executed';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_executed | |
+---------------+-------+
1 row in set (0.03 sec)
step.13.Load the mysqldump in slave server.
mysql> source /home/pankaj/mysqlbackup_dump.sql ;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
after checking gtid_executed
mysql> show global variables like 'gtid_executed';
+---------------+------------------------------------------+
| Variable_name | Value |
+---------------+------------------------------------------+
| gtid_executed | 1e9e14b8-5479-11ea-ab42-000c29ba8011:1-4 |
+---------------+------------------------------------------+
1 row in set (0.00 sec)
step.14.Execute the Change Master
Tell the slave to use the master with GTID based transactions as the replication data source, and to use GTID-based auto-positioning rather than file-based positioning.
mysql>CHANGE MASTER TO
MASTER_HOST = '54.89.xx.xx',
MASTER_PORT = 3306,
MASTER_USER = 'repl_user',
MASTER_PASSWORD = 'XXXXXXXXX',
MASTER_AUTO_POSITION = 1;
step.15.start slave server
start slave;
step.16.checking slave status
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.X.X
Master_User: repl_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 184224424
Relay_Log_File: relay-log-server.000006
Relay_Log_Pos: 182845119
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: 184224424
Relay_Log_Space: 183090775
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: ffb64763-5ed6-11ea-b7d9-525400615ff4
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: ffb64763-5ed6-11ea-b7d9-525400615ff4:3-246839
Executed_Gtid_Set: 72b3a920-5ee7-11ea-b87b-e4434b359f7a:1-1121914,
f231b58b-3370-11e9-a458-8cec4b7a7fe5:1-10029,
ffb64763-5ed6-11ea-b7d9-525400615ff4:1-246839
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
note:- you can suucessfull step master slave replication
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Thank You
