====== 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 '' * ''-p'': affichera un prompt pour saisir le mot de passe * ''--ssl'' utilise SSL * '''' === Exécuter des commandes depuis un fichier === mysql < script.sql === Exécuter des commandes sans lancer la CLI === mysql -e "SELECT * FROM table" ==== CLI ==== === Lister les bases de données === show databases; === Sélectionner une base de données === use ; === Lister les tables === show tables; === Décrire la structure d'une table === DESC table_name; Ou pour avoir la commande SQL qui permet de créer la table en question : SHOW CREATE TABLE ; === Obtenir les informations sur le serveur === \s === Vider une base de données === DROP DATABASE db; CREATE DATABASE db; === 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 : 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; === Afficher les paramètres de configuration === [[https://stackoverflow.com/questions/1493722/mysql-command-for-showing-current-configuration-variables|Source]] SHOW VARIABLES LIKE '%max%'; === 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]] SHOW TABLE STATUS WHERE Name = 'xxx' ==== 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]]) : mysqldump db_src | mysql db_dest ==== 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]] 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 Ajouter dans le fichier de configuration de MySQL: ssl_ca=/var/lib/mysql/chain.pem ssl_cert=/var/lib/mysql/cert.pem ssl_key=/var/lib/mysql/privkey.pem Redémarrer MySQL: service mysql restart Les erreurs concernant SSL sont rapportées dans le fichier ''/var/log/mysql/errors.log''. Pour s'assurer que SSL est bien configuré: mysql --ssl Puis demander: SHOW STATUS LIKE 'Ssl_cipher'; 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//: SELECT User, Host, plugin FROM mysql.user; Si cette commande renvoie autre chose que ''mysql_native_password'', exécuter les commandes suivantes: USE mysql; UPDATE user SET plugin='mysql_native_password' WHERE User='root'; FLUSH PRIVILEGES; 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''. apt update apt install mysql-community-server En root, en s'assurant que MySQL fonctionne, lancer la commande suivante: mysql_upgrade -p ==== 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'': ALTER USER 'debian-sys-maint'@'localhost' IDENTIFIED BY ''; === 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]] DROP USER 'jeffrey'@'localhost'; === 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]]) : CREATE USER 'user'@'localhost' IDENTIFIED BY 'motdepasse'; GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost'; FLUSH PRIVILEGES; Pour qu'il n'y ait pas de mot de passe ([[https://linuxconfig.org/mysql-allow-empty-password|source]]) : CREATE USER 'user'@'localhost' IDENTIFIED BY ''; 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éé === SHOW CREATE USER user@localhost; ==== 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 : # 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 ==== 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 : OPTIMIZE TABLE table; * [[https://github.com/major/MySQLTuner-perl|MySQLTuner]]