export.sh
Les données sont exporté avec mysqldump et le paramètre -T
-T: Creates tab separated textfile for each table to given path. (creates .sql and .txt files). NOTE: This only works if mysqldump is run on the same machine as the mysqld daemon.
Ce script doit donc etre exécuté directement sur le serveur MySQL
Export
#!/bin/bash
DST='/var/backup/mysql/dump
for bdd in `mysql --skip-column-names -B -e "show databases;" | egrep -v "^information_schema$|^mysql$"`; do
echo -ne $bdd
mysql --skip-column-names -B -e "SHOW CREATE DATABASE $bdd;" | cut -d" " -f2- > $DST/create-$bdd.sql
for table in `mysql --skip-column-names -B $bdd -e "show tables;"`; do
mkdir $DST/$bdd 2>/dev/null 1>&2
chown mysql:mysql $DST/$bdd
echo -ne "."
mysqldump -T $DST/$bdd/ $bdd $table
done
echo -ne "\r\n"
done
Import
#!/bin/bash
SRC='/var/backup/mysql
for bdd in `ls -1 $SRC`; do
echo -ne $bdd
for table in `ls -1 $SRC/$bdd | awk -F. '{print $1}' | sort | uniq`; do
mysql $bdd -e " SET foreign_key_checks = 0;
SOURCE $SRC/$bdd/$table.sql;
LOAD DATA INFILE '$SRC/$bdd/$table.txt
INTO TABLE $table;
SET foreign_key_checks = 1;"
echo -ne "."
done
echo -ne "\r\n"
done