Wiki CgX

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

Outils pour utilisateurs

Outils du site


it:auto-hebergement:mysql

Ceci est une ancienne révision du document !


MySQL / MariaDB

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>"

Processes en cours

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

Changer le moteur de toutes les tables d'une base

* name_of_your_db = le nom de la db

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` = 'MyISAM'
AND     `TABLE_TYPE` = 'BASE TABLE'
ORDER BY table_name DESC;

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

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

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
it/auto-hebergement/mysql.1626793444.txt.gz · Dernière modification: 20 Jul 2021 :: 17:04 de CgX