一、工具介绍
构建一个高可用的能实现读写分离的高效的MySQL集群，确保业务的稳定，能沟通方便的监控整个集群，同时利用ansible能批量的去部署和管理整个集群，提高管控效率。
1.Keepalived
Keepalived是一个基于VRRP协议的软件实现，主要用于提供负载均衡和高可用性。它通过VRRP协议实现多台机器之间的故障转移服务，确保在主服务器（Master）故障时，备份服务器（Backup）能够自动接管主服务器的工作，从而实现系统的高可用性。同时，Keepalived也可以实现负载均衡，将请求均匀地分发到多个服务器上，提高系统的整体性能和可扩展性。
2.GTID半同步主从复制
GTID（Global Transaction ID）半同步主从复制是MySQL数据库中的一种复制策略，旨在提高数据的安全性和一致性。在这种策略中，GTID用于唯一标识一个已提交的事务，它由UUID和TID组成，其中UUID代表MySQL实例的唯一标识，TID则代表该实例上已经提交的事务数量。
在GTID半同步主从复制中，主库在执行完客户端提交的事务后，不是立即返回给客户端，而是等待至少一个从库接收到并写到relay log中才返回。这种策略介于异步复制和全同步复制之间，既提高了数据的安全性，又避免了全同步复制可能带来的性能瓶颈。
使用GTID进行复制时，主库在提交事务时会创建与该事务对应的GTID，从库在应用中继日志时会使用GTID来识别和跟踪每个事务。当启动新的从库或因故障转移到新的主库时，可以使用GTID来标识复制的位置，从而简化了这些任务。
此外，GTID还使得主从自动定位和切换成为可能，不再需要像以前那样指定文件和位置。通过使用master_auto_position=1自动匹配GTID断点进行复制，slave端在接受master的binlog时，会校验GTID值。
总的来说，GTID半同步主从复制结合了异步复制和全同步复制的优点，既保证了数据的安全性，又避免了不必要的性能损失。在搭建基于GTID的半同步主从复制MySQL集群时，需要开启GTID功能，并在主从配置文件中进行相应设置。同时，为了确保复制的正确性和稳定性，还需要注意监控从库的状态和性能，以及及时处理可能出现的故障和错误。
3.Ansible
Ansible是一款运维自动化工具，它的主要功能是帮助运维人员实现IT工作的自动化，降低人为操作出现的失误，提高业务自动化率和运维工作效率。Ansible常用于软件部署自动化、配置自动化、管理自动化以及持续集成等场景。
4.MySQLRouter
MySQL Router 是一个轻量级的中间件，它提供透明的路由到 MySQL Group Replication 或 MySQL InnoDB Cluster 的功能。它允许客户端应用程序连接到 MySQL Router，然后由 Router 负责将连接路由到集群中的合适服务器。这有助于简化客户端应用程序的配置，因为它们只需要知道 MySQL Router 的地址，而不需要知道集群中所有服务器的详细信息。
5.Tpcc
TPCC压力测试是指使用TPCC（Transaction Processing Performance Council，交易处理性能委员会）推出的一套基准测试程序对数据库进行压力测试。TPCC测试模拟了一套电商环境，包括下单、支付、查订单、发货、查库存等各个环节，以获取数据并评估当前环境的吞吐量。
6.整个集群的架构
二、实验步骤
1.准备8台服务器
准备4台MySQL服务器，1台Ansible中台控制服务器，2台MySQLrouter服务器，1台测试服务器，配置好IP地址，IP地址不要出错，否则后续实验会出现断开情况，并给每台服务器命名，方便后续操作
Master：192.168.91.160
Slave-1：192.168.91.159
Slave-2：192.168.91.156
Backup：192.168.91.155
Ansible：192.168.91.154
MySQLRouter-1：192.168.91.157
MySQLRouter-2：192.168.91.158
Test：192.168.91.161
2.永久关闭selinux和防火墙（所有服务器）
为了以后服务器互相访问的时候不会被拦截，修改一些配置文件不会被阻拦，每台服务器都需要
修改selinux
[root@test ~]# vim /etc/selinux/config
[root@test ~]# getenforce
Disabled
关闭防火墙，并且关闭防火墙开机自启
[root@master ~]# systemctl disable firewalld
Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@master ~]# service firewalld stop
Redirecting to /bin/systemctl stop firewalld.service
[root@master ~]# systemctl status firewalld
● firewalld.service - firewalld - dynamic firewall daemon
Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
Active: inactive (dead)
Docs: man:firewalld(1)
4月 05 16:11:59 localhost.localdomain systemd[1]: Starting firewalld - dynamic firewall .....
4月 05 16:12:08 localhost.localdomain systemd[1]: Started firewalld - dynamic firewall d...n.
4月 05 16:12:09 localhost.localdomain firewalld[730]: WARNING: AllowZoneDrifting is enabl....
4月 05 18:43:40 master systemd[1]: Stopping firewalld - dynamic firewall daemon...
4月 05 18:44:23 master systemd[1]: Stopped firewalld - dynamic firewall daemon.
Hint: Some lines were ellipsized, use -l to show in full.
3.安装MySQL服务和半同步相关插件（4台MySQL服务器）
这里我使用的是一键安装MySQL-5.7.37，提取码为：0812
[root@master ~]# rz
[root@master ~]# ls
mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz onekey_install_mysql_binary_v3.sh
MySQL新建了一个root用户，密码为：Sanchuang23#，密码和用户可以自定义，只要修改一键安装的脚本就可以了
#set password='Sanchuang123#'; 修改root用户的密码为Sanchuang123#
mysql -uroot -p$tem_passwd --connect-expired-password -e "set password='Sanchuang123#';"
确定MySQL的Master：192.168.91.160，Slave：192.168.91.159，192.168.91.156，Backup(备份服务器)：192.168.91.155
4.开启二进制日志（4台MySQL服务器）
修改配置文件，开启二进制日志，注意每个服务器（无论是主服务器，从服务器还是备份服务器）都需要有一个唯一的服务器ID，以确保复制过程中的正确性和一致性
[root@master ~]# vim /etc/my.cnf
[mysqld]
log_bin
server_id = 1
expire_logs_days = 15
5.安装半同步插件（4台MySQL）
登录MySQL，用户账号和密码就是安装MySQL的时候创建的
[root@master ~]# mysql -uroot -p"Sanchuang123#"
如果使用一键安装后未能找到mysql命令，则输入su重新登录
[root@slave-1 ~]# mysql -uroot -p"Sanchuang123#"
bash: mysql: 未找到命令
[root@slave-1 ~]# su
[root@slave-1 ~]# mysql -uroot -p"Sanchuang123#"
mysql: [Warning] Using a password on the command line interface can be insecure.
root@(none) 20:06 mysql>
在Master机器上安装主半同步插件
root@(none) 19:43 mysql>INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (2.05 sec)
在Slave机器和Backup机器上安装从半同步插件
root@(none) 19:44 mysql>INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (3.85 sec)
6.永久修改半同步配置（4台MySQL）
Master
[root@master ~]# vim /etc/my.cnf
[mysqld]
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000 # 默认1 second
Slave和Backup
[root@slave-1 ~]# vim /etc/my.cnf
[mysqld]
rpl_semi_sync_slave_enabled=1
整体查看/etc/my.cnf配置文件信息
Master
[root@master ~]# cat /etc/my.cnf
[mysqld_safe]
[client]
socket=/data/mysql/mysql.sock
[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 = 1
expire_logs_days = 15
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000 #
[mysql]
auto-rehash
prompt=\u@\d \R:\m mysql>
Slave和Backup（就只有服务编号不同，Slave-1是2，Slave-2是3，Backup是4）
[root@slave-1 ~]# cat /etc/my.cnf
[mysqld_safe]
[client]
socket=/data/mysql/mysql.sock
[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
expire_logs_days = 15
rpl_semi_sync_slave_enabled=1
[mysql]
auto-rehash
prompt=\u@\d \R:\m mysql>
7.刷新mysql服务，查看配置是否生效（4台MySQL）
1.刷新MySQL服务（4台MySQL）
[root@master ~]# service mysqld restart
Shutting down MySQL..... SUCCESS!
Starting MySQL......... SUCCESS!
如果刷新服务出现问题
[root@master]# service mysqld restart
ERROR! MySQL server process #1299 is not running!
Starting MySQL... ERROR! The server quit without updating PID file (/data/mysql/mysql_extra.pid).
原因：
1.可能是selinux和防火墙没有关闭
2.半同步插件没有安装成功，就进行半同步配置
3.检查配置文件是否输入错误
4.查看系统日志或者查看MySQL的错误日志
[root@slave-2 log]# tail -10 /var/log/messages
Apr 5 20:20:40 localhost kernel: [<ffffffffb42350cb>] khugepaged+0x13b/0x490
Apr 5 20:20:40 localhost kernel: [<ffffffffb40c6d10>] ? wake_up_atomic_t+0x30/0x30
Apr 5 20:20:40 localhost kernel: [<ffffffffb4234f90>] ? khugepaged_scan_mm_slot+0xfd0/0xfd0
Apr 5 20:20:40 localhost kernel: [<ffffffffb40c5c21>] kthread+0xd1/0xe0
Apr 5 20:20:40 localhost kernel: [<ffffffffb40c5b50>] ? insert_kthread_work+0x40/0x40
Apr 5 20:20:40 localhost kernel: [<ffffffffb4793df7>] ret_from_fork_nospec_begin+0x21/0x21
Apr 5 20:20:40 localhost kernel: [<ffffffffb40c5b50>] ? insert_kthread_work+0x40/0x40
Apr 5 20:20:40 localhost kernel: Code: c3 0f 1f 80 00 00 00 00 48 83 ec 10 48 89 1c 24 4c 89 64 24 08 b9 3b 00 00 00 0f 1f 40 00 66 2e 0f 1f 84 00 00 00 00 00 48 ff c9 <48> 8b 06 48 8b 5e 08 48 8b 56 10 4c 8b 46 18 4c 8b 4e 20 4c 8b
Apr 5 20:21:32 localhost systemd-logind: New session 7 of user root.
Apr 5 20:21:32 localhost systemd: Started Session 7 of user root.
[root@slave-2 mysql]# tail -10 /data/mysql/slave-2.err
2024-04-05T12:27:43.043789Z 0 [Warning] No argument was provided to --log-bin, and --log-bin-index was not used; so replication may break when this MySQL server acts as a master and has his hostname changed!! Please use '--log-bin=slave-2-bin' to avoid this problem.
2024-04-05T12:27:50.459824Z 0 [Note] InnoDB: PUNCH HOLE support available
2024-04-05T12:27:50.466868Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2024-04-05T12:27:50.466875Z 0 [Note] InnoDB: Uses event mutexes
2024-04-05T12:27:50.466878Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
2024-04-05T12:27:50.466884Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2024-04-05T12:27:50.466888Z 0 [Note] InnoDB: Using Linux native AIO
2024-04-05T12:27:50.828814Z 0 [Note] InnoDB: Number of pools: 1
2024-04-05T12:27:51.457352Z 0 [Note] InnoDB: Using CPU crc32 instructions
2024-04-05T12:28:02.339958Z 0 [Note] InnoDB: Initializing buffer pool, total size = 512M, instances = 1, chunk size = 128M
2.查看二进制日志是否打开（4台MySQL）
root@(none) 20:31 mysql>show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (1.34 sec)
3.查看半同步是否打开（4台MySQL）
root@(none) 20:42 mysql>show variables like "%semi_%";
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 1000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set (0.09 sec)
8.统一数据
利用Ansible中台控制器服务器统一数据，为了保证4台MySQL服务器上的数据一致性。
1.在ansible机器上安装ansible连接四台MySQL
创建ansible与4台MySQL服务器之间的ssh免密通道
[root@ansible .ssh]# ssh-copy-id -i id_rsa.pub [email protected]
先安装epel-release，解决依赖关系
[root@ansible ~]# yum install epel-release -y
[root@ansible ~]# yum install ansible -y
修改ansible的配置文件，添加批量控制的服务器ip地址
[root@ansible ~]# vim /etc/ansible/hosts
[all-mysql]
192.168.91.160
192.168.91.159
192.168.91.156
192.168.91.155
[slave-backup]
192.168.91.159
192.168.91.156
192.168.91.155
2.创建一个初始数据库origin（Master）
root@(none) 20:51 mysql>create database origin;
Query OK, 1 row affected (1.10 sec)
root@(none) 21:01 mysql>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| origin |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
目的是模仿现实的真实情况，主服务器上有数据
3.导出Master上的所有数据库
[root@master ~]# mysqldump -uroot -p'Sanchuang123#' --all-databases > all_db.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@master ~]# ls
all_db.sql mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz onekey_install_mysql_binary_v3.sh
4.Master创建连接Ansiblessh的免密通道
[root@master ~]# ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:sXcNCRlFDemZmoG8+wPSCfeBuNT/1LFqdgvym6/yO1w root@master
The key's randomart image is:
+---[RSA 2048]----+
| o=++ |
| ..... |
| +.o .oo |
| + *oo +o. |
| . =S=.=...o |
| o =.=.. E |
| . o.+.o |
| . ooBo. |
| ..*BBo. |
+----[SHA256]-----+
[root@master .ssh]# ssh-copy-id -i /root/.ssh/id_rsa.pub [email protected]
5.数据传输
1.通过免密传输把Master上的all_db.sql的传给Ansible
[root@master ~]# scp all_db.sql [email protected]:/root
all_db.sql 100% 855KB 4.2MB/s 00:00
2.Ansible服务器通过ansible批量复制给剩余的3台MySQL服务器
[root@ansible ~]# ansible slave-backup -m copy -a "src=/root/all_db.sql dest=/root"
6.导入数据
利用ansible控制数据的导入
[root@ansible ~]# ansible slave-backup -m shell -a "mysql -uroot -p"Sanchuang123#" < /root/all_db.sql"
[WARNING]: Invalid characters were found in group names but not replaced, use -vvvv to see
details
192.168.91.156 | CHANGED | rc=0 >>
mysql: [Warning] Using a password on the command line interface can be insecure.
192.168.91.155 | CHANGED | rc=0 >>
mysql: [Warning] Using a password on the command line interface can be insecure.
192.168.91.159 | CHANGED | rc=0 >>
mysql: [Warning] Using a password on the command line interface can be insecure.
7.检查数据是否统一
root@(none) 21:33 mysql>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| origin |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.02 sec)
问题：导入数据的时候显示GTID问题
解决方法先不开启gtid功能，数据导入同步后，再开启gtid功能
9.打开GTID
1.修改配置文件(Master，Slave-1，Slave-2)
[root@master ~]# vim /etc/my.cnf
[mysqld]
gtid-mode=ON
enforce-gtid-consistency=ON
同时在Slave-1上打开log_slave_updates=ON（启用此选项时，从服务器可以作为一个中间服务器，允许其他从服务器从这个从服务器复制数据），为了实现与Backup服务器建立主从关系
[mysqld]
log_slave_updates=ON
2.刷新MySQL服务（Master，Slave-1，Slave-2）
[root@master ~]# service mysqld restart
如果出现一下错误
[root@slave-1 ~]# service mysqld restart
Shutting down MySQL............... SUCCESS!
Starting MySQL.................ERROR! The server quit without updating PID file (/data/mysql/slave-1.pid).
进入配置文件，把半同步配置的命令先注释掉，在重启一遍，进入MySQL，重新安装从半同步插件（如果在开启半同步之前先配置GTID就可以避免这个问题，GTID的配置与半同步配置在MySQL中的关系是相辅相成的，但并没有严格的先后顺序要求。然而，为了确保系统的稳定性和数据的一致性，通常建议先进行GTID的配置，然后再进行半同步复制的配置。）
[root@slave-1 ~]# vim /etc/my.cnf
[root@slave-1 ~]# service mysqld restart
ERROR! MySQL server PID file could not be found!
Starting MySQL.. SUCCESS!
[root@slave-1 ~]# mysql -uroot -p"Sanchuang123#"
root@(none) 21:58 mysql>INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.08 sec)
3.清除不纯洁的环境
在主服务器上
root@(none) 22:04 mysql>reset master;
Query OK, 0 rows affected (0.07 sec)
root@(none) 22:04 mysql>show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 | 154 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
在从服务器上
root@(none) 22:04 mysql>reset master;
Query OK, 0 rows affected (0.08 sec)
root@(none) 22:05 mysql>stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
root@(none) 22:05 mysql>reset slave all;
Query OK, 0 rows affected (0.09 sec)
当然之前没有做过gtid的，可以不用做这些操作，但是建议进行这些操作，以免之后实验出现状况
4.在Master上新建一个授权用户
目的是给Slave来复制二进制日志，实现半同步
root@(none) 22:04 mysql>grant replication slave on *.* to 'zhangwz'@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (1.19 sec)
5.从服务器连接主服务器（Slave-1，Slave-2）
root@(none) 22:05 mysql>CHANGE MASTER TO MASTER_HOST='192.168.91.160' ,
-> MASTER_USER='zhangwz',
-> MASTER_PASSWORD='123456',
-> MASTER_PORT=3306,
-> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.10 sec)
6.查看GTID是否连接（Slave-1，Slave-2）
root@(none) 22:14 mysql>start slave;
root@(none) 22:14 mysql>show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.91.160
Master_User: zhangwz
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 440
Relay_Log_File: slave-1-relay-bin.000002
Relay_Log_Pos: 655
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 440
Relay_Log_Space: 864
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: a6e80957-f33e-11ee-82b1-000c29ec852e
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: a6e80957-f33e-11ee-82b1-000c29ec852e:1
Executed_Gtid_Set: a6e80957-f33e-11ee-82b1-000c29ec852e:1
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
7.测试GTID连接
在Master上创建test库，创建test表，插入一些数据
root@(none) 22:09 mysql>create database test;
Query OK, 1 row affected (0.10 sec)
root@(none) 22:57 mysql>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| origin |
| performance_schema |
| sys |
| test |
+--------------------+
6 rows in set (0.19 sec)
root@(none) 22:57 mysql>use test;
Database changed
root@test 23:00 mysql>create table test(id int,name varchar(10));
Query OK, 0 rows affected (0.60 sec)
root@test 23:00 mysql>insert into test values(1,"汪海"),(2,"王亚林");
Query OK, 2 rows affected (4.69 sec)
Records: 2 Duplicates: 0 Warnings: 0
root@test 23:01 mysql>select * from test;
+------+-----------+
| id | name |
+------+-----------+
| 1 | 汪海 |
| 2 | 王亚林 |
+------+-----------+
2 rows in set (0.00 sec)
查看Slave-1，Slave-2的数据
root@(none) 22:57 mysql>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| origin |
| performance_schema |
| sys |
| test |
+--------------------+
6 rows in set (0.00 sec)
root@(none) 23:03 mysql>select * from test.test;
+------+-----------+
| id | name |
+------+-----------+
| 1 | 汪海 |
| 2 | 王亚林 |
+------+-----------+
2 rows in set (0.09 sec)
Master开启GTID后的导出数据
mysqldump -uroot -p"Sanchuang123#" --all-databases > all_db.sql --triggers --routines --events
10.Backup服务器
1.先配置打开GTID，并且刷新服务
记得先关闭半同步功能，刷新成功后，再下载一遍半同步插件，和之前步骤一样
[root@backup ~]# service mysqld restart
ERROR! MySQL server PID file could not be found!
Starting MySQL.. SUCCESS!
2.在备份服务器上指定主服务器是Slave-1
因为Slave-1打开了记录二进制日志的功能，允许Backup服务器去拉取
root@(none) 23:21 mysql>CHANGE MASTER TO MASTER_HOST='192.168.91.159' ,
-> MASTER_USER='zhangwz',
-> MASTER_PASSWORD='123456',
-> MASTER_PORT=3306,
-> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.09 sec)
3.设置延迟备份
防止Slave-1误操作之后，Backup也跟着做了误操作
root@(none) 23:21 mysql>CHANGE MASTER TO MASTER_DELAY = 10;
Query OK, 0 rows affected (0.08 sec)
这里设置的是十秒，是为了观察效果，可以根据实际情况自己调整
4.测试
root@(none) 23:24 mysql>start slave;
root@(none) 23:24 mysql>show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.91.159
Master_User: zhangwz
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: slave-1-bin.000001
Read_Master_Log_Pos: 1051
Relay_Log_File: backup-relay-bin.000002
Relay_Log_Pos: 1268
Relay_Master_Log_File: slave-1-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1051
Relay_Log_Space: 1476
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
Master_UUID: da511941-f33f-11ee-8b87-000c292ad689
Master_Info_File: /data/mysql/master.info
SQL_Delay: 10
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: a6e80957-f33e-11ee-82b1-000c29ec852e:1-4
Executed_Gtid_Set: a6e80957-f33e-11ee-82b1-000c29ec852e:1-4
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
5.制作计划任务备份Master里的数据
在Master和Backup上创建一个backup文件夹
[root@master ~]# mkdir -p /backup
[root@master ~]# cd /backup/
[root@backup ~]# mkdir -p /backup
编写一个备份脚本
[root@master backup]# vim all_db_backup.sh
#!/bin/bash
mkdir -p /backup
mysqldump -uroot -p'Sanchaung123#' --all-databases --triggers --routines --events >/backup/$(date +%Y%m%d%H%M%S)all_db.sql
scp /backup/$(date +%Y%m%d%H%M%S)all_db.sql 192.168.91.155:/backup
加入计划任务，每天的凌晨2:30
[root@master backup]# crontab -e
[root@master backup]# crontab -l
30 2 * * * bash /backup/all_db_backup.sh
这里是设置计划任务，如果需要更加高效率和高可用的备份，可以使用Rsync+Sersync实现文件的实时同步
11.MySQLRouter
MySQL Router很轻量级，只能通过不同的端口来实现简单的读/写分离，且读请求的调度算法只能使用默认的rr(round-robin)轮询算法。
1.安装MySQLRouter
2个MySQLRouter服务器都要上传
MySQLRouter-8.0.21
[root@mysqlrouter-1 ~]# ls
anaconda-ks.cfg mysql-router-community-8.0.21-1.el7.x86_64.rpm
[root@mysqlrouter-1 ~]# rpm -ivh mysql-router-community-8.0.21-1.el7.x86_64.rpm
警告：mysql-router-community-8.0.21-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY
准备中... ################################# [100%]
正在升级/安装...
1:mysql-router-community-8.0.21-1.e################################# [100%]
2.修改配置文件
#read-only
[routing:slaves]
bind_address = 192.168.91.157:7001 #MySQLRoute的IP
destinations = 192.168.91.159:3306,192.168.91.156:3306 #Slave的IP
mode = read-only
connect_timeout = 1
#read and write
[routing:masters]
bind_address = 192.168.91.157:7002
destinations = 192.168.91.160:3306 #Master的IP
mode = read-write
connect_timeout = 1
3.刷新服务
[root@mysqlrouter-1 mysqlrouter]# service mysqlrouter start
4.查看端口
[root@mysqlrouter-1 mysqlrouter]# yum install net-tools -y
[root@mysqlrouter-1 mysqlrouter]# netstat -anplut|grep mysql
tcp 0 0 192.168.91.157:7001 0.0.0.0:* LISTEN 1311/mysqlrouter
tcp 0 0 192.168.91.157:7002 0.0.0.0:* LISTEN 1311/mysqlrouter
#没有安装netstat用
[root@mysqlrouter-1 mysqlrouter]# ss -anplut|grep mysql
tcp LISTEN 0 128 192.168.91.157:7001 *:* users:(("mysqlrouter",pid=1311,fd=5))
tcp LISTEN 0 128 192.168.91.157:7002 *:* users:(("mysqlrouter",pid=1311,fd=4))
5.在Master上创建2个测试账号
一个是只读，一个是读写都可以
root@(none) 00:09 mysql>grant all on *.* to 'scwrite'@'%' identified by 'Sanchuang123#';
Query OK, 0 rows affected, 1 warning (0.20 sec)
root@(none) 00:09 mysql>grant select on *.* to 'scread'@'%' identified by 'Sanchuang123#';
Query OK, 0 rows affected, 1 warning (0.01 sec)
6.在测试机Test上测试读写分离
首先得在测试机上安装MySQL，其实这里可以直接用Master测试的，不过为了严谨性，不使用MySQL集群里面的机器
端口号写反不会影响操作，但是没有写权限的用户走7002端口也是只读
读写分离的关键点：其实是用户的权限，
让不同的用户连接不同的端口，最后仍然要到后端的mysql服务器里去验证是否有读写的权限
[root@test backup]# mysql -h 192.168.91.157 -P 7001 -uscread -p'Sanchuang123#'
scread@(none) 00:16 mysql>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| origin |
| performance_schema |
| sys |
| test |
+--------------------+
6 rows in set (0.00 sec)
scread@(none) 00:16 mysql>create database i;
ERROR 1044 (42000): Access denied for user 'scread'@'%' to database 'i'
[root@test backup]# mysql -h 192.168.91.157 -P 7002 -uscwrite -p'Sanchuang123#'
mysql: [Warning] Using a password on the command line interface can be insecure.
scwrite@(none) 00:20 mysql>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| origin |
| performance_schema |
| sys |
| test |
+--------------------+
6 rows in set (0.03 sec)
scwrite@(none) 00:20 mysql>create database tt;
Query OK, 1 row affected (0.11 sec)
查看其他从服务器
root@(none) 00:21 mysql>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| origin |
| performance_schema |
| sys |
| test |
| tt |
+--------------------+
7 rows in set (0.01 sec)
12.Keepalived
1.安装keepalived（MySQLRouter-1，MySQLRouter-2）
[root@mysqlrouter-1 mysqlrouter]# yum install keepalived -y
2.修改配置文件（MySQLRouter-1，MySQLRouter-2）
配置双vip，增强高可用和负载均衡，MySQLRouter-2与MySQLRouter-1配置内容一样，只要修改状态和优先级，确保虚拟ip可用
[root@mysqlrouter-1 mysqlrouter]# vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
[email protected]
[email protected]
[email protected]
}
notification_email_from [email protected]
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 #状态/MASTER
interface ens33
virtual_router_id 51 #虚拟路由id
priority 100 #优先级/200
advert_int 1
authentication {
auth_type PASS
auth_pass 1111 #认证密码
}
virtual_ipaddress {
192.168.91.199
}
}
vrrp_instance VI_2 {
state MASTER
interface ens33
virtual_router_id 61
priority 200
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.91.198
}
}
[root@test ~]# ping 192.168.91.198
PING 192.168.91.198 (192.168.91.198) 56(84) bytes of data.
From 192.168.91.161 icmp_seq=1 Destination Host Unreachable
From 192.168.91.161 icmp_seq=2 Destination Host Unreachable
From 192.168.91.161 icmp_seq=3 Destination Host Unreachable
^C
--- 192.168.91.198 ping statistics ---
5 packets transmitted, 0 received, +3 errors, 100% packet loss, time 4012ms
pipe 3
[root@test ~]# ping 192.168.91.199
PING 192.168.91.199 (192.168.91.199) 56(84) bytes of data.
From 192.168.91.161 icmp_seq=1 Destination Host Unreachable
From 192.168.91.161 icmp_seq=2 Destination Host Unreachable
From 192.168.91.161 icmp_seq=3 Destination Host Unreachable
From 192.168.91.161 icmp_seq=4 Destination Host Unreachable
^C
--- 192.168.91.199 ping statistics ---
5 packets transmitted, 0 received, +4 errors, 100% packet loss, time 4009ms
pipe 4
3.刷新服务（MySQLRouter-1，MySQLRouter-2）
[root@mysqlrouter-1 mysqlrouter]# service keepalived start
Redirecting to /bin/systemctl start keepalived.service
4.测试双vip
1.关掉MySQLRouter-2的keepalived
[root@mysqlrouter-2 keepalived]# service keepalived stop
Redirecting to /bin/systemctl stop keepalived.service
[root@mysqlrouter-2 keepalived]# ps -aux |egrep keep
root 1722 0.0 0.0 112824 976 pts/0 S+ 00:46 0:00 grep -E --color=auto keep
[root@mysqlrouter-1 keepalived]# ip add
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:29:9d:e2 brd ff:ff:ff:ff:ff:ff
inet 192.168.91.157/24 brd 192.168.91.255 scope global noprefixroute ens33
valid_lft forever preferred_lft forever
inet 192.168.91.198/32 scope global ens33
valid_lft forever preferred_lft forever
inet 192.168.91.199/32 scope global ens33
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fe29:9de2/64 scope link
valid_lft forever preferred_lft forever
[root@mysqlrouter-2 keepalived]# ip add
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:7c:37:be brd ff:ff:ff:ff:ff:ff
inet 192.168.91.158/24 brd 192.168.91.255 scope global noprefixroute ens33
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fe7c:37be/64 scope link
valid_lft forever preferred_lft forever
2.关掉MySQLRouter-1的keepalived
[root@mysqlrouter-1 keepalived]# service keepalived stop
Redirecting to /bin/systemctl restart keepalived.service
[root@mysqlrouter-1 mysqlrouter]# ps -aux |egrep keep
root 1521 0.0 0.0 112824 976 pts/0 S+ 00:45 0:00 grep -E --color=auto keep
[root@mysqlrouter-1 keepalived]# ip add
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:29:9d:e2 brd ff:ff:ff:ff:ff:ff
inet 192.168.91.157/24 brd 192.168.91.255 scope global noprefixroute ens33
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fe29:9de2/64 scope link
valid_lft forever preferred_lft forever
[root@mysqlrouter-2 keepalived]# ip add
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:7c:37:be brd ff:ff:ff:ff:ff:ff
inet 192.168.91.158/24 brd 192.168.91.255 scope global noprefixroute ens33
valid_lft forever preferred_lft forever
inet 192.168.91.198/32 scope global ens33
valid_lft forever preferred_lft forever
inet 192.168.91.199/32 scope global ens33
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fe7c:37be/64 scope link
valid_lft forever preferred_lft forever
3.查看MySQLRouter上是否都有vip
[root@mysqlrouter-2 keepalived]# service keepalived restart
Redirecting to /bin/systemctl restart keepalived.service
[root@mysqlrouter-2 keepalived]# ip add
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:7c:37:be brd ff:ff:ff:ff:ff:ff
inet 192.168.91.158/24 brd 192.168.91.255 scope global noprefixroute ens33
valid_lft forever preferred_lft forever
inet 192.168.91.199/32 scope global ens33
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fe7c:37be/64 scope link
valid_lft forever preferred_lft forever
[root@mysqlrouter-1 mysqlrouter]# service keepalived restart
Redirecting to /bin/systemctl restart keepalived.service
[root@mysqlrouter-1 mysqlrouter]# ip add
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:29:9d:e2 brd ff:ff:ff:ff:ff:ff
inet 192.168.91.157/24 brd 192.168.91.255 scope global noprefixroute ens33
valid_lft forever preferred_lft forever
inet 192.168.91.199/32 scope global ens33
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fe29:9de2/64 scope link
valid_lft forever preferred_lft forever
4.问题
出现双vip，也称脑裂现象
[root@mysqlrouter-1 ~]# ip add
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:29:9d:e2 brd ff:ff:ff:ff:ff:ff
inet 192.168.91.157/24 brd 192.168.91.255 scope global noprefixroute ens33
valid_lft forever preferred_lft forever
inet 192.168.91.198/32 scope global ens33
valid_lft forever preferred_lft forever
inet 192.168.91.199/32 scope global ens33
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fe29:9de2/64 scope link
valid_lft forever preferred_lft foreve
[root@mysqlrouter-2 ~]# ip add
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:7c:37:be brd ff:ff:ff:ff:ff:ff
inet 192.168.91.158/24 brd 192.168.91.255 scope global noprefixroute ens33
valid_lft forever preferred_lft forever
inet 192.168.91.198/32 scope global ens33
valid_lft forever preferred_lft forever
inet 192.168.91.199/32 scope global ens33
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fe7c:37be/64 scope link
valid_lft forever preferred_lft forever
1.vrid（虚拟路由id）不一样，认证密码不一样
2.网络通信有问题：中间有防火墙阻止了网络之间的选举的过程，vrrp报文的通信
3.配置文件出现错误，每个实例都有俩个vip
4.相同虚拟路由段的优先级一致
脑裂没有危害，能正常访问，反而还有负载均衡的作用
脑裂恢复的时候，还是有影响的，会短暂的中断，影响业务的
5.配置双vip后修改配置文件
目的是增强高可用
[root@mysqlrouter-1 ~]# vim /etc/mysqlrouter/mysqlrouter.conf
#read-only
[routing:slaves]
bind_address = 0.0.0.0:7001 #可以直接vip登录服务器，不用指定MySQLRouter的地址
destinations = 192.168.91.159:3306,192.168.91.156:3306
mode = read-only
connect_timeout = 1
#read and write
[routing:masters]
bind_address = 0.0.0.0:7002
destinations = 192.168.91.160:3306
mode = read-write
connect_timeout = 1
[root@mysqlrouter-1 ~]# service mysqlrouter restart
Redirecting to /bin/systemctl restart mysqlrouter.service
[root@mysqlrouter-1 ~]# netstat -anplut |egrep mysql
tcp 0 0 0.0.0.0:7001 0.0.0.0:* LISTEN 1425/mysqlrouter
tcp 0 0 0.0.0.0:7002 0.0.0.0:* LISTEN 1425/mysqlrouter
13.TPCC压力测试（Test测试机器）
1.下载源码包，解压安装
[root@test ~]# wget http://imysql.com/wp-content/uploads/2014/09/tpcc-mysql-src.tgz
-bash: wget: 未找到命令
[root@test ~]# yum install wget -y
[root@test ~]# wget http://imysql.com/wp-content/uploads/2014/09/tpcc-mysql-src.tgz
[root@test ~]# tar xf tpcc-mysql-src.tgz
2.生成两个二进制工具
tpcc_load（提供初始化数据的功能）和tpcc_start(进行压力测试)
[root@test ~]# cd tpcc-mysql/src
[root@test src]# ls
delivery.c main.c ordstat.c rthist.c sequence.h spt_proc.h trans_if.h
driver.c Makefile parse_port.h rthist.h slev.c support.c
load.c neword.c payment.c sequence.c spt_proc.c tpc.h
[root@test src]# make
[root@test src]# cd ..
[root@test tpcc-mysql]# ls
add_fkey_idx.sql create_table.sql load.sh schema2 src tpcc_start
count.sql drop_cons.sql README scripts tpcc_load
3.测试前准备
初始化数据库，在其他的服务器上连接到读写分离器上创建tpcc库，需要在测试的服务器上创建tpcc的库
[root@test tpcc-mysql]# mysqladmin -uscwrite -p'Sanchuang123#' -h 192.168.91.198 -P 7002 create tpcc
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
4.导入表到tpcc库里
将tpcc的create_table.sql 和add_fkey_idx.sql 远程拷贝到Master服务器上
[root@test tpcc-mysql]# scp create_table.sql add_fkey_idx.sql [email protected]:/root
在master服务器上导入create_table.sql 和add_fkey_idx.sql 文件
[root@master ~]# mysql -uroot -p'Sanchuang123#' tpcc <create_table.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@master ~]# mysql -uroot -p'Sanchuang123#' tpcc <add_fkey_idx.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
5.测试(Test机器）
1.执行脚本tpcc_load 去加载数据
[root@test tpcc-mysql]# ./tpcc_load 192.168.91.198:7002 tpcc scwrite Sanchuang123# 15
*************************************
*** ###easy### TPC-C Data Loader ***
*************************************
<Parameters>
[server]: 192.168.91.198
[port]: 7002
[DBname]: tpcc
[user]: scwrite
[pass]: Sanchuang123#
[warehouse]: 15
TPCC Data Load Started...
Loading Item
.................................................. 5000
...................................
真实测试中，数据库仓库一般不少于100个，如果配置了ssd，建议最少不低于1000个，为了实验的效率，这里选择的是15个仓库数量，加载数据的时间可能需要一段很长的时间，之后再执行下列语句
注意：server是要测试的服务器，db，user，password也是要测的服务器上mysql的信息
#./tpcc_load --help
./tpcc_load [server] [db] [user] [password] [warehouse]
服务器名 数据库名 用户名 密码 仓库数量
2.测试
[root@test tpcc-mysql]#./tpcc_start -h 192.168.91.198 -p 7002 -d tpcc -u scwrite -p Sanchuang123# -w 15 -c 12 -r 300 -l 360 -f test0.log -t test1.log - >test0.out
#./tpcc_start --help
tpcc_start -h [server_host] -P [port] -d [database_name] -u [mysql_user] -p [mysql_password] -w [warehouses] -c [connections] -r [warmup_time] -l [running_time] -i [report_interval] -f [report_file] -t [trx] - > out1各个参数用法如下：
-h server_host: 服务器名
-P port : 端口号，默认为3306
-d database_name: 数据库名
-u mysql_user : 用户名
-p mysql_password : 密码
-w warehouses: 仓库的数量
-c connections : 线程数，默认为1
-r warmup_time : 热身时间，单位:s，默认为10s , 热身是为了将数据加载到内存。（真实测试中建议热身时间不低于5分钟）
-l running_time: 测试时间，单位:s，默认为20s
-i report_interval: 指定生成报告间隔时长（真实测试中不低于30min）
-f report_file: 测试结果输出文件（一般命名为xxx.log）
-t trx：输出文件
out1: 将控制台输出存入文件out1中
三、总结
1.规划好整个集群的架构，配置要细心
2.防火墙和selinux的问题需要多注意
3.对MySQL的集群和高可要有深刻的了解
4.Keepalived的配置需要更加细心和IP地址的规划
5.对双vip的使用，添加2条负载均衡记录实现dns轮询，达到向2个vip负载均衡器上分流
本文转载自: https://blog.csdn.net/qq_63333972/article/details/137405387
版权归原作者 励志吃菊猫的咸鱼 所有， 如有侵权，请联系我们删除。
版权归原作者 励志吃菊猫的咸鱼 所有， 如有侵权，请联系我们删除。