Séparation ProxySQL en lecture-écriture de la configuration à utiliser

Le but de l'utilisation a été expliqué dans l'article sur les instructions de séparation en lecture-écriture de DBProxy de Meituan-Dianping. Cet article présente comment utiliser ProxySQL et la différence de performances avec DBProxy. Pour une introduction détaillée, veuillez consulter les instructions pertinentes sur le site officiel, et ce middleware est également un middleware recommandé par percona. Ses fonctionnalités ne sont pas très différentes des autres middlewares qui séparent la lecture et l'écriture. Les détails seront présentés dans l'article. Cet article présente brièvement quelques instructions lors de l'utilisation. Vous pouvez également consulter le wiki officiel pour obtenir de l'aide.

Environnement :

ID du distributeur : Ubuntu
Description : Ubuntu 14.04.5 LTS
Version : 14.04Nom de code : trusty

Télécharger

Site percona :

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

github/site officiel :

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

Jetons d'abord un coup d'œil à notre environnement :

MHA a été configuré :

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

Le manager MHA est au 172.16.16.34, le fichier de configuration est le suivant :

[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

Ci-dessous, nous construisons une séparation lecture-écriture basée sur un tel environnement MHA.

1 : Installez le logiciel ProxySQL, que nous déployons sur 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

À la fin, il y a l'invite suivante :

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

Autrement dit, l'installation est terminée. Vérifiez ensuite les fichiers spécifiques :

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

J'ai constaté que ProxySQL a effectivement été installé avec succès

2 : Commencez à configurer ProxySQL

Regardez le fichier de configuration :

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

Démarrez :

[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

Maintenant que le routage ProxySQL a été démarré, nous sommes invités à démarrer le 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>

Vous pouvez voir que nous nous sommes connectés avec succès. Ce que je veux expliquer ici, c'est que le fichier de configuration par défaut de proxysql est :

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

Ensuite, nous commençons à configurer 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)

Ajoutez les informations maître-esclave ci-dessous :

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)

Ajoutez ensuite le mappage en lecture-écriture (principalement pour garantir que ProxySQL lors du changement de backend MHA Il peut également changer automatiquement) :

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)

Ajouter un compte de surveillance pour ProxySQL :

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)

Configurer le compte de surveillance (configuré dans proxySQL) :

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)

PS : Parfois, si l'état de runtime_mysql_servers n'est pas EN LIGNE, vous pouvez consulter la table monitor.mysql_server_ping_log pour afficher les informations d'erreur spécifiques.

mysql> sélectionnez * dans monitor.mysql_server_ping_log;

Configurez ensuite le compte du programme, utilisez simplement root : 123456 pour configurer la plus haute autorité :

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)

Une fois la configuration terminée, commencez à recharger et à enregistrer notre configuration :

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)

Ensuite, commencez à configurer les règles de routage :

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)

Jusqu'à présent la configuration est terminée Terminé

3 : Testez la séparation en lecture et en écriture

Connectez le port proxySQL 6033 sur 172.16.16.35, et effectuez une simple opération de sélection :

[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)

Alors jetez un œil :

mysql> select * from stats_mysql_query_digest;

Vous pouvez constater que la séparation de la lecture et de l'écriture est achevée.

mysql> select @@server_id;+-------------+
| @@server_id |
+-------------+
| 353307 |
+-------------+1 row in set (0.01 sec)

Vérifiez que l'ID du serveur a été routé vers la base de données esclave 172.16.16.35:3307.

Test de mise à jour :

mysql> select * from maxiangqian.test for update;+-----+------+
| id | name |
+-----+------+
| 1 | qq |
| 2 | qq |
| 4 | aa |
| 11 | a |
| 111 | a |
+-----+------+5 rows in set (0.00 sec)

Vérifiez à nouveau

mysql> select * from stats_mysql_query_digest;

a été automatiquement acheminé vers la bibliothèque principale.

Le test de séparation de lecture et d'écriture ProxySQL+MySQL MHA est désormais terminé. . . A suivre

5 : Réflexions sur ProxySQL et les commandes simples

ProxySQL peut réaliser une séparation en lecture-écriture via la méthode ci-dessus, mais n'y a-t-il vraiment aucun problème avec cette méthode ? S'il s'agit de SQL qui nécessite des performances en temps réel très élevées, comme l'interrogation de l'état de la commande, il semble être acheminé to from BUG apparaîtra dans la bibliothèque. Nous pouvons choisir de contrôler ces paramètres sur le terminal. ProxySQL est uniquement utilisé comme équilibreur de charge. Créez plusieurs comptes pour ProxySQL, un pour la lecture et l'écriture, et un pour la lecture uniquement. Ensuite, le programme met en œuvre la séparation de la lecture et de l’écriture.

ProxySQL est conçu et exécuté en trois couches, à savoir RUNTIME, MEMORY et DISK :

RUNTIME représente la configuration actuellement effective de ProxySQL, y compris global_variables, mysql_servers, mysql_users, mysql_query_rules. La configuration ici ne peut pas être modifiée directement, elle doit être chargée depuis la couche suivante.

MEMORY est la configuration en main qui est habituellement modifiée sur la ligne de commande mysql Elle peut être considérée comme l'image de la base de données SQLite en mémoire

DISK / CONFIG FILE La configuration pour le stockage persistant. se trouve généralement dans $(DATADIR )/proxysql.db, qui sera chargé depuis le disque dur lors du redémarrage. Le fichier /etc/proxysql.cnf n'est utilisé que lors de la première initialisation. Après cela, si vous souhaitez modifier le port d'écoute, vous devez encore le modifier dans la ligne de commande de gestion puis l'enregistrer sur le disque dur

Commandes communes :

LOAD MYSQL SERVERS TO RUNTIME -- Faire prendre effet à la configuration modifiée, c'est-à-dire charger les paramètres depuis MEMORY, ce qui équivaut à LOAD MYSQL USERS FROM MEMORY La syntaxe de cette instruction. est relativement simple. FROM signifie CHARGER depuis la couche supérieure TO représente de cette couche vers une certaine couche. Par exemple, nous avons configuré le compte de surveillance MySQL plus tôt, mais nous devons encore exécuter LOAD et SAVE pour enregistrer les variables et les rendre efficaces.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!