實現MySQL讀寫分離 部署集群基礎環境(有圖)

實現MySQL讀寫分離
部署集群基礎環境

1 實現MySQL讀寫分離
1.1 問題

本案例要求配置2臺MySQL服務器+1臺代理服務器，實現MySQL代理的讀寫分離：

用戶只需要訪問MySQL代理服務器，而實際的SQL查詢、寫入操作交給后臺的2臺MySQL服務器來完成
其中Master服務器允許SQL查詢、寫入，Slave服務器只允許SQL查詢

1.2 方案

使用4臺RHEL 7.2虛擬機，如圖-1所示。其中192.168.4.10、192.168.4.20分別作為MySQL主、從服務器，是整個服務的后端；另一臺192.168.4.100作為MySQL代理服務器，是直接面向客戶的服務前端；客戶機192.168.4.120用作訪問測試。

圖－1

對比兩種方式的讀寫分離效果——

MySQL主從復制：客戶機訪問Master服務器來寫數據庫，客戶機訪問Slave服務器來讀數據庫。這種情況下，需要客戶端自行區分向何處寫、從何處讀。
MySQL主從復制+代理：客戶機訪問Proxy服務器，讀、寫請求交給Proxy識別，如果是寫數據庫操作則交給Master，如果是讀數據庫操作則交給Slave處理，具體由分配策略控制。這種情況下，無需客戶端區分讀、寫目標，而是由Proxy服務器代勞了，從而降低了客戶端程序的復雜度。

其中MySQL主、從復制結構的搭建參考前面的課程，這里不再贅述。
1.3 步驟

實現此案例需要按照如下步驟進行。

步驟一：部署mysql-proxy代理服務器

1）安裝mariadb官方提供的maxscale軟件包

[root@bogon ~]# rpm -ivh maxscale-2.1.2-1.rhel.7.x86_64.rpm 

修改配置文件：

[root@pxysvr pub]# [root@bogon ~]# grep -E -v '^#' /etc/maxscale.cnf
[maxscale]
threads=1
[server1]  #指定ip地址對應的名字
type=server
address=192.168.4.10  #主數據庫服務器ip地址
port=3306
protocol=MySQLBackend
[server2] #指定ip地址對應的名字
type=server
address=192.168.4.20  #從數據庫服務器ip地址
port=3306
protocol=MySQLBackend
[MySQL Monitor] #指定要監控的主機 和監控時連接的用戶
type=monitor
module=mysqlmon
servers=server1， server2 #前邊定義的主機名
user=scalemon    # 用戶名
passwd=111111    # 密碼
monitor_interval=10000
#[Read-Only Service]
#type=service
#router=readconnroute
#servers=server1
#user=myuser
#passwd=mypwd
#router_options=slave
[Read-Write Service] #定義服務器列表
type=service
router=readwritesplit
servers=server1， server2 #前邊定義的主機名
user=maxscale    # 用戶名
passwd=111111   # 密碼
max_slave_connections=100%
[MaxAdmin Service]
type=service
router=cli
#[Read-Only Listener]
#type=listener
#service=Read-Only Service
#protocol=MySQLClient
#port=4008
[Read-Write Listener]
type=listener
service=Read-Write Service
protocol=MySQLClient
port=4006
[MaxAdmin Listener]
type=listener
service=MaxAdmin Service
protocol=maxscaled
socket=default
[root@bogon ~]# 

分別在主、從數據庫服務器上添加授權用戶（只在主服務器授權即可 從服務器會自動同步）：

[root@pxysvr pub]# mysql> grant replication slave, replication client on *.* to scalemon@'%'  identified by “111111”;  //創建監控用戶
mysql> grant select on mysql.* to maxscale@'%' identified by “111111”;  //創建路由用戶
mysql> grant all  on *.*  to  student@'%' identified by “111111”;
//創建客戶端訪問用戶    

2）啟動maxscale服務

[root@bogon ~]# maxscale --config=/etc/maxscale.cnf
[root@bogon ~]# netstat -utnalp | grep maxscale
tcp        0      0 192.168.4.100:58960     192.168.4.10:3306      ESTABLISHED 19081/maxscale      
tcp        0      0 192.168.4.100:43508     192.168.4.20:3306      ESTABLISHED 19081/maxscale      
tcp6       0      0 :::4006                 :::*                    LISTEN      19081/maxscale      
[root@bogon ~]# kill -9 19081        //通過殺進程的方式停止服務

步驟二：測試配置

1）在客戶端192.168.4.120上使用上邊授權用戶student 連接代理服務器192.168.4.100:

[root@bogon ~]# mysql -h292.168.4.100 -P4006 -ustudent  -p111111
MySQL [(none)]> select  @@hostname; //顯示當前訪問的主機
+----------------+
| @@hostname      |
+----------------+
| slave20         |   //顯示的是從服務器的主機名
+----------------+
Query OK, 0 rows affected (0.00 sec)
MySQL [(none)]> insert  into bbsdb.a values(111);//插入新紀錄

客戶端當前訪問的是從數據庫服務器，仍然能夠插入紀錄。表示成功。
2 部署集群基礎環境
2.1 問題

本案例要求為MySQL集群準備基礎環境，完成以下任務操作：

數據庫授權
部署MySQL雙主多從結構
配置本機hosts解析記錄

2.2 方案

使用4臺RHEL 6虛擬機，如圖-1所示。其中192.168.4.10、192.168.4.11作為MySQL雙主服務器，192.168.4.12、192.168.4.13作為主服務器的從服務器。

圖－1
2.3 步驟

實現此案例需要按照如下步驟進行。

步驟一：準備環境

[root@master1 ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.4.10     master1         master1.tarena.com
192.168.4.11     master2         master2.tarena.com
192.168.4.12     slave1            slave1.tarena.com
192.168.4.13     slave2            slave2.tarena.com
192.168.4.100   master1         master1.tarena.com
[root@master1 ~]# ping -c 2 master1
PING master1 (192.168.4.10) 56(84) bytes of data.
64 bytes from master1 (192.168.4.10): icmp_seq=1 ttl=64 time=0.378 ms
64 bytes from master1 (192.168.4.10): icmp_seq=2 ttl=64 time=0.396 ms
--- master1 ping statistics ---
2 packets transmitted, 2 received, 0% packet loss, time 1001ms
rtt min/avg/max/mdev = 0.378/0.387/0.396/0.009 ms
[root@master1 ~]#

步驟二：部署數據庫主機

1）安裝啟動數據庫（4臺數據庫主機master1，master2，slave1，slave2執行以下操作）

[root@master1 ~]# tar xvf MySQL-5.6.15-1.el6.x86_64.rpm-bundle.tar    //解壓軟件包
.. ..
[root@master1 ~]# rpm -Uvh MySQL-*.rpm                                //安裝MySQL
.. ..
[root@master1 ~]# service mysql start
Starting MySQL.                                            [確定]

2）初始化配置數據庫（4臺數據庫主機master1，master2，slave1，slave2執行以下操作）

[root@master1 ~]# cat /root/.mysql_secret         //查看隨機生成密碼
# The random password set for the root user at Thu May  7 22:15:47 2015 (local time): wW1BNAjD
[root@master1 ~]# mysql -uroot -pwW1BNAjD        //使用隨機生成密碼登陸
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 1
Server version: 5.6.15
Copyright (c) 2000, 2013, 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> set password=password("pwd123");          //修改數據庫root密碼
Query OK, 0 rows affected (0.49 sec)
mysql> exit
Bye
[root@master1 ~]#

步驟三：部署雙主多從結構

1）數據庫授權（4臺數據庫主機master1，master2，slave1，slave2執行以下操作）

部署主從同步只需要授權一個主從同步用戶即可，但是我們要部署MySQL-MMM架構，所以在這里我們將MySQL-MMM所需用戶一并進行授權設置。再授權一個測試用戶，在架構搭建完成時測試使用。

[root@master1 ~]# mysql -uroot -ppwd123
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 2
Server version: 5.6.15 MySQL Community Server (GPL)
Copyright (c) 2000, 2013, 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> grant   replication  slave  on  *.*  to  slaveuser@"%" identified by  "pwd123";                                //主從同步授權
Query OK, 0 rows affected (0.00 sec)
mysql> grant  replication  client  on *.*  to  monitor@"%" identified by "monitor";                                         //MMM所需架構用戶授權
Query OK, 0 rows affected (0.06 sec)        
mysql> grant  replication client,process,super   on *.*  to  agent@"%" identified by "agent";                                 //MMM所需架構用戶授權
Query OK, 0 rows affected (0.00 sec)
mysql> grant  all  on *.*  to  root@"%" identified by "pwd123";  //測試用戶授權
Query OK, 0 rows affected (0.00 sec)
mysql>

2）開啟主數據庫binlog日志、設置server_id（master1，master2）

master1設置：

[root@master1 ~]# cat /etc/my.cnf 
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
server_id=10                        //設置server_id,該值集群中不可以重復
log-bin                            //開啟bin-log日志
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@master1 ~]# service mysql restart                //重啟MySQL服務
Shutting down MySQL..                                      [確定]
Starting MySQL..                                           [確定]
[root@master1 ~]# ls /var/lib/mysql/master1-bin*        //查看binlog日志是否生成
/var/lib/mysql/master1-bin.000001  /var/lib/mysql/master1-bin.index
[root@master1 ~]#

master2設置：

[root@master2 ~]# cat /etc/my.cnf 
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
server_id=11
log-bin
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@master2 ~]# service mysql restart
Shutting down MySQL..                                      [確定]
Starting MySQL.                                            [確定]
[root@master2 ~]# ls /var/lib/mysql/master2-bin.*
/var/lib/mysql/master2-bin.000001  /var/lib/mysql/master2-bin.index

3）從庫設置server_id

slave1設置：

[root@slave1 ~]# cat /etc/my.cnf 
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
server_id=12
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@slave1 ~]# service mysql restart
Shutting down MySQL..                                      [確定]
Starting MySQL..                                           [確定]
[root@slave1 ~]#

slave2設置：

[root@slave2 ~]# cat /etc/my.cnf 
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
server_id=13
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@slave2 ~]# service mysql restart
Shutting down MySQL..                                      [確定]
Starting MySQL.                                            [確定]
[root@slave2 ~]#

4）配置主從從從關系

配置master2、slave1、slave2成為master1的從服務器

查看master1服務器binlong日志使用節點信息：

[root@master1 ~]# mysql -uroot -ppwd123
.. ..
mysql> show master status\G
*************************** 1. row ***************************
             File: master1-bin.000001
         Position: 120
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)
mysql>

設置master2為master1從：

[root@master2 ~]# mysql -uroot -ppwd123
.. ..
mysql> change  master  to                         //設置主服務器信息
    -> master_host="192.168.4.10",                //設置主服務器IP地址
    -> master_user="slaveuser",                //設置主從同步用戶
    -> master_password="pwd123",                //設置主從同步密碼
    -> master_log_file="master1-bin.000001",    //設置主庫binlog日志名稱
    -> master_log_pos=120;                        //設置主從binlog日志使用節點
Query OK, 0 rows affected, 2 warnings (0.06 sec)
mysql> start slave;                            //啟動同步進程
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G                        //查看主從是否成功
.. ..

啟動同步進程后查看IO節點和SQL節點是否為Yes如果均為Yes表示主從正常。

             Slave_IO_Running: Yes                //IO節點正常
            Slave_SQL_Running: Yes                //SQL節點正常
.. ..
mysql>

設置slave1為master1從：

[root@slave1 ~]# mysql -uroot -ppwd123
.. ..
mysql> change  master  to 
    -> master_host="192.168.4.10",
    -> master_user="slaveuser",
    -> master_password="pwd123",
    -> master_log_file="master1-bin.000001",                    
    -> master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.12 sec)
mysql> start slave;
Query OK, 0 rows affected (0.16 sec)
mysql> show slave status\G
.. ..
             Slave_IO_Running: Yes                //IO節點正常
            Slave_SQL_Running: Yes                //SQL節點正常
.. ..
mysql>

設置slave2為master1從：

[root@slave2 ~]# mysql -uroot -ppwd123
.. ..
mysql> change  master  to 
    -> master_host="192.168.4.10",
    -> master_user="slaveuser",
    -> master_password="pwd123",
    -> master_log_file="master1-bin.000001",                    
    -> master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.13 sec)
mysql> start slave;
Query OK, 0 rows affected (0.27 sec)
mysql> show slave status\G
.. ..
             Slave_IO_Running: Yes                //IO節點正常
            Slave_SQL_Running: Yes                //SQL節點正常
.. ..
mysql>

5）配置主主從從關系，將master1配置為master2的從

查看master2的binlog使用信息：

[root@master2 ~]# mysql -uroot -ppwd123
.. ..
mysql> show master status\G
*************************** 1. row ***************************
             File: master2-bin.000001
         Position: 120
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)
mysql>

設置master1成為master2的從：

[root@master1 ~]# mysql -uroot -ppwd123
.. ..
mysql> change  master  to 
    -> master_host="192.168.4.11",
    -> master_user="slaveuser",
    -> master_password="pwd123",
    -> master_log_file="master2-bin.000001",                    
    -> master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.31 sec)
mysql> start slave;
Query OK, 0 rows affected (0.27 sec)
mysql> show slave status\G
.. ..
             Slave_IO_Running: Yes                //IO節點正常
            Slave_SQL_Running: Yes                //SQL節點正常
.. ..
mysql>

6）測試主從架構是否成功

master1更新數據，查看其它主機是否同步：

[root@master1 ~]# mysql -uroot -ppwd123
.. ..
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)
mysql> create database tarena;
Query OK, 1 row affected (0.06 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| tarena             |
| test               |
+--------------------+
5 rows in set (0.00 sec)
mysql>

master2主機查看：

[root@master2 ~]# mysql -uroot -ppwd123 -e "show databases"
Warning: Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| tarena             |
| test               |
+--------------------+
[root@master2 ~]#

slave1主機查看：

[root@slave1 ~]# mysql -uroot -ppwd123 -e "show databases"
Warning: Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| tarena             |
| test               |
+--------------------+
[root@slave1 ~]#

slave2主機查看：

[root@slave2 ~]# mysql -uroot -ppwd123 -e "show databases"
Warning: Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| tarena             |
| test               |
+--------------------+
[root@slave2 ~]#