====== MySQL & MariaDB ======

===== Fichiers de configuration =====
<WRAP center round download 60%>
Base de données d'entrainement : {{ :sakila-full.sql.gz |Sakila}}
</WRAP>

==== MariaDB ====

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

<WRAP center round important 60%>
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/]]
</WRAP>


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

===== Diagnostics =====

Voir les bases de données présentes :
<code>
SHOW DATABASES;
</code>

Voir les tables d'une base :
<code>
SHOW TABLES FROM nom_base;
</code>

Voir les colonnes d'une table :
<code>
SHOW COLUMNS FROM nom_base.nom_table;
</code>

Identifier les commandes en cours :
<code>
mysql -u root -e "SHOW FULL PROCESSLIST\G"
</code>

Récupérer les variables liées à l'état du cluster :
<code>
mysql -u root -e "SHOW STATUS LIKE 'wsrep_%'"
</code>

Récupérer la taille des tables d'une base (en MB) :
<code>
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;
</code>

Récupérer la valeur d'une variable de configuration (exemple innodb_buffer_pool_size) :<code>show variables like '%innodb_buffer_pool_size%';</code>

===== Dump & import de base =====
Importer une base de donnée : <code>mysql -u username -pPlainPassword databasename < filename.sql
OU
zcat databasename.sql.gz | mysql -u username -pPlainPassword databasename</code>

Dump de base sous mysql en la compressant : <code>mysqldump -u user -pmotdeppasse -h adresse.ip.hote --add-drop-table --add-locks nomdelabase | gzip > fichier.sql.gz</code>

Possibilité de remplacer "--add-drop-table --add-locks" par --opt. Ajoute quelques options supplémentaires (voir la [[https://mariadb.com/kb/en/mysqldump/#options|doc MariaDB sur cette option]])

<WRAP center round tip 60%>
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 : <code>mysqldump --opt --add-drop-database --databases NOM-DB|gzip > dump_example.sql.gz</code>

</WRAP>

===== Gestion des tables =====
Voir le nombre de tables actuellement ouvertes : <code>show global status like 'open_tables';</code>

Voir le nombre de tables ouvertes depuis le démarrage du service MariaDB : <code>show global status like 'opened_tables';</code>

Fermer toutes les tables inutiles et non lockées: <code>flush tables;</code>
<WRAP center round info 60%>

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/#]]
</WRAP>

===== Gestion des utilisateurs et des droits =====
Pour rappel quelques commandes de bases :
==== Manipulation des utilisateurs ====

Lister les utilisateurs :
<code>
SELECT host, user, password FROM mysql.user;
</code>
Création d'un utilisateur :
<code>
CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';
</code>

==== Privilèges  ====
Ajouter un privilège :
<code>
GRANT privileges ON object TO user;
</code>
Révoquer un privilège :
<code>
REVOKE privileges ON object FROM user;
</code>
**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 :<code>SHOW GRANTS;</code>
Voir les permissions du user actuel :<code>SHOW GRANTS FOR CURRENT_USER;</code>
Voir les permissions d'un user SQL :<code>SHOW GRANTS FOR 'user'@'host';</code>
<WRAP center round tip 60%>
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 : <code>update user set Grant_priv = 'Y' where User='monuser';</code>
</WRAP>


===== Réplication Master-Slave =====
<WRAP center round tip 60%>
Pour éviter toute écriture sur le slave, il convient de passer la commande set GLOBAL read_only = ON;
</WRAP>


==== Relance réplication ====
  * Voir le statut de la réplication: : <code>show slave status\G;
show master status\G</code>
  * Relancer la réplication depuis le slave: : <code>
stop slave;
start slave;</code>
===== 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 :

<code>
mysql -e "DROP DATABASE IF EXISTS database_name"
</code>

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|https://dba.stackexchange.com/questions/117059/mysql-replication-accidentally-dropped-database-on-slave]]
==== Supprimer une table ====
<code sql>
USE database_name;
DROP TABLE IF EXISTS table_name;
</code>

Si la suppression prend beaucoup de temps, lister les processus en cours et supprimer tous les processus utilisant la table :
<code sql>
SHOW PROCESSLIST;
KILL process_id;</code>
==== Wordpress ====
Changer l'URL dans une base de données de Wordpress  : <code>
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');</code>