Introduction to the process of how to implement dual-master synchronization of partial tables in MySQL5.7

This article mainly introduces to you the relevant information about the implementation of some tables of dual-master synchronization in MySQL 5.7. The article introduces it in detail through the example code. It has certain reference learning value for everyone to learn or use mysql. Friends who need it Let’s learn with the editor below.

Preface

Recently encountered a demand. Due to the company's own business, some tables between the two databases need real-time bidirectional data synchronization. And the downtime of any one of the databases will not affect the other database. Record your own technical research here for later use in reconstruction. Not much to say below, let’s take a look at the detailed introduction.

Install MySQL5.7

It is recommended to go directly to the official website and download the yum source to install, after all, it is safe and easy to install.

wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
yum -y install mysql57-community-release-el7-11.noarch.rpm
yum -y install mysql-server
# 安装MySQL5.7
systemctl start mysqld
# 启动MySQL

Configuring dual master

my.cnf configuration

This is just a simple dual master The configuration has not been optimized in database-related aspects. If optimization is needed, you can chat privately.

The configuration of MySQL1 is as follows

[mysqld]
vim /etc/my.cnf
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
skip_name_resolve=ON
#设置简单密码
validate_password_policy=0
validate_password_length=1
###主配置
log_bin=bin_log
server-id=1
binlog-do-db=ziyun
# 允许从复制的哪一个库
slave_parallel_type='logical_clock'
#设置复制类型为 LOGICAL_CLOCK
slave_parallel_workers=4
#设置并行数量为 4
###从配置
relay-log=relay-bin
relay-log-index=relay-bin.index
replicate-do-db=ziyun
# 允许复制主服务的库
replicate-do-table=ziyun.test1
# 允许复制主服务的库的哪一张表
replicate-do-table=ziyun.test2

The configuration of MySQL2 is basically the same as above, just modify one server-id=2, keep the others unchanged

MySQL command configuration

The following are all configured in the SQL command line interface

Main configuration:

mysql> CREATE USER 'slave'@'192.168.1.100' IDENTIFIED BY '123.com';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.1.100';
# 授权复制的用户和密码
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File   | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| bin_log.000002 |  8384 | ziyun  |     |     |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

From configuration:

mysql> CHANGE MASTER TO
 -> MASTER_HOST='192.168.1.101',
 -> MASTER_USER='slave',
 -> MASTER_LOG_FILE='bin_log.000002',
 -> MASTER_LOG_POS=8384;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G
   Slave_IO_Running: Yes
   Slave_SQL_Running: Yes
# 确定以上都为yes，并且无任何报错即可

The same configuration can be done on MySQL2

Dual master Synchronous test

#Here I have previously built a ziyun database on both sides, and built the test1 test2 test3 table. The test results are as follows:

Summarize

The above is the detailed content of Introduction to the process of how to implement dual-master synchronization of partial tables in MySQL5.7. For more information, please follow other related articles on the PHP Chinese website!