Mémento sur l'administration du service MySQL

• Mise en place de réplications

• https://hal.archives-ouvertes.fr/hal-02561729/document
• https://www3.ntu.edu.sg/home/ehchua/programming/sql/MySQL_Beginner.html

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

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.

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

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

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

• 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

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

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)

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

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.

• 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';