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,
The key steps in this startup process for the simplest possible GTID replication topology—consisting of one master and one slave:
Step 1: If replication is already running, synchronize both servers by making them read-only.
Synchronize the servers. Make the servers read-only. To do this, enable the
read_only
system variable by executing the following statement on both servers:mysql> SET @@global.read_only = ON;
Then, allow the slave to catch up with the master. It is extremely important that you make sure the slave has processed all updates before continuing.
Step 2: Stop both servers. Stop each server using mysqladmin as shown here, where
username
is the user name for a MySQL user having sufficient privileges to shut down the server:shell> mysqladmin -uusername
-p shutdown
Then supply this user's password at the prompt.
Step 3: Restart both servers with GTIDs enabled. To enable binary logging with global transaction identifiers, each server must be started with GTID mode, binary logging, slave update logging enabled, and with statements that are unsafe for GTID-based replication disabled. In addition, you should prevent unwanted or accidental updates from being performed on either server by starting both in read-only mode. This means that both servers must be started with (at least) the options shown in the following invocation of mysqld_safe:
shell> mysqld_safe --gtid_mode=ON --log-bin --log-slave-updates --enforce-gtid-consistency &
Note: On master my.cnf
We can add below parameters in my.cnf in place of above step,
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog_format = ROW
gtid_mode = on
enforce_gtid_consistency
log_slave_updates.
In addition, you should start the slave with the
--skip-slave-start
option along with the other server options specified in the example just shown.
Note: On Slave
Add the following variables to my.cnf:
[mysqld]
server_id = 2
log_bin = mysql-bin
binlog_format = ROW
skip_slave_start
gtid_mode = on
enforce_gtid_consistency
log_slave_updates
Restart MySQL to apply the configuration changes on both the server:
shell> service mysql restart
Step 4 : Create a MySQL user on Master to be used by the slave:
MySQL> GRANT REPLICATION SLAVE ON *.* TO 'slave_user_name'@'slave_ip' IDENTIFIED BY 's3cret';
Step 5: Direct the slave to use the master. Tell the slave to use the master as the replication data source, and to use GTID-based auto-positioning rather than file-based positioning. Execute a
CHANGE MASTER TO
statement on the slave, using the MASTER_AUTO_POSITION
option to tell the slave that transactions will be identified by GTIDs, and then start the slave.
You may also need to supply appropriate values for the master's host name and port number as well as the user name and password for a replication user account which can be used by the slave to connect to the master;
mysql>CHANGE MASTER TO
>MASTER_HOST = master_
>host
,MASTER_PORT = master_
>port
,MASTER_USER = slave_
>user
,MASTER_PASSWORD = slave_user_
>password
,MASTER_AUTO_POSITION = 1;
Neither the
MASTER_LOG_FILE
option nor the MASTER_LOG_POS
option may be used with MASTER_AUTO_POSITION
set equal to 1. Attempting to do so causes the CHANGE MASTER TO
statement to fail with an error.
Assuming that the
CHANGE MASTER TO
statement has succeeded, you can then start the slave, like this:mysql> START SLAVE;
Step 6: Disable read-only mode. Allow the master to begin accepting updates once again by running the following statement:
mysql> SET @@global.read_only = OFF;
GTID-based replication should now be running, and you can begin (or resume) activity on the master as before.
Most of the steps that follow require the use of the MySQL
root
account or another MySQL user account that has the SUPER
privilege. mysqladmin shutdown
requires either the SUPER
privilege or the SHUTDOWN
privilege.
Checking the replication !!
NewSlave > show slave status\G [..] Slave_IO_Running: Yes Slave_SQL_Running: Yes [...] Retrieved_Gtid_Set: c777888a-b6df-11e2-a604-080027635ef5:5 Executed_Gtid_Set: c777888a-b6df-11e2-a604-080027635ef5:1-5
No comments:
Post a Comment