How to create backup of MySQL databases using command line on linux server

In this post we will show you how to backup your databases in MySQL on your linux server. You must be thinking why is this important? The reason is that most websites make use of a content management system (such as Drupal, Joomla!, Wordpress, phpBB). Content management Systems need databases to store content, articles or posts along with all the necessary settings. Generally these content management systems are written in PHP/Perl and use MySQL as their data backend, so it is very important to create backup of the database at regular intervals.

You should take a backup of all the tables of the database at continual intervals so that you can restore the data if something happens to your web server. Now we will show you how to perform a full MySQL backup, and then compress the output file and finally how to download it from the Linux server. Just follow the steps given below.

1. SSH to your website and run the following command in terminal and all the MySQL databases will be placed in a file named as "all-databases.sql".
      
                                          mysqldump --all-databases > all-databases.sql -u root –p                            
      You will be prompted to enter the password of the root account associated with MySQL. Do so and everything will be copied in a file named all-databases.sql in the current folder as explained earlier.
   
2. Next we need to compress this file before downloading it from linux server to our local computer. By doing so we will be able to reduce its size and thus it will reduce download time and save bandwidth.
      
                                               bzip2 all-databases.sql                            
      A file named all-databases.sql.bz2 will be created in the current working folder.
   
3. Now move this file inside the root folder of your website so that you can download it via HTTP using an internet browser.
      
                                                mv all-databases.sql.bz2 /var/www/your_site_folder/
4. Since the file is now present in the root folder of your website just append the the filename to the URL of your website and download it. For example if your website is www.mywebsite.com type in the browser http://www.mywebsite.com/all-databases.sql.bz2 to download the file. Or download backup file via any FTP client like Filezilla.
   
5. Important: Finally we need to remove the file from the root folder otherwise malicious users can possibly steal it. Run the following command.
      
                                          rm -vf /var/www/your_site_folder/all-databases.sql.bz2                            
      You will be asked to confirm whether you want to really delete file named all-databases.sql.bz2, choose yes.
      
      Note: Be careful with rm command, avoid using wildcards like *, # etc.

Thats it! Do post your suggestions and comments.