Network Connectivity Requirements

Ensure bidirectional network commuincation between cluster nodes using ping utility.

C:\Users\administrator>ping 192.168.100.91

Pinging 192.168.100.91 with 32 bytes of data:
Reply from 192.168.100.91: bytes=32 time=2ms TTL=64
Reply from 192.168.100.91: bytes=32 time=1ms TTL=64
Reply from 192.168.100.91: bytes=32 time=2ms TTL=64
Reply from 192.168.100.91: bytes=32 time=1ms TTL=64

Ping statistics for 192.168.100.91:
    Packets: Sent = 4, Received = 4, Lost = 0 (0% loss)

Database Backup Creation

Generate a complete database dump for initial synchronization:

liyafei OEM:~$ mysqldump -hlocalhost -uroot -p1367356 test > test_backup.sql

Master-Master Configuration Setup

Primary Node Configuration

Operating System: Ubuntu 14.04 IP Address: 192.168.100.91

Modify /etc/mysql/my.cnf under [mysqld] section:

log-bin=mysql-bin
server-id=1

Secondary Node Configuration

Operating System: Windows 10 IP Address: 192.168.100.31

Update my.ini configuration file:

log-bin=mysql-bin
server-id=160

Replicasion User Setup

Create dedicated replication account on primary node:

mysql> CREATE USER 'replica_user'@'192.168.100.31' IDENTIFIED BY 'replica_password';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'192.168.100.31';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000006 |      592 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Slave Configuration

Configure secondary node to connect to primary master:

mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.100.91',
    -> MASTER_USER='replica_user',
    -> MASTER_PASSWORD='replica_password',
    -> MASTER_LOG_FILE='mysql-bin.000006',
    -> MASTER_LOG_POS=592;
Query OK, 0 rows affected (0.06 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.100.91
                  Master_User: replica_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 592
               Relay_Log_File: LAPTOP-HBRSJQ2D-relay-bin.000002
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000006
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
          Exec_Master_Log_Pos: 592
              Relay_Log_Space: 419
       Seconds_Behind_Master: 0

Verification Process

Create test database on primary node:

mysql> CREATE DATABASE cluster_test;
Query OK, 1 row affected (0.02 sec)

mysql> USE cluster_test;
Database changed

Confirm automatic synchronizatoin on secondary node:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cluster_test       |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

Firewall Configuration

Enable MySQL port 3306 in Windows firewall settings to allow remote connections.