MySQL Group Replication HAwith ProxySQL[プライマリノードのフェイルオーバーがアプリケーションに認識されない]

26103 ワード
mysql
1.はじめに
MySQL Group Replication(後にMGRと呼ぶ)GAバージョンは1ヶ月以上出ていますが、簡単なテストを経て、MGRがデータの最終的な一致性(注意、データの最終的な一致性、リアルタイム?できませんか、それとも一定の遅延があるか)を保証するのは信頼できます.前の文章のMGRの紹介を通じて、MGRは2つのモードがあって、単主と多主、現在の多主の制限に対して、およびテストの中で発見したいくつかの問題に対して、多主の実用性はまだ大きくないはずです.
実際には、MGRシングルマスターモードによって、従来のマスターがアーキテクチャからのフェイルオーバー後にデータが一致しない可能性があるという隠れた危険性を解消することができ、それ以外にもう一つの問題があります.HAは、どのようにMGRマスターノードのフェイルオーバーを自動的に行うかということです.
MGRシングルプライマリモードでは,プライマリノードが故障するとMGR内部で選挙が開始され,MGR内部で決定され実行される新しいプライマリが選択されることを知っている.しかし,MGRは周到に考慮されておらず,アプリケーションの接続がプライマリノードの切り欠きに遭遇した場合,自動的に切り替わることはない.すなわち、MGR内部には、プライマリノードのフェイルオーバがアプリケーションに対して感知されないことを実現するメカニズムが提供されていない.
MGR公式ドキュメントからの説明:
Quite obviously, regardless the mode Group Replication is deployed, it does not handle client-side fail-over. That must be handled by the application itself, connector or a middleware framework such as a proxy or router.
つまり、クライアントのフェイルオーバーを処理することはできません.これは私たちが自分を応用しなければなりません.あるいはミドルウェア、proxyなどのソフトウェアに頼っています!
実際のアプリケーションでは、プライマリノードが停止し、アプリケーションが再起動する必要がなく、自動的に接続を新しいプライマリにリセットし、サービスを継続することを望んでいます.この点を考慮して、Googleで関連資料を検索し、次のブログからヒントを得ました.
http://lefred.be/content/ha-with-mysql-group-replication-and-proxysql/
MySQLミドルウェアProySQLを使用して、上記の問題を解決できます.
2.目標
私たちの目標を簡単に説明します
MGRシングルマスターモードで、メインノードの故障切替を実現し、応用に対して感知がない
目標の実現には、ProxySQLというミドルウェアに依存する必要があります.
3.実現構想
前に述べたように、ProySQLで私たちの目標を実現することができます.文字はあまり言わないで、まず実現構想図をください.
アプリケーションはProxSQLミドルウェアを接続することによって、バックエンドMGRマスターノードに間接的にアクセスする.ProxySQLの内部に配置表があり、MGRノードのアクセス情報を維持し、スケジューラのスケジューラチェックスクリプト(Shellスクリプト)を開き、定期的にバックエンドMGRノードの状態を巡回し、MGRメインノードが掛けられていることを発見した場合、ProxySQLスケジューラスクリプトはこのエラーを検出し、新しいメインノードを確定し、元の持っていた古い接続を破棄する.新しいノードの接続を生成します(ProxySQL内部ではバックエンドMGRの各ノードの接続、マルチソース接続プールの概念を維持します).
上記のプロセス全体では、アプリケーションは変更する必要はありません.アプリケーションは、意識的に障害が発生したことから、接続が新しいプライマリに再指向され、通常はサービスが提供され、秒レベルの間隔です.
【重要】スクリプトの検証ロジックは、次の疑似コードで示されています.
set flag switchOver = false;
find current write node;
for( read node in readhostgroup) {
  isOk = check read node status; 
  if(read node is current write node) {
    if(!isOk) {
        // need to find new write node
        set flag switchOver = 1;
        update current read node status to be 'OFFLINE_SOFT';
        update current write node status to be 'OFFLINE_HARD';
    } else { 
        // is ok
        isPrimaryNode = check current write node is really the primary node;
        if(!isPrimaryNode) {
            // need to find new write node
            set flag switchOver = true;
            update current write node status to be 'OFFLINE_HARD';
            if(read node status != 'ONLINE') {
                update read node status to be 'ONLINE';
            }
            continue;
        }
        // is primary node
        if(read node status != 'ONLINE') {
            update current write node status to be 'ONLINE';
            update read node status to be 'ONLINE';
        }
    }
  } else if(!isOk) { // node is not current node and status is not ok
    update read node status to be 'OFFLINE_SOFT';
  } else if(isOk and read node status == 'OFFLINE_SOFT') {
    update read node status to be 'ONLINE';
  }
}

if(switchOver) {
    // need to find new write node
    successSwitchOver = false;
    for(read node in readgroup and status is 'ONLINE') {
        isNewPrimaryNode = check node is the new primary node;
        if(isNewPrimaryNode) {
            update current write node info as read node;
            successSwitchOver = true;
            break;
        }
    }
    if(!successSwitchOver) {
        // can not find the new write node
        report error msg;
    }
}

4.具体的な実施
次に、ProxySQLとMGRの共同作用を構成して、私たちの目標を達成する方法を紹介します.
4.1環境
  • Ubuntu 14.04(CentOS 6.5も配備済み)
  • MySQL 5.7.17
  • ProxySQL 1.3.1

    • 関連ソフトウェアのインストール配置はこの文書で考慮しない(MRGの構築を含む)
    4.2構成
    1台のマシン(マシンリソースが限られている)にMGR 3ノードクラスタを配備したと仮定します.モードは単一マスターモードです.
    mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 4a48f63a-d47b-11e6-a16f-a434d920fb4d | CrazyPig-PC |       24801 | ONLINE       |
| group_replication_applier | 592b4ea5-d47b-11e6-a3cd-a434d920fb4d | CrazyPig-PC |       24802 | ONLINE       |
| group_replication_applier | 6610aa92-d47b-11e6-a60e-a434d920fb4d | CrazyPig-PC |       24803 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)

    そして、このマシンにProxySQLをインストールして起動しました.次に、次の構成作業を行います.
    1)MGRクラスタは関連ユーザーを作成して許可する
    ProxysqlがMGRノードの状態を定期的にチェックし、バックエンドMGRエージェント層としてサービスを提供できるようにするには、proxysqlにMGRノードにログインする対応するユーザを作成し、許可する必要があります.
    MGRマスターノードで実行:
    grant all privileges on *.* to 'proxysql'@'%' identified by 'proxysql';
flush privileges;

    2)MGRノードの状態をチェックする関数とビューを作成する
    前のブログを参照して、MGRマスターノードで次のリンクのSQLを実行します.
    https://github.com/lefred/mysql_gr_routing_check/blob/master/addition_to_sys.sql
    3)proxysqlの構成
    MGRメンバーノードをproxysqlmysql_serversテーブルに追加:
    insert into mysql_servers (hostgroup_id, hostname, port) values(1, '127.0.0.1', 24801);
insert into mysql_servers (hostgroup_id, hostname, port) values(2, '127.0.0.1', 24801);
insert into mysql_servers (hostgroup_id, hostname, port) values(2, '127.0.0.1', 24802);
insert into mysql_servers (hostgroup_id, hostname, port) values(2, '127.0.0.1', 24803);
    hostgroup_id = 1writeグループを表し、我々が提案した制限に対して、この場所には1つのノードしか配置されていない.hostgroup_id = 2read groupを表し、MGRの全ノードを含む.
    proxysqlはまた、この特性の構成を考慮せずに、読み書き分離を構成することもできる.上記のhostgroup構成では、すべての読み書き操作がhostgroup_にデフォルトで送信されます.idが1のhostgroup,すなわち書き込みノードに送信される.
    次にproxysqlのモニタリングユーザとパスワードを変更して、上記のステップ1)で提供したユーザとパスワードを変更する必要があります.
    UPDATE global_variables SET variable_value='proxysql' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='proxysql' WHERE variable_name='mysql-monitor_password';

    さらに、proxysqlを介してバックエンドMGRノードにアクセスするユーザを追加する.
    insert into mysql_users(username, password) values('proxysql', 'proxysql');

    最後に、global_variables、mysql_servers、mysql_usersテーブルの情報をRUNTIMEにロードし、さらにDISKにロードする必要があります.
    LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

    4)schedulerの構成
    まず、Githubアドレスでhttps://github.com/ZzzCrazyPig/proxysql_groupreplication_checkerダウンロードgr_sw_mode_checker.sh次に、我々が提供するスクリプトgr_sw_mode_cheker.shディレクトリ/var/lib/proxysql/下
    最後に、proxysqlのschedulerテーブルに次のレコードをロードし、RUNTIMEにロードして有効にし、ディスクに永続化することもできます.
    insert into scheduler(id, active, interval_ms, filename, arg1, arg2, arg3, arg4)
  values(1, 1, 3000, '/var/lib/proxysql/gr_sw_mode_checker.sh', 1, 2, 1, '/var/lib/proxysql/checker.log');

LOAD SCHEDULER TO RUNTIME;
SAVE SCHEDULER TO DISK;
  • active:1:スクリプトスケジュールを有効にすることを示す
  • interval_ms:どれくらいの時間ごとにスクリプトを実行するか(eg:3000(ms)=3 sは3 sごとにスクリプトが呼び出されることを示す)
  • filename:スクリプトが存在する具体的なパスを指定し、上の/var/lib/proxysql/checker.log
  • arg 1~arg 4:スクリプトに渡すパラメータを指定
    • スクリプトおよび対応するパラメータの説明は次のとおりです.
    gr_sw_mode_cheker.sh writehostgroup_id readhostgroup_id [writeNodeCanRead] [log file]
  • arg 1->指定writehostgroup_id
  • arg 2->指定readhostgroup_id
  • arg 3->ライトノードが読み取りに使えるかどうか、1(YES,the default value)、0(NO)
  • arg4 -> log file, default: './checker.log'

    • はい、ここまで来たら大成功です.
    5.テスト
    私はJavaをやっているので、Javaプログラムを書いてJDBCで接続し、ProxySQL(クライアントが接続すべきのはProxySQL)に接続し、実行select @@port現在接続されているのはバックエンドMGRのどのノードかを確認し、ホストが掛けられた状況を手動でシミュレートし、現象を観察したいと思っています.コードは次のとおりです.
    import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestMgrHAWithProxysql {

    private static final String JDBC_URL = "jdbc:mysql://10.202.7.88:6033/test";
    private static final String USER = "proxysql";
    private static final String PASSWORD = "proxysql";

    public static void main(String[] args) {

        tryAgain();
    }

    private static void tryAgain() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(JDBC_URL, USER, PASSWORD);
            conn.setAutoCommit(false);
            String sql = "select @@port";
            Statement stmt = conn.createStatement();
            while(true) {
                ResultSet rs = stmt.executeQuery(sql);
                if(rs.next()) {
                    System.out.println("port : " + rs.getString(1));
                }
                try {
                    Thread.sleep(500);
                } catch (InterruptedException e) {
                    e.printStackTrace();
                }
            }
        } catch(SQLException e) {
            e.printStackTrace();
            tryAgain();
        } finally {
            if(conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

}

    初期MGRマスターノードが存在するポートは24802であるため、プログラムは常に出力される.
    port : 24802
...
...
...

    プログラムは常に実行され、マスターノードが削除された場合をシミュレートします.
    mysql> stop group_replication;
Query OK, 0 rows affected (8.34 sec)

    Javaプログラムが異常を出力し、出力を続けます.
    com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 567 milliseconds ago.  The last packet sent successfully to the server was 66 milliseconds ago.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:408)
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1137)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3715)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3604)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4149)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2834)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2783)
    at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1569)
    at TestMgrHAWithProxysql.tryAgain(TestMgrHAWithProxysql.java:26)
    at TestMgrHAWithProxysql.main(TestMgrHAWithProxysql.java:15)
Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
    at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3161)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3615)
    ... 9 more

port : 24803
...
...

    このとき、マスターは24803ポート、すなわち3番目のノードに切り替えられます.前に削除した24802ポートノードを再確認します.
    mysql> start group_replication;
Query OK, 0 rows affected (2.64 sec)

    その後、24803ポートノードにログインし、次のことを実行します.
    mysql> stop group_replication;
Query OK, 0 rows affected (8.15 sec)

    メインは24802に切り替わります.
    com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 547 milliseconds ago.  The last packet sent successfully to the server was 47 milliseconds ago.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:408)
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1137)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3715)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3604)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4149)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2834)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2783)
    at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1569)
    at TestMgrHAWithProxysql.tryAgain(TestMgrHAWithProxysql.java:26)
    at TestMgrHAWithProxysql.main(TestMgrHAWithProxysql.java:15)
Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
    at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3161)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3615)
    ... 9 more
port : 24802
...
...

    今回は24803を再加入するのではなく、再びstop 24802を行い、MGR全体に1つのノードしかなく、24801ポートを選択するかどうかを見てみましょう.答えは肯定的です.
    com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 542 milliseconds ago.  The last packet sent successfully to the server was 41 milliseconds ago.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:408)
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1137)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3715)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3604)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4149)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2834)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2783)
    at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1569)
    at TestMgrHAWithProxysql.tryAgain(TestMgrHAWithProxysql.java:26)
    at TestMgrHAWithProxysql.main(TestMgrHAWithProxysql.java:15)
Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
    at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3161)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3615)
    ... 9 more
port : 24801
...
...

    もちろん、この時点で24801ポートも終了すれば、遊ぶ必要はありません!
    6.ProxySQL導入の影響
    このセットの方案は中間部品を導入することによって、MGR単主モードの下で主に故障の切替が発生することを解決して、応用に対して感知がありません.次の影響があります.
  • ミドルウェアproxysqlを導入し、マシンリソースを増やすとともに運用の難易度と複雑さを増す
  • proxysqlは単点が存在し、高可用性を実現するには、より多くのことを考慮する必要がある(HAProxy?KeepAlived?)

    • 7.参照リソース
    7.1 ProxySQL
  • For DBA-MySQLミドルウェアのProxySQLインストール配置編
  • For DBA-MySQLミドルウェアのProxySQL_構成システム
  • For DBA-MySQLミドルウェアのProxySQL_読み書き分離/クエリー書き換え構成
  • ProxySQL公式サイト
  • ProxySQL Github(Wikiとdocディレクトリの下のドキュメントを参照)
    • 7.2 MGR HA
  • lefred’ Blog - MGR HA with HAProxy
  • lefred’ Blog - MGR HA with ProxySQL

    • 7.3本文の関連
  • My Github - MGR HA with ProxySQL
    • プロセス間通信-システム呼び出しsetjmp()とlongjmp()
    最大サブセグメント和(二)