MariaDB MySQL

Use DB ou prefixe

Use

Pour utiliser une table il est possible de se placer sur la DB souhaitée avec USE xxx; puis executer les commandes dessus :

USE mysql;
SELECT user FROM user;

L'interet de cette syntaxe, est qu'en mode interactif, cela nous évite de tapper le préfixe à chaque commande pour une succession de commandes concernant la meme table.

Prefixe

il est possible de prefixer le nom des tables par le nom de la base :

SELECT user FROM mysql.user;

L'interet de cette seconde syntaxe est qu'elle est plus rapide pour exécuter 1 seule commtande sur une autre table comme ci-dessus sur une table système.

Afficher des infos

Schéma BDD

Les 3 commandes suivantes sont équivalente pour obtenir les schéma de la BDD courante:

SHOW DATABASES;
SHOW SCHEMAS;
SELECT schema_name FROM information_schema.schemata;

attention si l'option --skip-show-database a été utilisé pour lancer le serveur, aucune info ne sera retournée à moins d'avoir le privilege SHOW DATABASES.

Filtrer

Pour affiner les recherches utliser l'opérateur LIKE :

SHOW DATABASES LIKE "wp%";

Tables

Pour lister les tables

SHOW TABLES;
SHOW FULL TABLES;  --montre le table_type "BASE TABLE" ou "VIEW"

Filtrer avec LIKE ou WHERE :

SHOW FULL TABLES WHERE table_type = 'VIEW';
SHOW FULL TABLES LIKE "%post%";

Pour avoir des infos sur la structure d'une table

DESC mysql.user;
DESCRIBE mysql.user;
SHOW COLUMNS FROM mysql.user;
SHOW FULL COLUMNS FROM mysql.user;

Le mot clé FULL ajoute les champs collation, privileges et comment

Filtrer

Filtrer SHOW [FULL] COLUMNS avec LIKE ou WHERE :

SHOW COLUMNS FROM wp_blog_behrouze.wp_posts LIKE "post%";
SHOW COLUMNS FROM wp_blog_behrouze.wp_posts WHERE Type LIKE "text";

Users

Utilisateur courant

select user();
select current_user();

+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.001 sec)

Liste des utilisateurs de la BDD

SELECT user, host FROM mysql.user;

SELECT user,
    host,
    is_role,
    password_expired,
    Password
FROM mysql.user;

Permissions

Les Users sont créés de base sans aucun droits, c'est le GRANT USAGE , cela leurs permets uniquement de se logger.

Pour checker les permissions du user avec lequel on s'est connecté:

SHOW GRANTS;  -- équivalent à SHOW GRANTS FOR CURRENT_USER; ou CURRENT_USER();

Pour lister les users de la BDD (et avoir la combinaison user@host) :

SHOW GRANTS FOR le_user_a_checker@le_host;

A noter qu'il est possible d'écrire:

'username'@'hostname'
"username"@"hostname"
`username`@`hostname`

Threads courants

Les 2 commandes suivantes sont équivalentes:

SHOW PROCESSLIST;
SELECT id,
    user,
    host,
    db,
    command,
    time,
    State,
    Info,
    Progress
FROM information_schema.processlist;

Créations

User

CREATE USER [IF NOT EXISTS] user_a_creer
IDENTIFIED BY 'password';

user_a_creer peut etre une combinaison nom_user@host ou juste nom_user auquel cas le host attribué est % automatiquement. La partie host designe la machine depuis laquel ce user pourra se connecter, et % signifie depuis partout (donc attention on permet les remote access) !

Pour modifier le mot de passe du user précédement créé :

ALTER USER user_a_creer IDENTIFIED BY 'mon_nouveau_password';

⚠ Attention si on utilise la syntaxe IDENTIFIED BY PASSWORD au lieu de IDENTIFIED BY, le SGDBD s'attendra a recevoir un hash de password que l'on obtiendra grâce à la fonction PASSWORD par un SELECT PASSWORD('mon_super_password'); ce qu'il nous fera comprendre par un message :

ERROR 1372 (HY000): Password hash should be a 41-digit hexadecimal number

Permissions

Une fois le User créé il faut lui attribuer des droits pour qu'il puisse interagir avec la BDD. Les permissions (ou droits) sont associés à la database, un table, une routine, ou à des columns particulière d'une table. L'attribution de ces droits se fait a l'aide de l'instruction GRANT, et on attribue le droit XXX sur l'élément de BDD à l'utilisateur nom_user comme ceci :

GRANT XXX 
ON la_bdd_voulue.* 
TO nom_user;

avec XXX le ou les privileges a attribuer, avec les plus courants: ALL PRIVILEGES, SELECT, INSERT, UPDATE, DELETE, INDEX, GRANT OPTION

et on vérifie les permissions attribuées avec un SHOW GRANTS FOR... voir

Permissions particulières

ALL PRIVILEGES

avec la permission ALL PRIVILEGES il nous est possible de toutes les attribuer (en dehors de GRANT OPTION) en une fois :

GRANT ALL PRIVILEGES ON  *.* to 'nom_user'@'localhost';

USAGE

C'est la permission qui est attribuée par défaut à la création d'un utilisateur

MariaDB [(none)]> CREATE USER toto IDENTIFIED BY 'mon_super_password';
Query OK, 0 rows affected (0.007 sec)

MariaDB [(none)]> SHOW GRANTS FOR toto@'%';
+--------------------------------------------------------+
| Grants for toto@%                                                                                   |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO `toto`@`%` IDENTIFIED BY PASSWORD '*A62D57DD7D1835DC24EE4D41B4400E32C40B46B6' |
+--------------------------------------------------------+
1 row in set (0.001 sec)

GRANT OPTION

Le privilège GRANT OPTION qui attribut le droit de modifier les permissions (à la hauteur des permission de l'utilisateur selectionné) n'est pas inclus dans le ALL PRIVILEGES.

Pour attribuer ce droit particulier il faut compléter la requete précédente ainsi :

GRANT ALL PRIVILEGES ON  *.* to 'nom_user'@'localhost' WITH GRANT OPTION;

Connexion distant

Il faut tout d'abord disposer d'un client mysql : sudo apt install mariadb-client -y Pour autoriser les connexion distantes il faut intervenir sur la configuration réseau du serveur, dans la config mariadb et dans le paramétrage de la base de données. Une fois le client installé, se connecter avec les credentials du user (créé pour une connexion distante avec soit % soit l'adresse IP en tant que host):

mysql -h host_name -u mon_user -p

config Serveur

Coté serveur vérifier que le port mariadb/mysql 3306 (ou celui que vous aurez défini dans /etc/mysql/mariadb.cnf) est bien ouvert au niveau réseau :

vérifier les config du firewall local ufw ou iptables -L
vérifier que mysql écoute bien sur le port 3306 avec un netstat -tupln

config mariadb

autoriser les connexions distantes au daemon mariadbd dans le fichier de config du serveur : /etc/mysql/mariadb.conf.d/50-server.cnf

bind-address            = 127.0.0.1 # interdit les connexions distantes
bind-address            = 0.0.0.0   # autorise les connexions distantes

redémarrer le service :

sudo systemctl restart mariadb

config en BDD

Attribuer les droits (pas forcément ALL) au couple username@host à l'entité de BDD souhaitée:

GRANT ALL ON la_bdd.* to 'username'@'hostname' IDENTIFIED BY 'password'

Le FLUSH PRIVILEGES n'est pas nécessaire pour appliquer les modifs comme indiqué dans la doc.

Astuces

Affichage en colonne

Pour un affichage plus lisible, surtout lors d'un select * mysql nous offre la possibilité d'afficher en mode colonne en finalisant la commande par \G; :

SELECT * FROM mysql.user WHERE User LIKE 'usr_%' \G;

Mode interactif

CTRL + U ou CTRL + w supprime les caractères précédent le curseur, donc tout ce qui est a sa gauche CTRL + K idem pour les caratères à droite jusqu'à la fin de ligne CTRL + L pour clear l'affichage

MariaDB possède un ensemble de "commandes" exécutés dans une session interactive en saisissant par ex \? + ENTER pour obtenir l'accès à l'aide en ligne.

Les commandes intéressantes sont : \G affichage en colonne/vertical \e pour editer la commande courante dans l'$EDITOR \. script.sql pour exécuter le script en argument \s status du serveur \! cmd execute une commande shell \T fichier.log permet de spécifier un fichier dans lequel toute la session sera loggée

Historique

l'historique des commandes exécutée en mode interactif est conservé dans le fichier .mysql_history situé dans le home de l'utilisateur. Pour info ce fichier est encodé

Please look at the ".mysql_history" file as file with a binary format, managed by the MariaDB or MySQL application

CTRL + R nous permet de chercher dans l'historique des commandes passées. Une fois cette combinaison de touche entrée, on entre dans le mode Recherche dans l'historique de MySQL. La navigation s'effectue ensuite via CTRL + R (ancien) et CTRL + S (récent).

Exécution de script

mysql db_name < script.sql > output.tab

voir toutes les options de la CLI mysql

Backup à la barbare

La carte micro-sd d'un de mes Raspberry Pie a laché, mais heureusement pour moi elle était encore lisible (mais dead en écriture).

Le backup à la barbare consiste à récupérer le contenu du dossier /var/lib/mysql sur la carte cramée puis sur le nouveau serveur (ayant une installation de mariadb) :

arreter l'instance de mariadb avec sudo systemctl stop mariadb
remplacer le dossier /var/lib/mysql local par celui de l'ancien serveur
supprimer les ib_logfiles rm /var/lib/mysql/ib_logfile* sans quoi on aura des erreurs du type [ERROR] Unknown/unsupported storage engine: InnoDB lors de l'accès aux données
sudo systemctl start mariadb

Liens

MySQLTutorial.org site de tuto très clair 🌟 avec de nombreux exemples
MySQL docs
MariaDB docs (pas très ergonomique cette doc 😵)

sql

mariadb

mysql

tips

cli

cheatsheet

devops

rédigé le 16/02/22 par Behrouze

Développeur FullStack & Devops

Notes et mémos techniques

MariaDB MySQL

Use DB ou prefixe

Use

Prefixe

Afficher des infos

Schéma BDD

Filtrer

Tables

Filtrer

Users

Utilisateur courant

Liste des utilisateurs de la BDD

Permissions

Threads courants

Créations

User

Permissions

Permissions particulières

ALL PRIVILEGES

USAGE

GRANT OPTION

Connexion distant

config Serveur

config mariadb

config en BDD

Astuces

Affichage en colonne

Mode interactif

Historique

Exécution de script

Backup à la barbare

Liens