2014年8月26日 星期二

MySQL小密訣 - 如何以SQL_MODE使資料輸入更有效率:


透過MySQL的SQL_MODE參數可以影響MySQL的行為,使我們在輸入/匯入或更改資料時能使MySQL依我們希望的方式來處理資料.這個參數可在my.cnf或mysqld開機參數設定到整個資料庫,或在登入MySQL後以”set SQL_MODE =;”的方式臨時改變MySQL對當下的連線的行為模式.這些SQL_MODE中有指定個別特性的,也可組合性的以一個MODE代表好幾個特性的組合,同一個MySQL instance或連線可以有多個SQL_MODE組合,例如”set SQL_MODE=ANSI,NO_ENGINESUBSTITUTE,STRICT_TRANS_TABLES;”.這些MODE可以使資料操作(例如自外部系統匯入資料)更方便,相反的也可能造成使用者的混肴或對MySQL的誤解.主要的指定個別特性的SQL_MODE如下:

ALLOW_INVALID_DATES
使MySQL只會檢查月份是否在1到12之間,日期是否在1到31之間,而不會檢查整個日期的合理性,例如您想要存入前端使用者輸入的一模一樣的日期資料,而此時使用者輸入的日期是”2014-04-31”,如果沒將此SQL_MODE設起來,此值會轉成”0000-00-00”並傳出一個warning.

ANSI_QUOTES
能把“"”當成辨識字的引號就如同“`”一樣,在輸入的資料中有“字符時能使它順利輸入到MySQL.

ERROR_FOR_DIVISION_BY_ZERO
如果沒設此MODE以0當分母的算式會傳回NULL,也不會有warning.

HIGH_NOT_PRECEDENCE
運算式最前面的 “NOT”是否要優先處理,這和對舊版MySQL的向上相容有關,預設是NOT (boolean預算),若打開此MODE則NOT會加入boolean式運算的第一個運萛中
,以下例解釋:
#以下相當於NOT (1 BETWEEN -5 AND 5)
    mysql> SET sql_mode = '';
    mysql> SELECT NOT 1 BETWEEN -5 AND 5;
            -> 0
#以下相當於(NOT 1) BETWEEN -5 AND 5
    mysql> SET sql_mode = 'HIGH_NOT_PRECEDENCE';
    mysql> SELECT NOT 1 BETWEEN -5 AND 5;
            -> 1

IGNORE_SPACE
使funciton名和”(“間可否有空白,內建function名當成保留字,要以保留字當成辨識字必需在期前後加上引號,例如
    mysql> CREATE TABLE count (i INT);
    ERROR 1064 (42000): You have an error in your SQL syntax
#加上引號後:
    mysql> CREATE TABLE `count` (i INT);
    Query OK, 0 rows affected (0.00 sec)

NO_AUTO_CREATE_USER
下GRANT指令時如果指定的使用者不存在時預設會建一個新的使用者,以此mode將此功能關閉.

NO_AUTO_VALUE_ON_ZERO
在插入AUTO_INCREMENT欄位時的值為0或NULL值可產生自動跳號的值插入該欄位中,以此mode會使0值留下來而不會跳號,只有NULL值才會產生跳號值.

NO_BACKSLASH_ESCAPES

如果SQL指令中的值有'\'值會和MySQL的跳脫符號衝到,以此mode可使MySQL忽略跳脫符號,將'\'當成值插入欄位中.

NO_DIR_IN_CREATE

建表時忽略 INDEX DIRECTORY和DATA DIRECTORY, 當主從資料庫間的目錄位置不一樣時,在從服務器上有以此mode解決這個問題.

NO_ENGINE_SUBSTITUTION
如果沒設這個mode,建表時如果指定的storage engine不存在,會有warning,再以default storage engine來建這個表.

NO_FIELD_OPTIONS
下SHOW CREATE TABLE時不顯示MySQL特有的欄位選項,以使mysqldump產生的檔案較有可攜性.

NO_KEY_OPTIONS
下SHOW CREATE TABLE時不顯示MySQL特有的key選項,以使mysqldump產生的檔案較有可攜性.

NO_TABLE_OPTIONS
下SHOW CREATE TABLE指令時不顯示MySQL特有的table選項,以使mysqldump產生的檔案較有可攜性.


NO_UNSIGNED_SUBTRACTION

UNSIGNED整數相減預設產生不可以產生負值的結果,此mode可使結果為SIGNED(有負值).

NO_ZERO_DATE
決定日期欄是否可以為’0000-00-00’,5.6.17以後的版本此mode為deprecated.

NO_ZERO_IN_DATE

決定日期欄是否可有0(日或月).

ONLY_FULL_GROUP_BY

規定在SELECT命令中有GROUP BY子句時,SELECT的非總合(aggregate)欄位,HAVING清單中或ORDER BY子句中不可以有非GROUP BY子句沒有指定到的欄位.範仃如下:

    mysql> SELECT name, address, MAX(age) FROM t GROUP BY name;
    ERROR 1055 (42000): 't.address' isn't in GROUP BY


    mysql> SELECT name, MAX(age) AS max_age FROM t GROUP BY name
        -> HAVING max_age < 30;
    Empty set (0.00 sec)
    ERROR 1463 (42000): Non-grouping field 'max_age' is used in HAVING clause
第二個例子中改寫成HAVING MAX(age)就可執行因為該欄位在aggregate function.

PAD_CHAR_TO_FULL_LENGTH
CHAR欄位中的字串長度如果不到CHAR欄位定義的長度,設此MODE可將最後面補上空白字.例子如下:

    mysql> CREATE TABLE t1 (c1 CHAR(10));
    Query OK, 0 rows affected (0.37 sec)

    mysql> INSERT INTO t1 (c1) VALUES('xy');
    Query OK, 1 row affected (0.01 sec)

    mysql> SET sql_mode = '';
    Query OK, 0 rows affected (0.00 sec)

    mysql> SELECT c1, CHAR_LENGTH(c1) FROM t1;
    +------+-----------------+
    | c1   | CHAR_LENGTH(c1) |
    +------+-----------------+
    | xy   |               2 |
    +------+-----------------+
    1 row in set (0.00 sec)

    mysql> SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
    Query OK, 0 rows affected (0.00 sec)

    mysql> SELECT c1, CHAR_LENGTH(c1) FROM t1;
    +------------+-----------------+
    | c1         | CHAR_LENGTH(c1) |
    +------------+-----------------+
    | xy         |              10 |
    +------------+-----------------+
    1 row in set (0.00 sec)

PIPES_AS_CONCAT
在後多資料庫認可以||代表字串連接,MySQL可以此Mode把||當成字串連接功能. ,而不是OR的同意字.

REAL_AS_FLOAT
把REAL當成FLOAT的同意字,沒有此設定,MySQL會將REAL當戶DOUBLE.

STRICT_ALL_TABLES
所有的storage engine,表都以strict mode處理.

STRICT_TRANS_TABLES
使交易型storage engine的表以strict mode處理.

STRICT_TRANS_TABLES

如果無法將值插入一個交易型的表,則放棄此指令.對非交易型的表,如果值出現在單一指令或在多行插入指令的第一個row則放棄此指令

TRADITIONAL
當有指令有錯誤資料時,如同典型資料庫一樣會有”error”而不時只有”warning.

組合的SQL Modes


以下組合幾個單項Mode成為一個SQL Mode,以方便使用,目的多為增加和某些第三方的資料庫的相容性:

ANSI
目當於一起使用REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE

DB2
目當於一起使用Equivalent to PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS.

MAXDB
目當於一起使用PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER.

MSSQL
目當於一起使用PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS.

MYSQL323
目當於一起使用NO_FIELD_OPTIONS, HIGH_NOT_PRECEDENCE.

MYSQL40
目當於一起使用 NO_FIELD_OPTIONS, HIGH_NOT_PRECEDENCE.

ORACLE
目當於一起使用PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER.

POSTGRESQL
目當於一起使用_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS.

TRADITIONAL
目當於一起使用STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION.
 

2014年8月3日 星期日

MySQL Fabric 之二-實作及示範


在上一篇文章中”MySQL Fabric -為您的應用系統提供高可用,高擴充的資料庫”中介紹了MySQL Fabric的用途和架構,本文是為有興趣自己動手以MySQL Fabric做資料分片(Data Shar)的人所寫的,希望本文能為您說明所有的安裝和設定的細節,同時透過範例程式使您能了解如何使用MySQL Fabric所管理的MySQL 資料庫Server Farm。

演示環境及配置


本範例的MySQL資料庫 Server Farm有6個資料庫及存放Server Farm的設定訊息之Fabric Repository的資料庫,這7個資料庫全在一個電腦上其IP都是127.0.0.1,一開始的架構如下圖所示:
Fabric Repository資料庫使用的端口為3506,其他用於Server Farm的資料庫分成三個高可用組(HA Group)分別為名為gp-global的global group其唯一的資料庫使用3606
做完Fabric設定後名為gp1的分片高可用組(HA Group),其下有三個資料庫分別使用3306、3308、和3314端口
名為gp2的分片高可用組,其構成的資料庫有使用3408及3406端口的資料庫。
一開始時沒有gp1和gp2 HA group,這6個資料庫之間並沒有關係,當以MySQL Fabric做完設定後才有主從複製的關係。

 

MySQL Fabric的下載及


MySQL Fabric並非單獨的軟體,它附在MySQL Utilities之下,是由Python Script所構成的系統。安裝MySQL Fabric必需下載MySQL Utilities,其下載網址為http://dev.mysql.com/downloads/utilities/ ,我比較習慣下載source code再compile安裝。MySQL Utilities只能執行於Python 2.7或2.6之下,要安裝前必需先確認電腦上的Python版本,其指令為:

$ python –V

以source code安裝MySQL Utilities的過程如下:
在下載好MySQL Utilities的zip檔後,執行以下指令

$ unzip mysql-utilities-1.4.4.zip
$ cd mysql-utilities-1.4.4
$ python ./setup.py build
$ sudo python ./setup.py install


安裝好MySQL Utilities後可以下列指令確認MySQL Fabric可以執行:

$ mysqlfabric

 

設定MySQL Fabric


Fabric的設定過程如下:

1. 在預定成為MySQL Fabric之Repository的資料庫上建立資料庫帳戶 (範例中為使用3506端口的資料庫):

當在Fabric的設定檔中的[storage]段落中被指定為資料庫帳戶後(詳步驟3),在執行設定Fabric的設定時(執行步驟4的mysqlfabric manage setup的指令),Fabric會在該資料庫以此處所建立的資料庫帳戶建立所需要的表和資料庫物件。在登入MySQL後執行建立資料庫帳戶的指令如下:

CREATE USER ''@'<fabric-host-ip>' IDENTIFIED BY '';
GRANT ALL ON fabric.* USER ''@'<fabric-host-ip>';


2. 建立server farm中的資料庫,範例中所有的資料庫用同一台電腦,只是使用的端口,資料目錄和socket不同,這些資料庫所用的端口分別為3606、3306、3308、3314、3406及3408,各資料庫的參數檔請參考附件的參數檔。

3. 在參與Fabric運作的server farm中所有的資料庫執行建立帳戶和授權 (Server Farm所有的資料庫用同樣帳戶名和密碼):
Fabric在執行server farm中的資料庫的某些設定命令時-例如mysqlfabric promote HA group或建立data shard等,會使用該帳戶在各server farm的資料庫中執行資料庫操作指令。這個動作是在登入各server farm的資料庫執行以下命令:

CREATE USER '<fabric-db-user>'@'' IDENTIFIED BY '';
GRANT ALL ON *.* TO ''@'';

4. 編輯Fabric的設定檔

在Linux上Fabric設定檔預設的位置位於/et/mysql/fabric.cnf ,也可以在mysqlfabric的指令中加上—config=指定配置檔,例如:mysqlfabric –config=~/myfabric.cnf manage setup
我的Fabric配置檔如附件的fabric.cnf所示,各參數的說明請參考手冊http://dev.mysql.com/doc/mysql-utilities/1.4/en/fabric-configure.html

在此說明比較重要的段落:

[storage]
指定Fabric Repository資料庫的位、Fabric在執行時和Repository互動時所用的帳戶等。
[servers]
指定Fabric在運作時,在對其管轄的server farm中所有的資料庫下命令(複製配置、偵測心跳、故障移轉等)所用的帳號。
[protocol.xmlrpg]
MySQL Fabric節點的參數,應用端在建立資料庫連線時,要對MySQL Fabric節點連線以取得MySQL資料庫server farm中各高可用組(HA Group)的相關設定參數,所需要的連線MySQL Fabric節點的帳號密碼等資訊。對Fabric的連線也可以走SSL,在此處可以指定SSL連線的CA、CERT、KEY等訊息。
[failure-tracking]
指定Fabric對高可用組的資料庫多久Ping一次,ping多少次沒回應就會判定資料庫已經有問題,而啟動故障移轉對相關資訊。

5. 使用MySQL Fabric指令操作Fabric的:
MySQL Fabric所有的操作均使用mysqlfabric指令,只有參數不同。mysqlfabric help commands指令可列示這些操作的詳細用法及其說明。
MySQL Fabric的Server Farm操作過程如下:
初始化Fabric,在Fabric Repository的資料庫中建立Fabric所用的表

 

$ mysqlfabric manage setup

Fabric節點的設定


執行以下指令, 開啟MySQL Fabric

$ mysqlfabric mange start &

做完這個指令後,整個Fabric的架構如上圖所示,MySQL Fabric節點及其Reporitory資料庫均開啟,Server Farm的資料庫可以開啟,但是在執行Fabric指令前,這些資料庫是獨立的彼此間沒有關係。

  • 建立3個高可用組,分別是準備當global group的”gp-global”,和兩個要做shard 的”gp1” 和”gp2”。其指令如下,

$ mysqlfabric group create gp-global
$ mysqlfabric group create gp1
$ mysqlgabric group create gp2


  • 執行完建立高可用組的指令後只會在Fabric的Repository中增加一些紀錄,以下指令為各高可用組加入資料庫和promote資料庫才會使server farm中的資料庫有主從關係。所以先指定各組的資料庫:

$ mysqlfabric group add gp-global 127.0.0.1:3606
$ mysqlfabric group add gp1 127.0.0.1:3306
$ mysqlfabric group add gp1 127.0.0.1:3308
$ mysqlfabric group add gp1 127.0.0.1:3314
$ mysqlfabric group add gp2 127.0.0.1:3406
$ mysqlfabric group add gp2 127.0.0.1:3408


  • 讓MySQL Fabric 為各高可用組指定一個主資料庫,組內其他資料庫成為其從資料庫,指令如下:

$ mysqlfabric group promote gp1
$ mysqlfabric group promote gp2


  • 由於gp-global中只有一個資料庫,所以不需promote (實務上還是要有兩個以上資料庫以支援高可用) 。讓Fabric 能主動偵測server farm中所有資料庫的狀況,並在主資料庫有問題時由Fabric自動做故障移轉

$ mysqlfabric group activate gp1
$ mysqlfabric group activate gp2


以上指令在執行時Fabric會對server farm中各資料庫下指令進行主從複製的設定(如下圖所示)。注意,為了使這些設定順利進行,各資料庫的參數檔必需加上以下參數:

log-binlog
log-slave-update
binlog-formate=ROW
server-id=
gtid-mode=on
enforce-gtid-consistency



執行以上指令後(紅色箭頭代表Fabrtic下指令的方向),各組之內的主從關係就建立起來了,可用以下指令查各組的內容:

$ mysqlfabric group lookup_server

是各組的名稱,例如gp1,gp2
  • 在global group (gp-global)建立shard的定義 ,shard定義必需加到global group中,目前MySQL Fabric支援RANG和HASH shard,本範例中用RANGE shard:

$ mysqlfabric sharding create_definition RANGE gp-global


  • 上述指令會傳出一個mapping_id的序號(由1開始往後跳號),以後的shard設定中會用到該mapping_id,假定mapping_id是”1”
將shard table加入shard的定義中:

$ mysqlfabric sharding add table 1 employees.employees id


此指令中add table後的“1”是上一個指令傳出的mapping_id (意味著加在gp-global上的data shard),要做shard (分表分庫)的表為employees schema之下的employees表。 最後一個參數”id”代表employees.employees表中的各行以名為id的欄位中所存的值決定各該欄位要存於那一個shard中。
  • 指定各shard (HA group)所存放的記錄:
$ mysqlfabric sharding add_shard 1 gp1/1,gp2/1000 --state=ENABLED

上列指令指定當employees表中某一記錄的id欄位之值在1~999之間,Fabric會將該行存於HA group gp1中,id的值大於或等於1000的記錄則會存到HA group gp2中。本範例中gp1的主資料庫會使用由Fabric所挑選3314的資料庫,則當插入的記錄之id小於1000時會由Fabric aware connector送到3314的資料庫,再透過主從複製傳到HA group中其他的從資料庫(使用3306及3308的資料庫),如果插入的記錄id的值大於等於1000,則會將該指入指令送到gp2的主資料庫(3408的資料庫),再以複製的方式傳到gp2的從資料庫 (用3406的資料庫) 。
 


經過以上的指令,MySQL Server Farm會有如上圖所示的架構(藍色箭頭代表複製的方向),使用3606的資料庫是使用3314之資料庫和使用3408之資料庫的主資料庫,使用3314的資料庫又是使用3308之資料庫和使用3314之資料庫的主資料庫,另一個HA Group中使用3408的資料庫又是使用3306之資料庫的主資料庫。如果您有MySQL Enterprise Monitor可以如下圖所示的replication監看畫面看到這個架構。

 

使用MysQL Fabric的客戶端


MySQL Fabric能使應用系統的程式在對資料庫下SQL指令時幾乎不需要了解shard table是如何在各資料庫間分配其資料,應用程式透過Fabric aware connector (目前有Connecotr/J、Connector/Python及Connector/PHP) 到Fabric節點取得Fabric Server Farm的設定訊息,以此設定訊息配合SQL查詢或更新指令中的WHERE條件所用到的shard key之值,及插入指令中的shard key的值來決定要指命送到那一個資料庫執行。



本範例先示範以Java使用Fabric Server Farm,如上圖所示,Connector/J在新建Connection object時的URL中的主機名和端口都指向Fabric 節點所在的位置,以下的Java程式片段顯示Fabric節點的位置在127.0.0.1,使用的端口為32272。在開發Java應用前要先下載Connector/J 5.1.30或之後的版本 (下載網址在http://dev.mysql.com/downloads/connector/j/),並將其library的位址加入Java執行環境的CLASSPATH中。

  • 對MySQL的Server Farm下DDL及Global Table的DML指令:


如上圖所示綠色箭頭所示,Java程式中要下DDL或對global table (此處為employees.departments) 做DML時,建Connection Object的URL中要加fabricServerGroup參數,且指向global group(此處為gp-global)。以下程式片段說明這個過程:

/*
Fabric connection的Java Class 為Fabric特有的com.mysql.fabric.jdbc.FabricMySQLConnection
*/
import com.mysql.fabric.jdbc.FabricMySQLConnection;

/*
. 建Fabric Connection的URL中host name指向Fabric節點的主機
. port number指向32274,如同fabric.cnf中所指定的本範例使用32274
. 建connection的URL中要加上” fabricServerGroup”參數,且指向global group,本範例的global group名為”gp-global”
*/
String baseUrl= "jdbc:mysql:fabric://:32274";
String database= "world";
rawConnection = DriverManager.getConnection(baseUrl + database + "?
  fabricServerGroup=gp-global“,user, password);
statement = rawConnection.createStatement();

/*
建employees表的DDL
*/
statement.executeUpdate("create table employees.employees (emp_no int
  not null, first_name varchar(50), last_name varchar(50),
  department_id int, primary key (emp_no))");

/*
插入兩筆記錄到global table-departments
*/
statement.executeUpdate("insert into employees.departments values
  (1,'Sales'),(2,'Financial')");

  • Shard Table的操作:
 


如上圖所示下半段的綠色箭頭所示,對Sharded Table (本範例中為employees)的操作必需在建connection object時加上參數” fabricShardTable”,本範例的shard table為employees.employees,這個表在前述Fabric設定shard table時以 ” mysqlfabric sharding add table 1 employees.employees id” 指令設定到Fabric中。如此可在employees表大到無法以單一資料庫來處理時,可將其資料分散到多個shard database中,同時在下SQL命令不需担心這些指令要送到那一個資料庫去執行,由Fabric aware Connector (此範例中是Connecto/J)。

/*
建Connection Object時要加上fabricShardTable參數且指向己在Fabric中設定的shard table名稱
建立的Connection Object 要Down Cast成FabricMySQLConnection Class的Object,由此Connection Object所下的SQL指令會依各記錄的id欄位中的值,送到不同的HA group的Primary (master) database
*/
rawConnection =   DriverManager.getConnection("jdbc:mysql:fabric://127.0.0.1:32274/mysql?
fabricShardTable=employees.employees“, user, password);
FabricMySQLConnection connection = (FabricMySQLConnection)rawConnection;
/*
插入四筆記錄到employees.employees表中,依Fabric在” $ mysqlfabric sharding add_shard 1 gp1/1,gp2/1000 --state=ENABLED”設定指令中所定義的資料存放的原則,id為1,2的記錄會依Fabric中記錄的Server Farm設定存到gp1 高可用組中,id為10001,10002的記錄會依Fabric設定的shard table的記錄存放原則存到gp2 高可用組中
*/
PreparedStatement ps = connection.prepareStatement(
  "INSERT INTO employees.employees VALUES (?,?,?,?)");
Integer ids[] = new Integer[]{1, 2, 10001, 10002};
String firstNames[] = new String[]{"John", "Jane", "Andy", "Alice"};
String lastNames[] = new String[]{"Doe", "Doe", "Wiley", "Wein",};
String departmentIds[] = new String[]{"1", "2", "1", "2"};
for (int i = 0; i < 4; ++i) {
  connection.setShardKey(ids[i].toString());
  ps.setInt(1, ids[i]);
  ps.setString(2, firstNames[i]);
  ps.setString(3, lastNames[i]);
  ps.setString(4, departmentIds[i]);
  ps.executeUpdate();
}

  • 唯讀的操作

當Fabric的MySQL Server Farm的HA group中配置多個database時,其中除了有一個資料庫由Fabric挑選為master database,其他的資料庫均為該master database的slave database,這些slave database除了支援故障移轉外,還可支援唯讀的操作。只要在執行shard table之Connection object 上的setReadOnly(true) method後,透過這個Connection Object所下的查詢指令會隨機送到各HA group的secondary database (不會送到Mater Database)去執行,而達到讀寫分離的功能。同樣的Java程式開發者在下SQL指令時也不需担心要送到那一個資料庫,而由Fabric aware connector (Connector/J)負責將查詢指令送到適當的資料庫。

FabricMySQLConnection newConn = (FabricMySQLConnection)rawConnection;
/*
將Connection Object設為唯讀,經由此Connection Object所下的查詢會送到各HA group的secondary (slave) database
*/
newConn.setReadOnly(true);
/*
SQL指令中shard table – employees.employees JOIN global table -employees.departments以查各employee的部門名稱,再JOIN information_schema.global_variables,以列示執行這個SQL指令的資料庫所用使的端口
*/
ps = newConn.prepareStatement(
"select emp_no, first_name, last_name, d.name, i.variable_value
from employees.employees e, information_schema.global_variables i,
employees.departments d where e.department_id = d.department_id
and i.variable_name='port' and emp_no = ?");
for (int i = 0; i < 4; ++i) {
  // we need to specify the shard key before accessing the data
  newConn.setShardKey(ids[i].toString());
  ps.setInt(1, ids[i]);
  ResultSet rs = ps.executeQuery();
  rs.next();
  System.out.format("%7d | %-30s | %-30s | %-30s | %-30s%n", rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4),rs.getString(5));
  rs.close();
}

  • Java程式的執行結果:
未執行Java程式前,所有資料庫都的employees和departments表中都沒有資料, 以下指令證明這個狀態


$ ./bin/mysql -uroot -p -h127.0.0.1 -P3314 employees
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11360
Server version: 5.6.15-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql> select * from employees;
Empty set (0.00 sec)

mysql> select * from departments;
Empty set (0.00 sec)


  • 執行Java程式 (範例中的程式為demo.fabric.EmployeeJDBC.java),此程式配合Fabric的設定,會將id為1,2的記錄會送到使用端口3314的資料庫,id為10001,10002的記錄會送到使用端口3408的資料庫,這點由執行完插入指令後的查核SQL指令所查出的port number可證實。

$ java -jar dist/demo.fabric.jar
Querying employees

emp_no | first_name                     | last_name                      | deptartment                    | port                         
--------+--------------------------------+--------------------------------+--------------------------------+------------------
      1 | John                           | Doe                            | Sales                          | 3314                         
      2 | Jane                           | Doe                            | Financial                      | 3314                         
  10001 | Andy                           | Wiley                          | Sales                          | 3408                         
  10002 | Alice                          | Wein                           | Financial                      | 3408                         

Querying employees with read only
 emp_no | first_name                     | last_name                      | deptartment                    | port                         
--------+--------------------------------+--------------------------------+--------------------------------+------------------
      1 | John                           | Doe                            | Sales                          | 3308                         
      2 | Jane                           | Doe                            | Financial                      | 3308                         
  10001 | Andy                           | Wiley                          | Sales                          | 3406                         
  10002 | Alice                          | Wein                           | Financial                      | 3406                         


  • 驗證Data Shard的作用

登入到使用端口3314和3408的資料庫下查詢命令,以驗證所有資料庫都有global table – departments的資料,而shard table – employees只有Fabric設定所指定的資料-使用3314端口的資料庫只有id欄位小於1000的記錄,使用3408端口的資料庫只有id欄位大於或等於1000的記錄

$ ./bin/mysql -uroot -p -h127.0.0.1 -P3314 employees
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11360
Server version: 5.6.15-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from employees;
+--------+------------+-----------+---------------+
| emp_no | first_name | last_name | department_id |
+--------+------------+-----------+---------------+
|      1 | John       | Doe       |             1 |
|      2 | Jane       | Doe       |             2 |
+--------+------------+-----------+---------------+
2 rows in set (0.00 sec)

mysql> select * from departments;
+---------------+-----------+
| department_id | name      |
+---------------+-----------+
|             1 | Sales     |
|             2 | Financial |
+---------------+-----------+
2 rows in set (0.01 sec)


$ ./bin/mysql -uroot -p -h127.0.0.1 -P3408 employees
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11721
Server version: 5.6.15-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select *  from employees;
+--------+------------+-----------+---------------+
| emp_no | first_name | last_name | department_id |
+--------+------------+-----------+---------------+
|  10001 | Andy       | Wiley     |             1 |
|  10002 | Alice      | Wein      |             2 |
+--------+------------+-----------+---------------+
2 rows in set (0.00 sec)

mysql> select * from departments;
+---------------+-----------+
| department_id | name      |
+---------------+-----------+
|             1 | Sales     |
|             2 | Financial |
+---------------+-----------+
2 rows in set (0.00 sec)


  • 故障移轉


如上圖所示,關掉使用3314端口的資料庫,再觀查Server Farm的狀況,和同樣的Java程式執行的結果是否有變化。

關掉HA group gp1的primary database (使用端口3314的資料庫)

關掉使用端口3314的資料庫後可觀察到Fabric偵測到HA group gp1的primary database不見了,隨後變更gp1的primary database到另一個資料庫。

./bin/mysqladmin -uroot -p -h127.0.0.1 -P3314 shutdown
Enter password: 


$ 140801 12:56:20 mysqld_safe mysqld from pid file /home/ivan/mysql-advanced-5.6.15-linux-glibc2.5-x86_64/data3/ivan-PORTEGE-R930.pid ended


$
$ [WARNING] 1406868972.991539 - FailureDetector(gp1) - Server (6bc545e1-ebc9-11e3-8463-b4b6769a2ef5) in group (gp1) is unreachable.

$ [WARNING] 1406868975.002187 - FailureDetector(gp1) - Server (6bc545e1-ebc9-11e3-8463-b4b6769a2ef5) in group (gp1) is unreachable.

ivan@ivan-PORTEGE-R930:~/mysql56$ [WARNING] 1406868977.012753 - FailureDetector(gp1) - Server (6bc545e1-ebc9-11e3-8463-b4b6769a2ef5) in group (gp1) is unreachable.
[WARNING] 1406868977.029330 - Executor-2 - Reported issue (FAULTY) for server (6bc545e1-ebc9-11e3-8463-b4b6769a2ef5).
[INFO] 1406868977.036337 - Executor-2 - Master (6bc545e1-ebc9-11e3-8463-b4b6769a2ef5) in group (gp1) has been lost.
[INFO] 1406868977.208566 - Executor-2 - Master has changed from 6bc545e1-ebc9-11e3-8463-b4b6769a2ef5 to 68930fd6-ebc9-11e3-8463-b4b6769a2ef5.



由MySQL Enterprise Monitor觀察到的Server Farm在Failover之後的狀態
MySQL Enterprise Monitor報告使用3314的資料庫無法使用 (出現火災符號),MEM報告使用3308的資料庫成為使用3306的資料庫的master database,同時它也的master database也由使用3314的資料庫轉為使用3606的資料庫。由此證實Fabric所發動的故障移轉成功。



  • 故障移轉後再Java程式的結果

如上圖所示,故障移轉完成後,應插入HA group gp1的指令送到Fabric所選擇的故障移轉資料庫-使用端口3308資料庫執行,對gp1的read only查詢由於使用端口3308資料庫己成為gp1的primary (slave)資料庫,則會送到另一個secondary database -使用端口3308資料庫。

$ java -jar dist/demo.fabric.jar
Querying employees
 emp_no | first_name                     | last_name                      | deptartment                    | port                         
--------+--------------------------------+--------------------------------+--------------------------------+------------------
      1 | John                           | Doe                            | Sales                          | 3308                         
      2 | Jane                           | Doe                            | Financial                      | 3308                         
  10001 | Andy                           | Wiley                          | Sales                          | 3408                         
  10002 | Alice                          | Wein                           | Financial                      | 3408                         

Querying employees with read only
 emp_no | first_name                     | last_name                      | deptartment                    | port                         
--------+--------------------------------+--------------------------------+--------------------------------+------------------
      1 | John                           | Doe                            | Sales                          | 3306                         
      2 | Jane                           | Doe                            | Financial                      | 3306                         
  10001 | Andy                           | Wiley                          | Sales                          | 3406                         
  10002 | Alice                          | Wein                           | Financial                      | 3406                         

  • 以Python使用Fabric

以下是一個名為add_emp_fabric.py的Python範例程式,由此Python程式驗證同一個Fabric 的Server Farm可同時支援Java和Pthon應用程式的存取:

import mysql.connector
from mysql.connector import fabric
def add_emp(conn,emp_no,first_name,last_name,department_id):
    conn.set_property(tables=["employees.employees"], key=emp_no,mode=fabric.MODE_READWRITE)
    cur = conn.cursor()
    cur.execute(
        "INSERT INTO employees.employees VALUES (%s, %s, %s, %s)",
        (emp_no,first_name,last_name,department_id)
        )
conn = mysql.connector.connect(
    fabric={"host" : "localhost", "port" : 32274, "username": "admin", "password" : "welcome1"},
    user="root", database="employees", password="welcome1",
    autocommit=True
)
conn.set_property(tables=["employees.employees"], scope=fabric.SCOPE_LOCAL)

add_emp(conn, 22, "Billy", "Bob","1")
add_emp(conn, 8372, "Banana", "Man","2")
add_emp(conn, 9, "Bill", "Ben","1")
add_emp(conn, 5006, "Andy", "Pandy","1")
add_emp(conn, 15050, "John", "Smith","1")
add_emp(conn, 8, "Tommy", "Cannon","2")


執行指令 $ python ./add_emp_fabric.py 後到使用端口3308及3406的資料庫查詢可得到以下的結果:

$ ./bin/mysql -uroot -p -h127.0.0.1 -P3308
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 42326
Server version: 5.6.15-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from employees.employees;
+--------+------------+-----------+---------------+
| emp_no | first_name | last_name | department_id |
+--------+------------+-----------+---------------+
|      1 | John       | Doe       |             1 |
|      2 | Jane       | Doe       |             2 |
|      8 | Tommy      | Cannon    |             2 |
|      9 | Bill       | Ben       |             1 |
|     22 | Billy      | Bob       |             1 |
+--------+------------+-----------+---------------+
5 rows in set (0.00 sec)



$ ./bin/mysql -uroot -p -h127.0.0.1 -P3406
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 42258
Server version: 5.6.15-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from employees.employees;
+--------+------------+-----------+---------------+
| emp_no | first_name | last_name | department_id |
+--------+------------+-----------+---------------+
|   5006 | Andy       | Pandy     |             1 |
|   8372 | Banana     | Man       |             2 |
|  10001 | Andy       | Wiley     |             1 |
|  10002 | Alice      | Wein      |             2 |
|  15050 | John       | Smith     |             1 |
+--------+------------+-----------+---------------+
5 rows in set (0.00 sec)

以此驗證Connector/Python也能依Fabric所設定的data shard規則到適當的資料庫中存取資料。

  • 切分shard (Split Shard)
如果一個shard的資料太多,MySQL Fabric還能支援資料庫Shard的分割-自原有的HA group 切分更多HA group ,進而將原shard中應存於新shard的資料搬到新的HA group中,其作法是:
i. 開一個新的資料庫,例如使用3706端口
ii. 開一個Fabric HA group:
$ mysqlfabric group create gp3
iii. 為新開的資料庫加上MySQL database instance
$ mysqlfabric group add gp3 :3706
iv. 分割HA group,將值大於2000的HA group 之內的記錄移到新的HA group中
$ mysqlfabric  sharding split_shard 2 gp3 –split_value=2000
上列指令的結果會使Fabric的MySQL Server Farm多一個shard,並啟動mysqldump(在Fabric的設定檔中指定用此程式導出資料)將id大於等於2000的記錄搬到新的gp3中,以後Connector/J在處理SQL指令時會將屬於gp3的記錄的操作送到使用端口3706的資料庫。

結論


以上的配置和Fabric應用的開發的說明,是用以展示MySQL Fabric的功能和特性,希望讀者能透過實作,實際體驗Fabric的功能和特性,如果您有興趣,可在https://drive.google.com/file/d/0B1hoXeCyIS3WdkgwaWUtcXNEWFE/edit?usp=sharing 下載我已做好的Fabric指令script、資料庫配置和應用程, 其中資料庫及Fabric要先準備好, 各資料庫的my.cnf的datadir的位置要指向各資料實際的資料目錄。

另外, 我在MySQL第一次聚會也做過 Fabric的示範, 那次所用的簡報存於http://www.slideshare.net/ivantu/my-sql-fabric-webinar-tw2  供需要的人參考

MySQL Fabric的產品理念相當先進,但是它還是一個相當新的產品,還有許多功能改進的空間,已有許多列在其WL中,這些包括:
  • 更友善的操作及管理介面
  • 更多的shard policy例如LIST shard
  • MySQL Fabric Node本身的高可用
  • 一道SQL查詢到多個shard收齊所有的記錄再傳回應用程式
  • Shard內部能支援更多的高可用架構,例如主備-DRBD,或MySQL Cluster
我們歡迎大家一起來試用MySQL Fabric,且將您的意見回饋給開發團隊。