MySQL is a great Open Source Software that is easy to setup and manage. It is available for both Windows and Unix/Linux Operating Systems ( and many others) . If you are using MySQL, here is a quick and easy way to backup and restore your MySQL databases.

This time, we are going to discuss about backup and restore using command line utilities. If you want a GUI to do this, you might as well read about the famous MySQL web administration tool, phpMyAdmin. For me, both are useful and I use them as per my convenience. This article assumes that you have shell access to MySQL server or at least remote access using mysql client from your computer to a MySQL DB Server.

Backing up the DB
We could use the mysqldump command like the one below :

For a full backup of all DBs :

 $ mysqldump  --all-databases > backup.sql 

Creating backup of a single DB:

 $ mysqldump  my_db_name > backup.sql 

This will create a text file with all data and structure of the DB. Remember, this is a quick and dirty way to do this, there are many options available to this command.

Creating backup of a single DB and using your password to login :

 $ mysqldump  --password=mypassword my_db_name > backup.sql 

For more examples and available options, run the command man mysqldump. You may also need to encrypt the backup.sql for security.

Restoring the DB
Restoring also is pretty easy. We could use the mysql command like the one below :

Run this command from where the backup.sql file is stored.

 $ mysql my_db_name < backup .sql 

Side Note : If you have shell access, you could add a cron job for running the backup command on every night or so. :)