MySQL Replication Basics

Introduction

In this lab, we will explore the fundamentals of MySQL replication. The primary goal is to set up a basic master-slave replication environment, enabling data synchronization between two MySQL servers.

The lab begins by configuring a master server with binary logging enabled, which is essential for recording all data modifications. We'll edit the MySQL configuration file (/etc/mysql/mysql.conf.d/mysqld.cnf) to set log_bin = mysql-bin and server_id = 1. Subsequently, we will set up a slave server to replicate data from the master, verify the replication status using SHOW SLAVE STATUS, and finally, simulate and fix a replication error to understand troubleshooting techniques.

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL mysql(("MySQL")) -.-> mysql/BasicKeywordsandStatementsGroup(["Basic Keywords and Statements"]) mysql(("MySQL")) -.-> mysql/TransactionManagementandSecurityGroup(["Transaction Management and Security"]) mysql(("MySQL")) -.-> mysql/SystemManagementToolsGroup(["System Management Tools"]) mysql/BasicKeywordsandStatementsGroup -.-> mysql/create_database("Database Creation") mysql/BasicKeywordsandStatementsGroup -.-> mysql/create_table("Table Creation") mysql/BasicKeywordsandStatementsGroup -.-> mysql/select("Data Retrieval") mysql/BasicKeywordsandStatementsGroup -.-> mysql/insert("Data Insertion") mysql/TransactionManagementandSecurityGroup -.-> mysql/grant_permission("Permission Granting") mysql/SystemManagementToolsGroup -.-> mysql/show_status("Status Overview") mysql/SystemManagementToolsGroup -.-> mysql/show_variables("Configuration Overview") subgraph Lab Skills mysql/create_database -.-> lab-550913{{"MySQL Replication Basics"}} mysql/create_table -.-> lab-550913{{"MySQL Replication Basics"}} mysql/select -.-> lab-550913{{"MySQL Replication Basics"}} mysql/insert -.-> lab-550913{{"MySQL Replication Basics"}} mysql/grant_permission -.-> lab-550913{{"MySQL Replication Basics"}} mysql/show_status -.-> lab-550913{{"MySQL Replication Basics"}} mysql/show_variables -.-> lab-550913{{"MySQL Replication Basics"}} end

Configure a Master Server with Binary Logging

In this step, we will configure a MySQL server to act as the master in a replication setup. A crucial part of this configuration is enabling binary logging. Binary logs record all data modifications made to the database, which are then used by the slave server to replicate the changes.

First, let's understand why binary logging is important. Without binary logging, the slave server would have no way of knowing what changes to replicate from the master. Binary logs act as a transaction log, ensuring that the slave server stays synchronized with the master.

Now, let's configure the master server.

Edit the MySQL configuration file:

The MySQL configuration file is usually located at /etc/mysql/mysql.conf.d/mysqld.cnf. We'll use nano to edit this file.
```
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
```
Enable binary logging:

Add the following lines to the mysqld section of the configuration file. If the mysqld section doesn't exist, locate the [mysqld] line and add the following configurations below it.
```
log_bin = mysql-bin
server_id = 1
```
- log_bin = mysql-bin: This enables binary logging and sets the base name for the log files to mysql-bin. MySQL will automatically append a sequence number to this base name (e.g., mysql-bin.000001, mysql-bin.000002, etc.).
- server_id = 1: This sets a unique ID for the master server. Each server in a replication setup must have a unique server_id.
The mysqld section of your configuration file should now look similar to this:
```
[mysqld]
#
## * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
log_bin = mysql-bin
server_id = 1
## ... other configurations ...
```
Press Ctrl+X, then Y, then Enter to save the changes and exit nano.
Restart the MySQL server:

To apply the changes, restart the MySQL server. Since we are in a Docker container, we cannot use systemctl. Instead, we will use the mysql.server script.
```
sudo /etc/init.d/mysql restart
```
You should see output similar to:
```
[ ok ] Restarting mysql (via systemctl): mysql.service.
```

Verify binary logging is enabled:

Log in to the MySQL server as the root user. You may need to use sudo to access MySQL if you haven't set up a password for the root user yet.

sudo mysql -u root

Run the following SQL query to check the status of binary logging:

SHOW VARIABLES LIKE 'log_bin';

The output should be:

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

This confirms that binary logging is enabled.

You can also check the server ID:

SHOW VARIABLES LIKE 'server_id';

The output should be:

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.00 sec)

Exit the MySQL monitor:

exit

You have now successfully configured the master server with binary logging. The next step will involve setting up the slave server to replicate data from this master.

Set Up a Slave Server to Replicate Data

In this step, we will configure a MySQL server to act as a slave, replicating data from the master server configured in the previous step. This involves configuring the slave server's settings and establishing a connection to the master.

Configure the Slave Server:

First, we need to configure the slave server with a unique server_id and enable relay logging. Relay logs are used by the slave server to store the binary log events received from the master before applying them to its own database.

Edit the MySQL configuration file on the slave server (usually located at /etc/mysql/mysql.conf.d/mysqld.cnf):
```
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
```
Add or modify the following lines in the [mysqld] section:
```
server_id = 2
relay_log = mysql-relay-bin
```
- server_id = 2: This sets a unique ID for the slave server. It must be different from the master server's server_id.
- relay_log = mysql-relay-bin: This enables relay logging and sets the base name for the relay log files.
The mysqld section of your configuration file should now look similar to this:
```
[mysqld]
#
## * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
server_id = 2
relay_log = mysql-relay-bin
## ... other configurations ...
```
Press Ctrl+X, then Y, then Enter to save the changes and exit nano.
Restart the MySQL server on the slave:

Restart the MySQL server to apply the changes:
```
sudo /etc/init.d/mysql restart
```
You should see output similar to:
```
[ ok ] Restarting mysql (via systemctl): mysql.service.
```
Create a Replication User on the Master Server:

On the master server, log in to the MySQL server as the root user:
```
sudo mysql -u root
```
Create a dedicated user for replication. Replace 'slave_user' with your desired username and 'password' with a strong password. Also, replace '%' with the IP address of your slave server for better security. If you are unsure of the slave server's IP address, you can use '%' to allow connections from any host (not recommended for production environments).
```
CREATE USER 'slave_user'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%';
FLUSH PRIVILEGES;
```
- CREATE USER 'slave_user'@'%' IDENTIFIED BY 'password': Creates a new MySQL user named slave_user that can connect from any host ('%').
- GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%': Grants the REPLICATION SLAVE privilege to the slave_user on all databases and tables. This privilege is required for the slave server to request binary log updates from the master.
- FLUSH PRIVILEGES: Reloads the grant tables to apply the new privileges.
Exit the MySQL monitor on the master server:
```
exit
```
Configure the Slave to Connect to the Master:

On the slave server, log in to the MySQL server as the root user:
```
sudo mysql -u root
```
Tell the slave server how to connect to the master server. Replace 'master_host' with the IP address or hostname of your master server, 'slave_user' with the replication username you created, and 'password' with the replication password.
```
CHANGE MASTER TO
    MASTER_HOST='master_host',
    MASTER_USER='slave_user',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=4;
```
- MASTER_HOST: The hostname or IP address of the master server.
- MASTER_USER: The username the slave will use to connect to the master.
- MASTER_PASSWORD: The password for the replication user.
- MASTER_LOG_FILE: The name of the master's binary log file to start replication from. You can find this by running SHOW MASTER STATUS; on the master server. For a fresh setup, it's usually mysql-bin.000001.
- MASTER_LOG_POS: The position in the binary log file to start replication from. You can find this by running SHOW MASTER STATUS; on the master server. For a fresh setup, it's usually 4.
Important: You need to determine the correct MASTER_LOG_FILE and MASTER_LOG_POS from the master server. On the master server, execute the following query:
```
SHOW MASTER STATUS;
```
The output will show the File (which corresponds to MASTER_LOG_FILE) and Position (which corresponds to MASTER_LOG_POS). Use these values in the CHANGE MASTER TO command on the slave server.

After executing the CHANGE MASTER TO command, start the slave:
```
START SLAVE;
```
Exit the MySQL monitor on the slave server:
```
exit
```

You have now configured the slave server to replicate data from the master server. In the next step, we will verify that replication is working correctly.

Verify Replication with SHOW SLAVE STATUS

In this step, we will verify that the slave server is successfully replicating data from the master server. We'll use the SHOW SLAVE STATUS command to check the status of the replication threads and identify any potential issues.

Check Slave Status:

On the slave server, log in to the MySQL server as the root user:
```
sudo mysql -u root
```
Execute the following command to view the slave status:
```
SHOW SLAVE STATUS\G
```
The \G at the end of the command formats the output vertically, making it easier to read.
Interpret the Output:

The output of SHOW SLAVE STATUS provides detailed information about the replication process. Here are some key fields to look for:
- Slave_IO_State: This indicates the current state of the I/O thread, which is responsible for reading binary log events from the master. It should ideally show Waiting for master to send event.
- Slave_IO_Running: This indicates whether the I/O thread is running. It should be Yes.
- Slave_SQL_Running: This indicates whether the SQL thread is running. The SQL thread applies the events received from the master to the slave database. It should be Yes.
- Last_IO_Error: If there are any errors related to the I/O thread, they will be displayed here.
- Last_SQL_Error: If there are any errors related to the SQL thread, they will be displayed here.
- Master_Log_File: The name of the binary log file on the master server that the slave is currently reading from.
- Read_Master_Log_Pos: The position in the binary log file that the slave has read up to.
- Relay_Log_File: The name of the relay log file on the slave server that the SQL thread is currently reading from.
- Relay_Log_Pos: The position in the relay log file that the SQL thread has read up to.
- Seconds_Behind_Master: This indicates how many seconds the slave is behind the master. A value of 0 means the slave is up-to-date. A larger value indicates that the slave is lagging behind.
A successful replication setup will have Slave_IO_Running and Slave_SQL_Running set to Yes, and Seconds_Behind_Master should be close to 0.

Example of a successful SHOW SLAVE STATUS output (truncated):
```
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master_host
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 311
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 472
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
            Master_SSL_CA_File:
            Master_SSL_CA_Path:
               Master_SSL_Cert:
             Master_SSL_Cipher:
                Master_SSL_Key:
         Seconds_Behind_Master: 0
...
```

Create a Test Database and Table on the Master:

To further verify replication, create a test database and table on the master server:

sudo mysql -u root

CREATE DATABASE test_replication;
USE test_replication;
CREATE TABLE test_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    data VARCHAR(255)
);
INSERT INTO test_table (data) VALUES ('This is a test record from master');
exit

Verify the Data on the Slave:

On the slave server, log in to the MySQL server as the root user:

sudo mysql -u root

Check if the test_replication database and test_table table have been replicated:

USE test_replication;
SELECT * FROM test_table;
exit

You should see the data inserted on the master server:

+----+-----------------------------------------+
| id | data                                    |
+----+-----------------------------------------+
|  1 | This is a test record from master       |
+----+-----------------------------------------+
1 row in set (0.00 sec)

If you see the test database, table, and data on the slave server, replication is working correctly.

Simulate a Replication Error and Fix It

In this step, we will simulate a common replication error and demonstrate how to resolve it. This will help you understand how to troubleshoot replication issues in a real-world scenario.

Simulate an Error:

A common replication error occurs when the slave server encounters a data inconsistency. We'll simulate this by manually inserting a row into the test_replication.test_table table on the slave server, which will cause a conflict when the master tries to replicate the same row.

On the slave server, log in to the MySQL server as the root user:
```
sudo mysql -u root
```
Insert a row into the test_replication.test_table table:
```
USE test_replication;
INSERT INTO test_table (data) VALUES ('This is an intentionally conflicting record on the slave');
exit
```
Trigger Replication:

Now, insert another row into the test_replication.test_table table on the master server:
```
sudo mysql -u root
```
```
USE test_replication;
INSERT INTO test_table (data) VALUES ('This is a new record from master');
exit
```
This insertion on the master will trigger replication to the slave. However, because we manually inserted a conflicting row on the slave, the replication process will likely encounter an error.

Check Slave Status:

On the slave server, log in to the MySQL server as the root user:

sudo mysql -u root

Check the slave status:

SHOW SLAVE STATUS\G

Examine the output. You should see that Slave_SQL_Running is likely set to No, and the Last_SQL_Error field will contain an error message indicating a duplicate key or similar conflict.

Example of error output:

...
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master_host
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay-bin.000003
                Relay_Log_Pos: 311
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1062
                   Last_Error: Duplicate entry '1' for key 'PRIMARY'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 472
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
            Master_SSL_CA_File:
            Master_SSL_CA_Path:
               Master_SSL_Cert:
             Master_SSL_Cipher:
                Master_SSL_Key:
         Seconds_Behind_Master: NULL
...

Fix the Replication Error:

To fix this error, we will skip the problematic event on the slave server. This tells the slave to ignore the event that caused the error and continue replicating from the next event.

First, stop the slave SQL thread:
```
STOP SLAVE SQL_THREAD;
```
Next, skip the error event:
```
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
```
This command tells the slave to skip the next event in the relay log.

Finally, start the slave SQL thread:
```
START SLAVE SQL_THREAD;
```
Verify Replication is Running Again:

Check the slave status again:
```
SHOW SLAVE STATUS\G
```
You should now see that Slave_SQL_Running is set to Yes, and Last_SQL_Error is empty. Replication should be running normally again.

Verify Data Consistency:

On the slave server, check the contents of the test_replication.test_table table:

USE test_replication;
SELECT * FROM test_table;
exit

You should see both the intentionally conflicting record and the new record from the master. The conflicting record was already present, and the new record was successfully replicated after skipping the error.

+----+-----------------------------------------------------+
| id | data                                                |
+----+-----------------------------------------------------+
|  1 | This is a test record from master                   |
|  2 | This is an intentionally conflicting record on the slave |
|  3 | This is a new record from master                    |
+----+-----------------------------------------------------+
3 rows in set (0.00 sec)

You have now successfully simulated a replication error and fixed it by skipping the problematic event. This is a common technique for resolving replication issues caused by data inconsistencies. Remember that skipping events should be done with caution, as it can lead to data divergence if not handled properly. It's crucial to understand the cause of the error before skipping events.

Summary

In this lab, the initial step focuses on configuring a MySQL server as the master in a replication setup by enabling binary logging. This involves editing the MySQL configuration file, typically located at /etc/mysql/mysql.conf.d/mysqld.cnf, and adding the lines log_bin = mysql-bin and server_id = 1 within the mysqld section.

Enabling binary logging is crucial because it allows the master server to record all data modifications, which are then used by the slave server to replicate the changes, ensuring data synchronization. The log_bin directive specifies the base name for the binary log files, while the server_id assigns a unique identifier to the master server within the replication environment.