# 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`):
```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.
***
---
# Agent Instructions: Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter:
```
GET https://ghoulsec.gitbook.io/ghoulsec-vault/security-operations/zabbix-nms/zabbix-database-management/mysql-replication-m-m.md?ask=
```
The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.