# MySQL Replication (M-W)

<figure><img src="https://2332860236-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2Fq6mjlFfyDOi3mV0lemKE%2Fuploads%2Fy14xGqULaWa08rphXQk1%2Fimage.png?alt=media&#x26;token=27069f98-087f-4379-8e34-ff4aa65b0d8b" alt=""><figcaption></figcaption></figure>

The key benefits of master-slave replication are:

* **Resiliency**. Slaves act as hot backups, ready to become the new master in case of a primary server failure. This setup minimizes downtime and data loss.
* **Scalability.** Read queries can be distributed across slaves, offloading the master and facilitating the handling of increased loads.
* **Disaster recovery.** Slaves safeguard data against hardware failures or catastrophic events, ensuring quick recovery.

***

MySQL master-slave replication is a process that involves copying data from a MySQL "master" server to one or more "slave" servers. This allows for data redundancy, scalability, and high availability, where the slave servers maintain a real-time copy of the master’s data.

Here's an overview of how MySQL master-slave replication works and how to set it up:

#### Steps for Setting Up MySQL Master-Slave Replication:

**1. Prepare the Master Server:**

* Ensure binary logging is enabled on the master server.
* Assign a unique server ID.
* Create a replication user with the necessary privileges.

**Configuration on the Master (`my.cnf` or `my.ini`):**

Edit the `my.cnf` or `my.ini` file on the master server:

```ini
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-do-db = your_database  # Optional, specify databases to replicate
```

* `server-id`: A unique ID for each server in the replication setup (must be different for each server).
* `log-bin`: Enables binary logging, which is essential for replication.
* `binlog-do-db`: (Optional) Specifies which databases to replicate. This is useful if you only want to replicate specific databases.

Restart MySQL:

```bash
sudo systemctl restart mysql
```

**2. Create a Replication User on the Master:**

Login to the MySQL master server:

```bash
mysql -u root -p
```

Then, create a user that the slave will use to connect to the master:

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

**3. Get the Master’s Binary Log Position:**

You need to capture the current position of the binary log to start replication from that point. Run the following command on the master server:

```sql
SHOW MASTER STATUS;
```

This will return output like this:

```
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 12345    | my_database  |                  |
+------------------+----------+--------------+------------------+
```

* `File`: The binary log file name.
* `Position`: The position in the binary log.

Take note of the `File` and `Position` values, as these will be needed on the slave server.

**4. Prepare the Slave Server:**

* Ensure that the slave server has a unique `server-id`.
* Copy the database or data from the master server to the slave.

**Configuration on the Slave (`my.cnf` or `my.ini`):**

Edit the `my.cnf` or `my.ini` file on the slave server:

```ini
[mysqld]
server-id = 2  # Unique ID different from master
```

Restart MySQL:

```bash
sudo systemctl restart mysql
```

**5. Set Up Replication on the Slave:**

Login to the MySQL slave server:

```bash
mysql -u root -p
```

Set the master connection information and start replication:

```sql
CHANGE MASTER TO
    MASTER_HOST = 'master_ip',
    MASTER_USER = 'replica_user',
    MASTER_PASSWORD = 'password',
    MASTER_LOG_FILE = 'mysql-bin.000001',  -- File from master
    MASTER_LOG_POS = 12345,  -- Position from master
    
START SLAVE;
```

* `MASTER_HOST`: IP address of the master server.
* `MASTER_USER`: The replication user created on the master.
* `MASTER_PASSWORD`: The password for the replication user.
* `MASTER_LOG_FILE`: The binary log file from the master.
* `MASTER_LOG_POS`: The binary log position from the master.

**6. Verify Replication:**

You can check the replication status on the slave server with the following command:

```sql
SHOW SLAVE STATUS\G
```