Optimisation et sécurisation de Mysql

Blog > Administration serveur dédié

Grâce aux deux précédents tutos, nous disposons d’un serveur LAMP fonctionnel.

Nous allons à présent faire quelques réglages et installer différents outils supplémentaires qui nous simplifieront la vie au quotidien et nous permettront de contrôler que tout se passe bien pour notre serveur de base de données.

PhpMyAdmin, une interface web pour Mysql

L’installation se fait très simplement avec le paquet phpMyAdmin présent dans le dépôt.

$ apt-get install phpmyadmin

Une fois installé, phpMyAdmin devrait être accessible à l’adresse http://monserveur/phpmyadmin. Mais personnellement ce n’est jamais le cas. Le fichier de configuration de phpmyadmin n’est pas ajouté à Apache. Nous allons donc le faire manuellement :

$ ln -s /etc/phpmyadmin/apache.conf /etc/apache2/conf-available/phpmyadmin.conf
$ a2enconf phpmyadmin.conf
$ systemctl reload apache2

Tout devrait à présent fonctionner.

Note

Si l’on souhaite disposer de la dernière version de phpMyAdmin ainsi que des mises à jour, il suffit de se rendre sur cette page, d’ajouter le dépôt au serveur puis de faire un update et un upgrade.

Sécurisation de Mysql

Nous allons maintenant utiliser un petit outil fourni avec Mysql pour améliorer sa sécurité.

$ mysql_secure_installation
Enter current password for root (enter for none):   # On rentre le mot de passe root
OK, successfully used password, moving on...
Change the root password? [Y/n] n                   # On l'a déjà défini
Remove anonymous users? [Y/n] Y
Disallow root login remotely? [Y/n] Y
Remove test database and access to it? [Y/n] Y
Reload privilege tables now? [Y/n] Y

Optimisation de votre config Mysql

Log des requêtes lentes

Le fichier de configuration de Mysq est /etc/mysql/mysql.conf.d/mysqld.cnf, il permet de modifier finement la configuration de Mysql ce qui n’est pas forcement évident tant que nous n’avons pas de retour sur son utilisation. Nous allons donc, dans un premier temps, nous contenter d’activer le log des requêtes trop lentes (celles mettant plus de 2 secondes à s’exécuter).

slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2

MySQLTuner

MySQLTuner est un petit script Perl qui va analyser l’activité de MySQL et vous proposer des améliorations pour la configuration du fichier /etc/mysql/my.cnf que nous avons déjà abordé.

Pour l’utiliser il suffit de télécharger la dernière version du script sur Github et de l’exécuter sur le serveur.

$ wget http://mysqltuner.pl/ -O mysqltuner.pl
$ perl mysqltuner.pl

Une fois l’utilisateur et le mot de passe rentrés, le script nous affiche le résultat de son analyse. Les !! indiquent les problèmes rencontrés. En fin du script, des recommandations nous sont faites.

[OK] Logged in using credentials from debian maintenance account.

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.61-0ubuntu0.10.04.1
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 2M (Tables: 22)
[!!] InnoDB is enabled but isn't being used
[OK] Total fragmented tables: 0

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 4h 50m 49s (954 q [0.009 qps], 123 conn, TX: 2M, RX: 2M)
[--] Reads / Writes: 85% / 15%
[--] Total buffers: 58.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 463.8M (23% of installed RAM)
[OK] Slow queries: 0% (0/954)
[OK] Highest usage of available connections: 1% (2/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/278.0K
[OK] Key buffer hit rate: 99.1% (4K cached / 41 reads)
[!!] Query cache efficiency: 7.3% (14 cached / 191 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 5 sorts)
[OK] Temporary tables created on disk: 24% (88 on disk / 355 total)
[OK] Thread cache hit rate: 98% (2 created / 123 connections)
[!!] Table cache hit rate: 16% (54 open / 329 opened)
[OK] Open file limit used: 9% (99/1K)
[OK] Table locks acquired immediately: 100% (276 immediate / 276 locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
    Add skip-innodb to MySQL configuration to disable InnoDB
    Enable the slow query log to troubleshoot bad queries
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_limit (> 1M, or use smaller result sets)
    table_cache (> 64)

Le script prend tout son sens sur un serveur déjà en route afin d’avoir beaucoup d’informations à analyser. Il est donc intéressant d’attendre un peu avant de le lancer et de le refaire régulièrement si la charge du serveur évolue.

Mytop, surveillance de MySQL en temps réel

Pour faire simple, mytop est à MySQL ce qu’est la commande top à votre serveur. Elle permet de contrôler à un instant donné les requêtes en cours d’exécution. Toujours pratique quand MySQL s’emballe.

Une fois de plus l’installation est très simple.

$ apt-get install mytop

La commande s’utilise de la manière suivante :

$ mytop -u username -p password                   # Regroupe toutes les bases de données
$ mytop -u username -p password -d databasename   # Cible une base de de donnée particulière

Mytop associé au log des requêtes lentes que nous avons activé dans le tuto précédent nous permettra de surveiller convenablement MySQL.

Conclusion

Mysql est à présent bien configuré et nous avons à notre disposition des moyens pour contrôler son fonctionnement. Nous allons pouvoir à présent nous pencher sur le monitoring de notre serveur d’une manière plus générale.


Inscrivez-vous à notre Newsletter

Recevez nos meilleurs articles et accédez au contenu exclussif reservé aux abonnés.

Cet article vous est proposé par IDNEO.

Notre agence de communication est spécialisée dans la création de sites Internet, le référencement, le graphisme et la photographie.

avatar