Replication setup with GTIDs
9:14 PM
This article assumes that you have already knowledge of configuring simple master slave replication setup.
Master Server
1. Add following variables in configuration file on master
shell> vi /etc/my.cnf server_id = 1 log_bin=mysql-bin gtid_mode=on enforce_gtid_consistency=on log-slave-updates = 1
2. Create replication user on master and grant necessary privilege
mysql>create user 'repusr2'@'192.168.1.2' identified by '$0mep@ssw0rd'; mysql>grant replication slave on *.* to 'repusr2'@'192.168.1.2' identified by '$0mep@ssw0rd';
- Replace '192.168.1.2.' with your replication server IP
3. Restart service and check running status of variables.
shell>service mysql restart mysql> show variables like '%gtid%'; +---------------------------------+-----------+ | Variable_name | Value | +---------------------------------+-----------+ | binlog_gtid_simple_recovery | OFF | | enforce_gtid_consistency | ON | | gtid_executed | | | gtid_mode | ON | | gtid_next | AUTOMATIC | | gtid_owned | | | gtid_purged | | | simplified_binlog_gtid_recovery | OFF | +---------------------------------+-----------+ 8 rows in set (0.00 sec)
Slave Server
1. Add following variables in configuration file on master
shell> vi /etc/my.cnf server_id = 2 log_bin=mysql-bin gtid_mode=on enforce_gtid_consistency=on log-slave-updates = 1
2. Restart service and check running status of variables.
shell>service mysql restart mysql> show variables like '%gtid%'; +---------------------------------+-----------+ | Variable_name | Value | +---------------------------------+-----------+ | binlog_gtid_simple_recovery | OFF | | enforce_gtid_consistency | ON | | gtid_executed | | | gtid_mode | ON | | gtid_next | AUTOMATIC | | gtid_owned | | | gtid_purged | | | simplified_binlog_gtid_recovery | OFF | +---------------------------------+-----------+ 8 rows in set (0.00 sec)
3. Setup and start replication
mysql>change master to master_host='192.168.1.1', master_user='repusr2', master_password='$0mep@ssw0rd', master_port=3306, master_auto_position=1; Query OK, 0 rows affected, 2 warnings (0.02 sec) mysql> show warnings; +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1759 | Sending passwords in plain text without SSL/TLS is extremely insecure. | | Note | 1760 | Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. | +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec)
- Replace '192.168.1.1' with your master server IP
4. Check slave status
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.1
Master_User: repusr2
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 754
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 964
Relay_Master_Log_File: mysql-bin.000005
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: 754
Relay_Log_Space: 1168
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: 100
Master_UUID: 774a5cbf-e329-11e4-917a-00221955e329
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 the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 774a5cbf-e329-11e4-917a-00221955e329:1-3
Executed_Gtid_Set: 774a5cbf-e329-11e4-917a-00221955e329:1-3
Auto_Position: 1
1 row in set (0.00 sec)
FAQs
What is GTID?
>A global transaction identifier (GTID) is a unique identifier created and associated with each transaction when it is committed on the server of origin.
>The identifier is unique not only to the server on which it is originated, but is unique across all the servers in a given replication setup.
>There is a 1-to-1 mapping between all transactions and all GTIDs.What is the format of GTID?
GTID = source_id:transaction_id
Source_id identifying the originating server and the transaction_id being a sequential number of the transactions that were committed on the originating server. For example, the twenty-third (23rd) transaction to be committed originally on the server having the
UUID: 774a5cbf-e329-11e4-917a-00221955e329 GTID: 774a5cbf-e329-11e4-917a-00221955e329:23
0 comments