什么叫读写分离
中间件：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.211
master ：192.168.0.171
slave :192.168.0.173具体步骤：
1，去官网下载mysqlrouter 安装包，传入linux
[root@mysqlrouter ~]# ls
anaconda-ks.cfg
mysql-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]# ls
mysqlrouter.conf修改mysqlrouter的配置文件
[root@mysqlrouter mysqlrouter]# vim mysqlrouter.conf
[root@mysqlrouter mysqlrouter]# cat mysqlrouter.conf
[DEFAULT]
logging_folder = /var/log/mysqlrouter
runtime_folder = /var/run/mysqlrouter
config_folder = /etc/mysqlrouter
[logger]
level = INFO
[keepalive]
interval = 60
[routing:read_write]
bind_address = 192.168.0.211
bind_port = 7001
mode = read-write
destinations = 192.168.0.171:3306
max_connections = 65535
max_connect_errors = 100
client_connect_timeout = 9
[routing:read_only]
bind_address = 192.168.0.211
bind_port = 7002
mode = read-only
destinations = 192.168.0.173:3306
max_connections = 65535
max_connect_errors = 100
client_connect_timeout = 9启动mysqlrouter的服务
[root@mysqlrouter mysqlrouter]# service mysqlrouter start
Redirecting to /bin/systemctl start mysqlrouter.service
[root@mysqlrouter mysqlrouter]# netstat -antplu
Active 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---->master
lele ----只读----7002----->slaveroot@(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#' -P7001
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 8
Server 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 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.
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.234
mysqlrouter-backup ：
192.168.0.212 ----master vip :192.168.0.234 backup vip :192.168.0.233
在两台机器上安装并配置mysqlrouter 和keepalivedmysqlrouter的安装及配置如上：
keepalived 的安装及配置如下：
keepalived 的安装
[root@mysqlrouter-master mysqlrouter]# yum install keepalived -y
已加载插件：fastestmirror
Loading 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
已加载插件：fastestmirror
Loading 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 keepalived
bal_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 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 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
}
}[root@mysqlrouter-master 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:2b:30:73 brd ff:ff:ff:ff:ff:ff
inet 192.168.0.211/24 brd 192.168.0.255 scope global noprefixroute ens33
valid_lft forever preferred_lft forever
inet 192.168.0.233/32 scope global ens33
valid_lft forever preferred_lft forever
inet 192.168.0.234/32 scope global ens33
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fe2b:3073/64 scope link
valid_lft forever preferred_lft forever[root@mysqlrouter-backup ~]# 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:05:d6:86 brd ff:ff:ff:ff:ff:ff
inet 192.168.0.212/24 brd 192.168.0.255 scope global noprefixroute ens33
valid_lft forever preferred_lft forever
inet 192.168.0.234/32 scope global ens33
valid_lft forever preferred_lft forever
inet 192.168.0.233/32 scope global ens33
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fe05:d686/64 scope link
valid_lft forever preferred_lft forever设置keepalived开机启动
[root@mysqlrouter-master ~]# systemctl enable keepalived
Created symlink from /etc/systemd/system/multi-user.target.wants/keepalived.service to /usr/lib/systemd/system/keepalived.service.
[root@mysqlrouter-master ~]# systemctl list-unit-files|grep keepalived
keepalived.service enabled[root@mysqlrouter-backup ~]# systemctl enable keepalived
Created symlink from /etc/systemd/system/multi-user.target.wants/keepalived.service to /usr/lib/systemd/system/keepalived.service.
[root@mysqlrouter-backup ~]# systemctl list-unit-files|grep keepalived
keepalived.service测试访问：
[root@delay ~]# mysql -h 192.168.0.234 -uwangwang -p'Sanchuang123#' -P7001
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 13
Server 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 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.
mysql>[root@delay ~]# mysql -h 192.168.0.233 -uwangwang -p'Sanchuang123#' -P7001
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 14
Server 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 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.
mysql>未完待续。。。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
