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
FLUSH TABLES, and
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!!