MySQL – Dump those tables

So how do you take backup of your MySQL database? Simple, launch SQLYog or HeidiSQL and export your database. Well its an easy solution on Windows. How would you go about it on Linux or simply put how wud you do it on command prompt. The answer is mysqldump.

Its there on your system once MySQL gets installed. Once you know what database you need to take backup of, its pretty simple then:

Lets say you have a database named workshop and you want to back it up in a file named workshop_backup.sql. Simple execute the following statement:

$ mysqldump -u root -p workshop > workshop_backup.sql

-u stands for user and -p is for password. If u have set some password for root, it will ask you to enter it once you hit enter.

In case of large database, you can gzip your dump like this:

$ mysqldump -u root -p workshop | gzip > workshop_backup.sql.gz

What if you want only specific tables from your database and not the entire database as such. Do this:

$ mysqldump -u root -p workshop payments orders > only_payment_orders.sql

This will copy only payments and orders tables from workshop database.

Whatz the use of a database dump unless you use it. So to restore the files generated in the examples above, simply run this command:

$ mysql -u root -p new_workshop < workskop_backup.sql

This will dump the entire workshop database into new_workshop database.

In case you had created gzipped file:

$ gunzip < workshop_backup.sql.gz | mysql -u root -p new_workshop

Therez one more utility called mysqlhotcopy. It uses LOCK TABLES, FLUSH TABLES, and cp or scp to make a database backup quickly. It can make backup of the database or single table pretty fast, but it can be run only on the same machine where the database directories are located.

One more thing. if you are on windows and you happen to have a big dump file, restoring it might give you an error, something like MySQL is down or something similar. In that case you need to add the following line in my.ini file in your MySQL directory (generally under C:\Program Files\MySQL\MySQL Server 5.1)


Save the changes. Don’t forget to restart the mysql service or you will keep getting the error.

Now that you know how to take backup, do not forget to take one today. Happy dumping!!


2 thoughts on “MySQL – Dump those tables

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s