sql:mysql [Notes]

Cette page est en lecture seule. Vous pouvez afficher le texte source, mais ne pourrez pas le modifier. Contactez votre administrateur si vous pensez qu'il s'agit d'une erreur.
====== MySQL ======

  * [[sql:|SQL]]
  * [[python:pymysql|PyMySQL]]

Les logs sont par défaut dans ''/var/log/mysql/''.

Le fichier de configuration est par défaut ''/etc/mysql/my.cnf''



==== Serveur MySQL ====

Installer le paquet ''mysql-server''. L'alternative semble maintenant être ''mariadb-server'' et ''mariadb-client''. Si utilisation avec PHP, également le paquet ''php-mysql'' (valable également pour MariaDB).

Pour configurer la sécurité du serveur, notamment définir le mot de passe //root//, lancer la commande ''mysql_secure_installation''.



==== Client MySQL ====
Installer le paquet ''mysql-client''. La commande est ensuite ''mysql''.

Options:
  * ''-h'': préciser l'adresse de l'hôte. Par défaut, essaie la socket locale.
  * ''-u <user>''
  * ''-p'': affichera un prompt pour saisir le mot de passe
  * ''--ssl'' utilise SSL
  * ''<database>''


=== Exécuter des commandes depuis un fichier ===

<code bash>
mysql <database> < script.sql
</code>


=== Exécuter des commandes sans lancer la CLI ===

<code bash>
mysql <database> -e "SELECT * FROM table"
</code>




==== CLI ====

=== Lister les bases de données ===

<code sql>
show databases;
</code>

=== Sélectionner une base de données ===

<code sql>
use <database>;
</code>

=== Lister les tables ===

<code sql>
show tables;
</code>


=== Décrire la structure d'une table ===

<code sql>
DESC table_name;
</code>

Ou pour avoir la commande SQL qui permet de créer la table en question :
<code sql>
SHOW CREATE TABLE <table_name>;
</code>


=== Obtenir les informations sur le serveur ===

<code sql>
\s
</code>


=== Vider une base de données ===

<code sql>
DROP DATABASE db;
CREATE DATABASE db;
</code>


=== Connaître l'espace occupé par les tables ===

[[https://stackoverflow.com/questions/6474591/how-can-you-determine-how-much-disk-space-a-particular-mysql-table-is-taking-up|Source]]

En Mo :
<code sql>
SELECT table_name, ROUND((data_length+index_length)/power(1024,2), 2) tablesize_mb FROM information_schema.tables WHERE table_schema='mydb' ORDER BY table_name;
</code>


=== Afficher les paramètres de configuration ===

[[https://stackoverflow.com/questions/1493722/mysql-command-for-showing-current-configuration-variables|Source]]
<code sql>
SHOW VARIABLES LIKE '%max%';
</code>


=== Connaître le moteur de stockage utilisé pour les tables ===

[[https://stackoverflow.com/questions/213543/how-can-i-check-mysql-engine-type-for-a-specific-table|Source]]
<code sql>
SHOW TABLE STATUS WHERE Name = 'xxx'
</code>


==== Dupliquer une base de données  ====

Créer la base de données de destination, puis ([[https://stackoverflow.com/questions/675289/mysql-cloning-a-mysql-database-on-the-same-mysql-instance|source]]) :
<code bash>
mysqldump db_src | mysql db_dest
</code>


==== Configurer SSL avec Let's Encrypt ====

  * [[http://delatbabel.blogspot.com/2017/03/lets-encrypt-ssl-recipes-mysql-with.html]]
  * [[https://alephnull.uk/configure-mysql-mariadb-ssl-tls-debian-ubuntu]]

<code bash>
cp /etc/letsencrypt/live/mysite.com/{chain,cert}.pem /var/lib/mysql/
openssl rsa -in /etc/letsencrypt/live/mysite.com/privkey.pem -out /var/lib/mysql/privkey.pem
chmod 400 /var/lib/mysql/*.pem
chown mysql:mysql /var/lib/mysql/*.pem
</code>

Ajouter dans le fichier de configuration de MySQL:
<file conf /etc/mysql/my.cnf>
ssl_ca=/var/lib/mysql/chain.pem
ssl_cert=/var/lib/mysql/cert.pem
ssl_key=/var/lib/mysql/privkey.pem
</file>

Redémarrer MySQL:
<code bash>
service mysql restart
</code>
Les erreurs concernant SSL sont rapportées dans le fichier ''/var/log/mysql/errors.log''.

Pour s'assurer que SSL est bien configuré:
<code bash>
mysql --ssl
</code>

Puis demander:
<code sql>
SHOW STATUS LIKE 'Ssl_cipher';
</code>
La valeur ne doit pas être vide.



==== PhpMyAdmin ====

=== Connexion interdite avec root ===

[[https://askubuntu.com/a/1059308|Source]]

Si PhpMyAdmin refuse de se connecter avec l'utilisateur //root//:

Dans un shell MySQL, connecté en tant que //root//:
<code sql>
SELECT User, Host, plugin FROM mysql.user;
</code>
Si cette commande renvoie autre chose que ''mysql_native_password'', exécuter les commandes suivantes:
<code sql>
USE mysql;
UPDATE user SET plugin='mysql_native_password' WHERE User='root';
FLUSH PRIVILEGES;
</code>
Fonctionne avec MySQL 5.5. À voir si cela persiste avec les mises à jour.

La meilleure solution semble être la création d'un utilisateur dédié à PhpMyAdmin




==== Installer MySQL 5.7 sur Debian 8 Jessie ====

[[https://tecadmin.net/install-mysql-debian-8-jessie/|Source]]

Télécharger le paquet de MySQL pour l'installation via APT: https://dev.mysql.com/downloads/repo/apt/.

Installer le paquet avec ''dpkg''.

<code bash>
apt update
apt install mysql-community-server
</code>

En root, en s'assurant que MySQL fonctionne, lancer la commande suivante:
<code bash>
mysql_upgrade -p
</code>


==== Utilisateurs ====

=== Problème d'accès avec debian-sys-maint ===

[[https://stackoverflow.com/questions/11644300/access-denied-for-user-debian-sys-maint|Source]]

Si ''mysql_upgrade'' échoue avec un problème d'accès pour l'utilisateur ''debian-sys-maint'', il faut probablement corriger le mot de passe MySQL de cet utilisateur:
  - le mot de passe est dans ''/etc/mysql/debian.cnf''
  - dans une console MySQL root: ''sudo mysql'':<code mysql>
ALTER USER 'debian-sys-maint'@'localhost' IDENTIFIED BY '<mdp>';
</code>


=== Mot de passe root ===

  * https://stackoverflow.com/questions/75978151/connect-to-mariadb-with-sudo-without-password
  * https://www.digitalocean.com/community/tutorials/how-to-reset-your-mysql-or-mariadb-root-password
  * https://www.digitalocean.com/community/tutorials/how-to-install-mariadb-on-debian-11
  * https://linuxconfig.org/how-to-change-mariadb-user-password
  * https://linuxize.com/post/how-to-reset-a-mysql-root-password/

=== Supprimer un utilisateur ===

[[https://dev.mysql.com/doc/refman/5.6/en/drop-user.html|Source]]

<code sql>
DROP USER 'jeffrey'@'localhost';
</code>

=== Créer un utilisateur ===

Avec tous les droits sur toutes les bases de données ([[https://www.digitalocean.com/community/tutorials/how-to-create-a-new-user-and-grant-permissions-in-mysql|source]]) :
<code sql>
CREATE USER 'user'@'localhost' IDENTIFIED BY 'motdepasse';
GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost';
FLUSH PRIVILEGES;
</code>

Pour qu'il n'y ait pas de mot de passe ([[https://linuxconfig.org/mysql-allow-empty-password|source]]) :
<code sql>
CREATE USER 'user'@'localhost' IDENTIFIED BY '';
</code>

Sans doute que cet utilisateur ne peut se connecter qu'en TCP et pas via la socket (à tester...).

=== Voir comment un utilisateur a été créé ===

<code sql>
SHOW CREATE USER user@localhost;
</code>



==== Sauvegardes ====

''mariadb-backup'' permet d'exporter la structure et les données d'un serveur MariaDB.


=== Mariabackup ===

  * [[https://github.com/omegazeng/run-mariabackup]]

Installer le paquet ''mariadb-backup''.

Sauvegardes incrémentales et compressées :
<code bash>
# Sauvegardes :
mkdir 20220306-1230-full # il faut créer le dossier avant
mariabackup --backup --stream=xbstream --extra-lsndir=$(pwd)/20220306-1230-full | gzip > $(pwd)/20220306-1230-full/backup.stream.gz
mkdir 20220306-1408
mariabackup --backup --stream=xbstream --extra-lsndir=$(pwd)/20220306-1540 --incremental-basedir=$(pwd)/20220306-1408/ | gzip > $(pwd)/20220306-1540/backup.stream.gz 

# Restauration :
cd 20220306-1230-full
mkdir backup
gunzip -c backup.stream.gz | mbstream -x -C backup/
cd ../20220306-1408
mkdir backup
gunzip -c backup.stream.gz | mbstream -x -C backup/
cd ..
mariabackup --prepare --target-dir 20220306-1230-full/backup
mariabackup --prepare --target-dir 20220306-1230-full/backup --incremental-dir 20220306-1408/backup
service mysql stop
mv /var/lib/mysql{,.old}
mariabackup --copy-back --target-dir 20220306-1230-full/backup/
chown -R mysql:mysql /var/lib/mysql
service mysql start
</code>



==== Table de verrous trop petite ====

[[https://stackoverflow.com/questions/6901108/the-total-number-of-locks-exceeds-the-lock-table-size|Source]]

Dans ''my.cnf'', augmenter la valeur de ''innodb_buffer_pool_size'' à ''64MB'' par exemple.



==== Optimisation des tables ====

Les ''DELETE'' ne libèrent pas de la place sur le disque. Il faut exécuter : 
<code sql>
OPTIMIZE TABLE table;
</code>

  * [[https://github.com/major/MySQLTuner-perl|MySQLTuner]]