Posts Tagged backup

Extract a table from a mysqldump

Today I encountered a problem: I needed to restore a single table from a database mysqldump.
Usually you cat the-mysqldump.sql |mysql the_database so you’re only able to restore the full database. I didn’t find any mysqldump option to extract a single table from a full database dump, so I’ve come up with this (minimal) shell script:


  grepstr="/*!40000 ALTER TABLE \`$TABLE\`"
  lines=`grep -n "$grepstr" $DUMPFILE |cut -d":" -f1`
  lines=`echo $lines|sed 's/\ /,/' `
  sed -n "$lines p" $DUMPFILE

extract_table $1 $2

Use it like this:
./ table-to-extract dumfile-for-extract |mysql the_database (use the |mysql after you have checked the content).

Be carefull, this script is minimalistic:

  • It doesn’t check if the file exist and is really a mysqldump file
  • It doesn’t check if the table to extract exists
  • It doesn’t work if disable-keys is set to false in mysqldump
  • It doesn’t have a usage() function

If some people request it, I’ll write all these features, but as usual, I wanted to come up with a solution I could already use one hour a ago, and I’m spending time to write this script, let’s do it the faster I can ! 🙂

Comments (1)

Transfering disk images with low disk space

If you want to dump a disk to a disk image you will use for example:
dd if=/dev/hdx1 of=/tmp/disk.img
and then, you will probably copy this disk image to another machine. The thing is, if you have low disk space than the size of /dev/hdx1 on your machine, you won’t be able to dump the disk to transfer it to the other machine.
There is a solution that I use, as usually with, ssh and pipe:
ssh hostname "dd if=/dev/hdx1" |dd of=/tmp/disk.img
on the machine receiving the image or
dd if=/dev/hdx1 |ssh hostname "dd of=/tmp/disk.img"
on the machine sending the image, so the content of the disk is directly transmitted through ssh !
That’s it !
Maybe you can tune the blocksize of the dd command so the troughput is better, maybe a futur article on that 🙂

Comments (3)

copying databases

Similarly to my last tip (copying directory with ssh and tar) , you can also copy databases. It’s pretty simple, here is my magic command:
mysqldump -ppassword db |ssh user@remote "cat - | mysql -u dbuser -ppassword db"

Here, you can also gzip or bzip2 the input, and it should be very efficient, because mysqldump output is pure ascii with sql, gzip and bzip2 will easily find good pattern for compression.

Also, as usual, using my.cnf files, you don’t need -ppassword parameters.

Comments (2)

Database (mysql) backup script

Don’t need to say much, everything is in the title of this post 🙂 .
Here is my mysql database backup script:


function backup_db {

BACKUPFILESUFFIX="`date +%m%d`.bz2"
DBLIST=`echo "show databases" | mysql -u backup -p$PASS -h $HOST`
NUMDB=`echo $DBLIST |wc -w`

if [ ! -d ${dir} ]; then
  mkdir ${dir}
if [ ! -e $DIR/count.$HOST ]; then
  echo $NUMDB > $DIR/count.$HOST

COUNT=`cat $DIR/count.$HOST`

if [ "$COUNT" -lt "$NUMDB" ]; then
  echo -e "Databases list: $DBLIST" | mail -s "New database, maybe new backups needed!" $MAIL
  echo $NUMDB > $DIR/count.$HOST

/usr/bin/mysqldump -u $USER -p$PASS -h $HOST --routines $DB|bzip2 > $DIR/$DB.$BACKUPFILESUFFIX

if [ $? != 0 ] ; then
  echo -e "Return code is : $? and log file contains:\n `cat $LOGFILE`" | mail -s "Backup MySQL $HOST: $DB Error" $MAIL

SIZE=`du  -sk $DIR/$DB.$BACKUPFILESUFFIX| cut -f1`
if [ "$SIZE" -lt $MINSIZE ]; then
  echo -e "File is smaller than $MINSIZE k, printing an ls output:\n `ls -l $DIR`" | mail -s "Backup MySQL $HOST potential error" $MAIL

# Cleaning up old files, or disks won't fill
find $DIR -ctime +7 -name "*bz2" -exec rm {} \; -print

backup_db "" "user" "password" "database" "9999"

To backup your databases add at the end of script, one line per database, based on this format:
backup_db "host" "user" "password" "database name" "min_size"

You also need to create a backup user on your database, I use this script:

CREATE USER 'backup'@ 'backup-host' IDENTIFIED BY 'a-password';

GRANT SHOW DATABASES ON * . * TO 'backup'@ 'backup-host' IDENTIFIED BY 'a-password ;

SHOW VIEW ON `a_database_to_backup` . * TO 'backup'@ 'backup-host';

And also add a crontab entry:

cat <<EOF >/etc/cron.d/dbbackup
0 5 * * * root /var/backups/db/ 2>&1 >/var/backups/db/backupdb.log
chmod +x /etc/cron.d/dbbackup

This script have some features I have implemented that I find usefull:

  • It mails you when a new databases is created (devs sometimes create a database but don’t inform me, they need backup of it, in case)
  • It’s easy to add new databases to backup
  • It checks for a minimum size, you know that some databases won’t be less than a fixed size, if it happens, there is probably a problem with the backup script or within the database
  • It also backups stored procedures (we use the --routines option of mysqldump)
  • It has a 7 days rotation mechanism, so the disk don’t fill
  • The databases are compressed

Some improvement that can be done to this script:

  • Better error handling, I’m not really sure how it works, I made this script pretty much fast for my daily needs
  • Use mk-parallel-dump from the maatkit
  • Use .my.cnf, and don’t display password in the script, is it better ?
  • please comment to give me some ideas 😉

Leave a Comment