MySQL/MariaDB

Published: 02-12-2015

Updated: 09-02-2018

By: Maxime de Roucy

tags: database mariadb mysql

Voici quelques notes sur MySQL/MariaDB.

processlist

Sources:

La command SHOW [FULL] PROCESSLIST peut rapidement montrer ses limites lorsqu’il y a beaucoup de connexion. Il est bien plus pratique d’utiliser la table information_schema.PROCESSLIST. (Je met la commande avec et sans mysql -e pour faciliter le copier/coller)

SELECT * FROM information_schema.PROCESSLIST\G
mysql -e 'SELECT * FROM information_schema.PROCESSLIST\G'
SELECT * FROM information_schema.PROCESSLIST WHERE ID <> CONNECTION_ID() AND INFO IS NOT NULL \G
mysql -e 'SELECT * FROM information_schema.PROCESSLIST WHERE ID <> CONNECTION_ID() AND INFO IS NOT NULL \G'

Si on ne voit rien n’anormal, toujours vérifier :

SELECT * FROM information_schema.PROCESSLIST WHERE ID <> CONNECTION_ID() \G
mysql -e 'SELECT * FROM information_schema.PROCESSLIST WHERE ID <> CONNECTION_ID() AND INFO IS NOT NULL \G'

Surveiller l’évolution des process :

watch 'mysql -N -e "SELECT COUNT(id) FROM information_schema.PROCESSLIST;"'
for i in {1..3}
do
	echo "############## run $i"
	mysql -e 'SELECT * FROM information_schema.PROCESSLIST WHERE ID <> CONNECTION_ID() AND INFO IS NOT NULL ORDER BY ID \G'
	sleep 1
done

Kill toutes les sessions en sleep :

mysql -N -e "SELECT CONCAT('KILL ',id,';') FROM information_schema.PROCESSLIST WHERE Command='Sleep'" | mysql

Éffacer rapidement une commande

Source : How to abandon current command in mysql cli

Parfois je commence à écrire une commande et je change d’avis en cours de route. Généralement quand je suis dans un shell je fait juste Ctrl+C pour avoir un nouveau prompte. Dans MySQL le Ctrl+C kill le client.

Pour supprimer la commande en cours il faut utiliser les combinaisons « ctrl+a ctrl+k » ou « ctrl+e ctrl+u ».

xtrabackup (innobackupex)

xtrabackup est le successeur de innobackupex, aujourd’hui innobackupex est un symlink vers xtrabackup.

xtrabackup permet de backuper les tables MyISAM, InnoDB et XtraDB. Les tables InnoDB et XtraDB sont backuper sans que cela ne lock de transaction (il n’y a pas de lock posé), contrairement à un simple mysqldump. En revanche les tables MyISAM (et autre) sont lock via un backup lock avant d’être backupé.

Pour savoir si vous avez des tables en MyISAM :

root@host # my_print_defaults mysqld | grep -- '--datadir=' | tail -n 1
--datadir=/var/lib/mysql
root@host # cd /var/lib/mysql
root@host # find -name '*.MYI'

restauration d’une réplication

Avant de lancer une restauration, n’oublier pas de vous mettre dans un screen.

Imaginons que le serveur slave « hostB » soit désynchroniser par rapport au serveur master « hostA ». On veut re-synchroniser hostB avec hostA.

Sur les deux machines, il faut installer percona-xtrabackup, via les dépôts officiels ou directement le paquet. Et créé un répertoire temporaire (appelé ici $XTRA_TMP).

Dans cette section :

root@host # my_print_defaults mysqld | grep -- '--datadir=' | tail -n 1
--datadir=/var/lib/mysql
root@host # DATADIR=/var/lib/mysql

sans compression

Backuper hostA sur hostB :

root@hostA # xtrabackup --stream=xbstream $XTRA_TMP | ssh hostB "xbstream -x -C $XTRA_TMP"

Rétablir hostB :

root@hostB # # appliquer les logs binaires
root@hostB # xtrabackup --apply-log --export $XTRA_TMP

root@hostB # # stop mysql, changer son datadir et start mysql
root@hostB # chown -R mysql: $XTRA_TMP
root@hostB # systemctl stop mysql
root@hostB # mv ${DATADIR}{,-old}
root@hostB # mv $XTRA_TMP $DATADIR
root@hostB # systemctl start mysql

root@hostB # # lancer la réplication
root@hostB # grep -E '^binlog_pos' $XTRA_TMP/xtrabackup_info
binlog_pos = filename 'master-log-bin.000001', position '88238'

root@hostB # mysql
mysql> CHANGE MASTER TO MASTER_HOST='hostA', MASTER_USER='toto', MASTER_PASSWORD='titi', MASTER_LOG_FILE='master-log-bin.000001', MASTER_LOG_POS=88238;
mysql> start slave;
mysql> show slave status;

avec compression

Vous pouvez activer la compression lors du backup de hostA vers hostB, mais c’est assez long. Et vue qu’on fait une restoration de réplication et non un vraie backup, on va devoir décompresser les éléments juste après. Donc je déconseille d’utiliser cette option.

Il faut installer qpress sur hostB (mais pas sur hostA si je me souvient bien).

root@hostA # xtrabackup --compress --stream=xbstream $XTRA_TMP | ssh hostB "xbstream -x -C $XTRA_TMP"

Décompression et suppression des archives :

root@hostB # xtrabackup --decompress --parallel=`nproc` $XTRA_TMP
root@hostB # find $XTRA_TMP -name '*.qp' -delete

Puis, comme pour le mode sans compression, rétablir hostB :

root@hostB # # appliquer les logs binaires
root@hostB # xtrabackup --apply-log --export $XTRA_TMP

root@hostB # # stop mysql, changer son datadir et start mysql
root@hostB # chown -R mysql: $XTRA_TMP
root@hostB # systemctl stop mysql
root@hostB # mv ${DATADIR}{,-old}
root@hostB # mv $XTRA_TMP $DATADIR
root@hostB # systemctl start mysql

root@hostB # # lancer la réplication
root@hostB # grep -E '^binlog_pos' $XTRA_TMP/xtrabackup_info
binlog_pos = filename 'master-log-bin.000001', position '88238'

root@hostB # mysql
mysql> CHANGE MASTER TO MASTER_HOST='hostA', MASTER_USER='toto', MASTER_PASSWORD='titi', MASTER_LOG_FILE='master-log-bin.000001', MASTER_LOG_POS=88238;
mysql> start slave;
mysql> show slave status;

Protocole d’authentification par mot de passe

Il existe plusieurs méthode d’authentification sous MySQL. Ici je m’intéresse à la méthode d’authentification classique, par défaut, Secure Password Authentication.

MySQL ne stock pas le mot de passe des utilisateurs. Il ne possède que le résultat de la commande PASSWORD. Cette commande correspond à un double sha1 sur le mot de passe réel. Le résultat de ce double sha1 sera appelé « hash2 » dans cette section. « password » désigne le mot de passe en claire de l’utilisateur.

Le protocole d’authentification par mot de passe de MySQL est décrit dans la page de documentation Secure Password Authentication. Et le code correspondant se trouve dans le fichier sql/password.c.

On peut retenir que l’authentification se déroule comme suit (le « . » désigne une concaténation) :

Server Client
Connection TCP
<————>
(généré aléatoirement) salt ————->
<————- scramble = sha1(password) xor sha1(salt . sha1(sha1(password)))
sha1(scramble xor hash2) == hash2

J’ai capturé, avec tcpdump, deux connexions une réussi et une échouée. J’ai ensuite visualisé les fichiers de trace avec Wireshark.

root@laptop # tcpdump -i lo -n -s0 -w mysql.pcap tcp port mysql

Connexion réussi

image : mysql password ok salt image : mysql password ok hash2

Le serveur envoie le « salt » au client ; n°4 Salt: ,qx)[sQz et Salt: 2+{P]bVexN\d. En fait le salt correspond à la concaténation de ces éléments, excepté le dernier octet de chaque élément. Pour avoir le « salt » il faut donc fair « Copy → Bytes → Hex Stream ». On obtient « 2c7178295b73517a00 » et « 322b7b505d625665784e5c6400 », le salt est donc « 2c7178295b73517a322b7b505d625665784e5c64 ».

Le client répond avec le scramble ; n°6 « Password: 1148c4ddaafd0fd0563e2269a34667130692bbaa ».

La connexion à réussi, n°8 « Response OK ».

Connexion échouée

image : mysql password fail salt image : mysql password fail hash2

Idem avec un salt différent, mais la connexion à échouée, n°8 « Response Error 1045 ». Le mot de passe n’était pas le bon.

scripts de vérification

Voici deux scripts python qui permettent de vérifier que le mot de passe entré par l’utilisateur est le bon. Le premier effectue exactement la même vérification que MySQL lorsqu’il reçoit le « scramble ». Il n’a pas besoin du mot de passe de l’utilisateur.

#!/usr/bin/python3

import hashlib

# from MySQL : show grants for 'user'@'…';
hash2 = password = bytes.fromhex('BF06A06D69EC935E85659FCDED1F6A80426ABD3B');

# from Wireshark ("Hex Stream" copy of the two Salt elements)
# remove the last byte (00) from each string
salt = bytes.fromhex("2c7178295b73517a" + "322b7b505d625665784e5c64");

# from Wireshark ("Hex Stream" copy of the Password element)
scramble = bytes.fromhex('1148c4ddaafd0fd0563e2269a34667130692bbaa');

hash1_int = int.from_bytes(scramble, 'big') ^ int.from_bytes(hashlib.sha1(salt + hash2).digest(), 'big');
# doesn't work… I don't know why
#hash1 = hash1_int.to_bytes(hash1_int.bit_length() // 8 + 1, byteorder='big');
hash1 = bytes.fromhex(format(hash1_int,'x'));

if hashlib.sha1(hash1).digest() == hash2 :
    print("good password");
else:
    print("wrong password");

Le deuxième se base sur le mot de passe de l’utilisateur, mais pas sur le hash2 stoqué dans la base MySQL ; « show grants for ‘user’@‘…’ ».

#!/usr/bin/python3

import hashlib

password_plain = b"some_pass";

# from Wireshark ("Hex Stream" copy of the two Salt elements)
# attention à bien enlever le 00 à la fin des 2 string
salt = bytes.fromhex("2c7178295b73517a" + "322b7b505d625665784e5c64");

# from Wireshark ("Hex Stream" copy of the Password element)
wireshark_scramble = '1148c4ddaafd0fd0563e2269a34667130692bbaa';

hash1 = hashlib.sha1(password_plain).digest();
hash2 = hashlib.sha1(hash1).digest();

scramble = int.from_bytes(hash1,'big') ^ int.from_bytes(hashlib.sha1(salt + hash2).digest(), 'big');

if wireshark_scramble == format(scramble,'x') :
    print("good password");
else:
    print("wrong password");

Je n’est pas vérifié dans les tréfond du code de MySQL mais l’erreur 1045 semble indiquer qu’il y a un problème soit sur le mot de passe, soit le login. Il est facile de vérifier le login, il est en claire dans la trace réseau. Si vous obtenait une erreur 1045 vous pouvez considérer que vous n’utilisez pas le bon mot de passe ; mais vous pouvez quand même utiliser les scriptes si vous êtes borné ☺.