Replicating mysql servers

Introduction

MySQL replication is the process of automatically coping data from one server (the master) to other server nodes (the slaves). So this allows you to easily maintain multiple copies of MySQL databases in any number of servers called slave devices. Replication in default is asynchronous. So it is not required to establish a permanent connectivity between master and it’s slaves. We can select and configure the databases or tables, that need to be replicated to the slave.

So this will be very useful when dealing with large chunks of sensitive data.

Why replication ?

1. Backup your data -Since master node periodically writes it’s data to it’s slave nodes which are generally a separate set of servers, your data will be stored in multiple server instances.

2. Data security -We can control the data replication process to slaves. So it is possible to run backup services on the slave without corrupting the master node data. If the data stored on master node get lost in an event of failure, still you can recover the data from corresponding slave nodes.

3. Flexibility -You can easily control the replicating databases or tables through a simple configuration change depending on your requirement.

4. Analytics -Live data will be continuously stored on the master, while the analysis of the information can take place on the slave without affecting the performance of the master.

5. Improved performance -You can configure all the data writes and updates to be take place on the master server while data read operations take place on one or more slaves. So this model can improve the performance of writes, while dramatically increasing read speed across an increasing number of slaves.

6. Automatic fail-over -Slave nodes in the replicated cluster can be configured to automatically handle the live system traffic in a fail-over situation. You can use a network routing service on mysql servers such as Keepalived, heartbeat, pacemaker etc to route the traffic automatically to one of the slave nodes (or another master node).

Replication methods

* M-M (Master to Master replication)

Any modification to data on one node, will be replicated on the other node and vice versa. Replication is bi-directional.

* M-S (Master to slave replication)

Any modification to data on master node will be replicated to slave node. But slave node modification will not be applied to master node. Replication is uni-directional.

How to replicate ?

Let’s get two mysql installed servers as 10.10.10.1 and 10.10.10.2 for the master and the slave.

In the master node(10.10.10.1), add the following lines to my.cnf configuration file.

[mysqld]
server-id    = 1
log_bin      = /var/log/mysql/mysql-bin.log
binlog_do_db = testdatabase

And comment the following line,

# bind-address = 127.0.0.1

Here I have configured only one database for replication. You can define any number of databases to be replicated in the above my.cnf file as shown below.

binlog-do-db = testdatabase2
binlog-do-db = testdatabase3
binlog-do-db = testdatabase4
binlog-do-db = testdatabase5

Now restart the service,

> sudo service mysqld restart

Then log into the server as root,

> mysql -u root -p

Now you need to run few commands to create a pseudo-user for replicating data between our two VPS. Lets take the user as “replicator” and grant access to the master node from the slave node.

mysql > create user ‘replicator’@’10.10.10.2' identified by “XXXXXX”;

Now let’s grant permission to replicate data to the slave node.

mysql > grant replication slave on *.* to ‘replicator’@’10.10.10.2';

Then execute the following command and copy the values of the parameters MASTER_LOG_FILE and MASTER_LOG_POS. These values will be used to configure the slave on the other server.

mysql > show master status\G

Now we have created a replication user and grated access for it to copy all the data in predefined databases from master to slave. So let’s configure the other server to receive the data and replicate them on slave.

Add the following lines to the my.cnf of the slave node (10.10.10.2),

[mysqld]
server-id    = 2
log_bin      = /var/log/mysql/mysql-bin.log
binlog_do_db = testdatabase

And comment the following line,

# bind-address = 127.0.0.1

Include all the databases defined with binlog_do_db in the master node in this configuration file as well.

Then restart the service and log into the server as root,

> sudo service mysqld restart
> mysql -u root -p

Now let’s configure the slave user. Use the values obtained from master node to the variables MASTER_LOG_FILE and MASTER_LOG_POS in the below command.

mysql > stop slave; 
mysql > CHANGE MASTER TO MASTER_HOST = ‘10.10.10.1’, MASTER_USER = ‘replicator’, MASTER_PASSWORD = ‘XXXXXX’, MASTER_LOG_FILE = ‘mysql-bin.00000X’, MASTER_LOG_POS = XXX;

Then start the slave using,

mysql > start slave;

Now check the slave status,

mysql > show slave status\G

If you have done all the configuration properly, then it should give an output similar to the below without any errors,

“show slave status\G” Sample output

If you need a master — master replication setup, follow the below instructions for further configurations.

Now the second server (slave node) should also be able to behave similar to a master server. So you will need another pseudo-user for replicating data from second server to first server. When replicating data from second server (10.10.10.2) to first server (10.10.10.1), second server will be acting as the master node while the first server will be acting as the slave node. So create a new user on server 2 as “replicator” and grant access to the new master from the new slave node.

mysql > create user ‘replicator’@’10.10.10.1' identified by “XXXXX”;

And grant the permission to replicate data from server 2 to server 1.

mysql > grant replication slave on *.* to ‘replicator’@’10.10.10.1';

Now execute the below command and copy the values of the parameters MASTER_LOG_FILE and MASTER_LOG_POS on this second master node.

mysql > show master status\G

Now again log in to the first server (10.10.10.1) as the root and configure the replicate user on first server. Use the values obtained from new master node(10.10.10.2) to the variables MASTER_LOG_FILE and MASTER_LOG_POS in the below command.

mysql > stop slave; 
mysql > CHANGE MASTER TO MASTER_HOST = ‘10.10.10.2’, MASTER_USER = ‘replicator’, MASTER_PASSWORD = ‘XXXXX’, MASTER_LOG_FILE = ‘mysql-bin.00000X’, MASTER_LOG_POS = XXX;

Then start the slave and check the slave status,

mysql > start slave; 
mysql > show slave status\G

Now you have configured a master — master mysql cluster successfully.

You can install keepalived service in this clustered environment by following the next article.