Configure MySQL Master-Slave Replication

This guide provides a step-by-step walkthrough for configuring basic asynchronous master-slave (source-replica) replication in MySQL. You will learn how to configure the configuration files on both servers, create a dedicated replication user, synchronize the initial data, and initiate the replication process to ensure real-time data copying from the master database to the slave.

Step 1: Configure the Master Server

To act as a replication source, the master server must have a unique server ID and binary logging enabled.

Open the MySQL configuration file (usually /etc/mysql/mysql.conf.d/mysqld.cnf or /etc/my.cnf) on the master server:
```
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
```

Add or uncomment the following lines in the [mysqld] section:

server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = mydb  # Replace with the database you want to replicate (optional)

Save the file and restart the MySQL service to apply changes:
```
sudo systemctl restart mysql
```

Step 2: Create a Replication User on the Master

The slave server needs dedicated credentials to connect to the master and read the binary logs.

Create a new user and grant replication privileges. Replace replica_user and password with your desired credentials, and slave_ip with the actual IP address of your slave server:

CREATE USER 'replica_user'@'slave_ip' IDENTIFIED WITH mysql_native_password BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'slave_ip';
FLUSH PRIVILEGES;

Keep the terminal open and obtain the current binary log coordinates by running:
```
SHOW MASTER STATUS;
```
Note down the values in the File (e.g., mysql-bin.000001) and Position (e.g., 154) columns. You will need these coordinates to configure the slave.

Step 3: Configure the Slave Server

The slave server requires a unique server ID to distinguish it from the master.

Open the MySQL configuration file on the slave server:
```
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
```

Add the following lines under the [mysqld] section:

server-id = 2
relay_log = /var/log/mysql/mysql-relay-bin.log

Save the file and restart the MySQL service:
```
sudo systemctl restart mysql
```

Step 4: Import Existing Data (If Applicable)

If your master database already contains data, export it from the master and import it into the slave before starting replication to ensure both databases start with identical datasets.

On the master server, dump the database:

mysqldump -u root -p --opt --skip-lock-tables --single-transaction --databases mydb > mydb.sql

Transfer the dump file to the slave server and import it:
```
mysql -u root -p < mydb.sql
```

Step 5: Start Replication on the Slave

Link the slave server to the master using the credentials and log coordinates obtained in Step 2.

Configure the connection parameters (for MySQL 8.0.22+, you can use CHANGE REPLICATION SOURCE TO instead of CHANGE MASTER TO):

CHANGE MASTER TO
  MASTER_HOST='master_ip',
  MASTER_USER='replica_user',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154;

Start the replication process:
```
START SLAVE;
```
(Note: For MySQL 8.0.22+, the command START REPLICA; can also be used).

Step 6: Verify Replication Status

Ensure that the replication process is running smoothly without errors.

Run the following command on the slave MySQL shell:
```
SHOW SLAVE STATUS\G
```
(Note: For MySQL 8.0.22+, use SHOW REPLICA STATUS\G).
Look for the following parameters in the output:
- Slave_IO_Running: Yes
- Slave_SQL_Running: Yes
- Last_IO_Error / Last_SQL_Error: Should be empty.

If both running states are set to “Yes”, your basic asynchronous MySQL master-slave replication is successfully configured and active.