> For the complete documentation index, see [llms.txt](https://ghoulsec.gitbook.io/ghoulsec-vault/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://ghoulsec.gitbook.io/ghoulsec-vault/security-operations/zabbix-nms/zabbix-database-management/mysql-replication-m-m.md).

# MySQL Replication (M-M)

<figure><img src="/files/tMy4bwUWjm7sXLHTmxDX" 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.

***