一、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、部署主从复制
2.1、主节点安装配置 MySQL 5.5
cd /usr/localwget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.5.62-linux-glibc2.12-x86_64.tar.gztar -zxvf mysql-5.5.62-linux-glibc2.12-x86_64.tar.gzln -s mysql-5.5.62-linux-glibc2.12-x86_64 mysqlgroupadd mysqluseradd -r -g mysql mysqlcd /usr/local/mysqlchown -R mysql:mysql .mkdir -p /usr/local/mysql/datachown -R mysql:mysql /usr/local/mysql/data./scripts/mysql_install_db --user=mysql --datadir=/usr/local/mysql/datacat > /etc/my.cnf << EOF[mysqld]basedir = /usr/local/mysqldatadir = /usr/local/mysql/datasocket = /tmp/mysql.sockpid-file = /usr/local/mysql/data/mysqld.piduser = mysqlport = 3306# 二进制日志配置server-id = 1log-bin = mysql-binbinlog-format = rowexpire_logs_days = 10
# 主从复制配置log-slave-updates = 1read-only = 0EOFcat > /etc/systemd/system/mysqld.service << EOF[Unit]Description=MySQL 5.5 Database ServerAfter=network.target
[Service]User=mysqlGroup=mysqlExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnfRestart=on-failure
[Install]WantedBy=multi-user.targetEOFsystemctl daemon-reload && systemctl start mysqld && systemctl enable mysqldecho 'export PATH="/usr/local/mysql/bin:$PATH"' >> /etc/profile.d/mysql.shsource /etc/profilemysqladmin -uroot password '123'
复制代码
2.2、从节点安装配置 MySQL 5.5
cd /usr/localwget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.5.62-linux-glibc2.12-x86_64.tar.gztar -zxvf mysql-5.5.62-linux-glibc2.12-x86_64.tar.gzln -s mysql-5.5.62-linux-glibc2.12-x86_64 mysqlgroupadd mysqluseradd -r -g mysql mysqlcd /usr/local/mysqlchown -R mysql:mysql .mkdir -p /usr/local/mysql/datachown -R mysql:mysql /usr/local/mysql/data./scripts/mysql_install_db --user=mysql --datadir=/usr/local/mysql/datacat > /etc/my.cnf << EOF[mysqld]basedir = /usr/local/mysqldatadir = /usr/local/mysql/datasocket = /tmp/mysql.sockpid-file = /usr/local/mysql/data/mysqld.piduser = mysqlport = 3306# 主从复制配置server-id = 2relay-log = mysql-relay-binlog-bin = mysql-binread-only = 1EOFcat > /etc/systemd/system/mysqld.service << EOF[Unit]Description=MySQL 5.5 Database ServerAfter=network.target
[Service]User=mysqlGroup=mysqlExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnfRestart=on-failure
[Install]WantedBy=multi-user.targetEOFsystemctl daemon-reload && systemctl start mysqld && systemctl enable mysqldecho 'export PATH="/usr/local/mysql/bin:$PATH"' >> /etc/profile.d/mysql.shsource /etc/profilemysqladmin -uroot password '123'
复制代码
select version();mysql -V
复制代码
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 tomaster_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());
复制代码
8、MySQL 5.5 单线程体现
这里有两个系统用户线程：
三、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 线程主要负责两部分内容：
这意味着 coordinator 线程并不是仅将日志发送给 worker 线程，自己也可以回放日志，但是所有可以并行的操作交付由 worker 线程完成。
Worker 线程作用：
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.gztar xzvf mysql-5.6.40-linux-glibc2.12-x86_64.tar.gzmkdir /applicationmv mysql-5.6.40-linux-glibc2.12-x86_64 /application/mysql-5.6.40ln -s /application/mysql-5.6.40/ /application/mysqlcd /application/mysql/support-files/\cp my-default.cnf /etc/my.cnfcp mysql.server /etc/init.d/mysqldcd /application/mysql/scriptsuseradd mysql -s /sbin/nologin -Myum -y install autoconf./mysql_install_db --user=mysql --basedir=/application/mysql --data=/application/mysql/dataecho 'export PATH="/application/mysql/bin:$PATH"' >> /etc/profile.d/mysql.shsource /etc/profilesed -i 's#/usr/local#/application#g' /etc/init.d/mysqld /application/mysql/bin/mysqld_safesed -i '/^# basedir = /a\basedir = /application/mysql/' /etc/my.cnfsed -i '/^# datadir = /a\datadir = /application/mysql/data/' /etc/my.cnfcat >> /usr/lib/systemd/system/mysqld.service <<EOF[Unit]Description=MySQL ServerDocumentation=man:mysqld(8)Documentation=https://dev.mysql.com/doc/refman/en/using-systemd.htmlAfter=network.targetAfter=syslog.target[Install]WantedBy=multi-user.target[Service]User=mysqlGroup=mysqlExecStart=/application/mysql/bin/mysqld --defaults-file=/etc/my.cnfLimitNOFILE = 5000EOFcat >> /etc/my.cnf << EOF# 主从复制配置server-id = 1log-bin = mysql-binbinlog-format = ROWexpire_logs_days = 10
# 其他配置innodb_flush_log_at_trx_commit = 1sync_binlog = 1EOFsystemctl daemon-reload && systemctl start mysqld && systemctl enable mysqldmysqladmin -uroot password '123'
复制代码
3.2、从节点安装配置 MySQL 5.6
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.6.40-linux-glibc2.12-x86_64.tar.gztar xzvf mysql-5.6.40-linux-glibc2.12-x86_64.tar.gzmkdir /applicationmv mysql-5.6.40-linux-glibc2.12-x86_64 /application/mysql-5.6.40ln -s /application/mysql-5.6.40/ /application/mysqlcd /application/mysql/support-files/\cp my-default.cnf /etc/my.cnfcp mysql.server /etc/init.d/mysqldcd /application/mysql/scriptsuseradd mysql -s /sbin/nologin -Myum -y install autoconf./mysql_install_db --user=mysql --basedir=/application/mysql --data=/application/mysql/dataecho 'export PATH="/application/mysql/bin:$PATH"' >> /etc/profile.d/mysql.shsource /etc/profilesed -i 's#/usr/local#/application#g' /etc/init.d/mysqld /application/mysql/bin/mysqld_safesed -i '/^# basedir = /a\basedir = /application/mysql/' /etc/my.cnfsed -i '/^# datadir = /a\datadir = /application/mysql/data/' /etc/my.cnfcat >> /usr/lib/systemd/system/mysqld.service <<EOF[Unit]Description=MySQL ServerDocumentation=man:mysqld(8)Documentation=https://dev.mysql.com/doc/refman/en/using-systemd.htmlAfter=network.targetAfter=syslog.target[Install]WantedBy=multi-user.target[Service]User=mysqlGroup=mysqlExecStart=/application/mysql/bin/mysqld --defaults-file=/etc/my.cnfLimitNOFILE = 5000EOFcat >> /etc/my.cnf << EOF# 主从复制配置server-id = 2relay-log = mysql-relay-binlog-bin = mysql-binread-only = 1
# 并行复制配置slave_parallel_workers = 4EOFsystemctl daemon-reload && systemctl start mysqld && systemctl enable mysqldmysqladmin -uroot password '123'
复制代码
mysql -Vselect version();
复制代码
4、检查主从库server_id、log_bin、 slave_parallel_workers
show variables like 'server_id';
show variables like 'log_bin';
show variables like 'slave_parallel_workers';
show processlist;
复制代码
5、创建主从复制用户
主库
#创建slave用户grant replication slave on *.* to slave@'192.168.112.%' identified by '123';
flush privileges;
复制代码
6、获取主库的二进制日志文件和位置
# 记录下 File 和 Position 的值show master status;
复制代码
7、配置从库连接主库参数并启动从库复制进程
change master tomaster_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;
复制代码
8、验证主从复制
8.1、查看主库从库数据
目前数据一致
show databases;
use test ; show tables;
复制代码
8.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());
复制代码
8.3、从库执行查询操作
show tables;
select * from test_table;
复制代码
9、主节点单库多表 sysbench 写压测 从节点测试延迟
9.1、安装 sysbench 压测工具
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
yum -y install sysbench
sysbench --version
复制代码
9.2、创建测试数据库
# 主节点create database tssysbench;
复制代码
9.3、使用 sysbench 准备测试数据
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
复制代码
/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 条数据
9.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 \run
复制代码
每 10s 压测情况报告（取第一个 10s 做参数详解）：
thds: 64，这个意思就是有 64 个线程在压测
tps: 4971.66，这个意思就是每秒执行了 4971.66 个事务
qps: 29851.48，意思是每秒执行了 29851.48 个请求
(r/w/o: 0.00/17826.55/8911.43) ，意思是每秒 29851.48 个请求中有 19902.85 个写请求、9948.63 个其他请求，是对 QPS 进行了 rwo 拆解
lat (ms,95%)：21.50，意思是 95%的请求延迟都在 21.50ms 以下
err/s: 0.00 reconn/s: 0.00 ，意思是每秒有 0.1 个请求是失败的，发生了 0 次网络重连
结果参数详解：
SQL statistics: SQL 统计信息 read:0， 0 次读操作 write:4154598， 4154598 次写操作 other:2077304, 2077304 次其他操作 total:6231902，6231902 次总操作数 transactions:1038646 (5192.32 per sec)，在整个测试过程中，完成了 1,038,646 个事务，平均每秒完成 5192.32 个事务（tps）。queries:6231902 (31154.05 per sec.)，在整个测试过程中，执行了 6,231,902 次查询，平均每秒执行 31,154.05 次查询（qps)。ignored errors:12 (0.06 per sec），在整个测试过程中，忽略了 12 个错误，平均每秒忽略 0.06 个错误 reconnects:0(0.00 per sec.) ,在整个测试过程中，没有发生任何重连，平均每秒重连次数为 0。
General statistics: 总体统计信息 total time:200.0347s，测试总用时 200.0347 秒 total number of events:1038646，在测试期间发生的总事件数 1038646，即完成的事务数。
Latency (ms):延迟统计信息 min: 1.29 最小延迟 1.29 毫秒 avg: 12.32 平均延迟 12.32 毫秒 max: 186.34 最大延迟 186.34 毫秒 95th percentile: 20.37 95% 的事件的响应时间不超过 20.37 毫秒 sum: 12798566.05 所有事件的总延迟事件为 12798566.05 毫秒
Threads fairness: 线程公平性 events (avg/stddev): 16228.8438/91.38 每个线程平均完成的事件数及其标准偏差。平均每个线程完成了 16,228.8438 个事件，标准偏差为 91.38execution time (avg/stddev): 199.9776/0.01 每个线程平均执行的时间及其标准偏差。平均每个线程执行了 199.9776 秒，标准偏差为 0.01
9.5、从库采集延迟时间
collect_delay.sh
#!/bin/bash
# 从库连接信息SLAVE_MYSQL_USER="root"SLAVE_MYSQL_PASSWORD="123"
# 采集次数COLLECT_COUNT=10
# 记录延迟时间的数组DELAY_TIMES=()
for ((i=1; i<=COLLECT_COUNT; i++)); do echo "Collecting delay $i..." # 获取从库的延迟时间 DELAY=$(mysql -u $SLAVE_MYSQL_USER -p$SLAVE_MYSQL_PASSWORD -e "SHOW SLAVE STATUS\G;" | grep Seconds_Behind_Master | awk '{print $2}') DELAY_TIMES+=($DELAY) # 等待一段时间 sleep 20done
# 计算平均延迟时间AVERAGE_DELAY=$(echo "${DELAY_TIMES[@]}" | tr ' ' '+' | bc -l)AVERAGE_DELAY=$(echo "$AVERAGE_DELAY / $COLLECT_COUNT" | bc -l)
echo "Average delay for replication: $AVERAGE_DELAY seconds"
复制代码
这里对从库主从复制延迟的参数是采集 Seconds_Behind_Master 的值，压测 200s 内同时对从库进行延迟数据的采集，10s 一次最后取值为 10 次平均值
最终延迟为 48.0s
9.6、事务处理速度（TPS，Transactions Per Second）和查询处理速度（QPS，Queries Per Second）
9.7、清除测试数据
run 改为 cleanup 即可
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 \cleanup
复制代码
10、主节点多库多表 sysbench 写压测 从节点测试延迟
因为我目前不知道 sysbench 是否有多库多表压测的特性，所以采用 parallel 并行执行 sysbench 写压测。观测从节点延迟。还是 20w 条数据
10.1、创建测试数据库
10 个数据库，每个数据库 10 张表，每张表 2000 条数据，64 线程模拟并发，持续时间 200s
create-db.sh
#!/bin/bash
# 主库连接信息MYSQL_HOST="192.168.112.10"MYSQL_USER="root"MYSQL_PASSWORD="123"
# 创建10个数据库for i in $(seq 1 10); do DB_NAME="test_db$i" mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e "CREATE DATABASE $DB_NAME;"done
复制代码
10.2、使用 sysbench 准备测试数据
sysbench-pre.sh
#!/bin/bash
# 主库连接信息MYSQL_HOST="192.168.112.10"MYSQL_USER="root"MYSQL_PASSWORD="123"
for i in $(seq 1 10); do DB_NAME="test_db$i" # 准备测试数据 sysbench /usr/share/sysbench/oltp_write_only.lua \ --mysql-host=$MYSQL_HOST --mysql-port=3306 \ --mysql-user=$MYSQL_USER --mysql-password=$MYSQL_PASSWORD \ --mysql-db=$DB_NAME --db-driver=mysql \ --tables=10 --table-size=2000 --report-interval=10 \ --threads=64 --time=200 \ preparedone
复制代码
10.3、运行写操作测试
sysbench-run.sh
#!/bin/bash
# 主库连接信息MYSQL_HOST="192.168.112.10"MYSQL_USER="root"MYSQL_PASSWORD="123"
# 使用 parallel 运行 sysbench 测试seq 1 10 | parallel --no-notice -j 8 ' DB_NAME=test_db{} sysbench /usr/share/sysbench/oltp_write_only.lua \ --mysql-host='$MYSQL_HOST' --mysql-port=3306 \ --mysql-user='$MYSQL_USER' --mysql-password='$MYSQL_PASSWORD' \ --mysql-db=$DB_NAME --db-driver=mysql \ --tables=10 --table-size=2000 --report-interval=10 \ --threads=8 --time=200 \ run'
复制代码
10.4、从库采集延迟时间
collect_delay.sh
#!/bin/bash
# 从库连接信息SLAVE_MYSQL_USER="root"SLAVE_MYSQL_PASSWORD="123"
# 采集次数COLLECT_COUNT=10
# 记录延迟时间的数组DELAY_TIMES=()
for ((i=1; i<=COLLECT_COUNT; i++)); do echo "Collecting delay $i..." # 获取从库的延迟时间 DELAY=$(mysql -u $SLAVE_MYSQL_USER -p$SLAVE_MYSQL_PASSWORD -e "SHOW SLAVE STATUS\G;" | grep Seconds_Behind_Master | awk '{print $2}') DELAY_TIMES+=($DELAY) # 等待一段时间 sleep 20done
# 计算平均延迟时间AVERAGE_DELAY=$(echo "${DELAY_TIMES[@]}" | tr ' ' '+' | bc -l)AVERAGE_DELAY=$(echo "$AVERAGE_DELAY / $COLLECT_COUNT" | bc -l)
echo "Average delay for replication: $AVERAGE_DELAY seconds"
复制代码
针对 MySQL 5.6 版本 多库多表的性能测试就先这样吧
从执行结果看使用 parallel 执行 sysbench 多库是串行而非并行所以控制不了变量
四、MySQL 5.7 基于组提交的并行复制
MySQL5.7 中 slave 服务器的回放与 master 是一致的，即 master 服务器上是怎么并行执行的，那么 slave 上就怎样进行并行回放。不再有库的并行复制限制。
1、原理
通过对事务进行分组，优化减少了生成二进制日志所需的操作数。当事务同时提交时，它们将在单个操作中写入到二进制日志中。如果事务能同时提交成功，那么它们就不会共享任何锁，这意味着它们没有冲突，因此可以在 Slave 上并行执行。所以通过在二进制日志中添加组提交信息，实现 Slave 可以并行地安全地运行事务。
Group Commit 技术在 MySQL5.6 中是为了解决事务提交的时候需要 fsync 导致并发性不够而引入的。简单来说，就是由于事务提交时必须将 Binlog 写入到磁盘上而调用 fsync，这是一个代价比较高的操作，事务并发提交的情况下，每个事务各自获取日志锁并进行 fsync 会导致事务实际上以串行的方式写入 Binlog 文件，这样就大大降低了事务提交的并发程度。
Group Commit 技术将事务的提交阶段分成了 Flush、Sync、Commit 三个阶段，每个阶段维护一个队列，并且由该队列中第一个线程负责执行该步骤，这样实际上就达到了一次可以将一批事务的 Binlog fsync 到磁盘的目的，这样的一批同时提交的事务称为同一个 Group 的事务
Group Commit 虽然是属于并行提交的技术，但是解决了从服务器上事务并行回放的一个难题——即如何判断哪些事务可以并行回放。如果一批事务是同时 Commit 的，那么这些事务必然不会有互斥的持有锁，也不会有执行上的相互依赖，因此这些事务必然可以并行的回放。为了标记事务所属的组，MySQL5.7 版本在产生 Binlog 日志时会有两个特殊的值记录在 Binlog Event 中，last_committed 和 sequence_number，其中 last_committed 指的是该事务提交时，上一个事务提交的编号，sequence_number 是事务提交的序列号，在一个 Binlog 文件内单调递增。如果两个事务的 last_committed 值一致，这两个事务就是在一个组内提交的。
为了兼容 MySQL5.6 基于库的并行复制，5.7 引入了新的变量 slave-parallel-type，其可以配置的值有：DATABASE（默认值，基于库的并行复制方式）、LOGICAL_CLOCK（基于组提交的并行复制方式)。
2、部署主从复制
因为变量 slave-parallel-type 的参数设置中 database 实际上就是 MySQL 5.6 版本的多线程复制
所以这个 MySQL 5.7 版本就采用 logical_clock
2.1、主节点安装配置 MySQL 5.7
cd /usr/localwget https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.35-linux-glibc2.12-x86_64.tar.gztar -zxvf mysql-5.7.35-linux-glibc2.12-x86_64.tar.gzln -s mysql-5.7.35-linux-glibc2.12-x86_64 mysqlgroupadd mysqluseradd -r -g mysql mysqlcd /usr/local/mysqlchown -R mysql:mysql .mkdir -p /usr/local/mysql/datachown -R mysql:mysql /usr/local/mysql/data./bin/mysqld --initialize --user=mysql --datadir=/usr/local/mysql/datacat > /etc/my.cnf << EOF[mysqld]basedir = /usr/local/mysqldatadir = /usr/local/mysql/datasocket = /tmp/mysql.sockpid-file = /usr/local/mysql/data/mysqld.piduser = mysqlport = 3306# 二进制日志配置server-id = 1log-bin = mysql-binbinlog-format = rowexpire_logs_days = 10gtid-mode = ONenforce-gtid-consistency=ON# 主从复制配置log-slave-updates = 1read-only = 0EOFcat > /etc/systemd/system/mysqld.service << EOF[Unit]Description=MySQL 5.7 Database ServerAfter=network.target
[Service]User=mysqlGroup=mysqlExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnfRestart=on-failure
[Install]WantedBy=multi-user.targetEOFsystemctl daemon-reload && systemctl start mysqld && systemctl enable mysqldecho 'export PATH="/usr/local/mysql/bin:$PATH"' >> /etc/profile.d/mysql.shsource /etc/profile======================================================================================> 使用临时密码登录MySQL，修改 root 密码ALTER USER 'root'@'localhost' IDENTIFIED BY '123';FLUSH PRIVILEGES;EXIT;
复制代码
临时密码
2.2、从节点安装配置 MySQL 5.7
cd /usr/localwget https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.35-linux-glibc2.12-x86_64.tar.gztar -zxvf mysql-5.7.35-linux-glibc2.12-x86_64.tar.gzln -s mysql-5.7.35-linux-glibc2.12-x86_64 mysqlgroupadd mysqluseradd -r -g mysql mysqlcd /usr/local/mysqlchown -R mysql:mysql .mkdir -p /usr/local/mysql/datachown -R mysql:mysql /usr/local/mysql/data./bin/mysqld --initialize --user=mysql --datadir=/usr/local/mysql/datacat > /etc/my.cnf << EOF[mysqld]basedir = /usr/local/mysqldatadir = /usr/local/mysql/datasocket = /tmp/mysql.sockpid-file = /usr/local/mysql/data/mysqld.piduser = mysqlport = 3306# 二进制日志配置server-id = 2log-bin = mysql-binbinlog-format = rowexpire_logs_days=10
# 主从复制配置relay-log=mysql-relay-binlog-slave-updates=ONgtid-mode=ONenforce-gtid-consistency=ONslave-parallel-type=LOGICAL_CLOCKslave-parallel-workers=4slave_preserve_commit_order=onread-only = 0EOFcat > /etc/systemd/system/mysqld.service << EOF[Unit]Description=MySQL 5.7 Database ServerAfter=network.target
[Service]User=mysqlGroup=mysqlExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnfRestart=on-failure
[Install]WantedBy=multi-user.targetEOFsystemctl daemon-reload && systemctl start mysqld && systemctl enable mysqldecho 'export PATH="/usr/local/mysql/bin:$PATH"' >> /etc/profile.d/mysql.shsource /etc/profile======================================================================================> 使用临时密码登录MySQL，修改 root 密码ALTER USER 'root'@'localhost' IDENTIFIED BY '123'; FLUSH PRIVILEGES;EXIT;
复制代码
临时密码
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、配置从库连接主库参数并启动从库复制进程
change master tomaster_host='192.168.112.10',master_user='slave',master_password='123',master_log_file='mysql-bin.000003',master_log_pos=602;
start slave;show slave status\G;
复制代码
7、验证主从复制
7.1、查看主库从库数据
目前数据一致
show databases;
# 主库create database test;use test;
复制代码
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 压测工具
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
yum -y install sysbench
sysbench --version
复制代码
8.2、创建测试数据库
# 主节点create database tssysbench;
复制代码
8.3、使用 sysbench 准备测试数据
与 MySQL 5.6 版本的单库多表写压测一样的数据形成对照
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
复制代码
/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 \ run
复制代码
每 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.16execution 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<=COLLECT_COUNT; i++)); do echo "Collecting delay $i..." # 获取从库的延迟时间 DELAY=$(mysql -u $SLAVE_MYSQL_USER -p$SLAVE_MYSQL_PASSWORD -e "SHOW SLAVE STATUS\G;" | grep Seconds_Behind_Master | awk '{print $2}') DELAY_TIMES+=($DELAY) # 等待一段时间 sleep 20done
# 计算平均延迟时间AVERAGE_DELAY=$(echo "${DELAY_TIMES[@]}" | tr ' ' '+' | bc -l)AVERAGE_DELAY=$(echo "$AVERAGE_DELAY / $COLLECT_COUNT" | bc -l)
echo "Average delay for replication: $AVERAGE_DELAY seconds"
复制代码
这里对从库主从复制延迟的参数是采集 Seconds_Behind_Master 的值，压测 200s 内同时对从库进行延迟数据的采集，10s 一次最后取值为 10 次平均值
最终延迟为 44.4s
8.6、事务处理速度（TPS，Transactions Per Second）和查询处理速度（QPS，Queries Per Second）
。。。同样的 sysbench 写压测参数，单库多表，20 张表、每张表 10000 条数据、并发线程数 64、持续压测时间 200s
至少相对 MySQL 5.6 版本，MySQL 5.7 版本的主从复制延迟时间是优化了的
8.7、清除测试数据
run 改为 cleanup 即可
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 \cleanup
复制代码
五、MySQL 8.0 基于 WriteSet 的并行复制
1、概述
基于组提交 LOGICAL_CLOCK 多线程复制机制在每组提交事务足够多，即业务量足够大时表现较好。但很多实际业务中，虽然事务没有 Lock Interval 重叠，但这些事务操作的往往是不同的数据行，也不会有锁冲突，是可以并行执行的，但 LOGICAL_CLOCK 的实现无法使这部分事务得到并行重放。为了解决这个问题，MySQL 在 5.7.22 版本推出了基于 WriteSet 的并行复制。简单来说，WriteSet 并行复制的思想是：不同事务的记录不重叠，则都可在从库上并行重放。可以看到并行的力度从组提交细化为记录级。
MySQL8.0 是基于write-set的并行复制，write-set由binlog-transaction-dependency-tracking参数进行控制。MySQL 会有一个集合变量来存储事务修改的记录信息（主键哈希值），所有已经提交的事务所修改的主键值经过 hash 后都会与那个变量的集合进行对比，来判断改行是否与其冲突，并以此来确定依赖关系，没有冲突即可并行。这样的粒度，就到了 row 级别了，此时并行的粒度更加精细，并行的速度会更快。
2、核心原理
Master 端
WriteSet 的生成：
WriteSet=hash(index_name | db_name | db_name_length | table_name | table_name_length | value | value_length)
复制代码
WriteSet 的存储：
last_committed 的更新：
无冲突：如果哈希表中不存在相同的 WriteSet，说明当前事务与之前的事务没有冲突。此时，WriteSet 插入哈希表，并且当前事务的 last_committed 值保持不变，与前一个事务的 last_committed 值相同。
有冲突：如果哈希表中存在相同的 WriteSet，说明当前事务与之前的事务有冲突。此时，更新哈希表中对应 WriteSet 的 sequence_number，并将当前事务的 last_committed 值更新为新的 sequence_number。
Slave 端
并行执行
在从库上，复制调度器会检查事务的 last_committed 值。
如果两个事务的 last_committed 值相同，说明它们可以并行执行。
如果两个事务的 last_committed 值不同，说明它们有冲突，必须按顺序执行。
3、MySQL 8.0 相关参数
在 MySQL 8.0 中，引入了参数binlog_transaction_dependency_tracking用于控制如何决定事务的依赖关系。
3.1、binlog_transaction_dependency_tracking
该值有三个选项：
COMMIT_ORDERE:表示继续使用 5.7 中的基于组提交的方式决定事务的依赖关系(默认值)；
WRITESET：表示使用写集合来决定事务的依赖关系；
WRITESET_SESSION：表示使用 WriteSet 来决定事务的依赖关系，但是同一个 Session 内的事务不会有相同的 last_committed 值(同一个会话中的事务不能并行执行)。
3.2、transaction_write_set_extraction
指定事务写集合的哈希算法，可设置的值有：
OFF
MURMUR32
XXHASH64（默认值）。
对于 Group Replication，该参数必须设置为 XXHASH64。
注意，若要将 binlog_transaction_dependency_tracking 设置为 WRITESET 或 WRITESET_SESSION，则该参数不能设置为 OFF。
3.3、binlog_transaction_dependency_history_size
m_writeset_history 的上限，默认 25000。
一般来说，binlog_transaction_dependency_history_size 越大，m_writeset_history 能存储的行的信息就越多。在不出现行冲突的情况下，m_writeset_history_start 也会越小。相应地，新事务的 last_committed 也会越小，在从库重放的并发度也会越高。
4、WriteSet 依赖检测条件
WriteSet 是基于主键的冲突检测(binlog_transaction_depandency_tracking = COMMIT_ORDERE|WRITESET|WRITESET_SESSION,修改的 row 的主键或非空唯一键没有冲突，即可并行)。
在开启了 WRITESET 或 WRITESET_SESSION 后，MySQL 按以下的方式标识并记录事务的更新：
如果事务当前更新的行有主键，则将 HASH（DB 名、TABLE 名、KEY 名称、KEY_VALUE1、KEY_VALUE2……）加入到当前事务的 vector write_set 中。
如果事务当前更新的行有非空的唯一键，同样将 HASH（DB 名、TABLE 名、KEY 名、KEY_VALUE1）……加入到当前事务的 write_set 中。
如果事务更新的行有外键约束且不为空，则将该外键信息与 VALUE 的 HASH 加到当前事务的 write_set 中。
如果事务当前更新的表的主键是其它某个表的外键，则设置当前事务 has_related_foreign_key = true。
如果事务更新了某一行且没有任何数据被加入到 write_set 中，则标记当前事务 has_missing_key = true。在执行冲突检测的时候，先会检查 has_related_foreign_key 和 has_missing_key ， 如果为 true，则退到 COMMIT_ORDER 模式；否则，会依照事务的 write_set 中的 HASH 值与已提交的事务的 write_set 进行比对。如果没有冲突，则当前事务与最后一个已提交的事务共享相同的 last_commited，否则将从全局已提交的 write_set 中删除那个冲突的事务之前提交的所有 write_set，并退化到 COMMIT_ORDER 计算 last_committed。
在每一次计算完事务的 last_committed 值以后，需要去检测当前全局已经提交的事务的 write_set 是否已经超过了 binlog_transaction_dependency_history_size 设置的值，如果超过，则清空已提交事务的全局 write_set。
从检测条件上看，该特性依赖于主键和唯一索引，如果事务涉及的表中没有主键且没有唯一非空索引，那么将无法从此特性中获得性能的提升。除此之外，还需要将 Binlog 格式设置为 Row 格式。
5、基于 COMMIT_ORDER，WRITESET_SESSION，WRITESET 方案的压测
MySQL 官方有对 COMMIT_ORDER，WRITESET_SESSION，WRITESET 这三种方案的压测测试
我这里就不测试了
主库环境：16 核，SSD，1 个数据库，16 张表，共 800w 条数据。
压测场景：OLTP Read/Write, Update Indexed Column 和 Write-only。
压测方案：在关闭复制的情况下，在不同的线程数下，注入 100w 个事务。开启复制，观察不同线程数下，不同方案的从库重放速度。
三个场景下的压测结果如图所示。
结论
1、对于 COMMIT_ORDER 方案，主库并发度越高，从库的重放速度越快。
2、对于 WRITESET 方案，主库的并发线程数对其几乎没有影响。甚至，单线程下 WRITESET 的重放速度都超过了 256 线程下的 COMMIT_ORDER。
3、与 COMMIT_ORDER 一样，WRITESET_SESSION 也依赖于主库并发。只不过，在主库并发线程数较低（4 线程、8 线程）的情况下，WRITESET_SESSION 也能实现较高的吞吐量。
6、开启并行复制
修改过 my.cnf 配置文件中的参数都需要重载重启 MySQL 才能生效
6.1、主库
binlog_transaction_dependency_tracking = WRITESETtransaction_write_set_extraction = XXHASH64binlog_transaction_dependency_history_size = 25000binlog_format = ROW
复制代码
binlog_transaction_dependency_tracking：
COMMIT_ORDER：基于提交顺序的依赖关系。这意味着事务的依赖关系是根据它们在主库上提交的顺序来确定的。
WRITESET：基于写集的依赖关系。这意味着事务的依赖关系是根据它们修改的数据行来确定的。
WRITESET_SESSION：基于会话的写集依赖关系。这是 WRITESET 的一种更细粒度的变体，它不仅考虑事务修改的数据行，还考虑会话内的事务依赖关系。
transaction_write_set_extraction = XXHASH64：选择用于计算 WriteSet 的哈希算法。
binlog_transaction_dependency_history_size：设置 WriteSet 历史记录的最大大小。
binlog_format：设置二进制日志的格式。
STATEMENT：基于语句的复制。二进制日志记录的是 SQL 语句本身。
ROW：基于行的复制。二进制日志记录的是每行数据的变化。（基于 WRITESET 的并行复制方案，只在 binlog 格式为 ROW 的情况下才生效。）
MIXED：混合模式。MySQL 自动选择 STATEMENT 或 ROW 模式，以确保复制的正确性。
6.2、从库
slave_parallel_type = LOGICAL_CLOCKslave_parallel_workers = 16slave_preserve_commit_order = ON
复制代码
slave_parallel_type：设置从库并行复制的类型。该参数有以下取值：
slave_parallel_workers：设置 Worker 线程的数量。
slave_preserve_commit_order：
文章转载自：misakivv
原文链接：https://www.cnblogs.com/misakivv/p/18561324
体验地址：http://www.jnpfsoft.com/?from=infoq
评论