MySQL InnoDB Cluster環境搭建和簡單測試

InnoDB Cluster初印象

  記得MySQL Group Replicatioin 剛開始的時候，MySQL界很是轟動，等待了多年，終于有了官方的這個高可用解決方案。你要說還有一些方案補充，比如MySQL Cluster,MySQL Proxy,這些的使用率個人感覺還是不高，也就是經受的考驗還不夠，原因有很多，就不贅述了。

   不久，我和一個MySQL DBA有了下面的一個基本對話。

   我： MySQL GR GA之后，里面的自動切換功能確實很贊，能夠做到讀寫分離，原本MHA的方案現在MGR也可以做了。

   MySQL DBA:如果數據庫發生了故障，這個自動切換的過程，其實對于應用不是透明的，因為讀寫節點相當于漂移到了另外一臺服務器上，除非再做個中間件。

   我：單純MGR目前還做不了這個，它目前只是保證數據庫層面的這種切換和高可用。

   MySQL DBA:所以說MGR的企業級應用還是需要一些輔助，這樣才算是一個完整的解決方案。

    不久，MySQL InnoDB Cluster推出，我覺得這個方案想比原來的MGR更進一步，說實話，我很看好這個方案，盡管目前愿意真正去用的用戶確實不多。

    如果你看一下官方的這個架構圖，就會發現，MGR本身就是Innodb Cluster的一部分，還有兩個組件，MySQL Shell,MySQL Router,這三板斧就是InnoDB Cluster的一個核心組件，而正如我之前所說，可以看到MySQL的一個格局和定位，他正在很努力去解決以前詬病的問題。

安裝前先保證Python滿足要求

    要安裝InnoDB Cluster,環境的一個基本要求就是Python,我看了下，很多默認的系統版本是2.6，而它的最低要求是2.7及以上，所以還是需要提前準備下這個部分。

    如果你的系統是Python 2.6版本的，可以考慮升級到2.7，參考如下的方法。

    下載安裝包，部署

 wget http://python.org/ftp/python/2.7/Python-2.7.tar.bz2     --no-check-certificate
./configure   
make all   
make install   
make clean   
make distclean   
查看Python的版本
# /usr/local/bin/python2.7 -V
Python 2.7
做基本的環境設置，替換舊的Python
mv /usr/bin/python /usr/bin/python2.6
ln -s /usr/local/bin/python2.7 /usr/bin/python

sandbox安裝部署InnoDB Cluster

搭建InnoDB Cluster顯而易見需要多臺服務器，而如果在一臺服務器上練習測試，也是全然沒有問題，如果想更快更方便的測試模擬，還可以使用sandbox來做，首先你得有sandbox，接著InnoDB Cluster的三大組件是MGR,MySQL Shell,MySQL Router,所以你可以從官網直接下載下來。

然后我們開啟安裝之旅。

使用MySQL Shell的命令mysqlsh開始部署，創建一個端口為3310的實例

mysql-js> dba.deploySandboxInstance(3310)
A new MySQL sandbox instance will be created on this host in
/root/mysql-sandboxes/3310

輸入密碼之后，一個3310端口的MySQL服務就啟動了。
Please enter a MySQL root password for the new instance:
Deploying new MySQL instance...
Instance localhost:3310 successfully deployed and started.
Use shell.connect('root@localhost:3310'); to connect to the instance.
接著創建另外兩個節點 3320,3330
dba.deploySandboxInstance(3320)
dba.deploySandboxInstance(3330)
我們切換到3310的MySQL實例，準備開始創建Cluster
mysql-js>  \connect root@localhost:3310
Creating a Session to 'root@localhost:3310'
Enter password:
Closing old connection...
Classic Session successfully established. No default schema selected.

定義一個Cluster變量，節點1就開啟了Cluster創建之旅，可以從下面的信息看出，至少需要3個節點

mysql-js>  var cluster = dba.createCluster('testCluster')
A new InnoDB cluster will be created on instance 'root@localhost:3310'.
Creating InnoDB cluster 'testCluster' on 'root@localhost:3310'...
Adding Seed Instance...
Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.
At least 3 instances are needed for the cluster to be able to withstand up to  one server failure.
接著把另外兩個節點加入進來，先加入端口為3320的節點
mysql-js> cluster.addInstance('root@localhost:3320')
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.
Please provide the password for 'root@localhost:3320':
Adding instance to the cluster ...
加入端口為3330的節點，日志和節點2相似。
mysql-js> cluster.addInstance('root@localhost:3330')
這個時候Cluster就創建好了。

這個時候，我們再配置一下MySQL Router，創建個軟鏈接，保證能夠正常調用。

# ln -s /home/innodb_cluster/mysql-router-2.1.3-linux-glibc2.12-x86-64bit/bin/mysqlrouter   /usr/bin/mysqlroute
# which mysqlroute
/usr/bin/mysqlroute
配置MySQL Router的啟動節點為端口3310的實例
# mysqlrouter --bootstrap root@localhost:3310 --user=mysql

這個時候還是要輸入密碼，成功之后，這個綁定就打通了。

Please enter MySQL password for root:
Bootstrapping system MySQL Router instance...
MySQL Router  has now been configured for the InnoDB cluster 'testCluster'.
The following connection information can be used to connect to the cluster.
Classic MySQL protocol connections to cluster 'testCluster':
- Read/Write Connections: localhost:6446
- Read/Only Connections: localhost:6447
X protocol connections to cluster 'testCluster':
- Read/Write Connections: localhost:64460
- Read/Only Connections: localhost:64470
可以從上面的日志看出來，分配的讀寫端口是6446，只讀端口是6447，還有x協議連接的端口為64460,64470
啟動MySQL Router
# mysqlrouter &
[1] 2913

如果對MySQL Router還有些疑問，可以看看安裝目錄下，會生成下面的配置文件，我們就看里面的.conf文件，里面的一部分內容如下：
[routing:testCluster_default_rw]
bind_address=0.0.0.0
bind_port=6446
destinations=metadata-cache://testCluster/default?role=PRIMARY
mode=read-write
protocol=classic

驗證測試

我們嘗試使用6446來連接登錄，這個時候就通過MySQL Shell開啟了連接入口，MySQL Router做了轉接，連接到了里面的讀寫節點3310

# mysqlsh --uri root@localhost:6446
Creating a Session to 'root@localhost:6446'
Enter password:
Classic Session successfully established. No default schema selected.
Welcome to MySQL Shell 1.0.9
切換到sql模式，查看端口就知道是哪個節點了。
mysql-js> \sql
Switching to SQL mode... Commands end with ;
mysql-sql> select @@port;
+--------+
| @@port |
+--------+
|   3310 |
+--------+
1 row in set (0.00 sec)
如果切換為腳本模式查看實例的狀態，可以使用里面定義的API來做，輸出都是JSON串。
mysql-js> dba.configureLocalInstance('root@127.0.0.1:3310')
Please provide the password for 'root@127.0.0.1:3310':
Detected as sandbox instance.
Validating MySQL configuration file at: /root/mysql-sandboxes/3310/my.cnf
Validating instance...
The instance '127.0.0.1:3310' is valid for Cluster usage
You can now use it in an InnoDB Cluster.
{
    "status": "ok"
}
如果查看Cluster的信息，可以看到下面的讀寫節點，只讀節點的狀態信息
mysql-js> dba.getCluster()
<Cluster:testCluster>

var cluster = dba.getCluster()

得到Cluster的信息

mysql-js> cluster.status()
{
    "clusterName": "testCluster",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "localhost:3310",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "localhost:3310": {
                "address": "localhost:3310",
                "mode": "R/W",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            },
            "localhost:3320": {
                "address": "localhost:3320",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            },
            "localhost:3330": {
                "address": "localhost:3330",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            }
        }
    }
}
也可以使用describe得到一些基本的信息
mysql-js> cluster.describe();
{
    "clusterName": "testCluster",
    "defaultReplicaSet": {
        "instances": [
            {
                "host": "localhost:3310",
                "label": "localhost:3310",
                "role": "HA"
            },
            {
                "host": "localhost:3320",
                "label": "localhost:3320",
                "role": "HA"
            },
            {
                "host": "localhost:3330",
                "label": "localhost:3330",
                "role": "HA"
            }
        ],
        "name": "default"
    }
}

切換測試

當然光看不練還是假把式，我們切換一下，看看好使不？

模擬一個節點出現問題，可以使用killSandboxInstance方法。
mysql-js> dba.killSandboxInstance(3310)
The MySQL sandbox instance on this host in
/root/mysql-sandboxes/3310 will be killed
Killing MySQL instance...
Instance localhost:3310 successfully killed.

節點被清理了，沒有任何進程存在。
# ps -ef|grep mysql|grep 3310
#
我們還是使用6446的端口來統一連接，這個時候就切換到了端口3320的MySQL服務
# mysqlsh --uri root@localhost:6446
Creating a Session to 'root@localhost:6446'
Enter password:
Classic Session successfully established. No default schema selected.
Welcome to MySQL Shell 1.0.9
mysql-js> \sql
Switching to SQL mode... Commands end with ;
mysql-sql> select @@port;
+--------+
| @@port |
+--------+
|   3320 |
+--------+
1 row in set (0.00 sec)

所以切換的部分沒有問題，我們再次把“迷失”的節點啟動起來。
# mysqlsh --uri root@localhost:6446
mysql-js> dba.startSandboxInstance(3310)
The MySQL sandbox instance on this host in
/root/mysql-sandboxes/3310 will be started
Starting MySQL instance...
Instance localhost:3310 successfully started.
這個時候再次查看Cluster的狀態，3320就是主了，3310就是只讀節點了。
mysql-js> dba.getCluster()
<Cluster:testCluster>

把節點2納入到Cluster中

mysql-js> cluster.rejoinInstance('root@localhost:3310')
Rejoining the instance to the InnoDB cluster. Depending on the original
problem that made the instance unavailable, the rejoin operation might not be
successful and further manual steps will be needed to fix the underlying
problem.
Please monitor the output of the rejoin operation and take necessary action if
the instance cannot rejoin.
Please provide the password for 'root@localhost:3310':
Rejoining instance to the cluster ...
The instance 'root@localhost:3310' was successfully rejoined on the cluster.
The instance 'localhost:3310' was successfully added to the MySQL Cluster.
mysql-js>

可以想象如果是一個生產系統，這么多的日志，這個過程真是讓人糾結。
最后來一個切換后的Cluster狀態
mysql-js> cluster.status()
{
    "clusterName": "testCluster",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "localhost:3320",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "localhost:3310": {
                "address": "localhost:3310",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            },
            "localhost:3320": {
                "address": "localhost:3320",
                "mode": "R/W",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            },
            "localhost:3330": {
                "address": "localhost:3330",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            }
        }
    }
}