Table des matières

MySQL

Optimisation

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

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

Quelques commandes

Pour voir toutes les variables

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

Pour modifier une variable global

mysql> set global max_connections=500;

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

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

La bonne pratique en prod est d’utiliser les options suivantes:

mysqldump -u USER -p --single-transaction --quick --lock-tables=false --all-databases (or) DATABASE | gzip > OUTPUT.sql.gz

Sauvegarder une table avec des conditions WHERE

# Dump only the rows with the id column bigger than 500
mysqldump my_db_name my_table_name --where="id > 500" > my_backup.sql
 
# Dump only the rows with the created_at column in the given interval
mysqldump my_db_name my_table_name --where="date between '2020-09-01' and '2020-09-30'" > my_backup.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;

Pour automatiser la connexion a mysql et ne pas avoir à taper de mot de passe, créer un fichier ~/.my.cnf

[client]
user=root
password=somepassword

Logguer les requêtes en temps réel ponctuellement

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)

Faire un backup via un script php

<?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/

Un autre script dans les paquets automysqlbackup https://www1.zonewebmaster.eu/serveur-debian-mysql:automysqlbackup

Percona XtraBackup

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.

Les slow query

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

InnoDB

Une requête intégressante

SHOW ENGINE INNODB STATUS

La commande innotop permet de faire comme htop avec beaucoup d’info sur ce qu’il se passe sur innodb

innotop

Partition

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/

Erreur

Après avoir lancé une optimization puis violemment killé la requete en cours, la table était inaccessible …

Starting MySQL database server: mysqld.
Checking for corrupt, not cleanly closed and upgrade needing tables..
ERROR 144 (HY000) at line 1: Table 'dashboard_log' is marked as crashed and last (automatic?) repair failed

Se placer dans le bon répertoire

cd /var/lib/mysql/MA_BDD
# myisamchk MA_TABLE
Checking MyISAM file: MA_TABLE
Data records:  654724   Deleted blocks:       0
myisamchk: warning: Table is marked as crashed and last repair failed
- check file-size
myisamchk: warning: Size of indexfile is: 275469312      Should be: 25405440
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check data record references index: 2
- check data record references index: 3
- check record links
myisamchk: error: Record-count is not ok; is 5773841      Should be: 654724
myisamchk: warning: Found 484145612 deleted space.   Should be 0
myisamchk: warning: Found    1581466 deleted blocks       Should be: 0
myisamchk: warning: Found    8799790 key parts. Should be: 654724
MyISAM-table 'MA_TABLE' is corrupted
Fix it using switch "-r" or "-o"
# myisamchk -r -v MA_TABLE
- recovering (with sort) MyISAM-table 'MA_TABLE'
Data records: 654724
- Fixing index 1
  - Searching for keys, allocating buffer for 147793 keys
  - Merging 5773841 keys
  - Last merge and dumping keys
- Fixing index 2
  - Searching for keys, allocating buffer for 139720 keys
  - Merging 5773841 keys
  - Last merge and dumping keys
- Fixing index 3
  - Searching for keys, allocating buffer for 2604 keys
  - Merging 5773841 keys
  - Last merge and dumping keys
Data records: 5773841

Ça remarche ! et même à chaud sans redémarrer mysql ! J’ai même l’impression que ça a été plus rapide de faire cette manip que d’attendre la fin de la requête d’optimisation…

Autre cas un peu plus complexe ou il a fallu rajouté un –force pour que ça passe. 30 minutes environ de traitement et ça remarche.

# myisamchk statistiques
Checking MyISAM file: statistiques
Data records: 43197952   Deleted blocks:       0
myisamchk: warning: Table is marked as crashed and last repair failed
myisamchk: warning: 1 client is using or hasn't closed the table properly
- check file-size
myisamchk: warning: Size of indexfile is: 2134406144      Should be: 1024
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
myisamchk: Unknown error 126
myisamchk: error: Can't read indexpage from filepos: -1
- check record links
myisamchk: error: Wrong bytesec: 0-0-0 at linkstart: 10578706084
MyISAM-table 'statistiques' is corrupted
Fix it using switch "-r" or "-o"


# myisamchk -r -v statistiques
- recovering (with sort) MyISAM-table 'statistiques'
Data records: 43197952
myisamchk: error: Can't create new tempfile: 'statistiques.TMD'
MyISAM-table 'statistiques' is not fixed because of errors
Try fixing it by using the --safe-recover (-o), the --force (-f) option or by not using the --quick (-q) flag

# myisamchk -f -r -v statistiques
- recovering (with sort) MyISAM-table 'statistiques'
Data records: 43197952
- Fixing index 1
  - Searching for keys, allocating buffer for 102782 keys
Wrong bytesec:   0-  0-  0 at 10578706084; Skipped
Wrong bytesec:   0-  0-  0 at 10578706104; Skipped
Wrong bytesec:   0-  0-  0 at 10578706124; Skipped
Wrong bytesec:   0-  0-  0 at 10578706144; Skipped
Wrong bytesec:   0-  0-  0 at 10578706164; Skipped
Wrong bytesec:   0-  0-  0 at 10578706184; Skipped
Wrong bytesec:   0-  0-  0 at 10578706204; Skipped
Wrong bytesec:   0-  0-  0 at 10578706224; Skipped
Wrong bytesec:   0-  0-  0 at 10578706244; Skipped
Wrong bytesec:   0-  0-  0 at 10578706264; Skipped
Wrong bytesec:   0-  0-  0 at 10578706284; Skipped
Wrong bytesec:   0-  0-  0 at 10578706304; Skipped
Wrong bytesec:   0-  0-  0 at 10578706324; Skipped
Wrong bytesec:   0-  0-  0 at 10578706344; Skipped
Wrong bytesec:   0-  0-  0 at 10578706364; Skipped
Wrong bytesec:   0-  0-  0 at 10578706384; Skipped
Wrong bytesec:   0-  0-  0 at 10578706404; Skipped
Wrong bytesec:   0-  0-  0 at 10578706424; Skipped
  - Merging 43197952 keys
  - Last merge and dumping keys
- Fixing index 2
  - Searching for keys, allocating buffer for 94325 keys
  - Merging 43197952 keys
  - Last merge and dumping keys
- Fixing index 3
  - Searching for keys, allocating buffer for 16873 keys
  - Merging 43197952 keys
  - Last merge and dumping keys
- Fixing index 4
  - Searching for keys, allocating buffer for 115510 keys
  - Merging 43197952 keys
  - Last merge and dumping keys
- Fixing index 5
  - Searching for keys, allocating buffer for 66644 keys
  - Merging 43197952 keys
  - Last merge and dumping keys

Strict Mode

Bon à savoir si jamais vous copiez des dates en 0000-00-00 et que vous avez une erreur

SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect date value: '0000-00-00' for column...

Strict mode affects whether the server permits ‘0000-00-00’ as a valid date: If strict mode is not enabled, ‘0000-00-00’ is permitted and inserts produce no warning. If strict mode is enabled, ‘0000-00-00’ is not permitted and inserts produce an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, ‘0000-00-00’ is permitted and inserts produce a warning.

Log binaire

Utiliser des log binaire pour mettre à jour une base de données distante après avoir fait un mysqldump

Vérifier si les log binaires sont activé

MariaDB [(none)]> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       328 |
+------------------+-----------+
1 row in set (0.000 sec)


MariaDB [(none)]> show variables like 'log_bin%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/log/mysql/mysql-bin       |
| log_bin_compress                | OFF                            |
| log_bin_compress_min_len        | 256                            |
| log_bin_index                   | /var/log/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                            |
+---------------------------------+--------------------------------+
6 rows in set (0.001 sec)

Si non

mysql> SHOW BINARY LOGS;
ERROR 1381 (HY000): You are not using binary logging

Pour activer dans le cas de mariaDB

vi /etc/mysql/mariadb.conf.d/50-server.cnf

Décommenter la ligne

log_bin                = /var/log/mysql/mysql-bin.log

et restarter mariaDB

systemctl restart mariadb.service

Vos fichiers de log binaires sont maintenant stocké dans /var/log/mysql/

mysqlbinlog permet de lire les fichiers de log binaire et de générer une sortie sql

# mysqlbinlog mysql-bin.000001 
mysqlbinlog: unknown variable 'default-character-set=utf8mb4'

---------- Si vous avez ce message d'erreur, utilisez l'option suivante

# mysqlbinlog --no-defaults mysql-bin.000001 

Filtre possible:

example de restauration

mysqlbinlog /var/log/mysql/mysql-bin.[0-9]* | mysql -u root -p
mysqlbinlog --no-defaults --start-datetime="2019-09-09 12:15:00" /var/log/mysql/mysql-bin.[0-9]* | mysql -u root -p

Par défaut, mysql s’arrête dès la 1ère erreur (par ex: duplicate entry), pour continuer après une erreur, ajouté l’option -f

mysqlbinlog --no-defaults --start-datetime="2019-09-09 12:15:00" /var/log/mysql/mysql-bin.[0-9]* | mysql -u root -p -f

Auto increment

On peut réinitialiser la valeur de l’auto increment d’un id avec la requete

ALTER TABLE `users` AUTO_INCREMENT = 1;

Mysql prendra automatiquement le max(id) + 1

Pour réinitialiser complètement une colonne d’id

ALTER TABLE `users` DROP `id`;
ALTER TABLE `users` AUTO_INCREMENT = 1;
ALTER TABLE `users` ADD `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

Si votre id est utilisé dans d’autres tables, il convient de définir les clés étrangères sur les autres tables avec l’option ON UPDATE CASCADE plutot que l’option par défaut ON UPDATE NO ACTION

SET @COUNT = 0;
UPDATE `users` SET `users`.`id` = @COUNT:= @COUNT + 1;
ALTER TABLE `users` AUTO_INCREMENT = 1;