|MySQL 5.5+||How To||MySQL||Configuration|
How to configure GTID based Replication in MySQL?
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_id
The source_id identifies the originating server. Normally, the server's server_uuid is used for this purpose. The transaction_id is a sequence number determined by the order in which the transaction was committed on this server; for example, the first transaction to be committed has 1 as its transaction_id, and the tenth transaction to be committed on the same originating server is assigned a transaction_id of 10. It is not possible for a transaction to have 0 as a sequence number in a GTID.
For example, the twenty-third transaction to be committed originally on the server with the UUID 3E11FA47-71CA-11E1-9E33-C80AA9429562 has this GTID:
This format is used to represent GTIDs in the output of statements such as SHOW SLAVE STATUS as well as in the binary log. They can also be seen when viewing the log file with mysqlbinlog --base64-output=DECODE-ROWS or in the output from SHOW BINLOG EVENTS.
As written in the output of statements such as SHOW MASTER STATUS or SHOW SLAVE STATUS, a sequence of GTIDs originating from the same server may be collapsed into a single expression, as shown here.
The example just shown represents the first through fifth transactions originating on the MySQL Server whose server_uuid is 3E11FA47-71CA-11E1-9E33-C80AA9429562.
Setting Up Replication Using GTIDs:
This section describes a process for configuring and starting GTID-based replication in MySQL 5.6. This is a “cold start” procedure that assumes either that you are starting the replication master for the first time, or that it is possible to stop it; To RESET the existing MySQL Configuration on the DBs please refer this link.
The key steps in this startup process for the simplest possible GTID replication topology—consisting of one master and one slave—are as follows:
- Set config values in /etc/mysql/my.cnf on Master & Slave.
- Add configs for binary and relay logs and enable GTIDS on Master & Slave nodes.
log-bin = mysql-bin
server-id = <Server ID> (Provide the Server ID = 1.2.3.... etc)
relay-log = relay-log-slave
binlog_format = MIXED
- Restart mysql service
$ service mysqladmin restart
- Add query to listen to master giving host, log pos, etc. Use the replication user created in the MASTER to connect to the Master node. The MASTER_AUTO_POSITION will tell the slave to use GTID based replication.
Run the following query on the SLAVE:
mysql> CHANGE MASTER TO
> MASTER_HOST = 'Master IP / Hostname',
> MASTER_PORT = port,
> MASTER_USER = 'repl-user',
> MASTER_PASSWORD = 'password',
> MASTER_AUTO_POSITION = 1;
- Start slave
mysql> START SLAVE;
This replication set up will not carry over existing data from Master. You may need to use additional tools like Percona XtraBackup or mysql dump.
If you are experiencing issues with ScaleArc or with any of it's features, please contact ScaleArc Support. We are available 24x7 by phone at 855 800 7225 or +1 408 412 7315. For general support inquiries, you can also e-mail us at email@example.com.
2901 Tasman Drive Santa Clara, CA 95054 | Email: firstname.lastname@example.org