Sharing Knowledge | System Admin Articles

Backup and Restore MySQL database




Databases are used to store large amount of precious data and it becomes very important to Backup your data. This article explains the how to backup and restore the MySQL Database.




Backing Up MySQL Database:


MySQL database backup can be accomplished in two ways:


1. Copying the raw mysql database files &
2. Exporting tables to text files


Copying the MySQL database files:


MySQL uses the same table format on different platforms, so it's possible to copy MySQL table and index files from one platform and use them on another without any difficulties (assuming, of course, that you're using the same version of MySQL on both platforms).


Exporting tables to text files:


The MySQLDump is handy utility that can be used to quickly backup the MySQL Database to the text files. 


The syntax for the command is as follows.


mysqldump -u [Username] -p [password] [databasename] > [backupfile.sql]
[username] - this is your database username
[password]- this is the password for your database
[databasename] - the name of your database
[backupfile.sql] - the filename for your database backup


For example,


If you want to backup MySQL Database named "accounts" into text file accounts.sql. Here are the scenarios of taking the backup. Assuming that both user name and password of the database is "admin".


1) Taking the full backup of all the tables including the data. Use the following command to accomplish this:
mysqldump -u admin -p admin accounts > accounts.sql


2) Taking the backup of table structures only. Use the following command to accomplish this:
mysqldump -u admin -p admin --no-data accounts > accounts.sql


3) Taking the backup data only. Use the following command to accomplish this:
mysqldump -u admin -p admin --no-create-info accounts > accounts.sql


Restoring MySQL Database:


You can use the following to command to restore the accounts database from accounts.sql backup file.


mysql - u admin -p admin accounts < accounts.sql


Labels: Database, mysql

My Profile PhotoAbout the Author

I'm Parthiban, An UNIX System Admin by Profession. I'm Experienced in Linux/Unix System Administration and Scripting. I have done lot of work on the infrastructure Mgmt side in Linux, UNIX and Windows system administration, Hardware, Storage and Data center. I'm blogging since 2008.
Follow Me On Twitter or On Facebook

2 Comments for "Backup and Restore MySQL database"

You can also use a program to make a MySQL database backup like Handy Backup (http://www.handybackup.net)

@Dan Admin,

Thanks for your useful tip...!

What do you think about this Article? Add your Opinion..!

Back To Top