Script mysqldump

By 6 de mayo de 2008 Linux, MySQL 3 Comments

Excelso script de Xavi Armengou que mediante switches puedes configurarlo con la idea de ponerlo en un crontab (por ejemplo).

Dicho script soporta modificadores.

Si alguien lo mejora, ruego lo notifique, gracias.

mysqldump.sh

El fuente lo pego para que en una lectura en diagonal veáis si os interesa o no:

#!/bin/bash

###

### shell script (cron job) to backup local mysql databases

### either pass databases to dump as arguments, or set the $databases array

### usage:  mysqldump.sh [database ...]

###





### constants

### by default, DEST is our working dir

DEST='.'

if test "$MAILTO" = 'root'; then

  ### if run by crond, set a common DEST

  ### uselessly tried 'test -t', $PS1 and ${-/i}

  DEST=/home/backup/database

fi

user='root'

pass='elpasswd'

databases=()





### action

### this script has to be run by root

### we choose not to fully verify permissions

### DEST dir is under a -hope- mounted filesystem

# mkdir -p -m 0755 "$DEST"

test -d "$DEST" -a -w "$DEST" || exit 73

# umask -S u=rwx,g=rx,o=

umask 027

### test the user's credentials

&>/dev/null mysqlshow --user="$user" --password="$pass" mysql || exit 78





### AFAIK, things changed between MySQL 4.0 and 4.1

# major=$(mysqldump -V | awk '{print $5}' | cut -d'.' -f1)

# minor=$(mysqldump -V | awk '{print $5}' | cut -d'.' -f2)

# : ${major:=0} ${minor:=0}

### let's try another way: is --skip-opt an [un]recognized option?

options=

mysqldump --skip-opt &>/dev/null

if test $? -ne 1; then

  ### MySQL Distrib 4.0.* or lower

  ### --opt is disabled by default, and it means:

  ### --add-drop-table  --add-locks   --all            --disable-keys

  ### --extended-insert --lock-tables --quick

  : options='--no-defaults --all'

else

  ### MySQL Distrib 4.1.* or higher

  ### --opt is enabled  by default, and it means:

  ### --add-drop-table  --add-locks   --create-options --disable-keys

  ### --extended-insert --lock-tables --quick          --set-charset

  options='--skip-opt --create-options'

  ### see below: we use this value as a version checker

fi





### when --all-databases or --databases option is given, this line is add:

### >> CREATE DATABASE /*!32312 IF NOT EXISTS*/ db_name;

### (no-create-db variable is FALSE by default)

### in our "else" case, we pretended to force this line, unsuccessfully

### (the boolean option does not apply)

###

declare -i RETVAL=0

test "$1" = '--'   && shift

if   test $# -eq 0 -a ${#databases[@]} -le 0; then

  ### dump all the databases, into a single file

  # options="--compress --opt --port=3306"

  mysqldump --user="$user" --password="$pass" ${options} --all-databases |\

  gzip - > "$DEST/all-databases.sql.gz" || ((RETVAL++))

  ###       --------------------------

elif test $# -eq 0 -a ${#databases[@]} -gt 0; then

  ### dump the variable-specified database/s

  ### and put those databases together in a file

  mysqldump --user="$user" --password="$pass" ${options} --databases "${databases[@]}" |\

  gzip - > "$DEST/databases.sql.gz"     || ((RETVAL++))

  ###       ----------------------

else

  ### dump the argument-given database/s

  ### every database will have its specific file

  if test "$options" = "${options/--skip-opt}"; then

    options="--add-drop-table --add-locks --all --disable-keys --lock-tables --quick"

  else

    options="--opt --skip-extended-insert"

  fi

  ### oops! it does not work!

  # options="${options} --no-create-db=FALSE"

  # options="...   --skip-no-create-db"

  for database do

    mysqlshow --user="$user" --password="$pass" >/dev/null "$database"  &&\

    mysqldump --user="$user" --password="$pass" ${options} "$database"   |\

    gzip - > "$DEST/${database}.sql.gz" || ((RETVAL++))

    ###       ------------------------

  done

fi





if ((RETVAL>255)); then RETVAL=255; fi

if test ${RETVAL:-0} -ne 0; then exit $RETVAL; fi

Cómo restituir la BBDD:

Desde fichero comprimido:
shell# zcat database.sql.gz | mysql -u USER -pPASSWORD database

Desde fichero plano:
shell # mysql -u USER -pPASSWORD database < database.sql

Usando el aplicativo mysqldump:

http://www.solusan.com/backup-mysql-usando-el-comando-mysqldump-ii.html

Nota: Si bajáis el fichero pasadle la aplicación dos2unix para eliminar posibles saltos de línea y cosas raras.

3 Comments

Leave a Reply

Your email address will not be published.