Mysql : Maitre/esclave avec proxy

Mysql : Maitre/esclave avec proxy jpp

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-debi… 
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 cela a l'air bel et bon.


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