環境:
OS: Ubuntu 14.10
MySQL: 5.5
MySQL-Proxy 安裝與設定
1. 安裝 mysql-proxy
$ sudo apt-get install mysql-proxy
|
2. 安裝 lua script (proxy實現讀寫分離需透過rw-splitting.lua script，故需安裝lua)
$ sudo apt-get install lua5.2
|
3. 編輯設定
$ sudo vim /etc/default/mysql-proxy
ENABLED="true"
OPTIONS="--defaults-file=/etc/mysql/mysql-proxy.cnf"
|
$ sudo vim /etc/mysql/mysql-proxy.cnf
[mysql-proxy]
daemon = true
user = mysql
proxy-skip-profiling = true
keepalive = true
max-open-files = 2048
event-threads = 50
pid-file = /var/run/mysql-proxy.pid
log-file = /var/log/mysql-proxy.log
log-level = debug
#主控台 IP、PORT ex. 192.168.0.100:4401
admin-address = [Proxy-Server IP]:[Port]
#用於登入主控台的帳號
admin-username = [account]
#特別注意密碼需與連結的sql master密碼一致
admin-password = [password]
admin-lua-script = /usr/lib/mysql-proxy/lua/admin.lua
#供web連結使用 ex. 192.168.0.100:3307
proxy-address = [Proxy-Server IP]:[Port]
#master node IP、PORT，mysql預設port:3306
proxy-backend-addresses = [Master-Server IP]:[Port]
#slave node IP、PORT，多台可用逗點隔開 ex. 192.168.0.102:3306,
192.168.0.103:3306
proxy-read-only-backend-addresses=[Slave-Server IP]:[Port]
#設定讀寫分離腳本
proxy-lua-script=/usr/share/mysql-proxy/rw-splitting.lua
|
4. 啟動mysql-proxy
$ sudo service mysql-proxy start
|
proxy主控台登入
$ mysql -h [proxy-server ip] -P [port] -u [account] -p #ex. mysql -h 192.168.0.100 -P 4401 -u root -p
|
目前mysql-proxy主控台僅支援兩個指令
透過指令 select * from backends 可檢視目前所有node狀態
啟用分流的連線數設定，編輯rw-splitting.lua檔
$ sudo vim /usr/share/mysql-proxy/rw-splitting.lua
|
設定min、max_idle_connections數值:
常見問題:
1. Web Application透過proxy讀取slave資料時會出現亂碼
解決方法在每台node的my.cnf設定檔內的 [mysqld] 加入以下設定:
$ sudo vim /etc/mysql/my.cnf
[mysqld]
skip-character-set-client-handshake
character-set-server = utf8
collation-server = utf8_general_ci
init-connect = SET NAMES utf8
|
SQL Replication是常見的 load balance 架構，可透過實作讀(Slave)寫(Master)分離進而減輕系統的loading，也可以藉由Slave複寫的資料做其他應用而不影響主要資料，例如 : 資料備份、資料分析..等。
以下分享Replication的佈置方式，在下一篇將介紹如何透過MySQL Proxy實現讀寫分離
About SQL Replication
- Master
- 接收查詢指令 (SELECT)
- 接收資料變更指令 (INSERT、UPDATE、DELETE)
- Slave
- 接收查詢指令 (SELECT)
- 接收 Master 資料庫上設定複寫的資料庫內容更新
- MySQL 複寫機制為非同步
- MySQL 複寫機制是由 Slave 主動向 Master 要求資料庫的內容更新
- Slave 不需要一直連接 Master 才能運作，長時間中斷連線仍能回復資料庫同步的狀態
Build Environment
- OS : Ubuntu 14.10
- MySQL : 5.5
Master Setting
1. 編輯 /etc/mysql/my.cnf，在[mysqld]區段內加入以下設定:
[mysqld]
#bind-address = 127.0.0.1
// 註解此行，因linux預設MySQL只會bind本機網路介面
server-id = 1 // id為唯一值，不可重覆
log-bin=mysql-bin
// 指定產生bin log檔案名稱，使MySQL記錄所有DB交易記錄
|
若只想複寫部分資料庫，可以加上binlog-do-db參數:
binlog-do-db = db_name1
binlog-do-db = db_name2
|
儲存後，重啟MySQL:
$ /etc/init.d/mysql restart
|
登入MySQL:
2. 在Master建立給Slave連接複寫資料的專用帳號
mysql> CREATE USER 'account'@'ip' IDENTIFIED BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'account'@'ip';
mysql> FLUSH PRIVILEGES;
|
3. 在Master建立一次完整備份，在Replication完成前，Master跟Slave的資料必須一致，
故在匯出資料前先Lock成唯讀:
mysql> FLUSH TABLES WITH READ LOCK;
|
開始備份:
$ mysqldump -u root -p --master-data --all-databases > master.sql
|
解除資料唯獨狀態:
Slave Setting
1. 將Master備份檔(master.sql)還原到Slave資料庫伺服器:
$ mysql -u root -p --default-character-set=utf8 < master.sql
|
2. 設定Slave的 /etc/mysql/my.cnf，在[mysqld]區段裡加入以下設定:
[mysqld]
server-id = 2
// Slave也可以設定log-bin參數，如此Slave也能當成Master供其他台複寫資料，這又是另一段故事了...more
|
儲存後，重啟MySQL:
$ /etc/init.d/mysql restart
|
3. 設定Slave資料庫，指定Master資料庫登入資訊，並啟動複寫機制:
mysql> CHANGE MASTER TO MASTER_HOST='master_ip',
MASTER_USER='account',
MASTER_PASSWORD='password';
// 指定master設定的複寫專用帳號
|
啟動Slave複寫:
4. 檢查Slave資料庫的複寫機制是否正常運作:
mysql> SHOW SLAVE STATUS \G;
|
正常狀態:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
|
打完收工~
Resync Replication
Master Setting
1. 重置Master，並Lock資料庫:
mysql> RESET MASTER;
mysql> FLUSH TABLES WITH READ LOCK;
|
2. 建立完整備份:
$ mysqldump -u root -p --master-data --all-databases > master.sql
|
3. 解除資料唯獨狀態:
Slave Setting
1. 停止Slave，並將Master備份檔(master.sql)還原到Slave資料庫伺服器，再重置Slave:
mysql> STOP SLAVE;
$ mysql -u root -p --default-character-set=utf8 < master.sql
mysql> RESET SLAVE;
|
2. 指定在Master資料庫的登入資訊，並啟動複寫機制:
mysql> CHANGE MASTER TO MASTER_HOST='master_ip',
MASTER_USER='account',
MASTER_PASSWORD='password';
// 指定master設定的複寫專用帳號
mysql> START SLAVE;
|
3. 檢查Slave資料庫的複寫機制是否正常運作:
mysql> SHOW SLAVE STATUS \G;
|
正常狀態:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
|
.png)
