DB Tech Showcase 2018 Tokyo

DB Tech Showcase 2018 TokyoにてInnoDB Clusterを用いた、MySQLの高可用性構成への取り組みに関して紹介させて頂きました。プロダクションのデータベースなので、これまでの機能紹介やシンプルなデモとは異なり、様々な確認や調整が必要で、DBのアップグレード以上に時間を割いている感じです。移行後は、シンプルにマスターを切り替える事で、MySQL Routerが自動的に接続を切り替えてくれるので非常に楽になります。
また、作業工数も大幅に削減出来るので、会社にとってもダウンタイムによる機会損失削減といったメリットだけでなく、エンジニアの工数削減によりサービス開発に専念が出来るようになり非常に良いソリューションだと考えています。

これからもMySQLだけでは無く、様々な高可用性構成をコストと運用の観点から考察して、順次導入していきたいと思っています。
システム移行と同時に様々な、改善も並行して出来るので1年を目途に最適化が出来ると良いかなと思ってます。

“MySQL5.6から5.7へ、そして更なるサービスの可用性を目指して ~急成長する靴の通販 LOCONDO.jp を支えるサービス安定化への取り組み”

補足:
資料には明記してませんが、シングルプライマリーモードで構成しているので、グループレプリケーション構成直後に以下の値を全て1に変更しています。


group_replication_auto_increment_increment 
auto_increment_increment
auto_increment_offset

Group Replicationのマスター、スレーブ間のレプリケーションラグの確認方法

select @last_exec:=SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX( @@global.GTID_EXECUTED,':',-1),':',1),'-',-1) last_executed;select @last_rec:=SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX( Received_transaction_set,':',-1),':',1),'-',-1) last_received FROM performance_schema.replication_connection_status WHERE Channel_name = 'group_replication_applier';select (@last_rec - @last_exec) as real_lag;

補足:RECOVERY中は値が正確に取得出来ない様子
https://bugs.mysql.com/bug.php?id=92219


MySQL用のコネクター、Connector/Jの動作を改めて確認してみました。
検証した内容としては、jdbc:mysql、jdbc:mysql:replication、jdbc:mysql+ReplicationDriver, jdbc:mysql:loadbalanceの接続方法による挙動の違い。

検証環境:
Connector/J: mysql-connector-java-community-5.1.36-bin.jar
MySQL環境:  mysql 5.7.21 でグループレプリケーション(シングルマスターモード)


-bash-4.2$ ./gr_mysql_status.sh
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| group_replication_applier | 1d69db5d-a273-11e8-b673-080027d65c57 | replications | 3310 | ONLINE |
| group_replication_applier | 271d12be-a273-11e8-bc0a-080027d65c57 | replications | 3320 | ONLINE |
| group_replication_applier | 2e395847-a273-11e8-866f-080027d65c57 | replications | 3330 | ONLINE |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
-bash-4.2$

オフィシャルマニュアルには以下のように書いてあるけど、十分に検証しないといけないので少し落ち着いたタイミングにならないとアップグレードは少々躊躇する。
変更点を一つ一つ確認していくのは難しいので、にバージョン毎の差を明確に、分かりやすく書いてあるマニュアルがあると有難い。
現状でも、5.1.17~5.1.47までリリースがある。

MySQL Connector / J 8.0は、MySQL Server 8.0,5.7,5.6、および5.5での使用を強く推奨します。 MySQL Connector / J 8.0にアップグレードしてください。

Connector / J 5.1は、JDBC 3.0,4.0,4.1、および4.2仕様に準拠したタイプ4の純粋なJava JDBCドライバーです。 5.5,5.6、および5.7を含むMySQLのすべての機能との互換性を提供します。 Connector / J 5.1は、Driver Managerによる自動登録、標準化された妥当性チェック、分類されたSQLExceptions、大規模な更新回数のサポート、java.timeパッケージからのローカルおよびオフセットの日時のサポートのサポート、 JDBC-4.x XML処理、接続ごとのクライアント情報のサポート、NCHAR、NVARCHARおよびNCLOBデータ型のサポートします。

Connector / J 8.0は、Java 8プラットフォーム用のタイプ4の純粋なJava JDBC 4.2ドライバーです。 これは、MySQL 5.5,5.6,5.7,8.0のすべての機能との互換性を提供します。

Connector / Jのバージョンの概要

*いくつかの暗号スイートを使用する場合、Connector / J 5.1がMySQL 5.6,5.7、および8.0にSSL / TLSで接続するためには、JRE 1.8.xが必要です。
*Connector / Jの現在の推奨バージョンは5.1です。 このガイドでは、以前のバージョンのConnector / Jについても説明します。具体的な注釈は、設定が特定のバージョンに適用される場合に指定します。

Connector / Jに必要なJavaバージョンの要約

Pattern(1): Standard Connector/J (jdbc:mysql)

8.1 Configuring Server Failover


[root@GA01 java]# cat conn_j_standard.java
import java.sql.*;
import java.util.Properties;
import com.mysql.jdbc.ReplicationDriver;

class conn_j_standard{
public static void main (String args[])
throws SQLException, ClassNotFoundException{
Connection conn = null;

try {
for(int i=0; i < 10000; i++){
try{
Thread.sleep(500);
} catch (Exception ex) {
// handle any errors
System.out.println(ex);
}

String strSQLNode1 = "192.168.56.111:3310";
String strSQLNode2 = "192.168.56.111:3320";
String strSQLNode3 = "192.168.56.111:3330";
String strDatabase = "";
conn = DriverManager.getConnection("jdbc:mysql://" + strSQLNode1 + "," +strSQLNode2 +',' + strSQLNode3 + "/" +
strDatabase+"demodb?" +
"user=router_user" +
"&password=password" +
"&autoReconnect=true" +
"&autoReconnectForPools=true"+
"&failOverReadOnly=true"+
"&roundRobinLoadBalance=true"+
"&connectTimeout=30000"+
"&socketTimeout=30000"+
"&maxReconnects=1"+
"&initialTimeout=1"+
"&loadBalanceBlacklistTimeout=3600000"+
"&loadBalancePingTimeout=100"
);
//conn.setReadOnly(true);
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery("select @@hostname,@@port from dual");
while ( rset.next() ) {
System.out.println(i + "\t" + rset.getString(1) + "\t" + rset.getString(2));
}
rset.close();
stmt.close();
conn.close();
}
} catch (SQLException ex) {
// handle any errors
System.out.println(ex);
}
}
}
[root@GA01 java]#

コンパイルして実行してみます。 基本的には、常時左から順に利用可能なサーバーにアクセスして処理します。 但し、アクセスしているサーバーに障害が発生した場合は、例2のようにフェールオーバーする事が可能です。


[root@GA01 java]# ls -l conn_j_standard.*
-rw-r--r--. 1 root root 2275 8月 18 08:26 conn_j_standard.class
-rw-r--r--. 1 root root 2443 8月 18 08:26 conn_j_standard.java
[root@GA01 java]#

[root@GA01 java]# java testmysql
0 replications 3310
1 replications 3310
2 replications 3310
3 replications 3310
4 replications 3310
5 replications 3310
6 replications 3310
7 replications 3310
8 replications 3310
9 replications 3310
10 replications 3310

例2)10回目の接続時に最初にアクセスしたサーバーをダウンさせたらフェールオーバーします。一番、左側に記載したサーバーが利用可能になると、一番左側のサーバーにアクセスします。(オプション設定)

[root@GA01 java]# java testmysql
0 replications 3310
1 replications 3310
2 replications 3310
3 replications 3310
4 replications 3310
5 replications 3310
6 replications 3310
7 replications 3310
8 replications 3310
9 replications 3310
10 replications 3320
11 replications 3320
12 replications 3320
13 replications 3320
14 replications 3320
15 replications 3320

Pattern(2): Connector/J Replication接続 (jdbc:mysql:replication)

8.3 Configuring Master/Slave Replication with Connector/J


[root@GA01 java]# cat conn_j_replication.java
import java.sql.*;
import java.util.Properties;
import com.mysql.jdbc.ReplicationDriver;

class conn_j_replication {
public static void main (String args[])
throws SQLException, ClassNotFoundException{
Connection conn = null;

try {
for(int i=0; i < 10000; i++){
try{
Thread.sleep(500);
} catch (Exception ex) {
// handle any errors
System.out.println(ex);
}

String strSQLNode1 = "192.168.56.111:3310";
String strSQLNode2 = "192.168.56.111:3320";
String strSQLNode3 = "192.168.56.111:3330";
String strDatabase = "";

conn = DriverManager.getConnection("jdbc:mysql:replication://" + strSQLNode1 + "," +strSQLNode2 + "," + strSQLNode3 + "/"+
strDatabase+"demodb?" +
"user=router_user" +
"&password=password" +
"&autoReconnect=true" +
"&autoReconnectForPools=true"+
"&failOverReadOnly=true"+
"&roundRobinLoadBalance=true"+
"&initialTimeout=1"
);

conn.setReadOnly(true);
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery("select @@hostname,@@port from dual");
while ( rset.next() ) {
System.out.println(i + "\t" + rset.getString(1) + "\t" + rset.getString(2));
}
rset.close();
stmt.close();
conn.close();
}
} catch (SQLException ex) {
// handle any errors
System.out.println(ex);
}
}
}
[root@GA01 java]#

コンパイルして実行してみます。基本的に, 以下の設定でマスターへの接続かスレーブへの接続か判断して処理します。

conn.setReadOnly(false) = マスター接続(特に記載なければマスター接続)
conn.setReadOnly(true)  = スレーブ


[root@GA01 java]# javac conn_j_replication.java
[root@GA01 java]# java conn_j_replication
0 replications 3320
1 replications 3330
2 replications 3330
3 replications 3330
4 replications 3330
5 replications 3330
6 replications 3320
7 replications 3330
8 replications 3330
9 replications 3320
10 replications 3320
11 replications 3320
12 replications 3320
13 replications 3320
14 replications 3320
15 replications 3330
[root@GA01 java]#

Pattern(3): Connector/J Replication接続② (jdbc:mysql + 明示的にレプリケーションドライバーを指定)
マニュアルには具体的な内容は発見出来ていませんが以下のようにもコメントされています。現状では上記のようにjdbc:mysql:Replicationでコーディングする方が主流なのかと。

Deprecate宣言

com.mysql.jdbc.[NonRegistering]Driver now understands URLs of the format jdbc:mysql:replication:// and jdbc:mysql:loadbalance:// which will create a ReplicationConnection (exactly like when using [NonRegistering]ReplicationDriver) and an experimental load-balanced connection designed for use with SQL nodes in a MySQL Cluster/NDB environment, respectively.

In an effort to simplify things, we’re working on deprecating multiple drivers, and instead specifying different core behavior based upon JDBC URL prefixes, so watch for [NonRegistering]ReplicationDriver to eventually disappear, to be replaced with com.mysql.jdbc[NonRegistering]Driver with the new URL prefix.

https://dev.mysql.com/doc/relnotes/connector-j/5.1/en/news-5-0-6.html

Remove宣言

4.3.1.6 Other Changes
Here are other changes with Connector/J 8.0:

Removed ReplicationDriver. Instead of using a separate driver, you can now obtain a connection for a replication setup just by using the jdbc:mysql:replication:// scheme.

https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-other-changes.html

If you have a write transaction, or if you have a read that is time-sensitive (remember, replication in MySQL is asynchronous), set the connection to be not read-only, by calling Connection.setReadOnly(false) and the driver will ensure that further calls are sent to the master MySQL server. The driver takes care of propagating the current state of autocommit, isolation level, and catalog between all of the connections that it uses to accomplish this load balancing functionality.

To enable this functionality, use the com.mysql.jdbc.ReplicationDriver class when configuring your application server’s connection pool or when creating an instance of a JDBC driver for your standalone application. Because it accepts the same URL format as the standard MySQL JDBC driver, ReplicationDriver does not currently work with java.sql.DriverManager-based connection creation unless it is the only MySQL JDBC driver registered with the DriverManager .

書き込みトランザクションがある場合や、読み込み時間が重要な場合(MySQLのレプリケーションが非同期であることを覚えておいてください)、Connection.setReadOnly(false)を呼び出して接続を読み取り専用に設定すると、ドライバは それ以降のコールがマスターMySQLサーバーに送信されるようにしてください。 ドライバは、このロードバランシング機能を達成するために使用するすべての接続間で、自動コミット、分離レベル、およびカタログの現在の状態を伝達します。

この機能を有効にするには、アプリケーション・サーバーの接続プールを構成するとき、またはスタンドアロン・アプリケーション用のJDBCドライバのインスタンスを作成するときにcom.mysql.jdbc.ReplicationDriverクラスを使用します。 標準のMySQL JDBCドライバと同じURLフォーマットを受け入れるため、DriverManagerに登録されている唯一のMySQL JDBCドライバでない限り、ReplicationDriverは現在java.sql.DriverManagerベースの接続作成では動作しません。


[root@GA01 java]# cat conn_j_standard2.java
import java.sql.*;
import java.util.Properties;
import com.mysql.jdbc.ReplicationDriver;

class conn_j_standard2 {
public static void main (String args[])
throws SQLException, ClassNotFoundException{
ReplicationDriver driver = new ReplicationDriver();

try {
for(int i=0; i < 100; i++){
try{
Thread.sleep(500);
} catch (Exception ex) {
// handle any errors
System.out.println(ex);
}

Properties props = new Properties();
props.put("autoReconnect", "true");
props.put("roundRobinLoadBalance","true");
props.put("failOverReadOnly","true");
props.put("user","router_user");
props.put("password","password");

Connection conn =
driver.connect(
"jdbc:mysql://192.168.56.111:3310,192.168.56.111:3320,192.168.56.111:3330/demodb",props);

conn.setReadOnly(true);
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery("select @@hostname,@@port from dual");
while ( rset.next() ) {
System.out.println(i + "\t" + rset.getString(1) + "\t" + rset.getString(2));
}
rset.close();
stmt.close();
conn.close();
}
} catch (SQLException ex) {
// handle any errors
System.out.println(ex);
}
}
}
[root@GA01 java]#

jdbc:mysql:Replicationと同じく、conn.setReadOnly(false)はマスターにconn.setReadOnly(true)はスレーブに参照しに行きます。
上記の例だとスレーブ間をラウンドロビンします。

[root@GA01 java]javac conn_j_standard2.java
[root@GA01 java]# java conn_j_standard2
0 replications 3320
1 replications 3330
2 replications 3330
3 replications 3330
4 replications 3330
5 replications 3330
6 replications 3330
7 replications 3320
8 replications 3330
9 replications 3320
10 replications 3320
11 replications 3320
12 replications 3330
13 replications 3320
14 replications 3320
15 replications 3330

Pattern(4): Connector/J Load Balance処理用の接続 (jdbc:mysql:loadbalance)

8.2 Configuring Load Balancing with Connector/J


[root@GA01 java]# cat conn_j_loadbalance.java
import java.sql.*;
import java.util.Properties;
import com.mysql.jdbc.ReplicationDriver;

class conn_j_loadbalance {
public static void main (String args[])
throws SQLException, ClassNotFoundException{
Connection conn = null;

try {
for(int i=0; i < 10000; i++){
try{
Thread.sleep(500);
} catch (Exception ex) {
// handle any errors
System.out.println(ex);
}

String strSQLNode1 = "192.168.56.111:3310";
String strSQLNode2 = "192.168.56.111:3320";
String strSQLNode3 = "192.168.56.111:3330";
String strDatabase = "";
conn = DriverManager.getConnection("jdbc:mysql:loadbalance://" + strSQLNode1 + "," + strSQLNode2 + "," + strSQLNode3 + "/"+
strDatabase+"demodb?" +
"user=router_user" +
"&password=password" +
"&loadBalanceConnectionGroup=first" +
"&loadBalanceEnableJMX=true"
);
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery("select @@hostname,@@port from dual");
while ( rset.next() ) {
System.out.println(i + "\t" + rset.getString(1) + "\t" + rset.getString(2));
}
rset.close();
stmt.close();
conn.close();
}
} catch (SQLException ex) {
// handle any errors
System.out.println(ex);
}
}
}

[root@GA01 java]#

以下のように、全てのサーバーにロードバランスされるのでマルチマスター環境で利用可能です。MySQL NDB Cluster, MySQL InnoDB Cluster (Multi Master設定)の構成で便利です。
但し、InnoDB Clusterの場合は制限事項があるので、バッチ処理等と同じデータベースと利用する場合は十分に検証が必要。


[root@GA01 java]# javac conn_j_loadbalance.java
[root@GA01 java]# ls -l conn_j_loadbalance.*
-rw-r--r--. 1 root root 1992 8月 18 09:03 conn_j_loadbalance.class
-rw-r--r--. 1 root root 1890 8月 18 09:04 conn_j_loadbalance.java
[root@GA01 java]#

[root@GA01 java]# java conn_j_loadbalance
0 replications 3330
1 replications 3330
2 replications 3320
3 replications 3310
4 replications 3310
5 replications 3330
6 replications 3310
7 replications 3330
8 replications 3330
9 replications 3330
10 replications 3320
11 replications 3320
12 replications 3330
13 replications 3330
14 replications 3320
15 replications 3310

参考:
MySQL Connector/J 5.1 Developer Guide
Chapter 10 Using Connector/J with Tomcat
Chapter 12 Using Connector/J with Spring


DB Tech Showcase 2017にてMySQL InnoDB Clusterの概要説明とデモをさせて頂きました。
プレゼン資料をスライドシェアにアップロードしたので、2017年4月にリリースされたMySQL標準の高可用性構成に興味あるある方、若しくは高可用性構成を検討の方はご覧ください。

余談ですが、WP Social Bookmarking Lightプラグインをアップグレードしたら古いPHPで動かなかったので、PHP5.6のバージョンにアップグレードしました。もし、WordPressのアップグレードのタイミングでwp-social-bookmarking-light.phpがエラーになったら、PHPを5.5以上にアップグレードしてみて下さい。