I have a Mysql dump file which is 30GB size and I want to test integrity of the same file by restoring it with another database on the same server ( no other options). Since the size is big I can’t open it with any of the text editor.
1. First I split the sql file in to 30 pieces each having 1 GB size. I can open 1 GB file on vi editor.
#split -b 1073741824 temp/prod-db-backup.06042010 dbpart_
This command will split the file in to iGB file with named dbpart_aa, dbpart_ab, dbpart_ac….etc. Now I have around 30 nos.
2. Next thing is to modify the file for deleting the entry of ” create database and use ” then stand on the same directory,
#cat dbpart_a* >> dbbackup.sql
#cat dbpart_b* >> dbbackup.sql
3. Now I am going to restore the database. So I wish to run it as a background process.
#mysql -uroot -hlocalhost -e ‘create database hugedb;use hugedb;source dbbackup.sql;’
####### Updates on this post on 08-04-2011 ###
This requirement is for removing the same database being used on the mysql backup which could make an issue when we are restoring the same backup on diff. database. It’s take a big time for doing above operations.
Recently I found that we can be solve this issue by adding “-p –tables database ” in myslql dump statement and this will eluminte the usage of “USE dbname” . “–no-create-db” will not help you to skip the “Use dbname”
mysqldump --max_allowed_packet=2G -h 192.168.0.47 -uuser -p'pass' --tables dbname --ignore-table=dbname.plogger_config | bzip2 > newbkp.bzip2
Here I’m copying one of my sample scripts to dump the mysql database which is GB in size.
MyUSER="dbusername" # USERNAME
MyPASS="userpass" # PASSWORD
MyHOST="192.168.20.11" # Hostname
# Linux bin paths, change this if it can't be autodetected via which command
# Backup Dest directory, change this if you have someother location
# Main directory where backup will be stored
# Get hostname
echo " Backup started on " `date` >> /home/installation/scripts/db-backup.log
# Get date in dd-mm-yyyy format
# File to store current backup file
# Database to backup
$MYSQLDUMP --max_allowed_packet=1G -h $MyHOST -u $MyUSER -p$MyPASS --tables fmi --ignore-table=$db.tablename | $BZIP2 > $FILE
echo " Backup Ended at " `date` >> /home/installation/scripts/db-backup.log
echo " #########completed the backup ############# "
You may need to change the following values as required,
a. MyUSER : database user name
b. MyPASS : database password
c. MyHOST : hostname
d. DEST : backup location
e. MBD : mysql backup folder
f. db-backup.log : Path to log file
g. db : database name
h. HOST : hostname
i. –max_allowed_packet and –ignore-table you can skip both option
This is a cool solution !!!