Mémento sur l'administration du service MySQL

Tutoriels

ressources

Tableau de comparaison

MyIsam Innodb
+ lecture + écriture
Sockage par table Stockage au format RAW
Dédié pour les concurrences Dédié pour les transactions
- Lecture : si du cache, utiliser memecache ou redis

Créer un utilisateur avec sa base

create user opencart@localhost identified by '$MDP';
GRANT ALL PRIVILEGES ON opencart.* TO 'opencart'@'localhost';

Créer un utilisateur avec des droits précis

GRANT USAGE ON *.* TO 'user'@'% ' IDENTIFIED BY PASSWORD '*733BE7BCdddddddddddd67E21EBCA68D648';
GRANT SELECT, INSERT, UPDATE ON `base`.`table1` TO 'user'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON `base`.`table2` TO 'user'@'%';

la désignation “%” est pour que ce soit accessible depuis n'importe quel IP. Le filtrage pourrait se faire par exemple au niveau du firewall.

Supprimer un utilisateur avec ses droits

drop user 'user'@'193.24.15.213';
FLUSH PRIVILEGES;

Changer de mot de passe du compte utilisateur

UPDATE mysql.user SET password=PASSWORD("XXBnb0o") where User="test" and Host="localhost";

Recréer le mot de passe administrateur

service mysql stop
mysqld_safe --skip-grant-tables &
mysql -u root -p

On donne son mot de passe puis :

update user set password=PASSWORD("nouveaumotdepasse") where User='root';
FLUSH PRIVILEGES;
quit

Migrer sa base de donnée

  • Exporter
mysqldump -u [utilisateur] -p [bdd] > bdd.sql
  • Importer

Se connecter à mysql et créer la base de donnée dans un premier temps :

mysql -u [utilisateur] -p
CREATE DATABASE nouvelle_base_de_donnee;
quit

Puis injecter les instructions :

mysql -u [utilisateur] -p nouvelle_base_de_donnee < bdd.sql

Adapter la base de donnée pour une petite application

nano /etc/mysql/my.cf

Modifier ces paramètres :

key_buffer              = 8M
max_connections         = 30
query_cache_size        = 8M
query_cache_limit       = 512K
thread_stack            = 128K

Optimisations

Le script suivant va permettre de savoir quoi configurer en fonction des capacités de la machine hôte :

wget http://mysqltuner.pl/
chmod +x mysqltuner.pl
./mysqltuner.pl

Parfois, il peut être intéressant de lancer les commandes suivante pour entretenir la qualité des bases :

mysqlcheck -u root -p --auto-repair --check  --all-databases
mysqlcheck -u root -p --optimize --all-databases
  • Adapter le nombres maximum d'ouverture de connexions :
MariaDB [(none)]> show global status like "%Max_used%";
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 16    |
+----------------------+-------+
1 row in set (0.01 sec)

Ainsi mettre max_connections à 18 parait bien.

  • Lister toutes les variables utilisé :
SHOW STATUS;
SHOW GLOBAL STATUS;
SHOW VARIABLES;
  • Augmenter l'usage mémoire (à froids)
innodb_buffer_pool_size à 16go
innodb_buffer_pool_instances à 8 (1go de mémoire par instance)

Benchmark

Pour tester les performances du serveur, on peut simuler des connexions :

sysbench –num-threads=100 –max-time=900 –max-requests=20000000 –test=oltp –oltp-table-size=20000000 –mysql-table-engine=innodb –oltp-test-mode=complex –oltp-read-only run

On peut activer le cache de requête pour les SELECT :

query_cache_type = 1

Méthode d'analyse

Naïf et bourrin que je suis, je me fais une todo-list pour éviter mes précipitations
  1. Regarder dans les logs mysql s'il n'y a pas de warning ou d'erreurs.
  2. Faire un show master/slave status pour voir s'il n'y a pas de bases répliqué.
  3. Vérifier l'espace disque disponible.
  4. Voir quel moteur est utilisé : InnoDB ou MyISAM ?
  5. Être sur que le client est au courant des manipulations faites sur les bases.
  6. Lui indiquer si besoin des temps de coupures qu'il peut y avoir.
  7. Vérifier que les backups sont présents.

Quelques commandes

  • Lister les utilisateurs :
SELECT Host,USER FROM mysql.user;

* Lister les droits utilisateurs :

SELECT CONCAT('SHOW GRANTS FOR ''',USER,'''@''',host,''';') FROM mysql.user INTO OUTFILE '/tmp/show_grants.txt';