Table of Contents

• Install Slave MySQL DB and Edit Configuration File
• Copy the Data Folder
• Start Replication

 In systems with a single database server, when multiple clients send a large number of query requests simultaneously, it can result in slower processing speeds. This becomes even more critical if the system involves continuously writing data in real-time. If the system cannot write data to the database on time, it could lead to serious issues. One way to avoid this excessive load is by using the replication feature to create multiple copies of the database, distributing the load across different servers.

 In my case, since I need to write data to the database in real-time and cannot afford any failures in data recording due to external factors, I configured one database as the Master and allowed only the software that writes and updates data to access it. External clients read data from a replicated Slave database to distribute the load and solve the problem.

 

1. Install Slave MySQL DB and Edit Configuration File

Activating replication in MySQL is quite simple. First, configure the existing MySQL DB as the Master and install MySQL on another PC to set it as the Slave. Then, modify the configuration file as shown below. To edit the configuration file, stop the running MySQL server first.

File Name	PC1 (Master)	PC2 (Slave)
my.ini	server-id = 1 log-bin = mysql-bin	server-id = 2 log-bin = mysql-bin

2. Copy the Data Folder

Copy the data folder from the Master DB to the MySQL data folder on the Slave PC.

3. Start Replication

Now, start both the Master and Slave MySQL servers that were previously stopped.

Run the following query on the Master server:

SHOW MASTER STATUS

Then, execute the following query on the Slave server:

CHANGE MASTER TO
  MASTER_HOST = '172.27.208.1', -- Master server's IP
  MASTER_USER = 'root',
  MASTER_PASSWORD = 'root',
  MASTER_LOG_FILE = 'mysql-bin.000003', -- Master log file
  MASTER_LOG_POS = 98; -- Master log position

START SLAVE;

Once the query is executed, replication will begin on the Slave server. To check the replication status, run the following query:

SHOW SLAVE STATUS

If both "Slave_IO_Running" and "Slave_SQL_Running" show "Yes," replication is working correctly. If either of them shows "No," check the log files to diagnose and resolve the issue.

Now that replication is running, all transactions happening on the Master DB will also occur on the Slave DB.

If the Slave has been down for a long time and is restarted, it might not work correctly if the log files and position from before the downtime are cleared. In such cases, stop both the Master and Slave MySQL servers and follow the procedure from Step 2 to reinitialize replication.