2014年4月15日 星期二

[Linux] High Availability MySQL 筆記:auto-increment-increment, auto-increment-offset, my.cnf @ Ubuntu 12.04

想起來很妙,在過去累積至今 2014 年初的工作經驗上,對於處理資料儲存方面,我並非使用 SQL DB 的,結果今年一口氣摸了不少 XD 不過原理都大同小異,就順便筆記一下。

以 High Availability MySQL (MySQL HA) 為例,通常為了降低系統複雜度又要提供 HA 時,可以考慮將 Read/Write 分離,讓 Write 在某一檯機器上,但 Read 可以有一批機器,只是開發上又要留意,如果 client 有更新資料時,必須讓 client 後續的動作也在 write 那檯機器,以免 MySQL Replication 的過程還未及時,這是讀比寫大量的使用情境。

如果寫也要做 HA 時,最常碰到的問題就是每檯 server 上的 primary key collision 的問題,以兩台某 table 的 int id 為例,讓其中一檯為 2N,另一台是 2N + 1,其中 N 是第幾筆資料,同理有 10 台,那就依序為 10N, 10N+1, 10N+2, ..., 10N+9。

達成這個效果,慶幸地只需要更改 my.cnf 或動態更新環境變數即可,共有兩個參數:

$ sudo vim /etc/mysql/my.cnf

[mysqld]
# ...
# http://dev.mysql.com/doc/refman/5.5/en/replication-options-master.html
auto-increment-increment = 10
auto-increment-offset = 1

$ sudo service mysql restart


如果不想要讓 mysql restart 的話,可以透過設定 global variable 的方式:

mysql> set global auto_increment_increment=10;
mysql> set global auto_increment_offset=1;


另外,也可以查詢目前情況:

mysql> show variables like '%auto_increment%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+

mysql> set global auto_increment_increment=10;

mysql> show session variables like '%auto_increment%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+

mysql> show global variables like '%auto_increment%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 10    |
| auto_increment_offset    | 1     |
+--------------------------+-------+


需留意 session variables 代表是此時 mysql connection 的狀態,若透過 set global 變數的方式,需要重連才會看到。

透過上述 primary 的分開後,除了可以做 write HA 外(如:Master-Master 架構),其實也可以用在移機過程,例如目前有 Server 1 跟 Server 2 且 Server 1 是目前的 DB server,想要把 db 從 Server 1 移到 Server 2 時,先透過 MySQL Replication 機制讓 Server 2 同步跟著 Server 1 資料,等到資料差不多時,可以把 target db connection 角色從 Server 1 移到 Server 2,但轉移的過程中總是有一些連線還在跟 Server 1 溝通,就會變成有新資料新增到 Server 2 上,而 Server 1 裡還未同步到 Server 2的資料,其 primary key id 很大的機會跟 Server 2 裡的一樣,導致 primary key collision 問題。

因此,為了解決這個問題,就可以透過 auto-increment-increment 和 auto-increment-offset 的設定,讓兩台的 auto-increment-offset 不一樣,就可以避免這種問題了。

2 則留言:

  1. 你好,在文章中你提到多台主機 write 的狀況下採用 auto_increment 各自 10N ++ .. 的方式處理

    請問若是遭遇以下狀況如何處置

    1. 需要統計 auto_increment 時如何處理,在一般狀況的資料庫可能已經幾千萬編號,但可能某一台只有幾千幾百號。
    2. 此方式可容納 Replication *10/台的資料庫主機,若是資料壯大後 10+n 台資料庫以不敷使用,如何處置?
    3. 承2 , 若改回 1+1 的方式處置後,以往的 auto_increment 資料欄位是否已不可靠?

    不吝指教 , 謝謝

    回覆刪除
  2. 坊間常見的 Write HA,大多是 N=2 ,就兩台而已。兩台就可以進行兩邊同步機制,Master Master replication。在同步機制上,就會碰到 primary key 衝突,因此才有 auto_increment_increment 跟 auto_increment_offset 的搭配,來確保兩台 db server 內不會有衝突。

    至於統計 auto_increment 的需求,我這邊就比較沒經驗了。通常 HA 架構有多組時,大多是設計成流量均衡分佈,在站流量均衡的需求上,應該要多台都很平均才是。

    至於 Replication 的部分,不少是設計一台 Write ,多台 Read only 的行為,例如以 write x1 + read x4 共有 5台 server 架構,同時 read 要加機器都很方便,可以不斷增加。由於 read server 是從 write server 複製而來的,所以本身不見得會設定 auto_increment_increment 或 auto_increment_offset ,因為不會有 write 需求。

    因此對於 2 跟 3 的需求,我就不太清楚該怎樣回答了,也可能我這邊經驗還沒有很夠。

    回覆刪除