mysql学习笔记-----读写分离+高可用

最新推荐文章于 2024-07-30 19:26:47 发布

兴走耳又

最新推荐文章于 2024-07-30 19:26:47 发布

阅读量586

点赞数

文章标签：数据库 mysql

本文链接：https://blog.csdn.net/qq_44936336/article/details/109289253

版权

什么叫读写分离

中间件：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----->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#' -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 和keepalived

mysqlrouter的安装及配置如上：
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>

未完待续。。。

兴走耳又

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql学习笔记-----读写分离+高可用

什么叫读写分离中间件：middleware客户机访问web服务器----> web服务器通过中间件来访问后面的db，如果是要读取数据库里的内容，中间件会任选一台db进行读取，如果是要往数据库里写内容，则只往主库里写，从而实现读写分离。读与写会访问mysqlrouter不同的端口中间件的种类：MySQLrouter ----mysql官方提供的mycat — 开源，中国，数据库分库分表的中间件什么是分库分表分库分表就是为了解决由于数据量过大，而导致的数据库的性能下降的问题，将原来
复制链接

扫一扫