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'

ID <> CONNECTION_ID() permet de ne pas lister la connexion courante (qui fait la requête à information_schema.PROCESSLIST)
INFO IS NOT NULL permet de ne lister que les process qui effectue une requête d’un utilisateur (à vérifier). Détails concernant le champ “INFO” :

The statement the thread is executing, or NULL if it is not executing any statement. The statement might be the one sent to the server, or an innermost statement if the statement executes other statements. For example, if a CALL statement executes a stored procedure that is executing a SELECT statement, the Info value shows the SELECT statement.

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 ».

création d’un utilisateur de réplication

Sur le master:

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'monutilisateur'@'%' IDENTIFIED BY '…';

CREATE USER 'monutilisateur'@'%' IDENTIFIED BY '…';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'monutilisateur'@'%';

Restoration de réplication

xtrabackup (innobackupex)

Sources:

https://www.percona.com/live/e17/sites/default/files/slides/Percona%20XtraBackup%20Best%20Practices%20-%20FileId%20-%20118506.pdf

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'

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). Note: $XTRA_TMP doit se terminer par un / (je ne sais plus pourquoi).

Dans cette section :

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

Backuper hostA sur hostB

root@hostA # xtrabackup --backup --stream=xbstream --target-dir="$XTRA_TMP" | ssh hostB "xbstream --extract --directory='$XTRA_TMP'"

Avec quelques options supplémentaire (et en evitant l’utilisation de ssh):

root@hostB # nc -l 1234 | xbstream --extract --directory="$XTRA_TMP"

root@hostA # xtrabackup --backup --parallel=`nproc` --compress --compress-threads=`nproc` --stream=xbstream --target-dir="${XTRA_TMP}" | nc hostB 1234

A partir de la version 8.0 xbstream permet d’utiliser les options :

–decompress
–compress-threads=X vous devez installer qpress avant de pouvoir utiliser cette option.

Quelques options qui permettent parfois de se sortir de la merde (cf. xtrabackup –help) :

–parallel=X: nombre de thread de lecture/transfert utiliser durant le backup
–use-memory=X: memoire qu’il est possible d’utiliser pour faire le xtrabackup (100MB par défault)
–compress: compresse les fichier avant de les transférer
–decompress: décompresse les fichier (à utilisé sur hostB). Vous devez installer qpress avant de pouvoir utiliser cette option.
–compress-threads=X: nombre de threads de compression
--skip-tables-compatibility-check: Si la bdd contient beaucoup de tables (à n’utiliser que si un xtrabackup strandard échoue tellement il y a de table).
–no-lock: à n’utiliser que si un xtrabackup strandard échoue.

Rétablir hostB

si vous avez utiliser l’option –compress lors de la création du backup, mais que vous n’avez pas utilisé l’option --decompress de xbstream (ou qu’elle n’est pas disponible dans votre version). Attention, il faut installer qpress au préalable.

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

Préparation du backup

root@hostB # xtrabackup --prepare --target-dir=$XTRA_TMP
root@hostB # chown -R mysql: $XTRA_TMP

Stop mysql

root@hostB # systemctl stop mysql

Application du backup

root@hostB # xtrabackup --copy-back $XTRA_TMP
ou
root@hostB # xtrabackup --move-back $XTRA_TMP
ou
root@hostB # mv ${DATADIR}{,-old}
root@hostB # mv $XTRA_TMP $DATADIR

Lancer mysql

root@hostB # systemctl start mysql

Relancer la réplication

root@hostB # ls /space/mysql/xtrabackup_binlog_*
xtrabackup_binlog_info        xtrabackup_binlog_pos_innodb
root@hostB # cat /space/mysql/xtrabackup_binlog_info
master-log-bin.000019   16299519
root@hostB # cat /space/mysql/xtrabackup_binlog_pos_innodb
master-log-bin.000019   16299519

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

rsync

Sources:

https://dev.mysql.com/doc/refman/8.0/en/lock-instance-for-backup.html

Cette technique est beaucoup plus rapide que celle du xtrabackup, mais elle implique un lock temporaire (généralement assez court, de quelques secondes à quelques minutes…). Elle n’est valide qu’a partir de mysql 8.0. Je ne l’ai pas testée personnellement.

Pour rétablir une réplication entre hostB et hostA (ou hostA est le master, d’une réplication master→slave, non master↔master).

On stop mysql sur hostB.

root@hostB # systemctl stop mysql.service

On test combien de temps prendra le lock du master (ça peut être trop important pour le client)

root@hostA # rsync -axvz --delete /space/mysql/ root@hostB:/space/mysql/ && time rsync -axvz --delete /space/mysql/ root@hostB:/space/mysql/

On synchronise hostB avec les données de hostA (attention, hostA sera lock pendant le deuxième rsync).

root@hostA # cat script.sh
#!/bin/bash

set -e -x

LOCK=/tmp/rsync_mysql_backup

if test -e "$LOCK"
then
        sync
        rsync -axz --delete --exclude "auto.cnf" /space/mysql/ root@hostB:/space/mysql/
        exit 0
fi

touch "$LOCK"
trap 'rm -f "$LOCK"' EXIT

rsync -axz --delete --exclude "auto.cnf" /space/mysql/ root@hostB:/space/mysql/

mysql --skip-column-names --batch <<END
SET net_read_timeout = 3600;
SET net_write_timeout = 3600;
SET wait_timeout = 3600;
FLUSH TABLES WITH READ LOCK;
LOCK INSTANCE FOR BACKUP;
SELECT SLEEP(5);
SYSTEM /bin/bash "$0";
SHOW MASTER STATUS;
UNLOCK INSTANCE;
UNLOCK TABLES;
END

root@hostA # bash -x script.sh

On configure et relance le slave mysql de hostB.

root@hostB # systemctl start mysql.service
root@hostB # mysql
> RESET SLAVE ALL;
> CHANGE MASTER TO MASTER_HOST='hostA', MASTER_USER='repl', MASTER_PASSWORD='…', MASTER_LOG_FILE='<check show master status output>', MASTER_LOG_POS=<check show master status output>;
> START SLAVE;

vérifier qu’un slave contient bien les même data que le master

Sources:

TODO

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é ☺.

Tchernomax's cloud

MySQL/MariaDB

processlist

Éffacer rapidement une commande

création d’un utilisateur de réplication

Restoration de réplication

xtrabackup (innobackupex)

Backuper hostA sur hostB

Rétablir hostB

rsync

vérifier qu’un slave contient bien les même data que le master

Protocole d’authentification par mot de passe

Connexion réussi

Connexion échouée

scripts de vérification