基于keepalived+gtid半同步主从复制的MySQL高可用集群+tpcc压力测试

最新推荐文章于 2024-06-02 14:54:06 发布

励志吃菊猫的咸鱼

最新推荐文章于 2024-06-02 14:54:06 发布

阅读量810

点赞数

文章标签： mysql 数据库压力测试网络 linux

本文链接：https://blog.csdn.net/qq_63333972/article/details/137405387

版权

一、工具介绍

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

先安装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 root@192.168.91.154

5.数据传输

1.通过免密传输把Master上的all_db.sql的传给Ansible

[root@master ~]# scp all_db.sql root@192.168.91.154:/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 {
     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 #状态/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 root@192.168.91.160:/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负载均衡器上分流

励志吃菊猫的咸鱼

关注

0
点赞
踩
12

收藏

觉得还不错? 一键收藏
1
评论
基于keepalived+gtid半同步主从复制的MySQL高可用集群+tpcc压力测试

构建一个高可用的能实现读写分离的高效的MySQL集群，确保业务的稳定，能沟通方便的监控整个集群，同时利用ansible能批量的去部署和管理整个集群，提高管控效率。
复制链接

扫一扫