In production database setups, mysql replication is commonly used to duplicate a single database over many database servers.
The primary database is referred to as the master database, while the other replicated databases are referred to as slave databases. To put it another way, we can use it to duplicate a database.
Replication is used to offer high availability, redundancy, and load balancing in a system.
In this article, we will look at the benefits of MySQL replication and the many techniques we may take to configure it to match our specific needs.
What is MySQL Replication?
MySQL replication is a mechanism used to keep multiple copies of data on various servers. It guarantees that data is consistent across all participating servers, providing for high system availability and fault tolerance.
Benefits and Use Cases of Replication
- High Availability: Replication guarantees that data is available on multiple servers, reducing the chance of downtime and data loss if the master server has hardware failure or other difficulties.
- Load Balancing: Replication may assist to balance the load on your database system by dividing read requests over multiple slave servers, boosting speed and response times.
- Data Redundancy: Replication generates multiple copies of data, which can be helpful in keeping backups for disaster recovery or adhering to data preservation regulations.
- Scaling: As your application expands, you may add extra servers to your system, essentially scaling up the database architecture to accommodate increased traffic and demand.
- Geographical Distribution: Replication can distribute data across many geographical locations, lowering latency for consumers accessing your service from different parts of the world.
- Reporting and Analytics: You may reduce the impact of these resource-intensive procedures on the master server's performance by offloading reporting and analytics functions to a slave server/s.
- Testing and Staging: A replicated environment is helpful for testing and staging, ensuring that changes to your application or database schema do not impact production.
Setting up MySQL Replication
Configuration steps
We'll assume throughout this instruction that you've set up a MySQL server on two nodes in the same network.
A high-level view of the steps to follow:
| Step | Tasks to do on Master Node | Tasks to do on Slave Node |
| 1 | Create a backup of the Master node | |
| 2 | Restore the Backup on the Slave node | |
| 3 | Create a replication user on the Master node | |
| 4 | Configuring the Master node server config file | Configuring the Slave node server config file |
| 5 | Restart mysql service on master node | Restart mysql service on Slave node |
| 6 | Replication Initialization |
Prepare the servers
Before starting the replication setup, it's essential to ensure that both the master and slave servers have the same data. This can be achieved by creating a backup of the master server and restoring it on the slave server. You can use tools like mysqldump to create the backup and restore it.
1. Create a backup on Master Server
Stop any write operations to the master server.
To stop write operations on the master server temporarily, you can either use the FLUSH TABLES WITH READ LOCK command or set the global read_only variable. Keep in mind that both methods have implications on your application's behavior, so choose the one that best fits your situation. In this guide we will focus on the FLUSH TABLES WITH READ LOCK command.
Login to the mysql server using a mysql client and execute the read lock.
root@mysql-master:~# mysql -u root mysql> FLUSH TABLES WITH READ LOCK; Query OK, 0 rows affected (0.01 sec)
Perform the backup process using mysqldump.
mysqldump --all-databases --master-data --single-transaction --flush-logs --routines --triggers > backup_$(date +%Y-%m-%d).sql
2. Restore backup on Slave server
Move the backup to the slave server. You can use a tool such as WinSCP to achieve this. Or directly copy file from master server to the slave server using scp, or sftp commands from the command line interface.
root@mysql-slave:~# mysql < backup.sql
3. Create replication user on Master Server
The replication user is essential for the slave server to connect to the master server and read the binary log events. When creating the replication user, grant only the necessary privileges to maintain a secure environment.
mysql> CREATE USER 'replication_user'@'%' IDENTIFIED BY 'replicationpw'; Query OK, 0 rows affected (0.04 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%'; Query OK, 0 rows affected (0.01 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec)
Configuring Replication
Having prepared both servers and created the replication user, you can proceed to configure and initialize replication. For further clarity, we'll divide this section into three parts: Master server configuration, Slave server configuration, and Replication initialization.
1. Configure Master server
Locate and Edit MySQL configuration file to enable binary logging and set the appropriate binary log format. On a typical ubuntu system the file could be located at /etc/mysql/mysql.conf.d/
root@mysql-master:/etc/mysql/mysql.conf.d# ls -al total 16 drwxr-xr-x 2 root root 4096 Apr 30 18:21 . drwxr-xr-x 4 root root 4096 Apr 30 18:21 .. -rw-r--r-- 1 root root 132 Nov 15 20:42 mysql.cnf -rw-r--r-- 1 root root 2220 Nov 15 20:42 mysqld.cnf
Add the below lines to the mysqld.cnf under [mysqld] tag
server-id = 1 log-bin = mysql-bin binlog-format = MIXED log_bin = /var/log/mysql/mysql-bin.log log_bin_index =/var/log/mysql/mysql-bin.log.index
A quick explanation of what each of the configuration line does:
- server-id: Assign a unique ID to the master server (e.g., 1). Each server in the replication setup must have a unique server ID.
- log-bin: Enable binary logging by specifying a log file prefix (e.g., mysql-bin). The binary logs are essential for replication, as they store the data changes made on the master server.
- binlog-format: Set the binary log format, which can be one of the following: STATEMENT (SBR), ROW (RBR), or MIXED (MMR). The choice depends on your replication requirements and priorities.
Also, you will need to edit the bind address configured in the mysqld.conf.
Change it from 127.0.0.1 to the local IP address of the master server.
root@mysql-master:~# ip a 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000 link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1/8 scope host lo valid_lft forever preferred_lft forever inet6 ::1/128 scope host valid_lft forever preferred_lft forever 2: enp0s3: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000 link/ether 08:00:27:9c:bf:26 brd ff:ff:ff:ff:ff:ff inet 192.168.1.6/24 brd 192.168.1.255 scope global dynamic enp0s3 valid_lft 45015sec preferred_lft 45015sec inet6 fe80::a00:27ff:fe9c:bf26/64 scope link valid_lft forever preferred_lft forever root@mysql-master:~# nano /etc/mysql/mysql.conf.d/mysqld.cnf locate the bind-address, and change it to the local IP of the server. … … … bind-address = 192.168.1.6 … … …
Restart mysql: Restart the MySQL Server to apply the changes.
root@mysql-master:~# systemctl restart mysql.service
There are a few inputs we need to get from the master node before we move to configure the slave node. Execute the below to retrieve the necessary information.
mysql> SHOW MASTER STATUS\G; *************************** 1. row *************************** File: mysql-bin.000001 Position: 157 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec) ERROR: No query specified
Please note the highlighted; they will be required in the next steps of configuring the slave node.
2. Slave server configuration
Locate and Edit MySQL configuration file to enable binary logging and set the appropriate binary log format.
nano /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld] server-id = 2 relay-log = mysql-relay-bin
Save and close the configuration file.
Before finishing up the slave server configuration, you will need to change the bind address of the slave server as well. For this, follow the same steps as we followed on the master server.
root@mysql-slave:~# ip a 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000 link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1/8 scope host lo valid_lft forever preferred_lft forever inet6 ::1/128 scope host valid_lft forever preferred_lft forever 2: enp0s3: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000 link/ether 08:00:27:d1:a1:75 brd ff:ff:ff:ff:ff:ff inet 192.168.1.28/24 brd 192.168.1.255 scope global dynamic enp0s3 valid_lft 44706sec preferred_lft 44706sec inet6 fe80::a00:27ff:fed1:a175/64 scope link valid_lft forever preferred_lft forever
Setup Bind Address: Locate the bind-address, and change it to the local IP of the server.
nano /etc/mysql/mysql.conf.d/mysqld.cnf
… … … bind-address = 192.168.1.28 … … …
Restart the MySQL service
root@mysql-slave:~# systemctl restart mysql.service
Initialize Replication
Replication initialization is the process of establishing a connection between the master and slave servers and starting the replication process. This involves configuring the slave server with the master server's connection details and binary log coordinates, and then starting the slave server. Here's a detailed description of the replication initialization process.
Configure the connection to Master
To establish a connection, the slave server needs the master server's IP address or hostname, the replication user's credentials, and the binary log file name and position. These details are provided using the CHANGE MASTER TO command. Follow these steps:
Highlighted values are taken from the outputs of master server configuration.
Connect to the slave server using MySQL Client.
root@mysql-slave:~# mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.32-0ubuntu0.20.04.2 (Ubuntu) Copyright (c) 2000, 2023, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>STOP SLAVE; mysql> CHANGE MASTER TO MASTER_HOST ='192.168.1.6', MASTER_USER ='replication_user', MASTER_PASSWORD ='replicationpw', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 157; mysql>START SLAVE;
# If you encounter any issues while starting the slave, you can follow the below steps
Reset slave
mysql>STOP SLAVE; mysql>RESET SLAVE; mysql> CHANGE MASTER TO MASTER_HOST ='192.168.1.6', MASTER_USER ='replication_user', MASTER_PASSWORD ='replicationpw', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 157; mysql>START SLAVE;
Login to Master from Slave
There may be problems with the authentication method set up on the master server. To check if there are any issues with credentials or permissions, attempt to log in to the master server database using the replication user credentials from the slave server's MySQL client.
From Slave server:
(Master Server IP - 192.168.1.6)
root@mysql-slave:~# mysql -u replication_user -h 192.168.1.6 -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 66 Server version: 8.0.32-0ubuntu0.20.04.2 (Ubuntu) Copyright (c) 2000, 2023, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
If you are not able to login like this from the slave server to the Master server, you will need to troubleshoot the permission issues further.
More information can be found on the mysql error logs. By default, its located in /var/log/mysql/error.log
As soon as the slave is initiated, the databases will be synchronized. Any modifications made on the master node will be simultaneously synced to the slave node(s).
Testing Replication
After setting up everything, finally its time to test and see if the replication mechanism is working or not.
In this example, I have a database named Bookshop that contains a few tables. Authors is one of them, and for the example, I'm altering the data on the
master node's Authors table to check if the changes are reflected on the slave node.
Queries On Master Node:
mysql> show tables; +--------------------+ | Tables_in_Bookshop | +--------------------+ | Authors | | Books | | Customers | | OrderDetails | | Orders | +--------------------+ 5 rows in set (0.00 sec) mysql> select * from Authors; +----------+-------------+-------------+ | AuthorID | FirstName | LastName | +----------+-------------+-------------+ | 1 | J.K. | Rowling | | 2 | Stephen | King | | 3 | J.R.R. | Tolkien | | 4 | George R.R. | Martin | | 12 | William | Shakespeare | +----------+-------------+-------------+ 5 rows in set (0.00 sec) mysql> delete from Authors where AuthorID = 12; Query OK, 1 row affected (0.03 sec) mysql> select * from Authors; +----------+-------------+----------+ | AuthorID | FirstName | LastName | +----------+-------------+----------+ | 1 | J.K. | Rowling | | 2 | Stephen | King | | 3 | J.R.R. | Tolkien | | 4 | George R.R. | Martin | +----------+-------------+----------+ 4 rows in set (0.00 sec) mysql> INSERT INTO Authors (AuthorID, FirstName, LastName) VALUES (5, 'Leo', 'Tolstoy'); Query OK, 1 row affected (0.03 sec) mysql> select * from Authors; +----------+-------------+----------+ | AuthorID | FirstName | LastName | +----------+-------------+----------+ | 1 | J.K. | Rowling | | 2 | Stephen | King | | 3 | J.R.R. | Tolkien | | 4 | George R.R. | Martin | | 5 | Leo | Tolstoy | +----------+-------------+----------+ 5 rows in set (0.00 sec)
Queries On Slave Node:
mysql> show tables; +--------------------+ | Tables_in_Bookshop | +--------------------+ | Authors | | Books | | Customers | | OrderDetails | | Orders | +--------------------+ 5 rows in set (0.00 sec) mysql> select * from Authors; +----------+-------------+-------------+ | AuthorID | FirstName | LastName | +----------+-------------+-------------+ | 1 | J.K. | Rowling | | 2 | Stephen | King | | 3 | J.R.R. | Tolkien | | 4 | George R.R. | Martin | | 12 | William | Shakespeare | +----------+-------------+-------------+ 5 rows in set (0.00 sec) mysql> select * from Authors; +----------+-------------+----------+ | AuthorID | FirstName | LastName | +----------+-------------+----------+ | 1 | J.K. | Rowling | | 2 | Stephen | King | | 3 | J.R.R. | Tolkien | | 4 | George R.R. | Martin | +----------+-------------+----------+ 4 rows in set (0.00 sec) mysql> select * from Authors; +----------+-------------+----------+ | AuthorID | FirstName | LastName | +----------+-------------+----------+ | 1 | J.K. | Rowling | | 2 | Stephen | King | | 3 | J.R.R. | Tolkien | | 4 | George R.R. | Martin | | 5 | Leo | Tolstoy | +----------+-------------+----------+ 5 rows in set (0.00 sec)
As we can see, the entries in the "Authors" table in slave database reflects data exactly similar to the master server. This shows that replications setup correctly and working.
In this article mysql replication was setup and tested on Ubuntu.
root@mysql-master:~# lsb_release -d Description: Ubuntu 20.04.6 LTS
Types of MySQL Replication
MySQL replication can be classified into three primary types based on the replication format: statement-based replication (SBR), row-based replication (RBR), and mixed-mode replication (MMR). Each type has its advantages and disadvantages, depending on the specific requirements of your database system.
1. Statement-Based Replication (SBR)
In statement-based replication, the master server records SQL statements that modify data in its binary log. These statements are then executed on the slave server to replicate the changes. SBR replicates the actions performed on the master server rather than the actual data changes.
Advantages of SBR
- Smaller binary log size compared to RBR, as only the SQL statements are logged
- Easier to audit and review, since the logged statements are human-readable
Disadvantages of SBR
- In some cases, it may produce different results on the master and slave servers due to non-deterministic statements or functions.
- Slower replication, as the slave server needs to execute each SQL statement to apply the changes
2. Row-Based Replication (RBR)
In row-based replication, the master server logs the actual data changes made by SQL statements in its binary log. The slave server then applies these changes directly to its copy of the data. RBR replicates the data changes rather than the actions performed on the master server.
Advantages of RBR
- More accurate and consistent replication, as it replicates the exact data changes
- Faster replication, as the slave server doesn't need to execute SQL statements to apply the changes
Disadvantages of RBR
- Larger binary log size compared to SBR, as the logged data changes can be more voluminous
- More difficult to audit and review, since the logged data changes are not human-readable
3. Mixed-Mode Replication (MMR)
Mixed-mode replication combines the best of both SBR and RBR by automatically choosing the most appropriate replication format based on the executed SQL statement. MMR is the default replication mode in MySQL and is designed to provide a balance between the benefits of SBR and RBR.
Advantages of MMR
- Automatically selects the most suitable replication format for each SQL statement, optimizing for consistency and performance
- Provides a balance between the human-readable logs of SBR and the accurate replication of RBR
Disadvantages of MMR
- Can still encounter issues related to non-deterministic statements, although less frequently than in SBR
- Requires additional decision-making overhead during replication, which may slightly impact performance
In summary, the choice between SBR, RBR, and MMR depends on your specific needs and priorities. Each type has its strengths and weaknesses, and understanding their differences is crucial for selecting the most appropriate replication format for your MySQL system.
Components of MySQL Replication
MySQL replication consists of several key components that work together to ensure data consistency and synchronization across the master and slave servers. Understanding these components is essential for effectively setting up and managing a replicated MySQL environment.
- Master Server
- Slave Server
- Binary Logs
- Relay Logs
- Global Transaction Identifiers (GTIDs)
- Replication Threads
Master Server
The master server is the primary source of data in a MySQL replication setup. It is responsible for processing write operations (INSERT, UPDATE, DELETE) and logging these changes in the binary log. The master server also coordinates the replication process by sending updates to the connected slave servers.
Slave Server
The slave server is the recipient of data changes from the master server. It reads the updates from the master's binary log and applies them to its local copy of the data. Slave servers can be configured to accept read operations (SELECT), helping distribute the workload and improve overall system performance.
Binary Logs
Binary logs are a critical component of the replication process. They record all data modifications made on the master server, either as SQL statements (in SBR) or actual data changes (in RBR). The binary logs are stored on the master server and serve as the source of updates for the slave servers.
Relay Logs
Relay logs are used by the slave server to store the updates received from the master server's binary logs. Once the updates are stored in the relay logs, the slave server applies them to its local copy of the data. Relay logs act as a buffer, allowing the slave server to catch up with the master server in case of temporary connectivity issues or delays in replication.
Global Transaction Identifiers (GTIDs)
Global Transaction Identifiers (GTIDs) are unique identifiers assigned to each transaction in a MySQL replication setup. GTIDs simplify the management of replication by allowing you to track and compare the progress of individual transactions across the master and slave servers. They help ensure consistency and avoid data conflicts during replication.
Replication Threads
There are two main replication threads involved in the MySQL replication process:
- I/O Thread: This thread runs on the slave server and is responsible for connecting to the master server, reading the binary log events, and writing them to the slave's relay logs.
- SQL Thread: This thread also runs on the slave server and is responsible for reading the relay logs, applying the logged events to the local data, and updating the slave's position in the master's binary log.
Understanding these components is crucial for effectively setting up and managing a MySQL replication environment. Each component plays a specific role in the replication process, and ensuring their proper configuration and operation is key to maintaining a consistent and synchronized database system.
Conclusion
So that was a quick and brief guide for beginners on how to setup database replication with mysql. Replication is a critical and powerful service required in enterprise environment that need redundant and backup data servers.
Let us know your thoughts and questions in the comments below.
