MySQL Replication

Introduction to MySQL Replication

The following article provides an outline for MySQL Replication. For maintaining high availability of database and load balancing when a large number of users are accessing the MySQL database, various strategies need to be implemented and multiple database servers can be used that work together with the server and the users. In this case, it is very easy to provide this facility if all the operations are read-only which means no operation is performed on the data present in the database servers only the available data is retrieved.

But in the case of reading or write servers that involve all kinds of manipulations on the data of the database becomes difficult. This is because a single change in one database should be reflected in all the database servers that are serving the users. This is called replication of the data. Where a single change in the database server needs to be done in all the database servers with which it is working to provide high availability to the end-users.

Working of MySQL Replication

Given below is the working of MySQL Replication:

• In MySQL, there is usually one database server on which all the modification relate operations and requests are served and those changes are in turn applied to other database servers called the slave servers or standby database servers.
• That means the master nodes are always the senders of the replicated data while slave nodes receive the replicated data and apply the same changes over there.

Setup of Replication

It is required that the two instances of MySQL are running to demonstrate the master-slave configuration of replication. However, you can have any number of slaves where the replication should be maintained. For the current demonstration, we will consider two nodes over the private network. One of which will be master and the other will be a slave.

• master IP: 172.16.2.4
• slave IP: 172.16.2.15

Firstly, we will set up the master. For this, you will have to follow the following steps:

Given below are the steps:

1. Set the configuration file

The first thing to do will be to add the following lines in your configuration file mysqld.cnf. The bind-address statement will make sure that your master is listening at that address the server-id will be set to 1. This id needs to remain unique within the network of master-slaves in which your replication will work. This is used to identify the node. Further, the log_bin is specified what will specify the path where the logs will be stored.

• bind-address: 172.16.2.4
• server-id: 1
• log_bin: /var/log/mysql/mysql-bin.log

We have changed configurations in the configuration file located in /etc/mysql/mysql.conf.d/mysqld.cnf folder.

2. Reflect changes made in configuration file, restart MySQL service

Now, to make sure that all the changes made in the configuration file are applied properly while setup, we will need to restart the MySQL server using the following statement.

Code:

sudo systemctl restart mysql

Output:

3. Create a user which can have replication privileges

We will create a new user named newUserForRep that will be used for communication between slaves and masters. For that, the created user must have the privilege of replication on slave.

Code:

sudo mysql -u root -p

Output:

Code:

CREATE USER 'newUserForRep'@'172.16.2.15' IDENTIFIED BY 'passwordForSlave';

Output:

Code:

GRANT REPLICATION SLAVE ON *.* TO 'newUserForRep'@'172.16.2.15';

Output:

4. Lock the master database

Now, we will have to lock the database for write operations so that only read operations will be performed. This is necessary to lock master data to get the snapshot of slave and is required until be dump the database that is our further step. To lock masters we will use the following command.

Code:

FLUSH TABLES WITH READ LOCK;

Output:

5. Store log position of master replication for further reference

Now, we will need to note down the location of the logs where they will be stored. This will be used further while setting up the slave node.

Code:

SHOW MASTER STATUS;

6. Dumping of the master database

Now, we will dump our master database using the following command.

Code:

sudo mysqldump -u root -p --all-databases --master-data > dbdump.sql

Output:

7. Unlock the master database

As we have finished with dumping our master database, now we can unlock the tables using the following command.

Code:

UNLOCK TABLES;

Output:

8. Copy dumped file to slave node

Now, we will copy the master data that we had dumped earlier to the slave node by copying the dumped file to the slave node.

For example, using scp and following command, we can copy master data to the slave.

Code:

scp dbdump.sql 172.16.2.15:/tmp

Output:

Configuration of the Slave

Given below are the configuration of the slave:

1. Set the configuration file

Similar to the master process, we will make changes to the configuration file for slave node setup by making the following changes.

• bind-address: 172.16.2.15
• server-id: 2
• log_bin: /var/log/mysql/mysql-bin.log

2. Reflect the changes made in configuration file, restart MySQL service

Next step is the same for the slave to restart MySQL service using the following command.

Code:

sudo systemctl restart mysql

Output:

3. Import the dumped file for the database

Next step is to import the master data dumped at the master node to the slave node by using the following command.

Code:

mysql -u root -p < /tmp/dbdump.sql

4. Setup the slave node to communicate with master

Now, we will make sure that slave and master are communicating properly by stopping it and setting the master to our master by mentioning the things we had noted earlier and using the user we had created for replication and restarting the slave node.

Code:

sudo mysql -u root -p

Output:

Code:

STOP SLAVE;
CHANGE MASTER TO
MASTER_HOST='172.16.2.4',
MASTER_USER='newUserForRep',
MASTER_PASSWORD='passwordForSlave',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=613;
START SLAVE;

5. Check the working of replication

This is it. You have completed your setup for replication. Now, all you have to do is to check whether replication is working fine by creating a new table or inserting the values in the table on the master node and then check on the slave node whether the updated data is written over there too by retrieving the records and viewing the schema.

Conclusion – MySQL Replication

We can provide high availability and balance the load on the database server by maintaining multiple database servers that can work together and implement replication to serve a large number of users without affecting the performance much in the MySQL database. However, you need to be careful while performing replication and make sure that all the settings are set correctly for the respective database servers depending on the role which they are going to perform in replication and request handling.

Recommended Articles

This is a guide to MySQL Replication. Here we discuss the working of MySQL replication, setup of replication and configuration of the slave. You may also have a look at the following articles to learn more –

1. MySQL Database Repair
2. MySQL Timestamp
3. MySQL Outer Join
4. ORDER BY in MySQL