Mysql DB Backup and Restore How to

by sahab on May.31, 2010, under Backup, RHEL5, Ubuntu, ubuntu 10.04, ubuntu 7.10, ubuntu 8.04, ubuntu 8.10, ubuntu 9.04, Ubuntu 9.10

Database Backup

If you’re storing anything in MySQL databases that you do not want to lose, it is very important to make regular backups of your data to protect it from loss. It is also a good idea to create a backup before making any major changes, incase something goes wrong and you need to revert to the unmodified version. Database backups can also be used to transfer your database from one server to another if you change web hosts.

Command Line (using mysqldump)

# mysqldump -u user_name -p your_password database_name > File_name.sql

Eg:
Let’s assume that:
Username: ubuntu
Password: jaunty
Database Name: ubuntulinux

# mysqldump -u ubuntu -p jaunty ubuntulinx> ubuntulinux.sql

This will backup the database to a file called ubuntulinux.sql

Back up more than one database

In this case you can use the –database option followed by the list of databases you would like to backup. Each database name has to be separated by space.

#mysqldump -u root -p –databases ubuntu ubuntulinux > data_backup.sql

Backup all Database

Back up all the databases in the server at one time you should use the –all-databases option. It tells MySQL to dump all the databases it has in storage.

#mysqldump -u root -p –all-databases > all_backup.sql

Database Backup From the phpMyAdmin

1. Login to phpMyAdmin
2. Click on your database name
3. Click on the tab labeled EXPORT
4. Select all tables you want to backup (usually all)
5. Default settings usually work, just make sure SQL is checked
6. Check the SAVE FILE AS box
7. Hit GO

Database Restore

Restore Database From the Command Prompt

If you are moving your data to a new server, or you have removed the old database completely you can restore it using the code below. This will only work if the database does not already exist:

#mysql – u user_name -p your_password database_name < file_name.sql

Or using our previous example

#mysql – u ubuntu -p jaunty ubuntulinux < ubuntuBackup.sql

If your database already exists and you are just restoring it, try this line instead:

#mysqlimport -u user_name -p your_password database_name file_name.sql

Or using our example again:

#mysqlimport -u ubuntu -p jaunty ubuntulinux ubuntuBackup.sql

Restore Database From the phpMyAdmin

Login to phpMyAdmin
Click on the tab labeled SQL
Unclick the ‘show query here again’ box
Choose your backup file
Hit GO

:linux, Mysql DB Backup and Restore, Ubuntu, ubuntu 10.04, ubuntu backup tool, ubuntu guide, ubuntu linux

4 Comments for this entry

Bill
January 12th, 2012 on 11:18 am
In the last recipe to ‘Restore Database From the phpMyAdmin’ don’t you have to choose an ‘import’ option somewhere in the phpMyAdmin GUI??
Bill
January 12th, 2012 on 11:20 am
Thanks heaps for publishing this article. It will really help me in the future. Help like this is one of the Great Things about using Ubuntu.
Leo Scouten
April 19th, 2012 on 10:55 am
What a great list. How did you get that on your blog?
muzi
April 20th, 2012 on 2:33 am
thanks man, this was very helpful