Recent posts

透過MySQL Proxy實現讀寫分離

凌晨3:19 0 Comments


既上一篇文章MYSQL REPLICATION (MASTER SLAVE) 建置之後,若要實現讀寫分離則需要透過SQL Proxy來實現,以下分享安裝與設定方法以及過程中遇到的問題解決方法和需注意的事項

環境:
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

0 意見:

MySQL Replication (Master Slave) 建置

上午9:08 0 Comments




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:
  $ mysql -u root -p

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
解除資料唯獨狀態:
  mysql> UNLOCK TABLES;

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複寫:
  mysql> START 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. 解除資料唯獨狀態:
  mysql> UNLOCK TABLES;

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:

0 意見: