Mysql-cluster小專案

• 目標
• 利用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的硬碟
  
• 接下來建立四個分割槽

[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  ]

• 獲取臨時密碼
  
• 進行安全安裝,並且修改密碼

[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  ]

• 通常來說,臨時密碼在日誌檔案中
  
• 需要通過臨時密碼來修改密碼

[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上面驗證複製使用者能夠登陸

* 連線sql-slave-realvs1上面,配置並且啟動從節點

* 檢視從節點狀態

* 然後啟動從節點,再次檢視狀態

* 配置sql-slave-realvs2節點

* 嘗試在主節點上面建立資料庫

* sql-slave-reallvs1上面已經建立,並且事務號碼已經更改

* sql-slave-reallvs2上面也已經建立,並且事務執行號碼也已經更改

• 手動施加讀鎖,雖然這裡還沒有表;
  

• 在sql-salve-reallcs2上面施加讀鎖
  

• 主從架構的配置已經完成,接下來去配置兩個從節點的負載均衡;

• 首先配置mysql-lvs節點 的VIP地址

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

• 檢視配置成功的地址
  
• 配置後端節點的RIP地址,RIP有三塊網路卡,其中一塊作為主從複製,所以不進行修改,這裡只修改lo以及eth1網路卡的配置資訊
• 對於sql-slave-reallvs1的配合i過程:
• 首先給eth1配置一個地址
  
• 重新啟動網路服務,保證服務的配置是正常的

* 修改關於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 

• 配置完成之後的資訊如下

* 接下來配置sql-slave-reallvs2上面的網路卡資訊

* 網路卡配置成功之後的資訊

* 配置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

• 檢視配置的網路卡資訊
  
• 為兩個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

• 檢視已經建立規則的狀態,這裡會顯示活躍的連線;
  
• 在mysql-master上面建立一個可以用於遠端登陸連線的使用者,這裡使用這個使用者來再次驗證
• 這裡是連線上去,顯示的資訊
  
• 接下來是lvs排程顯示的資訊
  
• 可以觀察到的是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埠是否開啟
  
• 在真機使用測試使用者進行登陸

[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)

• 因為構建了主從服務架構,但是主伺服器不會向從伺服器同步資料,所以檢視扎格表是否在主伺服器上面建立;

* 可以驗證的是寫操作在mysql-master上面執行,檢視從伺服器是否同步到資料

* 測試讀寫分離,因為只有mysql-master可以進行寫入操作,並且sql-slave-reallvs只能夠向mysql-master進行資料同步,所以只需要檢查兩個sql-slave-reallvs上面是否存在資料更新,就可以,上面已經進行了這些操作;
* 接下來提供驗證lvs排程的查詢操作的方法,通過觀察

* 當進行查詢操作時,這些節點的資料是會存在變化的

* 資料發生了改變

* 根據實驗的規劃,功能都已經完成了;