Activities

March 2011
M T W T F S S
« Feb   Apr »
 123456
78910111213
14151617181920
21222324252627
28293031  

MySQL : Multiple tables backup using mysqldump

I have a requirement to backup few tables from a huge database as total dump is taking 2 hours and about 30GB in size. Huge tables are containing static data which doesn’t change frequently.

Here is the scripts to do that,

#!/bin/bash
# Created by Liju on 09/03/2011
#
DB_NAME=’db’
USER=’username’
PASS=’user_pass’
HOST=’localhost’
DUMP_LOC=’/storage1/temp’
NOW=”$(date +”%d-%m-%Y”)”
echo “Fmi daily tables backup started on ” `date` >> /home/installation/scripts/database-backup.log
tbl_names=’table1 table2 table3 table4 table5 table6 table7 table8 table9 table10 table11 table12 table13 ‘

mysqldump –max_allowed_packet=1G –add-drop-table -ui$USER -p$PASS $DB_NAME $tbl_names | bzip2 -c > $DUMP_LOC/$DB_NAME-$NOW-tbles_bkp.bz2
echo “Fmi daily tables backup has ended at ” `date` >> /home/installation/scripts/database-backup.log

You can download the scripts from here

2 comments to MySQL : Multiple tables backup using mysqldump

  • Hi! To make multiple MySQL databases backup you can also use this: here.
    It creates a file for each database with the dump, and makes you save a lot of time making just the first configuration.
    Regards!
    AN

    • Liju

      Hello Andrea

      Thanks for build such tools. Most the sys-admin prefer to use the tools which shipped with the package as this would works anywhere. Also the requirement of taking multiple database is a rare scenario. I’d done full backup database backup in case of think about a backup most the cases.

      Also individual database backup is proposed to be taking for bugging/analyzing some issue with the database especially for development purpose.

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>