mysql-proxy 簡介

MySQL Proxy是一個處于你的client端和MySQL server端之間的簡單程序，它可以監測、分析或改變它們的通信。它使用靈活，沒有限制，常見的用途包括：負載平衡，故障、查詢分析，查詢過濾和修改等等。

MySQL Proxy就是這么一個中間層代理，簡單的說，MySQL Proxy就是一個連接池，負責將前臺應用的連接請求轉發給后臺的數據庫，并且通過使用lua腳本，可以實現復雜的連接控制和過濾，從而實現讀寫分離和負載平衡。對于應用來說，MySQL Proxy是完全透明的，應用則只需要連接到MySQL Proxy的監聽端口即可。當然，這樣proxy機器可能成為單點失效，但完全可以使用多個proxy機器做為冗余，在應用服務器的連接池配置中配置到多個proxy的連接參數即可。

MySQL Proxy更強大的一項功能是實現“讀寫分離”，基本原理是讓主數據庫處理事務性查詢，讓從庫處理SELECT查詢。數據庫復制被用來把事務性查詢導致的變更同步到集群中的從庫。

?

?

?

架構示意圖

1 2 3 4 5

???????????????????___?master_mysql(10.10.10.3) ??????????????????|??????????| ???mysql-proxy(10.10.10.2)???| ??????????????????|___???????| ???????????????????????slave_mysql(10.10.10.4)

?

(一)mysql主從同步

1、mysql-master設置

(1)

1 2 3

server-id?=?1????//master端ID號 binlog-ignore-db?=????//設置不同步的sql庫 binlog-do-db=????//設置同步的數據

(2)設置完上面以后重啟mysql

(3)添加用戶訪問主庫,并且只有備份權限

1 2	mysql?>?grant?replication?slave??on?*.*?to?'rsync'@'10.10.%'?identified?by?'1q2w3e4rys'?with?grant?option; mysql?>?flush?privileges;

(4)鎖表,主庫數據繼續寫入?

1	mysql?>?flush?tables?with?read?lock;

(5)記錄 master file與position的值,slave庫配置同步時候要使用

(6)導出mysql主庫數據

1	mysqldump?-uuser?-p?databases?>?databases.sql

注:這里有個問題,當主庫數據上G,較大時需要做如下操作

1 2	--max_allowed_packet=XXX????//客戶端/服務器之間通信的緩存區的最大大小; --net_buffer_length=XXX????//TCP/IP和套接字通信緩沖區大小,創建長度達net_buffer_length的行;

注意：max_allowed_packet和net_buffer_length不能比目標數據庫的配置數值大,否則可能出錯。

首先確定目標庫的參數值 ? ????

1 2	mysql>show?variables?like?'max_allowed_packet'; mysql>show?variables?like?'net_buffer_length';

根據參數值書寫mysqldump命令,如:

1	mysql?>?mysqldump?-uuser?-p??數據庫名?-e?--max_allowed_packet=1048576?--net_buffer_length=16384?>?databases.sql

(7)master庫同步到slave服務器上

1	#?scp?databases.sql??root@host:/path

2、mysql-slave設置

(1)

1	server-id?=?2

(2)登錄mysql創建sql數據庫

1	mysql?>?CREATE?DATABASE?`?sql_db?`?DEFAULT?CHARACTER?SET?utf8?COLLATE?utf8_general_ci;

(3)導入master庫數據到slave庫中

1	mysql?-uuser?-p??sql_db?<?databases.sql

(4)登錄mysql停止slave

1	mysql?>?stop?slave;

(5)配置從庫同步

1	mysql?>?change?master?to?master_host=’10.10.10.2’,master_user=’rsync’,master_password=’1q2w3e4rys’,master_port=3306,master_log_file=’mysql-bin.000047’,master_log_pos=107;

(6)啟動同步功能

1	mysql?>?start?slave;

(7)解除主庫鎖表

1	unlock?tables;

(8)查看slave同步狀態

1	mysql?>?show?slave?status\G????//多執行幾遍,查看數據寫入后的狀態

注：查看上面的Slave_IO_Running: Yes 和Slave_SQL_Running: Yes ?2個都為yes則證明主從同步正常,如果有任一個顯示NO,則證明同步有問題.可以查看數據庫日志文件,里面基本上會顯示出錯誤之處,根據錯誤一步一步排查,基本上都可以解決的.

?

(二)mysql-proxy讀寫分離

1、安裝mysql-proxy

(1)

1 2 3 4

#?wget?http://ftp.ntu.edu.tw/pub/MySQL/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 #?cd?/usr/local #?ln?-sv?mysql-proxy-0.8.5-linux-el6-x86-64bit?mysql-proxy

2、設置mysql-proxy屬主屬組

(1)

1 2	#?groupadd?mysql-proxy #?useradd?-g?mysql-proxy?-s?/sbin//nologin?-M?mysql-proxy

3、配置mysql-proxy

(1)為mysql-proxy提供SysV服務腳本,腳本如下:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84

#!/bin/bash # #?mysql-proxy?This?script?starts?and?stops?the?mysql-proxy?daemon # #?chkconfig:?-?78?30 #?processname:?mysql-proxy #?description:?mysql-proxy?is?a?proxy?daemon?for?mysql ?? #?Source?function?library. .?/etc/rc.d/init.d/functions ?? prog="/usr/local/mysql-proxy/bin/mysql-proxy" ?? #?Source?networking?configuration. if?[?-f?/etc/sysconfig/network?];?then ????.?/etc/sysconfig/network fi ?? #?Check?that?networking?is?up. [?${NETWORKING}?=?"no"?]?&&?exit?0 ?? #?Set?default?mysql-proxy?configuration. ADMIN_USER="admin" ADMIN_PASSWD="admin" ADMIN_LUA_SCRIPT="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua" PROXY_OPTIONS="--daemon" PROXY_PID=/var/run/mysql-proxy.pid PROXY_USER="mysql-proxy" ?? #?Source?mysql-proxy?configuration. if?[?-f?/etc/sysconfig/mysql-proxy?];?then ????.?/etc/sysconfig/mysql-proxy fi ?? RETVAL=0 ?? start()?{ ????echo?-n?$"Starting?$prog:?" ????daemon?$prog?$PROXY_OPTIONS?--pid-file=$PROXY_PID?--proxy-address="$PROXY_ADDRESS"?--user=$PROXY_USER?--admin-username="$ADMIN_USER"?--admin-lua-script="$ADMIN_LUA_SCRIPT"?--admin-password="$ADMIN_PASSWORD" ????RETVAL=$? ????echo ????if?[?$RETVAL?-eq?0?];?then ????????touch?/var/lock/subsys/mysql-proxy ????fi } ?? stop()?{ ????echo?-n?$"Stopping?$prog:?" ????killproc?-p?$PROXY_PID?-d?3?$prog ????RETVAL=$? ????echo ????if?[?$RETVAL?-eq?0?];?then ????????rm?-f?/var/lock/subsys/mysql-proxy ????????rm?-f?$PROXY_PID ????fi } #?See?how?we?were?called. case?"$1"?in ????start) ????????start ????????;; ????stop) ????????stop ????????;; ????restart) ????????stop ????????start ????????;; ????condrestart|try-restart) ????????if?status?-p?$PROXY_PIDFILE?$prog?>&/dev/null;?then ????????????stop ????????????start ????????fi ????????;; ????status) ????????status?-p?$PROXY_PID?$prog ????????;; ????*) ????????echo?"Usage:?$0?{start|stop|restart|reload|status|condrestart|try-restart}" ????????RETVAL=1 ????????;; esac ?? exit?$RETVAL

(2)將上述內容保存為/etc/rc.d/init.d/mysql-proxy,給予執行權限,而后加入到服務列表。

1 2	#?chmod?+x?/etc/rc.d/init.d/mysql-proxy #?chkconfig?--add?mysql-proxy

(3)為服務腳本提供配置文件/etc/sysconfig/mysql-proxy,內容如下所示：

1 2 3 4 5 6 7 8

#?Options?for?mysql-proxy? ADMIN_USER="admin" ADMIN_PASSWORD="admin" ADMIN_ADDRESS="" ADMIN_LUA_SCRIPT="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua" PROXY_ADDRESS="" PROXY_USER="mysql-proxy" PROXY_OPTIONS="--daemon?--log-level=info?--log-use-syslog?--plugins=proxy?--plugins=admin?--proxy-backend-addresses=10.10.10.3:3306?--proxy-read-only-backend-addresses=10.10.10.4:3306?--proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"

(4)mysql-proxy配置選項注解

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

--help --help-admin --help-proxy --help-all?————?以上四個選項均用于獲取幫助信息； ?? --proxy-address=host:port?————?代理服務監聽的地址和端口； --admin-address=host:port?————?管理模塊監聽的地址和端口； --proxy-backend-addresses=host:port?————?后端mysql服務器的地址和端口； --proxy-read-only-backend-addresses=host:port?————?后端只讀mysql服務器的地址和端口； --proxy-lua-script=file_name?————?完成mysql代理功能的Lua腳本； --daemon?————?以守護進程模式啟動mysql-proxy； --keepalive?————?在mysql-proxy崩潰時嘗試重啟之； --log-file=/path/to/log_file_name?————?日志文件名稱； --log-level=level?————?日志級別； --log-use-syslog?————?基于syslog記錄日志； --plugins=plugin,..?————?在mysql-proxy啟動時加載的插件； --user=user_name?————?運行mysql-proxy進程的用戶； --defaults-file=/path/to/conf_file_name?————?默認使用的配置文件路徑；其配置段使用[mysql-proxy]標識； --proxy-skip-profiling?————?禁用profile； --pid-file=/path/to/pid_file_name?————?進程文件名；

(5)復制如下內容建立admin.lua文件,將其保存至/usr/local/mysql-proxy/share/doc/mysql-proxy/目錄中.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100

--[[?$%BEGINLICENSE%$ ?Copyright?(c)?2007,?2012,?Oracle?and/or?its?affiliates.?All?rights?reserved. ?? ?This?program?is?free?software;?you?can?redistribute?it?and/or ?modify?it?under?the?terms?of?the?GNU?General?Public?License?as ?published?by?the?Free?Software?Foundation;?version?2?of?the ?License. ?? ?This?program?is?distributed?in?the?hope?that?it?will?be?useful, ?but?WITHOUT?ANY?WARRANTY;?without?even?the?implied?warranty?of ?MERCHANTABILITY?or?FITNESS?FOR?A?PARTICULAR?PURPOSE.?See?the ?GNU?General?Public?License?for?more?details. ?? ?You?should?have?received?a?copy?of?the?GNU?General?Public?License ?along?with?this?program;?if?not,?write?to?the?Free?Software ?Foundation,?Inc.,?51?Franklin?St,?Fifth?Floor,?Boston,?MA ?02110-1301??USA ?? ?$%ENDLICENSE%$?--]] ?? function?set_error(errmsg)? ????proxy.response?=?{ ????????type?=?proxy.MYSQLD_PACKET_ERR, ????????errmsg?=?errmsg?or?"error" ????} end ?? function?read_query(packet) ????if?packet:byte()?~=?proxy.COM_QUERY?then ????????set_error("[admin]?we?only?handle?text-based?queries?(COM_QUERY)") ????????return?proxy.PROXY_SEND_RESULT ????end ?? ????local?query?=?packet:sub(2) ?? ????local?rows?=?{?} ????local?fields?=?{?} ?? ????if?query:lower()?==?"select?*?from?backends"?then ????????fields?=?{? ????????????{?name?=?"backend_ndx",? ??????????????type?=?proxy.MYSQL_TYPE_LONG?}, ?? ????????????{?name?=?"address", ??????????????type?=?proxy.MYSQL_TYPE_STRING?}, ????????????{?name?=?"state", ??????????????type?=?proxy.MYSQL_TYPE_STRING?}, ????????????{?name?=?"type", ??????????????type?=?proxy.MYSQL_TYPE_STRING?}, ????????????{?name?=?"uuid", ??????????????type?=?proxy.MYSQL_TYPE_STRING?}, ????????????{?name?=?"connected_clients",? ??????????????type?=?proxy.MYSQL_TYPE_LONG?}, ????????} ?? ????????for?i?=?1,?#proxy.global.backends?do ????????????local?states?=?{ ????????????????"unknown", ????????????????"up", ????????????????"down" ????????????} ????????????local?types?=?{ ????????????????"unknown", ????????????????"rw", ????????????????"ro" ????????????} ????????????local?b?=?proxy.global.backends[i] ?? ????????????rows[#rows?+?1]?=?{ ????????????????i, ????????????????b.dst.name,??????????--?configured?backend?address ????????????????states[b.state?+?1],?--?the?C-id?is?pushed?down?starting?at?0 ????????????????types[b.type?+?1],???--?the?C-id?is?pushed?down?starting?at?0 ????????????????b.uuid,??????????????--?the?MySQL?Server's?UUID?if?it?is?managed ????????????????b.connected_clients??--?currently?connected?clients ????????????} ????????end ????elseif?query:lower()?==?"select?*?from?help"?then ????????fields?=?{? ????????????{?name?=?"command",? ??????????????type?=?proxy.MYSQL_TYPE_STRING?}, ????????????{?name?=?"description",? ??????????????type?=?proxy.MYSQL_TYPE_STRING?}, ????????} ????????rows[#rows?+?1]?=?{?"SELECT?*?FROM?help",?"shows?this?help"?} ????????rows[#rows?+?1]?=?{?"SELECT?*?FROM?backends",?"lists?the?backends?and?their?state"?} ????else ????????set_error("use?'SELECT?*?FROM?help'?to?see?the?supported?commands") ????????return?proxy.PROXY_SEND_RESULT ????end ?? ????proxy.response?=?{ ????????type?=?proxy.MYSQLD_PACKET_OK, ????????resultset?=?{ ????????????fields?=?fields, ????????????rows?=?rows ????????} ????} ????return?proxy.PROXY_SEND_RESULT end

(6)測試

1 2	#mysql?-uadmin?-padmin?-h110.10.10.2?--port=4041? mysql?>?SELECT?*?FROM?backends;

1 2 3 4 5 6 7

+-------------+-------------------+-------+------+------+-------------------+ |?backend_ndx?|?address???????????|?state?|?type?|?uuid?|?connected_clients?| +-------------+-------------------+-------+------+------+-------------------+ |???????????1?|?10.10.10.3:3306???|?up????|?rw???|?NULL?|?????????????????0?|? |???????????2?|?10.10.10.4:3306???|?up????|?ro???|?NULL?|?????????????????0?|? +-------------+-------------------+-------+------+------+-------------------+ 2?rows?in?set?(0.00?sec)

?

本文出自 “阿俊博客” 博客，請務必保留此出處http://ssc4469.blog.51cto.com/6315913/1854011

《新程序員》：云原生和全面數字化實踐50位技術專家共同創作，文字、視頻、音頻交互閱讀

總結

以上是生活随笔為你收集整理的基于mysql主从同步的proxy读写分离的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。