How to backup and restore large mysql database

by lifeLinux on May 1, 2011

If you have very large mysql database then it is very hard to backup and restore using the conventional phpmyadmin or any other programs.
In this Tutorial I will explain you how to backup and restore a larger database. 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. Then follow the steps:

To Backup Mysql Database

root@lifelinux:~# mysqldump  -u [username] -p[password] [dbname] > [backup.sql]

[username] is your database user name
[password] is the password for your database (Note: there is no space between -p and the password)
[dbname] is The name of your database
[backup.sql] is The file name for your database backup

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.

root@lifelinux:~# mysqldump -u [username] -p[password] [dbname] | gzip -9 > [backup.sql.gz]

Restore Mysql Database

To restore the database you need to create the database in target machine then use this command

root@lifelinux:~# mysql -u [username] -p[password] [dbname] < [backup.sql]

Restore Compressed Mysql Database

root@lifelinux:~# gunzip < [backup.sql.gz] | mysql -u [username] -p[password] [dbname]

Related Posts:

Previous post:

Next post: