MySQL

• http://www.openlogic.com/wazi/bid/195905/Tips-and-Tricks-to-Optimize-MySQL - astuces
• http://mysqltuner.com/ - script

wget --no-check-certificate https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
perl mysqltuner.pl

Y’a plus qu’a corriger les valeurs des variables qu’il suggère et faire un restart de mysql ;)

• http://www.debianhelp.co.uk/mysqlperformance.htm - exemple
• http://hackmysql.com/mysqlreport - script
• https://wiki.deimos.fr/MysqlTuner_:_Optimiser_votre_serveur_MySQL
• http://www.bocciolesi.fr/tutoriels-et-cours/optimisation-du-serveur-mysql/

A voir le paquet php5-mysqlnd qui est un client mysql natif qui est optimisé par mysql pour mysql

Pour voir toutes les variables

mysqladmin variables -p
mysql> show variables;
mysql> show global variables like 'log_output';

Pour voir les status

mysql> show status;

Pour vérifier vos fichiers de config avant de redémarrer le service mysql

mysqld --help

ou

mysqld --verbose --help | head

Process list

mysql>show processlist;

Kill process

mysql>kill "number from first col";

Sauvegarder une base

mysqldump -u user -p nom_de_la_base > backup.sql

Sauvegarder toutes les tables avec un prefix

mysqldump -h hostname -u user -p $(mysql nom_de_la_base -h hostname -u user -p -Bse 'show tables like"phpbb\_%"') > dump_phpbb_.sql

Restaurer

mysql -u user -p nom_de_la_base < backup.sql

Script PERL pour extraire une table d’un dump SQL

#!/usr/bin/perl -s -wnl
#extract a single table from a mysql dump
BEGIN {
    $table or warn
    "Usage: $0 -table=TABLE_TO_EXTRACT mysqldumpfile.sql"
    and exit 1;
}
 
/^DROP TABLE IF EXISTS `$table`/ .. /^UNLOCK TABLES;$/ and print;

Se connecter à mysql

mysql -h localhost -u root -p

Vérifier la config des logs

mysql> show global variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show global variables like 'general_log_file';
+------------------+----------------------------+
| Variable_name    | Value                      |
+------------------+----------------------------+
| general_log_file | /var/run/mysqld/mysqld.log |
+------------------+----------------------------+
1 row in set (0.00 sec)

mysql> show global variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log   | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

Activer les logs

mysql> set global general_log='ON';
Query OK, 0 rows affected (0.04 sec)

Désactiver les logs

mysql> set global general_log='OFF';
Query OK, 0 rows affected (0.02 sec)

Puis consulter votre fichier de log (ex:/var/run/mysqld/mysqld.log)

<?php
$host = 'localhost';
$db = 'maBase';
$password = 'p4ssW0rD';
$user = 'root';
 
echo "Votre base est en cours de sauvegarde.......
 
";
system("mysqldump --host=$host --user=$user --password=$password $db > $db.sql");
echo "C'est fini. Vous pouvez récupérer la base par FTP";
?>

Plein d’exemples de codes de backup et restauration sur ⇒ http://vitobotta.com/smarter-faster-backups-restores-mysql-databases-with-mysqldump/

A tester ⇒ https://www.digitalocean.com/community/tutorials/how-to-create-hot-backups-of-mysql-databases-with-percona-xtrabackup-on-ubuntu-14-04

Permet des backups à chaud sans perte en copiant directement les fichiers de mysql et en appliquant les transactions en cours.

Afin d’identifier les requêtes qui prennent du temps, activer les slow_query

log_slow_queries        = /var/log/mysql/mysql-slow.log
long_query_time = 1 # pour logguer les requêtes qui prennet plus d'une seconde

Ensuite, utiliser l’utilitaire mysqldumpslow pour analyser le log.

# mysqldumpslow /var/log/mysql/mysql-slow.log

Reading mysql slow query log from /var/log/mysql/mysql-slow.log
Count: 8  Time=11.45s (91s)  Lock=0.00s (0s)  Rows=2.1 (17), INF[INF]@localhost
  select `process_id` from `dashboard_log` left join `dashboard_process` on `process_id` = `dashboard_process`.`id` where `dashboard_process`.`id` is null group by `process_id` order by `process_id` asc

Count: 2  Time=7.05s (14s)  Lock=0.00s (0s)  Rows=1.0 (2), zabbix[zabbix]@localhost
  SELECT hu.itemid,AVG(hu.value) AS avg,MIN(hu.value) AS min,MAX(hu.value) AS max,MAX(hu.clock) AS clock FROM history_uint hu WHERE hu.itemid='S' AND hu.clock>='S' AND hu.clock<='S' GROUP BY hu.itemid

Count: 1  Time=5.43s (5s)  Lock=0.00s (0s)  Rows=557.0 (557), root[root]@localhost
  SHOW TABLE STATUS

Count: 8  Time=4.34s (34s)  Lock=0.00s (0s)  Rows=2.1 (17), INF[INF]@localhost
  select `process_id` from `dashboard_events` left join `dashboard_process` on `process_id` = `dashboard_process`.`id` where `dashboard_events`.`deleted_at` is null and `dashboard_process`.`id` is null group by `process_id` order by `process_id` asc

Count: 11  Time=1.27s (13s)  Lock=0.00s (0s)  Rows=1.0 (11), INF[INF]@localhost
  select count(*) as aggregate from `dashboard_log` where `process_id` = 'S'

Count: 11  Time=1.08s (11s)  Lock=0.00s (0s)  Rows=59.0 (649), INF[INF]@localhost
  SELECT B.* FROM
  (
  SELECT MAX( started_at ) as started_at, process_id, `order`
  FROM dashboard_events D
  INNER JOIN dashboard_page_process C
  USING ( process_id )
  where page_id=N
  GROUP BY process_id
  ) A INNER JOIN dashboard_events B USING (started_at,process_id) ORDER BY A.order

Count: 1  Time=1.03s (1s)  Lock=0.00s (0s)  Rows=2.0 (2), INF[INF]@localhost
  select `dashboard_events`.`id` as `id`, `etat`, `etape`, `process_id`, `started_at`, `updated_at`, `commentaire`, `erreur`, `nbr_log` from `dashboard_events` left join `dashboard_process` on `dashboard_events`.`process_id` = `dashboard_process`.`id` where `dashboard_events`.`deleted_at` is null and `etape` < 'S' order by `created_at` asc

Une requête intégressante

SHOW ENGINE INNODB STATUS

Sur le lien ci-dessous, des scripts permettant d’automatiser la création et la purge de partition de type date

⇒ http://www.geoffmontee.com/automatic-partition-maintenance-in-mysql-and-mariadb-part-3/