Mémento sur l'administration de MySQL

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";

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

Réplication MySQL

Avant de commencer, il faudra qu'il y ait la même base de donnée des deux côtés.
  • Stratégie Master → Slave :

On commence par le master en indiquant ces options :

nano /etc/mysql/mariadb.conf.d/50-server.cnf
log_bin          = /var/log/mysql/mysql-bin.log
bind-address = 0.0.0.0
server-id = 1
binlog_format     = mixed
  1. On spécifie l'emplacement du binlog
  2. On dit que le master écoutera tous toutes les IPs pour que le slave puisse s'y connecter
  3. Chaque serveur doit avoir un id différent
  4. On définis ce format pour de meilleurs performances.

/etc/init.d/mysql restart

Puis on passe au serveur slave :

nano /etc/mysql/mariadb.conf.d/50-server.cnf
server-id = 2
<code>

/etc/init.d/mysql restart

On créer sur les deux machines un utilisateur dédié à cette réplication :

<code mysql>
GRANT REPLICATION SLAVE ON *.* TO user@'%' IDENTIFIED BY 'password';

En restant sur le slave, on déclare la machine master :

CHANGE MASTER TO MASTER_HOST='IP-SERVER-MASTER', MASTER_USER='user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.NNNNN', MASTER_LOG_POS=XXX;

Pour avoir les informations liée au valeur MASTER_LOG_FILE et MASTER_LOG_POS, il suffira de tapez sur le master ceci :

show master status;

Un fois cela fait, il reste plus qu'a démarrer la réplication avec les infos que l'on peut obtenir :

START SLAVE;
SHOW SLAVE STATUS\G;

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

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';
  • Dernière modification: 2020/02/04 11:15