Detailed explanation of the implementation process of dual-master synchronization of partial tables in MySQL 5.7

Preface

Recently, I encountered a requirement. Due to the company's own business, some tables between two databases need to synchronize data in real time and bidirectionally, and the downtime of any one database will not affect the other database. Here I record my own technical research for use in later reconstruction. Without further ado, let’s take a look at the detailed introduction.

Install MySQL 5.7

It is recommended to go directly to the official website https://dev.mysql.com to download the yum source for installation, after all, it is safe and convenient.

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
# Install MySQL 5.7
systemctl start mysqld
# Start MySQL

Configuring Dual Active

my.cnf configuration

Here we only do a simple dual-master configuration, and no database-related optimization is done. If you need optimization, 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
#Set simple password validate_password_policy=0
validate_password_length=1
###Main configuration log_bin=bin_log
server-id=1
binlog-do-db=ziyun
# Which library is allowed to be replicated from slave_parallel_type='logical_clock'
#Set the replication type to LOGICAL_CLOCK
slave_parallel_workers=4
#Set the number of parallel operations to 4
###From the configuration relay-log=relay-bin
relay-log-index=relay-bin.index
replicate-do-db=ziyun
# Allow replication of the main service library replicate-do-table=ziyun.test1
# Which table of the main service library is allowed to be replicated replicate-do-table=ziyun.test2

The configuration of MySQL2 is basically the same as above, except that server-id=2 is changed and the rest remain unchanged.

MySQL command configuration

The following are 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';
# Authorized replication user and passwordmysql> 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 the 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
# Make sure all the above are yes and there are no errors.

The same configuration can be done on MySQL2

Dual master synchronization test

Here I have built a ziyun database on both sides, and created test1 test2 test3 tables. The test results are as follows:

Summarize

The above is the full content of this article. I hope that the content of this article can bring some help to your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support of 123WORDPRESS.COM.

You may also be interested in: