Creating MySQL replicas for scaling and high availability

When your application is small, you can use a single MySQL server for all your database needs. As your application becomes popular and you get more and more requests, the database starts becoming a bottleneck for application performance. With thousands of queries per second, the database write queue gets longer and read latency increases. To solve this problem, you can use multiple replicas of the same database and separate read and write queries between them.

In this recipe, we will learn how to set up replication with the MySQL server.

Getting ready

You will need two MySQL servers and access to administrative accounts on both.

Make sure that port 3306 is open and available on both servers.

How to do it…

Follow these steps to create MySQL replicas:

1. Create the replication user on the Master server:

   $ mysql -u root -p
   mysql> grant replication slave on *.* TO ‘slave_user’@’10.0.2.62’ identified by ‘password’;
   mysql> flush privileges...