MySQL Replication (M-M)

---

Prerequisites:

1. Two MySQL Servers — You need two MySQL servers, each with a unique hostname (e.g., server1.example.com and server2.example.com).

2. Network Access — Ensure that both MySQL servers can communicate with each other over the network (typically port 3306).

3. Admin Access — You need to have root access to both servers to configure replication and make the necessary changes.

---

Step 1: Initial MySQL Configuration

1. Configure MySQL on both servers to enable binary logging and unique server IDs.

   Edit the my.cnf file on both servers (/etc/my.cnf or /etc/mysql/my.cnf depending on your system):

   • On Server 1 (e.g., server1.example.com):

     [mysqld]
     server-id = 1  # Unique server ID
     log-bin = mysql-bin  # Enable binary logging for replication
     binlog-do-db = your_database_name  # Optional: replicate only specific DBs

   • On Server 2 (e.g., server2.example.com):

     [mysqld]
     server-id = 2  # Unique server ID (must differ from Server 1)
     log-bin = mysql-bin  # Enable binary logging for replication
     binlog-do-db = your_database_name  # Optional: replicate only specific DBs

   After making these changes, restart MySQL on both servers:

   sudo systemctl restart mysql

---

Step 2: Create Replication User

1. Create a replication user on both servers. This user will have the necessary privileges to replicate data between the servers.

   • On Server 1 (run from mysql client):

     CREATE USER 'replica'@'%' IDENTIFIED BY 'password';
     GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
     FLUSH PRIVILEGES;

   • On Server 2 (run from mysql client):

     CREATE USER 'replica'@'%' IDENTIFIED BY 'password';
     GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
     FLUSH PRIVILEGES;

---

Step 3: Get the Binary Log Coordinates

1. Find the current binary log position on each server.

   • On Server 1, execute:

     SHOW MASTER STATUS;

     This will show something like this:

     +------------------+--------------+------------------+-----------+
     | File             | Position     | Binlog_Do_DB     | Binlog_Ignore_DB |
     +------------------+--------------+------------------+-----------+
     | mysql-bin.000001 | 107          | your_database_name |            |
     +------------------+--------------+------------------+-----------+

     Note the File and Position values. In this example, it’s mysql-bin.000001 and 107.

   • On Server 2, execute the same command to get the binary log status:

     SHOW MASTER STATUS;

     Again, note the File and Position.

---

Step 4: Set Up Replication on Server 1

1. Configure Server 1 to replicate from Server 2.

   • On Server 1, connect to the MySQL client and run:

     CHANGE MASTER TO
         MASTER_HOST='server2.example.com',
         MASTER_USER='replica',
         MASTER_PASSWORD='password',
         MASTER_LOG_FILE='mysql-bin.000001',  # Use the File from Server 2
         MASTER_LOG_POS=107;  # Use the Position from Server 2

2. Start the replication on Server 1:

   START SLAVE;

3. Check the status of replication on Server 1 to ensure it’s running:

   SHOW SLAVE STATUS\G

   • Look for Slave_IO_Running: Yes and Slave_SQL_Running: Yes in the output. If both are Yes, replication is working.

---

Step 5: Set Up Replication on Server 2

1. Configure Server 2 to replicate from Server 1.

   • On Server 2, connect to the MySQL client and run:

     CHANGE MASTER TO
         MASTER_HOST='server1.example.com',
         MASTER_USER='replica',
         MASTER_PASSWORD='password',
         MASTER_LOG_FILE='mysql-bin.000001',  # Use the File from Server 1
         MASTER_LOG_POS=107;  # Use the Position from Server 1

2. Start the replication on Server 2:

   START SLAVE;

3. Check the status of replication on Server 2:

   SHOW SLAVE STATUS\G

   • Look for Slave_IO_Running: Yes and Slave_SQL_Running: Yes in the output. If both are Yes, replication is working.

---

Step 6: Verify and Test the Setup

1. Test replication by inserting data into Server 1:

   USE your_database_name;
   INSERT INTO your_table (column1, column2) VALUES ('value1', 'value2');

2. Check if the data appears on Server 2:

   • On Server 2, run:

     SELECT * FROM your_table WHERE column1 = 'value1';

   You should see the data inserted on Server 1 appearing on Server 2.

3. Repeat the same on Server 2, inserting data into Server 2, and check if it appears on Server 1.

---

Step 7: Monitor Replication

1. Regularly monitor the replication status using:

   SHOW SLAVE STATUS\G

   • Ensure that both Slave_IO_Running and Slave_SQL_Running are Yes.

2. Check for any replication errors. If you encounter errors, resolve them before proceeding to prevent data inconsistencies.

---