實驗環境:

mysql-proxy服務端： xuegod1 IP：192.168.10.31

mysql伺服器（主，負責寫）服務端：xuegod2 IP：192.168.10.32

mysql伺服器（從，負責讀）用戶端：xuegod3 IP：192.168.10.33

1 部署MYSQL-PROXY服務端xuegod1

1.1 安裝mysql-proxy

安裝前需要系統支援LUA語言環境:

[root@xuegod1

~]# yum install lua

安裝mysql-proxy:

推薦采用已經編譯好的二進制版本，因為采用源碼包進行編譯時，最新版的MySQL-Proxy對automake，glib以及libevent的版本都有很高的要求，而這些軟體包都是系統的基礎套件，不建議強行進行更新。

并且這些已經編譯好的二進制版本在解壓後都在統一的目錄内，是以建議選擇以下版本：

~]# wget

http://dev.mysql.com/get/Downloads/MySQL-Proxy/mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz

~]# tar -xf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local/

local]# mv mysql-proxy-0.8.5-linux-el6-x86-64bit/ mysql-proxy

1.2

修改系統環境變量

[root@xuegod1 ~]# vim /etc/profile

export

PATH=/usr/local/mysql-proxy/bin/:/usr/local/mysql/bin:$PATH

[root@xuegod1 ~]# source !$

source /etc/profile

mysql-proxy 腳本配置檔案位置

[root@xuegod1 ~]# mv mysql-proxy

/usr/local/

[root@xuegod1 ~]# ls

/usr/local/mysql-proxy/share/doc/mysql-proxy/

1.3 修改配置檔案實作讀寫分離

[root@xuegod1 ~]# vim

/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua

将

min_idle_connections = 4,

max_idle_connections = 8,

修改為：

2 建立資料庫和表，用于實作讀操作

xuegod2和xuegod3安裝mysql-community-server

5.6

1.1 在xuegod2上建立資料庫和表

[root@xuegod2 ~]# mysql -uroot -p123456

mysql> create database db;

Query OK, 1 row affected (0.02 sec)

mysql> use db;

Database changed

mysql> create3 table test(id int);

ERROR 1064 (42000): You have an error in

your SQL syntax; check the manual that corresponds to your MySQL server version

for the right syntax to use near 'create3 table test(id int)' at line 1

mysql> create table test(id int);

Query OK, 0 rows affected (0.11 sec)

mysql> insert into test values(64);

mysql> grant all on db.* to user1@'%'

identified by "123456";

Query OK, 0 rows affected (0.00 sec)

1.2 在xuegod3上建立資料庫和表

Query OK, 1 row affected (0.01 sec)

Query OK, 0 rows affected (0.04 sec)

mysql> insert into test values(65);

Query OK, 1 row affected (0.03 sec)

啟動服務MYSQL-PROXY服務

[root@xuegod1 ~]# mysql-proxy

--proxy-read-only-backend-addresses=192.168.10.33:3306

--proxy-backend-addresses=192.168.10.32:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua

[1] 1987

[root@xuegod1 ~]# 2017-05-10 07:02:13:

(critical) plugin proxy 0.8.5 started

3 啟動服務MYSQL-PROXY服務

[root@xuegod1 ~]# mysql-proxy

--proxy-backend-addresses=192.168.10.32:3306

--proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua

[1] 1603

[root@xuegod1 ~]# 2017-05-10 20:11:42:

檢視proxy是否啟動：

[root@xuegod1 ~]# lsof -i :4040

COMMAND

PID USER FD TYPE DEVICE SIZE/OFF NODE NAME

mysql-pro 1603 root 9u

IPv4 12023 0t0

TCP *:yo-main (LISTEN)

4 測試讀寫分離

4.1 測試讀操作

~]# mysql -uuser1 -p123456 -P4040 -h 192.168.10.31

mysql> use

db;

查詢

mysql> select * from test;

+------+

| id

64 |

3 rows in set (0.00 sec)

再開一個用戶端視窗登入mysql查詢

65 |

1 row in set (0.01 sec)

兩個視窗都查到xuegod3上的資料

##看不到xuegod3的資料，可以看到剛寫入的資料，說明寫操作成功。因為是第一個客端連接配接，還沒有啟動讀寫分離，是以select讀時，沒有看到xuegod3的資料，而是看到主上xuegod2的資料。

接下來，多打開幾個用戶端測試一下讀。注：第一個連結，一定是走backend 主mysql伺服器的。

4.2 測試寫操作

插入一條資料66

mysql>

insert into test values(66);

Query OK, 1

row affected (0.00 sec)

查到的是xuegod2上的65

select * from test;

| id |

| 65 |

1 row in set

(0.00 sec)

這說明讀寫分離測試成功。但是資料還沒有保持同步。

保持資料同步，可以通過mysql主從來實作。

4.3 檢視用戶端連接配接狀态

mysql> show

processlist;

+----+-------+------------------+------+---------+------+-------+------------------+

| Id |

| 1073 | | NULL |

| 1162 | | NULL |

| 0 | init | show processlist |

| 332 | | NULL |

4 rows in set

5 部署mysql主從并實作讀寫分離

在之前的基礎上配置主從

5.1 配置XUEGOD2為MASTER

5.1.1 修改配置檔案

在配置檔案中添加如下幾行：

[root@xuegod2

~]# vim /etc/my.cnf

log-bin=mysql-bin-master

#啟用二進制日志

server-id=1 #本機資料庫ID 标示

binlog-do-db=db

#可以被從伺服器複制的庫。二進制需要同步的資料庫名

binlog-ignore-db=mysql

#不可以被從伺服器複制的庫

5.1.2 授權

~]# mysql -uroot -p123456;

grant replication slave on *.* to [email protected] identified by "123456";

5.1.3 删除test表，重新開機mysql服務

Reading table information for completion of

table and column names

You can turn off this feature to get a

quicker startup with -A

mysql> drop table test;

mysql> exit

Bye

[root@xuegod2 ~]# service mysqld restart

Stopping mysqld:

[ OK ]

Starting mysqld:

5.2 配置XUEGOD3為SLAVE

5.2.1 修改配置檔案

在配置檔案中添加如下配置：

[root@xuegod3 ~]# vim /etc/my.cnf

server-id=2

mysql 5.1版本需添加如下幾行配置：

master-host=192.168.1.63 #指定主伺服器IP位址

master-user=slave #指定定在主伺服器上可以進行同步的使用者名

master-password=123456 #密碼

重新開機服務：

[root@xuegod3 ~]# service mysqld restart

5.2.2 修改slave的主

mysql>stop

slave;

mysql> change master to

master_host='192.168.10.32',master_user='slave',master_password='123456';

Query OK, 0 rows affected, 2 warnings (0.04

sec)

5.2.3 删除test表，啟動slave

Query OK, 0 rows affected (0.03 sec)

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G

*************************** 1. row

***************************

Slave_IO_State: Waiting for

master to send event

Master_Host: 192.168.10.32

Master_User: slave

Master_Port: 3306

Connect_Retry: 60

Master_Log_File:

mysql-bin-master.000003

Read_Master_Log_Pos: 120

Relay_Log_File:

mysqld-relay-bin.000002

Relay_Log_Pos: 290

Relay_Master_Log_File: mysql-bin-master.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 120

Relay_Log_Space: 464

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_UUID:

b9f08209-350d-11e7-b224-000c2978dedd

Master_Info_File: /var/lib/mysql/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the

slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

1 row in set (0.00 sec)

6 主從同步測試

6.1 mysql主資料庫xuegod2插入資料

mysql> create table admin(id int);

mysql> insert into admin values(2);

6.2 mysql主從資料庫xuegod2/xuegod3上檢視同步的資料

主伺服器：

mysql> select * from admin;

2 |

從伺服器：

6.3 測試

同步後，測試使用MYSQL-PROXY 能否讀到同樣的資料。

在其他測試機上測試：

[root@xuegod1 ~]# mysql -uuser1 -p123456

-P4040 -h 192.168.10.31 -e 'select * from db.admin;'

Warning: Using a password on the command

line interface can be insecure.

停掉從伺服器，stop slave 測試插入資料看看讀寫分離

#可以檢視到admin中的63記錄，說明mysql+proxy+主從讀寫分離成功。

插入資料測試：

mysql> stop slave;

mysql> insert into admin values(3);

3 |

2 rows in set (0.00 sec)

在xuegod1和xuegod2上檢視都有

當我們SLAVE挂掉後咋樣呢

設立mysqlproxy+主主，檢視一個宕掉的情況

模拟故障：從伺服器挂掉了

[root@xuegod3

~]# service mysqld stop

在mysql-proxy上測試讀寫

mysql> insert into admin values(4);

4 |

xuegod2上檢視連接配接狀态，确認關閉slave後，讀寫都是通路xuegod2

總結：當停止掉 slave 資料庫，proxy 的查詢就會轉移到 master 上，當把 slave 啟動後，proxy 依然在讀 master，當有新的連結進來的時候才會重新去讀取

slave 的資料。有時可能需要重新開機下 mysql-proxy。

如果主資料庫挂了：

主從也沒了，在mysql-proxy上隻能檢視資料

在xuegod2上插入一條資料，在xuegod1,2,3上都可以查到

mysql> insert into admin values (6);

mysql> mysql> select * from admin;

5 |

6 |

5 rows in set (0.01 sec)

7 其他

停掉proxy

可以查到程序然後殺掉程序，也可以killall mysql-proxy

mysql-proxy配置檔案模闆（位址根據實際環境修改）

2-17-MySQL讀寫分離-mysql-proxy