MySQL 主从复制之多线程复制
目录[*]一、MySQL 多线程复制的背景
[*]二、MySQL 5.5 主从复制
[*]1、原理
[*]2、部署主从复制
[*]2.1、主节点安装配置MySQL 5.5
[*]2.2、从节点安装配置MySQL 5.5
[*]3、检查主从库 server_id 和 log_bin 配置
[*]4、创建主从复制用户
[*]5、获取主库的二进制日志文件和位置
[*]6、配置从库连接主库参数并启动从库复制进程
[*]7、验证主从复制
[*]7.1、查看主库从库数据
[*]7.2、主库创建表并插入数据
[*]7.3、从库执行查询操作
[*]8、MySQL 5.5 单线程体现
[*]三、MySQL 5.6 基于 schema（库级别） 的并行复制
[*]1、原理
[*]2、存在的问题
[*]3、部署主从复制
[*]3.1、主节点安装配置 MySQL 5.6
[*]3.2、从节点安装配置 MySQL 5.6
[*]4、检查主从库server_id、log_bin、 slave_parallel_workers
[*]5、创建主从复制用户
[*]6、获取主库的二进制日志文件和位置
[*]7、配置从库连接主库参数并启动从库复制进程
[*]8、验证主从复制
[*]8.1、查看主库从库数据
[*]8.2、主库创建表并插入数据
[*]8.3、从库执行查询操作
[*]9、主节点单库多表 sysbench 写压测 从节点测试延迟
[*]9.1、安装 sysbench 压测工具
[*]9.2、创建测试数据库
[*]9.3、使用 sysbench 准备测试数据
[*]9.4、运行写操作测试
[*]9.5、从库采集延迟时间
[*]9.6、事务处理速度（TPS，Transactions Per Second）和查询处理速度（QPS，Queries Per Second）
[*]9.7、清除测试数据
[*]10、主节点多库多表 sysbench 写压测 从节点测试延迟
[*]10.1、创建测试数据库
[*]10.2、使用 sysbench 准备测试数据
[*]10.3、运行写操作测试
[*]10.4、从库采集延迟时间
[*]四、MySQL 5.7 基于组提交的并行复制
[*]1、原理
[*]2、部署主从复制
[*]2.1、主节点安装配置 MySQL 5.7
[*]2.2、从节点安装配置 MySQL 5.7
[*]3、检查主从库server_id、log_bin、 slave_parallel_workers、slave-parallel-type
[*]4、创建主从复制用户
[*]5、获取主库的二进制日志文件和位置
[*]6、配置从库连接主库参数并启动从库复制进程
[*]7、验证主从复制
[*]7.1、查看主库从库数据
[*]7.2、主库创建表并插入数据
[*]7.3、从库执行查询操作
[*]8、主节点单库多表 sysbench 写压测 从节点测试延迟
[*]8.1、安装 sysbench 压测工具
[*]8.2、创建测试数据库
[*]8.3、使用 sysbench 准备测试数据
[*]8.4、运行写操作测试
[*]8.5、从库采集延迟时间
[*]8.6、事务处理速度（TPS，Transactions Per Second）和查询处理速度（QPS，Queries Per Second）
[*]8.7、清除测试数据
[*]五、MySQL 8.0 基于 WriteSet 的并行复制
[*]1、概述
[*]2、核心原理
[*]Master 端
[*]Slave 端
[*]3、MySQL 8.0 相关参数
[*]3.1、binlog_transaction_dependency_tracking
[*]3.2、transaction_write_set_extraction
[*]3.3、binlog_transaction_dependency_history_size
[*]4、WriteSet 依赖检测条件
[*]5、基于 COMMIT_ORDER，WRITESET_SESSION，WRITESET 方案的压测
[*]6、开启并行复制
[*]6.1、主库
[*]6.2、从库
一、MySQL 多线程复制的背景
MySQL 的主从复制延迟一直是受开发者最为关注的问题之一，MySQL 从 5.6 版本开始追加了并行复制功能，目的就是为了改善复制延迟问题，并行复制称为enhanced multi-threaded slave（简称MTS）。
[*]MySQL 的复制是基于 binlog 的。
[*]MySQL 复制包括两部分，从库中有两个线程：IO 线程和 SQL 线程。
[*]IO 线程主要是用于拉取接收 Master 传递过来的 binlog，并将其写入到 relay log.
[*]SQL 线程主要负责解析 relay log，并应用到 slave 中。
[*]IO 和 SQL 线程都是单线程的，然而master却是多线程的，所以难免会有延迟，为了解决这个问题，多线程应运而生了。
[*]IO 没必要多线程，因为 IO 线程并不是瓶颈。
[*]SQL 多线程，目前最新的5.6，5.7，8.0 都是在 SQL 线程上实现了多线程，来提升 slave 的并发度，减少复制延迟。
二、MySQL 5.5 主从复制
1、原理
[*]master 节点上的binlogdump 线程，在slave 与其正常连接的情况下，将binlog 发送到slave 上。
[*]slave 节点的I/O Thread ，通过读取master 节点binlog 日志名称以及偏移量信息将其拷贝到本地relay log 日志文件。
[*]slave 节点的SQL Thread，该线程读取relay log 日志信息，将在master 节点上提交的事务在本地回放，达到与主库数据保持一致的目的。
2、部署主从复制
主库192.168.112.10从库192.168.112.20MySQL版本5.5.622.1、主节点安装配置MySQL 5.5
cd /usr/local
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.5.62-linux-glibc2.12-x86_64.tar.gz
tar -zxvf mysql-5.5.62-linux-glibc2.12-x86_64.tar.gz
ln -s mysql-5.5.62-linux-glibc2.12-x86_64 mysql
groupadd mysql
useradd -r -g mysql mysql
cd /usr/local/mysql
chown -R mysql:mysql .
mkdir -p /usr/local/mysql/data
chown -R mysql:mysql /usr/local/mysql/data
./scripts/mysql_install_db --user=mysql --datadir=/usr/local/mysql/data
cat > /etc/my.cnf << EOF
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /tmp/mysql.sock
pid-file = /usr/local/mysql/data/mysqld.pid
user = mysql
port = 3306
# 二进制日志配置
server-id = 1
log-bin = mysql-bin
binlog-format = row
expire_logs_days = 10
# 主从复制配置
log-slave-updates = 1
read-only = 0
EOF
cat > /etc/systemd/system/mysqld.service << EOF
Description=MySQL 5.5 Database Server
After=network.target
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
Restart=on-failure
WantedBy=multi-user.target
EOF
systemctl daemon-reload && systemctl start mysqld && systemctl enable mysqld
echo 'export PATH="/usr/local/mysql/bin:$PATH"' >> /etc/profile.d/mysql.sh
source /etc/profile
mysqladmin -uroot password '123'2.2、从节点安装配置MySQL 5.5
cd /usr/local
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.5.62-linux-glibc2.12-x86_64.tar.gz
tar -zxvf mysql-5.5.62-linux-glibc2.12-x86_64.tar.gz
ln -s mysql-5.5.62-linux-glibc2.12-x86_64 mysql
groupadd mysql
useradd -r -g mysql mysql
cd /usr/local/mysql
chown -R mysql:mysql .
mkdir -p /usr/local/mysql/data
chown -R mysql:mysql /usr/local/mysql/data
./scripts/mysql_install_db --user=mysql --datadir=/usr/local/mysql/data
cat > /etc/my.cnf << EOF
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /tmp/mysql.sock
pid-file = /usr/local/mysql/data/mysqld.pid
user = mysql
port = 3306
# 二进制日志配置
server-id = 1
log-bin = mysql-bin
binlog-format = row
expire_logs_days = 10
# 主从复制配置
log-slave-updates = 1
read-only = 0
EOF
cat > /etc/systemd/system/mysqld.service << EOF
Description=MySQL 5.5 Database Server
After=network.target
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
Restart=on-failure
WantedBy=multi-user.target
EOF
systemctl daemon-reload && systemctl start mysqld && systemctl enable mysqld
echo 'export PATH="/usr/local/mysql/bin:$PATH"' >> /etc/profile.d/mysql.sh
source /etc/profile
mysqladmin -uroot password '123'select version();
mysql -V
3、检查主从库 server_id 和 log_bin 配置
show variables like 'server_id';
show variables like 'log_bin';主库从库4、创建主从复制用户
主库
#登录数据库
mysql -uroot -p123
#创建slave用户
grant replication slave on *.* to slave@'192.168.112.%' identified by '123';
flush privileges;
[*]grant replication slave on *.*: 授予 replication slave 权限，允许用户从主库复制数据。*.* 表示所有数据库和所有表。
[*]to slave@'192.168.112.%': 指定用户名为 slave，允许从 192.168.112.% 子网内的任何IP地址连接到主库。
[*]identified by '123': 设置用户的密码为 123。
5、获取主库的二进制日志文件和位置
# 记录下 File 和 Position 的值
show master status;
6、配置从库连接主库参数并启动从库复制进程
change master to
master_host='192.168.112.10',
master_user='slave',
master_password='123',
master_log_file='mysql-bin.000004',
master_log_pos=332;
start slave;
show slave status\G;
7、验证主从复制
7.1、查看主库从库数据
目前数据一致
show databases;
use test ; show tables;
7.2、主库创建表并插入数据
create table test_table (id int auto_increment primary key,name varchar(255),created_at timestamp default current_timestamp);
insert into test_table (name , created_at) values ('Data1', now());
insert into test_table (name , created_at) values ('Data2', now());
insert into test_table (name , created_at) values ('Data3', now());7.3、从库执行查询操作
show tables;
select * from test_table;
8、MySQL 5.5 单线程体现
# 从库
show processlist;
这里有两个系统用户线程：
[*]Id 1 是IO线程，负责从主库读取二进制日志并写入从库的中继日志。
[*]Id 2 是SQL线程，负责从中继日志中读取事件并应用到从库。
三、MySQL 5.6 基于 schema（库级别） 的并行复制
如果在MySQL 5.6 版本开启并行复制功能（slave_parallel_workers > 0），那么SQL 线程就变为了coordinator 线程。但是其并行只是基于schema的，也就是基于库的。如果用户的MySQL数据库实例中存在多个schema且schema下表数量较少，对于从服务器复制的速度的确可以有比较大的帮助。
1、原理
通过配置参数 slave_parallel_workers = n 开启并行复制，原来的单个SQL线程的功能被拆分成了两个部分：一个 Coordinator 线程和多个 Worker 线程。
coordinator线程主要负责两部分内容：
[*]若判断可以并行执行，那么选择worker线程执行事务的二进制日志
[*]若判断不可以并行执行，如该操作是DDL，或者是事务跨schema操作，则等待所有的worker线程执行完成之后再执行当前的日志。
这意味着coordinator 线程并不是仅将日志发送给worker 线程，自己也可以回放日志，但是所有可以并行的操作交付由worker 线程完成。
Worker线程作用：
[*]WorkThread 线程负责实际应用中继日志中的事务。每个 WorkThread 线程负责处理特定数据库（Schema）的事务。
[*]可以通过配置参数 slave_parallel_workers 来设置 WorkThread 线程的数量。默认值为0，表示不启用并行复制。
2、存在的问题
基于schema级别的并行复制存在一个问题，schema级别的并行复制效果并不高，如果用户实例有很少的库和较多的表，那么并行回放效果会很差，甚至性能会比原来的单线程更差，但是日常维护中其实单个实例的的事务处理相对集中在一个 DB 上。因此单库多表是比多库多表更为常见的一种情形。
这种并行复制的模式,只有在实例中有多个 DB，且 DB 的事务都相对繁忙的情况下才会有较高的并行度。
3、部署主从复制
3.1、主节点安装配置 MySQL 5.6
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.6.40-linux-glibc2.12-x86_64.tar.gz
tar xzvf mysql-5.6.40-linux-glibc2.12-x86_64.tar.gz
mkdir /application
mv mysql-5.6.40-linux-glibc2.12-x86_64 /application/mysql-5.6.40
ln -s /application/mysql-5.6.40/ /application/mysql
cd /application/mysql/support-files/
\cp my-default.cnf /etc/my.cnf
cp mysql.server /etc/init.d/mysqld
cd /application/mysql/scripts
useradd mysql -s /sbin/nologin -M
yum -y install autoconf
./mysql_install_db --user=mysql --basedir=/application/mysql--data=/application/mysql/data
echo 'export PATH="/application/mysql/bin:$PATH"' >> /etc/profile.d/mysql.sh
source /etc/profile
sed -i 's#/usr/local#/application#g' /etc/init.d/mysqld /application/mysql/bin/mysqld_safe
sed -i '/^# basedir = /a\basedir = /application/mysql/' /etc/my.cnf
sed -i '/^# datadir = /a\datadir = /application/mysql/data/' /etc/my.cnf
cat >> /usr/lib/systemd/system/mysqld.service <<EOF
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=https://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
WantedBy=multi-user.target
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
EOF
cat >> /etc/my.cnf << EOF
# 主从复制配置
server-id = 1
log-bin = mysql-bin
binlog-format = ROW
expire_logs_days = 10
# 其他配置
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
EOF
systemctl daemon-reload && systemctl start mysqld && systemctl enable mysqld
mysqladmin -uroot password '123'临时密码
2.2、从节点安装配置 MySQL 5.7
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.6.40-linux-glibc2.12-x86_64.tar.gz
tar xzvf mysql-5.6.40-linux-glibc2.12-x86_64.tar.gz
mkdir /application
mv mysql-5.6.40-linux-glibc2.12-x86_64 /application/mysql-5.6.40
ln -s /application/mysql-5.6.40/ /application/mysql
cd /application/mysql/support-files/
\cp my-default.cnf /etc/my.cnf
cp mysql.server /etc/init.d/mysqld
cd /application/mysql/scripts
useradd mysql -s /sbin/nologin -M
yum -y install autoconf
./mysql_install_db --user=mysql --basedir=/application/mysql--data=/application/mysql/data
echo 'export PATH="/application/mysql/bin:$PATH"' >> /etc/profile.d/mysql.sh
source /etc/profile
sed -i 's#/usr/local#/application#g' /etc/init.d/mysqld /application/mysql/bin/mysqld_safe
sed -i '/^# basedir = /a\basedir = /application/mysql/' /etc/my.cnf
sed -i '/^# datadir = /a\datadir = /application/mysql/data/' /etc/my.cnf
cat >> /usr/lib/systemd/system/mysqld.service <<EOF
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=https://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
WantedBy=multi-user.target
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
EOF
cat >> /etc/my.cnf << EOF
# 主从复制配置
server-id = 2
relay-log = mysql-relay-bin
log-bin = mysql-bin
read-only = 1
# 并行复制配置
slave_parallel_workers = 4
EOF
systemctl daemon-reload && systemctl start mysqld && systemctl enable mysqld
mysqladmin -uroot password '123'临时密码
3、检查主从库server_id、log_bin、 slave_parallel_workers、slave-parallel-type
show variables like 'version';show variables like 'server_id';
show variables like 'log_bin';show variables like 'slave_parallel_type';
4、创建主从复制用户
主库
#登录数据库
mysql -uroot -p123
#创建slave用户
grant replication slave on *.* to slave@'192.168.112.%' identified by '123';
flush privileges;select user,host,authentication_string from mysql.user;
[*]grant replication slave on *.*: 授予 replication slave 权限，允许用户从主库复制数据。*.* 表示所有数据库和所有表。
[*]to slave@'192.168.112.%': 指定用户名为 slave，允许从 192.168.112.% 子网内的任何IP地址连接到主库。
[*]identified by '123': 设置用户的密码为 123。
5、获取主库的二进制日志文件和位置
# 记录下 File 和 Position 的值
show master status;
6、配置从库连接主库参数并启动从库复制进程
# 记录下 File 和 Position 的值
show master status;
7、验证主从复制
7.1、查看主库从库数据
目前数据一致
change master to
master_host='192.168.112.10',
master_user='slave',
master_password='123',
master_log_file='mysql-bin.000001',
master_log_pos=552;
start slave;
show slave status\G;
7.2、主库创建表并插入数据
create table test_table (id int auto_increment primary key,name varchar(255),created_at timestamp default current_timestamp);
insert into test_table (name , created_at) values ('Data1', now());
insert into test_table (name , created_at) values ('Data2', now());
insert into test_table (name , created_at) values ('Data3', now());7.3、从库执行查询操作
use test;show tables;
select * from test_table;
8、主节点单库多表 sysbench 写压测 从节点测试延迟
8.1、安装 sysbench 压测工具
show tables;
select * from test_table;8.2、创建测试数据库
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
yum -y install sysbench
sysbench --version8.3、使用 sysbench 准备测试数据
与MySQL 5.6 版本的单库多表写压测一样的数据形成对照
# 主节点
create database tssysbench;
[*]/usr/share/sysbench/ 目录下有OLTP 基准测试的脚本文件，我们使用的是写测试模拟并发写入情况
[*]--mysql-host=192.168.112.10 --mysql-port=3306 \--mysql-user=root --mysql-password='123' \数据库的用户和密码等信息
[*]--mysql-db=tssysbench --tables=20 --table_size=10000：这一串的意思，就是说在tssysbench这个库里，构造20个测试表，每个测试表里构造1万条测试数据，测试表的名字会是类似于sbtest1，sbtest2这个样子的
[*]--db-driver=mysql：代表数据库驱动
[*]--time=200：这个就是说连续访问200秒
[*]--threads=64：这个就是说用64个线程模拟并发访问
[*]--report-interval=10：这个就是说每隔10秒输出一下压测情况
[*]最后有一个prepare，意思是参照这个命令的设置去构造出来我们需要的数据库里的数据，他会自动创建20个测试表，每个表里创建1万条测试数据。一共20w条数据
8.4、运行写操作测试
sysbench /usr/share/sysbench/oltp_write_only.lua \
--mysql-host=192.168.112.10 --mysql-port=3306 \
--mysql-user=root --mysql-password='123' \
--mysql-db=tssysbench --db-driver=mysql \
--tables=20 --table-size=10000 --report-interval=10 \
--threads=64 --time=200 \
prepare
每10s压测情况报告（取第一个10s做参数详解）：
[*]thds: 64，这个意思就是有64个线程在压测
[*]tps: 4502.30，这个意思就是每秒执行了4502.30个事务
[*]qps: 27035.08，意思是每秒执行了27035.08个请求
[*](r/w/o: 0.00/18025.39/9009.69)，意思是每秒27035.08个请求中有18025.39个写请求、9009.69个其他请求，是对QPS进行了rwo拆解
[*]lat (ms,95%)：23.52，意思是95%的请求延迟都在23.52ms以下
[*]err/s: 0.10 reconn/s: 0.00 ，意思是每秒有0.1个请求是失败的，发生了0次网络重连
结果参数详解：
[*]SQL statistics: SQL统计信息
[*]read:0， 0次读操作
[*]write:3352319， 3352319次写操作
[*]other:1676162，1676162次其他操作
[*]total:5028481，5028481次总操作数
[*]transactions:838077 (4189.48 per sec)，在整个测试过程中，完成了 838,077 个事务，平均每秒完成 4,189.48 个事务（tps）。
[*]queries:5028481 (25136.95 per sec.)，在整个测试过程中，执行了 5,028,481 次查询，平均每秒执行 25,136.95 次查询（qps)。
[*]ignored errors:8(0.04 per sec），在整个测试过程中，忽略了 8 个错误，平均每秒忽略 0.04 个错误
[*]reconnects:0(0.00 per sec.),在整个测试过程中，没有发生任何重连，平均每秒重连次数为 0。
[*]General statistics: 总体统计信息
[*]total time:200.0429s，测试总用时200.0429 秒
[*]total number of events:838077，在测试期间发生的总事件数838,077，即完成的事务数。
[*]Latency (ms):延迟统计信息
[*]min: 1.25 最小延迟1.25 毫秒
[*]avg: 15.27 平均延迟15.27 毫秒
[*]max: 153.27 最大延迟 153.27 毫秒
[*]95th percentile: 25.28 95% 的事件的响应时间不超过 25.28 毫秒
[*]sum: 12798796.95 所有事件的总延迟事件为 12798796.95 毫秒
[*]Threads fairness: 线程公平性
[*]events (avg/stddev): 13094.9531/66.16 每个线程平均完成的事件数及其标准偏差。平均每个线程完成了 13,094.9531 个事件，标准偏差为 66.16
[*]execution time (avg/stddev): 199.9812/0.01每个线程平均执行的时间及其标准偏差。平均每个线程执行了 199.9812 秒，标准偏差为 0.01
8.5、从库采集延迟时间
collect_delay.sh
#!/bin/bash# 从库连接信息SLAVE_MYSQL_USER="root"SLAVE_MYSQL_PASSWORD="123"# 采集次数COLLECT_COUNT=10# 记录延迟时间的数组DELAY_TIMES=()for ((i=1; i
页:
[1]