介绍
构建一个高可用的能实现读写分离的高效的MySQL集群,确保业务的稳定
步骤
部署基于Gtid的主从复制+延迟备份
准备工作
①
准备四台服务器,都开启mysql服务
尽量四台服务器的数据库版本一致
②
master服务器开启二进制日志
master服务器和slave服务器的server_id不能相同,必须唯一
master和slave都关闭防火墙和selinux
- 关闭防火墙
临时关闭
service firewalld stop
永久关闭
[root@localhost backup]# systemctl disable firewalld
- 关闭selinux
临时关闭
set enforcing 0
永久关闭
[root@localhost backup]# vim /etc/selinux/config
MASTER配置
1.创建用来复制二进制日志的用户
mysql>create user 'slave'@'192.168.186.129' identified by 'wp123456';
Query OK, 0 rows affected (1.05 sec)
2.授权 - - - 授权获取二进制日志的权限
root@(none) 12:33 mysql>grant replication slave on *.* to 'slave'@'192.168.186.129';
Query OK, 0 rows affected (0.00 sec)
3.创建完全备份
[root@localhost /]# mysqldump -uroot -p'wp123456' --all-databases >/backup/all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
4.将完全备份传输到slave机器上
[root@localhost /]# scp /backup/all.sql root@192.168.186.129:/root
The authenticity of host '192.168.186.129 (192.168.186.129)' can't be established.
ECDSA key fingerprint is SHA256:0aDD3cub4HOR1gWHbgqPRYCLydWdpE0F1FljaTnQ+fc.
ECDSA key fingerprint is MD5:8f:8c:72:87:9d:3a:d6:40:5a:c3:35:11:96:af:af:05.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.186.129' (ECDSA) to the list of known hosts.
root@192.168.186.129's password:
all.sql 100% 887KB 16.8MB/s 00:00
5.修改mysql配置文件
- 开启二进制日志
- 配置server_id
master配置
[mysqld]
socket=/data/mysql/mysql.sock
port = 3306
open_files_limit = 8192
innodb_buffer_pool_size = 512M
character-set-server=utf8
slow_query_log=1
long_query_time = 0.001
general_log
# 开启二进制日志
log_bin
# 指定二进制日志名称前缀
log-bin=mysql-bin
# 服务器的编号
server_id = 1
SLAVE配置
slave和delay服务器都相同操作
1.在slave、delay、slave2服务器上导入备份
[root@localhost ~]# mysql -uroot -p'wp123456' <all.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
2.修改mysql配置文件
vim /etc/my.cnf
slave配置
[mysqld]
socket=/data/mysql/mysql.sock
port = 3306
open_files_limit = 8192
innodb_buffer_pool_size = 512M
character-set-server=utf8
# 二进制日志
log_bin
# 服务器编号
server_id = 2
log-bin=mysql-bin
delay配置
[mysqld]
socket=/data/mysql/mysql.sock
port = 3306
open_files_limit = 8192
innodb_buffer_pool_size = 512M
character-set-server=utf8
# 二进制日志
log_bin
# 服务器编号
server_id = 3
log-bin=mysql-bin
slave2配置
[mysqld]
socket=/data/mysql/mysql.sock
port = 3306
open_files_limit = 8192
innodb_buffer_pool_size = 512M
character-set-server=utf8
# 二进制日志
log_bin
# 服务器编号
server_id = 4
log-bin=mysql-bin
3.查看master的相关信息
- 查看二进制日志文件的名称和位置号
root@(none) 13:28 mysql>show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 | 154 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
4.SLAVE上启用master的信息
在slave和delay、slave2服务器上都进行相同的操作
CHANGE MASTER TO MASTER_HOST='192.168.186.128',
MASTER_USER='slave',
MASTER_PASSWORD='wp123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=154;
MASTER_USER是先前创建获取二进制日志文件的用户
MASTER_PASSWORD是先前创建获取二进制日志文件的用户的密码
MASTER_PORT端口号
MASTER_LOG_FILE二进制日志文件名
MASTER_LOG_POS位置号
master和slave都重启mysql服务
[root@localhost /]# service mysqld restart
Shutting down MySQL.... SUCCESS!
Starting MySQL... SUCCESS!
查看同步效果
mysql>show slave status\G;
开启slave线程
mysql>start slave;
此时异步复制完成
部署基于Gtid的主从复制
关闭slave服务
root@mysql 22:29 mysql>stop slave;
Query OK, 0 rows affected (0.38 sec)
slave和delay、slave2都要清除master的信息
root@(none) 22:40 mysql>reset master;
Query OK, 0 rows affected (0.01 sec)
配置文件信息
master配置
[mysqld]
socket=/data/mysql/mysql.sock
port = 3306
open_files_limit = 8192
innodb_buffer_pool_size = 512M
character-set-server=utf8
slow_query_log=1
long_query_time = 0.001
general_log
# 开启二进制日志
log_bin
# 指定二进制日志名称前缀
log-bin=mysql-bin
# 服务器的编号
server_id = 1
# 开启gtid
gtid-mode=ON
enforce-gtid-consistency=ON
enforce_gtid_consistency 保证GTID安全的参数强制gtid一致性,开启后对于特定create table不被支持
slave配置
[mysqld]
socket=/data/mysql/mysql.sock
port = 3306
open_files_limit = 8192
innodb_buffer_pool_size = 512M
character-set-server=utf8
# 二进制日志
log_bin
# 服务器编号
server_id = 2
log-bin=mysql-bin
# gtid
gtid-mode=ON
enforce-gtid-consistency=ON
log_slave_updates=ON
delay配置
[mysqld]
socket=/data/mysql/mysql.sock
port = 3306
open_files_limit = 8192
innodb_buffer_pool_size = 512M
character-set-server=utf8
# 二进制日志
log_bin
# 服务器编号
server_id = 3
log-bin=mysql-bin
# gtid
gtid-mode=ON
enforce-gtid-consistency=ON
log_slave_updates=ON
slave2配置
[mysqld]
socket=/data/mysql/mysql.sock
port = 3306
open_files_limit = 8192
innodb_buffer_pool_size = 512M
character-set-server=utf8
# 二进制日志
log_bin
# 服务器编号
server_id = 4
log-bin=mysql-bin
# gtid
gtid-mode=ON
enforce-gtid-consistency=ON
log_slave_updates=ON
master和slave都重启mysql服务
[root@localhost /]# service mysqld restart
Shutting down MySQL.... SUCCESS!
Starting MySQL... SUCCESS!
至此基于Gtid的主从复制完成
延迟备份配置
在delay服务器上设置
步骤:
- stop slave;
root@w 21:35 mysql>stop slave;
Query OK, 0 rows affected (0.00 sec)
- change master to master_delay = 3600;
root@w 21:35 mysql>change master to master_delay = 3600;
Query OK, 0 rows affected (0.01 sec)
- start slave;
root@w 21:36 mysql>start slave;
Query OK, 0 rows affected (0.00 sec)
- 查看效果
至此延迟备份已完成
读写分离
安装mysqlrouter
[root@localhost ~]# rpm -ivh mysql-router-community-8.0.23-1.el7.x86_64.rpm
警告:mysql-router-community-8.0.23-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY
准备中... ################################# [100%]
正在升级/安装...
1:mysql-router-community-8.0.23-1.e################################# [100%]
关闭防火墙和selinux
master和backup服务器都需要关闭
- 临时
防火墙
[root@slave ~]# service firewalld stop
Redirecting to /bin/systemctl stop firewalld.service
selinux
[root@slave ~]# setenforce=0
- 永久
防火墙
[root@slave ~]# systemctl disable firewalld
selinux
vim /etc/selinux/config
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
# enforcing - SELinux security policy is enforced.
# permissive - SELinux prints warnings instead of enforcing.
# disabled - No SELinux policy is loaded.
SELINUX=disabled
# SELINUXTYPE= can take one of three values:
# targeted - Targeted processes are protected,
# minimum - Modification of targeted policy. Only selected processes are protected.
# mls - Multi Level Security protection.
SELINUXTYPE=targeted
修改配置文件
- 查看配置文件
[root@localhost ~]# cd /etc/mysqlrouter/
[root@localhost mysqlrouter]# ls
mysqlrouter.conf
[root@localhost mysqlrouter]# vim mysqlrouter.conf
- 修改配置文件
# 名字可以自定义
[routing:read_write]
#是mysql-router服务器的ip地址
bind_address = 192.168.186.130
bind_port = 7001
#支持可读可写
mode = read-write
#mysql-master服务器的ip地址:mysql服务的端口号
destinations = 192.168.186.128:3306
max_connections = 65535
max_connect_errors = 100
client_connect_timeout = 9
[routing:read_only]
#是mysql-router服务器的ip地址
bind_address = 192.168.186.130
bind_port = 7002
# 仅可读
mode = read-only
# mysql-slave服务器的ip地址:mysql服务的端口号
destinations = 192.168.186.129:3306,192.168.186.132:3306,192.168.186.133:3306
max_connections = 65535
max_connect_errors = 100
client_connect_timeout = 9
- 开启mysqlrouter服务
[root@localhost mysqlrouter]# service mysqlrouter start
Redirecting to /bin/systemctl restart mysqlrouter.service
- 查看7001和7002端口是否启动
[root@localhost mysqlrouter]# yum install net-tools -y
创建用户和授权
在master上建立
- 可读可写的用户
root@(none) 17:13 mysql>create user 'write-read'@'%' identified by 'wp123456';
Query OK, 0 rows affected (1.10 sec)
root@(none) 17:16 mysql>grant all on *.* to 'write-read'@'%' identified by 'wp123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
- 仅读的用户
root@(none) 17:14 mysql>create user 'only-read'@'%' identified by 'wp123456';
Query OK, 0 rows affected (0.00 sec)
root@(none) 17:15 mysql>grant select on *.* to 'only-read'@'%' identified by 'wp123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
测试连接
所连接的时mysqlrouter ,划分权限的依靠用户所被授予的权限,端口不能限制权限
[root@client ~]# mysql -h 192.168.186.130 -P 7001 -uwrite-read -pwp123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.33-log MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
验证权限
可读写用户
write-read@(none) 19:44 mysql>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| TENNIS |
| mysql |
| performance_schema |
| sys |
| test1 |
| w |
+--------------------+
7 rows in set (0.04 sec)
write-read@(none) 19:44 mysql>create database test;
Query OK, 1 row affected (0.03 sec)
write-read@(none) 19:45 mysql>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| TENNIS |
| mysql |
| performance_schema |
| sys |
| test |
| test1 |
| w |
+--------------------+
8 rows in set (0.00 sec)
仅读用户
only-read@(none) 21:08 mysql>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| TENNIS |
| mysql |
| performance_schema |
| sys |
| test |
| test1 |
| w |
+--------------------+
8 rows in set (0.04 sec)
only-read@(none) 21:34 mysql>create database 1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1
双vip高可用
1.安装keepalived
master和backup都安装
[root@mysqlrouter ~]# yum install keepalived -y
查看版本
[root@mysqlrouter ~]# keepalived -v
Keepalived v1.3.5 (03/19,2017), git commit v1.3.5-6-g6fa32f2
Copyright(C) 2001-2017 Alexandre Cassen, <acassen@gmail.com>
Build options: PIPE2 LIBNL3 RTA_ENCAP RTA_EXPIRES RTA_PREF RTA_VIA FRA_OIFNAME FRA_SUPPRESS_PREFIXLEN FRA_TUN_ID RTAX_CC_ALGO RTAX_QUICKACK LIBIPTC LIBIPSET_DYNAMIC LVS LIBIPVS_NETLINK VRRP VRRP_AUTH VRRP_VMAC SOCK_NONBLOCK SOCK_CLOEXEC FIB_ROUTING INET6_ADDR_GEN_MODE SNMP_V3_FOR_V2 SNMP SNMP_KEEPALIVED SNMP_CHECKER SNMP_RFC SNMP_RFCV2 SNMP_RFCV3 SO_MARK
2.修改配置文件
[root@mysqlrouter ~]# vim /etc/keepalived/keepalived.conf
参数解释
此处只说明了需要用到的参数,如需了解更加详细的参数信息请点击这里
[root@mysqlrouter keepalived]# vim keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
acassen@firewall.loc
failover@firewall.loc
sysadmin@firewall.loc
}
notification_email_from Alexandre.Cassen@firewall.loc
smtp_server 192.168.200.1
smtp_connect_timeout 30
router_id LVS_DEVEL
vrrp_skip_check_adv_addr
vrrp_strict
vrrp_garp_interval 0
vrrp_gna_interval 0
}
# 定义一个vrrp协议的实例,名字为VI_1
vrrp_instance VI_1 {
# 为master的状态
state MASTER
# 指定监听网络的接口
interface eth33
# 虚拟路由器id
virtual_router_id 51
# 优先级 范围1~255
priority 100
# 宣告消息时间间隔--检查间隔 1秒
advert_int 1
# 身份验证
authentication {
# 验证类型 PASS表示用密码验证
auth_type PASS
# 密码
auth_pass 1111
}
# 虚拟ip地址 vip
virtual_ipaddress {
192.168.186.88
}
}
虚拟路由需一致,如修改,master和backup都要修改
实际配置
由于是双VIP,此处我们需要配置两个vip,让master和backup互为主主
,最大的利用资源
- master的修改
做master - - - VI_1
- 删除从virtual server开始的所有(从这里开始主要是keepalived的负载均衡作用,此次我们只使用keepalived的高可用)
- 注释 vrrp_strict
# vrrp_strict
- 修改状态为backup
state MASTER
- 修改网络接口与本机一致 - - - 一致则不用修改
interface eth33
- 修改virtual_router_id —
一致
- 修改优先级 - - -优先级高于backup
- 修改虚拟ip地址vip - - -
一致
做backup - - - VI_2
- 修改vrrp_instance 为
VI_2
- 修改状态为backup
state BACKUP
- 修改网络接口与本机一致 - - - 一致则不用修改
interface eth33
- 修改virtual_router_id —
一致
- 修改优先级 - - - 优先级低于master
- 修改虚拟ip地址vip - - -
一致
! Configuration File for keepalived
global_defs {
notification_email {
acassen@firewall.loc
failover@firewall.loc
sysadmin@firewall.loc
}
notification_email_from Alexandre.Cassen@firewall.loc
smtp_server 192.168.200.1
smtp_connect_timeout 30
router_id LVS_DEVEL
vrrp_skip_check_adv_addr
# vrrp_strict
vrrp_garp_interval 0
vrrp_gna_interval 0
}
vrrp_instance VI_1 {
state MASTER
interface ens33
virtual_router_id 51
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.186.88
}
}
vrrp_instance VI_2 {
state BACKUP
interface ens33
virtual_router_id 52
priority 80
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.186.90
}
}
- backup的修改
做backup - - - VI_1
- 删除从virtual server开始的所有
- 注释 vrrp_strict
# vrrp_strict
- 修改状态为backup
state BACKUP
- 修改网络接口与本机一致 - - - 一致则不用修改
interface eth33
- 修改virtual_router_id —
一致
- 修改优先级 - - - 优先级低于master
- 修改虚拟ip地址vip - - -
一致
做master - - - VI_2
- 修改vrrp_instance 为
VI_2
- 修改状态为backup
state MASTER
- 修改网络接口与本机一致 - - - 一致则不用修改
interface eth33
- 修改virtual_router_id —
一致
- 修改优先级 - - - 优先级高于master
- 修改虚拟ip地址vip - - -
一致
! Configuration File for keepalived
global_defs {
notification_email {
acassen@firewall.loc
failover@firewall.loc
sysadmin@firewall.loc
}
notification_email_from Alexandre.Cassen@firewall.loc
smtp_server 192.168.200.1
smtp_connect_timeout 30
router_id LVS_DEVEL
vrrp_skip_check_adv_addr
# vrrp_strict
vrrp_garp_interval 0
vrrp_gna_interval 0
}
vrrp_instance VI_1 {
state BACKUP
interface ens33
virtual_router_id 51
priority 80
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.186.88
}
}
vrrp_instance VI_2 {
state MASTER
interface ens33
virtual_router_id 52
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.186.90
}
}
修改mysqlrouter的配置
允许client可以通过vip连接数据库
# 名字可以自定义
[routing:read_write]
#绑定vip的地址,为了方便允许任意端口可接入
bind_address = 0.0.0.0
bind_port = 7001
#支持可读可写
mode = read-write
#mysql-master服务器的ip地址:mysql服务的端口号
destinations = 192.168.186.128:3306
max_connections = 65535
max_connect_errors = 100
client_connect_timeout = 9
[routing:read_only]
#绑定vip的地址,为了方便允许任意端口可接入
bind_address = 0.0.0.0
bind_port = 7002
# 仅可读
mode = read-only
# mysql-slave服务器的ip地址:mysql服务的端口号
destinations = 192.168.186.129:3306
max_connections = 65535
max_connect_errors = 100
client_connect_timeout = 9
3.刷新mysql服务,启动keepalived服务
master和backup都刷新
- 开启mysqlrouter服务
[root@localhost mysqlrouter]# service mysqlrouter start
Redirecting to /bin/systemctl restart mysqlrouter.service
- 开启keepalived服务
[root@mysqlrouter keepalived]# service keepalived start
Redirecting to /bin/systemctl start keepalived.service
4.查看效果
查看ip
master
[root@localhost ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:8b:e5:f8 brd ff:ff:ff:ff:ff:ff
inet 192.168.186.130/24 brd 192.168.186.255 scope global noprefixroute dynamic ens33
valid_lft 1505sec preferred_lft 1505sec
inet 192.168.186.88/32 scope global ens33
valid_lft forever preferred_lft forever
inet6 fe80::522a:8ea1:760d:44f8/64 scope link noprefixroute
valid_lft forever preferred_lft forever
backup
[root@localhost ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:70:4a:ae brd ff:ff:ff:ff:ff:ff
inet 192.168.186.131/24 brd 192.168.186.255 scope global noprefixroute dynamic ens33
valid_lft 1582sec preferred_lft 1582sec
inet 192.168.186.90/32 scope global ens33
valid_lft forever preferred_lft forever
inet6 fe80::c7fd:392d:c0ef:41ff/64 scope link noprefixroute
valid_lft forever preferred_lft forever
master和backup均有一个vip
在mysql-master上查看连接信息
root@(none) 10:48 mysql>show processlist;
+----+------------+-----------------------+------+------------------+-------+---------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------------+-----------------------+------+------------------+-------+---------------------------------------------------------------+------------------+
| 6 | slave | 192.168.186.129:51530 | NULL | Binlog Dump GTID | 53274 | Master has sent all binlog to slave; waiting for more updates | NULL |
| 7 | write-read | 192.168.186.130:48134 | NULL | Sleep | 88 | | NULL |
| 8 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+------------+-----------------------+------+------------------+-------+---------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
通过查看连接信息显示,真正连接到mysql-master上的是mysqlrouter的ip地址,而不是vip
压力测试
使用sysbench进行压力测试,点击sysbench了解更多详细操作
安装sysbench
下载解压
wget https://github.com/akopytov/sysbench/archive/1.0.zip -O "sysbench-1.0.zip"
unzip sysbench-1.0.zip
cd sysbench-1.0
安装依赖
yum install automake libtool –y
安装
确保安装此时位于之前解压的sysbench目录中
[root@localhost sysbench-1.0]# ./autogen.sh
[root@localhost sysbench-1.0]#./configure
[root@localhost sysbench-1.0]# export LD_LIBRARY_PATH=/usr/local/mysql/include #这里换成机器中mysql路径下的include
[root@localhost sysbench-1.0]# make
安装成功
[root@localhost sysbench-1.0]# sysbench -version
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
cpu测试
[root@localhost sysbench-1.0]# sysbench --test=cpu --cpu-max-prime=20000 run
WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 1
Initializing random number generator from current time
Prime numbers limit: 20000
Initializing worker threads...
Threads started!
CPU speed:
events per second: 487.17
General statistics:
total time: 10.0012s
total number of events: 4873
Latency (ms):
min: 1.93
avg: 2.05
max: 4.17
95th percentile: 2.52
sum: 9995.00
Threads fairness:
events (avg/stddev): 4873.0000/0.00
execution time (avg/stddev): 9.9950/0.00
线程测试
[root@localhost sysbench-1.0]# sysbench --test=threads --num-threads=64 --thread-yields=100 --thread-locks=2 run
WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.
WARNING: --num-threads is deprecated, use --threads instead
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 64
Initializing random number generator from current time
Initializing worker threads...
Threads started!
General statistics:
total time: 10.0149s
total number of events: 47017
Latency (ms):
min: 0.12
avg: 13.62
max: 10013.19
95th percentile: 0.55
sum: 640494.67
Threads fairness:
events (avg/stddev): 734.6406/3334.72
execution time (avg/stddev): 10.0077/0.00
磁盘IO性能测试
[root@localhost sysbench-1.0]# sysbench --test=fileio --num-threads=16 --file-total-size=3G --file-test-mode=rndrw prepare
[root@localhost sysbench-1.0]# sysbench --test=fileio --num-threads=16 --file-total-size=3G --file-test-mode=rndrw run
WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.
WARNING: --num-threads is deprecated, use --threads instead
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 16
Initializing random number generator from current time
Extra file open flags: (none)
128 files, 24MiB each
3GiB total file size
Block size 16KiB
Number of IO requests: 0
Read/Write ratio for combined random IO test: 1.50
Periodic FSYNC enabled, calling fsync() each 100 requests.
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random r/w test
Initializing worker threads...
Threads started!
File operations:
reads/s: 772.40
writes/s: 514.37
fsyncs/s: 1847.51
Throughput:
read, MiB/s: 12.07
written, MiB/s: 8.04
General statistics:
total time: 10.1141s
total number of events: 29656
Latency (ms):
min: 0.00
avg: 5.41
max: 442.80
95th percentile: 39.65
sum: 160551.68
Threads fairness:
events (avg/stddev): 1853.5000/243.78
execution time (avg/stddev): 10.0345/0.06
[root@localhost sysbench-1.0]# sysbench --test=fileio --num-threads=16 --file-total-size=3G --file-test-mode=rndrw cleanup
WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.
WARNING: --num-threads is deprecated, use --threads instead
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Removing test files...
上述参数指定了最大创建16个线程,创建的文件总大小为3G,文件读写模式为随机读
内存测试
[root@localhost sysbench-1.0]# sysbench --test=memory --memory-block-size=8k --memory-total-size=4G run
WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 1
Initializing random number generator from current time
Running memory speed test with the following options:
block size: 8KiB
total size: 4096MiB
operation: write
scope: global
Initializing worker threads...
Threads started!
Total operations: 524288 (1606122.75 per second)
4096.00 MiB transferred (12547.83 MiB/sec)
General statistics:
total time: 0.3248s
total number of events: 524288
Latency (ms):
min: 0.00
avg: 0.00
max: 0.69
95th percentile: 0.00
sum: 266.02
Threads fairness:
events (avg/stddev): 524288.0000/0.00
execution time (avg/stddev): 0.2660/0.00
排错
主从复制出现错误
- 可能出现的错误:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHANGE MASTER TO MASTER_HOST='192.168.186.139',
MASTER_USER='slave',
MASTER_P' at line 1
解决方法
可以使用下列语句
CHANGE MASTER TO
MASTER_HOST='192.168.186.139',
MASTER_USER='slave',
MASTER_PASSWORD='wp123456',
MASTER_LOG_FILE='localhost-bin.000005',
MASTER_LOG_POS=38028;
ERROR 1794 (HY000): Slave is not configured or failed to initialize properly. You must at least set --server-id to enable either a master or a slave. Additional error messages can be found in the MySQL error log.
解决方法
1.drop 备份的 ibd表
use mysql;
drop table if exists slave_master_info;
drop table if exists slave_relay_log_info;
drop table if exists slave_worker_info;
drop table if exists innodb_index_stats;
drop table if exists innodb_table_stats;
2.重建
mysql> source /usr/local/mysql/share/mysql_system_tables.sql
3.重启数据库
[root@localhost ~]# service mysql restart
Redirecting to /bin/systemctl restart mysql.service
线程启动失败
- mysql服务不能启动
[root@master mysql]# service mysqld restart
ERROR! MySQL server PID file could not be found!
Starting MySQL.........
使用ps aux查看进程
[root@master mysql]# ps aux |grep mysqld
root 1755 0.0 0.1 11824 1580 pts/0 S 10:38 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/localhost.localdomain.pid
mysql 1909 0.0 21.3 1545108 212112 pts/0 Sl 10:38 0:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=localhost.localdomain.err --open-files-limit=8192 --pid-file=/data/mysql/localhost.localdomain.pid --socket=/data/mysql/mysql.sock --port=3306
root 3747 0.0 0.1 11824 1584 pts/0 S 11:28 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/master.pid
mysql 3963 1.4 19.2 976416 191748 pts/0 Sl 11:28 0:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=master.err --open-files-limit=8192 --pid-file=/data/mysql/master.pid --socket=/data/mysql/mysql.sock --port=3306
root 3993 0.0 0.0 112824 976 pts/0 R+ 11:28 0:00 grep --color=auto mysqld
发现有两个mysqld_safe和basedir的进程在启动,找出不是自己指定PID路径的进程
kill -9 进程号
[root@master mysql]# kill -9 1755
[root@master mysql]# kill -9 1909
查看进程
[root@master mysql]# ps aux |grep mysqld
root 3747 0.0 0.1 11824 1584 pts/0 S 11:28 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/master.pid
mysql 3963 0.5 21.2 1479376 211308 pts/0 Sl 11:28 0:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=master.err --open-files-limit=8192 --pid-file=/data/mysql/master.pid --socket=/data/mysql/mysql.sock --port=3306
root 4011 0.0 0.0 112824 976 pts/0 R+ 11:29 0:00 grep --color=auto mysqld
重启mysql服务
[root@master mysql]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
- SQL线程没有启动
查看最后一个错误
如果错误提示如上示两图,错误原因都是master和slave的数据不一致,slave服务器有数据丢失的可能,导致master所作的操作没有同步到slave上
- IO线程没有开启
错误提醒
Last_IO_Error: error connecting to master 'slave@192.168.186.128:3306' - retry-time: 60 retries: 1
此类的错误都是主库删除多余的用户名,导致从库没有此信息造成主从故障!
解决方法
mysql> grant select on dbname *.* to "'select_user'"@"%" identified by "123456";
mysql> flush privileges;
mysql> stop slave;
mysql> start slave;
以上命令执行后,从库’select_user’也会sql线程也会自动删掉select_user用户,主从同步恢复正常。
读写分离出现错误
- 排错
查看错误提醒
[root@localhost mysqlrouter]# journalctl -xe
4月 07 16:56:28 localhost dhclient[28665]: DHCPREQUEST on ens33 to 192.168.186.254 port 67 (xid=0x753f40bc)
4月 07 16:56:28 localhost dhclient[28665]: DHCPACK from 192.168.186.254 (xid=0x753f40bc)
4月 07 16:56:28 localhost NetworkManager[6611]: <info> [1617785788.0258] dhcp4 (ens33): address 192.168.186.140
4月 07 16:56:28 localhost NetworkManager[6611]: <info> [1617785788.0260] dhcp4 (ens33): plen 24 (255.255.255.0)
4月 07 16:56:28 localhost NetworkManager[6611]: <info> [1617785788.0260] dhcp4 (ens33): gateway 192.168.186.2
4月 07 16:56:28 localhost NetworkManager[6611]: <info> [1617785788.0260] dhcp4 (ens33): lease time 1800
4月 07 16:56:28 localhost NetworkManager[6611]: <info> [1617785788.0260] dhcp4 (ens33): nameserver '192.168.186.2'
4月 07 16:56:28 localhost NetworkManager[6611]: <info> [1617785788.0260] dhcp4 (ens33): domain name 'localdomain'
4月 07 16:56:28 localhost NetworkManager[6611]: <info> [1617785788.0260] dhcp4 (ens33): state changed bound -> bound
4月 07 16:56:28 localhost dbus[6005]: [system] Activating via systemd: service name='org.freedesktop.nm_dispatcher' unit='dbus-org.freedesktop.n
4月 07 16:56:28 localhost systemd[1]: Starting Network Manager Script Dispatcher Service...
-- Subject: Unit NetworkManager-dispatcher.service has begun start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit NetworkManager-dispatcher.service has begun starting up.
4月 07 16:56:28 localhost dhclient[28665]: bound to 192.168.186.140 -- renewal in 894 seconds.
4月 07 16:56:28 localhost dbus[6005]: [system] Successfully activated service 'org.freedesktop.nm_dispatcher'
4月 07 16:56:28 localhost systemd[1]: Started Network Manager Script Dispatcher Service.
-- Subject: Unit NetworkManager-dispatcher.service has finished start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit NetworkManager-dispatcher.service has finished starting up.
--
-- The start-up result is done.
4月 07 16:56:28 localhost nm-dispatcher[28943]: req:1 'dhcp4-change' [ens33]: new request (2 scripts)
4月 07 16:56:28 localhost nm-dispatcher[28943]: req:1 'dhcp4-change' [ens33]: start running ordered scripts...
查看mysqlroute日志
[root@localhost mysqlrouter]# vim /var/log/mysqlrouter/mysqlrouter.log
2021-04-07 17:05:22 io INFO [7efc566de880] starting 1 io-threads, using backend 'linux_epoll'
2021-04-07 17:05:23 keepalive INFO [7efc51a70700] keepalive started with interval 60
2021-04-07 17:05:23 keepalive INFO [7efc51a70700] keepalive
2021-04-07 17:05:25 routing INFO [7efc50a6e700] [routing:read_write] started: listening on 192.168.186.140:7001, routing strategy = first-available
2021-04-07 17:05:25 routing INFO [7efc5126f700] [routing:read_only] started: listening on 192.168.186.140:7002, routing strategy = round-robin
2021-04-07 17:06:23 keepalive INFO [7efc51a70700] keepalive
2021-04-07 17:07:23 keepalive INFO [7efc51a70700] keepalive
keepalived出错
1.vrrp配置不一致
解决方法:将master和backup的keepalived的配置文件里的vrid修改为一致的
2.网络通信出现问题
- 如防火墙阻止了vrrp报文的通信
查看vip的mac地址
[root@slave ~]# arping 192.168.186.88
ARPING 192.168.186.88 from 192.168.186.129 ens33
Unicast reply from 192.168.186.88 [00:0C:29:8B:E5:F8] 0.701ms
Unicast reply from 192.168.186.88 [00:0C:29:70:4A:AE] 0.726ms
发现一个vip绑定了两个mac
解决方法:关闭防火墙
- 网络出现问题
解决方法:检查网线
压力测试出错
出现下列错误
[root@qht134 sysbench-master]# /usr/local/sysbench/bin/sysbench --version
/usr/local/sysbench/bin/sysbench: error while loading shared libraries: libmysqlclient.so.20: cannot open shared object file: No such file or directory
解决方法:
安装好sysbench后,试着打开时出错:
[root@qht134 sysbench-master]# /usr/local/sysbench/bin/sysbench --version
/usr/local/sysbench/bin/sysbench: error while loading shared libraries: libmysqlclient.so.20: cannot open shared object file: No such file or directory
字面意思就是打不到共享库文件libmysqlclient.so.20.
先查找一下本地有没有这个文件:
[root@qht134 /]# find . -name 'libmysqlclient*' ./usr/lib64/mysql/libmysqlclient.so.16
./usr/lib64/mysql/libmysqlclient.so.16.0.0
./usr/lib64/mysql/libmysqlclient_r.so.16
./usr/lib64/mysql/libmysqlclient_r.so.16.0.0
./usr/local/mysql/lib/libmysqlclient.so.20
./usr/local/mysql/lib/libmysqlclient.so.20.3.8
./usr/local/mysql/lib/libmysqlclient.a
./usr/local/mysql/lib/libmysqlclient.so
在/usr/loca/mysql/lib下面有这个文件,解决方法是先建立一个软链接到/usr/local/lib
[root@qht134 /]# ln -s /usr/local/mysql/lib/libmysqlclient.so.20 /usr/local/lib/libmysqlclient.so.20
接着在/etc/ld.so.cnf中加入/usr/loca/lib这一行
[root@qht134 /]# cat /etc/ld.so.conf
include ld.so.conf.d/*.conf
/usr/local/lib
执行ldconfig -v更新下配置就可以了
[root@qht134 /]# /sbin/ldconfig -v
现在sysbench可以执行了!
[root@qht134 /]# /usr/local/sysbench/bin/sysbench --version
sysbench 1.1.0