[MySQL] master-slave replication

Article directory

I. Introduction
Two, MySQL master-slave replication
- 1. Principle of master-slave replication
- 2. Master-slave replication synchronization method
- - 2.1 MySQL statement-based replication
  - - 2.1.1 Enable the binary log function on the primary server, and record the update operation in the binary log
    - 2.1.2 Connect to the master server on the slave server and start synchronizing data
    - 2.1.3 Updating data on the master server
    - 2.1.4 Check whether the update is synchronized on the slave server:
  - 2.2 Row-based replication
  - - 2.2.1 Create a new database and table on the master server
    - 2.2.2 Insert some rows of data into the master server’s database
    - 2.2.3 Enable MySQL’s binary log (binlog)
    - 2.2.4 Enabling row-based replication on the slave
    - 2.2.5 Add the master server information on the slave server
    - 2.2.6 Start row-based replication
    - 2.2.7 Update some row data on the primary server
    - 2.2.8 Check data synchronization on the slave server
  - 2.3 Hybrid replication
  - - 2.3.1 Create a new database and table on the master server:
    - 2.3.2 Insert some rows of data into the master server’s database:
    - 2.3.3 Enable MySQL binary log (binlog):
    - 2.3.4 Enable hybrid replication on the slave server:
    - 2.3.5 Add the master server information on the slave server:
    - 2.3.6 Start hybrid replication:
    - 2.3.7 Update some row data and insert data on the primary server:
    - 2.3.8 Check data synchronization on the slave server:
- 3. Application scenario of master-slave replication
- - 3. 1 Data backup
  - 3. 2 Load balancing
  - 3. 3 Read and write separation
3. MySQL High Availability
- 1. The concept of high availability
- 2. MySQL High Availability Solution
- - 1. High availability scheme based on master-slave replication
  - 2. High-availability scheme based on master-master replication
  - 3. MySQL high availability solution based on MHA
  - 4. High availability solution based on Galera Cluster
4. Integration of MySQL master-slave replication and high availability
- 1. Preconditions
- 2. MHA + MySQL master-slave replication integrated deployment
- 3. MHA + MySQL master-master replication integrated deployment
- 4. MHA + Galera Cluster integrated deployment
V. Summary and Outlook

1. Foreword

MySQL is a relational database that is widely used in various Internet applications. As the business continues to expand and the amount of data continues to increase, how to make MySQL have better availability and disaster recovery capabilities has become an increasingly important issue. This article will focus on the implementation of MySQL master-slave replication and high availability, aiming to provide guidance and help for MySQL users.

2. MySQL master-slave replication

1. Principle of master-slave replication

MySQL master-slave replication refers to the function of replicating the data of one MySQL server to another MySQL server. Among them, one MySQL server is called the “master server” and the other MySQL server is called the “slave server”. The master server records data updates into the binary log (binlog), and then the slave server obtains the data that needs to be updated and performs corresponding operations by reading the binlog log file on the master server. In this way, the data on the slave server can be synchronized with the master server to achieve data backup, load balancing, read-write separation and other purposes.

2. Master-slave replication synchronization

Database data synchronization can be achieved in the following ways:

2.1 MySQL statement-based replication

MySQL statement-based replication is a master-slave synchronization method for MySQL databases. The master server converts update operations into SQL statements and records them in the binlog. The slave server connects to the master server to poll the binlog and parses the binlog. Synchronize SQL statements and execute these SQL statements on the slave server to achieve master-slave synchronization.
The following are examples of operations based on statement replication:

2.1.1 Enable the binary log function on the master server, and record the update operation in the binary log

Add the following to the master server’s my.cnf configuration file:

[mysqld]
log-bin = mysql-bin #Enable the binary log function and specify the file name to save the log
server-id = 1 #Set server ID

Restart MySQL to make the configuration file take effect.

2.1.2 Connect to the master server on the slave server and start synchronizing data

At the slave server command line, enter the following command:

mysql> CHANGE MASTER TO
    -> MASTER_HOST = 'master_host_ip',
    -> MASTER_USER = 'replication',
    -> MASTER_PASSWORD = 'password',
    -> MASTER_LOG_FILE = 'mysql-bin.000001',
    -> MASTER_LOG_POS = 113;

Among them, MASTER_HOST is the IP address of the master server, MASTER_USER and MASTER_PASSWORD are the synchronization users and passwords of the master server, and MASTER_LOG_FILE and MASTER_LOG_POS are the positions that have been synchronized in the binlog after the slave server last synchronized data.

Then, start the replication process from the server:

mysql> START SLAVE;

2.1.3 Update data on the master server

At the master server command line, enter the following command:

mysql> INSERT INTO test_db. test_table(col1, col2) VALUES(1, 'one');

After this statement is executed on the master server, it will be recorded in the binlog of the master server.

2.1.4 Check whether the update is synchronized on the slave server:

At the slave server command line, enter the following command:

mysql> SELECT * FROM test_db.test_table;

If you can query the data just inserted on the master server, it means that the master-slave synchronization has been successfully performed.

It should be noted that when synchronizing data based on statement replication, the data structures on the master and slave servers must be consistent, otherwise synchronization problems will occur. At the same time, when performing master-slave synchronization, you also need to pay attention to issues such as data consistency and security.

2.2 Row-based replication

Row-based replication: The master server records the updated row data into the binlog, and the slave server periodically polls the master server’s binlog to obtain the row data that needs to be synchronized, and updates the row data on the slave server.
The following is an example of an operation based on row replication:

2.2.1 Create a new database and table on the master server

CREATE DATABASE mydatabase;
USE mydatabase;
CREATE TABLE mytable (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT
);

2.2.2 Insert some rows of data into the database of the main server

INSERT INTO mytable (id, name, age) VALUES (1, 'John', 30);
INSERT INTO mytable (id, name, age) VALUES (2, 'Mary', 25);

2.2.3 Enable MySQL binary log (binlog)

Add the following information to the MySQL configuration file my.cnf:

[mysqld]
log-bin=mysql-bin
server-id=1

2.2.4 Enable row-based replication on the slave

Add the following information to the MySQL configuration file my.cnf:

[mysqld]
server-id=2
binlog-format=ROW
log-slave-updates=TRUE
relay-log=relay-bin

2.2.5 Add the master server information on the slave server

CHANGE MASTER TO MASTER_HOST='IP address of the main server',
MASTER_USER='replication_user',
MASTER_PASSWORD='replication_password',
MASTER_LOG_FILE='The binlog file name of the master server',
MASTER_LOG_POS=The binlog offset of the master server;

Among them, replication_user and replication_password are the account number and password authorized to access the slave server on the master server; the binlog file name and offset of the master server can be obtained through the SHOW MASTER STATUS command.

2.2.6 Start row-based replication

START SLAVE;

2.2.7 Update some row data on the primary server

UPDATE mytable SET age=35 WHERE id=1;

2.2.8 Check data synchronization on the slave server

SELECT * FROM mydatabase.mytable;

It can be found that the age field of the first piece of data in the mytable table on the slave server has been updated to 35.

It should be noted that MySQL’s row-based replication mode can be used not only for master-slave replication, but also for ring replication and hierarchical replication. When using, you need to choose the appropriate replication mode according to your specific needs.

2.3 Hybrid replication

Hybrid replication: This method is based on a hybrid of statement replication and row replication. The master server chooses to record the statement or row data into the binlog according to the different SQL statements. The slave server will also select a method for synchronization according to different SQL statements.

The following are examples of operations based on hybrid replication:

2.3.1 Create a new database and table on the master server:

CREATE DATABASE mydatabase;
USE mydatabase;
CREATE TABLE mytable (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT
);

2.3.2 Insert some rows of data into the database of the main server:

INSERT INTO mytable (id, name, age) VALUES (1, 'John', 30);
INSERT INTO mytable (id, name, age) VALUES (2, 'Mary', 25);

2.3.3 Enable MySQL’s binary log (binlog):

Add the following information to the MySQL configuration file my.cnf:

[mysqld]
log-bin=mysql-bin
server-id=1

2.3.4 Enable hybrid replication on the slave server:

Add the following information to the MySQL configuration file my.cnf:

[mysqld]
server-id=2
log-slave-updates=TRUE
binlog-format=MIXED
relay-log=relay-bin

2.3.5 Add the master server information on the slave server:

CHANGE MASTER TO MASTER_HOST='IP address of the main server',
MASTER_USER='replication_user',
MASTER_PASSWORD='replication_password',
MASTER_LOG_FILE='The binlog file name of the master server',
MASTER_LOG_POS=The binlog offset of the master server;

2.3.6 Start hybrid replication:

START SLAVE;

2.3.7 Update some row data and insert data on the master server:

UPDATE mytable SET age=35 WHERE id=1;
INSERT INTO mytable (id, name, age) VALUES (3, 'Tom', 20);

2.3.8 Check data synchronization on the slave server:

SELECT * FROM mydatabase.mytable;

It can be found that the first two pieces of data of the mytable table on the slave server are synchronized through row replication, while the third piece of data is synchronized through statement replication.

It should be noted that in hybrid replication, if the SQL statement cannot be parsed correctly, row replication will be used for data synchronization. Therefore, if data inconsistency occurs during the use of hybrid replication, it is necessary to check whether the data synchronization method between the master and slave servers is consistent, and whether the SQL statement can be parsed correctly.

3. Application scenario of master-slave replication

MySQL master-slave replication can be applied to the following scenarios:

3. 1 Data backup

Master-slave replication can realize the data backup of the master server. The slave server can be used as a backup server. When the data of the master server is lost or fails, the backup server can be quickly activated to restore the data.

The following is an example of an operation for data backup:

Enable the binary log (binlog) function on the master server:
Add the following information to the MySQL configuration file my.cnf:

[mysqld]
log-bin=mysql-bin
server-id=1

Enable statement-based replication on the slave:
Add the following information to the MySQL configuration file my.cnf:

[mysqld]
server-id=2
binlog-format=STATEMENT
log-slave-updates=TRUE
relay-log=relay-bin

Copy the data from the master server to the slave server:

Add the information of the master server on the slave server:

CHANGE MASTER TO MASTER_HOST='IP address of the main server',
MASTER_USER='replication_user',
MASTER_PASSWORD='replication_password',
MASTER_LOG_FILE='The binlog file name of the master server',
MASTER_LOG_POS=The binlog offset of the master server;

Then, start the replication process on the slave:

START SLAVE;

Back up data manually:
On the slave server, use the mysqldump command to back up the database data to a local file, for example:

mysqldump -u root -p mydatabase > mydatabase.sql

Automatic backup data:
For automatic backup, you can automatically back up database data at a specific time by configuring crontab scheduled tasks. For example:

0 0 * * * mysqldump -u root -p mydatabase > /backup/mydatabase-`date + "%Y%m%d"`.sql

The above command specifies that the backup operation will be performed at 0:00 every day, and the backup file will be saved in the /backup directory, named with the date as the suffix, such as mydatabase-20211010.sql.

It should be noted that backup files need to be stored in a safe place to avoid data leakage or loss. In addition, the backup operation may affect the performance of the server, so you need to select an appropriate backup time according to the actual situation.

3. 2 Load balancing

Master-slave replication can reduce the load on the master server and improve the overall concurrent processing capability by forwarding query requests to the slave server for read operations.

The following is an example of load balancing in action:

Enable the binary log (binlog) function on the master server:

Add the following information to the MySQL configuration file my.cnf:

[mysqld]
log-bin=mysql-bin
server-id=1

Enable statement-based replication on the slave:

Add the following information to the MySQL configuration file my.cnf:

[mysqld]
server-id=2
binlog-format=STATEMENT
log-slave-updates=TRUE
relay-log=relay-bin

Copy the data from the master server to the slave server:

Add the information of the master server on the slave server:

CHANGE MASTER TO MASTER_HOST='IP address of the main server',
MASTER_USER='replication_user',
MASTER_PASSWORD='replication_password',
MASTER_LOG_FILE='The binlog file name of the master server',
MASTER_LOG_POS=The binlog offset of the master server;

Then, start the replication process on the slave:

START SLAVE;

Configure the load balancer:

On the load balancer, the configuration forwards read requests to the slave servers. This can be achieved with load balancing software such as Nginx, HAProxy, LVS, etc. Taking Nginx as an example, you can add the following configuration to its configuration file:

upstream mysql_servers {
server 192.168.1.101:3306;
server 192.168.1.102:3306;
}

server {
listen 3306;
proxy_pass mysql_servers;
}

The above configuration specifies that all requests entering port 3306 are forwarded to the MySQL servers on 192.168.1.101 and 192.168.1.102 to achieve load balancing.

It should be noted that when the load balancer forwards the read request to the slave server for processing, data inconsistency may occur. Because the master server will synchronize to the slave server during the write operation, but it may not be synchronized during the read operation, resulting in the data on the slave server not being up-to-date. Therefore, it is necessary to select an appropriate load balancing strategy according to the actual situation, and ensure data consistency through master-slave replication when necessary.

3. 3 Read and write separation

By forwarding write operations to the master server for processing and forwarding read operations to the slave server, performance problems caused by read-write conflicts can be resolved.

The following is an example of an operation for read-write splitting:

Configure master server

On the master server, binlog_format needs to be set to ROW, which allows the slave server to perform replication operations and ensure data integrity.

Code example:

mysql> SET GLOBAL binlog_format = 'ROW';

Create a slave server certificate

On the master server, a certificate needs to be created for the slave to communicate with the master.

Code example:

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
mysql> FLUSH PRIVILEGES;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;

Configure the slave server

On the slave server, you need to run the CHANGE MASTER TO operation to connect the slave server to the master server.

Code example:

mysql> CHANGE MASTER TO
    -> MASTER_HOST='master_host_name',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='password',
    -> MASTER_LOG_FILE='recorded_log_file_name',
    -> MASTER_LOG_POS=recorded_log_position;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G

Note, please replace MASTER_HOST with the IP address or hostname of the master server, MASTER_USER and MASTER_PASSWORD with the user and password created on the master server, and MASTER_LOG_FILE and MASTER_LOG_POS with the values output by SHOW MASTER STATUS.

test

Now you can write on the master and read on the slave.

Code example:

mysql> INSERT INTO MyTable VALUES (1,'Hello');
mysql> SELECT * FROM MyTable;

This will insert data on the master and it will be readable on the slave.

Please note that in order to ensure synchronization performance, we usually deploy the master server and slave server on different machines and use high-speed network connections for communication.

3. MySQL High Availability

1. High availability concept

High availability means that the system can maintain a normal operating state in the event of a single point of failure or unexpected interruption. Even if the entire system or some system components or nodes fail, these parts cannot affect the normal operation of the entire system.

2. MySQL High Availability Solution

1. High availability scheme based on master-slave replication

Master-slave replication is a solution to increase the availability of MySQL by sending write operations to a master node and read operations to one or more slave nodes. When the master node fails, the slave nodes can take over the duties of the master node. However, the weakness of this scheme is that the single point of failure of the master node may cause data inconsistency of the slave nodes. Therefore, the master-slave synchronization status must be checked periodically.

2. High availability scheme based on master-master replication

Master-master replication is a solution to improve the availability of MySQL using two nodes, each of which can act as a master and a slave. When one node fails, other nodes can continue to serve. This scheme requires more hardware resources, but its data consistency and fault tolerance are higher than the master-slave replication scheme.

3. MySQL high availability solution based on MHA

MHA (Master High Availability) is a solution based on master-slave replication, which provides automatic fault detection and resource allocation functions. When the master node fails, MHA will automatically switch to an available slave node to ensure the availability of MySQL. MHA can realize failover and automatic provision of database services among multiple nodes.

4. High availability solution based on Galera Cluster

Galera Cluster is a cluster solution that provides high availability and scalability. Galera Cluster ensures data consistency by synchronizing data on multiple nodes. It prevents single points of failure by sharing data across multiple nodes, and can automatically perform failback in the event of a failure.

4. MySQL master-slave replication and high availability integration

1. Precondition

Before performing MySQL master-slave replication and high availability integration, you need to meet the following conditions:

Familiar with the installation and configuration of Linux operating system and MySQL database
At least two MySQL instances are running, one as the main database (master) and one or more as backup databases (slave)
Familiar with the architecture and use of MHA and Galera Cluster

2. MHA + MySQL master-slave replication integrated deployment

In MySQL master-slave replication, one database instance (the primary database) synchronizes changes to one or more standby databases. In the event of a failure of the primary library, the standby library can take over the functions of the primary library, thereby achieving high availability. MHA is a widely used tool for managing the high availability of MySQL clusters, which can automatically switch the master-slave role in master-slave replication.

Proceed as follows:

Install the MHA package and copy the configuration file to the /etc directory.
Configuring MHA mainly includes:

Automatic failover, that is, when the main library hangs up, the standby database is automatically promoted to the main library
Monitor the status of the MySQL instance
Designate a new master when a manual failover occurs

Confirm that all MySQL instances can be connected normally, and update the MHA configuration file.
Install ssh to coordinate automated role switching between MHA and MySQL.
To start MHA Manager, execute the following command:

/usr/local/bin/masterha_manager --conf=/etc/app1.cnf

Verify whether MHA is available, and perform master-slave switching test.

3. MHA + MySQL master-master replication integrated deployment

MySQL master-master replication refers to the process of synchronizing changes from one database instance to another so that both instances can handle client requests. MHA can help you manage the failover of MySQL master-master replication instances to achieve high availability.

Proceed as follows:

Install MySQL master-master replication, and make sure that both instances can connect normally.
Install MHA and copy the configuration file to the /etc directory.
Configuring MHA mainly includes:

Automatic failover, that is, when a MySQL instance hangs up, the request is automatically sent to another instance
Monitor the status of the MySQL instance
Designate a new MySQL instance when manual failover occurs

Confirm that all MySQL instances can be connected normally, and update the MHA configuration file.
Install ssh to coordinate automated role switching between MHA and MySQL.
To start MHA Manager, execute the following command:

/usr/local/bin/masterha_manager --conf=/etc/app1.cnf

Confirm whether MHA is available, and perform master-slave switching test.

4. MHA + Galera Cluster integrated deployment

Galera Cluster is a multi-master controlled cluster. In Galera Cluster, each node is the master library, and changes can be propagated throughout the cluster. Using MHA and Galera Cluster integration, high availability and automatic failover can be achieved.

Proceed as follows:

Install Galera Cluster and create related databases and users.
Configure the IP and port numbers of the Galera Cluster cluster nodes.
Install MHA and copy the configuration file to the /etc directory.
Configuring MHA mainly includes:

Automatic failover, that is, when a Galera Cluster node hangs up, the request is automatically sent to another node
Monitor the status of Galera Cluster nodes
Designate new Galera Cluster nodes when manual failover occurs

Confirm that all Galera Cluster nodes can connect normally, and update the MHA configuration file.
Install ssh to coordinate automated role transitions between MHA and Galera Cluster.
To start MHA Manager, execute the following command:

/usr/local/bin/masterha_manager --conf=/etc/app1.cnf

Confirm whether MHA is available, and perform a failover test.

In this article, we provide three ways to integrate MHA and MySQL high availability solutions. You can choose the option that best suits your needs and environment for high availability and automated failover.

5. Summary and Outlook

This blog introduces common solutions for MySQL high availability, including load balancing, master-slave replication, master-master replication, and integration solutions combining MHA and Galera Cluster. High availability is critical for database systems to ensure that user requests are always fulfilled and to reduce the risk of system downtime.

Load balancing is to achieve high availability and efficiency by distributing requests to multiple database nodes. This approach allows adding nodes to the system, increasing capacity and performance. However, its implementation requires some expertise, and several factors need to be considered in deployment to ensure its reliability.

MySQL master-slave replication is a common high-availability solution that synchronizes changes from one database instance (master database) to one or more standby databases (slave databases). When the main library fails, the slave library can take over its functions, thus ensuring the continuous operation of the system. However, in this scheme, the slave library is generally read-only, and we can choose to combine tools such as MHA to achieve automatic failover.

MySQL master-master replication is another common high-availability solution that connects multiple database instances to each other, each of which can handle client requests and receive changes. In this solution, if one instance fails, requests are automatically routed to other instances, thus maintaining system stability. Different from master-slave replication, master-master replication can maintain the read and write permissions of all nodes, and has high reliability.

Both MHA and Galera Cluster are tools to help manage high availability. They can be integrated with MySQL master-slave and master-master replication schemes for failover and high availability. These tools automate role transitions and ensure that data integrity and consistency is maintained in the event of node failure.

In summary, high availability is critical for database systems. When choosing a solution, multiple factors such as system reliability, performance, scalability, and cost need to be considered. While existing solutions are mature and widely used, better solutions may emerge in the future as technology advances and needs change.