…et virer la base test par défaut

sudo mysql_secure_installation

… et suivre les instructions

mysqldump --add_drop_table -c -C -e -q -Q -u<user> -p"<password>" -B <BDDNAME> | gzip > dump.sql.gz

mysql -u root -p"<password>" --skip-column-names -B -e "show databases" | grep -v "_schema" | grep -v "mysql"

mysql -u root -p"<password>" < dump.sql

zcat dump.sql.gz | mysql -u root -p"<password>"

Options supplémentaires classiques pour la commande mysql :

• -h <host>
• -B <database>

mysql --password=<password> --user=root -e "show processlist\G" | grep Info | grep -v processlist | grep -v "Info: NULL";

On stoppe le service et on efface tout le répertoire contenant les bases

systemctl stop mariadb
rm -fr /var/lib/mysql/*

On recréé le répertoire proprement et on redémarre le tout :

mysql_install_db --datadir=/var/lib/mysql --user=mysql
chown -R mysql:mysql /var/lib/mysql
systemctl start mariadb

Et on n'oublie pas de reconfigurer correctment les accès :

sudo mysql_secure_installation

mysqlcheck ( --check | --auto-repair | --repair | --optimize ) --all-databases

• –check : Vérifier
• –check –auto-repair : Vérifier et réparer si il y a des problèmes
• –repair : Réparer
• –optimize : Optimiser

Cette requête change l'encodage de la base, de toutes les tables, et de tous les champs textuels de chaque table.

• name_of_your_db = le nom de la db
• Ici, l'exemple convertit tout en utf8mb4_bin

USE information_schema;
SELECT CONCAT("ALTER DATABASE `",table_schema,"` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin;") AS _sql
FROM `TABLES` WHERE table_schema LIKE "name_of_your_db" AND TABLE_TYPE='BASE TABLE' GROUP BY table_schema UNION
SELECT CONCAT("ALTER TABLE `",table_schema,"`.`",table_name,"` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;") AS _sql  
FROM `TABLES` WHERE table_schema LIKE "name_of_your_db" AND TABLE_TYPE='BASE TABLE' GROUP BY table_schema, table_name UNION
SELECT CONCAT("ALTER TABLE `",`COLUMNS`.table_schema,"`.`",`COLUMNS`.table_name, "` CHANGE `",column_name,"` `",column_name,"` ",data_type,"(",character_maximum_length,") CHARACTER SET utf8mb4 COLLATE utf8mb4_bin",IF(is_nullable="YES"," NULL"," NOT NULL"),";") AS _sql 
FROM `COLUMNS` INNER JOIN `TABLES` ON `TABLES`.table_name = `COLUMNS`.table_name WHERE `COLUMNS`.table_schema like "name_of_your_db" and data_type in ('varchar','char') AND TABLE_TYPE='BASE TABLE' UNION
SELECT CONCAT("ALTER TABLE `",`COLUMNS`.table_schema,"`.`",`COLUMNS`.table_name, "` CHANGE `",column_name,"` `",column_name,"` ",data_type," CHARACTER SET utf8mb4 COLLATE utf8mb4_bin",IF(is_nullable="YES"," NULL"," NOT NULL"),";") AS _sql 
FROM `COLUMNS` INNER JOIN `TABLES` ON `TABLES`.table_name = `COLUMNS`.table_name WHERE `COLUMNS`.table_schema like "name_of_your_db" and data_type in ('text','tinytext','mediumtext','longtext') AND TABLE_TYPE='BASE TABLE';

Cette requête genère les commandes ALTER à exécuter par la suite :)

• name_of_your_db = le nom de la db
• Ici, l'exemple convertit tout en InnoDB

SELECT  CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements
FROM    information_schema.tables AS tb
WHERE   table_schema = 'name_of_your_db'
AND     `ENGINE` != 'InnoDB'
AND     `TABLE_TYPE` = 'BASE TABLE'
ORDER BY table_name DESC;

Cette requête genère les commandes ALTER à exécuter par la suite :)

Cette requête affiche le résultat trié par Champ, sauf les valeurs valeur qui seront retournées en dernier :

SELECT * FROM table ORDER BY FIELD(Champ,'valeur'),Champ

Pour faire pareil, en mettant valeur en premier :

SELECT * FROM table ORDER BY FIELD(Champ,'valeur') DESC,Champ

On peut utiliser plusieurs valeurs :

SELECT * FROM table ORDER BY FIELD(Champ,'valeur3','valeur2','valeur1') DESC,Champ

Si on veut remplir une table déjà pleine avec des valeurs incrémentales :

UPDATE table
CROSS JOIN (SELECT @id := 0 ) AS a
SET Champ = @id := @id + 1
WHERE Champ =0;