April 2010
M T W T F S S
« Mar   May »
 1234
567891011
12131415161718
19202122232425
2627282930  
  1. We welcome any feedback, questions or comments

Remove first few lines from the Huge/GB file

Question.

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.

Solution.

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”
eg :

 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
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
CHOWN="$(which chown)"
CHMOD="$(which chmod)"
BZIP2="$(which bzip2)"

# Backup Dest directory, change this if you have someother location
DEST="/backup/database"

# Main directory where backup will be stored
MBD="$DEST/mysql"

# Get hostname
HOST="finadb"
echo " Backup started on "  `date` >> /home/installation/scripts/db-backup.log
# Get date in dd-mm-yyyy format
NOW="$(date +"%d-%m-%Y")"

# File to store current backup file
FILE=""
# Database to backup
db="fmi"
FILE="$MBD/$db.$HOST.$NOW.bz2"
$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 !!! :-)

Leave a Reply

  

  

  

You can use these HTML tags

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