Backup and restore large mysql database

It is very hard to backup and restore of a very large mysql database using the conventional phpmyadmin.

Through command line you can take a backup of large database.

First you need to have shell (ssh) access to your server.
To Backup Mysql Database

$ mysqldump  -u [uname] -p[pass] [dbname] > [backupfile.sql]

[uname] Your database user name
[pass] The password for your database (note there is no space between -p and the password)
[dbname] The name of your database
[backupfile.sql] The file name for your database backup

To Backup Mysql Database with compress

For a very big database it is good 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]

For Uncompressed the Compressed Backup

$ gunzip [backupfile.sql.gz]
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]

Please follow and like us:

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload the CAPTCHA.