DBMS/MySQL/backup scripts

Scripts Utiles pour les backups MySQL

Voici quelques scripts utiles qui permettent de faciliter les sauvegardes et/ou restaurations dans MySQL.

Extraction de table depuis un dump texte SQL (extract.sh):

Ce script permet d'extraire des bases et/ou des tables d'un fichier dump. Cela va permettre, par exemple, de ressortir le fichier sql d'une seule base lorsque l'on fait des backups de toutes les bases dans un seul fichier de dump avec une commande du type:

# mysql -uuser -ppassword --all-databases > backup_of_the_day.sql

Voici le script:

#!/bin/bash

# 
# Extracts a schema or a table from a text dump generated by mysqldump or phpMyAdmin
# (c) 2012 Maciej Dobrzanski http://www.dbasquare.com/
# Released under GNU General Public License, version 2
# 

function help()
{
  cat <<EOF
  Usage: [-h] -f <dump file> -d <database name> [-t <table>]
EOF
}

while getopts "hf:d:t:" flag ; do
  case $flag in
    h)
      help
      exit 255
      ;;
    f)
      EX_DFILE=${OPTARG}
      ;;
    d)
      EX_SCHEMA=${OPTARG}
      ;;
    t)
      EX_TABLE=${OPTARG}
      ;;
    \?)
      echo "Invalid option: -$OPTARG" 
      ;;
  esac
done

: ${EX_DFILE?"Missing argument: -f must be specified; try '$0 -h' for details"}
: ${EX_SCHEMA?"Missing argument: -d must be specified; try '$0 -h' for details"}

if [ ! ${EX_TABLE} ] ; then
  sed -n \
      -e '1,/^-- \(Current \)\?Database/{/^\(--.*\)*\?$/d ; p } ; /^-- \(Current \)\?Database: `'"${EX_SCHEMA}"'`/,/^-- \(Current \)\?Database/ { /^-- \(Current \)\?Database/b ; /^\/\*\![0-9]\+.*=@OLD/be ; /^--$/d ; p }' \
      -e ':e /^\/\*\![0-9]\+.*=@OLD/,${ p }' "${EX_DFILE}" 
else
  sed -n \
      -e '1,/^-- \(Current \)\?Database/{/^\(--.*\)*\?$/d ; p } ; /^-- \(Current \)\?Database: `'"${EX_SCHEMA}"'`/,/^-- \(Current \)\?Database/{ /^-- Table.*`'"${EX_TABLE}"'`/,/^-- \(Table\|Temporary\|\(Current \)\?Database\)/ { /^-- \(Table\|Temporary\|\(Current \)\?Database\)/b ; /^\/\*\![0-9]\+.*=@OLD/be ; /^--$/d ; p } }' \
      -e ':e /^\/\*\![0-9]\+.*=@OLD/,${ p }' "${EX_DFILE}" 
fi

Il s'utilise ainsi:

# ./extract.sh -h
  Usage: [-h] -f <dump file> -d <database name> [-t <table>]

Par exemple:

# mysqldump -uroot --databases world > world.sql
# mysql -e "use world; show tables;" 
+-----------------+
| Tables_in_world |
+-----------------+
| City            |
| Country         |
| CountryLanguage |
+-----------------+

[root@gilles-p4 scripts]# ./extract.sh -f world.sql -d world -t City > City_world.sql
[root@gilles-p4 scripts]# cat City_world.sql
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2013-02-28 15:47:45

Source: [1]

Extraction de toutes les tables contenues dans un dump texte SQL (mysql_splitdump.sh):

Cet autre script permet de faire à peu prés la même chose que ci-dessus, sauf que celui permet d'exporter chacune des tables contenues dans une base. On prend donc le dump d'une base puis on exécute le script pour obtenir chaque dump de chacune des tables.

Voici le script:

#!/bin/bash

####
# Split MySQL dump SQL file into one file per table
# based on http://blog.tty.nl/2011/12/28/splitting-a-database-dump
####

if [ $# -lt 1 ] ; then
  echo "USAGE $0 DUMP_FILE [TABLE]" 
  exit
fi

if [ $# -ge 2 ] ; then
  csplit -s -ftable $1 "/-- Table structure for table/" "%-- Table structure for table \`$2\`%" "/-- Table structure for table/" "%40103 SET TIME_ZONE=@OLD_TIME_ZONE%1" 
else
  csplit -s -ftable $1 "/-- Table structure for table/" {*}
fi

[ $? -eq 0 ] || exit

mv table00 head

FILE=`ls -1 table* | tail -n 1`
if [ $# -ge 2 ] ; then
  mv $FILE foot
else
  csplit -b '%d' -s -f$FILE $FILE "/40103 SET TIME_ZONE=@OLD_TIME_ZONE/" {*}
  mv ${FILE}1 foot
fi

for FILE in `ls -1 table*`; do
  NAME=`head -n1 $FILE | cut -d$'\x60' -f2`
  cat head $FILE foot > "$NAME.sql" 
done

rm head foot table*

Utilisation du script:

# ./mysql_splitdump.sh
USAGE ./mysql_splitdump.sh DUMP_FILE [TABLE]

# mysqldump -uroot --databases world > world.sql
# mysql -e "use world; show tables;" 
+-----------------+
| Tables_in_world |
+-----------------+
| City            |
| Country         |
| CountryLanguage |
+-----------------+

# ./mysql_splitdump.sh world.sql
# ll *.sql
-rw-r--r-- 1 root root 179386 28 févr. 16:51 City.sql
-rw-r--r-- 1 root root  28468 28 févr. 16:51 CountryLanguage.sql
-rw-r--r-- 1 root root  39097 28 févr. 16:51 Country.sql
-rw-r--r-- 1 root root 244153 28 févr. 16:49 world.sql

Source: [2]