How MySQL master-slave synchronization architecture is built

Catalog

I. Preface

This article will guide you through building all MySQL master-slave synchronization architecture schemes:

  • Primary Multi-slave Architecture
  • Primary two-way synchronization architecture
  • Level 3 M-S-S Cascade Synchronization Architecture
  • Multiple Master and Multiple Slave Architecture

2. About MySQL Master-Slave Synchronization

MySQL master-slave synchronization is the basis for building large, high-performance applications. MySQL master-slave synchronization enables query execution from the slave server (that is, what we often call read function), reduces the pressure on the master server (write from the master, read from the slave, reduce pressure), backups from the master server, and avoids impacting the master server service (ensure data security) during backups.When the primary server has a problem, you can switch to the slave server (to improve performance).

3. Deployment Planning

3.1 Server Planning

The server Operating System Version CPU Architecture MySQL Version
node6 CentOS Linux release 7.4.1708 x86_64 5.7.26
node7 CentOS Linux release 7.4.1708 x86_64 5.7.26
node8 CentOS Linux release 7.4.1708 x86_64 5.7.26
node9 CentOS Linux release 7.4.1708 x86_64 5.7.26

3.2 Database Catalog Planning

file type File Deployment Location
Data directory datadir /data/data (/data directory make sure it is large enough)
configuration fileMy.cnf /etc/my.cnf
Error log-error /data/log/mysql_error.log
Binary log-bin /data/binlogs/mysql-bin (for database recovery and master-slave replication, and audit operations)
Slow Query Log slow_query_log_file /data/log/mysql_slow_query.log
Socket file socket /data/run/mysql.sock
Process ID fileMysql.pid /data/run/mysql.pid

4. Preparation Tools

1.MySQL Universal Binary Package: mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz

Download address: https://dev.mysql.com/downloads/mysql/5.7.html#downloads

5.4 Use Universal Binary Package to install MySQL on four machines (node7 as an example)

5.1 Upload MySQL Universal Binary Installation Package to Noe7/usr/local/src directory

[root@node7 src]# pwd
/usr/local/src
[root@node7 src]# ls
mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz

5.2 Unzip MySQL to the specified directory and rename it

[root@node7 src]# tar -zxf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@node7 src]# cd /usr/local/
[root@node7 local]# ls
bin  etc  games  include  lib  lib64  libexec  mysql-5.7.26-linux-glibc2.12-x86_64  sbin  share  src
[root@node7 local]# mv mysql-5.7.26-linux-glibc2.12-x86_64 mysql
[root@node7 local]# ls
bin  etc  games  include  lib  lib64  libexec  mysql  sbin  share  src

5.3 Create MySQL users and user groups

[root@node7 local]# groupadd -g 1111 mysql
[root@node7 local]# useradd -g mysql -u 1111 -s /sbin/nologin mysql
[root@node7 local]# id mysql    #View user information
uid=1111(mysql) gid=1111(mysql) groups=1111(mysql)

5.4 Configure MySQL bin directory to PATH path

[root@node7 local]# echo "export PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
[root@node7 local]# source /etc/profile
[root@node7 local]# mysql    #After entering MySQL, double-click the tab key to list the candidate MySQL commands
mysql                       mysql_client_test_embedded  mysqld-debug                mysqldumpslow               mysql_plugin                mysqlslap                   mysql_upgrade
mysqladmin                  mysql_config                mysqld_multi                mysql_embedded              mysqlpump                   mysql_ssl_rsa_setup         mysqlxtest
mysqlbinlog                 mysql_config_editor         mysqld_safe                 mysqlimport                 mysql_secure_installation   mysqltest_embedded          
mysqlcheck                  mysqld                      mysqldump                   mysql_install_db            mysqlshow                   mysql_tzinfo_to_sql

5.5 Create MySQL Data Storage Directory

[root@node7 ~]# mkdir -p /data/{data,log,binlogs,run}
[root@node7 ~]# tree /data    #If there is no tree command, then yum-y install tree installation
/data
├── binlogs
├── data
├── log
└── run

4 directories, 0 files
[root@node7 ~]# chown -R mysql:mysql /data
[root@node7 ~]# ll /data/
total 0
drwxr-xr-x 2 mysql mysql 6 Dec  3 11:07 binlogs
drwxr-xr-x 2 mysql mysql 6 Dec  3 11:07 data
drwxr-xr-x 2 mysql mysql 6 Dec  3 11:07 log
drwxr-xr-x 2 mysql mysql 6 Dec  3 11:07 run

5.6 Configure MySQL Profile

[root@node7 mysql]# rm -rf /etc/my.cnf
[root@node7 mysql]# touch /etc/my.cnf
#my.cnf Profile details, please check my previous article blog Of#https://www.cnblogs.com/renshengdezheli/p/11913248.html MySQL Profile Optimized Reference for
[root@node7 mysql]# cat /etc/my.cnf
[client]
port=3306
socket=/data/run/mysql.sock

[mysqld]
port=3306
socket=/data/run/mysql.sock
pid_file=/data/run/mysql.pid
datadir=/data/data
default_storage_engine=InnoDB
max_allowed_packet=512M
max_connections=2048
open_files_limit=65535

skip-name-resolve
lower_case_table_names=1

character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'

innodb_buffer_pool_size=1024M
innodb_log_file_size=2048M
innodb_file_per_table=1
innodb_flush_log_at_trx_commit=0

key_buffer_size=64M

log-error=/data/log/mysql_error.log
log-bin=/data/binlogs/mysql-bin
slow_query_log=1
slow_query_log_file=/data/log/mysql_slow_query.log
long_query_time=5

tmp_table_size=32M
max_heap_table_size=32M
query_cache_type=0
query_cache_size=0

server-id=1

5.7 Initialize MySQL database

[root@node7 mysql]# mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/data
[root@node7 mysql]# echo $?
0
[root@node7 mysql]# grep 'temporary password' /data/log/mysql_error.log    #View MySQL Initialization Password
2019-12-03T03:47:42.639938Z 1 [Note] A temporary password is generated for root@localhost: lhrh>J,p<8gw

5.8 Generate ssl (optional)

#About MySQL Open ssl ViewHttps://www.cnblogs.com/mysql-dba/p/7061300.html
[root@node7 mysql]# mysql_ssl_rsa_setup --basedir=/usr/local/mysql --datadir=/data/data
Generating a 2048 bit RSA private key
......................................+++
.+++
writing new private key to 'ca-key.pem'
-----
Generating a 2048 bit RSA private key
....................................+++
............................+++
writing new private key to 'server-key.pem'
-----
Generating a 2048 bit RSA private key
.....................................................................................+++
..............................................+++
writing new private key to 'client-key.pem'
-----
#After execution, a *.pem file is generated in the datadir directory
[root@node7 mysql]# ls /data/data/
auto.cnf    client-cert.pem  ibdata1      mysql               public_key.pem   sys
ca-key.pem  client-key.pem   ib_logfile0  performance_schema  server-cert.pem
ca.pem      ib_buffer_pool   ib_logfile1  private_key.pem     server-key.pem

5.9 Configure MySQL startup item and set startup self-startup

Version 5.9.1 centos6

cd /usr/local/mysql
cp support-files/mysql.server /etc/init.d/mysql.server
chkconfig --add mysql.server
chkconfig  mysql.server on
chkconfig --list

Version 5.9.2 centos7

[root@node7 system]# cd /usr/lib/systemd/system
[root@node7 system]# touch mysqld.service 
[root@node7 system]# vim mysqld.service 
[root@node7 system]# cat mysqld.service 
# Copyright (c) 2015, 2016, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA
#
# systemd service file for MySQL forking server
#

[Unit]
Description=MySQL Server
Documentation=man:mysqld(5.7)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql

Type=forking

PIDFile=/data/run/mysql.pid

# Disable service start and stop timeout logic of systemd for mysqld service.
TimeoutSec=0

# Execute pre and post scripts as root
PermissionsStartOnly=true

# Needed to create system tables
#ExecStartPre=/usr/bin/mysqld_pre_systemd

# Start main service
ExecStart=/usr/local/mysql/bin/mysqld --daemonize --pid-file=/data/run/mysql.pid $MYSQLD_OPTS

# Use this to switch malloc implementation
EnvironmentFile=-/etc/sysconfig/mysql

# Sets open_files_limit
LimitNOFILE = 65535

Restart=on-failure

RestartPreventExitStatus=1

PrivateTmp=false

[root@node7 system]# systemctl daemon-reload    #Reload Service Profile
[root@node7 system]# systemctl enable mysqld    #Set MySQL Start-Up Self-Start
Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /usr/lib/systemd/system/mysqld.service.
[root@node7 system]# systemctl is-enabled mysqld   #Check if MySQL boot-up self-start is set successfully
enabled

5.10 Start MySQL

[root@node7 system]# systemctl start mysqld
[root@node7 system]# systemctl status mysqld    #View MySQL startup status
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Tue 2019-12-03 14:42:14 CST; 9s ago
     Docs: man:mysqld(5.7)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 2905 ExecStart=/usr/local/mysql/bin/mysqld --daemonize --pid-file=/data/run/mysql.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
 Main PID: 2907 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─2907 /usr/local/mysql/bin/mysqld --daemonize --pid-file=/data/run/mysql.pid

Dec 03 14:42:13 node7 systemd[1]: Starting MySQL Server...
Dec 03 14:42:14 node7 systemd[1]: Started MySQL Server.
[root@node7 system]# ps -ef | grep mysql         #View MySQL processes
mysql      2907      1  2 14:42 ?        00:00:00 /usr/local/mysql/bin/mysqld --daemonize --pid-file=/data/run/mysql.pid
root       2942   2576  0 14:42 pts/0    00:00:00 grep --color=auto mysql

5.11 MySQL security initialization (optional)

[root@node7 system]# mysql_secure_installation 

Securing the MySQL server deployment.

Enter password for user root:    #Here you enter the password generated when MySQL is initialized (grep'temporary password'/data/log/mysql_Error.log)

The existing password for the user account root has expired. Please set a new password.

New password:   #Enter a new password

Re-enter new password: 

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No: n   #y Install MySQL password plug-in
Using existing password for root.
Change the password for root ? ((Press y|Y for Yes, any other key for No) : n

 ... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y  #y Remove anonymous users
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : n  #Whether root remote login is allowed

 ... skipping.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y  #Whether to remove the test database
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y  #Refresh Permission Table
Success.

All done!

5.12 Modify the password and grant the user permissions (according to their own circumstances)

[root@node7 ~]# mysql -uroot -p123456

Mysql> SET PASSWORD = PASSWORD ('123456'); #Modify the root password to 123 456, if ERROR 1819 (HY000) is prompted: Your password does not satisfy the current policy requirements, then the password setting is too simple, if you want to set a simple password like 123456, you can execute it in SQL:
	#mysql> set global validate_password_policy=0;
	#mysql> set global validate_password_length=1;
	#This will allow SET PASSWORD = PASSWORD('123456') to be executed again.
Query OK, 0 rows affected, 1 warning (0.01 sec)

MySQL > UPDATEMysql.userSET authentication_String =PASSWORD ('123456') WHERE User='mysql'; #Password of MySQL user who modified MySQL is 123 456
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 1

mysql> GRANT ALL PRIVILEGES ON *.* TO mysql@localhost IDENTIFIED BY '123456' WITH GRANT OPTION;   
Query OK, 0 rows affected, 2 warnings (0.00 sec)

Mysql> GRANT ALL PRIVILEGES ON *. * TO mysql@'%'IDENTIFIED BY'123456' WITH GRANT OPTION; #Give MySQL users the ability to log on to any machine and have all privileges on all tables
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY '123456' WITH GRANT OPTION;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY '123456' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.07 sec)

Mysql> FLUSH PRIVILEGES; #Refresh permissions for changes to take effect immediately
Query OK, 0 rows affected (0.00 sec)

mysql> exit;
Bye

--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
#Here's an introduction to granting permissions to MySQL
 MySQL > grant permission 1, permission 2,...Privilege n on database name.table name to user name@user address identified by'connection password';
Permission 1, Permission 2,...Permission n represents 14 permissions, such as select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file, etc.
When Permission 1, Permission 2,...Permission n is replaced by all privileges or all, meaning that the user is given full permission.
When the database name. table name is replaced by *. * it gives the user permission to operate all tables in all databases on the server.
The user address can be a localhost or an ip address, machine name, or domain name.You can also use'%'to indicate a connection from any address.
The'Connection Password'cannot be empty or the creation will fail.
For example:
mysql>grant select,insert,update,delete,create,drop on vtdc.employee to joe@10.163.225.87 identified by '123′;
Assign user joe from 10.163.225.87 permissions to select,insert,update,delete,create,drop the employee table of the vtdc database and set the password to 123.
 
mysql>grant all privileges on vtdc.* to joe@10.163.225.87 identified by '123′;
Assign user joe from 10.163.225.87 permissions to perform all operations on all tables of the vtdc database with a password of 123.
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------

5.13 Import time zone information to the MySQL Library

[root@node7 system]# mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -uroot -p123456 mysql
#After doing the above, time_zone, time_zone_leap_second, time_zone_name, time_zone_transition, time_zone_transition_The type table has time zone data
[root@node7 system]# mysql -uroot -p123456 mysql
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
31 rows in set (0.00 sec)

5.14 View MySQL version information

[root@node7 system]# mysql -V
mysql  Ver 14.14 Distrib 5.7.26, for linux-glibc2.12 (x86_64) using  EditLine wrapper
[root@node7 system]# mysqladmin version -uroot -p123456
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
mysqladmin  Ver 8.42 Distrib 5.7.26, for linux-glibc2.12 on x86_64
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version		5.7.26-log
Protocol version	10
Connection		Localhost via UNIX socket
UNIX socket		/data/run/mysql.sock
Uptime:			31 min 53 sec

Threads: 1  Questions: 8855  Slow queries: 0  Opens: 214  Flush tables: 1  Open tables: 203  Queries per second avg: 4.628

5.15 If the firewall is open, port 3306 needs to be opened

[root@node7 system]# systemctl status firewalld
● firewalld.service - firewalld - dynamic firewall daemon
   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
   Active: active (running) since Tue 2019-12-03 15:22:18 CST; 3s ago
     Docs: man:firewalld(1)
 Main PID: 3343 (firewalld)
   CGroup: /system.slice/firewalld.service
           └─3343 /usr/bin/python -Es /usr/sbin/firewalld --nofork --nopid

Dec 03 15:22:17 node7 systemd[1]: Starting firewalld - dynamic firewall daemon...
Dec 03 15:22:18 node7 systemd[1]: Started firewalld - dynamic firewall daemon.
Dec 03 15:22:18 node7 firewalld[3343]: WARNING: ICMP type 'beyond-scope' is not supported by the kernel for ipv6.
Dec 03 15:22:18 node7 firewalld[3343]: WARNING: beyond-scope: INVALID_ICMPTYPE: No supported ICMP type., ignoring...-time.
Dec 03 15:22:18 node7 firewalld[3343]: WARNING: ICMP type 'failed-policy' is not supported by the kernel for ipv6.
Dec 03 15:22:18 node7 firewalld[3343]: WARNING: failed-policy: INVALID_ICMPTYPE: No supported ICMP type., ignorin...-time.
Dec 03 15:22:18 node7 firewalld[3343]: WARNING: ICMP type 'reject-route' is not supported by the kernel for ipv6.
Dec 03 15:22:18 node7 firewalld[3343]: WARNING: reject-route: INVALID_ICMPTYPE: No supported ICMP type., ignoring...-time.
Hint: Some lines were ellipsized, use -l to show in full.
#Add Firewall Rules
[root@node7 system]# firewall-cmd --permanent --zone=public --add-port=3306/tcp
success
#Reload Firewall Rules
[root@node7 system]# firewall-cmd --reload
success
#Check if the rule settings are valid
[root@node7 system]# firewall-cmd --zone=public --query-port=3306/tcp
yes
#List all open ports of the firewall
[root@node7 system]# firewall-cmd --list-all
public (active)
  target: default
  icmp-block-inversion: no
  interfaces: ens33
  sources: 
  services: ssh dhcpv6-client
  ports: 3306/tcp
  protocols: 
  masquerade: no
  forward-ports: 
  source-ports: 
  icmp-blocks: 
  rich rules: 

5.16 Use logrotate to rotate MySQL logs (log automatic backup cutting)

#For detailed logrotate configuration, see:https://www.linuxidc.com/Linux/2019-02/157099.htm
[root@node7 ~]# touch /root/.my.cnf
[root@node7 ~]# vim /root/.my.cnf 
[root@node7 ~]# cat /root/.my.cnf 
[mysqladmin]  
password=123456
user=root
[root@node7 ~]# chmod 600 /root/.my.cnf 
[root@node7 ~]# cp /usr/local/mysql/support-files/mysql-log-rotate /etc/logrotate.d/
[root@node7 ~]# chmod 644 /etc/logrotate.d/mysql-log-rotate 
[root@node7 ~]# vim /etc/logrotate.d/mysql-log-rotate 
[root@node7 ~]# cat /etc/logrotate.d/mysql-log-rotate 
# The log file name and location can be set in
# /etc/my.cnf by setting the "log-error" option
# in either [mysqld] or [mysqld_safe] section as
# follows:
#
# [mysqld]
# log-error=/usr/local/mysql/data/mysqld.log
#
# In case the root user has a password, then you
# have to create a /root/.my.cnf configuration file
# with the following content:
#
# [mysqladmin]
# password = <secret> 
# user= root
#
# where "<secret>" is the password. 
#
# ATTENTION: The /root/.my.cnf file should be readable
# _ONLY_ by root !

/data/log/mysql_*.log {
        # create 600 mysql mysql
        notifempty  #Do not rotate when log file is empty
        daily  #Perform rotate rotation once a day by default
        rotate 52  #How many log files to keep (rotate a few times). Four are reserved by default. This is the number of times to rotate before deleting the log file. 0 is no backup. This means to keep the log for 52 days.
        missingok   #Do not show errors if log files are missing
        compress    #Dump future logs with gzip compression
    postrotate   #Executed instructions
	# just if mysqld is really running
	if test -x /usr/local/mysql/bin/mysqladmin && \
	   /usr/local/mysql/bin/mysqladmin ping &>/dev/null
	then
	   /usr/local/mysql/bin/mysqladmin flush-logs
	fi
    endscript
}
[root@node7 ~]# 
[root@node7 ~]# logrotate -fv /etc/logrotate.d/mysql-log-rotate #Force log rotation
reading config file /etc/logrotate.d/mysql-log-rotate
Allocating hash table for state file, size 15360 B

Handling 1 logs

rotating pattern: /data/log/mysql_*.log  forced from command line (52 rotations)
empty log files are not rotated, old logs are removed
considering log /data/log/mysql_error.log
  log needs rotating
considering log /data/log/mysql_slow_query.log
  log needs rotating
rotating log /data/log/mysql_error.log, log->rotateCount is 52
dateext suffix '-20191203'
glob pattern '-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
renaming /data/log/mysql_error.log.52.gz to /data/log/mysql_error.log.53.gz 
(t -- won't try to dispose of it
.................
renaming /data/log/mysql_slow_query.log to /data/log/mysql_slow_query.log.1
running postrotate script
compressing log with: /bin/gzip
[root@node7 ~]# 
[root@node7 ~]# echo $?
0
#Looking at the log directory at this point, you can see that the log has been rotated and compressed
[root@node7 ~]# ls /data/log/
mysql_error.log  mysql_error.log.1.gz  mysql_slow_query.log  mysql_slow_query.log.1.gz

Since then, MySQL has been installed on node7, as has MySQL on node6, node8, and node9.

Installation of MySQL is the basis for master-slave synchronization, read-write separation, and table-based library configuration. Only installation of MySQL can proceed to the next step.

6. MySQL Master-Slave Synchronization One Master-Slave Architecture

6.1 Server Planning

host name IP Operating System Version MySQL Version role
node7 192.168.110.188 CentOS 7.4.1708 5.7.26 master
node8 192.168.110.186 CentOS 7.4.1708 5.7.26 slave (from)
node9 192.168.110.187 CentOS 7.4.1708 5.7.26 slave (from)

6.2 Principle of Master-Slave Synchronization

The master records the changes in the binary log, slave copies the master's binary log events to its relay log, slave redoes the events in the relay log, and modifies the data on salve.

6.3 Deploy one of MySQL master-slave synchronizations Master-slave

6.3.1 Configure master database server node7

6.3.1.1 Create databases and their tables that need to be synchronized

[root@node7 ~]# mysql -uroot -p123456

mysql> create database hotdata;     #Create Hot Spot Database
Query OK, 1 row affected (0.70 sec)

mysql> use hotdata;
Database changed
#Create Customer Table
mysql> create table customers(cust_id int,cust_name varchar(30),cust_address varchar(50),cust_city varchar(30),cust_state varchar(50),cust_email varchar(30),cust_country varchar(50));
Query OK, 0 rows affected (0.44 sec)

mysql> desc customers;   #View table structure
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| cust_id      | int(11)     | YES  |     | NULL    |       |
| cust_name    | varchar(30) | YES  |     | NULL    |       |
| cust_address | varchar(50) | YES  |     | NULL    |       |
| cust_city    | varchar(30) | YES  |     | NULL    |       |
| cust_state   | varchar(50) | YES  |     | NULL    |       |
| cust_email   | varchar(30) | YES  |     | NULL    |       |
| cust_country | varchar(50) | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
7 rows in set (0.36 sec)

mysql> exit
Bye

6.3.1.2 Modify MySQL configuration file

#Close the database before modifying the MySQL configuration file
[root@node7 ~]# systemctl stop mysqld
[root@node7 ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: inactive (dead) since Thu 2019-12-05 10:59:38 CST; 8s ago
     Docs: man:mysqld(5.7)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 6777 ExecStart=/usr/local/mysql/bin/mysqld --daemonize --pid-file=/data/run/mysql.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
 Main PID: 6779 (code=exited, status=0/SUCCESS)

Dec 05 10:35:44 node7 systemd[1]: Starting MySQL Server...
Dec 05 10:36:07 node7 systemd[1]: Started MySQL Server.
Dec 05 10:59:36 node7 systemd[1]: Stopping MySQL Server...
Dec 05 10:59:38 node7 systemd[1]: Stopped MySQL Server.

#The modified configuration file is as follows, and the master-slave synchronization related configurations are all placed in "#Under mysql replication
[root@node7 ~]# vim /etc/my.cnf
[root@node7 ~]# cat /etc/my.cnf
[client]
port=3306
socket=/data/run/mysql.sock

[mysqld]
port=3306
socket=/data/run/mysql.sock
pid_file=/data/run/mysql.pid
datadir=/data/data
default_storage_engine=InnoDB
max_allowed_packet=512M
max_connections=2048
open_files_limit=65535

skip-name-resolve
lower_case_table_names=1

character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'

innodb_buffer_pool_size=1024M
innodb_log_file_size=2048M
innodb_file_per_table=1
innodb_flush_log_at_trx_commit=0

key_buffer_size=64M

log-error=/data/log/mysql_error.log

slow_query_log=1
slow_query_log_file=/data/log/mysql_slow_query.log
long_query_time=5

tmp_table_size=32M
max_heap_table_size=32M
query_cache_type=0
query_cache_size=0


skip_ssl

#mysql replication, master-slave synchronization configuration
#The logbin parameter enables binary logging and places the binary logging in the / data/binlogs directory
log-bin=/data/binlogs/mysql-bin  
#Database flag ID, unique
server-id=1
#Libraries that binlog-do-db can be copied from the server
binlog-do-db=hotdata
#Libraries that binlog-ignore-db cannot be copied from the server
binlog-ignore-db=mysql

[root@node7 ~]# systemctl restart mysqld   #Restart MySQL database

6.3.1.3 Primary libraries grant replication privileges to sublibraries

[root@node7 ~]# mysql -uroot -p123456

#Grant node8 replication privileges from the library
mysql> grant replication slave on *.* to slave@192.168.110.186 identified by "123456";
Query OK, 0 rows affected, 1 warning (0.11 sec)

#Grant node9 replication privileges from the library
mysql> grant replication slave on *.* to slave@192.168.110.187 identified by "123456";
Query OK, 0 rows affected, 1 warning (0.01 sec)

#Refresh Permissions
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

#View master status information
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000012 |      902 | hotdata      | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> exit
Bye

#View binary logs
[root@node7 ~]# ll /data/binlogs/
total 2896
-rw-r----- 1 mysql mysql     177 Dec  3 11:47 mysql-bin.000001
-rw-r----- 1 mysql mysql 2915818 Dec  3 16:38 mysql-bin.000002
-rw-r----- 1 mysql mysql     201 Dec  3 16:38 mysql-bin.000003
-rw-r----- 1 mysql mysql     177 Dec  3 17:09 mysql-bin.000004
-rw-r----- 1 mysql mysql     177 Dec  3 17:14 mysql-bin.000005
-rw-r----- 1 mysql mysql     177 Dec  3 17:25 mysql-bin.000006
-rw-r----- 1 mysql mysql    1220 Dec  4 03:12 mysql-bin.000007
-rw-r----- 1 mysql mysql     201 Dec  4 03:12 mysql-bin.000008
-rw-r----- 1 mysql mysql     177 Dec  4 10:49 mysql-bin.000009
-rw-r----- 1 mysql mysql    1743 Dec  5 10:35 mysql-bin.000010
-rw-r----- 1 mysql mysql     665 Dec  5 10:59 mysql-bin.000011
-rw-r----- 1 mysql mysql     902 Dec  5 11:47 mysql-bin.000012
-rw-r----- 1 mysql mysql     372 Dec  5 11:40 mysql-bin.index
[root@node7 ~]# mysql -uroot -p123456

##View Binary Log Events
mysql> show binlog events\G
*************************** 1. row ***************************
   Log_name: mysql-bin.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 123
       Info: Server ver: 5.7.26-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: mysql-bin.000001
        Pos: 123
 Event_type: Previous_gtids
  Server_id: 1
End_log_pos: 154
       Info: 
*************************** 3. row ***************************
   Log_name: mysql-bin.000001
        Pos: 154
 Event_type: Stop
  Server_id: 1
End_log_pos: 177
       Info: 
3 rows in set (0.00 sec)

mysql> exit
Bye

6.3.1.4 Backup of database hotdata that needs to be synchronized from the database

#Backup database hotdata
[root@node7 ~]# mysqldump -uroot -p123456 hotdata >hotdata.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
#Distribute backed up databases from libraries
[root@node7 ~]# scp hotdata.sql root@192.168.110.186:~/
hotdata.sql                                        100% 2239   510.9KB/s   00:00    
[root@node7 ~]# scp hotdata.sql root@192.168.110.187:~/
hotdata.sql                                        100% 2239   382.8KB/s   00:00    

6.3.2 Configuration from database server node8

6.3.2.1 Check database version

#Problems can occur if master-slave database versions are inconsistent
[root@node8 ~]# mysql -uroot -p123456
mysql> show variables like "%version%";
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.7.26                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| tls_version             | TLSv1,TLSv1.1                |
| version                 | 5.7.26-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | linux-glibc2.12              |
+-------------------------+------------------------------+
8 rows in set (0.01 sec)

mysql> quit
Bye

6.3.2.2 Test whether the connection to the primary server was successful

[root@node8 ~]# mysql -uslave -p123456 -h 192.168.110.188
#You cannot see other libraries unless you have permission to copy.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

mysql> exit
Bye

6.3.2.3 Import the hotdata database, consistent with the master database

[root@node8 ~]# mysql -uroot -p123456
mysql> create database hotdata;
Query OK, 1 row affected (0.00 sec)

mysql> exit
Bye

#Import the hotdata table
[root@node8 ~]# mysql -uroot -p123456 hotdata<hotdata.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.

6.3.2.4 Modify Profile

[root@node8 ~]# systemctl stop mysqld
[root@node8 ~]# vim /etc/my.cnf
[root@node8 ~]# cat /etc/my.cnf
[client]
port=3306
socket=/data/run/mysql.sock

[mysqld]
port=3306
socket=/data/run/mysql.sock
pid_file=/data/run/mysql.pid
datadir=/data/data
default_storage_engine=InnoDB
max_allowed_packet=512M
max_connections=2048
open_files_limit=65535

skip-name-resolve
lower_case_table_names=1

character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'

innodb_buffer_pool_size=1024M
innodb_log_file_size=2048M
innodb_file_per_table=1
innodb_flush_log_at_trx_commit=0

key_buffer_size=64M

log-error=/data/log/mysql_error.log

slow_query_log=1
slow_query_log_file=/data/log/mysql_slow_query.log
long_query_time=5

tmp_table_size=32M
max_heap_table_size=32M
query_cache_type=0
query_cache_size=0

#mysql replication configuration
#server-id must be unique
server-id=2
#Neither of the following log-bin, bin log-do-db, bin log-ignore-db parameters is required
log-bin=/data/binlogs/mysql-bin
binlog-do-db=hotdata
binlog-ignore-db=mysql
[root@node8 ~]# 
[root@node8 ~]# systemctl restart mysqld

6.3.2.5 Set slave from library to copy master library data

[root@node8 ~]# mysql -uroot -p123456
mysql> stop slave;    #Stop slave
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> change master to master_host='192.168.110.188',master_user='slave',master_password='123456';
Query OK, 0 rows affected, 2 warnings (0.02 sec)

#Interpretation:
#change master to #master_host='192.168.0.68',master_user='root',master_password='root',master_log_file='#mysql-bin.000004', master_log_pos=28125;
#Above master_log_file Yes Master in show master status Displayed File,and master_log_pos Yes Master in#Position shown by show master status.
#You can also use the show slave status View configuration information,If synchronization fails, compare show slave status In position and#Does the file correspond to the show master status?

mysql> start slave;   #start slave
Query OK, 0 rows affected (0.01 sec)
#View slave status
mysql> show slave status\G     
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.110.188
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000012
          Read_Master_Log_Pos: 902
               Relay_Log_File: node8-relay-bin.000010
                Relay_Log_Pos: 519
        Relay_Master_Log_File: mysql-bin.000007
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1062
                   Last_Error: Could not execute Update_rows event on table mysql.user; Duplicate entry '%-root' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000007, end_log_pos 942
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 306
              Relay_Log_Space: 7216
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1062
               Last_SQL_Error: Could not execute Update_rows event on table mysql.user; Duplicate entry '%-root' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000007, end_log_pos 942
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: a8da7421-157f-11ea-b1bf-000c297c0226
             Master_Info_File: /data/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 191205 15:18:40
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

#You can see Last_Error has reported an error because the primary key is repeated. Just follow these steps
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL sql_slave_skip_counter =1;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

#View again slave Status if Last_Error No error, and Slave_IO_Running and Slave_SQL_Running All for yes Explanation#Configuration succeeded
#Slave_IO_Running: Responsible for IO communication with host
#Slave_SQL_Running: Responsible for your own slave mysql process
#If executed stop slave,SET GLOBAL sql_slave_skip_counter =1,start slave After that, show slave #status\G Error or error, execute again stop slave,SET GLOBAL sql_slave_skip_counter =1,start #slave can be executed up to three times to eliminate all errors.
mysql> show slave status\G   
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.110.188
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000013
          Read_Master_Log_Pos: 154
               Relay_Log_File: node8-relay-bin.000037
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000013
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 693
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: a8da7421-157f-11ea-b1bf-000c297c0226
             Master_Info_File: /data/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

#Looking at the data directory, you can see Relay_Log_File
[root@node8 ~]# ls /data/data/
auto.cnf        ibdata1      ibtmp1       node8-relay-bin.000036  performance_schema
hotdata         ib_logfile0  master.info  node8-relay-bin.000037  relay-log.info
ib_buffer_pool  ib_logfile1  mysql        node8-relay-bin.index   sys

6.3.3 Configuration from database server node9

The configuration of node9 is the same as that of node8. Note the configuration fileMy.cnfServer-id must be unique inside, not the same as node7,node8.

6.3.4 View status on primary server

[root@node7 ~]# mysql -uroot -p123456
#You can see two slave s
mysql> show processlist\G
*************************** 1. row ***************************
     Id: 8
   User: slave
   Host: 192.168.110.186:49414
     db: NULL
Command: Binlog Dump
   Time: 4313
  State: Master has sent all binlog to slave; waiting for more updates
   Info: NULL
*************************** 2. row ***************************
     Id: 10
   User: slave
   Host: 192.168.110.187:33510
     db: NULL
Command: Binlog Dump
   Time: 4208
  State: Master has sent all binlog to slave; waiting for more updates
   Info: NULL
*************************** 3. row ***************************
     Id: 11
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: starting
   Info: show processlist
3 rows in set (0.00 sec)

6.3.5 Insert Data Test Master-Slave Synchronization

#Insert data on primary server
mysql> use hotdata;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> insert into customers values (1,'Zhang San','Pearl River New City','Guangzhou','Guangdong Province','1234567890@qq.com','china');
Query OK, 1 row affected (0.10 sec)

mysql> insert into customers values (2,'Li Si','Tiananmen','Beijing','Beijing','1234127890@qq.com','china');
Query OK, 1 row affected (0.04 sec)

mysql> insert into customers values (3,'May you stay forever young','bell tower and drum tower','Kunming','Yunnan Province','1234567870@qq.com','china');
Query OK, 1 row affected (0.01 sec)

mysql> insert into customers values (4,'Four of Zhao','Campo dei fiori','Foshan','Guangdong Province','1239867890@qq.com','china');
Query OK, 1 row affected (0.00 sec)

mysql> insert into customers values (5,'Lennon','sports center','Guangzhou','Guangdong Province','1234512890@qq.com','china');
Query OK, 1 row affected (0.00 sec)

mysql> insert into customers values (6,'Gunn Quayle Tse','Sports West Road','Guangzhou','Guangdong Province','1364567890@qq.com','china');
Query OK, 1 row affected (0.00 sec)

mysql> select * from customers;   #View data
+---------+--------------+--------------+-----------+------------+-------------------+--------------+
| cust_id | cust_name    | cust_address | cust_city | cust_state | cust_email        | cust_country |
+---------+--------------+--------------+-----------+------------+-------------------+--------------+
|       1 | Zhang San         | Pearl River New City     | Guangzhou      | Guangdong Province     | 1234567890@qq.com | china        |
|       2 | Li Si         | Tiananmen       | Beijing      | Beijing     | 1234127890@qq.com | china        |
|       3 | May you stay forever young     | bell tower and drum tower       | Kunming      | Yunnan Province     | 1234567870@qq.com | china        |
|       4 | Four of Zhao         | Campo dei fiori     | Foshan      | Guangdong Province     | 1239867890@qq.com | china        |
|       5 | Lennon         | sports center     | Guangzhou      | Guangdong Province     | 1234512890@qq.com | china        |
|       6 | Gunn Quayle Tse       | Sports West Road     | Guangzhou      | Guangdong Province     | 1364567890@qq.com | china        |
+---------+--------------+--------------+-----------+------------+-------------------+--------------+
6 rows in set (0.01 sec)

mysql> exit
Bye

#View data on two slave s
[root@node8 ~]# mysql -uroot -p123456
#Looking at the data on node8, we found that it was synchronized
mysql> select * from hotdata.customers;
+---------+--------------+--------------+-----------+------------+-------------------+--------------+
| cust_id | cust_name    | cust_address | cust_city | cust_state | cust_email        | cust_country |
+---------+--------------+--------------+-----------+------------+-------------------+--------------+
|       1 | Zhang San         | Pearl River New City     | Guangzhou      | Guangdong Province     | 1234567890@qq.com | china        |
|       2 | Li Si         | Tiananmen       | Beijing      | Beijing     | 1234127890@qq.com | china        |
|       3 | May you stay forever young     | bell tower and drum tower       | Kunming      | Yunnan Province     | 1234567870@qq.com | china        |
|       4 | Four of Zhao         | Campo dei fiori     | Foshan      | Guangdong Province     | 1239867890@qq.com | china        |
|       5 | Lennon         | sports center     | Guangzhou      | Guangdong Province     | 1234512890@qq.com | china        |
|       6 | Gunn Quayle Tse       | Sports West Road     | Guangzhou      | Guangdong Province     | 1364567890@qq.com | china        |
+---------+--------------+--------------+-----------+------------+-------------------+--------------+
6 rows in set (0.00 sec)

mysql> exit
Bye


[root@node9 ~]# mysql -uroot -p123456
#Looking at the data on node9, we found that it was synchronized
mysql> select * from hotdata.customers;
+---------+--------------+--------------+-----------+------------+-------------------+--------------+
| cust_id | cust_name    | cust_address | cust_city | cust_state | cust_email        | cust_country |
+---------+--------------+--------------+-----------+------------+-------------------+--------------+
|       1 | Zhang San         | Pearl River New City     | Guangzhou      | Guangdong Province     | 1234567890@qq.com | china        |
|       2 | Li Si         | Tiananmen       | Beijing      | Beijing     | 1234127890@qq.com | china        |
|       3 | May you stay forever young     | bell tower and drum tower       | Kunming      | Yunnan Province     | 1234567870@qq.com | china        |
|       4 | Four of Zhao         | Campo dei fiori     | Foshan      | Guangdong Province     | 1239867890@qq.com | china        |
|       5 | Lennon         | sports center     | Guangzhou      | Guangdong Province     | 1234512890@qq.com | china        |
|       6 | Gunn Quayle Tse       | Sports West Road     | Guangzhou      | Guangdong Province     | 1364567890@qq.com | china        |
+---------+--------------+--------------+-----------+------------+-------------------+--------------+
6 rows in set (0.00 sec)

mysql> exit
Bye

Be careful:

  • Master-slave synchronization, adding data to the master database, synchronizing from the database, but adding data from the database, master is not synchronized
  • Since it is master-slave synchronization, if data is deleted from the master, then data from the master will be lost, so it is recommended to make regular backups on the master (mysqldump)

Since then, one of MySQL master-slave synchronization master-slave architectures has been built.

6.3.6 Remove master-slave synchronization completely

Since there are master-slave synchronizations and master-slave synchronizations undone, if there is a need to undo master-slave synchronization, see below.

#Execute on Main Library
#Reset master record information
mysql> reset master;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 154
     Binlog_Do_DB: hotdata
 Binlog_Ignore_DB: mysql
Executed_Gtid_Set: 
1 row in set (0.00 sec)

#Execute on two slave Libraries
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

#Empty from all connections, information records
mysql> reset slave all;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
Empty set (0.00 sec)

You can see that both master and slave libraries have been released from the master-slave relationship. Finally, you can delete the master-slave related configuration from the configuration file.

6.4 Summary

MySQL master-slave synchronization is one of the master-slave architectures. It is generally used for read-write separation. Master is responsible for writing data and other slaves are responsible for reading data. This architecture has the greatest problem of I/O pressure concentration. Multiple synchronizations on Master affect IO

7. MySQL master-slave synchronization master-bidirectional synchronization architecture

7.1 Server Planning

host name IP Operating System Version MySQL Version role
node7 192.168.110.188 CentOS 7.4.1708 5.7.26 Master, slave (master and slave)
node8 192.168.110.186 CentOS 7.4.1708 5.7.26 Master, slave (master and slave)

7.2 Principle of Master-Slave Synchronization

The master records the changes in the binary log, slave copies the master's binary log events to its relay log, slave redoes the events in the relay log, and modifies the data on salve.

7.3 Deploy MySQL Master-Slave Synchronization Primary-Two-Way Synchronization

7.3.1 Configure database server node7

Noe7 has a dual identity, being both the master and the slave of Noe8.

7.3.1.1 Create databases and their tables that need to be synchronized

[root@node7 ~]# mysql -uroot -p123456

mysql> create database hotdata;     #Create Hot Spot Database
Query OK, 1 row affected (0.70 sec)

mysql> use hotdata;
Database changed
#Create Customer Table
mysql> create table customers(cust_id int,cust_name varchar(30),cust_address varchar(50),cust_city varchar(30),cust_state varchar(50),cust_email varchar(30),cust_country varchar(50));
Query OK, 0 rows affected (0.44 sec)

mysql> desc customers;   #View table structure
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| cust_id      | int(11)     | YES  |     | NULL    |       |
| cust_name    | varchar(30) | YES  |     | NULL    |       |
| cust_address | varchar(50) | YES  |     | NULL    |       |
| cust_city    | varchar(30) | YES  |     | NULL    |       |
| cust_state   | varchar(50) | YES  |     | NULL    |       |
| cust_email   | varchar(30) | YES  |     | NULL    |       |
| cust_country | varchar(50) | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
7 rows in set (0.36 sec)

mysql> exit
Bye

7.3.1.2 Modify MySQL configuration file

[root@node7 ~]# vim /etc/my.cnf
#Configurations related to master-slave synchronization are#Below mysql replication
[root@node7 ~]# cat /etc/my.cnf
[client]
port=3306
socket=/data/run/mysql.sock

[mysqld]
port=3306
socket=/data/run/mysql.sock
pid_file=/data/run/mysql.pid
datadir=/data/data
default_storage_engine=InnoDB
max_allowed_packet=512M
max_connections=2048
open_files_limit=65535

skip-name-resolve
lower_case_table_names=1

character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'

innodb_buffer_pool_size=1024M
innodb_log_file_size=2048M
innodb_file_per_table=1
innodb_flush_log_at_trx_commit=0

key_buffer_size=64M

log-error=/data/log/mysql_error.log

slow_query_log=1
slow_query_log_file=/data/log/mysql_slow_query.log
long_query_time=5

tmp_table_size=32M
max_heap_table_size=32M
query_cache_type=0
query_cache_size=0


skip_ssl

#mysql replication
#The logbin parameter enables binary logging and places the binary logging in the / data/binlogs directory
log-bin=/data/binlogs/mysql-bin
#Database flag ID, unique
server-id=1
#Libraries that binlog-do-db can be copied from the server
binlog-do-db=hotdata
#Libraries that binlog-ignore-db cannot be copied from the server
binlog-ignore-db=mysql

7.3.1.3 Grant replication to node8

#Restart MySQL
[root@node7 ~]# systemctl restart mysqld
[root@node7 ~]# mysql -uroot -p123456

#View master status
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 | hotdata      | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

mysql> grant replication slave on *.* to slave@'192.168.110.186' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

#Refresh Permissions
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

7.3.2 Configure database server node8

Noe8 has a dual identity, being both the master and the slave of Noe7.

7.3.2.1 Create databases and their tables that need to be synchronized

[root@node8 ~]# mysql -uroot -p123456

mysql> create database hotdata;     #Create Hot Spot Database
Query OK, 1 row affected (0.70 sec)

mysql> use hotdata;
Database changed
#Create Customer Table
mysql> create table customers(cust_id int,cust_name varchar(30),cust_address varchar(50),cust_city varchar(30),cust_state varchar(50),cust_email varchar(30),cust_country varchar(50));
Query OK, 0 rows affected (0.44 sec)

mysql> desc customers;   #View table structure
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| cust_id      | int(11)     | YES  |     | NULL    |       |
| cust_name    | varchar(30) | YES  |     | NULL    |       |
| cust_address | varchar(50) | YES  |     | NULL    |       |
| cust_city    | varchar(30) | YES  |     | NULL    |       |
| cust_state   | varchar(50) | YES  |     | NULL    |       |
| cust_email   | varchar(30) | YES  |     | NULL    |       |
| cust_country | varchar(50) | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
7 rows in set (0.36 sec)

mysql> exit
Bye

7.3.2.2 Modify MySQL configuration file

[root@node8 ~]# vim /etc/my.cnf
#Master-slave synchronization related configurations are#mysql replication configuration below
[root@node8 ~]# cat /etc/my.cnf
[client]
port=3306
socket=/data/run/mysql.sock

[mysqld]
port=3306
socket=/data/run/mysql.sock
pid_file=/data/run/mysql.pid
datadir=/data/data
default_storage_engine=InnoDB
max_allowed_packet=512M
max_connections=2048
open_files_limit=65535

skip-name-resolve
lower_case_table_names=1

character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'

innodb_buffer_pool_size=1024M
innodb_log_file_size=2048M
innodb_file_per_table=1
innodb_flush_log_at_trx_commit=0

key_buffer_size=64M

log-error=/data/log/mysql_error.log

slow_query_log=1
slow_query_log_file=/data/log/mysql_slow_query.log
long_query_time=5

tmp_table_size=32M
max_heap_table_size=32M
query_cache_type=0
query_cache_size=0

#mysql replication configuration
log-bin=/data/binlogs/mysql-bin
server-id=2
binlog-do-db=hotdata
binlog-ignore-db=mysql
[root@node8 ~]# 
//Restart MySQL
[root@node8 ~]# systemctl restart mysqld

7.3.2.3 Test whether slave from account can log on to node7

[root@node8 ~]# mysql -uslave -p123456 -h 192.168.110.188
#You can see a successful login to node7
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

mysql> exit
Bye

7.3.2.4 gives node7 replication privileges and sets node8 to replicate node7

[root@node8 ~]# mysql -uroot -p123456

mysql> grant replication slave on *.* to slave@'192.168.110.188' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> change master to master_host='192.168.110.188',master_user='slave',master_password='123456';
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.36 sec)

#View slave status if Slave_IO_Running, Slave_SQL_Running is yes, which means ok
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.110.188
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 604
               Relay_Log_File: node8-relay-bin.000002
                Relay_Log_Pos: 817
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 604
              Relay_Log_Space: 1024
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: a8da7421-157f-11ea-b1bf-000c297c0226
             Master_Info_File: /data/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

mysql> exit
Bye

7.3.3 Configure database server node7

7.3.3.1 Test whether slave from account can log on to node8

[root@node7 ~]# mysql -uslave -p123456 -h 192.168.110.186
#You can see that you successfully logged in to node8 with your slave account
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

mysql> exit
Bye

7.3.3.2 Setting node7 to replicate node8

[root@node7 ~]# mysql -uroot -p123456

mysql> change master to master_host='192.168.110.186',master_user='slave',master_password='123456';
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.10 sec)

#View slave status if Slave_IO_Running, Slave_SQL_Running is yes, which means ok
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.110.186
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 604
               Relay_Log_File: node7-relay-bin.000003
                Relay_Log_Pos: 817
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 604
              Relay_Log_Space: 1391
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2
                  Master_UUID: f083c41e-1671-11ea-8342-000c29f7e789
             Master_Info_File: /data/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.01 sec)

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 604
     Binlog_Do_DB: hotdata
 Binlog_Ignore_DB: mysql
Executed_Gtid_Set: 
1 row in set (0.00 sec)

7.3.4 Insert Data Test Master-Slave Synchronization

7.3.4.1 Insert data on node7 to see if there is synchronous data on node8

#Insert data on node7
mysql> insert into hotdata.customers values (1,'Zhang San','Pearl River New City','Guangzhou','Guangdong Province','1234567890@qq.com','china');
Query OK, 1 row affected (0.01 sec)

mysql> insert into hotdata.customers values (2,'Li Si','Tiananmen','Beijing','Beijing','1234127890@qq.com','china');
Query OK, 1 row affected (0.00 sec)

mysql> insert into hotdata.customers values (3,'May you stay forever young','bell tower and drum tower','Kunming','Yunnan Province','1234567870@qq.com','china');
Query OK, 1 row affected (0.01 sec)

mysql> insert into hotdata.customers values (4,'Four of Zhao','Campo dei fiori','Foshan','Guangdong Province','1239867890@qq.com','china');
Query OK, 1 row affected (0.01 sec)

mysql> insert into hotdata.customers values (2,'Li Si','Tiananmen','Beijing','Beijing','1234127890@qq.com','china');
 values (3,'May you stay forever young','bell tower and drum tower','Kunming','Yunnan Province','1234567870@qq.com','china');
insert into hotdata.customers values (4,'Four of Zhao','Campo dei fiori','Foshan','Guangdong Province','1239867890@qq.com','china');Query OK, 1 row affected (0.00 sec)

mysql> insert into hotdata.customers values (3,'May you stay forever young','bell tower and drum tower','Kunming','Yunnan Province','1234567870@qq.com','china');
Query OK, 1 row affected (0.00 sec)

mysql> insert into hotdata.customers values (4,'Four of Zhao','Campo dei fiori','Foshan','Guangdong Province','1239867890@qq.com','china');
Query OK, 1 row affected (0.00 sec)

mysql> select * from hotdata.customers;
+---------+--------------+--------------+-----------+------------+-------------------+--------------+
| cust_id | cust_name    | cust_address | cust_city | cust_state | cust_email        | cust_country |
+---------+--------------+--------------+-----------+------------+-------------------+--------------+
|       1 | Zhang San         | Pearl River New City     | Guangzhou      | Guangdong Province     | 1234567890@qq.com | china        |
|       2 | Li Si         | Tiananmen       | Beijing      | Beijing     | 1234127890@qq.com | china        |
|       3 | May you stay forever young     | bell tower and drum tower       | Kunming      | Yunnan Province     | 1234567870@qq.com | china        |
|       4 | Four of Zhao         | Campo dei fiori     | Foshan      | Guangdong Province     | 1239867890@qq.com | china        |
|       2 | Li Si         | Tiananmen       | Beijing      | Beijing     | 1234127890@qq.com | china        |
|       3 | May you stay forever young     | bell tower and drum tower       | Kunming      | Yunnan Province     | 1234567870@qq.com | china        |
|       4 | Four of Zhao         | Campo dei fiori     | Foshan      | Guangdong Province     | 1239867890@qq.com | china        |
+---------+--------------+--------------+-----------+------------+-------------------+--------------+
7 rows in set (0.00 sec)

#Query data on node8
mysql> select * from hotdata.customers;
+---------+--------------+--------------+-----------+------------+-------------------+--------------+
| cust_id | cust_name    | cust_address | cust_city | cust_state | cust_email        | cust_country |
+---------+--------------+--------------+-----------+------------+-------------------+--------------+
|       1 | Zhang San         | Pearl River New City     | Guangzhou      | Guangdong Province     | 1234567890@qq.com | china        |
|       2 | Li Si         | Tiananmen       | Beijing      | Beijing     | 1234127890@qq.com | china        |
|       3 | May you stay forever young     | bell tower and drum tower       | Kunming      | Yunnan Province     | 1234567870@qq.com | china        |
|       4 | Four of Zhao         | Campo dei fiori     | Foshan      | Guangdong Province     | 1239867890@qq.com | china        |
|       2 | Li Si         | Tiananmen       | Beijing      | Beijing     | 1234127890@qq.com | china        |
|       3 | May you stay forever young     | bell tower and drum tower       | Kunming      | Yunnan Province     | 1234567870@qq.com | china        |
|       4 | Four of Zhao         | Campo dei fiori     | Foshan      | Guangdong Province     | 1239867890@qq.com | china        |
+---------+--------------+--------------+-----------+------------+-------------------+--------------+
7 rows in set (0.00 sec)

7.3.4.2 Insert data on node8 to see if there is synchronous data on node7

#Insert data on node8
mysql> insert into hotdata.customers values (5,'Lennon','sports center','Guangzhou','Guangdong Province','1234512890@qq.com','china');
tomers values (7,'Philosophy of Life','Tapo Mountain','Dali','Yunnan Province','2489567890@qq.com','china');
insert into hotdata.customers values (8,'American TV Series','American TV Series','U.S.A','U.S.A','2489567890@qq.com','usa');Query OK, 1 row affected (0.01 sec)

mysql> insert into hotdata.customers values (6,'Gunn Quayle Tse','Sports West Road','Guangzhou','Guangdong Province','1364567890@qq.com','china');
Query OK, 1 row affected (0.00 sec)

mysql> insert into hotdata.customers values (7,'Philosophy of Life','Tapo Mountain','Dali','Yunnan Province','2489567890@qq.com','china');
Query OK, 1 row affected (0.00 sec)

mysql> insert into hotdata.customers values (8,'American TV Series','American TV Series','U.S.A','U.S.A','2489567890@qq.com','usa');
Query OK, 1 row affected (0.00 sec)

mysql> select * from hotdata.customers;
+---------+-----------------+--------------+-----------+------------+-------------------+--------------+
| cust_id | cust_name       | cust_address | cust_city | cust_state | cust_email        | cust_country |
+---------+-----------------+--------------+-----------+------------+-------------------+--------------+
|       1 | Zhang San            | Pearl River New City     | Guangzhou      | Guangdong Province     | 1234567890@qq.com | china        |
|       2 | Li Si            | Tiananmen       | Beijing      | Beijing     | 1234127890@qq.com | china        |
|       3 | May you stay forever young        | bell tower and drum tower       | Kunming      | Yunnan Province     | 1234567870@qq.com | china        |
|       4 | Four of Zhao            | Campo dei fiori     | Foshan      | Guangdong Province     | 1239867890@qq.com | china        |
|       2 | Li Si            | Tiananmen       | Beijing      | Beijing     | 1234127890@qq.com | china        |
|       3 | May you stay forever young        | bell tower and drum tower       | Kunming      | Yunnan Province     | 1234567870@qq.com | china        |
|       4 | Four of Zhao            | Campo dei fiori     | Foshan      | Guangdong Province     | 1239867890@qq.com | china        |
|       5 | Lennon            | sports center     | Guangzhou      | Guangdong Province     | 1234512890@qq.com | china        |
|       6 | Gunn Quayle Tse          | Sports West Road     | Guangzhou      | Guangdong Province     | 1364567890@qq.com | china        |
|       7 | Philosophy of Life      | Tapo Mountain       | Dali      | Yunnan Province     | 2489567890@qq.com | china        |
|       8 | American TV Series            | American TV Series         | U.S.A      | U.S.A       | 2489567890@qq.com | usa          |
+---------+-----------------+--------------+-----------+------------+-------------------+--------------+
11 rows in set (0.00 sec)

#Noe7 Query Data
mysql> select * from hotdata.customers;
+---------+-----------------+--------------+-----------+------------+-------------------+--------------+
| cust_id | cust_name       | cust_address | cust_city | cust_state | cust_email        | cust_country |
+---------+-----------------+--------------+-----------+------------+-------------------+--------------+
|       1 | Zhang San            | Pearl River New City     | Guangzhou      | Guangdong Province     | 1234567890@qq.com | china        |
|       2 | Li Si            | Tiananmen       | Beijing      | Beijing     | 1234127890@qq.com | china        |
|       3 | May you stay forever young        | bell tower and drum tower       | Kunming      | Yunnan Province     | 1234567870@qq.com | china        |
|       4 | Four of Zhao            | Campo dei fiori     | Foshan      | Guangdong Province     | 1239867890@qq.com | china        |
|       2 | Li Si            | Tiananmen       | Beijing      | Beijing     | 1234127890@qq.com | china        |
|       3 | May you stay forever young        | bell tower and drum tower       | Kunming      | Yunnan Province     | 1234567870@qq.com | china        |
|       4 | Four of Zhao            | Campo dei fiori     | Foshan      | Guangdong Province     | 1239867890@qq.com | china        |
|       5 | Lennon            | sports center     | Guangzhou      | Guangdong Province     | 1234512890@qq.com | china        |
|       6 | Gunn Quayle Tse          | Sports West Road     | Guangzhou      | Guangdong Province     | 1364567890@qq.com | china        |
|       7 | Philosophy of Life      | Tapo Mountain       | Dali      | Yunnan Province     | 2489567890@qq.com | china        |
|       8 | American TV Series            | American TV Series         | U.S.A      | U.S.A       | 2489567890@qq.com | usa          |
+---------+-----------------+--------------+-----------+------------+-------------------+--------------+
11 rows in set (0.01 sec)

Thus, the master-master two-way synchronization can be completed.

7.3.5 Remove master-slave synchronization completely

Since there is master-slave synchronization set up and there is master-slave synchronization undone, if there is a need to undo master-slave synchronization, see below.

Both mysql servers need to clear the configuration of matser and slave because they are primary and primary two-way synchronization.

#Do the following on both MySQL, for example, node7
#Clear master configuration
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 154
     Binlog_Do_DB: hotdata
 Binlog_Ignore_DB: mysql
Executed_Gtid_Set: 
1 row in set (0.00 sec)

mysql> stop slave;
Query OK, 0 rows affected (0.88 sec)

mysql> reset slave all;
Query OK, 0 rows affected (0.12 sec)

#Clear slave configuration
mysql> show slave status\G
Empty set (0.00 sec)

It can be seen that both master and slave libraries have been decoupled, and the master-slave related configurations in the configuration file can be deleted at last.

7.4 Summary

For MySQL master-slave synchronization master two-way synchronization architecture, many people mistakenly think that this can achieve MySQL load balancing, which is actually very bad. Every server needs to do the same synchronization updates, which destroys the isolation of things and data consistency, and is not recommended.

M-S-S architecture for MySQL master-slave synchronization

8.1 Server Planning

host name IP Operating System Version MySQL Version role
node7 192.168.110.188 CentOS 7.4.1708 5.7.26 master
node8 192.168.110.186 CentOS 7.4.1708 5.7.26 slave relay (relay)
node9 192.168.110.187 CentOS 7.4.1708 5.7.26 slave (from)

8.2 Principle of Master-Slave Synchronization

The master records the changes in the binary log, slave copies the master's binary log events to its relay log, slave redoes the events in the relay log, and modifies the data on salve.

Because the structural IO pressure of one master and many slaves is concentrated on the master, using a slave as the relay to share the pressure of the Master requires the slave relay to turn on bin-log and configure log-slave-updates. The Slave relay can use the Black-hole storage engine to store data on disk instead of just logging binary logs.

8.3 Deploy M-S-S for MySQL Master-Slave Synchronization

8.3.1 Configure master database server node7

8.3.1.1 Create databases and their tables that need to be synchronized

[root@node7 ~]# mysql -uroot -p123456

mysql> create database hotdata;     #Create Hot Spot Database
Query OK, 1 row affected (0.70 sec)

mysql> use hotdata;
Database changed
#Create Customer Table
mysql> create table customers(cust_id int,cust_name varchar(30),cust_address varchar(50),cust_city varchar(30),cust_state varchar(50),cust_email varchar(30),cust_country varchar(50));
Query OK, 0 rows affected (0.44 sec)

mysql> desc customers;   #View table structure
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| cust_id      | int(11)     | YES  |     | NULL    |       |
| cust_name    | varchar(30) | YES  |     | NULL    |       |
| cust_address | varchar(50) | YES  |     | NULL    |       |
| cust_city    | varchar(30) | YES  |     | NULL    |       |
| cust_state   | varchar(50) | YES  |     | NULL    |       |
| cust_email   | varchar(30) | YES  |     | NULL    |       |
| cust_country | varchar(50) | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
7 rows in set (0.36 sec)

mysql> exit
Bye

8.3.1.2 Grant node8 replication permissions

[root@node7 ~]# mysql -uroot -p123456

mysql> grant replication slave on *.* to repl@'192.168.110.186' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.36 sec)

mysql>  flush privileges;
Query OK, 0 rows affected (0.14 sec)

mysql> exit
Bye

8.3.1.3 Modify the configuration file and restart

[root@node7 ~]# vim /etc/my.cnf
#Configurations related to master-slave synchronization are#Below mysql replication
[root@node7 ~]# cat /etc/my.cnf
[client]
port=3306
socket=/data/run/mysql.sock

[mysqld]
port=3306
socket=/data/run/mysql.sock
pid_file=/data/run/mysql.pid
datadir=/data/data
default_storage_engine=InnoDB
max_allowed_packet=512M
max_connections=2048
open_files_limit=65535

skip-name-resolve
lower_case_table_names=1

character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'

innodb_buffer_pool_size=1024M
innodb_log_file_size=2048M
innodb_file_per_table=1
innodb_flush_log_at_trx_commit=0

key_buffer_size=64M

log-error=/data/log/mysql_error.log

slow_query_log=1
slow_query_log_file=/data/log/mysql_slow_query.log
long_query_time=5

tmp_table_size=32M
max_heap_table_size=32M
query_cache_type=0
query_cache_size=0


skip_ssl

#mysql replication
log-bin=/data/binlogs/mysql-bin
#server-id=1 must be unique
server-id=1
binlog-do-db=hotdata
binlog-ignore-db=mysql
#After each transaction commit, MySQL will execute a disk synchronization instruction such as fsync to binlog_Data in cache is forced to write to disk
#For a detailed explanation of sync-binlog, see:https://www.cnblogs.com/wt645631686/p/8109002.html
sync-binlog=1
#ROW mode (RBR): Does not record context information for each sql statement, only records which data was modified and what changed
#Explanation View:https://www.cnblogs.com/xingyunfashi/p/8431780.html
binlog-format=row
[root@node7 ~]# systemctl restart mysqld

8.3.2 Configure slave relay node8

8.3.2.1 Create databases and their tables that need to be synchronized

[root@node8 ~]# mysql -uroot -p123456

mysql> create database hotdata;     #Create Hot Spot Database
Query OK, 1 row affected (0.70 sec)

mysql> use hotdata;
Database changed
#Create Customer Table
mysql> create table customers(cust_id int,cust_name varchar(30),cust_address varchar(50),cust_city varchar(30),cust_state varchar(50),cust_email varchar(30),cust_country varchar(50));
Query OK, 0 rows affected (0.44 sec)

mysql> desc customers;   #View table structure
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| cust_id      | int(11)     | YES  |     | NULL    |       |
| cust_name    | varchar(30) | YES  |     | NULL    |       |
| cust_address | varchar(50) | YES  |     | NULL    |       |
| cust_city    | varchar(30) | YES  |     | NULL    |       |
| cust_state   | varchar(50) | YES  |     | NULL    |       |
| cust_email   | varchar(30) | YES  |     | NULL    |       |
| cust_country | varchar(50) | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
7 rows in set (0.36 sec)

mysql> exit
Bye

8.3.2.2 Modify the configuration file and restart

##Configurations related to master-slave synchronization are#Below mysql replication
[root@node8 ~]# cat /etc/my.cnf
[client]
port=3306
socket=/data/run/mysql.sock

[mysqld]
port=3306
socket=/data/run/mysql.sock
pid_file=/data/run/mysql.pid
datadir=/data/data
default_storage_engine=InnoDB
max_allowed_packet=512M
max_connections=2048
open_files_limit=65535

skip-name-resolve
lower_case_table_names=1

character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'

innodb_buffer_pool_size=1024M
innodb_log_file_size=2048M
innodb_file_per_table=1
innodb_flush_log_at_trx_commit=0

key_buffer_size=64M

log-error=/data/log/mysql_error.log

slow_query_log=1
slow_query_log_file=/data/log/mysql_slow_query.log
long_query_time=5

tmp_table_size=32M
max_heap_table_size=32M
query_cache_type=0
query_cache_size=0

#mysql replication configuration
log-bin=/data/binlogs/mysql-bin
server-id=2
#log-slave-updates The parameter is off by default, if not set manually then bin-log Only records executed directly on the library SQL language##Sentence, by replication Mechanismatic SQL Thread Read relay-log And executed SQL Statement will not be logged bin-log,That's not possible#The three-level cascade synchronization described.
log-slave-updates=1
binlog-format=row
relay-log=/data/data/relay-log.info
[root@node8 ~]# 

[root@node8 ~]# systemctl restart mysqld

8.3.2.3 Authorize node8 to replicate node7 and grant node9 replication privileges

[root@node8 ~]# mysql -uroot -p123456

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> change master to master_host='192.168.110.188',master_user='repl',master_password='123456';
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql>  start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.110.188
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 154
               Relay_Log_File: relay-log.000003
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 1230
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: a8da7421-157f-11ea-b1bf-000c297c0226
             Master_Info_File: /data/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

mysql> grant replication slave on *.* to 'repl'@'192.168.110.187' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

8.3.3 Configure from server node9

8.3.3.1 Create databases and their tables that need to be synchronized

[root@node9 ~]# mysql -uroot -p123456

mysql> create database hotdata;     #Create Hot Spot Database
Query OK, 1 row affected (0.70 sec)

mysql> use hotdata;
Database changed
#Create Customer Table
mysql> create table customers(cust_id int,cust_name varchar(30),cust_address varchar(50),cust_city varchar(30),cust_state varchar(50),cust_email varchar(30),cust_country varchar(50));
Query OK, 0 rows affected (0.44 sec)

mysql> desc customers;   #View table structure
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| cust_id      | int(11)     | YES  |     | NULL    |       |
| cust_name    | varchar(30) | YES  |     | NULL    |       |
| cust_address | varchar(50) | YES  |     | NULL    |       |
| cust_city    | varchar(30) | YES  |     | NULL    |       |
| cust_state   | varchar(50) | YES  |     | NULL    |       |
| cust_email   | varchar(30) | YES  |     | NULL    |       |
| cust_country | varchar(50) | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
7 rows in set (0.36 sec)

mysql> exit
Bye

8.3.3.2 Modify the configuration file and restart

[root@node9 ~]# vim /etc/my.cnf
[root@node9 ~]# cat /etc/my.cnf
[client]
port=3306
socket=/data/run/mysql.sock

[mysqld]
port=3306
socket=/data/run/mysql.sock
pid_file=/data/run/mysql.pid
datadir=/data/data
default_storage_engine=InnoDB
max_allowed_packet=512M
max_connections=2048
open_files_limit=65535

skip-name-resolve
lower_case_table_names=1

character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'

innodb_buffer_pool_size=1024M
innodb_log_file_size=2048M
innodb_file_per_table=1
innodb_flush_log_at_trx_commit=0

key_buffer_size=64M

log-error=/data/log/mysql_error.log

slow_query_log=1
slow_query_log_file=/data/log/mysql_slow_query.log
long_query_time=5

tmp_table_size=32M
max_heap_table_size=32M
query_cache_type=0
query_cache_size=0

#mysql replication configuration
server-id=3
log-bin=/data/binlogs/mysql-bin
binlog-format=row
#relay-log=/data/relaylog/relay.log
relay-log=/data/data/relay-log.info
[root@node9 ~]# 

[root@node9 ~]#  systemctl restart mysqld

8.3.3.3 Specifies that node8 is the master of node9

[root@node9 ~]# mysql -uroot -p123456

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> change master to master_host='192.168.110.186',master_user='repl',master_password='123456';
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql>  show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.110.186
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 1052
               Relay_Log_File: relay-log.000003
                Relay_Log_Pos: 1265
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1052
              Relay_Log_Space: 1679
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2
                  Master_UUID: f083c41e-1671-11ea-8342-000c29f7e789
             Master_Info_File: /data/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.01 sec)

mysql> exit
Bye

8.3.4 Insert Data Test

#Insert data on node7 to see if node8 and node9 are synchronizing data

#Insert data on node7 first
[root@node7 ~]# mysql -uroot -p123456

mysql> insert into hotdata.customers values (1,'Zhang San','Pearl River New City','Guangzhou','Guangdong Province','1234567890@qq.com','china');

mysql> insert into hotdata.customers values (2,'Li Si','Tiananmen','Beijing','Beijing','1234127890@qq.com','china');
Query OK, 1 row affected (0.01 sec)

mysql> insert into hotdata.customers values (3,'May you stay forever young','bell tower and drum tower','Kunming','Yunnan Province','1234567870@qq.com','china');
','Yunnan Province','2489567890@qq.com','china');Query OK, 1 row affected (0.09 sec)

mysql> insert into hotdata.customers values (4,'Four of Zhao','Campo dei fiori','Foshan','Guangdong Province','1239867890@qq.com','china');
Query OK, 1 row affected (0.00 sec)

mysql> insert into hotdata.customers values (5,'Lennon','sports center','Guangzhou','Guangdong Province','1234512890@qq.com','china');
Query OK, 1 row affected (0.02 sec)

mysql> insert into hotdata.customers values (6,'Gunn Quayle Tse','Sports West Road','Guangzhou','Guangdong Province','1364567890@qq.com','china');
Query OK, 1 row affected (0.01 sec)

mysql> insert into hotdata.customers values (7,'Philosophy of Life','Tapo Mountain','Dali','Yunnan Province','2489567890@qq.com','china');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> 
mysql> select * from hotdata.customers;
+---------+-----------------+--------------+-----------+------------+-------------------+--------------+
| cust_id | cust_name       | cust_address | cust_city | cust_state | cust_email        | cust_country |
+---------+-----------------+--------------+-----------+------------+-------------------+--------------+
|       1 | Zhang San            | Pearl River New City     | Guangzhou      | Guangdong Province     | 1234567890@qq.com | china        |
|       2 | Li Si            | Tiananmen       | Beijing      | Beijing     | 1234127890@qq.com | china        |
|       3 | May you stay forever young        | bell tower and drum tower       | Kunming      | Yunnan Province     | 1234567870@qq.com | china        |
|       4 | Four of Zhao            | Campo dei fiori     | Foshan      | Guangdong Province     | 1239867890@qq.com | china        |
|       5 | Lennon            | sports center     | Guangzhou      | Guangdong Province     | 1234512890@qq.com | china        |
|       6 | Gunn Quayle Tse          | Sports West Road     | Guangzhou      | Guangdong Province     | 1364567890@qq.com | china        |
|       7 | Philosophy of Life      | Tapo Mountain       | Dali      | Yunnan Province     | 2489567890@qq.com | china        |
+---------+-----------------+--------------+-----------+------------+-------------------+--------------+
7 rows in set (0.00 sec)

#View data on node8
[root@node8 ~]# mysql -uroot -p123456

mysql> select * from hotdata.customers;
+---------+-----------------+--------------+-----------+------------+-------------------+--------------+
| cust_id | cust_name       | cust_address | cust_city | cust_state | cust_email        | cust_country |
+---------+-----------------+--------------+-----------+------------+-------------------+--------------+
|       1 | Zhang San            | Pearl River New City     | Guangzhou      | Guangdong Province     | 1234567890@qq.com | china        |
|       2 | Li Si            | Tiananmen       | Beijing      | Beijing     | 1234127890@qq.com | china        |
|       3 | May you stay forever young        | bell tower and drum tower       | Kunming      | Yunnan Province     | 1234567870@qq.com | china        |
|       4 | Four of Zhao            | Campo dei fiori     | Foshan      | Guangdong Province     | 1239867890@qq.com | china        |
|       5 | Lennon            | sports center     | Guangzhou      | Guangdong Province     | 1234512890@qq.com | china        |
|       6 | Gunn Quayle Tse          | Sports West Road     | Guangzhou      | Guangdong Province     | 1364567890@qq.com | china        |
|       7 | Philosophy of Life      | Tapo Mountain       | Dali      | Yunnan Province     | 2489567890@qq.com | china        |
+---------+-----------------+--------------+-----------+------------+-------------------+--------------+
7 rows in set (0.00 sec)

#View data on node9
[root@node9 ~]# mysql -uroot -p123456

mysql> select * from hotdata.customers;
+---------+-----------------+--------------+-----------+------------+-------------------+--------------+
| cust_id | cust_name       | cust_address | cust_city | cust_state | cust_email        | cust_country |
+---------+-----------------+--------------+-----------+------------+-------------------+--------------+
|       1 | Zhang San            | Pearl River New City     | Guangzhou      | Guangdong Province     | 1234567890@qq.com | china        |
|       2 | Li Si            | Tiananmen       | Beijing      | Beijing     | 1234127890@qq.com | china        |
|       3 | May you stay forever young        | bell tower and drum tower       | Kunming      | Yunnan Province     | 1234567870@qq.com | china        |
|       4 | Four of Zhao            | Campo dei fiori     | Foshan      | Guangdong Province     | 1239867890@qq.com | china        |
|       5 | Lennon            | sports center     | Guangzhou      | Guangdong Province     | 1234512890@qq.com | china        |
|       6 | Gunn Quayle Tse          | Sports West Road     | Guangzhou      | Guangdong Province     | 1364567890@qq.com | china        |
|       7 | Philosophy of Life      | Tapo Mountain       | Dali      | Yunnan Province     | 2489567890@qq.com | china        |
+---------+-----------------+--------------+-----------+------------+-------------------+--------------+
7 rows in set (0.00 sec)


You can see that the data is all synchronized, but there is a problem, slave relay node8 also found the data and did not meet the requirements.

Setting up tables that node8 needs to synchronizeHotdata.customersThe storage engine is blackhole

#Turn off logging
mysql> set sql_log_bin=off;
Query OK, 0 rows affected (0.01 sec)

#Blackhole engine–"Black Hole". It works just like its name: any data written to the engine is discarded without actual storage;#The content of the Select statement is always empty.This is exactly the same as the / dev/null file on Linux.
mysql> alter table hotdata.customers ENGINE=blackhole;
Query OK, 8 rows affected (0.01 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> select * from hotdata.customers;
Empty set (0.00 sec)

At this point, data is inserted on Noe7, Noe9 synchronizes data, and Noe8 only shares the IO pressure on Noe7 and does not store data.

Since then, the M-S-S architecture for MySQL master-slave synchronization has been built.

8.4 Summary

The benefit of MySQL Master-Slave Synchronization M-S architecture is that it can greatly reduce the pressure on the master node.

Another use of MySQL cascade replication is for data migration.For example, two new servers, B and C, will replace the old server A, while B and C are new subordinates.Therefore, it is also easy to switch by configuring cascade replication to migrate data.

The conversion process is as follows:

Master A ----> slave B ------> slave C = convert to ===> matser B ----> slave C

9. MySQL Master-Slave Synchronization Multi-Master-Slave Architecture

9.1 Server Planning

host name IP Operating System Version MySQL Version role
node6 192.168.110.185 CentOS 7.4.1708 5.7.26 master,slave
node7 192.168.110.188 CentOS 7.4.1708 5.7.26 master,slave
node8 192.168.110.186 CentOS 7.4.1708 5.7.26 slave
node9 192.168.110.187 CentOS 7.4.1708 5.7.26 slave

9.2 MySQL Multi-master Multi-slave Architecture Diagram

The schema diagram illustrates that node6 and node8, node7 and node9 are master-slave architectures, and node6 and node7 are master-slave two-way synchronizations.

9.3 Principle of Master-Slave Synchronization

The master records the changes in the binary log, slave copies the master's binary log events to its relay log, slave redoes the events in the relay log, and modifies the data on salve.

A master-slave architecture can alleviate the pressure of reading, but once the master database is down, it cannot be written. With a dual-master-slave architecture, one master database is down and another master database can be used instead.

9.4 Deploy MySQL Master-Slave Synchronization Dual Master-Slave

9.4.1 Configure master database server node6

9.4.1.1 Create databases and their tables that need to be synchronized

[root@node6 ~]# mysql -uroot -p123456

mysql> create database hotdata;     #Create Hot Spot Database
Query OK, 1 row affected (0.70 sec)

mysql> use hotdata;
Database changed
#Create Customer Table
mysql> create table customers(cust_id int,cust_name varchar(30),cust_address varchar(50),cust_city varchar(30),cust_state varchar(50),cust_email varchar(30),cust_country varchar(50));
Query OK, 0 rows affected (0.44 sec)

mysql> desc customers;   #View table structure
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| cust_id      | int(11)     | YES  |     | NULL    |       |
| cust_name    | varchar(30) | YES  |     | NULL    |       |
| cust_address | varchar(50) | YES  |     | NULL    |       |
| cust_city    | varchar(30) | YES  |     | NULL    |       |
| cust_state   | varchar(50) | YES  |     | NULL    |       |
| cust_email   | varchar(30) | YES  |     | NULL    |       |
| cust_country | varchar(50) | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
7 rows in set (0.36 sec)

mysql> exit
Bye

9.4.1.2 Modify MySQL configuration file for node6

[root@node6 ~]# vim /etc/my.cnf
#MySQL Master-slave synchronization is configured in#Under mysql replication
[root@node6 ~]# cat /etc/my.cnf
[client]
port=3306
socket=/data/run/mysql.sock
default-character-set=utf8

[mysqld]
port=3306
socket=/data/run/mysql.sock
pid_file=/data/run/mysql.pid
datadir=/data/data
default_storage_engine=InnoDB
max_allowed_packet=512M
max_connections=2048
open_files_limit=65535

skip-name-resolve
lower_case_table_names=1

character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'

innodb_buffer_pool_size=1024M
innodb_log_file_size=2048M
innodb_file_per_table=1
innodb_flush_log_at_trx_commit=0

key_buffer_size=64M

log-error=/data/log/mysql_error.log
slow_query_log=1
slow_query_log_file=/data/log/mysql_slow_query.log
long_query_time=5

tmp_table_size=32M
max_heap_table_size=32M
query_cache_type=0
query_cache_size=0

#mysql replication
#server-id must be unique
server-id=4
#The logbin parameter enables binary logging and places the binary logging in the / data/binlogs directory
log-bin=/data/binlogs/mysql-bin
#Libraries that binlog-do-db can be copied from the server
binlog-do-db=hotdata
#Libraries that binlog-ignore-db cannot be copied from the server
binlog-ignore-db=mysql
##auto_increment_increment,Controls the self-increasing step of the primary key to prevent Master and Master Duplicate self-additions between copies##Segment value, usually auto_increment_increment=n, how many primary servers are there, n is set to how many;
auto_increment_increment=2
#auto_increment_offset=1 Set the self-increasing starting value, set here to 1, so Master Of auto_increment Value generated by field##Yes: 1, 3, 5, 7, ...Equal odd number ID,Be careful auto_increment_offset Settings for different master Settings should not be the same, otherwise##Primary key conflicts are prone, such as master 1 offset=1, Master 2 offset=2, Master 3 offset=3
auto_increment_offset=1
#In dual main mode, log-slave-updates Configuration items must be configured, otherwise they will be node6 Updates were made on the#Noe7 and Noe8 will be updated, but not on Noe9
log-slave-updates
#Sync_Bilog means that every few transaction commits, MySQL flushes the binlog cache into the log file, defaulting to 0, and the safest setting is 1;
sync_binlog=1

#Restart MySQL
[root@node6 ~]# systemctl restart mysqld
[root@node6 ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Thu 2019-12-12 15:02:26 CST; 28s ago
     Docs: man:mysqld(5.7)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 3753 ExecStart=/usr/local/mysql/bin/mysqld --daemonize --pid-file=/data/run/mysql.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
 Main PID: 3755 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─3755 /usr/local/mysql/bin/mysqld --daemonize --pid-file=/data/run/mysql.pid

Dec 12 15:02:25 node6 systemd[1]: Starting MySQL Server...
Dec 12 15:02:26 node6 systemd[1]: Started MySQL Server.

9.4.1.3 Noe6 Create duplicate accounts and authorize Noe7 and Noe8

[root@node6 ~]# mysql -uroot -p123456

mysql> grant replication slave on *.* to 'copy'@'192.168.110.188' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> grant replication slave on *.* to 'copy'@'192.168.110.186' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

9.4.2 Configure master database server node7

9.4.2.1 Create databases and their tables that need to be synchronized

[root@node7 ~]# mysql -uroot -p123456

mysql> create database hotdata;     #Create Hot Spot Database
Query OK, 1 row affected (0.70 sec)

mysql> use hotdata;
Database changed
#Create Customer Table
mysql> create table customers(cust_id int,cust_name varchar(30),cust_address varchar(50),cust_city varchar(30),cust_state varchar(50),cust_email varchar(30),cust_country varchar(50));
Query OK, 0 rows affected (0.44 sec)

mysql> desc customers;   #View table structure
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| cust_id      | int(11)     | YES  |     | NULL    |       |
| cust_name    | varchar(30) | YES  |     | NULL    |       |
| cust_address | varchar(50) | YES  |     | NULL    |       |
| cust_city    | varchar(30) | YES  |     | NULL    |       |
| cust_state   | varchar(50) | YES  |     | NULL    |       |
| cust_email   | varchar(30) | YES  |     | NULL    |       |
| cust_country | varchar(50) | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
7 rows in set (0.36 sec)

mysql> exit
Bye

9.4.2.2 Modify the MySQL configuration file for node7

[root@node7 ~]# vim /etc/my.cnf
[root@node7 ~]# cat /etc/my.cnf
[client]
port=3306
socket=/data/run/mysql.sock
default-character-set=utf8

[mysqld]
port=3306
socket=/data/run/mysql.sock
pid_file=/data/run/mysql.pid
datadir=/data/data
default_storage_engine=InnoDB
max_allowed_packet=512M
max_connections=2048
open_files_limit=65535

skip-name-resolve
lower_case_table_names=1

character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'

innodb_buffer_pool_size=1024M
innodb_log_file_size=2048M
innodb_file_per_table=1
innodb_flush_log_at_trx_commit=0

key_buffer_size=64M

log-error=/data/log/mysql_error.log

slow_query_log=1
slow_query_log_file=/data/log/mysql_slow_query.log
long_query_time=5

tmp_table_size=32M
max_heap_table_size=32M
query_cache_type=0
query_cache_size=0


skip_ssl

#mysql replication
log-bin=/data/binlogs/mysql-bin
server-id=1
binlog-do-db=hotdata
binlog-ignore-db=mysql
auto_increment_increment=2
auto_increment_offset=2
log-slave-updates
sync_binlog=1

[root@node7 ~]# systemctl restart mysqld
[root@node7 ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Thu 2019-12-12 15:02:29 CST; 26s ago
     Docs: man:mysqld(5.7)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 14635 ExecStart=/usr/local/mysql/bin/mysqld --daemonize --pid-file=/data/run/mysql.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
 Main PID: 14637 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─14637 /usr/local/mysql/bin/mysqld --daemonize --pid-file=/data/run/mysql.pid

Dec 12 15:02:25 node7 systemd[1]: Starting MySQL Server...
Dec 12 15:02:29 node7 systemd[1]: Started MySQL Server.

9.4.2.3 Noe7 Creates a duplicate account and authorizes Noe6 and Noe9

[root@node7 ~]# mysql -uroot -p123456

mysql> grant replication slave on *.* to 'copy'@'192.168.110.185' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.12 sec)

mysql> grant replication slave on *.* to 'copy'@'192.168.110.187' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

9.4.3 Configuration from database server node8

9.4.3.1 Create databases and their tables that need to be synchronized

[root@node8 ~]# mysql -uroot -p123456

mysql> create database hotdata;     #Create Hot Spot Database
Query OK, 1 row affected (0.70 sec)

mysql> use hotdata;
Database changed
#Create Customer Table
mysql> create table customers(cust_id int,cust_name varchar(30),cust_address varchar(50),cust_city varchar(30),cust_state varchar(50),cust_email varchar(30),cust_country varchar(50));
Query OK, 0 rows affected (0.44 sec)

mysql> desc customers;   #View table structure
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| cust_id      | int(11)     | YES  |     | NULL    |       |
| cust_name    | varchar(30) | YES  |     | NULL    |       |
| cust_address | varchar(50) | YES  |     | NULL    |       |
| cust_city    | varchar(30) | YES  |     | NULL    |       |
| cust_state   | varchar(50) | YES  |     | NULL    |       |
| cust_email   | varchar(30) | YES  |     | NULL    |       |
| cust_country | varchar(50) | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
7 rows in set (0.36 sec)

mysql> exit
Bye

9.4.3.2 Modify the MySQL configuration file for node8

[root@node8 ~]# vim /etc/my.cnf
[root@node8 ~]# cat /etc/my.cnf
[client]
port=3306
socket=/data/run/mysql.sock
default-character-set=utf8

[mysqld]
port=3306
socket=/data/run/mysql.sock
pid_file=/data/run/mysql.pid
datadir=/data/data
default_storage_engine=InnoDB
max_allowed_packet=512M
max_connections=2048
open_files_limit=65535

skip-name-resolve
lower_case_table_names=1

character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'

innodb_buffer_pool_size=1024M
innodb_log_file_size=2048M
innodb_file_per_table=1
innodb_flush_log_at_trx_commit=0

key_buffer_size=64M

log-error=/data/log/mysql_error.log

slow_query_log=1
slow_query_log_file=/data/log/mysql_slow_query.log
long_query_time=5

tmp_table_size=32M
max_heap_table_size=32M
query_cache_type=0
query_cache_size=0

#mysql replication configuration
server-id=2

[root@node8 ~]# systemctl restart mysqld
[root@node8 ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Thu 2019-12-12 15:02:28 CST; 27s ago
     Docs: man:mysqld(5.7)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 24078 ExecStart=/usr/local/mysql/bin/mysqld --daemonize --pid-file=/data/run/mysql.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
 Main PID: 24080 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─24080 /usr/local/mysql/bin/mysqld --daemonize --pid-file=/data/run/mysql.pid

Dec 12 15:02:25 node8 systemd[1]: Starting MySQL Server...
Dec 12 15:02:28 node8 systemd[1]: Started MySQL Server.

9.4.4 Configure from database server node9

9.4.4.1 Create databases and their tables that need to be synchronized

[root@node9 ~]# mysql -uroot -p123456

mysql> create database hotdata;     #Create Hot Spot Database
Query OK, 1 row affected (0.70 sec)

mysql> use hotdata;
Database changed
#Create Customer Table
mysql> create table customers(cust_id int,cust_name varchar(30),cust_address varchar(50),cust_city varchar(30),cust_state varchar(50),cust_email varchar(30),cust_country varchar(50));
Query OK, 0 rows affected (0.44 sec)

mysql> desc customers;   #View table structure
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| cust_id      | int(11)     | YES  |     | NULL    |       |
| cust_name    | varchar(30) | YES  |     | NULL    |       |
| cust_address | varchar(50) | YES  |     | NULL    |       |
| cust_city    | varchar(30) | YES  |     | NULL    |       |
| cust_state   | varchar(50) | YES  |     | NULL    |       |
| cust_email   | varchar(30) | YES  |     | NULL    |       |
| cust_country | varchar(50) | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
7 rows in set (0.36 sec)

mysql> exit
Bye

9.4.4.2 Modify the MySQL configuration file for node9

[root@node9 ~]# vim /etc/my.cnf
[root@node9 ~]# cat /etc/my.cnf
[client]
port=3306
socket=/data/run/mysql.sock
default-character-set=utf8

[mysqld]
port=3306
socket=/data/run/mysql.sock
pid_file=/data/run/mysql.pid
datadir=/data/data
default_storage_engine=InnoDB
max_allowed_packet=512M
max_connections=2048
open_files_limit=65535

skip-name-resolve
lower_case_table_names=1

character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'

innodb_buffer_pool_size=1024M
innodb_log_file_size=2048M
innodb_file_per_table=1
innodb_flush_log_at_trx_commit=0

key_buffer_size=64M

log-error=/data/log/mysql_error.log

slow_query_log=1
slow_query_log_file=/data/log/mysql_slow_query.log
long_query_time=5

tmp_table_size=32M
max_heap_table_size=32M
query_cache_type=0
query_cache_size=0

#mysql replication configuration
server-id=3


[root@node9 ~]# systemctl restart mysqld
[root@node9 ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Thu 2019-12-12 15:02:27 CST; 28s ago
     Docs: man:mysqld(5.7)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 27714 ExecStart=/usr/local/mysql/bin/mysqld --daemonize --pid-file=/data/run/mysql.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
 Main PID: 27716 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─27716 /usr/local/mysql/bin/mysqld --daemonize --pid-file=/data/run/mysql.pid

Dec 12 15:02:25 node9 systemd[1]: Starting MySQL Server...
Dec 12 15:02:27 node9 systemd[1]: Started MySQL Server.

9.4.5 Configure master on slave (all nodes)

Four MySQL serves as slave s, so all nodes need to be configured

#Execute the following on MySQL for all nodes
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)

mysql> reset master;
Query OK, 0 rows affected (0.03 sec)

#Because node6 and node9 are slaves of node7, node7 and node8 are slaves of node6

#So execute on Noe6 and Noe9
mysql> change master to master_host='192.168.110.188',master_user='copy',master_password='123456';
Query OK, 0 rows affected, 2 warnings (0.02 sec)

#Execute on Noe7 and Noe8
mysql> change master to master_host='192.168.110.185',master_user='copy',master_password='123456';
Query OK, 0 rows affected, 2 warnings (0.04 sec)

9.4.6 Start slave to bring four MySQL into master-slave replication

#Execute on all MySQL nodes
#mysql> start slave;
#mysql> show master status\G
#mysql> show slave status\G
#If after show slave status\G, Slave_IO_Running and lave_SQL_Running is yes, indicating that master-slave synchronization was successful

#Take node6 as an example, and the other nodes are similar
mysql> start slave;
Query OK, 0 rows affected (0.05 sec)

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 154
     Binlog_Do_DB: hotdata
 Binlog_Ignore_DB: mysql
Executed_Gtid_Set: 
1 row in set (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.110.188
                  Master_User: copy
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: node6-relay-bin.000002
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 574
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: a8da7421-157f-11ea-b1bf-000c297c0226
             Master_Info_File: /data/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

mysql> exit
Bye

9.4.7 Test Validation Dual Primary and Dual Subordinate

#Test 1: Insert a data on node6 that all other mysql s should synchronize
 #Test 2: Insert a data on node7 that all other mysql s should synchronize
 #Test 3: Stop MySQL on node6 (simulated failure), insert a piece of data on node7, only synchronize the data on node9. After restarting node6 (simulated failure recovery), node6 and node8 should also synchronize the data.

#If all the above tests pass, MySQL Dual-master and dual-slave architecture is built and functionally correct.

9.5 Summary

MySQL Master-Slave Synchronization Multi-Master-Slave Architecture enables high availability of MySQL services, even if one MySQL master database is down, using another master database instead can avoid data asynchronization and service unavailability.

10. References

https://www.cnblogs.com/wuchangsoft/p/10374438.html

Tags: MySQL Database socket CentOS

Posted on Wed, 27 May 2020 21:17:41 -0700 by acac