MySQL

Pense-bête

Changer le mot de passe de l’administrateur

$ mysqladmin -u root -p<le mot de passe actuel> password '<le nouveau mot de passe>'

⚠️ Il faut que le service MySQL soit lancé.

Créer un utilisateur

mysql> CREATE USER '<utilisateur>'@'%' [IDENTIFIED BY '<mot de passe>'];

Attribuer des droits à un utilisateur

mysql> GRANT <liste de droits> ON <base>.<table> TO <utilisateur> [IDENTIFIED BY '<mot de passe>'];

Changer le mot de passe d’un utilisateur (non administrateur)

mysql> SET PASSWORD FOR '<utilisateur>'@'%' = PASSWORD('<nouveau mot de passe>');
mysql> FLUSH PRIVILEGES;

Relancer une réplication en sautant une (ou plusieurs) requête(s) bloquante(s)

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

ℹ️ Remplacer 1 par un nombre N sautera les N prochaines requêtes SQL du master

Augmenter le nombre maximum de connexions autorisées

mysql> SET GLOBAL max_connections = 1000;

Vérifier que le changement a été pris en compte :

mysql> SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 1000  |
+-----------------+-------+
1 row in set (0.00 sec)

Purger les les binlogs d’un master

Pour les purger de manière sélective (par exemple plus ancien qu’une certaine date), utiliser la commande PURGE BINARY LOGS : http://dev.mysql.com/doc/refman/5.5/en/purge-binary-logs.html

Pour supprimer tous les logs binaires d’un master, utiliser la commande RESET MASTER : http://dev.mysql.com/doc/refman/5.5/en/reset-master.html

Verrouiller les tables le temps d’un backup/snapshot

mysql> FLUSH TABLES WITH READ LOCK;

Une fois le snapshot effectué :

mysql> UNLOCK TABLES;

⚠️ Le verrou est appliqué à toutes les bases de données du serveur.

(Re)configurer une réplication à chaud

mysql> CHANGE MASTER TO
    MASTER_HOST='<serveur master>',
    MASTER_PORT=3306,
    MASTER_USER='<utilisateur>',
    MASTER_PASSWORD='<mot de passe>',
    MASTER_LOG_FILE='<fichier de binlog du master, eg.g "mysql-bin.000001">',
    MASTER_LOG_POS=<position dans le binlog du master>;

Afficher les tables verrouillées

mysql> SHOW OPEN TABLES;

Les lignes dont le champ In_use est supérieur à 0 sont verrouillées (référence).

Astuces

Un logger de requêtes SQL du pauvre

# tcpdump -i eth0 -s 0 -l -w - dst port 3306 | strings | perl -e '
while(<>) { chomp; next if /^[^ ]+[ ]*$/;
  if(/^(SELECT|UPDATE|DELETE|INSERT|SET|COMMIT|ROLLBACK|CREATE|DROP|ALTER)/i) {
    if (defined $q) { print "$q\n"; }
    $q=$_;
  } else {
    $_ =~ s/^[ \t]+//; $q.=" $_";
  }
}'

Calculer la taille des bases de données d’une instance

SELECT table_schema "Database", sum(data_length + index_length) / 1024 / 1024  "Size in MB" FROM information_schema.TABLES GROUP BY table_schema;

ℹ️ Il est possible de restreindre le calcul à une base spécifique en ajoutant la clause WHERE table_schema = "ma_base" avant la clause GROUP BY.

Calculer la taille des tables d’une base de données

SELECT table_name AS "Tables", round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" FROM information_schema. TABLES WHERE table_schema = "ma_base";

ℹ️ Il est possible de restreindre le calcul à une table spécifique en ajoutant la clause AND table_name = "ma_table" à la fin de la requête.

Récupérer les résultats d’une requête proprement depuis un shell

Les options suivantes vous permettent de récupérer les lignes de résultats d’une requête SQL en vue de les réutiliser dans un script ou de les piper à un autre programme :

$ mysql une_base -N -r -s -e 'SELECT * FROM une_table…'

Obtenir des statistiques sur les tables d’une base

SHOW TABLE STATUS from <base> like '<table>';

Note : <table> peut contenir des caractères wildcard %, par exemple prod_%.

Désactiver le log binaire le temps d’une session

Pour gagner en performances lors de l’import d’un dump, il est possible de désactiver ponctuellement le log binaire en exécutant la commande suivante avant de lancer l’import :

SET SESSION SQL_LOG_BIN=0;
SOURCE /chemin/vers/dump.sql

Ou en CLI :

mysql --init-command="SET SQL_LOG_BIN = 0;" -e "…"

⚠️ La désactivation du log binaire n’est effective que dans la session courante
et nécessite que l’utilisateur dispose du privilège SUPER. Plus d’informations: http://dev.mysql.com/doc/refman/5.5/en/set-sql-log-bin.html