ProxySQL の読み取り/書き込みを構成から分離して使用する

使用目的は、Meituan-Dianping の DBProxy 読み取り/書き込み分離の使用方法の記事で説明されています。この記事では、ProxySQL の使用方法と DBProxy とのパフォーマンスの違いを紹介します。詳細な紹介については、公式 Web サイトの関連説明を参照してください。このミドルウェアは、percona が推奨するミドルウェアでもあります。機能は読み書きを分離する他のミドルウェアとあまり変わりません。詳細は記事内で紹介します。この記事では使用時の手順を簡単に紹介します。ヘルプについては公式 Wiki を参照してください。

環境:

ディストリビューターID: Ubuntu
説明: Ubuntu 14.04.5 LTS
リリース: 14.04コード名: trusty

ダウンロード

percona サイト:

https:// www.percona.com/downloads/proxysql /

github/公式 Web サイト:

https://github.com/sysown/proxysql/releases

まず環境を見てみましょう:

MHA が構築されました:

master：172.16.16.35:3306slave：172.16.16.35:3307slave：172.16.16.34:3307

MHA マネージャーは 172.16.16.34 にあり、設定ファイルは次のとおりです:

[root@localhost bin]# cat /etc/masterha/app1.cnf
[server default]
manager_log=/var/log/mha/app1/manager.log
manager_workdir=/var/log/mha/app1.log
master_binlog_dir=/home/mysql/db3306/log/master_ip_failover_script=/usr/local/bin/master_ip_failover
master_ip_online_change_script=/usr/local/bin/master_ip_online_change
password=123456ping_interval=1remote_workdir=/tmp
repl_password=123456repl_user=root
report_script=/usr/local/bin/send_report
shutdown_script=""ssh_user=root
user=root
[server1]
hostname=172.16.16.35port=3306[server2]
candidate_master=1check_repl_delay=0hostname=172.16.16.34port=3306[server3]
hostname=172.16.16.35port=3307

このような MHA 環境に基づいて読み取りと書き込みの分離を構築します。

1: ProxySQL ソフトウェアをインストールします。これを 172.16.16.34 にデプロイします。 以上でインストールは完了です。次に、特定のファイルを確認します:

[root@localhost bin]# sudo yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm[root@localhost bin]# yum install proxysql

ProxySQL が実際に正常にインストールされていることがわかりました

2: ProxySQL の構成を開始します

構成ファイルを確認します:

Installed:
proxysql.x86_64 0:1.3.7-1.1.el6
Complete!

スタートup :

[root@localhost bin]# find / -name proxysql/var/lib/proxysql/var/run/proxysql/etc/rc.d/init.d/proxysql/usr/bin/proxysql

ProxySQL のルーティングが開始されたので、proxysql サービスを開始するように求められます

[root@localhost bin]# cat /etc/proxysql-admin.cnf
# proxysql admin interface credentials.
export PROXYSQL_USERNAME="admin"export PROXYSQL_PASSWORD="admin"export PROXYSQL_HOSTNAME="localhost"export PROXYSQL_PORT="6032"
 # PXC admin credentials for connecting to pxc-cluster-node.
export CLUSTER_USERNAME="admin"export CLUSTER_PASSWORD="admin"export CLUSTER_HOSTNAME="localhost"export CLUSTER_PORT="3306"
 # proxysql monitoring user. proxysql admin script will create this user in pxc to monitor pxc-nodes.
export MONITOR_USERNAME="monitor"export MONITOR_PASSWORD="monit0r"
 # Application user to connect to pxc-node through proxysql
export CLUSTER_APP_USERNAME="proxysql_user"export CLUSTER_APP_PASSWORD="passw0rd"
 # ProxySQL read/write hostgroup
export WRITE_HOSTGROUP_ID="10"export READ_HOSTGROUP_ID="11"
 # ProxySQL read/write configuration mode.
export MODE="singlewrite"

正常にログインしたことがわかります。ここで説明したいのは proxysql です。デフォルトの設定ファイルは次のとおりです:

[root@localhost bin]# proxysql-admin --config-file=/etc/proxysql-admin.cnf --enable
This script will assist with configuring ProxySQL (currently only Percona XtraDB cluster in combination with ProxySQL is supported)
ProxySQL read/write configuration mode is singlewrite
ProxySQL is not running; please start the proxysql service

次に、ProxySQL の設定を開始します:

[root@localhost bin]# service proxy
proxy proxysql proxysql-admin proxysql_galera_checker proxysql_node_monitor
[root@localhost bin]# service proxysql start
Starting ProxySQL: DONE![root@localhost bin]# mysql -uadmin -padmin -h127.0.0.1 -P6032
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1Server version: 5.7.14 (ProxySQL Admin Module)
Copyright (c) 2000, 2016, 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>

以下のマスター/スレーブ情報を追加します。リーリー

次に、読み取りおよび書き込みマッピングを追加します (主に、MHA バックエンドが切り替えられたときに ProxySQL が自動的に切り替えられるようにするため):

[root@localhost bin]# find / -name proxysql.cnf/etc/proxysql.cnf

ProxySQL の監視アカウントを追加します:

[root@localhost bin]# mysql -uadmin -padmin -h127.0.0.1 -P6032
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2Server version: 5.7.14 (ProxySQL Admin Module)
 
Copyright (c) 2000, 2016, 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> show databases;+-----+---------+-------------------------------+
| seq | name | file |
+-----+---------+-------------------------------+
| 0 | main | |
| 2 | disk | /var/lib/proxysql/proxysql.db |
| 3 | stats | |
| 4 | monitor | |
+-----+---------+-------------------------------+4 rows in set (0.00 sec)
 
mysql> use admin
Database changed
mysql> show tables;+--------------------------------------+
| tables |
+--------------------------------------+
| global_variables |
| mysql_collations |
| mysql_query_rules |
| mysql_replication_hostgroups |
| mysql_servers |
| mysql_users |
| runtime_global_variables |
| runtime_mysql_query_rules |
| runtime_mysql_replication_hostgroups |
| runtime_mysql_servers |
| runtime_mysql_users |
| runtime_scheduler |
| scheduler |
+--------------------------------------+13 rows in set (0.00 sec)

監視アカウントを構成します (proxySQL で構成) 構成):

mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(100,'172.16.16.35',3306,1,1000,10,'test');
Query OK, 1 row affected (0.00 sec)
mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(101,'172.16.16.34',3306,1,1000,10,'test');
Query OK, 1 row affected (0.00 sec)
mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(101,'172.16.16.35',3307,1,1000,10,'test');
Query OK, 1 row affected (0.00 sec)
mysql> select * from mysql_servers;+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 100 | 172.16.16.35 | 3306 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | test |
| 101 | 172.16.16.34 | 3306 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | test |
| 101 | 172.16.16.35 | 3307 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | test |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+3 rows in set (0.00 sec)

PS: runtime_mysql_servers のステータスが ONLINE でない場合は、monitor.mysql_server_ping_log テーブルをチェックして特定のエラーを確認できる場合があります。情報。

mysql> select * frommonitor.mysql_server_ping_log;

次に、root: 123456 の最高権限を使用して設定します:

mysql> insert into mysql_replication_hostgroups values(100,101,'masterha') ;
Query OK, 1 row affected (0.00 sec)
mysql> select * from mysql_replication_hostgroups;+------------------+------------------+----------+
| writer_hostgroup | reader_hostgroup | comment |
+------------------+------------------+----------+
| 100 | 101 | masterha |
+------------------+------------------+----------+1 row in set (0.00 sec)

設定が完了したら、リロードを開始しますそして保存しています 設定:

mysql> GRANT SUPER, REPLICATION CLIENT ON *.* TO 'proxysql'@'172.16.16.%' IDENTIFIED BY 'proxysql';
Query OK, 0 rows affected, 1 warning (0.09 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.10 sec)

次に、ルーティングルールの設定を開始します:

mysql> set mysql-monitor_username='proxysql';
Query OK, 1 row affected (0.00 sec)
mysql> set mysql-monitor_password='proxysql';
Query OK, 1 row affected (0.00 sec)
mysql> load mysql variables to runtime;
Query OK, 0 rows affected (0.00 sec)
mysql> save mysql variables to disk;
Query OK, 74 rows affected (0.02 sec)

これまでの設定は完了です

3: 読み取りと書き込みの分離をテストします

で172 .16.16.35 proxySQL ポート 6033 に接続し、単純な選択操作を実行します:

mysql> insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent) values('root','123456',1,100,1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from mysql_users;+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| root | 123456 | 1 | 0 | 100 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+1 row in set (0.00 sec)

次に、それを確認してください:

mysql> load mysql servers to runtime;
Query OK, 0 rows affected (0.01 sec)
mysql> save mysql servers to disk;
Query OK, 0 rows affected (0.08 sec)
mysql> load mysql users to runtime;
Query OK, 0 rows affected (0.00 sec)
mysql> save mysql users to disk;
Query OK, 0 rows affected (0.03 sec)

読み取りと書き込みが分離されていることがわかります。完成しました。

mysql> INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT.*FOR UPDATE$',100,1);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT',101,1);
Query OK, 1 row affected (0.00 sec)
mysql> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
mysql> SAVE MYSQL QUERY RULES TO DISK;
Query OK, 0 rows affected (0.04 sec)

サーバー ID がスレーブ データベース 172.16.16.35:3307 にルーティングされていることを確認します。

更新のテスト:

[root@localhost ~]# mysql -uroot -p123456 -h172.16.16.34 -P6033
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 22Server version: 5.7.14 (ProxySQL)
Copyright (c) 2000, 2016, 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 maxiangqian.test;+-----+------+
| id | name |
+-----+------+
| 1 | qq |
| 2 | qq |
| 4 | aa |
| 11 | a |
| 111 | a |
+-----+------+5 rows in set (0.04 sec)

もう一度確認してください

mysql> select * from stats_mysql_query_digest;

はメインデータベースに自動的にルーティングされました。

これで、ProxySQL+MySQL MHA の読み取りと書き込みの分離テストが完了しました。 。 。続く

5: ProxySQLと簡単なコマンドについての考察

ProxySQL は上記の方法で読み書き分離を実現できますが、注文ステータスのクエリなど、非常に高いリアルタイム性が要求される SQL の場合は、実際にこの方法で問題なく送信されるようです。スレーブデータベースのバグが表示されます。 ProxySQL はロード バランサーとしてのみ使用され、1 つは読み取り/書き込み、もう 1 つは読み取り専用になります。次に、プログラムは読み取りと書き込みの分離を実装します。

ProxySQL は、RUNTIME、MEMORY、DISK の 3 つのレイヤーで設計および実行されます。

RUNTIME は、global_variables、mysql_servers、mysql_users、mysql_query_rules を含む、ProxySQL の現在有効な構成を表します。ここの設定は直接変更できません。次の層からロードする必要があります。

MEMORY は、通常、mysql コマンドラインで変更される main の設定です。メモリ内の SQLite データベースのイメージと考えることができます。

DISK / CONFIG FILE 永続ストレージの設定は、通常、$(DATADIR) にあります。 /proxysql.db は、再起動後にハードドライブからロードされます。 /etc/proxysql.cnf ファイルは最初の初期化時にのみ使用されます。その後、リスニング ポートを変更する場合は、管理コマンド ラインで変更してからハード ディスクに保存する必要があります。一般的なコマンド:

LOAD MYSQL SERVERS TO RUNTIME -- 変更した設定を有効にします。つまり、MEMORY からパラメータをロードします。これは、LOAD MYSQL USERS FROM MEMORY と同等です。このステートメントの構文は、LOAD を意味します。は上の層から、TOはこの層からある層へという意味です。たとえば、先ほど MySQL 監視アカウントを設定しましたが、変数を保存して有効にするために LOAD と SAVE を実行する必要があります。

以上がProxySQL の読み取り/書き込みを構成から分離して使用するの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。