Mysql-cluster小项目

你的名字 2022-05-16 20:19 184阅读 0赞

*  **目标**
 *  利用`Mysql-proxy`实现对于前端请求的读写分离,利用`LVS`实现对于后端读请求的负载均衡,后端的读写服务器之间实现主从复制功能;
 *  **规划**
    
    > 实验环境:rhel6.5,内核版本:2.6.32-431.el6.x86\_64;  
    > 读写分离主机:172.25.23.82 主机名:mysql-proxy  
    > 写服务器+主从架构的主服务器:172.25.23.78/24 主机名:mysql-master  
    > LVS所在主机:172.25.23.79/24 主机名:lvs-server  
    > LVS后端主机1: sql-slave-reallvs 主机名:sql-slave-reallvs1  
    > LVS后端主机2:172.25.23.81 主机名:sql-slave2-realvs2  
    > 读写分离采用的软件为mysql-proxy;
 *  **步骤简介**
 *  首先为`mysql-master`配置磁盘阵列,这里采用软件`raid 5`并且提供一块冗余磁盘;
 *  通过软件包安装`mysql-5.7.11`并且采用`Innodb`存储引擎,启用每表一个表空间文件;
 *  `mysql-master`的配置文件,配置`master`端的复制用户;
 *  `sql-slave-realvs1`安装软件,并且将数据文件单独保存在一块磁盘上面;
 *  `sql-slave-realvs2`安装软件,并且将数据文件单独保存在一块磁盘上面;
 *  `slave`在实验环境下,不再使用`raid`阵列,仅仅使用单独的分区;
 *  配置两个`slave`向`master`同步数据;
 *  至此主从架构配置完成;
 *  接下来配置`sql-slave-realvs`的负载均衡;
 *  采用`LVS-DR`模型配置`sql-slave-realvs`
 *  这里希望通过自己改写的`LVS`后端服务检查的脚本来实现对于`Mysql`后端服务健康状况的检查,并且能够实现自动移除故障节点,并且在服务正常后,自动的添加正常节点;
 *  接下来配置前端的`mysql-proxy`,采用`rpm`安装软件,通过配置实现读写分离,将写请求调度到`Mysql-master`,将读请求调度到`lvs-server`,并且最终实现调度到`sql-slave-realvs`上面;
 *  **正式的配置过程开始** 
 *  首先添加磁盘制作`raid 5`阵列
 *  为`mysql-master`添加一块`40G`的硬盘  
    ![这里写图片描述][70]
 *  接下来创建四个分区

[root@mysql-master ~]# fdisk /dev/vdb 
    Command (m for help): n
    Command action
       e   extended
       p   primary partition (1-4)
    p
    Partition number (1-4): 1
    First cylinder (1-83220, default 1): 
    Using default value 1
    Last cylinder, +cylinders or +size{K,M,G} (1-83220, default 83220): +10G
    
    Command (m for help): p
    
    Disk /dev/vdb: 42.9 GB, 42949672960 bytes
    16 heads, 63 sectors/track, 83220 cylinders
    Units = cylinders of 1008 * 512 = 516096 bytes
    Sector size (logical/physical): 512 bytes / 512 bytes
    I/O size (minimum/optimal): 512 bytes / 512 bytes
    Disk identifier: 0xad29dba2
    
       Device Boot      Start         End      Blocks   Id  System
    /dev/vdb1               1       20806    10486192+  fd  Linux raid autodetect
    /dev/vdb2           20807       41612    10486224   fd  Linux raid autodetect
    /dev/vdb3           41613       62418    10486224   fd  Linux raid autodetect
    /dev/vdb4           62419       83220    10484208   fd  Linux raid autodetect
    
    [root@mysql-master ~]# partprobe /dev/vdb

*  创建上面四个分区并且调整类型为`fd`

[root@mysql-master ~]# mdadm -C /dev/md5 -l5 -n 3 -x 1 /dev/vdb1 /dev/vdb2 /dev/vdb3 /dev/vdb4 
    mdadm: Defaulting to version 1.2 metadata
    mdadm: array /dev/md5 started.

*  查看`raid`阵列的状态

Number   Major   Minor   RaidDevice State
           0     252       17        0      active sync   /dev/vdb1
           1     252       18        1      active sync   /dev/vdb2
           4     252       19        2      spare rebuilding   /dev/vdb3
    
           3     252       20        -      spare   /dev/vdb4

*  接下来进行格式化操作

[root@mysql-master ~]# mke2fs -j /dev/md5 
    mke2fs 1.41.12 (17-May-2010)
    Filesystem label=
    OS type: Linux
    Block size=4096 (log=2)
    Fragment size=4096 (log=2)
    Stride=128 blocks, Stripe width=256 blocks
    1310720 inodes, 5237760 blocks
    261888 blocks (5.00%) reserved for the super user
    First data block=0
    Maximum filesystem blocks=4294967296
    160 block groups
    32768 blocks per group, 32768 fragments per group
    8192 inodes per group
    Superblock backups stored on blocks: 
        32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208, 
        4096000
    
    Writing inode tables: done                            
    Creating journal (32768 blocks): done

*  创建数据目录并且进行挂载

[root@mysql-master ~]# mkdir /mysql/data -pv
    mkdir: created directory `/mysql'
    mkdir: created directory `/mysql/data'
    [root@mysql-master ~]# mount /dev/md5 /mysql/data/

*  创建自动挂载

[root@mysql-master ~]# vim /etc/fstab 
    /dev/md5                /mydata/data            ext3    defaults        0 0

*  这里配置了`mysql-5.7-11`的`Yum`源,所以使用软件包可以直接安装;

mysql-community-client.x86_64 : MySQL database client applications and tools
    mysql-community-common.x86_64 : MySQL database common files for server and
                                  : client libs
    mysql-community-devel.x86_64 : Development header files and libraries for MySQL
                                 : database client applications
    mysql-community-embedded.x86_64 : MySQL embedded library
    mysql-community-embedded-devel.x86_64 : Development header files and libraries
                                          : for MySQL as an embeddable library
    mysql-community-libs.x86_64 : Shared libraries for MySQL database client
                                : applications
    mysql-community-libs-compat.x86_64 : Shared compat libraries for MySQL 5.1.72
                                       : database client applications
    mysql-community-server.x86_64 : A very fast and reliable SQL database server
    mysql-community-test.x86_64 : Test suite for the MySQL database server

*  修改配置文件,进行`Mysql`的初始化

[root@mysql-master ~]# mysql_install_db --datadir=/mysql/data 
    2018-08-10 07:42:20 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize
    2018-08-10 07:42:50 [WARNING] select() timed out.

*  修改配置文件如下,然后尝试启动`Mysql`

datadir=/mysql/data
    socket=/mysql/data/mysql.sock
    
    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0
    
    log-error=/mysql/data/mysqld.log
    pid-file=/mysql/data/mysqld.pid

*  还需要修改当前目录里面的文件的权限

[root@mysql-master mysql]# chown mysql.mysql ./data/ -R

*  尝试启动`Mysql`

[root@mysql-master mysql]# /etc/init.d/mysqld start
    Starting mysqld:                                           [  OK  ]

*  查看`3306`端口已经启动

tcp        0      0 0.0.0.0:22                  0.0.0.0:*                   LISTEN      1095/sshd           
    tcp        0      0 127.0.0.1:25                0.0.0.0:*                   LISTEN      1462/master         
    tcp        0      0 172.25.23.78:22             172.25.23.250:45360         ESTABLISHED 1593/sshd           
    tcp        0      0 :::22                       :::*                        LISTEN      1095/sshd           
    tcp        0      0 ::1:25                      :::*                        LISTEN      1462/master         
    tcp        0      0 :::3306                     :::*                        LISTEN      5202/mysqld

*  接下来执行`Mysql`安全安装脚本

[root@mysql-master ~]# mysql_secure_installation 
    
    Securing the MySQL server deployment.
    
    Connecting to MySQL server using password in '/root/.mysql_secret'
    
    VALIDATE PASSWORD PLUGIN can be used to test passwords
    and improve security. It checks the strength of password
    and allows the users to set only those passwords which are secure enough. Would you like to setup VALIDATE PASSWORD plugin? Press y|Y for Yes, any other key for No: y There are three levels of password validation policy: LOW Length >= 8 MEDIUM Length >= 8, numeric, mixed case, and special characters STRONG Length >= 8, numeric, mixed case, special characters and dictionary file Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0 Using existing password for root. Estimated strength of the password: 50 Change the password for root ? ((Press y|Y for Yes, any other key for No) : y New password: Re-enter new password: Estimated strength of the password: 50 Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y By default, a MySQL installation has an anonymous user, allowing anyone to log into MySQL without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment. Remove anonymous users? (Press y|Y for Yes, any other key for No) : y Success. Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y Success. By default, MySQL comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y - Dropping test database... Success. - Removing privileges on test database... Success. Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y Success. All done!

*  用于设置密码策略,并且修改密码
 *  利用修改后的密码登陆

[root@mysql-master ~]# mysql -uroot -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 10
    Server version: 5.7.11
    
    Copyright (c) 2000, 2016, 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 databases; +--------------------+
    | Database | +--------------------+
    | information_schema | | mysql | | performance_schema |
    | sys | +--------------------+
    4 rows in set (0.00 sec)

*  修改配置文件添加一些必要的参数

innodb_file_per_table = 1    //必须的参数
    server-id = 1                //必须的参数
    log-bin=master-bin           //master必须的参数
    binlog-format=ROW            //二进制日志的记录格式
    log-slave-updates=true       //建议的参数
    gtid-mode=ON                 //非常建议的参数
    enforce-gtid-consistency=true
    master-info-repository=TABLE
    relay-log-info-repository=TABLE
    sync-master-info=1
    slave-parallel-workers=2
    binlog-checksum=CRC32
    master-verify-checksum=1
    slave-sql-verify-checksum=1
    binlog-rows-query-log_events=1
    report-port=3306          //作用暂时未知
    report-host=172.25.23.78

*  启动`Mysql`服务器,并且创建允许有复制权限的用户

[root@mysql-master data]# /etc/init.d/mysqld restart 
    Stopping mysqld:                                           [  OK  ]
    Starting mysqld:                                           [  OK  ]

*  创建具有复制权限的用户,对于`mysql5.7`默认密码策略比较严格,也是为了安全;

grant replication slave on *.* to 'repluser'@'172.25.23.%' identified by 'Replpass121314..';
    Query OK, 0 rows affected, 1 warning (0.11 sec)

*  在本机使用尝试使用这个用户链接

[root@mysql-master data]# mysql -h172.25.23.78 -urepluser -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    //如果这个过程比较长,建议打开选项skip_name_resolve

*  作为`master-salve`的`master`端就已经配置完成了;
 *  接下来配置`slave`节点
 *  `sql-slave-realvs1` 首先安装软件包

[root@sql-slave-reallvs1 ~]# yum install mysql-community-client.x86_64 mysql-community-common.x86_64 mysql-community-devel.x86_64 mysql-community-embedded.x86_64 mysql-community-libs.x86_64 mysql-community-server.x86_64 -y

*  创建数据目录

[root@sql-slave-reallvs1 ~]# mkdir /mysql/data -pv
    mkdir: created directory `/mysql'
    mkdir: created directory `/mysql/data'

*  修改数据文件的位置

vim /etc/my.cnf datadir=/mysql/mydata

*  启动并且进行数据库的初始化

[root@sql-slave-reallvs1 ~]# /etc/init.d/mysqld start
    Initializing MySQL database:                               [  OK  ]
    Installing validate password plugin:                       [  OK  ]
    Starting mysqld:                                           [  OK  ]

*  获取临时密码  
    ![这里写图片描述][70 1]
 *  进行安全安装,并且修改密码

[root@sql-slave-reallvs1 ~]# mysql_secure_installation 
    
    Securing the MySQL server deployment.
    
    Enter password for user root: 
    
    The existing password for the user account root has expired. Please set a new password. New password: Re-enter new password: The 'validate_password' plugin is installed on the server. The subsequent steps will run with the existing configuration of the plugin. Using existing password for root. Estimated strength of the password: 100 Change the password for root ? ((Press y|Y for Yes, any other key for No) : y New password: Re-enter new password: Estimated strength of the password: 100 Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y By default, a MySQL installation has an anonymous user, allowing anyone to log into MySQL without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment. Remove anonymous users? (Press y|Y for Yes, any other key for No) : y Success. Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y Success. By default, MySQL comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y - Dropping test database... Success. - Removing privileges on test database... Success. Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y Success. All done!

*  尝试使用新密码登陆成功;
 *  **在`sql-slave-realvs2`上面进行同样的操作**

[root@sql-slave2-realvs2 ~]# yum install mysql-community-client.x86_64 mysql-community-common.x86_64 mysql-community-devel.x86_64 mysql-community-libs.x86_64 mysql-community-server.x86_64 -y

*  创建数据目录

[root@sql-slave2-realvs2 ~]# mkdir /mysql/data -pv
    mkdir: created directory `/mysql'
    mkdir: created directory `/mysql/data'

*  编辑配置文件,修改数据目录的位置

datadir=/mysql/data

*  首次安装数据库,会自动进行初始化

Initializing MySQL database:                               [ OK ]
    Installing validate password plugin:                       [ OK ]
    Starting mysqld:                                           [ OK ]

*  通常来说,临时密码在日志文件中  
    ![这里写图片描述][70 2]
 *  需要通过临时密码来修改密码

[root@sql-slave2-realvs2 ~]# mysql_secure_installation 
    
    Securing the MySQL server deployment.
    
    Enter password for user root: 
    
    The existing password for the user account root has expired. Please set a new password. New password: Re-enter new password: ... Failed! Error: Your password does not satisfy the current policy requirements New password: Re-enter new password: The 'validate_password' plugin is installed on the server. The subsequent steps will run with the existing configuration of the plugin. Using existing password for root. Estimated strength of the password: 100 Change the password for root ? ((Press y|Y for Yes, any other key for No) : y New password: Re-enter new password: Estimated strength of the password: 100 Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y By default, a MySQL installation has an anonymous user, allowing anyone to log into MySQL without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment. Remove anonymous users? (Press y|Y for Yes, any other key for No) : y Success. Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y Success. By default, MySQL comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y - Dropping test database... Success. - Removing privileges on test database... Success. Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y Success. All done!

*  如果不进行这一步,通过临时密码可以登陆系统,但是无法执行任何操作
 *  尝试使用新密码登陆成功;
 *  这里的主从服务架构,都是一主多从,并且是两个从向主节点进行数据复制
 *  配置的`sql-salve-realvs1`配置信息如下

innodb_file_per_table = 1
    server-id = 2
    log-bin=slave-bin
    
    binlog-format=ROW
    log-slave-updates=true
    gtid-mode=on
    enforce-gtid-consistency=true
    master-info-repository=TABLE
    sync-master-info=1
    slave-parallel-workers=2
    binlog-checksum=CRC32
    master-verify-checksum=1
    slave-sql-verify-checksum=1
    binlog-rows-query-log_events=1
    report-port=3306
    port=3306
    report-host=172.25.23.80

*  配置`sql-slave-realvs2`的配置信息如下

innodb_file_per_table = 1
    server-id = 3
    log-bin=slave-bin
    
    binlog-format=ROW
    log-slave-updates=true
    gtid-mode=on
    enforce-gtid-consistency=true
    master-info-repository=TABLE
    sync-master-info=1
    slave-parallel-workers=2
    binlog-checksum=CRC32
    master-verify-checksum=1
    slave-sql-verify-checksum=1
    binlog-rows-query-log_events=1
    report-port=3306
    port=3306
    report-host=172.25.23.81

*  在两个节点上面启动服务

[root@sql-slave-reallvs1 ~]# /etc/init.d/mysqld restart
    Stopping mysqld:                                           [  OK  ]
    Starting mysqld:                                           [  OK  ]

*  启动`sql-slave-realvs2`节点上面的服务

[root@sql-slave2-realvs2 ~]# /etc/init.d/mysqld restart
    Stopping mysqld:                                           [  OK  ]
    Starting mysqld:                                           [  OK  ]

*  在节点`sql-salve-realvs1`上面验证复制用户能够登陆

![这里写图片描述][70 3]

![这里写图片描述][70 4]  
\* 连接`sql-slave-realvs1`上面,配置并且启动从节点  
![这里写图片描述][70 5]  
\* 查看从节点状态

![这里写图片描述][70 6]  
\* 然后启动从节点,再次查看状态  
![这里写图片描述][70 7]  
\* 配置`sql-slave-realvs2`节点  
![这里写图片描述][70 8]  
\* 尝试在主节点上面创建数据库  
![这里写图片描述][70 9]  
\* `sql-slave-reallvs1`上面已经创建,并且事务号码已经更改  
![这里写图片描述][70 10]  
\* `sql-slave-reallvs2`上面也已经创建,并且事务执行号码也已经更改  
![这里写图片描述][70 11]  
![这里写图片描述][70 12]

*  手动施加读锁,虽然这里还没有表;  
    ![这里写图片描述][70 13]
 *  在`sql-salve-reallcs2`上面施加读锁  
    ![这里写图片描述][70 14]
 *  主从架构的配置已经完成,接下来去配置两个从节点的负载均衡;
 *  首先配置`mysql-lvs`节点 的`VIP`地址

[root@mysql-lvs ~]# ip addr add 172.25.23.100/24 dev eth0:0

*  查看配置成功的地址  
    ![这里写图片描述][70 15]
 *  配置后端节点的`RIP`地址,`RIP`有三块网卡,其中一块作为主从复制,所以不进行修改,这里只修改`lo`以及`eth1`网卡的配置信息
 *  对于`sql-slave-reallvs1`的配合i过程:
 *  首先给`eth1`配置一个地址  
    ![这里写图片描述][70 16]
 *  重新启动网络服务,保证服务的配置是正常的

![这里写图片描述][70 17]  
\* 修改关于`arp`协议响应的配置信息

[root@sql-slave-reallvs1 ~]# sysctl -w net.ipv4.conf.eth1.arp_announce=2
    net.ipv4.conf.eth1.arp_announce = 2
    [root@sql-slave-reallvs1 ~]# sysctl -w net.ipv4.conf.lo.arp_announce=2
    net.ipv4.conf.lo.arp_announce = 2
    [root@sql-slave-reallvs1 ~]# echo 1 > /proc/sys/net/ipv4/conf/eth1/arp_ignore 
    [root@sql-slave-reallvs1 ~]# echo 1 > /proc/sys/net/ipv4/conf/lo/arp_ignore 
    
    [root@sql-slave-reallvs1 ~]# ifconfig lo:0 172.25.23.100 broadcast 172.25.23.100 netmask 255.255.255.255

*  配置完成之后的信息如下

![这里写图片描述][70 18]  
\* 接下来配置`sql-slave-reallvs2`上面的网卡信息  
![这里写图片描述][70 19]  
\* 网卡配置成功之后的信息  
![这里写图片描述][70 20]  
\* 配置`arp`响应的相关信息

[root@sql-slave2-realvs2 ~]# sysctl -w net.ipv4.conf.lo.arp_announce=2
    net.ipv4.conf.lo.arp_announce = 2
    [root@sql-slave2-realvs2 ~]# sysctl -w net.ipv4.conf.eth1.arp_announce=2
    net.ipv4.conf.eth1.arp_announce = 2
    [root@sql-slave2-realvs2 ~]# echo 1 > /proc/sys/net/ipv4/conf/lo/arp_ignore
    [root@sql-slave2-realvs2 ~]# echo 1 > /proc/sys/net/ipv4/conf/eth1/arp_ignore
    [root@sql-slave2-realvs2 ~]# ifconfig lo:0 172.25.23.100 broadcast 172.25.23.100 netmask 255.255.255.255

*  查看配置的网卡信息  
    ![这里写图片描述][70 21]
 *  为两个`sql-slave-reallvs`添加一条特殊的路由信息

[root@sql-slave-reallvs1 ~]# route add -host 172.25.23.100 dev lo:0
    [root@sql-slave2-realvs2 ~]# route add -host 172.25.23.100 dev lo:0

*  `sql-slave-reallvs`上面的`Mysql`服务已经配置,所以这里来指定调度规则;

[root@mysql-lvs ~]# ipvsadm -A -t 172.25.23.100:3306 -s wlc
    [root@mysql-lvs ~]# ipvsadm -a -t 172.25.23.100:3306 -r 172.25.23.1 -g -w 2
    [root@mysql-lvs ~]# ipvsadm -a -t 172.25.23.100:3306 -r 172.25.23.2 -g -w 1

*  查看已经创建规则的状态,这里会显示活跃的连接;  
    ![这里写图片描述][70 22]
 *  在`mysql-master`上面创建一个可以用于远程登陆连接的用户,这里使用这个用户来再次验证
 *  这里是连接上去,显示的信息  
    ![这里写图片描述][70 23]
 *  接下来是`lvs`调度显示的信息  
    ![这里写图片描述][70 24]
 *  可以观察到的是`LVS`已经可以正常工作了,由于`Mysql`对于后端的服务没有进行后端服务的检查,所以这里来提供一个脚本,希望能够检测`Mysql`后台服务器的状况,并且根据状况作出相应的策略;
 *  能够进行后端服健康状况检查的脚本如下

[root@mysql-lvs /]# cat heath_check1.sh 
    #!/bin/bash
    VIP=172.25.23.100
    CPORT=3306
    RIP=("172.25.23.1" "172.25.23.2")
    RW=("1" "2")
    RSTATUS=("1" "1")
    FALL_BACK=127.0.0.1
    RPORT=3306
    TYPE=wlc
    add() {
      ipvsadm -a -t $VIP:$CPORT -r $1:$RPORT -g -w $2
      echo "add $I successful"
      [ $? -eq 0 ] && return 0 || return 1
    }
    
    del() {
      ipvsadm -d -t $VIP:$CPORT -r $1:$RPORT 
      echo "del $I successful"
      [ $? -eq 0 ] && return 0 || return 1
    }
    
    while :; do
    let COUNT=0
    for I in ${RIP[*]}; do
      result=`mysqladmin ping -h$I -utestuser -pLixun121314.. 2> /dev/null | awk '{print $3}'`
      if [ $result == "alive" ]; then
         if [ ${RSTATUS[$COUNT]} -eq 0 ]; then
            echo "add start"
        add $I ${RW[$COUNT]} 
        [ $? -eq 0 ] && RSTATUS[$COUNT]=1
         fi
      else
         if [ ${RSTATUS[$COUNT]} -eq 1 ]; then
            echo "del start"
        del $I
        [ $? -eq 0 ] && RSTATUS[$COUNT]=0
         fi
        let COUNT++
      fi
      done
      sleep 5
    done

*  这个脚本能够检查`172.25.23.1`以及`172.25.23.2`的服务的健康状况,并且能够动态的添加,删除后台的服务;
 *  到这里`LVS`负载两台`sql-slave-reallvs`的任务就已经完成,接下来完成读写分离;
 *  读写分离使用的软件是`mysql-proxy-0.8.3`,并且使用的是编译汉的二进制源码软件包

[root@localhost mnt]# tar -xf mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit.tar.gz -C /usr/local/mysql-proxy/

*  然后指定读服务器和写服务器,启动`Mysql-proxy`

./mysql-proxy --daemon --log-level=debug --plugins="proxy" --log-file=/var/log/mysql-proxy.log --proxy-backend-addresses="172.25.23.78:3306" --proxy-read-only-backend-addresses="172.25.23.100:3306" --proxy-lua-script=../mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit/share/doc/mysql-proxy/rw-splitting.lua //这个脚本一定需要进行指定,否则无法完成读写分离功能;

*  查看`4040`端口是否打开  
    ![这里写图片描述][70 25]
 *  在真机使用测试用户进行登陆

[root@lvs-server Desktop]# mysql -h172.25.23.82 -utestuser -p --port=4040
    Enter password: 
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MySQL connection id is 28
    Server version: 5.7.11-log MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

*  执行一个创建表的操作

MySQL [hello]> create table teacher(id int, name char);
    Query OK, 0 rows affected (1.66 sec)

*  因为构建了主从服务架构,但是主服务器不会向从服务器同步数据,所以查看扎格表是否在主服务器上面创建;

![这里写图片描述][70 26]  
\* 可以验证的是写操作在`mysql-master`上面执行,查看从服务器是否同步到数据  
![这里写图片描述][70 27]

![这里写图片描述][70 28]  
\* 测试读写分离,因为只有`mysql-master`可以进行写入操作,并且`sql-slave-reallvs`只能够向`mysql-master`进行数据同步,所以只需要检查两个`sql-slave-reallvs`上面是否存在数据更新,就可以,上面已经进行了这些操作;  
\* 接下来提供验证`lvs`调度的查询操作的方法,通过观察  
![这里写图片描述][70 29]  
\* 当进行查询操作时,这些节点的数据是会存在变化的  
![这里写图片描述][70 30]  
\* 数据发生了改变  
![这里写图片描述][70 31]  
\* 根据实验的规划,功能都已经完成了;

[70]: /images/20220517/28d096dd4921494bb315ca16aa081255.png
[70 1]: /images/20220517/9156f9e6c473427a91893b3a257d1cf7.png
[70 2]: /images/20220517/3f324b194eab49bba11adabf4c46f12b.png
[70 3]: /images/20220517/0655017cf2ce4186aea4dc06e7995fd2.png
[70 4]: /images/20220517/39d05f2197a247768443f9262e2eb88b.png
[70 5]: /images/20220517/d6205e99f5094c7ea8618beaf9832254.png
[70 6]: /images/20220517/02efd9d7db134aec9bef720090e4e215.png
[70 7]: /images/20220517/4145d315358d4553a4f56a4a9dc13705.png
[70 8]: /images/20220517/c0080d205e6c45a19726202c1af7df6f.png
[70 9]: /images/20220517/7427645589ac41ca92d43723b9e0ddff.png
[70 10]: /images/20220517/649088c6d0e74fd0a34499805c9c6fe3.png
[70 11]: /images/20220517/ab2587dbe6c64010b68d96758cbd7d6f.png
[70 12]: /images/20220517/55db980d22d14d9c9ab88fd4d1ab4669.png
[70 13]: /images/20220517/5b971ecf8e534f9e9d60654ed4224fc4.png
[70 14]: /images/20220517/0991603d3b73422c8c9214faa2ea5a0d.png
[70 15]: /images/20220517/36fb47fd4e1849c7ab4312c82a309f45.png
[70 16]: /images/20220517/a8cfd424433e41f59f52c85e70795937.png
[70 17]: /images/20220517/7ea5c3725b63412f86fb8863eecbe7a1.png
[70 18]: /images/20220517/f2221bd40a554a808ba02eff05f40a94.png
[70 19]: /images/20220517/3c352d522ed44931bedda0d39e4e1ffe.png
[70 20]: /images/20220517/806a3013414440d7aa052325a8e56327.png
[70 21]: /images/20220517/4e5674fc492a46db994eb0a0cecaf084.png
[70 22]: /images/20220517/bff37fdeb3cc40728a499acece0db9b9.png
[70 23]: /images/20220517/fdca880dc93c4245a35be27aec9bc494.png
[70 24]: /images/20220517/5003722797534d0e9df99206130ac729.png
[70 25]: /images/20220517/37dd763d76f04cf68cefa289b162d486.png
[70 26]: /images/20220517/77267b4864e84b3e87a779c242bb2d64.png
[70 27]: /images/20220517/01ddb4ac07934ef98ba8c836172688c3.png
[70 28]: /images/20220517/bbf8f3c8ca1344ac9e6de96511e2ec9f.png
[70 29]: /images/20220517/28d64b43f5f54ed3ba343e623099df0e.png
[70 30]: /images/20220517/0533a9795c414b1e85dd1ff4edef00de.png
[70 31]: /images/20220517/8cf024dd4429447bb6720d1895ca4123.png