Common Replication Administration Tasks

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)

1
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:

1
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 :

1
2
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.

1
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.

1
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 :

  1. Using replication for Backups
  2. using replication with different Master and Slave Storage Engines
  3. Using replication for scale-out
  4. Replicating different databases to different slaves.
  5. Improving Replication Performance
  6. 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:

1
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO  ‘slave2’@’192.168.36.1.2’ IDENTIFIED BY ‘password';

On Server2:

1
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.
1
2
3
4
5
6
7
8
9
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:

1
2
3
4
5
6
7
8
9
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.

1
mysqldump –u user –p –all-databases > backup.sql;

Unlock tables;

On Server2:

flush tables with read lock;

1
2
3
Show master status;

Unlock tables;

And finally, run the command to make server2 as slave to server1

1
CHANGE MASTER TO MASTER_HOST='192.168.1.1', MASTER_USER='slave2',  MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin', MASTER_LOG_POS=<em>position</em>;

And then start the slave

1
Start slave;

Then check the slave status with the following command:

1
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:

1
Stop slave;

Run the following command:

1
CHANGE MASTER TO MASTER_HOST='192.168.1.2', MASTER_USER='slave1',  MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin', MASTER_LOG_POS=<em>position</em>;

Start the slave:

1
Start slave;

Check the slave status:

1
Show slave statusG;

It is important that both slave_io_running and slave_sql_running have the value yes..

MySQL Replication

Implementing Master-Slave Replication:

Master-Slave replication is mainly for scale-out solutions- spreading the load among the multiple slaves to improve the performance.

And for Analytics – while the analysis of the information can take place on the slave without affecting the performance of the master.

The target uses for Replication in MySQL include:

  1. Scale-out solutions: spreading the load among multiple slaves to improve performance. In this environment, all writes and updates must take place on the master server. Reads, however, may take place on one or more slaves
  2. Data Security: Because data is replicated to the slave, and the slave can pause the replication process, it is possible to run backup services on the slave without corrupting the corresponding master data.
  3. Analytics: while the analysis of the information can take place on the slave without affecting the performance of the master.

Replication Configuration:

Replication between servers in MySQL works through binary logging mechanism.

Each slave must connect to the master using a standard mysql username and password, so there must be a user account on master that the slave can use to connect, and the user needs only REPLICATION SLAVE privilege.

Assuming that username is slave, password is slave123 and Master host name is 192.168.36.1, Slave host name is 192.168.36.2.

On Master Server: (192.168.36.1)

1. Create a user for replication and grant REPLICATION SLAVE privilege.

GRANT REPLICATION SLAVE ON *.* TO ‘slave’@’192.168.36.1’ IDENTIFIED BY ‘slave123′;

Note: We can create a different user for each slave or we can use the same user for each slave that needs to connect and the user we want to use for replication has REPLICATION SLAVE privilege.

2. Setting the Replication Master Configuration.

Note: For replication to work you must enable binary logging on the master. If binary logging is not enabled, replication will not be possible as it is the binary log that is used to exchange data between the master and slaves.

Edit the configuration file on Master Server:  /etc/my.cnf

log-bin    = mysql-bin

Each server within a replication group must be configured with a unique server-id value.  Server-id is used to identify individual servers within the group.

And server-id must be an integer between 1 and (232)–1.

server-id = 1 # selection of these integers are entirely up to you

3. Setting the Replication Slave Configuration:

On Slave Server: (192.168.36.2)

The only option we must configure on slave is to set the unique server-id.

server-id=2

Note: If you do not specify a server-id value, it defaults to 0.  If you omit server-id (or set it explicitly to 0), a master refuses connections from all slaves, and a slave refuses to connect to a master. Thus, omitting server-id is good only for backup with a binary log.

We do not have to enable binary logging on the slave for replication to be enabled.

If we enable the binary logging on slave , we can user binary log for data backup and crash recovery on the slave. And also we can use the slave as  part of a more complex replication topology,  where the slave acts as a Master to other slaves.

log-bin= mysql-bin

4. Getting the replication master information:

  1. Start the command-line and flush all tables and block write statements by executing  the statement :

flush tables with read lock;

  1. Use the following MySQL statement to determine the current binary log file name and position.

show master status;

Ex: mysql-bin.0001 and position is 98

Note: This represents the replication coordinates at which the slave should begin processing new updates from the master.

On Master Server (192.168.36.1)

5. Take the database snapshot from the master with the tool mysqldump or whatever.

–         If you haven’t already locked the tables on the server to prevent statements that update data from executing start by executing

flush tables with read lock ;

–         In another session , use mysqldump to create a dump of all –databases or selected databases to replicate.

mysqldump –u root –p –all-databases –lock-all-tables  > db_dump.sql

Another alternative is use with the –master-data option, which automatically appends the change master to statement required on the slave to start the eplication process.

mysqldump –u root –p –all-databases  –master-data –lock-all-tables  > db_dump.sql

6. Restore the backup on slave server. ( 192.168.36.2)

mysql –u root –p < db_dump.sql    (OR)

On Master Server (192.168.36.1)

You will need either to copy the dump file to the slave, or to use the file from the master when connecting remotely to the slave to import the data.

mysql -h 192.168.36.2 < db_dump.sql

7.  After restoring the db dump start the slave :

CHANGE MASTER TO MASTER_HOST=’192.168.36.1’, MASTER_USER=‘slave’, MASTER_PASSWORD=‘slave123d’, MASTER_LOG_FILE= ‘mysql-bin.0001‘, MASTER_LOG_POS=98;

8.  After that, start the slave server.

Start slave;

9. Check the replication status with the following command

Show slave status\G

Note: To check the slave status on replication server, the user requires either the SUPER or REPLICATION CLIENT privilege.

Note: Check both the Slave_IO_Running & Slave_SQL_Running should be YES.

.