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
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
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.