MySQL Transaction Replication on Windows Server | Step-by-Step Guide
#mysql#replication#windows#database
Chitt Ranjan Mahto (Chirag)

Chitt Ranjan Mahto (Chirag) @chittrmahto

About: Database Administrator at Data Centre | PostgreSQL | SQL Server | MySQL | Oracle | MongoDB | Cluster | AlwaysOn | HA | Replication | Mirroring | Log Shipping | Master-Slave | Migration | Tuning | PITR

Joined:
Dec 21, 2024

MySQL Transaction Replication on Windows Server | Step-by-Step Guide

Publish Date: Jul 7
0 0

inchirags@gmail.com Chirag's MySQL Tutorial https://www.chirags.in

MySQL Transaction Replication on Windows Server | Step-by-Step Guide
Enter fullscreen mode Exit fullscreen mode

Configuring MySQL Transaction Replication on Windows Server

This guide outlines the process to download, install, configure MySQL Community Edition, set up Master-Slave replication, and configure the Windows Firewall on two servers

(Master: 192.168.224.131, Slave: 192.168.224.132).

Step 1: Download MySQL Community Edition

Visit the MySQL Website:

Go to downloads.mysql.com on both servers.

Navigate to the MySQL Community Edition section.

Download the Installer:

https://dev.mysql.com/downloads/windows/installer/8.0.html
Enter fullscreen mode Exit fullscreen mode

Select the MySQL Installer for Windows (e.g., mysql-installer-community-8.0.42.0.msi).

Download the installer on both Server1 and Server2.

Step 2: Install MySQL Community Edition

Run the Installer on Both Servers:

Double-click the downloaded .msi file.

Choose Custom Setup to select MySQL Server and MySQL Workbench.

Click Next and proceed with the installation.

Configure MySQL Server:

During installation, select Standalone MySQL Server/Classic MySQL Replication.

Set the root password to admin@123 when prompted.

Choose Development Machine or Server configuration type based on your needs.

Enable Show Advanced and Logging Options to configure binary logging later.

Complete Installation:

Finish the installation process on both servers.

Verify MySQL is running by checking the Windows Services panel (mysqld service).

Step 3: Configure MySQL on Master (192.168.224.131)

Locate the Configuration File:

Open the MySQL configuration file (my.ini), typically located at

C:\ProgramData\MySQL\MySQL Server 8.0\my.ini
Enable Binary Logging:

Under the [mysqld] section, add or modify:

server-id=1
log_bin=mysql-bin
binlog_format=ROW
Enter fullscreen mode Exit fullscreen mode

The server-id must be unique (use 1 for Master).

binlog_format=ROW ensures compatibility for transaction replication.

Restart MySQL Service:

Open Command Prompt as Administrator.

Run:

net stop MySQL80
net start MySQL80
Enter fullscreen mode Exit fullscreen mode

Create a Replication User:

Open MySQL Workbench or Command Prompt and connect to MySQL:

mysql -u root -p
Enter fullscreen mode Exit fullscreen mode

Enter password: admin@123.

Execute:

-- Create the replication user for the correct IP (slave server)

CREATE USER 'replication_user'@'192.168.224.132' IDENTIFIED WITH mysql_native_password BY 'admin@123';
Enter fullscreen mode Exit fullscreen mode

-- Grant REPLICATION SLAVE privilege to that specific IP

GRANT REPLICATION SLAVE ON . TO 'replication_user'@'192.168.224.132';
Enter fullscreen mode Exit fullscreen mode

-- Apply the changes

FLUSH PRIVILEGES;
Enter fullscreen mode Exit fullscreen mode

This creates a user for replication with the necessary permissions.

Get Master Binary Log Coordinates:

In MySQL, run:

mysql>

SHOW MASTER STATUS;
Enter fullscreen mode Exit fullscreen mode

+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 879 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
Note the File (e.g., mysql-bin.000001) and Position (e.g., 879). These will be used on the Slave.

Step 4: Configure MySQL on Slave (192.168.224.132)

Locate the Configuration File:

Open my.ini at

C:\ProgramData\MySQL\MySQL Server 8.0\my.ini
Enter fullscreen mode Exit fullscreen mode

Set Unique Server ID:

Under the [mysqld] section, add:

server-id=2
log_bin=mysql-bin
read_only=ON
Enter fullscreen mode Exit fullscreen mode

Use server-id=2 to differentiate from the Master.

read_only=ON ensures the Slave is not modified directly.

Restart MySQL Service:

Run:

net stop MySQL80
net start MySQL80
Enter fullscreen mode Exit fullscreen mode

Configure Slave to Connect to Master:

Connect to MySQL on the Slave:

mysql -u root -p
Enter fullscreen mode Exit fullscreen mode

Enter password: admin@123

Execute:

CHANGE MASTER TO
    MASTER_HOST='192.168.224.131',
    MASTER_USER='replication_user',
    MASTER_PASSWORD='admin@123',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=879;
Enter fullscreen mode Exit fullscreen mode

Replace MASTER_LOG_FILE and MASTER_LOG_POS with the values from the Master’s SHOW MASTER STATUS.

Start Slave:

Run:

START SLAVE;
Enter fullscreen mode Exit fullscreen mode

Verify replication status:

SHOW SLAVE STATUS\G
Enter fullscreen mode Exit fullscreen mode

Check Slave_IO_Running and Slave_SQL_Running are both Yes. If not, check Last_IO_Error or Last_SQL_Error for troubleshooting.

Step 5: Configure Windows Firewall on Both Servers

Open Windows Firewall Settings:

On both servers, open Control Panel > System and Security > Windows Defender Firewall > Advanced Settings.

Create Inbound Rule for MySQL Port:

Click Inbound Rules > New Rule.

Select Port, then TCP, and specify port 3306.

Allow the connection.

Apply to all profiles (Domain, Private, Public).

Name the rule (e.g., “MySQL_3306”).

Allow Specific IP Communication:

In the rule’s properties, go to the Scope tab.

Under Remote IP Address, add:

On Master (192.168.224.131): Allow 192.168.224.132.

On Slave (192.168.224.132): Allow 192.168.224.131.

This restricts MySQL communication to only these servers.

Apply and Test:

Apply the firewall rules.

Test connectivity from the Slave to the Master:

mysql -h 192.168.224.131 -u replication_user -p

Enter password: admin@123. If successful, the connection is established.

Step 6: Test Replication

Create a Test Database on Master:

On the Master (192.168.224.131), connect to MySQL:

mysql -u root -p
Enter fullscreen mode Exit fullscreen mode

Enter password: admin@123.

Create a database and table:

CREATE DATABASE test_db;
USE test_db;
CREATE TABLE test_table (id INT AUTO_INCREMENT PRIMARY KEY, data VARCHAR(50));
INSERT INTO test_table (data) VALUES ('Test Data');
Enter fullscreen mode Exit fullscreen mode

Verify on Slave:

On the Slave (192.168.224.132), connect to MySQL:

mysql -u root -p
Enter fullscreen mode Exit fullscreen mode

Enter password: admin@123.

Check if the database and data are replicated:

USE test_db;
SELECT * FROM test_table;
Enter fullscreen mode Exit fullscreen mode

You should see the Test Data row.

Step 7: Troubleshooting Tips

Check Logs: If replication fails, check the MySQL error log (C:\ProgramData\MySQL\MySQL Server 8.0\Data<hostname>.err).

Firewall Issues: Ensure port 3306 is open and the IPs are correctly configured.

Network Issues: Verify connectivity using ping 192.168.224.131 from the Slave and vice versa.

Replication Errors: Use SHOW SLAVE STATUS\G on the Slave to identify errors. Common issues include incorrect log file/position or authentication failures.

For any doubts and query, please write on YouTube video 📽️ comments section.

Note : Flow the Process shown in video 📽️.

😉Please Subscribe for more videos:

https://www.youtube.com/@chiragstutorial

💛Don't forget to, 💘Follow, 💝Like, Share 💙&, Comment

Thanks & Regards,

Chitt Ranjan Mahto "Chirag"

https://www.chirags.in

Note: All scripts used in this demo will be available in our website.

Link will be available in description.

Comments 0 total

    Add comment