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 --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"
            }
        }
    }
}