双vip的MySQL高可用集群

最新推荐文章于 2024-04-24 14:48:34 发布

Vivant-W

最新推荐文章于 2024-04-24 14:48:34 发布

阅读量896

点赞数 1

分类专栏： mysql 文章标签： mysql 数据库 linux

本文链接：https://blog.csdn.net/weixin_54470937/article/details/115600465

版权

mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

介绍

构建一个高可用的能实现读写分离的高效的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

参考：https://blog.csdn.net/jolly10/article/details/80388217/

Vivant-W

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
1
评论
双vip的MySQL高可用集群

介绍步骤部署基于Gtid的主从复制准备工作①准备两台服务器，都开启mysql服务尽量两台服务器的数据库版本一致②msater服务器开启二进制日志master服务器和slave服务器的server_id不能相同，必须唯一master配置1.修改mysql配置文件开启二进制日志配置server_idvim /etc/my.cnf修改如下[mysqld]socket=/data/mysql/mysql.sockport = 3306open_files_limit =
复制链接

扫一扫