# MySQL Replication (M-M)

<figure><img src="https://2332860236-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2Fq6mjlFfyDOi3mV0lemKE%2Fuploads%2FpEdVbx6xuHs8b5wV80ey%2Fimage.png?alt=media&#x26;token=a320e3cf-e6a0-42a3-81ef-750491f412e4" alt=""><figcaption></figcaption></figure>

***

#### 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`):

        ```ini
        [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`):

        ```ini
        [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:

    ```bash
    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):

       ```sql
       CREATE USER 'replica'@'%' IDENTIFIED BY 'password';
       GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
       FLUSH PRIVILEGES;
       ```
   *   On **Server 2** (run from `mysql` client):

       ```sql
       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:

       ```sql
       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:

       ```sql
       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:

       ```sql
       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**:

    ```sql
    START SLAVE;
    ```
3.  **Check the status** of replication on Server 1 to ensure it’s running:

    ```sql
    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:

       ```sql
       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**:

    ```sql
    START SLAVE;
    ```
3.  **Check the status** of replication on Server 2:

    ```sql
    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**:

    ```sql
    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:

        ```sql
        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:

    ```sql
    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.

***
