1. Build MySQL master-slave database

Refer to another blog post of the author
https://blog.csdn.net/weixin_37838921/article/details/104033403

2.MySQL dual machine environment

3. MySQL dual database construction

stay Building a MySQL master-slave database The data synchronization from master1 to master2 has been completed in. Next, the data synchronization from master2 to master1 needs to be completed.
Log in to master2 to view account information

use mysql; 
select Host, User from user; // View host and user name

Add the account of master1.

stop slave;
set global validate_password_policy=LOW; //Modify password policy, optional
set global validate_password_length=6; //Change password length, optional
create user 'repl'@'192.168.11.10' identified by '123456';
GRANT REPLICATION SLAVE ON *.* to 'repl'@'192.168.11.10' identified by '123456';
flush privileges;

View account information

Modify the configuration files of master1 and master2 (vi /etc/my.cnf) about dual computers (configuration of master and slave computers)

master1:

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysqld] character_set_server=utf8 init_connect='SET NAMES utf8'
#Master slave hot standby plus
log_bin = mysql-bin
binlog_format = mixed
server_id = 1
#read-only = 0
replicate-do-db=test
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
#auto-increment-increment=2
#auto-increment-offset=1
relay_log=mysql-relay-log
log-slave-updates=ON

master2:

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysqld] character_set_server=utf8 init_connect='SET NAMES utf8'
#Master slave hot standby plus
log_bin = mysql-bin
binlog_format = mixed
server_id = 2
#read-only = 0
replicate-do-db=test
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
#auto-increment-increment=2
#auto-increment-offset=1
relay_log=mysql-relay-log
log-slave-updates=ON

Restart mysql of master1 and master2

systemctl restart mysqld

Log in to master2 and execute

show master status\G;

The values of File and Position will be used later.
Log in to the master1 database and execute

change master to
master_host='192.168.11.30',
master_port=3306,
master_user='repl',
master_password='123456',
master_log_file='mysql-bin.000001',
master_log_pos=154;

Where master log File and master log POS are the values of File and Position in the result of Master 2 database show master status\G

start slave;

implement

show master status\G;

The values of File and Position will be used later.
Log in to the master2 database and execute

stop slave;
reset slave;

Then execute

change master to
master_host='192.168.11.10',
master_port=3306,
master_user='repl',
master_password='123456',
master_log_file='mysql-bin.000001',
master_log_pos=154;

Where master log File and master log POS are the values of File and Position in the result of master 1 database show master status\G; respectively.

start slave;

Execute in master1 and master2 respectively

show slave status\G;

You can see that the values of slave IO running and slave SQL running are Yes, which indicates that the MySQL dual machine environment is built successfully.

4. MySQL dual database test

Log in to master1 and create the test database

create database test;

Log in to master2 to see the test database created by master1

Create table t1 in master 2 database and insert record

use test;
create table t1(id int,name varchar(10));
insert into t1 values(1,"zhangsan"),(2,"lisi");
select * from t1;

In the test database of master1, you can see the t1 table created by master2

So far, the MySQL dual database has been successfully built.

5. The solution to the problem of MySQL database synchronization

Before building a dual machine database, if the database structures of master1 and master2 are inconsistent, it may lead to the failure of building a dual machine database, and the problem of synchronization after building a dual machine database. At this time, it is necessary to ensure that the database structure of master1 and master2 is consistent, delete the deleted database and table (or back them up first), make the structure of master1 and master2 consistent, and then build a dual machine database.
Execute in master1 and master2 respectively

show master status\G;

Then?

stop slave;
reset slave;
change master to
master_host='192.168.11.10',
master_port=3306,
master_user='repl',
master_password='123456',
master_log_file='master1/master2 master State File value',
master_log_pos=master1/master2 master State Position value;

Please refer to the third step of this blog to build MySQL dual database