Table des matières

MySQL & MariaDB

MySQL & MariaDB

Fichiers de configuration

Base de données d'entrainement : Sakila

MariaDB

Répertoires

Binaires : /usr/bin/mysql
Fichiers de configuration : /etc/mysql
Données (bases de données etc.) : /var/lib/mysql

Attention à la hierarchie des fichiers de configuration. Surtout avec le my.cnf Plus d'infos : https://mariadb.com/kb/en/library/configuring-mariadb-with-mycnf/

Liste des options de configuration : https://mariadb.com/kb/en/library/mysqld-options/

Diagnostics

Voir les bases de données présentes :

SHOW DATABASES;

Voir les tables d'une base :

SHOW TABLES FROM nom_base;

Voir les colonnes d'une table :

SHOW COLUMNS FROM nom_base.nom_table;

Identifier les commandes en cours :

mysql -u root -e "SHOW FULL PROCESSLIST\G"

Récupérer les variables liées à l'état du cluster :

mysql -u root -e "SHOW STATUS LIKE 'wsrep_%'"

Récupérer la taille des tables d'une base (en MB) :

SELECT table_name AS "Table", round(((data_length + index_length) / 1024 / 1024), 2) as size FROM information_schema.TABLES WHERE table_schema = "ma_base" ORDER BY size DESC;

Récupérer la valeur d'une variable de configuration (exemple innodb_buffer_pool_size) :

show variables like '%innodb_buffer_pool_size%';

Dump & import de base

Importer une base de donnée :

mysql -u username -pPlainPassword databasename < filename.sql
OU
zcat databasename.sql.gz | mysql -u username -pPlainPassword databasename

Dump de base sous mysql en la compressant :

mysqldump -u user -pmotdeppasse -h adresse.ip.hote --add-drop-table --add-locks nomdelabase | gzip > fichier.sql.gz

Possibilité de remplacer “–add-drop-table –add-locks” par –opt. Ajoute quelques options supplémentaires (voir la doc MariaDB sur cette option)

Il peut être recommandé d'ajouter un drop-databases dans les options de dump. Dans MariaDB, il faut ajouter un –databases après l'option –drop-databases. Commande pour un dump complet :

mysqldump --opt --add-drop-database --databases NOM-DB|gzip > dump_example.sql.gz

Gestion des tables

Voir le nombre de tables actuellement ouvertes :

show global status like 'open_tables';

Voir le nombre de tables ouvertes depuis le démarrage du service MariaDB :

show global status like 'opened_tables';

Fermer toutes les tables inutiles et non lockées:

flush tables;

Close tables given as options or all open tables if no table list was used. From MariaDB 10.4.1, using without any table list will only close tables not in use, and tables not locked by the FLUSH TABLES connection. If there are no locked tables, FLUSH TABLES will be instant and will not cause any waits, as it no longer waits for tables in use. When a table list is provided, from MariaDB 10.4.1, the server will wait for the end of any transactions that are using the tables. Previously, FLUSH TABLES only waited for the statements to complete.

https://mariadb.com/kb/en/flush/#

Gestion des utilisateurs et des droits

Pour rappel quelques commandes de bases :

Manipulation des utilisateurs

Lister les utilisateurs :

SELECT host, user, password FROM mysql.user;

Création d'un utilisateur :

CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';

Privilèges

Ajouter un privilège :

GRANT privileges ON object TO user;

Révoquer un privilège :

REVOKE privileges ON object FROM user;

Liste des privilèges :

CREATE - the ability to create new tables or databases
DROP - the ability to them to delete tables or databases
DELETE - the ability to delete rows from tables
INSERT - the ability to insert rows into tables
SELECT - the ability to use the SELECT command to read through databases
UPDATE - the ability to update table rows
GRANT OPTION - allows them to grant or remove other users’ privileges
ALL PRIVILEGES - all of the above.

Voir toutes les permissions :

SHOW GRANTS;

Voir les permissions du user actuel :

SHOW GRANTS FOR CURRENT_USER;

Voir les permissions d'un user SQL :

SHOW GRANTS FOR 'user'@'host';

Pour donner des permissions à un User alors qu'on n'a pas soit-même le privilège GRANT OPTION, effectuer une modification dans la table user dela base mysql pour se donner ce droit :

update user set Grant_priv = 'Y' where User='monuser';

Réplication Master-Slave

Pour éviter toute écriture sur le slave, il convient de passer la commande set GLOBAL read_only = ON;

Relance réplication

Voir le statut de la réplication: :

show slave status\G;
show master status\G

Relancer la réplication depuis le slave: :
```
stop slave;
start slave;
```

Divers

Supprimer une base de données

Sur une réplication Master-Slave afin de supprimer une base de données sans casser la réplication, placez vous sur le maître et exécutez :

mysql -e "DROP DATABASE IF EXISTS database_name"

En cas de suppression sur l'esclave exécutez la même commande sur le maître, la commande ne provoquera pas d'erreur sur le slave.

Si la réplication est prévue sur des schémas en particulier procédez de la sorte https://dba.stackexchange.com/questions/117059/mysql-replication-accidentally-dropped-database-on-slave

Supprimer une table

USE database_name;
DROP TABLE IF EXISTS TABLE_NAME;

Si la suppression prend beaucoup de temps, lister les processus en cours et supprimer tous les processus utilisant la table :

SHOW PROCESSLIST;
KILL process_id;

Wordpress

Changer l'URL dans une base de données de Wordpress :

UPDATE wp_options SET option_value = replace(option_value, 'http://www.oldurl', 'http://www.newurl') WHERE option_name = 'home' OR option_name = 'siteurl';

UPDATE wp_posts SET guid = replace(guid, 'http://www.oldurl','http://www.newurl');

UPDATE wp_posts SET post_content = replace(post_content, 'http://www.oldurl', 'http://www.newurl');

UPDATE wp_postmeta SET meta_value = replace(meta_value,'http://www.oldurl','http://www.newurl');