Database series (6)-MariaDB master-slave replication concept and read-write separation environment construction

Master-slave replication concept

Replication is a function that allows the content mirroring of one or more servers (called the master server) to be synchronized to one or more servers (called the slave servers).

You can control the data to be copied. All databases, one or more databases, or every table in a database can be selectively replicated.

Main mechanism : The main mechanism used in replication is the binary log. If binary logging is enabled, all updates to the database (data operations and data definitions) will be written to the binary log as binary log events. The slave station reads the binary log from each master station in order to access the data to be copied. Create a relay log on the slave server and use the same format as the binary log to perform replication. When no longer needed, the old relay log file will be deleted.

The slave server tracks the position of the last event applied on the slave server in the binary log of the master server. This allows the slave server to reconnect after replication is temporarily stopped and recover from where it stopped. It also allows the slave to disconnect, be cloned, and then allow the new slave to resume replication from the same master.

The master station and the slave station do not need to communicate with each other frequently. It is very possible to take the servers offline or disconnect from the network, and when they resume, replication will continue where they left off.

scenes to be used

Copy is used in many common scenarios. Uses include:

  • Scalability. By having one or more slave servers, reads can be distributed across multiple servers, thereby reducing the load on the master server. The most common scenario for a high-read and low-write environment is that there is a master server, where all write operations occur, replicated to multiple slave servers, and they handle most of the read operations.
  • data analysis. The analysis data may have too much impact on the master server, which can be handled similarly on the slave server, and the master server continues to be unaffected by the additional load.
  • Backup help. If the server is not actively changing the data, it can be easier to run a backup. A common scenario is to copy data to the slave, then disconnect from the master, and the data is in a stable state. Then perform a backup from this server. See Replication as a backup solution.
  • Data distribution. You can copy data locally and work with this data instead of connecting to a remote master server.

Common deployment architecture diagram

One master and one slave or one master and multiple slaves

Provide unlimited read expansion, and provide high availability by upgrading slave to master.

Insert picture description here

Circular copy

Provides read-write scaling and does not handle conflicts. If a master fails, replication will stop.

Insert picture description here

Star copy

Provides read-write scaling, does not handle conflicts, and must use replication filters to avoid data duplication.

Insert picture description here

Multi-source replication

Allows you to combine data from different sources, and different domains are executed independently and in parallel on all slaves.

Insert picture description here

Read and write separation environment construction

1. Install Mariadb

Installation documentation
Prepare two virtual machines and install the database separately by referring to the documentation.

Insert picture description here

2. Configure the main database

[[email protected] my.cnf.d]# cd /etc/my.cnf.d/
[[email protected] my.cnf.d]# vim server.cnf 
[[email protected] my.cnf.d]# vi server.cnf 
# 添加内容
[mariadb]
# 开启二进制日志
log-bin
# 每个 MariaDB/MySQL 服务器的唯一编号
server_id=1
# binlog日志前缀
log-basename=master1
# binlog日志格式,mixed表示混合模式
binlog-format=mixed
# 重启数据库
[[email protected] my.cnf.d]# systemctl restart mariadb
[[email protected] my.cnf.d]# mysql -uroot -p
# 创建从主数据库复制数据的用户及密码
MariaDB [(none)]> CREATE  USER  'replication_user'@'%'  IDENTIFIED  BY  'bigs3cret'; 
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';
# 查看主数据库状态 File表示日志文件,Position 表示当前二进制日志位置,
# 配置从节点时会用到,从节点需要从日志文件和位置读取数据
MariaDB [(none)]>  show master status;
+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| master1-bin.000001 |   568|              |                  |
+--------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

3. Configure the slave database

[[email protected] ~]# vi /etc/my.cnf.d/server.cnf 
# 添加
[mysqld]
# 全局唯一的server-id
server_id=2
# 开启只读模式,root用户不起效,实际环境需要配置只读数据的用户
read_only=ON
[[email protected] ~]# systemctl restart mariadb
[[email protected] ~]# mysql -uroot -p
# 输入命令,需要替换为自己的主服务器信息
# MASTER_HOST=》主服务器ip
# MASTER_USER=》 用于复制的账户名
# MASTER_PASSWORD=》用于复制的账户密码 
# MASTER_PORT=》主服务器端口
# MASTER_LOG_FILE=》主服务器binlog日志名,和File保持一致               
# MASTER_LOG_POS=》 主服务器binlog日志记录位置,和Position保持一致
# MASTER_CONNECT_RETRY=》 连接重试次数
CHANGE MASTER TO
  MASTER_HOST='192.168.58.241',
  MASTER_USER='replication_user',
  MASTER_PASSWORD='bigs3cret',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='master1-bin.000001',
  MASTER_LOG_POS=568,
  MASTER_CONNECT_RETRY=10;
# 开启复制
MariaDB [(none)]> start slave;
# 查看复制状态
MariaDB [(none)]> show slave status;
# 查看进程
MariaDB [(none)]> show processlist;
# 查看复制所有信息,图中箭头位置为YES时,表示配置完成
MariaDB [(none)]> show slave status\G;
Insert picture description here

4. Testing

Add tables and data to the main library, and the slave library immediately synchronizes the data. In summary, the read-write separation environment of one master and one slave is set up, and the other modes are similar.

Insert picture description here