Wiki CgX

Parce que j'ai un cerveau, mais pas trop.

Outils pour utilisateurs

Outils du site


it:linux-selfhosting:mysql

MySQL / MariaDB

Côté Adminsys Serveur

Reconfigurer les accès root

…et virer la base test par défaut

sudo mysql_secure_installation

… et suivre les instructions

Dump de base + Gzip

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

Lister les bases

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

Importer une base

Décompréssée

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

Gzippée

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

Options supplémentaires classiques pour la commande mysql :

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

Processes en cours

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

Repartir avec une base clean (et vide)

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

Vérifier/Réparer/Optimiser des bases de données

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

Côté SQL

Changer l'encodage de tous les éléments d'une base

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

Changer le moteur de toutes les tables d'une base

  • 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 :)

Trier des élements...sauf certains !

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,'valeur','valeur2','valeur3') DESC,Champ
it/linux-selfhosting/mysql.txt · Dernière modification: 15 Apr 2022 :: 16:38 de CgX