El blog de Juan Palómez

17 marzo 2013

Automysqlbackup additions

Filed under: Uncategorized — Etiquetas: , — thisisoneball @ 16:05

These are some things I add or change to the default AutoMySQLBackup script:

  • mysqldump options
OPT="--quote-names --opt"

For this section you should read the mysqldump documentation, but this is what I use:

OPT="--quote-names --opt --routines --single-transaction --skip-dump-date"

–routines dumps your stored procedures and functions along with the tables (it creates DROP PROCEDURE and CREATE PROCEDURE statements in your dump file). Routines are also stored in the information_schema database, so if you are also dumping that database, the routines will be there anyway.

–single-transaction avoids this kind of error:

mysqldump: Got error: 1449: The user specified as a definer (‘root’@’%’) does not exist when using LOCK TABLES


this eliminates the “– Dump completed on xxxx-xx-xx” line at the end of each dump file. Otherwise, dumps from identical schemas appear to be different. With this, the files are identical and you can delete duplicate files and save disk space

  • Parallel compresion

Compression of the dump files was taking too long in my system, as the files are big and I also use bzip2 instead of gzip because of its higher compression rate.
So I installed the pbzip2 package, which can use several CPU cores at the same time, unlike bzip2. I have a 4-core processor so I use -p3 so that it uses 3 cores in parallel. By default it autodetects the number of cores, which is usually ok, although if the processor has Hyperthreading I would recommend setting the number manually, because it can autodetect twice the real number of cores. I also remove the -v switch as it’s too verbose in pbzip2

Original options:

BZIP2="`${WHICH} bzip2`"
${BZIP2} -f -v ${1} 2>&1

My options:

BZIP2="`${WHICH} pbzip2` -p3"
${BZIP2} -f ${1} 2>&1
  • Skipping backup

In my server some DB processes can take days to complete, and in that case I prefer not to run the DB backup. I read the /proc/loadavg file (which shows the same system load values as the uptime or top commands) to check if there is something running (this is a dedicated DB server so if the load is high it should be the DB causing it):

if [ "$(cut -f1 -d. /proc/loadavg)" -gt 1 ]
    echo "System load too high, skipping backup"
    exit 10

2 comentarios »

  1. Hola Juan. en que lineas hicistes cambino añadir –single-transaction ? The problem I have is everytime script runs it looks up database and applications.

    Comentario por Francis Suarez (@codex73) — 16 septiembre 2014 @ 20:46

    • Mira arriba donde pone OPT=”…
      Se ve la linea original y la sustituida

      Comentario por thisisoneball — 15 noviembre 2014 @ 21:23

RSS feed for comments on this post. TrackBack URI


Por favor, inicia sesión con uno de estos métodos para publicar tu comentario:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión /  Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión /  Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión /  Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión /  Cambiar )


Conectando a %s

Crea un blog o un sitio web gratuitos con WordPress.com.

A %d blogueros les gusta esto: