[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 不一樣，就可以避免這種問題了。