How to implement MySQL synchronized data Replication

MySQL's Replication function can synchronize data from one database to multiple other databases. The first one is generally called the master database (master), and the second one is called the slave database (slave). The MySQL replication process uses an asynchronous method, but the delay is very small and synchronized in seconds.

1. Basic principles of synchronous data replication

1. Data changes that occur on the main library are recorded to the binary log Binlog
2. The IO thread of the slave library copies the Binlog of the main library To your own relay log Relay log
3. The SQL thread of the slave library realizes data replication by reading and replaying the relay log

There are three types of MySQL replication Mode: Statement Level, Row Level, Mixed Level. Different replication levels cause the master server to generate different forms of binary log files.

2. Synchronization data example

Operating system: centos7
Database: mysql8
Host (master): 192.168.0.101
Slave: 192.168.0.102

1. Modify the master-slave mysql configuration file

Host configuration my.cnf

[mysqld] 
# 服务器标识，每个服务器不能一样
server_id=101
# 开启日志文件
log_bin=binlog 
# 普通用户只能读 OFF是关闭状态
read_only=off 
# 超级用户只能读 OFF是关闭状态
super_read_only=off

Slave configuration

[mysqld]
# 服务器标识
server_id=102
# 启用binlog日志，并指定文件名前缀
log_bin=binlog
# 普通用户只能读 on是开启状态 
read_only=on 
# 超级用户只能读 on是开启状态 
super_read_only=on

Restart service

systemctl restart mysqld

2. The host establishes a synchronization account and checks the main database status information.

Log in to the database and create an account.

create user repl identified with mysql_native_password by 'repl123';
grant replication slave on *.* to repl;
flush privileges;

Check the main database master status and obtain the log file name and offset information.

3. Slave synchronization configuration

Copy the binary log operation of the host (part of the data is checked by checking the main database master status)
The synchronization account of the host: source_user
Password: source_password
Port: source_port
Log file: source_log_file
Offset: source_log_pos

change replication source to source_host='192.168.0.101', source_user='repl', source_password='repl123', source_port=3306,source_log_file='binlog.000001', source_log_pos=154;

Slave startup synchronization

start replica;

4. View synchronization configuration Effect

On the host, check whether there is a connection from the slave

show processlist;

Check the status of the slave's synchronous replication

show slave status\G

On the host, insert data into a table, Check the data in the corresponding table of the slave machine. The test results are very good (omitted)

The above is the detailed content of How to implement MySQL synchronized data Replication. For more information, please follow other related articles on the PHP Chinese website!