[mysql]linux mysql 讀寫分離

[mysql]linux mysql 讀寫分離

作者：flymaster

1、簡介

　　當今MySQL使用相當廣泛，随着使用者的增多以及資料量的增大，高并發随之而來。然而我們有很多辦法可以緩解資料庫的壓力。分布式資料庫、負載均衡、讀寫分離、增加緩存伺服器等等。這裡我們将采用讀寫分離技術進展緩解資料庫的壓力。

　　其中實作讀寫分離的技術有很多方法，這裡我們将采用mysql-proxy這個中間軟體來實作。這個軟體中含有一個讀寫分離的lua檔案，這也是我們使用mysql-proxy實作讀寫分離必用的檔案，它需要lua解析器進行解析。是以我們還需要安裝一個lua解析器。

回到頂部

2、基本環境

　　三台linux虛拟主機

　　Linux版本CentOS6.6、MySQL 5.5

　　mysql-proxy-0.8.5

　　lua-5.1.4

　　ip：192.168.95.11（寫）、192.168.95.12（讀）、192.168.95.13（mysql-proxy）

3、配置主從複制

詳細可以參考：mysql主從複制與主主複制

粗略介紹一下資料庫的主從複制的配置：

第一步：

在192.168.95.11中建立一個192.168.95.12主機中可以登入的MySQL使用者

使用者：mysql12

密碼：mysql12

mysql>GRANT REPLICATION SLAVE ON *.* TO ‘mysql12’@’192.168.95.12’ IDENTIFIED BY ‘mysql12’;

mysql>FLUSH PRIVILEGES;

第二步：

檢視192.168.95.11MySQL伺服器二進制檔案名與位置

mysql>SHOW MASTER STATUS;

第三步：

告知二進制檔案名與位置

在192.168.95.12中執行：

mysql> change master to

-> master_host='192.168.95.11',

-> master_user='mysql12',

-> master_password='mysql12',

-> master_log_file='mysql-bin.000124',

-> master_log_pos=586;

第四步：

在192.168.95.12中

mysql>SLAVE START;   #開啟複制

mysql>SHOW SLAVE STATUS\G   #檢視主從複制是否配置成功

主從複制配置成功！

（注意：上面Relicate_Do_DB:aa表示主從複制隻針對資料庫aa【這是我之前設定的就沒改了】，這裡就不講這個了，要想去了解學醫這個的話可以參考文章回到頂部

4、MySQL讀寫分離配置

百度雲下載下傳：連結：http://pan.baidu.com/s/1slTl18L 密碼：9j0m

　　4.1、安裝lua

　　官網下載下傳：http://www.lua.org/download.html

　　Lua 是一個小巧的腳本語言。Lua由标準C編寫而成，代碼簡潔優美，幾乎在所有作業系統和平台上都可以編譯，運作。

　　一個完整的Lua解釋器不過200k，在目前所有腳本引擎中，Lua的速度是最快的。這一切都決定了Lua是作為嵌入式腳本的最佳選擇。

　　1）、安裝lua需要依賴很多軟體包。

　　可以通過rpm -qa | grep name檢查以下軟體是否安裝：

　　gcc*、gcc-c++*、autoconf*、automake*、zlib*、libxml*、ncurses-devel*、libmcrypt*、libtool*、flex*、pkgconfig*、libevent*、glib*

　　若缺少相關的軟體包，可通過yum -y install方式線上安裝，或直接從系統安裝CD光牒中找到并通過rpm -ivh方式安裝。（我的話一般是直接在系統CD光牒軟體庫中找到直接rpm安裝的，有些找不到，則先在網上下載下傳然後在ftp傳給linux再進行安裝）

　　2）、依賴軟體安裝完畢後則進行編譯安裝lua

　　MySQL-Proxy的讀寫分離主要是通過rw-splitting.lua腳本實作的，是以需要安裝lua。

　　官網下載下傳：http://www.lua.org/download.html（下載下傳源碼包）

# wget http://www.lua.org/ftp/lua-5.1.4.tar.gz

# tar zxvf lua-5.1.4.tar.gz

# cd lua-5.1.4

# make linux

# make install

# export LUA_CFLAGS="-I/usr/local/include" LUA_LIBS="-L/usr/local/lib -llua -ldl" LDFLAGS="-lm"

（我安裝的時候是直接在CD光牒軟體庫中找到，直接rpm安裝）

　　4.2、安裝mysql-proxy

　　1）、首先檢視linux版本确認是32位還是64為系統

　　檢視linux核心版本

　　# cat /etc/issue

　　檢視linux版本

　　# cat /proc/version

　　2）、按系統位數下載下傳（上面百度雲連結64位的檔案）

　　3）、安裝

# tar –zxvf mysql-proxy-0.8.5- linux-rhel5-x86-64bit.tar.gz

# mkdir /usr/local/mysql-proxy

# cp ./ mysql-proxy-0.8.5-linux-rhel5-x86-64bit/* /usr/local/mysql-proxy

# cd /usr/local/mysql-proxy

安裝成功

5、MySQL讀寫分離測試

　　1）、修改rw-splitting.lua檔案

　　修改預設連接配接，進行快速測試，不修改的話要達到連接配接數為4時才啟用讀寫分離

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

　　# vi rw-splitting.lua

　　2）、修改完成後，啟動mysql-proxy

# cd /usr/local/mysql/bin

# ./mysql-proxy --proxy-read-only-backend-addresses=192.168.95.12:3306 --proxy-backend-addresses=192.168.95.11:3306 --proxy-lua-script=/usr/local/mysql-proxy/rw-splitting.lua &

參數： 

　　--proxy-read-only-backend-addresses　　#隻讀伺服器位址（ip）

　　--proxy-backend-addresses　　　　　　　#伺服器位址（主伺服器）

　　--proxy-lua-script　　　　　　　　　　　　#lua腳本路勁

　　&　　　　　　　　　　　　　　　　　　　　 #表示背景執行

　　3）、建立用于讀寫分離的資料庫連接配接使用者

　　使用者名：proxy1

　　密  碼：321

mysql>grant all on *.* to 'proxy1'@'192.168.95.13' identified by '321';

mysql>use aa;

mysql>create table tab1(id int auto_increment,name varchar(32) not null,primary key(id));

【因為已經開啟了主從複制是以，11、12主機mysql中都建立了這個使用者】

　　4）、測試登陸賬号[email protected]進行添加資料

　　可以使用任意ip用戶端登陸這個賬号

　　在192.168.95.13登陸：

　　# ./mysql -u proxy1 -P4040 -h192.168.95.13 –p

在兩個mysql中檢視結果：一緻

結果表明：賬号使用

(ps：id是自增長，之前高主主複制的時候更改了配置檔案，還沒更改回來，就将就用着先吧)

　　5）、關閉12mysql的從複制

　　mysql> stop slave;

　　6）、證明寫分離

　　使用[email protected]賬号打開多個用戶端進行插入資料

　　打開三個mysql用戶端分别插入2條資料：

mysql> insert into tab1 (name) values('stop_slave11111');

….

mysql> insert into tab1 (name) values('stop_slave6666’);

　　檢視：

　　分别登陸11mysql與12mysql檢視aa.tab1中的資料

　　主資料庫：

從資料庫：

結果中顯示插入的資料存在與主資料庫，而從資料庫沒有，是以證明寫能夠分離。

　　7）、證明讀分離

　　使用[email protected]賬号登陸mysql，檢視aa.tab1中的資料

mysql>select*from tab1;

結果中顯示隻有從資料庫的資料，結合上面的測試，可以證明讀分離。

6、建議

為了友善啟動與管理mysql-proxy可以建立mysql-proxy服務管理腳本

下面這個管理腳本僅适合以上我給出的安裝路徑位置

【此管理腳本需要按照自己的安裝路徑做出相應的修改方可使用】

1 #!/bin/sh

2

3 #

4 # mysql-proxy This script starts and stops the mysql-proxy daemon

5 #

6 # chkconfig: - 78 30

7 # processname: mysql-proxy

8 # description: mysql-proxy is a proxy daemon to mysql

9

10 # Source function library.

11 . /etc/rc.d/init.d/functions

12

13 #PROXY_PATH=/usr/local/bin

14 PROXY_PATH=/usr/local/mysql-proxy/bin

15

16 prog="mysql-proxy"

17

18 # Source networking configuration.

19 . /etc/sysconfig/network

20

21 # Check that networking is up.

22 [ ${NETWORKING} = "no" ] && exit 0

23

24 # Set default mysql-proxy configuration.

25 #PROXY_OPTIONS="--daemon"

26

27 PROXY_OPTIONS="--proxy-read-only-backend-addresses=192.168.95.12:3306 --proxy-backend-addresses=192.168.95.11:3306 --proxy-lua-script=/usr/local/mysql-proxy/rw-splitting.lua"

28

29 PROXY_PID=/usr/local/mysql-proxy/run/mysql-proxy.pid

30

31 # Source mysql-proxy configuration.

32 if [ -f /etc/sysconfig/mysql-proxy ]; then

33 . /etc/sysconfig/mysql-proxy

34 fi

35

36 PATH=$PATH:/usr/bin:/usr/local/bin:$PROXY_PATH

37 # By default it's all good

38 RETVAL=0

39

40 # See how we were called.

41 case "$1" in

42 start)

43 # Start daemon.

44 echo -n $"Starting $prog: "

45 $NICELEVEL $PROXY_PATH/mysql-proxy $PROXY_OPTIONS --daemon --pid-file=$PROXY_PID --user=root --log-level=debug --log-file=/usr/local/mysql-proxy/log/mysql-proxy.log

46 RETVAL=$?

47 echo

48 if [ $RETVAL = 0 ]; then

49 touch /var/lock/subsys/mysql-proxy]

50 echo "ok"

51 fi

52 ;;

53 stop)

54 # Stop daemons.

55 echo -n $"Stopping $prog: "

56 killproc $prog

57 RETVAL=$?

58 echo

59 if [ $RETVAL = 0 ]; then

60 rm -f /var/lock/subsys/mysql-proxy

61 rm -f $PROXY_PID

62 fi

63 ;;

64 restart)

65 $0 stop

66 sleep 3

67 $0 start

68 ;;

69 condrestart)

70 [ -e /var/lock/subsys/mysql-proxy ] && $0 restart

71 ;;

72 status)

73 status mysql-proxy

74 RETVAL=$?

75 ;;

76 *)

77 echo "Usage: $0 {start|stop|restart|status|condrestart}"

78 RETVAL=1

79 ;;

80 esac

81 exit $RETVAL

#---我将mysql-proxy服務管理腳本放在了/usr/local/mysql-proxy/init.d/檔案夾裡

#---給執行權限，建立相應目錄

#chmod +x /usr/local/mysql-proxy/init.d/mysql-proxy

#mkdir /usr/local/mysql-proxy/run

#mkdir /usr/local/mysql-proxy/log

#cd /usr/local/mysql-proxy/init.d/

#---啟動mysql-proxy

#./mysql-proxy start

#---停止mysql-proxy

#./mysql-proxy stop

#---重新開機mysql-proxy

#./mysql-proxy restart

一些相關參數：

PROXY_PATH=/usr/local/mysql-proxy/bin //定義mysql-proxy服務二進制檔案路徑

--proxy-read-only-backend-addresses=192.168.95.12:3306   //定義後端隻讀從伺服器位址

--proxy-backend-addresses=192.168.95.11:3306   //定義後端主伺服器位址

--proxy-lua-script=/usr/local/mysql-proxy/rw-splitting.lua   //定義lua讀寫分離腳本路徑

PROXY_PID=/usr/local/mysql-proxy/run/mysql-proxy.pid   //定義mysql-proxy PID檔案路徑

--daemon   //定義以守護程序模式啟動

--keepalive   //使程序在異常關閉後能夠自動恢複【上面的管理腳本沒有加上此參數】

--user=root   //以root使用者身份啟動服務

--log-level=debug   //定義log日志級别，由高到低分别有(error|warning|info|message|debug)

--log-file=/usr/local/mysql-proxy/log/mysql-proxy.log   //定義log日志檔案路徑