MySQL master-slave replication architecture

MySQL master-slave replication architecture

1. Overview of MySQL Cluster

1. The main types of clusters

  • Highly available cluster (High Available Cluster, HA Cluster)
  • High-availability cluster refers to the connection of independent servers through special software to form a cluster that can provide failover (Fail Over) functions

2. How to measure high availability

Availability level (metric)Annual downtimedescriptionName
99%3.65 days/yearBasic available system2 of 9
99.9%8.76 hours/yearAvailable systems3 9s
99.99%52.6 minutes/yearHighly available system4 of 9
99.999%5.3 minutes/yearAnti-failure system5 9s
99.9999%32 seconds/yearFault-tolerant system6 of 9

Calculation method:

1年 = 365天 = 8760小时
99% = 8760 * 1% = 8760 * 0.01 = 87.6小时=3.65天
99.9 = 8760 * 0.1% = 8760 * 0.001 = 8.76小时
99.99 = 8760 * 0.0001 = 0.876小时 = 0.876 * 60 = 52.6分钟
99.999 = 8760 * 0.00001 = 0.0876小时 = 0.0876 * 60 = 5.26分钟

##3, commonly used cluster architecture

  • MySQL Replication
  • MySQL Cluster
  • MySQL Group Replication (MGR) 5.7.17 Multi-Master and One-Slave
  • MariaDB Galera Cluster
  • MHA|Keepalived|HeartBeat|Lvs, Haproxy and other technologies to build a highly available cluster

2. Introduction to MySQL Replication

1. What is MySQL replication

  • Replication can replicate data from one database server (master) to one to multiple database servers (slave)
  • By default, it belongs to asynchronous replication, so there is no need to maintain a long connection

2. MySQL replication principle (emphasis)

Simply put, the master writes the changes to the database**Binary log, The slave synchronizes these binary logs and performs according to these binary logsData playback operation**, to achieve data asynchronous synchronization.

master: master

slave: slave

Insert picture description here

Detailed Description:

When the master-slave synchronization configuration is completed:

  1. The IO thread on the slave side sends a request to the binlog dump thread on the master side
  2. master binlog dump threadGet the binary loginformation(File name and location information) Sent to the IO thread on the slave side
  3. The content obtained by the IO thread on the salve sideIn turnWrite to the relay log on the slave side, and record the name and location of the bin-log file on the master side in master.info
  4. The SQL thread on the salve side detects that the content in the relay log is updated, it will parse the updated content in the relay log, andPerform these actions, So as to achieve consistency with the master data
Extension: relay log relay log

**Function: **Record the binary log received from the (slave) server from the master (master) server.

**Scenario: **Used forMaster-slave replication

The master master server sends its own binary log to the slave slave server, and the slave first saves it in its ownRelay log, And then execute the sql in your local relay log to make the database changes consistent with the master.

How to turn it on?

Default relay logNot turned on, Can be opened by modifying the configuration file, as follows:

# vim my.cnf
[mysqld]
#指定二进制日志存放位置及文件名
relay-log=/mysql_3306/data/relaylog

3. MySQL replication architecture

(1) Dual machine hot backup (AB copy)

Insert picture description here

By default, the master accepts read and write requests, and the slave only accepts read requests to reduce the pressure on the master.

(2) Cascade copy

Insert picture description here

**Advantages: **Further share the pressure of reading

**Disadvantages: **slave1 fails, all subsequent cascaded slave servers will fail to synchronize

(Iii) Parallel replication (one master and multiple slaves)

Insert picture description here

**Advantages: **Solve the single point of failure of slave1 above, while also sharing the reading pressure

**Disadvantages: **Indirectly increase the pressure of the master (pressure of transferring binary logs)

(Iv) Double master copy (understand)

Insert picture description here

Features:

From the naming point of view, it seems that both masters can accept read and write requests, but in fact, often in the process of operation, only one of the masters will accept write requests at the same time, and the other will accept read requests.

Three, the establishment of MySQL master-slave replication (AB replication)

1. Traditional AB replication architecture (MS)

Note : When configuring the MySQL master-slave architecture, you must ensure that the database version is highly consistent, and the unified version is 5.7.31

2. Environmental planning

NumberingHost nameHost IP addressRole information
1master.itcast.cn10.1.1.10Master master server
2slave.itcast.cn10.1.1.100SLAVE slave server

3. Preparation before installation

Step 1: Clone two brand new database servers, MASTER/SLAVE

Step 2: First start MASTER, then start SLAVE, change the host name

Master:

# hostnamectl set-hostname master.itcast.cn
# su

Slave:

# hostnamectl set-hostname slave.itcast.cn
# su

Step 3: Change the static IP configuration, make the Master and Slave configurations consistent with the plan, and then close NetworkManager

Master:

# vim /etc/sysconfig/network-scripts/ifcfg-ens33
...
IPADDR=10.1.1.10
...

Slave:

# vim /etc/sysconfig/network-scripts/ifcfg-ens33
...
IPADDR=10.1.1.10
...

Master/Slave:

# systemctl stop NetworkManager
# systemctl disable NetworkManager

After setting up, restart the network, and then use MX to connect Master and Slave.

Step 4: Since the two machines are in a cluster architecture, they need to be connected to each other. Bind the host name and IP address to /etc/hosts

Master/Slave:

# vim /etc/hosts
10.1.1.10 master.itcast.cn
10.1.1.100 slave.itcast.cn

Step 5: Turn off the firewall and SELinux

# systemctl stop firewalld
# systemctl disable firewalld
# systemctl list-unit-files|grep firewalld
# setenforce 0
# sed -i '/^SELINUX=/cSELINUX=disabled' /etc/selinux/config

Step 6: Configure Yum source (Tencent source is recommended)

Configuration refer to previous notes (omitted)

Step 7: Time synchronization

# ntpdate 182.92.12.11

4. The core idea of ​​MySQL master-slave replication

Insert picture description here
  1. The slave must install the same version of mysql database software
  2. The master must be turned onBinary log; The slave must be turned onrelay log
  3. Server-id number of master and slaveNot consistent
  4. The slave side is configured to synchronize data to the master
  • A replication user must be created on the master side
  • Ensure master and slaveThe initial data is consistent
  • Configure master-slave replication (slave side)

5. The specific practice of MySQL master-slave replication

Step 1: Upload MySQL software package to Master and Slave

Step 2: Install, initialize and run mysql software on the Master side

Installation requirements:

OptionsValue (custom can also take the default form)
installation path/usr/local/mysql
Data path/usr/local/mysql/data
The port number3306

① Install MySQL software

# vim mysql.sh
#!/bin/bash
yum install libaio -y
tar -xf mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz
rm -rf /usr/local/mysql
mv mysql-5.7.31-linux-glibc2.12-x86_64 /usr/local/mysql
useradd -r -s /sbin/nologin mysql
rm -rf /etc/my.cnf
cd /usr/local/mysql
mkdir mysql-files
chown mysql:mysql mysql-files
chmod 750 mysql-files
bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql &> /root/password.txt
bin/mysql_ssl_rsa_setup --datadir=/usr/local/mysql/data
cp support-files/mysql.server /etc/init.d/mysqld
service mysqld start
echo 'export PATH=$PATH:/usr/local/mysql/bin' >> /etc/profile
source /etc/profile

# source mysql.sh
Shell script is actually a stacking of commands. A bunch of Linux commands are written in the same file and executed together.

② Security configuration

# mysql_secure_installation

③ Configure my.cnf (focus on enabling binary log)

# cd /usr/local/mysql
# vim my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
port=3306
log-error=/usr/local/mysql/data/master.err
log-bin=/usr/local/mysql/data/binlog		=>	  一定要开启二进制日志
server-id=10
character_set_server=utf8mb4			 	=>    utf8mb4相当于utf8升级版

配置完成后,重启mysqld服务
# service mysqld restart
# chkconfig --add mysqld
# chkconfig mysqld on

Step 3: Install mysql software from the server side in Slave (no initialization required)

① Install MySQL software

# vim mysql.sh
#!/bin/bash
yum install libaio -y
tar -xf mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz
rm -rf /usr/local/mysql
mv mysql-5.7.31-linux-glibc2.12-x86_64 /usr/local/mysql
useradd -r -s /sbin/nologin mysql
rm -rf /etc/my.cnf
cd /usr/local/mysql
mkdir mysql-files
chown mysql:mysql mysql-files
chmod 750 mysql-files
cp support-files/mysql.server /etc/init.d/mysqld
echo 'export PATH=$PATH:/usr/local/mysql/bin' >> /etc/profile
source /etc/profile
Compared with the installation and configuration of MySQL on the master server, the slave server does not need to perform initialization operations because its data will come from the master server in the future.

② Configure the my.cnf file

# cd /usr/local/mysql
# vim my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
port=3310
log-error=/usr/local/mysql/data/slave.err
relay-log=/usr/local/mysql/data/relaylog			=>    	开启中继日志
server-id=100
character_set_server=utf8mb4

③ Synchronize the data directory of the master server to the slave server

a. Stop mysqld in the MASTER server

# service mysqld stop

b. Delete the auto.cnf file in the /usr/local/mysql/data directory in the MASTER server

# rm -rf /usr/local/mysql/data/auto.cnf
Without installing a mysql software, the data directory will generate an auto.cnf file, which is a unique number, which is equivalent to the ID number of each of us.

c. Copy the data directory in /usr/local/mysql on the MASTER server to the /usr/local/mysql directory on the SLAVE slave server

# rsync -av /usr/local/mysql/data [email protected]:/usr/local/mysql/

d. After the synchronization is complete, start mysqld in the master server and the slave server

# service mysqld start

Solutions to common problems

☆ Common problem 1: MySQL cannot be started

# service mysqld start
Redirecting to /bin/systemctl start mysqld.service
Failed to start mysqld.service: Unit not found.

The main reason for the above problems is that there is no mysqld file in the /etc/init.d directory. In other words, you do not have the cp mysql.server script

# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
记住:/etc/init.d目录中的文件叫什么名字,我们service的时候就应该使用什么名字
# service mysqld start

☆ Common mistake 2: MySQL is not installed in the /usr/local/mysql directory, and the service cannot be started

# service mysqld start
无法启动的原因可能是你的MySQL并没有安装在/usr/local/mysql目录中,因为/etc/init.d/mysqld脚本中的basedir与datadir默认指向的都是/usr/local/mysql

So if we change the installation location of mysql, we must change the directory basedir and datadir in the /etc/init.d/mysqld script

# vim /etc/init.d/mysqld
basedir=你的安装路径
datadir=你的安装路径/data

☆ Common problem 3: My.cnf configuration file causes mysql to fail to start

# vim my.cnf
[mysqld]
basedir=/usr/local/mysql			=>    安装路径
datadir=/usr/local/mysql/data		=>    数据目录
socket=/tmp/mysql.sock				=>    GLIBC默认就是/tmp/mysql.sock
port=3310
log-error=/usr/local/mysql/slave.err	=>	 错误日志到底放在哪里
relay-log=/usr/local/mysql/data/relaylog
server-id=100
character_set_server=utf8mb4

Start error:

Starting MySQL.2020-08-31T07:17:06.554270Z mysqld_safe error: log-error set to '/usr/local/mysql/slave.err', however file don't exists. Create writable for user 'mysql'.
 ERROR! The server quit without updating PID file (/usr/local/mysql/data/slave.itcast.cn.pid).

The main reason for the above problems is that the user mysql does not have write permissions to the /usr/local/mysql folder

# ll -d /usr/local/mysql
drwxr-xr-x 11 7161 31415 174 Aug 31 15:16 /usr/local/mysql
发现文件拥有者位置与所属组位置都是两个数字,正常应该是文件拥有者的名称与文件所属组的名称。但是由于GLIBC已经提前打包了,我们解压后,如果在我们系统中,找不到原文件对应的文件拥有着与所属组,则以两个数字代替文件拥有者与所属组的显示。

solution:

Solution 1: It is recommended to put the error log in the data directory

# vim my.cnf
...
log-error=/usr/local/mysql/data/slave.err

Option 2: directly change the permissions of the /usr/local/mysql directory

# chown -R mysql.mysql /usr/local/mysql

④ Configure master-slave synchronization

a. Create an account in the MASTER master server, dedicated to data synchronization

MySQL5.7 and the following versions:

mysql> grant replication slave on *.* to 'slave'@'10.1.1.%' identified by '123';

In the new version of MySQL:

mysql> create user 'slave'@'10.1.1.%' identified by '123';
mysql> grant replication slave on *.* to 'slave'@'10.1.1.%';
mysql> flush privileges;

b. Lock the table in the MASTER, and then view the name and location of the binary file

mysql> flush tables with read lock;
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 |      597 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+

c. In the SLAVE slave server, use change master to specify the master server, and realize data synchronization

mysql> change master to master_host='10.1.1.10',master_user='slave',master_password='123',master_port=3306,master_log_file='binlog.000002',master_log_pos=597;

master_host:主机的IP地址
master_user:主机的user账号
master_password:主机的user账号密码
master_port:主机MySQL的端口号
master_log_file:二进制日志文件名称
master_log_pos:二进制日志文件位置
Tip: What should I do if I can’t remember the change master to statement in master-slave replication? Answer: Ask for help, mysql> help change master to;
CHANGE MASTER TO
MASTER_HOST='master2.example.com',
MASTER_USER='replication',
MASTER_PASSWORD='password',
MASTER_PORT=3306,
MASTER_LOG_FILE='master2-bin.001',
MASTER_LOG_POS=4,
MASTER_CONNECT_RETRY=10;

d. Start slave data synchronization

mysql> start slave;
mysql> show slave status\G
Insert picture description here

Solutions to common problems

When configuring the master-slave, you usually encounter errors, most of which are the wrong change master to statement (80%). The solution:

mysql> stop slave;
mysql> reset slave;
mysql> change master to master_host='10.1.1.10',master_user='slave',master_password='123',master_port=3306,master_log_file='binlog.000002',master_log_pos=597;

mysql> start slave;

⑤ Unlock the master master server

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

to sum up:

  1. Master-slave replication must ensure that the two database instancesserver-id is inconsistent
  2. Main serverBinary log must be turned on; From the serverRelay log must be turned on
  3. Master-slave replicationMust ensure that the initial data is consistent
  4. The master server must give the slave serverCreate a copy user and grant copy permissions
  5. Master—>Slave architecture,The master writer will synchronize to the slave;andSlave write will not sync to master

6. Solution for SLAVE to accidentally write data from the server

Under normal conditions:

MASTER can both read and write. But the SLAVE slave server can only perform read operations. Once we write data in the slave server, the master-slave architecture will fail.

SLAVE:

# show slave status\G
Insert picture description here

Encountered the above problem: if the number is small, you can also solve it by skipping the current sentence. But if too much data is written from the server, the above architecture must be rebuilt!

solution:

ask:

If the data is directly changed to the slave server due to human operation or other reasons, the data synchronization fails, how to solve it?

Answer: You can sql_slave_skip_countertemporarily skip transactions for processing through variables

SET GLOBAL sql_slave_skip_counter = N  	N代表跳过N个事务
举例说明:
mysql> SET GLOBAL sql_slave_skip_counter=1;
mysql> stop slave;
mysql> start slave;
注意:
1. 跳过事务应该在slave上进行
2. 传统的AB复制方式可以使用变量:sql_slave_skip_counter,基于GTIDs的方式不支持

4. AB replication architecture (MS) based on GTIDs

1. Overview of GTIDs

  • What are GTIDs and what are their characteristics?

GTIDs (Global transaction identifiers)Global transaction identifier, Is a new technology added to mysql 5.6

When using GTIDs, every transaction can beIdentify and track

When a new slave is added or the master identity or role needs to be migrated to the slave when a failure occurs, there is no need to consider which binary log and which position value it is.Greatly simplified related operations

GTIDs are completely transaction-based, soDoes not support MYISAM storage engine

GTID consists of source_id and transaction_id:

1) source_id comes from server_uuid, which can be seen in auto.cnf

2) Transation_id is a serial number, which is automatically generated.

  • What are the restrictions on using GTIDs?
  1. Does not support non-transaction engine (MyISAM), because it may cause multiple gtid to be assigned to the same transaction
  2. create table… select statement is not supported (the main library syntax error is reported)
  3. The create/drop temporary table statement is not supported
  4. The enforce-gtid-consistency parameter must be used
  5. sql-slave-skip-counter does not support (traditional way of skipping errors)
  6. In the GTID replication environment, it must be required to turn on and GTID or turn off GTID uniformly
  7. Before mysql 5.6.7, usemysql_upgradeThere will be a problem with the command

2. Master-slave replication based on GTIDs

In a production environment, MySQL 5.6 used in most cases is basically upgraded from version 5.5 or lower, which means that the previous mysql replication solution was deployed in a traditional way and is already running. Therefore, next we will use the existing environment to upgrade to GITDs-based Replication

〇Thinking

  1. Modify the configuration file to support GTIDs (master+slave)
  2. Restart the database (master + slave)
  3. In order to ensure data consistency, master and slave are set to read-only mode (master + slave)
  4. Reconfigure synchronization from the server (From)

3. Master-slave replication practice based on GTIDs

① Modify the configuration file to support GTIDs

MASTER => my.cnf

# vim my.cnf
...
gtid-mode=on
log-slave-updates=1
enforce-gtid-consistency

SLAVE => my.cnf

# rm -rf data/binlog.*
# vim my.cnf
...
log-bin=/usr/local/mysql/data/binlog	=>   必须要开启二进制
gtid-mode=on
log-slave-updates=1
enforce-gtid-consistency
skip-slave-start	 =>    当MASTER主服务器GTIDs没有启动时,跳过SLAVE服务器的启动
Note:
1) To enable GITDs, you need to configure gtid-mode, log-bin, log-slave-updates, and enforce-gtid-consistency on both master and slave (this parameter was -disable-gtid-unsafe-statement before 5.6.9 )
2) Secondly, the slave also needs to increase the skip-slave-start parameter, the purpose is to start, do not get the slave up, you need to do some configuration
3) Based on GTIDs replication from the server must open the binary log!

② Restart the mysqld service

# service mysqld restart

③ Master-slave configuration read-only mode

mysql> set @@global.read_only=ON;

④ SLAVE reconfigure change master to

mysql> stop slave;
mysql> reset slave;
mysql> change master to master_host='10.1.1.10',master_user='slave',master_password='123',master_port=3306,master_auto_position=1;

注意:
1.确保有复制用户
2.主要区别于传统复制的参数是:master_auto_position=1

mysql> start slave;
mysql> show slave status\G

⑤ Turn off the read-only mode of the master-slave server

mysql> set @@global.read_only=OFF;

Test verification (write some data to the main server, verify it)

4. Solution for SLAVE to accidentally write data from the server

Method 1: Skip the transaction

指定需要跳过的GTIDs编号
SET GTID_NEXT='aaa-bbb-ccc-ddd:N';
开始一个空事务
BEGIN;
COMMIT;
使用下一个自动生成的全局事务ID。
SET GTID_NEXT='AUTOMATIC';

举例说明:
mysql> stop slave;
mysql> SET @@SESSION.GTID_NEXT= '13e36f00-eb70-11ea-91a8-000c29d1f40a:1'/*!*/;
mysql> BEGIN;
mysql> commit;
mysql> SET @@SESSION.GTID_NEXT= 'AUTOMATIC';	
mysql> start slave;
mysql> show slave status\G

说明:需要跳过哪个事务,需要手动查看relaylog文件得到
[[email protected] data]# ../bin/mysqlbinlog relay.000003|less
。。。。
# at 756
#181015 12:04:45 server id 10  end_log_pos 817 CRC32 0x5374f49e         GTID [commit=yes]
SET @@SESSION.GTID_NEXT= '044e6392-cf9b-11e8-a748-000c294ca304:3'/*!*/;

Method 2: Re-synchronize the data directory and re-change master to...

Skip transaction

指定需要跳过的GTIDs编号
SET GTID_NEXT='aaa-bbb-ccc-ddd:N';
开始一个空事务
BEGIN;
COMMIT;
使用下一个自动生成的全局事务ID。
SET GTID_NEXT='AUTOMATIC';

举例说明:
mysql> stop slave;
mysql> SET @@SESSION.GTID_NEXT= '13e36f00-eb70-11ea-91a8-000c29d1f40a:1'/*!*/;
mysql> BEGIN;
mysql> commit;
mysql> SET @@SESSION.GTID_NEXT= 'AUTOMATIC';	
mysql> start slave;
mysql> show slave status\G

说明:需要跳过哪个事务,需要手动查看relaylog文件得到
[[email protected] data]# ../bin/mysqlbinlog relay.000003|less
。。。。
# at 756
#181015 12:04:45 server id 10  end_log_pos 817 CRC32 0x5374f49e         GTID [commit=yes]
SET @@SESSION.GTID_NEXT= '044e6392-cf9b-11e8-a748-000c294ca304:3'/*!*/;

Method 2: Re-synchronize the data directory and re-change master to...