Databases, Systems & Networks

Sauvegarde MySQL

Pour sauvegarder une base de données (sans et avec compression) :

# mysqldump NOM_BASE > NOM_FICHIER
# mysqldump NOM_BASE | gzip > NOM_FICHIER

Pour restaurer une base de données (sans et avec compression) :

# mysqladmin create NOM_BASE
# mysql NOM_BASE < NOM_FICHIER
# gunzip < NOM_FICHIER | mysql NOM_BASE

Sauvegarder toutes les bases :

# mysqldump --opt --all-databases > NOM_FICHIER

Pour sauvegarder uniquement certaines tables :

# mysqldump NOM_BASE NOM_TABLE0 [NOM_TABLE1...] > NOM_FICHIER

Pour presque faire un « –exclude » (qui manque cruellement à mysqldump):

mysql -B -N -e 'show databases' | 
  perl -ne 'print unless /b(?:phpmyadmin|mysql|information_schema)b/' | 
  xargs echo mysqldump -B

Et pour sauvegarder des tables correspondant à un motif (préfixe le plus souvent) :

# mysqldump NOM_BASE $(mysql NOM_BASE -B --column-names=False -e "show tables like 'exemple_%'") > NOM_FICHIER

Pour dumper avec une condition particulière :

mysqldump -t <base> <table> --where="my_id='66666666'"

Ce qui permet de réinjecter des données résultantes d’un SELECT * FROM base.table WHERE my_id='66666666'.

Il est évidement possible de faire toutes ces opération sur une instance en précisant son port avec l’option –port (valable pour mysqldump et mysql).

Pour obtenir une liste des utilisateurs mysql, on peut utiliser cette fonction (glanée sur serverfault) :

mygrants()
{
  mysql -B -N -e "SELECT DISTINCT CONCAT(
    'SHOW GRANTS FOR ''', user, '''@''', host, ''';'
    ) AS query FROM mysql.user" | 
  mysql | 
  sed 's/(GRANT .*)/1;/;s/^(Grants for .*)/## 1 ##/;/##/{x;p;x;}'
}

Pour restaurer une seule base d’un dump complet on peut utiliser :

mysql -o MaBase < dump.sql

Pour restaurer une base, dont la structure de chaque table est stocké dans un fichier SQL indépendant, et dont les données sont dans des fichiers de type  » tab-separated data files » (option -T de mysqldump) avec une extension .txt :

db=test1

for file in *.sql; do
   mysql $db <$file
done

for file in *.txt; do
   tablename=`basename $file .txt`
   echo "LOAD DATA INFILE '$PWD/$file' INTO TABLE $tablename"  CHARACTER SET utf8 | mysql $db
done

Note 1 : Attention, l’utilisateur MySQL doit avoir le droit de lecture sur les fichiers .txt

Note 2 : Si vous n’avez pas toutes vos tables en utf8, ce n’est pas bien… et vous devrez pour la peine adapter le script (en détectant le charset utilisé avec « file » si nécessaire)

Il est possible d’exporter le contenu d’une base au format CSV :

mysqldump -T /tmp --fields-enclosed-by=" --fields-terminated-by=, --no-create-db --no-create-info MaBase

NOTE : il est nécessaire que MySQL ait les droits d’écriture dans le répertoire de destination (ici /tmp).

Utilisation

Créer une nouvelle base de données nommée NOM_BASE :

mysql> CREATE DATABASE NOM_BASE;

Voir les bases de données créées :

mysql> SHOW DATABASES;

Utiliser la base de données NOM_BASE :

mysql> USE NOM_BASE

Voir les tables créées :

mysql> SHOW TABLES;

Créer une table nommée test avec différents champs :

mysql> CREATE TABLE test (id INT not null AUTO_INCREMENT, prenom VARCHAR
(50) not null , nom VARCHAR (50) not null , ne_le DATE not null ,
ville VARCHAR (90), enfants INT, PRIMARY KEY (id));

Décrire une table :

DESC test;

Ajouter un champ à une table :

mysql> ALTER TABLE test ADD another VARCHAR(100) DEFAULT NULL;

Ajouter un champ à une table en précisant sa place :

mysql> ALTER TABLE test ADD another VARCHAR(100) DEFAULT NULL AFTER prenom;

Supprimer un champ à une table :

mysql> ALTER TABLE test DROP another;

Renommer un champ :

mysql> ALTER TABLE test CHANGE COLUMN another anotherone TEXT;

Changer le type d’un champ :

mysql> ALTER TABLE test CHANGE another another enum('foo',bar');

Insertion de données dans une table :

mysql> INSERT INTO test VALUES (1,'jp','papin','2005-06-12','Marseille',2);
INSERT INTO test (id,prenom,nom,ne_le) VALUES (2,'c','waddle','2004-06-17');

Sélectionner tous les champs d’une table :

mysql> SELECT * FROM test;

Effacer des données d’une table :

mysql> DELETE FROM test WHERE nom='waddle';

Effacer TOUTES les données d’une table :

DELETE FROM test;

Supprimer une table :

DROP TABLE test;

Supprimer une base de données :

DROP DATABASE NOM_BASE;

Source: Evolix