MySQLは読み書き分離を実行するミドルウェア――ProxySQL
13237 ワード
上にmysqlの主従モデルを紹介し、データベースサーバの性能を向上させ、データの完全性と信頼性を保証することができますが、それだけといえば、masterは依然として大きなアクセスと書き込み操作の圧力を負い、新しいボトルネックに昇格します.これにより、読み書き分離が可能なMySQLのミドルウェアであるProxySQLを紹介し、主従モデルに依存して、ユーザーのアクセス操作をslaveに送信することができ、書き込み操作をmasterに送信することができ、このように私たちのmasterの圧力は一定の向上を得ることができる.
ProxySQLはC++に基づいて開発された高性能軽量級製品で、強力なmysqlのミドルウェアであり、彼は多種の方式の読み書き分離を実現することができる.
Master IP:172.16.75.4 CentOS 7.5D server_id:401
Slave IP:172.16.75.3 CentOS 7.5C server_id:301
1.まず、yumをインストールしてから起動し、起動ポートは6032(私のproxysqlはmasterにインストールされています).
2.mysqlクライアントツールを使用してproxysqlにログインし、ユーザー名とパスワードはadminで、ポートは6032で、デフォルトではlocalhostログインは許可されていないので、127.0.0.1 IPアドレスでログインします.
3.主にmianライブラリとmonitorライブラリを紹介する.
4.mysql_でserversテーブルにはいくつかの重要なプロパティがあります.
hostgroup_id:masterとslaveを区別するためのグループID;
hostname:バックエンドmasterとslaveのIPアドレス;
port:バックエンドmasterとslaveのIPポート、デフォルト3306;
注意:
1.設定したマスターのhostgroup_idは10(書き込みグループ);
2.設定したslaveのhostgroup_idは20(リードグループ)である.
3.proxysqlの操作が完了するたびに、メモリに手動でロードし、ディスクに手動で保存します.テーブル名の「_」スペースに変更mysql_servers à mysql servers
5.マスターにバックエンドのノードを監視するための監視ユーザーを許可する(注意:マスターのmysqlであり、Proxysqlではない).
このユーザーに必要な権限:replication clientとreplication slave
6.proxysqlにノードを追加する.
テーブルmysql_を表示するにはserver_ping_logバックエンドサーバのステータス情報を理解する;
7.バックエンド定義のサーバのパケットに対するリードグループとライトグループの設定、mysql_replication_hostgroupsテーブルに定義を追加するには、monitorライブラリのmysql_を表示します.server_read_only_logテーブルバックエンドノードにread_があるかどうかを確認only権限;
8.これで、基本的な構成が完了し、バックエンドのmasterに2つのユーザーアカウントを作成し、proxysqlに異なるhostgroup_を追加します.idは、異なるユーザ間で読み書き分離を完了する.
Master mysql:
Proxysql:
8.任意の1台のホストは、ユーザーの読み書き分離メカニズムに基づいてテストする.
9.SQL文に基づいて読み書き分離を実現する;
mysql_でquery_rulesテーブルに2つの正規表現を追加するルール.
10.任意のホストテストはSQL文の読み書き分離のメカニズムに基づいている.
Masterのhellodbデータベースを表示します.
データベース・サーバにとって、クエリーの効率的な速度を保証するとともに、俗っぽくない書き込み操作を保証する必要があります.そのため、読み書き分離はデータベースの性能を向上させる上で特に重要です.これはユーザーの体験に関係しています.そのため、MySQLのミドルウェアであるProxySQLは、生産環境の中でそんなに少なくないように見えます.結局、体験を向上させる良いものです.
ProxySQLはC++に基づいて開発された高性能軽量級製品で、強力なmysqlのミドルウェアであり、彼は多種の方式の読み書き分離を実現することができる.
Master IP:172.16.75.4 CentOS 7.5D server_id:401
Slave IP:172.16.75.3 CentOS 7.5C server_id:301
1.まず、yumをインストールしてから起動し、起動ポートは6032(私のproxysqlはmasterにインストールされています).
[root@slave2 ~]# ss -tnl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 50 *:3306 *:*
LISTEN 0 128 *:111 *:*
LISTEN 0 128 *:6032 *:*
LISTEN 0 128 *:6033 *:*
LISTEN 0 128 *:6033 *:*
LISTEN 0 128 *:6033 *:*
LISTEN 0 128 *:6033 *:*2.mysqlクライアントツールを使用してproxysqlにログインし、ユーザー名とパスワードはadminで、ポートは6032で、デフォルトではlocalhostログインは許可されていないので、127.0.0.1 IPアドレスでログインします.
[root@slave2 ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.30 (ProxySQL Admin Module)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> show databases;
+-----+---------------+-------------------------------------+
| seq | name | file |
+-----+---------------+-------------------------------------+
| 0 | main | |
| 2 | disk | /var/lib/proxysql/proxysql.db |
| 3 | stats | |
| 4 | monitor | |
| 5 | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
5 rows in set (0.00 sec)3.主にmianライブラリとmonitorライブラリを紹介する.
MySQL [(none)]> show tables from main;
+--------------------------------------------+
| tables |
+--------------------------------------------+
| global_variables |
| mysql_collations |
| mysql_group_replication_hostgroups |
| mysql_query_rules |
| mysql_query_rules_fast_routing |
| mysql_replication_hostgroups |
| mysql_servers | ，master slave
| mysql_users |
| proxysql_servers |
| runtime_checksums_values |
| runtime_global_variables |
| runtime_mysql_group_replication_hostgroups |
| runtime_mysql_query_rules |
| runtime_mysql_query_rules_fast_routing |
| runtime_mysql_replication_hostgroups |
| runtime_mysql_servers |
| runtime_mysql_users |
| runtime_proxysql_servers |
| runtime_scheduler |
| scheduler |
+--------------------------------------------+
20 rows in set (0.00 sec)4.mysql_でserversテーブルにはいくつかの重要なプロパティがあります.
hostgroup_id:masterとslaveを区別するためのグループID;
hostname:バックエンドmasterとslaveのIPアドレス;
port:バックエンドmasterとslaveのIPポート、デフォルト3306;
MySQL [main]> insert into mysql_servers (hostgroup_id,hostname,port) values (10,'172.16.75.4',3306),(20,'172.16.75.3',3306);
Query OK, 2 rows affected (0.02 sec)
MySQL [main]> load mysql servers to runtime;
Query OK, 0 rows affected (0.01 sec)
MySQL [main]> save mysql servers to disk;
Query OK, 0 rows affected (0.06 sec)注意:
1.設定したマスターのhostgroup_idは10(書き込みグループ);
2.設定したslaveのhostgroup_idは20(リードグループ)である.
3.proxysqlの操作が完了するたびに、メモリに手動でロードし、ディスクに手動で保存します.テーブル名の「_」スペースに変更mysql_servers à mysql servers
5.マスターにバックエンドのノードを監視するための監視ユーザーを許可する(注意:マスターのmysqlであり、Proxysqlではない).
このユーザーに必要な権限:replication clientとreplication slave
MariaDB [(none)]> grant replication client,replication slave on *.* to 'monitor'@'%' identified by '123456';
Query OK, 0 rows affected (0.02 sec)6.proxysqlにノードを追加する.
MySQL [main]> set mysql-monitor_username='monitor';
Query OK, 1 row affected (0.00 sec)
MySQL [main]> set mysql-monitor_password='123456';
Query OK, 1 row affected (0.00 sec)
MySQL [main]> load mysql variables to runtime;
Query OK, 0 rows affected (0.00 sec)
MySQL [main]> save mysql variables to disk;
Query OK, 95 rows affected (0.03 sec)テーブルmysql_を表示するにはserver_ping_logバックエンドサーバのステータス情報を理解する;
MySQL [main]> select * from mysql_server_ping_log limit 1,10;
+-------------+------+------------------+----------------------+----------------------------------------------------------------------+
| hostname | port | time_start_us | ping_success_time_us | ping_error |
+-------------+------+------------------+----------------------+----------------------------------------------------------------------+
| 172.16.75.4 | 3306 | 1541505676594192 | 0 | Access denied for user 'monitor'@'172.16.75.4' (using password: YES) |
| 172.16.75.3 | 3306 | 1541505686592082 | 0 | Host '172.16.75.4' is not allowed to connect to this MariaDB server |
| 172.16.75.4 | 3306 | 1541505686594872 | 0 | Access denied for user 'monitor'@'172.16.75.4' (using password: YES) |
| 172.16.75.3 | 3306 | 1541505696592635 | 0 | Host '172.16.75.4' is not allowed to connect to this MariaDB server |
| 172.16.75.4 | 3306 | 1541505696595442 | 0 | Access denied for user 'monitor'@'172.16.75.4' (using password: YES) |
| 172.16.75.3 | 3306 | 1541505706593101 | 0 | Host '172.16.75.4' is not allowed to connect to this MariaDB server |
| 172.16.75.4 | 3306 | 1541505706596427 | 0 | Access denied for user 'monitor'@'172.16.75.4' (using password: YES) |
| 172.16.75.3 | 3306 | 1541505716593471 | 0 | Host '172.16.75.4' is not allowed to connect to this MariaDB server |
| 172.16.75.4 | 3306 | 1541505716596416 | 0 | Access denied for user 'monitor'@'172.16.75.4' (using password: YES) |
| 172.16.75.3 | 3306 | 1541505726593810 | 0 | Host '172.16.75.4' is not allowed to connect to this MariaDB server |
+-------------+------+------------------+----------------------+----------------------------------------------------------------------+
10 rows in set (0.00 sec)7.バックエンド定義のサーバのパケットに対するリードグループとライトグループの設定、mysql_replication_hostgroupsテーブルに定義を追加するには、monitorライブラリのmysql_を表示します.server_read_only_logテーブルバックエンドノードにread_があるかどうかを確認only権限;
MySQL [main]> insert into mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup) values (10,20);
Query OK, 1 row affected (0.00 sec)
MySQL [main]> load mysql servers to runtime;
Query OK, 0 rows affected (0.00 sec)
MySQL [main]> save mysql servers to disk;
Query OK, 0 rows affected (0.03 sec)
MySQL [main]> select * from monitor.mysql_server_read_only_log limit 3;
+-------------+------+------------------+-----------------+-----------+-------+
| hostname | port | time_start_us | success_time_us | read_only | error |
+-------------+------+------------------+-----------------+-----------+-------+
| 172.16.75.4 | 3306 | 1541506648164762 | 766 | 0 | NULL |
| 172.16.75.3 | 3306 | 1541506648162822 | 3585 | 1 | NULL |
| 172.16.75.3 | 3306 | 1541506649664049 | 993 | 1 | NULL |
+-------------+------+------------------+-----------------+-----------+-------+
3 rows in set (0.00 sec)8.これで、基本的な構成が完了し、バックエンドのmasterに2つのユーザーアカウントを作成し、proxysqlに異なるhostgroup_を追加します.idは、異なるユーザ間で読み書き分離を完了する.
Master mysql:
MariaDB [(none)]> grant all on *.* to 'reader'@'%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> grant all on *.* to 'writer'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)Proxysql:
MySQL [main]> insert into mysql_users (username,password,default_hostgroup) values ('reader','123456',20),('writer','123456',10);
Query OK, 2 rows affected (0.00 sec)
MySQL [main]> load mysql users to runtime;
Query OK, 0 rows affected (0.01 sec)
MySQL [main]> save mysql users to disk;
Query OK, 0 rows affected (0.03 sec)
MySQL [main]> select * from mysql_users\G
*************************** 1. row ***************************
username: reader
password: 123456
active: 1
use_ssl: 0
default_hostgroup: 20
default_schema: NULL
schema_locked: 0
transaction_persistent: 1
fast_forward: 0
backend: 1
frontend: 1
max_connections: 10000
*************************** 2. row ***************************
username: writer
password: 123456
active: 1
use_ssl: 0
default_hostgroup: 10
default_schema: NULL
schema_locked: 0
transaction_persistent: 1
fast_forward: 0
backend: 1
frontend: 1
max_connections: 10000
2 rows in set (0.00 sec)8.任意の1台のホストは、ユーザーの読み書き分離メカニズムに基づいてテストする.
[root@slave2 ~]# mysql -uwriter -h172.16.75.4 -P6033 -p123456 -e 'select @@server_id';
+-------------+
| @@server_id |
+-------------+
| 401 |
+-------------+
[root@slave2 ~]# mysql -ureader -h172.16.75.4 -P6033 -p123456 -e 'select @@server_id';
+-------------+
| @@server_id |
+-------------+
| 301 |
+-------------+9.SQL文に基づいて読み書き分離を実現する;
mysql_でquery_rulesテーブルに2つの正規表現を追加するルール.
MySQL [main]> insert into mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) values (1,1,'^SELECT.*FOR UPDATE$',10,1),(2,1,'^SELECT',20,1);
Query OK, 2 rows affected (0.00 sec)
MySQL [main]> load mysql query rules to runtime;
Query OK, 0 rows affected (0.00 sec)
MySQL [main]> save mysql query rules to disk;
Query OK, 0 rows affected (0.02 sec)10.任意のホストテストはSQL文の読み書き分離のメカニズムに基づいている.
[root@slave2 ~]# mysql -ureader -h172.16.75.4 -P6033 -p123456 -e 'set @@autocommit=0;\
start transaction;\
use hellodb;\
insert into coc (ID,ClassID,CourseID) values (100,100,100);\
select @@server_id;\
commit;'
+-------------+
| @@server_id |
+-------------+
| 301 |
+-------------+Masterのhellodbデータベースを表示します.
MariaDB [hellodb]> select * from coc;
+----+---------+----------+
| ID | ClassID | CourseID |
+----+---------+----------+
| 1 | 1 | 2 |
| 2 | 1 | 5 |
| 3 | 2 | 2 |
| 4 | 2 | 6 |
| 5 | 3 | 1 |
| 6 | 3 | 7 |
| 7 | 4 | 5 |
| 8 | 4 | 2 |
| 9 | 5 | 1 |
| 10 | 5 | 9 |
| 11 | 6 | 3 |
| 12 | 6 | 4 |
| 13 | 7 | 4 |
| 14 | 7 | 3 |
+----+---------+----------+
14 rows in set (0.00 sec)
MariaDB [hellodb]> select * from coc;
+-----+---------+----------+
| ID | ClassID | CourseID |
+-----+---------+----------+
| 1 | 1 | 2 |
| 2 | 1 | 5 |
| 3 | 2 | 2 |
| 4 | 2 | 6 |
| 5 | 3 | 1 |
| 6 | 3 | 7 |
| 7 | 4 | 5 |
| 8 | 4 | 2 |
| 9 | 5 | 1 |
| 10 | 5 | 9 |
| 11 | 6 | 3 |
| 12 | 6 | 4 |
| 13 | 7 | 4 |
| 14 | 7 | 3 |
| 100 | 100 | 100 |
+-----+---------+----------+
15 rows in set (0.00 sec)データベース・サーバにとって、クエリーの効率的な速度を保証するとともに、俗っぽくない書き込み操作を保証する必要があります.そのため、読み書き分離はデータベースの性能を向上させる上で特に重要です.これはユーザーの体験に関係しています.そのため、MySQLのミドルウェアであるProxySQLは、生産環境の中でそんなに少なくないように見えます.結局、体験を向上させる良いものです.