本文共 12534 字，大约阅读时间需要 41 分钟。
中间件：middleware
客户机访问web服务器----> web服务器通过中间件来访问后面的db，如果是要读取数据库里的内容，中间件会任选一台db进行读取，如果是要往数据库里写内容，则只往主库里写，从而实现读写分离。
读与写会访问mysqlrouter不同的端口
中间件的种类：
MySQLrouter ----mysql官方提供的 mycat — 开源，中国，数据库分库分表的中间件什么是分库分表
分库分表就是为了解决由于数据量过大，而导致的数据库的性能下降的问题，将原来独立的数据库拆分为若干数据库组成，将数据大表拆分成若干数据表组成，使得单一数据库，单一数据表的数据量变小，从而达到提升数据库性能的问题
分表：一张商品信息表，可以将访问频次低的商品描述信息单独存放在一张表中，将访问频次较高的商品的基本信息单独存放在一张表中。
垂直分库：就是根据业务耦合性，将关联度低的不同表存储在不同的数据库垂直分表： 将一个表按照字段分成多表，每个表存储其中一部分字段优点：避免io争抢并减少锁表几率，查看详情的用户与商品信息浏览，互不影响；充分发挥热门数据的操作效率，商品信息的操作的高效率不会被商品描述的低效率所拖累。水平分库水平分表
项目名称：双vip的mysql高可用集群
项目环境：6台服务器，centos7.8 mysql 5.7.30 msyqlrouter :8.0.21 keepalived:2.0.10 项目描述：本项目的目的是构建一个高可用的能实现读写分离的高效的mysql集群，来确保业务的稳定，能沟通方便的监控整个集群，同时能批量的去部署管理整个集群。 项目步骤： 1，安装好centos7.8的系统，部署好ansible，在所有的机器之间配置ssh免密通道，、 2，部署zabbix监控系统 3，通过ansible去部署以二进制方式安装mysql（通过脚本一键安装） 4，使用ansible 安装mysqlrouter 和keepalived ，在另外2台中间件服务器上，实现读写分离和高可用，在keepalived上配置两个vip，互为master和backup ，更加好的提升高可用的性能，采用双vip 5，在3台mysql服务器上配置好主从复制，形成master+slave节点的集群，提供数据库服务 6， 尝试部署mysql的failover插件，实现自动的故障切换，确保master宕机，能自动提升另外一台slave为主，另外一台slave自动切换到新的master 上获取二进制日志 7，验证 8，使用压力测试软件来测试环境准备：
三台机器mysqlrouter ：192.168.0.211master ：192.168.0.171slave :192.168.0.173
具体步骤：
1，去官网下载mysqlrouter 安装包，传入linux[root@mysqlrouter ~]# lsanaconda-ks.cfgmysql-router-community-8.0.21-1.el7.x86_64.rpm#安装[root@mysqlrouter ~]# 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%]#查看mysqlrouter的配置文件[root@mysqlrouter ~]# cd /etc/mysqlrouter[root@mysqlrouter mysqlrouter]# lsmysqlrouter.conf
修改mysqlrouter的配置文件
[root@mysqlrouter mysqlrouter]# vim mysqlrouter.conf [root@mysqlrouter mysqlrouter]# cat mysqlrouter.conf [DEFAULT]logging_folder = /var/log/mysqlrouterruntime_folder = /var/run/mysqlrouterconfig_folder = /etc/mysqlrouter[logger]level = INFO[keepalive]interval = 60[routing:read_write]bind_address = 192.168.0.211 bind_port = 7001mode = read-writedestinations = 192.168.0.171:3306max_connections = 65535max_connect_errors = 100client_connect_timeout = 9[routing:read_only]bind_address = 192.168.0.211bind_port = 7002mode = read-onlydestinations = 192.168.0.173:3306max_connections = 65535max_connect_errors = 100client_connect_timeout = 9
启动mysqlrouter的服务
[root@mysqlrouter mysqlrouter]# service mysqlrouter startRedirecting to /bin/systemctl start mysqlrouter.service[root@mysqlrouter mysqlrouter]# netstat -antpluActive Internet connections (servers and established)Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 7048/sshd tcp 0 0 192.168.0.211:7001 0.0.0.0:* LISTEN 20972/mysqlrouter tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 7222/master tcp 0 0 192.168.0.211:7002 0.0.0.0:* LISTEN 20972/mysqlrouter tcp 0 36 192.168.0.211:22 192.168.0.39:62193 ESTABLISHED 20704/sshd: root@pt tcp 0 0 192.168.0.211:22 192.168.0.28:56784 ESTABLISHED 18280/sshd: root@pt tcp6 0 0 :::22 :::* LISTEN 7048/sshd tcp6 0 0 ::1:25 :::* LISTEN 7222/master
在master主机上新建用户并授权
wangwang ----可读可写---7001---->masterlele ----只读----7002----->slave
root@(none) 16:43 mysql>grant all on *.* to "wangwang"@"%" identified by 'Sanchuang123#';Query OK, 0 rows affected, 1 warning (0.01 sec)root@(none) 16:47 mysql>grant select on *.* to 'lele'@'%' identified by 'Sanchuang123#';Query OK, 0 rows affected, 1 warning (0.01 sec)
测试：
在开启一台虚拟机，充当客户机—192.168.0.172[root@delay log]# mysql -h 192.168.0.211 -uwangwang -p'Sanchuang123#' -P7001mysql: [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 8Server version: 5.7.30-log MySQL Community Server (GPL)Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show processlist;+----+----------+---------------------+------+-------------+--------+---------------------------------------------------------------+------------------+| Id | User | Host | db | Command | Time | State | Info |+----+----------+---------------------+------+-------------+--------+---------------------------------------------------------------+------------------+| 3 | xionghan | 192.168.0.173:44698 | NULL | Binlog Dump | 112898 | Master has sent all binlog to slave; waiting for more updates | NULL || 5 | root | localhost | NULL | Sleep | 1755 | | NULL || 7 | xionghan | 192.168.0.172:58874 | NULL | Binlog Dump | 1228 | Master has sent all binlog to slave; waiting for more updates | NULL || 8 | wangwang | 192.168.0.211:52592 | NULL | Query | 0 | starting | show processlist |+----+----------+---------------------+------+-------------+--------+---------------------------------------------------------------+------------------+4 rows in set (0.00 sec)mysql> create database sc_mysql;Query OK, 1 row affected (0.01 sec)mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || chenran || mysql || performance_schema || sanchuang || sc || sc_mysql || sys || wang1 || wangwang || wangwang1 || xh1 || xionghan |+--------------------+13 rows in set (0.07 sec)mysql>
实验改进，做mysqlrouter的高可用
再加一台机器，Mysqlrouter2，并用keepalived来做高可用，一台做master，一台做backup，用keepalived , --> vip问题：可能会出现一台闲置，如何解决？再搞一个vip，双vip实现keepalived 双主热备mysqlrouter-master ：192.168.0.211 ----master vip:192.168.0.233 backup vip :192.168.0.234mysqlrouter-backup ：192.168.0.212 ----master vip :192.168.0.234 backup vip :192.168.0.233在两台机器上安装并配置mysqlrouter 和keepalived
mysqlrouter的安装及配置如上：
keepalived 的安装及配置如下：keepalived 的安装
[root@mysqlrouter-master mysqlrouter]# yum install keepalived -y已加载插件：fastestmirrorLoading mirror speeds from cached hostfile * base: mirrors.163.com * epel: mirror.arizona.edu * extras: mirrors.163.com * updates: mirrors.aliyun.com
[root@mysqlrouter-backup mysqlrouter]# yum install keepalived -y已加载插件：fastestmirrorLoading mirror speeds from cached hostfile * base: mirrors.163.com * epel: mirror.arizona.edu * extras: mirrors.163.com * updates: mirrors.aliyun.com *
mysqlrouter-master的配置
[root@mysqlrouter-master keepalived]# cat keepalived.conf ! Configuration File for keepalivedbal_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 110 advert_int 1 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 192.168.0.233 }}vrrp_instance VI_2 { state MASTER interface ens33 virtual_router_id 52 priority 80 advert_int 1 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 192.168.0.234 }} [root@mysqlrouter keepalived]# cat keepalived.conf ! Configuration File for keepalivedglobal_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 53 priority 80 advert_int 1 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 192.168.0.233 }}vrrp_instance VI_2 { state MASTER interface ens33 virtual_router_id 54 priority 110 advert_int 1 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 192.168.0.234 }}