Backup and restore large mysql database
If you have very large mysql database then it is very hard to backup and restore using the conventional phpmyadmin or any other program ( like bigdump).
In this Tutorial I will explain you how to backup and restore a larger database ( having millions of rows). This tutorial can be used while moving your database from one server to another.
First you need to have shell (ssh) access to your server.You can use any free program like putty to connect your server Then follow the steps:
To Backup Mysql Database
$ mysqldump -u [uname] -p[pass] [dbname] > [backupfile.sql]
To Backup Mysql Database with compress
If your mysql database is very big, you might want to compress the output of mysql dump. Just use the mysql backup command below and pipe the output to gzip, then you will get the output as gzip file.
$ mysqldump -u [uname] -p[pass] [dbname] | gzip -9 > [backupfile.sql.gz]
If you want to un compress you database file created in above step the use this command
$ gunzip [backupfile.sql.gz]
To Restore Mysql Database
To restore the database you need to create the database in target machine then use this command
$ mysql -u [uname] -p[pass] [db_to_restore] < [backupfile.sql]
To Restore Compressed Mysql Database
gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[pass] [dbname]
For huge databases(say GB size) its recommended to move mysql data directory to an LVM partition and take snapshot backup. It would take only seconds to complete:)
Ref:
http://www.lenzg.net/mylvmbackup/
http://www.mysqlperformanceblog.com/2006/08/21/using-lvm-for-mysql-backup-and-replication-setup/