Common Replication Administration tasks :
1. Checking the Replication Status.
The most common task when managing a replication process is to ensure that replication is taking place and that there has been no errors between the slave and the master.
On Slave Server (192.168.36.2)
mysql> Show slave statusG
The key fields from the status report to examine are:
Slave_io_state: indicates the current status of the slave.
Slave_io_running: shows whether the IO thread for the reading the master’s binary log is running
Slave_sql_running: shows whether the SQL thread for the executing events in the relay log is running
Last error: shows the last error registered when processing the relay log. Ideally this should be blank, indicating no errors.
Seconds_behind_master: shows the number of seconds that the slave SQL thread is behind processing the master binary log. A high number (or an increasing one) can indicate that the slave is unable to cope with the large number of statements from the master.
On the MASTER :
On the master, you can check the status of slaves by examining the list of running processes. Slaves execute the Binlog Dump command:
mysql> show full processlist;
2. Pausing Replication on the slave.
You can start and stop the replication of the statements on the slave using the commands :
start slave ;
stop slave;
When execution is stopped, the slave does not read the binary log from the master (the IO_THREAD) and stops processing events from the relay log that have not yet been executed the (SQL_THREAD) .
You can pause either the IO or SQL threads individually by specifying the thread type.
mysql> STOP SLAVE IO_THREAD;
Stopping the IO thread will allow the statements in the relay log to be executed up until the point where the relay log has ceased to receive new events.
Using this option can be useful when you want to pause execution to allow the slave to catch up with events from the master, when you want to perform administration on the slave but also ensure you have the latest updates to a specific point.
This method can also be used to pause execution on the slave while you conduct administration on the master while ensuring that there is not a massive backlog of events to be executed when replication is started again.
mysql> STOP SLAVE SQL_THREAD;
Stopping the SQL thread can be useful if you want to perform a backup or other task on a slave that only processes events from the master. The IO thread will continue to be read from the master, but not executed, which will make it easier for the slave to catch up when you start slave operations again.
Replication Solutions are :
- Using replication for Backups
- using replication with different Master and Slave Storage Engines
- Using replication for scale-out
- Replicating different databases to different slaves.
- Improving Replication Performance
- Switching Masters during failover.
Some of the important replication slave options for future purpose:
These are not mandatory, these are optional
–log-slave-updates: Normally, a slave does not log to its own binary log any updates that are received from a master server. This option tells the slave to log the updates performed by its SQL thread to its own binary log. For this option to have any effect, the slave must also be started with the -–log-bin option to enable binary logging.
This variable is used when you want to chain replication servers. For example, you might want to set up replication servers using this arrangement:
A -> B -> C
–read-only: Cause the slave to allow no updates except from slave threads or from users having the SUPER privilege. On a slave server, this can be useful to ensure that the slave accepts updates only from its master server and not from clients. This variable does not apply to temporary tables.
Implementation of Master –Master Replication Server Setup:
The advantages of master-master replication over the traditional master-slave replication are that you don’t have to modify your applications to make write accesses only to the master, and that it is easier to provide high-availability because if the master fails, you still have the other master.
Server1.example.com with the IP address 192.168.1.1 to the server server2.example.com with the IP address 192.168.1.2 and vice versa. Each system is the slave of the other master and the master of the other slave at the same time.
Steps for setup Master-Master Replication:
1) Create a user (slave2) for replication to access the MySQL Database on Server1.
On Server1:
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ‘slave2’@’192.168.36.1.2’ IDENTIFIED BY ‘password';
On Server2:
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ‘slave1’@’192.168.36.1.1’ IDENTIFIED BY ‘password’;
2) Now we set up master-master replication in /etc/my.cnf.
The crucial configuration options for master-master replication are auto_increment_increment and auto_increment_offset for auto-increment columns:
- auto_increment_increment controls the increment between successive AUTO_INCREMENT values.
- auto_increment_offset determines the starting point for AUTO_INCREMENT column values.
vi /et/my.cnf:
[mysqld]
server-id = 1
# Assume that we have 2 nodes and offset values are even and odd
auto-increment-increment = 2
auto-increment-offset = 1
#Binary Logging Enabled
log-bin = mysql-bin
log-slave-updates
After configuring the variables in my.cnf need to restart the server.
On Server2, assuming 192.168.1.2:
Vi /etc/my.cnf:
[mysqld]
server-id = 2
# Assuming that we have 2 nodes, and offset value start from 2 i.e. even
auto-increment-increment = 2
auto-increment-offset = 2
#Binary Logging Enabled
log-bin = mysql-bin
After configuring the variables in my.cnf need to restart the server.
On Server 1:
3) Assuming that both the servers have the same database copy, otherwise take the backup and restore.
Before taking the dump flush the tables with read lock and check the bin log name and position:
flush tables with read lock;
show master status;
Take the db snapshot on server1 and then restore it on server2.
mysqldump –u user –p –all-databases > backup.sql;
Unlock tables;
On Server2:
flush tables with read lock;
Show master status;
Unlock tables;
And finally, run the command to make server2 as slave to server1
CHANGE MASTER TO MASTER_HOST='192.168.1.1', MASTER_USER='slave2', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin', MASTER_LOG_POS=position;
And then start the slave
Start slave;
Then check the slave status with the following command:
Show slave status;
It is important that both slave_io_running and slave_sql_running have the value yes.
Now the replication from server1 to server2 is set up.
Next we should configure the replication from server2 to server1.
To do this, we stop the slave on server1 and make it a slave of server2:
On Server 1:
Stop slave;
Run the following command:
CHANGE MASTER TO MASTER_HOST='192.168.1.2', MASTER_USER='slave1', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin', MASTER_LOG_POS=position;
Start the slave:
Start slave;
Check the slave status:
Show slave statusG;
It is important that both slave_io_running and slave_sql_running have the value yes..