MySQL Master/Slave Server (Replication Database)

MySQL 提供 Master/Slave 機制讓您輕易的完成多個 MySQL Server 之間的資料同步,有了多個資料同步的 MySQL Server 在管理上會較有彈性,例如你可以建置備援主機或是進行負載平衡等等。但是要注意:一台 Master Server 可以擁有很多台 Slave Server;但一台 Slave Server 只可對應到一台 Master Server。

架設步驟:

[事前準備]

  1. 確保 Master 與 Slave 之間的資料一致

[Master Server 方面]

  1. 設定 Server-id
  2. 開啟 Binary Log
  3. 設定 Replication Slave 權限

[Slave Server 方面]

  1. 設定 Server-id
  2. 將 Master_Host 設定為 Master Server
  3. 啟動與檢查 Slave Server 的狀態

在此範例中我們假設:

[Master Server]

  1. IP 為 192.168.1.1
  2. server-id 為 1
  3. 進行資料同步時所使用的帳號資訊:
    帳號: slave_server
    密碼: 12345678

[Slave Server]

  1. IP 為 192.168.1.2
  2. server-id 為 2

Master/Slave Server 架設

一、Master Server 方面

A.設定 Server-id
首先要設定 server-id。基本上沒有什麼特別的限制,只要 Master 和 Slave 的 server-id 不一樣即可,但其值必需為 1 至 2^32-1 之間。但為了方便識別,通常我們會把這個值設定為 IP 的最後一組數字,例如若 IP 為 192.168.1.1,則 server-id 設定為 1;若 IP 為 192.168.1.2,則 server-id 設定為 2。
[mysqld]
server-id=1

B.開啟 Binary Log

修改 MySQL Server 的系統設定檔,在 [mysqld] 下方加上 log-bin=mysql-bin,例如:

 

引用:

[mysqld]
log-bin

MySQL 的 Binary Log 會將所有對於資料庫的修改操作全部記錄起來,而 Slave 與 Master 之間進行資料同步的方式很簡單,就是 Slave 會把 Master Server 的 Binary Log 拿過來執行,也就是說 Slave Server 會 “重做" 在 Master Server 上發生的各種修改操作。因此 Master Server 勿必要開啟 Binary Log 功能,否則 Master/Slave 架構無法運作。

C.設定 Replication Slave 權限

我們必須要在 Master Server 上做設定,讓 Slave 具有可以從 Master Server 上 Copy 資料的權限(正式的說法為 Replication Slave Priviledges),所需使用的指令如下:

 

引用:

GRANT REPLICATION SLAVE ON *.* TO ‘user’@’slaver_ip’ IDENTIFIED BY ‘PASSWORD’;

意思為:

  1. 允許 slaver_ip 這個 IP 使用 USER 帳號,來進行資料同步(Replication)。
  2. slave_server 這個帳號的密碼為 PASSWORD。

二、Slave Server 方面

A.設定 Server-id

在 Slave 我們將其設定為 2:

 

引用:

[mysqld]
server-id=2

B.將 Master_Host 設定為 Master Server

我們必須要明確的告訴 Slave Server 哪一台 Server 才是 Master Server,修改MY.CNF:

 

引用:

[mysqld]
server-id=2
master-host=master_ip
master-user=user
master-password=password

naster-port=3306
replicate-do-db=mydb#預作同步資料庫
log-slave-updates

意思為:

  1. Master Server 是 192.168.1.1
  2. 使用 TCP Port 3306 連接
  3. 使用 slave_server 這個帳號登入
  4. 登入時使用的密碼為 12345678

將 Master / Slave 的 MySQL 停掉
將要複製的資料庫 (/var/db/mysql/) 打包後,複製一份到 Slave MySQL 那邊

將 Master / Slave 兩邊的的記錄檔
例如:
rm /var/db/mysql/*.*

Master/Slave 的應用

vbulletin 原本即內建支援多台 MySQL Server 之間的負載平衡,只要你事先建立好 MySQL Master/Slave Server,並在 Slave Server 開放 Master Server 的權限,接下來只要在 vbb 的設定檔中做一些簡單的修改即可。

備註:

查詢 Master / Slave 資料同步的狀況

MySQL Master

mysql> show master status\G
*************************** 1. row ***************************
File: db-bin.001
Position: 82044017
Binlog_do_db: mydb
Binlog_ignore_db:
1 row in set (0.00 sec)

MySQL Slave

mysql> show slave status\G
*************************** 1. row ***************************
Master_Host: x.x.x.x
Master_User: replication
Master_Port: 3306
Connect_retry: 60
Master_Log_File: db-bin.001
Read_Master_Log_Pos: 82190094
Relay_Log_File: db-relay-bin.001
Relay_Log_Pos: 564644047
Relay_Master_Log_File: db-bin.001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_do_db: mydb
Replicate_ignore_db:
Last_errno: 0
Last_error:
Skip_counter: 0
Exec_master_log_pos: 82190094
Relay_log_space: 564644047
1 row in set (0.00 sec)

Slave 上會有兩個記錄檔 master.info 、 relay-log.info

# cat master.info
db8-bin.005
44110392
x.x.x.x
replication
1234 (密碼)
3306
60

# cat relay-log.info
./db-relay-bin.002
631411217
db-bin.001
44173174

PHP MySQL Tips

Continuing from my earlier post on PHP performance, I thought I’d share a few Mysql tips that I’ve learnt over the years. Hope it helps someone and please leave a comment with your own tips or provide any corrections to the ones mentioned.

Word searching

1.

SELECT * FROM TABLE WHERE MATCH (`field`) AGAINST ('Keyword')

(Fastest)

2.

SELECT * FROM TABLE WHERE MATCH (`field`) AGAINST ('+Keyword' IN BOOLEAN MODE)

(Fast)

3.

SELECT * FROM TABLE WHERE RLIKE '(^| +)Keyword($| +)'

OR

SELECT * FROM TABLE WHERE
RLIKE '([[:space:]]|[[:<:]])Keyword([[:space:]]|[[:>:]])'

(Slow)

Contains searching

1.

SELECT * FROM TABLE WHERE MATCH (`field`) AGAINST ('Keyword*' IN BOOLEAN MODE)

(Fastest)

2.

SELECT * FROM TABLE WHERE FIELD LIKE 'Keyword%'

(Fast)

3.

SELECT * FROM TABLE WHERE MATCH (`field`) AGAINST ('*Keyword*' IN BOOLEAN MODE)

(Slow)

4.

SELECT * FROM TABLE WHERE FIELD LIKE '%Keyword%'

(Slow)

Recordsets

1.

SELECT SQL_CALC_FOUND_ROWS * FROM TABLE WHERE Condition LIMIT 0, 10

SELECT FOUND_ROWS()

(Fastest)

2.

SELECT * FROM TABLE WHERE Condition LIMIT 0, 10

SELECT COUNT(PrimaryKey) FROM TABLE WHERE Condition

(Fast)

3.

$result = mysql_query("SELECT * FROM table", $link);
$num_rows = mysql_num_rows($result);

(Very slow)

Joins

Use an INNER JOIN when you want the joining table to only have matching records that you specify in the join. Use LEFT JOIN when it doesn’t matter if the records contain matching records or not.

SELECT * FROM products
INNER JOIN suppliers ON suppliers.SupplierID = products.SupplierID

Returns all products with a matching supplier.

SELECT * FROM products
LEFT JOIN suppliers ON suppliers.SupplierID = products.SupplierID

WHERE suppliers.SupplierID IS NULL

Returns all products without a matching supplier.

Best practice

1. Always use lowercase for table names. (If you use different OS’s this is a must)
2. Always prepend the table name to the field. E.g. ProductName, SupplierPostCode.
This makes multiple joins very easy.
3. Always create a primary id field with the name of the table followed by the id. e.g. ProductID
4. Index fields used for joins.
5. Use a separate logging table or transactions for logs of table updates, deletes etc.

如何秀出複數的排序條件資料

SELECT * FROM Product ORDER BY SellDate DESC

因為資料的SellDate可能同時會有幾十筆都相同,我希望可以在篩選出這批資料後在更詳細的依照他們的編號去做排序…

1. 先過濾出整批的較新日期的資料,資料舊的往後排
2. 再依照這些相同SellDate的資料去依照他們的編號往下排,編號大的排上面

SELECT * FROM Product ORDER BY SellDate DESC, product_no DESC

若果編號在另一個 table (假設名稱是 ‘Porduct2’), ‘Product’ 和 ‘Product2’ 都有 common key ‘id’:

SELECT Product.* FROM Product, Product2 WHERE Product.id = Product2.id ORDER BY Product.SellDate DESC, Product2.product_no DESC