Nous allons voir dans cette article comment installer, configurer, optimiser et gérer MySQL.

Installation et configuration

Installation Standard

Pour installer MySQL, exécutez les commandes suivantes:

aptitude update  
aptitude install mysql-server  
mkdir /data/mysql  
/etc/init.d/mysql stop
mv /var/lib/mysql/* /data/mysql/  
chown -R mysql:mysql /data/mysql/  

Configuration

Editez le fichier suivant :

nano /etc/mysql/my.cnf  

Puis commentez bind-address

datadir         = /var/lib/mysql  
#bind-address           = 127.0.0.1

Note : Si vous souhaitez stocker vos données dans une autre arborescence que /var/lib/mysql, vous devez modifier le paramètre datadir.

Puis lancez les commandes suivantes:

/etc/init.d/mysql restart
mysql -uroot -p  
use mysql;  
update user set host='%' where user='root' and host='localhost';  
flush privileges;  
exit;  

Sauvegarde et Restauration

Sauvegarde

Sauvegarder l'ensemble des bases de données

mysqldump --all-databases -u root -p | gzip > /backup/allMysqlDatabases.sql.gz

ou

su - mysql -c mysqldump --all-databases | gzip > /backup/allMysqlDatabases.sql.gz  

Sauvegarde du système de fichier

Sauvegardez /var/lib/mysql après avoir coupé MySQL.

Gestion

Optimisation et correction des tables

mysqlcheck -u <utilisateur> -p --auto-repair --optimize --all-databases  

Optimisation de MySQL

Téléchargez l'utilitaire mysqltunner:

wget https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl  
chmod +x mysqltuner.pl  

Puis exécutez le :

./mysqltuner.pl

Entrez le login root et son mot de passe.

Vous devez laisser tourner MySQL au moins un jour avant de relancer l'outil pour vérifier s'il existe encore des optimisations à réaliser.

Vous devez obtenir un résultat similaire à cela :

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.95
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive +BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 105M (Tables: 74)
[--] Data in InnoDB tables: 11M (Tables: 171)
[!!] BDB is enabled but isn't being used
[!!] Total fragmented tables: 4

-------- Security Recommendations  -------------------------------------------
[!!] User '@localhost' has no password set.
[!!] User '@localhost.localdomain' has no password set.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 8d 16h 22m 14s (40M q [53.533 qps], 388K conn, TX: 6B, RX: 38B)
[--] Reads / Writes: 22% / 78%
[--] Total buffers: 34.0M global + 2.7M per thread (100 max threads)
[OK] Maximum possible memory usage: 309.0M (1% of installed RAM)
[OK] Slow queries: 0% (0/40M)
[OK] Highest usage of available connections: 22% (22/100)
[OK] Key buffer size / total MyISAM indexes: 8.0M/126.1M
[OK] Key buffer hit rate: 100.0% (812M cached / 192K reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (8 temp sorts / 150K sorts)
[!!] Joins performed without indexes: 28268
[OK] Temporary tables created on disk: 1% (1K on disk / 111K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 1% (64 open / 5K opened)
[OK] Open file limit used: 6% (64/1K)
[OK] Table locks acquired immediately: 99% (38M immediate / 38M locks)
[!!] InnoDB data size / buffer pool: 11.9M/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:  
    Add skip-bdb to MySQL configuration to disable BDB
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    Set thread_cache_size to 4 as a starting value
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:  
    query_cache_size (>= 8M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    thread_cache_size (start at 4)
    table_cache (> 64)
    innodb_buffer_pool_size (>= 11M)

Pour mettre en place les optimisations proposées, vous devez éditer le fichier suivant :

nano /etc/my.cnf  

Puis ajouter les lignes suivantes :

log-error=/var/log/mysqld.log  
pid-file=/var/run/mysqld/mysqld.pid

# CUSTOM FROM MYSQLTUNER #
skip-bdb

# Best tunning
innodb_log_file_size = 512M  
innodb_buffer_pool_size = 6000M  
innodb_log_buffer_size = 16MB  
key_buffer_size = 6000M  
table_cache = 1024  
thread_cache = 16  
query_cache_size = 64M  
join_buffer_size = 16M  
query_cache_limit = 64M  
max_connections = 15  

Si vous modifiez les paramètres innodb_log_file_size, vous devez réaliser les actions complémentaires suivantes :

/etc/init.d/mysqld stop
cd /var/lib/mysql  
mv ib_logfile0 ib_logfile0.old  
mv ib_logfile1 ib_logfile1.old  
/etc/init.d/mysqld start

Pour prendre en compte les modifications (dans tout les cas), vous devez relancer MySQL :

/etc/init.d/mysqld restart