|
目录
一、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
- [mysqld]
- 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
- [Unit]
- Description=MySQL 5.5 Database Server
- After=network.target
- [Service]
- User=mysql
- Group=mysql
- ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
- Restart=on-failure
- [Install]
- 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
- [mysqld]
- 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
- [Unit]
- Description=MySQL 5.5 Database Server
- After=network.target
- [Service]
- User=mysql
- Group=mysql
- ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
- Restart=on-failure
- [Install]
- 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 单线程体现
这里有两个系统用户线程:
- 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
- [Unit]
- Description=MySQL Server
- Documentation=man:mysqld(8)
- Documentation=https://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
- 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
- [Unit]
- Description=MySQL Server
- Documentation=man:mysqld(8)
- Documentation=https://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
- 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 --version
复制代码 8.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
[code]#!/bin/bash# 从库连接信息SLAVE_MYSQL_USER="root"SLAVE_MYSQL_PASSWORD="123"# 采集次数COLLECT_COUNT=10# 记录延迟时间的数组DELAY_TIMES=()for ((i=1; i |
本帖子中包含更多资源
您需要 登录 才可以下载或查看,没有账号?立即注册
x
|