technique:mysql

Commandes MySQL

Se connecter à MySQL en ligne de commande sous Linux :

mysql ‐u nomutilisateur ‐p

Afficher les bases de données MySQL existantes :

SHOW DATABASES;

Se placer et utiliser une base de données :

USE nomdelabdd;

Afficher les tables de cette base de données :

SHOW TABLES;

Afficher la structure (colonnes) d'une table de la base de données :

DESCRIBE nomdelatable;

Supprimer une table

DROP TABLE nomdelatable;

Création de la base de données :

CREATE DATABASE nomdelabdd;

Supprimer la base de données :

DROP DATABASE nomdelabdd;

Création d'un utilisateur

create USER 'nomuser'@'localhost' IDENTIFIED BY 'motdepassesecret';

Attribution des privilèges complets sur une base :

GRANT ALL PRIVILEGES ON nombase.* TO 'nomuser'@'localhost';
flush privileges;

Attribution des privilèges en consultation :

GRANT SELECT ON nombase.* TO 'nomuser'@'localhost';
flush privileges;

Suppression d'un user :

DROP USER 'nomuser@localhost';

Création d'une table :

CREATE TABLE nomdelatable ( nomcolonne1 INT , nomcolonne2 FLOAT , nomcolonne3 VARCHAR ( 50 ), nomcolonne3 CHAR ( 50 ) );

Supprimer une table :

DROP TABLE nomdelatable ;

Ajouter une colonne à une table :

ALTER TABLE nomdelatable ADD nomdecolonne INT ;
ALTER TABLE nomdelatable ADD nomdecolonne1 VARCHAR ( 50 );
ALTER TABLE nomdelatable ADD COLUMN nomdelacolonne INT ;

Supprimer une colonne d'une table :

ALTER TABLE nomdelatable DROP COLUMN nomdecolonne1 ;

Renommer / changer le nom d'une colonne de la table :

ALTER TABLE nomdelatable RENAME AS lenouveaunomdelatable
ALTER TABLE nomancientable RENAME TO nomnouveautable ;
RENAME TABLE nomancientable TO nomnouveautable ;

Renommer / changer le type d'une colonne :

ALTER TABLE nomdelatable CHANGE nomdelacolonne nouveaunomdelacolonne INT ;

Ajouter une contrainte de clé primaire sur une colonne d'une table :

ALTER TABLE nomdelatable ADD PRIMARY KEY ( nomcolonne );
ALTER TABLE nomdelatable ADD CONSTRAINT pk_nomdelacontrainte PRIMARY KEY ( nomcolonne );

Supprimer une contrainte de clé primaire sur une colonne :

ALTER TABLE nomdelatable DROP PRIMARY KEY ;

Ajouter une contrainte de clé étrangère sur une colonne d'une table :

ALTER TABLE nomdelatable ADD CONSTRAINT fk_nomdelacontrainte FOREIGN KEY ( nomcolonne ) REFERENCES tabledereference ( nomcolonne );

Supprimer une contrainte de clé étrangère sur une colonne d'une table :

ALTER TABLE nomdelatable DROP CONSTRAINT fk_nomdelacontrainte ;

Afficher / sélectionner des enregistrements d'une table SQL :

SELECT nomcolonne FROM nomtable ;

Afficher / sélectionner des enregistrements d'une table SQL de façon distincte :

SELECT DISTINCT nomcolonne FROM nomtable ;

Afficher / sélectionner des enregistrements d'une table SQL avec une condition :

SELECT nomcolonne FROM nomtable WHERE nomcolonne = '1' ;
SELECT nomcolonne FROM nomtable WHERE nomcolonne LIKE '%.com' ;

Afficher / sélectionner des enregistrements de plusieurs tables via une jointure internet :

SELECT colonne1 , colonne2 , colonne3  FROM table1 INNER JOIN table2 ON table1 . colonne1 = table2 . colonne3 WHERE colonne1 = 'cequejecherche'

Mise à jour de l'ensemble des enregistrements sur une colonne d'une table :

UPDATE nomtable SET nomcolonne1 = 'mavaleur' , nomcolonne2 = 'mavaleur2'

Mise à jour de l'ensemble des enregistrements sur une colonne d'une table avec une condition :

UPDATE nomtable SET nomcolonne1 = 'mavaleur' , nomcolonne2 = 'mavaleur2' WHERE nomcolonne3 = '0'

Insérer un enregistrement dans une table :

INSERT INTO nomtable ( colonne1 , colonne2 , colonne3 ) VALUES ( valeu1 , valeur2 , valeur3 );

Supprimer un enregistrement dans une table :

DELETE FROM nomtable WHERE nomcolonne = 'lavaleur'

Trouver la valeur Maximal d'une colonne et ne garder que celle ligne :

SELECT MAX(macolonne) FROM YourTable

Pour sauvegarder toutes les bases de données on utilise la commande suivante :

mysqldump --user=mon_user --password=mon_password --all-databases> fichier_destination.sql

Pour sauvegarder une base de données précise :

mysqldump --user=mon_user --password=mon_password --databases nom_de_la_base> fichier_destination.sql

Pour sauvegarder plusieurs bases de données :

mysqldump --user=mon_user --password=mon_password --databases nom_de_la_base_1 nom_de_la_base_2> fichier_destination.sql

Pour sauvegarder une table précise :

mysqldump --user=mon_user --password=mon_password --databases nom_de_la_base --tables nom_de_la_table> fichier_destination.sql

Pour sauvegarder plusieurs tables :

mysqldump --user=mon_user --password=mon_password --databases nom_de_la_base --tables nom_de_la_table_1 nom_de_la_table_2> fichier_destination.sql

Pour tous ces types de sauvegarde mysql les fichiers générés sont en .sql. Cela dit on peut mettre l’extension que l’on veut comme .txt par exemple. Ca n’a pas d’importance. Les fichiers sont lisibles en clair et contiennent toutes les commandes SQL pour pouvoir faire une restauration mysql.

Cela dit les fichiers .sql sont volumineux. Pour gagner de la place, on va les compresser à la volée lors de la sauvegarde.

Pour compresser les données, nous allons utiliser gunzip après avoir invoqué mysqldump.

Cela donnera des commandes du type :

mysqldump <commandes> | gzip> fichier_destination.sql.gz

Exemple avec la commande de sauvegarde complète :

mysqldump --user=mon_user --password=mon_password --all-databases | gzip> fichier_destination.sql.gz

A noter qu’avec cette méthode, la commande de sauvegarde mysql produit un seul fichier compressé. le fichier source n’existe plus.

Pour la restauration mysql on n’utilise plus la commande mysqldump. On utilise la commande mysql. La commande mysql va aller lire un fichier puis le restaurer en fonction de son contenu et de ses instructions.

La restauration mysql peut se faire pour toutes les bases de données ou pour un base de données précise.

Exemple sans distinction de base de données :

mysql --user=mon_user --password=mon_password <fichier_source.sql

Autre exemple pour restaurer dans une base de données précise :

mysql --user=mon_user --password=mon_password nom_de_la_base <fichier_source.sql

Si notre base de données tourne dans un docker, il suffit d'appeler docker exec (ou docker compose exec) avec le paramètre -T pour pouvoir restaurer un dump :

docker compose exec -T [nom du docker dans le compose] mysql limesurvey --user=password --password=password <dump.sql

docker  exec -T [nom complet du docker] mysql limesurvey --user=password --password=password <dump.sql

Pour toutes les bases :

mysqldump --user=root --password=XXX --all-databases> alldatabases-`date +%Y%m%d-%H%M`.sql

Script complet de backup à adapter :

#!/usr/bin/env bash

SQL_USER="root"
SQL_PASS="XXX"
SQL_HOST="localhost"

FILE_PREFIX="backup_db"

FILE_DATE=`date +%Y-%m-%d-%H-%M-%S`
TARGET_FILE_PATH="/root/Backups/"
mkdir -p "$TARGET_FILE_PATH"

DATABASES=$(mysql -h $SQL_HOST -u $SQL_USER --password=$SQL_PASS -e "SHOW DATABASES;" | tr -d " | " | grep -v -e Database -e _schema -e mysql)

for DB_NAME in "$DATABASES"; do
        mysqldump -h $SQL_HOST -u $SQL_USER --password=$SQL_PASS --single-transaction --skip-lock-tables "$DB_NAME"> "$TARGET_FILE_PATH""$FILE_PREFI$
        gzip "$TARGET_FILE_PATH""$FILE_PREFIX"."$DB_NAME"."$FILE_DATE".sql
done
  • technique/mysql.txt
  • Dernière modification : 2024/02/13 17:05
  • de guillaumev