MySQL主從（MySQL proxy Lua讀寫分離設定，一主多從同步配置，分庫分表方案）

標籤：多版本   https   同步   /etc/   無法啟動   self   insert   star   blog   

Mysql Proxy Lua讀寫分離設定

一.讀寫分離說明

1. 讀寫分離（Read/Write Splitting），基本的原理是讓主要資料庫處理事務性增、改、刪操作（INSERT、UPDATE、DELETE），而從資料庫處理SELECT查詢操作。資料庫複寫被用來把事務性操作導致的變更同步到叢集中的從資料庫。 

 

1、設定說明

1. Master伺服器: 192.168.41.196  

2. Slave伺服器: 192.168.41.197  

3. Proxy伺服器: 192.168.41.203 

2、安裝Mysql Proxy

在Proxy伺服器上安裝即可. 如果源碼方式安裝, 需提前安裝pkg-config,libevent,glibc,lua等依賴包, 非常麻煩, 建議直接使用二進位版.

1. # cd /u01/software/mysql  

2. # tar -zxvf Mysql Proxy-0.8.1-linux-rhel5-x86-32bit.tar.gz -C /usr/local  

3. # cd /usr/local  

4. # ln -s Mysql Proxy-0.8.1-linux-rhel5-x86-32bit Mysql Proxy  

5. # vi + ~/.bash_profile  

6. export PATH=$PATH:/usr/local/Mysql Proxy/bin/  

7. # . ~/.bash_profile 

3、Mysql Proxy選項說明

1. # Mysql Proxy help-all 

管理功能選項:

1. admin-address=host:port 指定一個mysqo-proxy的管理連接埠, 預設是4041;  

2. admin-username=<string> username to allow to log in  

3. admin-password=<string> password to allow to log in  

4. admin-lua-script=<filename> script to execute by the admin plugin 

代理功能選項:

1. -P, proxy-address=<host:port> 是Mysql Proxy 伺服器端的監聽連接埠, 預設是4040;  

2. -r, proxy-read-only-backend-addresses=<host:port> 唯讀Slave的地址和連接埠, 預設為不設定;  

3. -b, proxy-backend-addresses=<host:port> 遠程Master地址和連接埠, 可設定多個做failover和load balance, 預設是127.0.0.1:3306;  

4. proxy-skip-profiling 關閉查詢分析功能, 預設是開啟的;  

5. proxy-fix-bug-25371 修正 mysql的libmysql版本大於5.1.12的一個#25371號bug;  

6. -s, proxy-lua-script=<file> 指定一個Lua指令碼來控制Mysql Proxy的運行和設定, 這個指令碼在每次建立串連和指令碼發生修改的的時候將重新調用; 

其他選項:

1. defaults-file=<file>設定檔, 可以把Mysql Proxy的參數資訊置入一個設定檔裡;  

2. daemon Mysql Proxy以守護進程方式運行  

3. pid-file=file 設定Mysql Proxy的儲存PID檔案的路徑  

4. keepalive try to restart the proxy if it crashed, 保持串連啟動進程會有2個, 一號進程用來監視二號進程, 如果二號進程死掉自動重啟proxy. 

4、資料庫準備工作

（1）安裝半同步補丁(建議)

讀寫分離不能迴避的問題之一就是延遲, 可以考慮Google提供的SemiSyncReplication補丁.

（2）給使用者授權

在Master/Slave建立一個測試使用者, 因為以後用戶端發送的SQL都是通過Mysql Proxy伺服器來轉寄, 所以要確保可以從Mysql Proxy伺服器上登入MySQL主從庫.

1. mysql> grant all privileges on *.* to ‘u_test‘@‘192.168.41.203‘ identified by ‘xxx‘ with grant option; 

（3）在Master建立測試表

1. mysql> create table db_test.t_test (col varchar(10));  

2. mysql> insert into db_test.t_test values (‘testA‘);  

3. mysql> select * from db_test.t_test;  

4. +-+  

5. | col   |  

6. +-+  

7. | testA |  

8. +-+ 

5、Mysql Proxy啟動

（1）修改讀寫分離lua指令碼

預設最小4個最大8個以上的用戶端串連才會實現讀寫分離, 現改為最小1個最大2個:

1. # vi +40 /usr/local/Mysql Proxy/share/doc/Mysql Proxy/rw-splitting.lua  

2.  connection pool  

3. if not proxy.global.config.rwsplit then  

4.         proxy.global.config.rwsplit = {  

5.                 min_idle_connections = 1,  

6.                 max_idle_connections = 2,  

7.                 is_debug = true   

8.         }         

9. end 

這是因為Mysql Proxy會檢測用戶端串連, 當串連沒有超過min_idle_connections預設值時, 不會進行讀寫分離, 即查詢操作會發生到Master上.

（2）啟動Mysql Proxy

建議使用設定檔的形式啟動, 注意設定檔必須是660許可權, 否則無法啟動. 如果有多個Slave的話, proxy-read-only-backend-addresses參數可以配置多個以逗號分隔的IP:Port從庫列表.

1. # killall Mysql Proxy   

2. # vi /etc/Mysql Proxy.cnf  

3. [Mysql Proxy]  

4. admin-username=wangnc 

5. admin-password=iamwangnc 

6. admin-lua-script=/usr/local/Mysql Proxy/lib/Mysql Proxy/lua/admin.lua  

7. proxy-backend-addresses=192.168.41.196:3351  

8. proxy-read-only-backend-addresses=192.168.41.197:3351  

9. proxy-lua-script=/usr/local/Mysql Proxy/share/doc/Mysql Proxy/rw-splitting.lua  

10. log-file=/var/tmp/Mysql Proxy.log  

11. log-level=debug 

12. daemon=true 

13. keepalive=true 

14. # chmod 660 /etc/Mysql Proxy.cnf  

15. # Mysql Proxy defaults-file=/etc/Mysql Proxy.cnf  

16. # ps -ef | grep Mysql Proxy | grep -v grep  

17. root      1869     1  0 18:16 ?        00:00:00 /usr/local/Mysql Proxy/libexec/Mysql Proxy defaults-file=/etc/Mysql Proxy.cnf  

18. root      1870  1869  0 18:16 ?        00:00:00 /usr/local/Mysql Proxy/libexec/Mysql Proxy defaults-file=/etc/Mysql Proxy.cnf  

19. # tail -50f /var/tmp/Mysql Proxy.log 

6、用戶端串連測試

（1）先停止Slave的複製進程

1. mysql> stop slave; 

（2）串連Proxy連接埠, 插入資料

1. # mysql -uu_test -pxxx -h192.168.41.203 -P4040 -Ddb_test  

2. mysql> insert into db_test.t_test values (‘testB‘);  

3. mysql> select * from db_test.t_test;  

4. +-+  

5. | col   |  

6. +-+  

7. | testA |  

8. | testB |  

9. +-+ 

（3）多開幾個用戶端, 串連Proxy連接埠, 查詢資料

1. # mysql -uu_test -pxxx -h192.168.41.203 -P4040 -Ddb_test  

2. mysql> select * from db_test.t_test;  

3. +-+  

4. | col   |  

5. +-+  

6. | testA |  

7. +-+ 

如果查詢不到上步新插入的資料, 說明串連到了Slave, 讀寫分離成功. 在同一線程再插入資料並驗證:

1. mysql> insert into db_test.t_test values (‘testC‘);  

2. mysql> select * from db_test.t_test;  

3. +-+  

4. | col   |  

5. +-+  

6. | testA |  

7. +-+ 

發現insert操作成功, 但是select不出剛插入的資料, 說明同一線程也讀寫分離成功. 從日誌中可以驗證:

1. # tail -50f /var/tmp/Mysql Proxy.log  

2. ...  

3. [read_query] 192.168.41.203:45481  

4.   current backend   = 0 

5.   client default db = db_test 

6.   client username   = u_test 

7.   query             = select * from db_test.t_test  

8.   sending to backend : 192.168.41.197:3351  

9.     is_slave         : true  

10.     server default db: db_test  

11.     server username  : u_test  

12.     in_trans        : false  

13.     in_calc_found   : false  

14.     COM_QUERY       : true  

15. [read_query] 192.168.41.203:45481  

16.   current backend   = 0 

17.   client default db = db_test 

18.   client username   = u_test 

19.   query             = insert into db_test.t_test values (‘testC‘)  

20.   sending to backend : 192.168.41.196:3351  

21.     is_slave         : false  

22.     server default db: db_test  

23.     server username  : u_test  

24.     in_trans        : false  

25.     in_calc_found   : false  

26.     COM_QUERY       : true 

（4）測試完畢後, 啟動Slave的複製進程

1. mysql> start slave; 

7、正式環境說明

1、Mysql Proxy當前還只是個測試版, MySQL官方還不建議用到生產環境中;

2、Mysql Proxy的rw-splitting.lua指令碼在網上有很多版本, 但是最準確無誤的版本仍然是源碼包中所附帶的rw-splitting.lua指令碼, 如果有lua指令碼編程基礎的話, 可以在這個指令碼的基礎上再進行最佳化；

3、Mysql Proxy實際上非常不穩定, 在高並發或有錯誤串連的情況下, 進程很容易自動關閉, 因此開啟keepalive參數讓進程自動回復是個比較好的辦法, 但還是不能從根本上解決問題, 因此通常最穩妥的做法是在每個從伺服器上安裝一個Mysql Proxy供自身使用, 雖然比較低效但卻能保證穩定性；

4、Amoeba for MySQL是一款優秀的中介軟體軟體, 同樣可以實現讀寫分離, 負載平衡等功能, 並且穩定性要大大超過Mysql Proxy, 建議大家用來替代Mysql Proxy, 甚至MySQL-Cluster.

 

 

二、MySQL一主多從同步配置

1.配置（3台上都一樣）

在/etc目錄下可能無my.cnf檔案，從/user/share/mysql目錄中拷貝my-medium.cnf 到/etc並修改成my.cnf

[[email protected] etc]# cp /usr/share/mysql/my-medium.cnf my.cnf

[[email protected] etc]# ll |grep my

-rwxr-xr-x 1 root root 5204 Feb 13 22:52 my_bak

-rwxr-xr-x 1 root root 4765 Jul 10 23:07 my.cnf

 

master 上;

[[email protected] ~]# vi /etc/my.cnf

1.修改master上的設定檔my.cnf。

在[mysqld]下添加如下欄位：

server-id = 1

log-bin=mysql-bin

binlog-do-db=YYY //需要同步的資料庫

binlog-ignore-db=mysql //被忽略的資料庫

binlog-ignore-db=information-schema //被忽略的資料庫

 

在master上為slave添加一個同步帳號

mysql> grant replication slave on *.* to ‘affairlog‘@‘192.168.2.182‘ identified by ‘pwd123‘;

//在slave1上登陸成功

mysql> grant replication slave on *.* to ‘affairlog‘@‘192.168.2.111‘ identified by ‘pwd123‘;

//在slave2上登陸成功

 

儲存後，重啟master的mysql服務：

service mysql restart;

用show master status命令查看日誌情況

mysql> show master status\G;

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

File: mysql-bin.000087

Position: 106

Binlog_Do_DB: YYY

Binlog_Ignore_DB: mysql,information-schema

1 row in set (0.00 sec)

2.修改slave1上的設定檔my.cnf。

在[mysqld]下添加如下欄位

[[email protected] ~]# vi /etc/my.cnf

server-id=182

master-host=192.168.3.101

master-user= affairlog

master-password=pwd123

master-port=3306

master-connect-retry=60

replicate-do-db=YYY //同步的資料庫

replicate-ignore-db=mysql //被忽略的資料庫

replicate-ignore-db=information-schema //被忽略的資料庫

 

查看Master上 master_log_file的檔案名稱 和master_log_pos 。

mysql>change master to master_host=‘192.168.3.101‘ master_user=‘affairlog‘ master_password=‘pwd123‘ master_log_file=‘mysql-bin.000087‘ master_log_pos=1845;

儲存後，重啟slave的mysql服務：

service mysql restart;

 

修改slave2上的設定檔my.cnf，和上面類似，只是把server-id改下，為了方便，我都用了相應的ip某位，

so，slave2上我設定的server-id是111。

在進入兩個slave機中的mysql。

mysql>start slave;

mysql>show slave status\G;

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.3.101

Master_User: affairlog

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000087

Read_Master_Log_Pos: 106

Relay_Log_File: vm111-relay-bin.000002

Relay_Log_Pos: 251

Relay_Master_Log_File: mysql-bin.000087

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: YYY

Replicate_Ignore_DB: mysql,information-schema

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: 106

Relay_Log_Space: 406

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:

1 row in set (0.00 sec)

如果兩個slave中的Slave_IO_Running、Slave_SQL_Running狀態均為Yes則表明設定成功。

 

Slave_IO_Running：串連到主庫，並讀取主庫的日誌到本地，產生本地記錄檔

Slave_SQL_Running:讀取本地記錄檔，並執行日誌裡的SQL命令。

 

三、MySQL分庫分表方案：https://my.oschina.net/ydsakyclguozi/blog/199498

MySQL主從（MySQL proxy Lua讀寫分離設定，一主多從同步配置，分庫分表方案）