翼度科技»论坛 编程开发 mysql 查看内容

MySQL 主从复制之多线程复制

7

主题

7

帖子

21

积分

新手上路

Rank: 1

积分
21
目录

一、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
  1. cd /usr/local
  2. wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.5.62-linux-glibc2.12-x86_64.tar.gz
  3. tar -zxvf mysql-5.5.62-linux-glibc2.12-x86_64.tar.gz
  4. ln -s mysql-5.5.62-linux-glibc2.12-x86_64 mysql
  5. groupadd mysql
  6. useradd -r -g mysql mysql
  7. cd /usr/local/mysql
  8. chown -R mysql:mysql .
  9. mkdir -p /usr/local/mysql/data
  10. chown -R mysql:mysql /usr/local/mysql/data
  11. ./scripts/mysql_install_db --user=mysql --datadir=/usr/local/mysql/data
  12. cat > /etc/my.cnf << EOF
  13. [mysqld]
  14. basedir = /usr/local/mysql
  15. datadir = /usr/local/mysql/data
  16. socket = /tmp/mysql.sock
  17. pid-file = /usr/local/mysql/data/mysqld.pid
  18. user = mysql
  19. port = 3306
  20. # 二进制日志配置
  21. server-id = 1
  22. log-bin = mysql-bin
  23. binlog-format = row
  24. expire_logs_days = 10
  25. # 主从复制配置
  26. log-slave-updates = 1
  27. read-only = 0
  28. EOF
  29. cat > /etc/systemd/system/mysqld.service << EOF
  30. [Unit]
  31. Description=MySQL 5.5 Database Server
  32. After=network.target
  33. [Service]
  34. User=mysql
  35. Group=mysql
  36. ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
  37. Restart=on-failure
  38. [Install]
  39. WantedBy=multi-user.target
  40. EOF
  41. systemctl daemon-reload && systemctl start mysqld && systemctl enable mysqld
  42. echo 'export PATH="/usr/local/mysql/bin:$PATH"' >> /etc/profile.d/mysql.sh
  43. source /etc/profile
  44. mysqladmin -uroot password '123'
复制代码
2.2、从节点安装配置MySQL 5.5
  1. cd /usr/local
  2. wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.5.62-linux-glibc2.12-x86_64.tar.gz
  3. tar -zxvf mysql-5.5.62-linux-glibc2.12-x86_64.tar.gz
  4. ln -s mysql-5.5.62-linux-glibc2.12-x86_64 mysql
  5. groupadd mysql
  6. useradd -r -g mysql mysql
  7. cd /usr/local/mysql
  8. chown -R mysql:mysql .
  9. mkdir -p /usr/local/mysql/data
  10. chown -R mysql:mysql /usr/local/mysql/data
  11. ./scripts/mysql_install_db --user=mysql --datadir=/usr/local/mysql/data
  12. cat > /etc/my.cnf << EOF
  13. [mysqld]
  14. basedir = /usr/local/mysql
  15. datadir = /usr/local/mysql/data
  16. socket = /tmp/mysql.sock
  17. pid-file = /usr/local/mysql/data/mysqld.pid
  18. user = mysql
  19. port = 3306
  20. # 二进制日志配置
  21. server-id = 1
  22. log-bin = mysql-bin
  23. binlog-format = row
  24. expire_logs_days = 10
  25. # 主从复制配置
  26. log-slave-updates = 1
  27. read-only = 0
  28. EOF
  29. cat > /etc/systemd/system/mysqld.service << EOF
  30. [Unit]
  31. Description=MySQL 5.5 Database Server
  32. After=network.target
  33. [Service]
  34. User=mysql
  35. Group=mysql
  36. ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
  37. Restart=on-failure
  38. [Install]
  39. WantedBy=multi-user.target
  40. EOF
  41. systemctl daemon-reload && systemctl start mysqld && systemctl enable mysqld
  42. echo 'export PATH="/usr/local/mysql/bin:$PATH"' >> /etc/profile.d/mysql.sh
  43. source /etc/profile
  44. mysqladmin -uroot password '123'
复制代码
  1. select version();
  2. mysql -V
复制代码

3、检查主从库 server_id 和 log_bin 配置
  1. show variables like 'server_id';
  2. show variables like 'log_bin';
复制代码
主库
从库
4、创建主从复制用户

主库
  1. #登录数据库
  2. mysql -uroot -p123
  3. #创建slave用户
  4. grant replication slave on *.* to slave@'192.168.112.%' identified by '123';
  5. flush privileges;
复制代码

  • grant replication slave on *.*: 授予 replication slave 权限,允许用户从主库复制数据。*.* 表示所有数据库和所有表。
  • to slave@'192.168.112.%': 指定用户名为 slave,允许从 192.168.112.% 子网内的任何IP地址连接到主库。
  • identified by '123': 设置用户的密码为 123。

5、获取主库的二进制日志文件和位置
  1. # 记录下 File 和 Position 的值
  2. show master status;
复制代码

6、配置从库连接主库参数并启动从库复制进程
  1. change master to
  2. master_host='192.168.112.10',
  3. master_user='slave',
  4. master_password='123',
  5. master_log_file='mysql-bin.000004',
  6. master_log_pos=332;
  7. start slave;
  8. show slave status\G;
复制代码

7、验证主从复制

7.1、查看主库从库数据

目前数据一致
  1. show databases;
  2. use test ; show tables;
复制代码

7.2、主库创建表并插入数据
  1. create table test_table (id int auto_increment primary key,name varchar(255),created_at timestamp default current_timestamp);
  2. insert into test_table (name , created_at) values ('Data1', now());
  3. insert into test_table (name , created_at) values ('Data2', now());
  4. insert into test_table (name , created_at) values ('Data3', now());
复制代码
7.3、从库执行查询操作
  1. show tables;
  2. select * from test_table;
复制代码

8、MySQL 5.5 单线程体现
  1. # 从库
  2. 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
  1. wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.6.40-linux-glibc2.12-x86_64.tar.gz
  2. tar xzvf mysql-5.6.40-linux-glibc2.12-x86_64.tar.gz
  3. mkdir /application
  4. mv mysql-5.6.40-linux-glibc2.12-x86_64 /application/mysql-5.6.40
  5. ln -s /application/mysql-5.6.40/ /application/mysql
  6. cd /application/mysql/support-files/
  7. \cp my-default.cnf /etc/my.cnf
  8. cp mysql.server /etc/init.d/mysqld
  9. cd /application/mysql/scripts
  10. useradd mysql -s /sbin/nologin -M
  11. yum -y install autoconf
  12. ./mysql_install_db --user=mysql --basedir=/application/mysql  --data=/application/mysql/data
  13. echo 'export PATH="/application/mysql/bin:$PATH"' >> /etc/profile.d/mysql.sh
  14. source /etc/profile
  15. sed -i 's#/usr/local#/application#g' /etc/init.d/mysqld /application/mysql/bin/mysqld_safe
  16. sed -i '/^# basedir = /a\basedir = /application/mysql/' /etc/my.cnf
  17. sed -i '/^# datadir = /a\datadir = /application/mysql/data/' /etc/my.cnf
  18. cat >> /usr/lib/systemd/system/mysqld.service <<EOF
  19. [Unit]
  20. Description=MySQL Server
  21. Documentation=man:mysqld(8)
  22. Documentation=https://dev.mysql.com/doc/refman/en/using-systemd.html
  23. After=network.target
  24. After=syslog.target
  25. [Install]
  26. WantedBy=multi-user.target
  27. [Service]
  28. User=mysql
  29. Group=mysql
  30. ExecStart=/application/mysql/bin/mysqld --defaults-file=/etc/my.cnf
  31. LimitNOFILE = 5000
  32. EOF
  33. cat >> /etc/my.cnf << EOF
  34. # 主从复制配置
  35. server-id = 1
  36. log-bin = mysql-bin
  37. binlog-format = ROW
  38. expire_logs_days = 10
  39. # 其他配置
  40. innodb_flush_log_at_trx_commit = 1
  41. sync_binlog = 1
  42. EOF
  43. systemctl daemon-reload && systemctl start mysqld && systemctl enable mysqld
  44. mysqladmin -uroot password '123'
复制代码
临时密码

2.2、从节点安装配置 MySQL 5.7
  1. wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.6.40-linux-glibc2.12-x86_64.tar.gz
  2. tar xzvf mysql-5.6.40-linux-glibc2.12-x86_64.tar.gz
  3. mkdir /application
  4. mv mysql-5.6.40-linux-glibc2.12-x86_64 /application/mysql-5.6.40
  5. ln -s /application/mysql-5.6.40/ /application/mysql
  6. cd /application/mysql/support-files/
  7. \cp my-default.cnf /etc/my.cnf
  8. cp mysql.server /etc/init.d/mysqld
  9. cd /application/mysql/scripts
  10. useradd mysql -s /sbin/nologin -M
  11. yum -y install autoconf
  12. ./mysql_install_db --user=mysql --basedir=/application/mysql  --data=/application/mysql/data
  13. echo 'export PATH="/application/mysql/bin:$PATH"' >> /etc/profile.d/mysql.sh
  14. source /etc/profile
  15. sed -i 's#/usr/local#/application#g' /etc/init.d/mysqld /application/mysql/bin/mysqld_safe
  16. sed -i '/^# basedir = /a\basedir = /application/mysql/' /etc/my.cnf
  17. sed -i '/^# datadir = /a\datadir = /application/mysql/data/' /etc/my.cnf
  18. cat >> /usr/lib/systemd/system/mysqld.service <<EOF
  19. [Unit]
  20. Description=MySQL Server
  21. Documentation=man:mysqld(8)
  22. Documentation=https://dev.mysql.com/doc/refman/en/using-systemd.html
  23. After=network.target
  24. After=syslog.target
  25. [Install]
  26. WantedBy=multi-user.target
  27. [Service]
  28. User=mysql
  29. Group=mysql
  30. ExecStart=/application/mysql/bin/mysqld --defaults-file=/etc/my.cnf
  31. LimitNOFILE = 5000
  32. EOF
  33. cat >> /etc/my.cnf << EOF
  34. # 主从复制配置
  35. server-id = 2
  36. relay-log = mysql-relay-bin
  37. log-bin = mysql-bin
  38. read-only = 1
  39. # 并行复制配置
  40. slave_parallel_workers = 4
  41. EOF
  42. systemctl daemon-reload && systemctl start mysqld && systemctl enable mysqld
  43. mysqladmin -uroot password '123'
复制代码
临时密码

3、检查主从库server_id、log_bin、 slave_parallel_workers、slave-parallel-type
  1. show variables like 'version';show variables like 'server_id';
  2. show variables like 'log_bin';show variables like 'slave_parallel_type';
复制代码

4、创建主从复制用户

主库
  1. #登录数据库
  2. mysql -uroot -p123
  3. #创建slave用户
  4. grant replication slave on *.* to slave@'192.168.112.%' identified by '123';
  5. 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、获取主库的二进制日志文件和位置
  1. # 记录下 File 和 Position 的值
  2. show master status;
复制代码

6、配置从库连接主库参数并启动从库复制进程
  1. # 记录下 File 和 Position 的值
  2. show master status;
复制代码

7、验证主从复制

7.1、查看主库从库数据

目前数据一致
  1. change master to
  2. master_host='192.168.112.10',
  3. master_user='slave',
  4. master_password='123',
  5. master_log_file='mysql-bin.000001',
  6. master_log_pos=552;
  7. start slave;
  8. show slave status\G;
复制代码

7.2、主库创建表并插入数据
  1. create table test_table (id int auto_increment primary key,name varchar(255),created_at timestamp default current_timestamp);
  2. insert into test_table (name , created_at) values ('Data1', now());
  3. insert into test_table (name , created_at) values ('Data2', now());
  4. insert into test_table (name , created_at) values ('Data3', now());
复制代码
7.3、从库执行查询操作
  1. use test;show tables;
  2. select * from test_table;
复制代码

8、主节点单库多表 sysbench 写压测 从节点测试延迟

8.1、安装 sysbench 压测工具
  1. show tables;
  2. select * from test_table;
复制代码
8.2、创建测试数据库
  1. curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
  2. yum -y install sysbench
  3. sysbench --version
复制代码
8.3、使用 sysbench 准备测试数据

与MySQL 5.6 版本的单库多表写压测一样的数据形成对照
  1. # 主节点
  2. 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、运行写操作测试
  1. sysbench /usr/share/sysbench/oltp_write_only.lua \
  2. --mysql-host=192.168.112.10 --mysql-port=3306 \
  3. --mysql-user=root --mysql-password='123' \
  4. --mysql-db=tssysbench --db-driver=mysql \
  5. --tables=20 --table-size=10000 --report-interval=10 \
  6. --threads=64 --time=200 \
  7. 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

举报 回复 使用道具