ToB企服应用市场:ToB评测及商务社交产业平台

标题: MySQL 主从复制之多线程复制 [打印本页]

作者: 老婆出轨    时间: 2024-11-21 20:01
标题: MySQL 主从复制之多线程复制
目录

一、MySQL 多线程复制的背景

MySQL 的主从复制延迟一直是受开发者最为关注的问题之一,MySQL 从 5.6 版本开始追加了并行复制功能,目的就是为了改善复制延迟问题,并行复制称为enhanced multi-threaded slave(简称MTS)。
二、MySQL 5.5 主从复制

1、原理


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;
复制代码

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;
复制代码

这里有两个系统用户线程:
三、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
  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;
复制代码

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;
复制代码


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做参数详解):
效果参数详解:
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




欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/) Powered by Discuz! X3.4