mysql is the master-slave copy of each other

Posted by cybersurfur on Sun, 19 Jan 2020 10:28:59 +0100

Master-1: 192.169.7.242
Master-2: 192.169.0.197
MySQL version: mysql-5.7

1. Modification and description of MySQL configuration file

Modify mysql of the two nodes respectively. Here, modify the configuration file to / etc/my.cnf
Modify Master-1 as follows:

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.

[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

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

# 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 

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 



server-id=1 #Unique identification of server
auto_increment_offset=1 #Starting value of self increasing id
auto_increment_increment=2 #Self increasing number per time

log-bin = mysql-bin #Open binary function, MASTER master MASTER server must open this item
max_binlog_size=1024M #binlog single file maximum
binlog_format=mixed #Specify the format of mysql's binlog log, mixed mode
relay-log=relay-bin #Turn on the relay log function
relay-log-index=slave-relay-bin.index #Relay log list

replicate-ignore-db = mysql #Ignore unsynchronized master slave database
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate-ignore-db = test

Modify Master-2 as follows:

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.

[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

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

# 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 

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 


server-id=2 #Unique identification of server
auto_increment_offset=2 #Starting value of self increasing id
auto_increment_increment=2 #Self increasing number per time

log-bin = mysql-bin #Open binary function, MASTER master MASTER server must open this item
max_binlog_size=1024M #binlog single file maximum
binlog_format=mixed #Specify the format of mysql's binlog log, mixed mode
relay-log=relay-bin #Turn on the relay log function
relay-log-index=slave-relay-bin.index #Relay log list

replicate-ignore-db = mysql #Ignore unsynchronized master slave database
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate-ignore-db = test

Be careful:

The server ID and auto increment offset configurations of Master-1 and Master-2 are different, and the others are the same.
Server ID: it is the server number. Therefore, the values on the two servers are different. Here they are set as 1 and 2
Auto increment offset: used to set the value of automatic growth in the database, so as to prevent duplicate fields of sub growth when two sets of external services are provided at the same time
In my.cnf configuration file, add "binlog? Do? DB = database name" configuration item (multiple can be added) to specify the database to be synchronized. If this configuration item is configured, but the database behind it is not added, binlog does not record its events

2. Restart mysql to make the configuration file effective

Execute the following commands on two nodes respectively:

service mysql stop
service mysql start

3. Add master-slave synchronization account

The operation of Master-1 is as follows:

grant replication slave on *.* to 'm1slave'@'172.16.160.37' identified by 'password';

The operation of Master-2 is as follows:

grant replication slave on *.* to 'm2slave'@'172.16.160.35' identified by 'password';

Be careful:

The IP address in the above SQL statement is the setting of who accesses the local machine, not the local IP address
If any IP can be accessed by this user, just change the IP address to the percentage sign (%)

4. View the status of Master library

Execute the following commands on two nodes respectively:

show master status;

The results are as follows:
Master-1:

Master-2:

5. Configure synchronization data

The operation of Master-1 is as follows:

change master to master_host='192.169.7.242', master_port=3306, master_user='root', master_password='123456', master_log_file='mysql-bin.000003', master_log_pos=154;

The operation of Master-2 is as follows:

change master to master_host='192.169.0.197', master_port=3306, master_user='root', master_password='123456', master_log_file='mysql-bin.000003', master_log_pos=466;

Be careful:

The configuration of master log file and master log POS is based on the data queried in step 4
The configuration of master user and master password is to add the account and password of the role in step 3

6. Run the Slave Library

Execute the following commands on two nodes respectively: