Database Scaling with master-slave Replications

Replication involves transferring and copying information from one database server to another. In general, this is the best way to ensure that data is up-to-date and consistent across systems and locations. Here are a few reasons why you should use replication:

• Performance and scalability: simultaneous read and write operations can cause the server to be unable to cope with the load. In this case, replication reads data and executes complex queries to reduce the load on the master server.
• Data backup is used to protect against complete or partial data loss.
• Availability means that if one server goes down, access to resources will be provided by other servers.
• Geographical distribution, which is quick access in any corner of the world.

There are two types of replication: Master-Slave and Multi-Master.

Master-Slave is one of the most common approaches to database scaling and methods, consisting of a master node (Master) and nodes that copy data from it  (Slave). A special feature is that Slave can only read data. The Master is responsible for writing and reading.

This method is advantageous as it creates a copy of the data, making the loading process faster. For example, when it comes to writing, Master node can be responsible for database while Slave node performs reads and complex queries instead of central node, leading to speeding up client-side data retrieval process.

One will understand how to configure MySQL Master-Slave replication using Docker.

In the Docker-compose file, two containers are created: master-db which is the main database and slave-replication working as slave-db.

Each database should have its unique identifier with the help of “–server-id” parameter. As for the master database, it has been given 1. On the other hand, replication would take 2.

version: ‘3.1’ services:   master-db:     image: mysql:5.7     container_name: master-db     volumes:       – master_db_data:/var/lib/mysql     restart: always     environment:       MYSQL_DATABASE: myDb       MYSQL_ROOT_PASSWORD: 12345       MYSQL_USER: dev_user       MYSQL_PASSWORD: 12345     ports:       – 3306:3306     command: mysqld –server-id=1 –log-bin=mysql-bin    –binlog-format=ROW    networks:       – mysql_network   slave-db:     image: mysql:5.7     container_name: slave-db     volumes:       – slave_db_data:/var/lib/mysql     restart: always     environment:       MYSQL_ROOT_PASSWORD: 12345       MYSQL_USER: dev_user       MYSQL_PASSWORD: 12345     ports:       – 3307:3306     command: mysqld –server-id=2 –relay-log=relay-bin –log-bin=mysql-bin –binlog-format=ROW     networks:       – mysql_network volumes:   master_db_data:   slave_db_data: networks:   mysql_network:

After that, we will log in to the MySQL Shell within the master-db container and create a user that the slave replication will use to connect to the main database:

CREATE USER ‘slave_user‘@‘%’ IDENTIFIED BY ‘password’; GRANT REPLICATION SLAVE ON *.* TO ‘slave_user’@‘%’; FLUSH PRIVILEGES;

Run this command

SHOW MASTER STATUS;

Copy the values of File and Position

Afterward, make sure you have logged into the MySQL Shell in the slave-db container, then perform settings for connecting to a central database.

CHANGE MASTER TO   MASTER_HOST=‘master-db’,   MASTER_USER=‘slave_user’,   MASTER_PASSWORD=‘password’,   MASTER_LOG_FILE=‘mysql-bin.000001’, — Use the ‘File’ value from the master status   MASTER_LOG_POS=123456;              — Use the ‘Position’ value from the master status START SLAVE;

Then we run a command to verify whether or not our slave database is working and if Slave_IO_Running and Slave_SQL_Running show Yes, then it means it has successfully started.

We appreciate Olexandr taking the time to share about database scaling and replication. Our team works hard to make every project a success. To find out more about how our team could help your team, schedule a free assessment.