Mysql : Maitre/esclave avec proxy

Soumis par drupal_admin le lun 22/08/2016 - 00:03

Mettre l'esclave au boulot ! Pour réaliser cet exploit il faut utiliser un "proxy intelligent" capable de distinguer les ordres en lecture seule (SELECT) et les autres (le SELECT ... FOR UPDATE) est un piège.
Le proxy que j'ai choisi de tester s'appelle "ProxySql", nom très original s'il en est, mais il fonctionne fort bien et est assez simple à manipuler.

Le paquet est téléchargeable (V1.2.1 au moment de ces tests) à :
https://github.com/sysown/proxysql/releases/download/v1.2.1/proxysql_1.2.1-debian8_amd64.deb
La liste complète des téléchargements est accessible à  l'URL:
https://github.com/sysown/proxysql/releases/
L'installation se fait sans mystère par :
dpkg -i proxysql_1.2.1-debian8_amd64.deb
Un script d'init est installé ainsi qu'un fichier de configuration /etc/proxysql.cnf, un répertoire /var/lib/proxysql est créé, de la doc /usr/share/doc ... Tout a l'air présent !
Le service n'est pas démarré par défaut, après un "
service proxysql initial" (pour la première fois) le service est lancé et écoute sur les ports 6032 et 6033 (paramètres par défaut).
Ce paramètre "initial" permet d'initialiser la base de données interne de proxysql.
Si vous n'avez pas personnalisé un minimum votre fichier "proxysql.cnf" vous pouvez le modifier et repasser un petit coup de "initial". Au passage renommer rapidement ce fichier sinon au prochain démarrage il sera réutilisé !
Proxysql utilise syslog et vous trouverez ses messages dans /var/log/daemon.log
Par la suite on n'aura plus besoin de repasser le paramètre "initial", sauf pour réparer une "grosse bêtise".
Testons maintenant la connexion à Proxysql (user/mot de passe fixés dans proxysql.cnf)  :

mysql -u admin -p  -h 127.0.0.1 -P 6032
Enter password: admin
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.30 (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>

Cela a un petit air connu ...
Remarque : la connexion par défaut est sur la base "main" :

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 main;
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_scheduler                    |
| scheduler                            |
+--------------------------------------+
mysql> use disk;
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_scheduler                    |
| scheduler                            |
+--------------------------------------+
\q

Tiens ? Les mêmes tables sont présentes dans les bases "disk" et "main".

On peut "regarder" le contenu des tables par des ordres "SELECT", ajouter des choses par "INSERT", modifier par "UPDATE", et faire des "DELETE". La plupart des commandes de Mysql fonctionnent (sauf "desc" ?).
Pour faire des modifications "rollbackables" il faut les faire au sein d'une transaction explicite (begin transaction;), le rollback sera ainsi possible.
Le proxy dispose de deux règles par défaut "
Select for update" et "select" qui envoyent respectivement sur les "hostgroups" 0 et 1, traditionnellement le "0" est le maître (celui qui peut effectuer des modifications de la base) et le groupe 1 celui des esclaves en lecture seule.
On se dépêche de changer le user "admin:admin" en autre chose de plus sécurisé :

update global_variables set variable_value = 'new_admin:admin_new' where variable_name = 'admin-admin_credentials';
Attention aux '-' , ":" et aux '_' !!!
Vous avez certainement remarqué les noms des tables doublés entre :
global_variables
runtime_global_variables
.....
Après une modif dans les tables "sans préfixe" il faut les recopier dans les tables "runtime" puis les sauvegarder sur disque pour rendre les modifications permanentes.
On peut ainsi préparer une nouvelle config dans les tables "sans préfixe" qui ne sont pas actives puis la basculer sur les tables "runtime" ce qui les rend actives.
Cela se fait à l'aide de commandes "LOAD" et "SAVE".
Dans notre cas de modfification de l'accès administrateur il faut :
Le reporter dans le "runtime"

LOAD MYSQL VARIABLES TO RUNTIME;
et les sauvegarder pour la postérité :
SAVE MYSQL VARIABLES TO DISK;

Dès que vous avez changé le user/mot de passe administrateur vous pouvez (devez) :

  • Détruire (ou renommer) le fichier /etc/proxysql.cnf
  • Redémarrer le service proxysql

Votre nouveau user/mot de passe sera alors actif.

Pour les tests le hostgroup 0 est une VM Mysql "master", le hostgroup 1 est une VM Mysql "slave" de la première qui ne recevra que les ordres "SELECT" purs exempts de toute clause "FOR UPDATE".
On revient un peu en arrière et on va voir la config postée initialement dans le fichier "proxysql.cnf" pour nos serveurs Serveur1 (master) et Serveur2 (slave) :

mysql_servers =
(
   { address="mysql1" , port=3306 , hostgroup=0 , max_connections=10 },
   { address="mysql2" , port=3306 , hostgroup=1 , max_connections=10 }
)
mysql_users:
(
  { username = "test" , password = "test", default_hostgroup = 0, active = 1 }
)

Si on regarde (avec notre nouvel "admin") l'état de la table mysql_servers :

select * main.from mysql_servers;
*************************** 1. row ***************************
       hostgroup_id: 0
           hostname: mysql1
               port: 3306
             status: ONLINE
             weight: 1
        compression: 0
    max_connections: 10
max_replication_lag: 0
            use_ssl: 0
     max_latency_ms: 0
*************************** 2. row ***************************
       hostgroup_id: 1
           hostname: mysql2
               port: 3306
             status: ONLINE
             weight: 1
        compression: 0
    max_connections: 10
max_replication_lag: 0
            use_ssl: 0
     max_latency_ms: 0
2 rows in set (0,00 sec)

C'est bien l'état indiqué dans le fichier original ,vérifions maintenant nos règles de répartition :

mysql> select * from mysql_query_rules\G
*************************** 1. row ***************************
              rule_id: 1
               active: 1
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: NULL
        match_pattern: ^SELECT .* FOR UPDATE$
 negate_match_pattern: 0
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 0
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
                  log: NULL
                apply: 1
*************************** 2. row ***************************
              rule_id: 2
               active: 1
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: NULL
        match_pattern: ^SELECT
 negate_match_pattern: 0
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 1
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
                  log: NULL
                apply: 1
2 rows in set (0,00 sec)

C'est bien ce que nous voulions installer.
Quand à notre user il semble lui aussi OK :

select * from MYSQL_USERS\G
*************************** 1. row ***************************
              username: test
              password: test
                active: 1
               use_ssl: 0
     default_hostgroup: 0
        default_schema:
         schema_locked: 0
transaction_persistent: 0
          fast_forward: 0
               backend: 1
              frontend: 1
       max_connections: 10000

Tout celà a l'air bel et bon.

Site à voir :
http://severalnines.com/blog/how-proxysql-adds-failover-and-query-control-your-mysql-replication-setup

taxonomie