Featured image of post Create a MySQL slave replica in 4 short steps

Create a MySQL slave replica in 4 short steps

Scale your database using master-slave replication


In this article, we will create a MySQL slave replica. A MySQL slave is a read-only copy of the master database. Using MySQL replication, the slave database is kept in sync with the master database.

The steps we will follow are:

  1. Spin up MySQL master and slave databases
  2. Create a user for replication
  3. Obtain master binary log coordinates
  4. Configure slave and start replication

What is database replication?

Database replication is a process that allows data from one database server (the master) to be copied to one or more database servers (the slaves or replicas). Replication is asynchronous, meaning that the slave does not need to be connected to the master constantly. The replica can catch up with the master when it is available.

Database replicas are used for:

  • Scaling read operations
  • High availability
  • Disaster recovery

MySQL implements replication using the binary log. The master server writes changes to the binary log, and the slave server reads the binary log and applies the changes to its database.

Create MySQL master and slave databases

We will use Docker to create the MySQL master and slave databases. We will use the official MySQL Docker image. The master database will run on port 3308, and the slave database will run on port 3309.

We run docker compose up using the following docker-compose.yml file:

Create a DB user for replication

Replication in MySQL requires a user with the REPLICATION SLAVE privilege. We will create a user named replicator with the password rotacilper.

Connect to the master database using the MySQL client:

mysql --host --port 3308 -uroot -ptoor

Create the replicator user and grant the REPLICATION SLAVE privilege:

CREATE USER 'replicator'@'%' IDENTIFIED BY 'rotacilper';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';

Retrieve master binary log coordinates

For the slave to start replication, it needs to know the master’s binary log file and position. We can obtain this information using the MySQL client which we opened in the previous step.


The output will look like this:

| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
| bin.000003 |      861 |              |                  |                   |
1 row in set (0.01 sec)

We must remember the File and Position values for the next step.

Configure slave and start replication

Now, we will connect to the slave database and configure it to replicate from the master database.

mysql --host --port 3309 -uroot -ptoor

Use the CHANGE MASTER TO command to configure the slave to replicate from the master. Replace MASTER_LOG_FILE and MASTER_LOG_POS with the values obtained in the previous step.


MASTER_HOST is the hostname of the master, which matches the docker service name. The GET_MASTER_PUBLIC_KEY option is needed for MySQL 8.0 caching_sha2_password authentication.

Finally, start the slave:


The slave will now start replicating data from the master database. You can check the replication status using the SHOW REPLICA STATUS\G command.

We can create a table with data on the master database and check if it is replicated to the slave database:

USE test;
INSERT INTO users VALUES (1, 'Alice');

Further reading on database scaling

Follow along with the MySQL master-slave replication on video

Note: If you want to comment on this article, please do so on the YouTube video.