Import/Export MySQL "rapide"

 Publié le 20-01-2012  |  1 minutes  |  Pascal

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
Tips  MySQL  mysqldump