Initial Data Migration

If the primary server already contains existing datasets prior to establishing replication, the data must be duplicated to the secondary instance to ensure baseline consistency.

Exporting from the Primary Host

Execute the following command on the primary Linux machine to generate a complete backup snapshot:

mkdir ~/backup_dir
cd ~/backup_dir
mysqldump -uadmin -padminpass --all-databases --lock-all-tables > ./primary_snapshot.sql

The --all-databases flag exports every schema, while --lock-all-tables guarantees data consistency during the read operation by preventing write operations.

Restoring on the Secondary Host

Transfer the generated primary_snapshot.sql file to the secondary Windows machine. Open a command prompt in the directory containing the file and run:

mysql -uadmin -padminpass < primary_snapshot.sql

You can verify the successful restoration by logging into the database client:

mysql -uadmin -padminpass
SHOW DATABASES;

Primary Server Setup

Modify the primary database configuration to enable binary logging and assign a unique identifier.

Open the configuration file:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Uncomment the server-id parameter and set it to 1, and uncomment the log_bin directive. Additionally, comment out the bind-address = 127.0.0.1 line to permit external connections. Save and exit the editor.

Apply the changes by restarting the service:

sudo systemctl restart mysql

Next, log into the primary database and create a dedicated user for replication:

GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'%' IDENTIFIED BY 'replica_pwd';
FLUSH PRIVILEGES;

Retrieve the current binary log coordinates by running:

SHOW MASTER STATUS;

Record the File and Position values displayed. These coordinates are essential for configuring the secondary server.

Secondary Server Setup

On the Windows secondary host, locate the MySQL configuration file (typically my.ini) and modify the server-id to a distinct integer, such as 10. Save the file.

Restart the MySQL service via the Windows services manager (services.msc) to apply the new configuration.

Establishing the Replication Link

Access the MySQL client on the secondary Windows machine:

mysql -uadmin -padminpass

If this instance was previously configured for replication, halt the existing process:

STOP SLAVE;

Configure the secondary node to point to the primary host using the coordinates recorded earlier:

CHANGE MASTER TO
  MASTER_HOST='10.0.0.5',
  MASTER_USER='replica_user',
  MASTER_PASSWORD='replica_pwd',
  MASTER_LOG_FILE='mysql-bin.000002',
  MASTER_LOG_POS=154;

Ensure the MASTER_HOST matches the primary server's IP address, and MASTER_LOG_FILE and MASTER_LOG_POS correspond exactly to the output from SHOW MASTER STATUS;.

Initiate the synchronization process:

START SLAVE;

Confirm the replication status is functioning correctly:

SHOW SLAVE STATUS \G

Replication Verification

On the primary Linux server, create a new schema:

CREATE DATABASE test_replication_db DEFAULT CHARSET=utf8;
SHOW DATABASES;

Immediately query the secondary Windows server to confirm the newly created schema has been synchronized:

SHOW DATABASES;