How to backup WordPress database ?

WordPress is a complete storehouse where we maintain and organize our website information using tables, databases, and other variables. So in the process of website management, its strongly advised to taking a backup of your files and database at regular intervals or before any upgrade. 
WordPress being the most reliable web-based and database driven content management system, the act of taking backups for databases is extremely important for securing your hard work, time, money and efforts. 
This habit safeguards the website data and helps in restoring it back if something goes wrong or some accident occurs even in the highly secured setup. 
In short, website security and maintenance demand a high level of responsibility of taking regular backups for the reason of WordPress being prone to vulnerabilities and damages. 

Let us look into some of the important reasons for taking WordPress database backups: 
 

• Gives immense peace of mind during unfavorable situations like site-hacks, server crash, system errors or abrupt deletion.
• When the hosting company suspends user accounts, then backup helps in easy access and restoring of data for personal use.
• Backups are helpful at the time when hackers inject unwanted data into databases. Backups help in cleaning unwanted data or tables.
• Site owners may lose some data or have corrupt websites due to server hack or use of incompatible plugins. If we have the knowledge to bring the website back to its original form with the help of backups, the situation is handled in a safe manner.
• In the e-commerce websites, all the important data should not get lost during transactions tracking.
• Backups are taken to avoid foreign threats, unforeseen developer errors, corrupted database, website breaks or any other failure attacks.
• The process involving exporting and importing website database during site-migration may lose some data which can be recovered by backups.
• Totally depending on the mercy of your hosting company is not advisable.

Reasons we cannot depend on hosting company backups are as follows: 
 

• Most of the Hosting company backups are not specially designed for WordPress. This may lead to some incompatibility issues.
• The company’s scheduling of taking backups may not be regular to fulfill all the recovery requirements of a sudden disaster.
• Restoring of a single website can be a tedious job without restoring all the sites of a user account.
• The user may not get any notifications for any backup failure .

Note: For safer side, user should maintain his own set of backup solutions using any tool specially meant for WordPress. 

Factors to be considered before deciding for any Backup solution: 
 

• Automation ability: It should be able to automate WordPress backup.
• Backup frequency: User should check for the frequency of taking backups, if its sufficient for the recovery requirement.
• Storage locations: If the solution supports cloud storage like Google Drive or Dropbox, its good enough.
• Restore ability: User should look for the convenience of restoring the files and databases to our system.
• Partial or Full backups: Some backup solutions only saves the database inspite of storing the full website data. Its always better to look for full backups.
• Site migration: It should be able to provide wordpress migration service.

Note: User may face issues like Unsupported web hosts, incomplete migrations, downtime for very large websites during the site migration process. 

For complete backup for any WordPress site, the following are required: 
 

• WordPress blog content
• WordPress files
• All configuration files in the root
• The home directory that consists of the site’s themes, plugins, and the uploaded content.
• The MySQL database that consists of posts, drafts, tag information of posts and comments.
• The forwarded emails, e-mail filters and its configuration.
• The settings of your website.
• The details registered users of the website.

Different methods to take WordPress Backup: Backup of any WordPress site or database can be taken manually or programmatically using any ready-made plugin. 
 

1. Using cPanel: cPanel is just a simple interface which is used to securely manage and take a backup of your WordPress website. Here automatic backup can be scheduled. 
   Log in to the control panel of your respective web host and select the Backups Wizard option under Files section. 
    

1. Within the cPanel, log into the hosting account backend and naivete to the File Manager section. Inside this, we have complete or partial backup options as required. 

   Note: Partial backups are more reliable as it lets the user restore his database if the user is in the same host. 
    

2. Using Command line: 
    

mysqldump -u [username] -p[password] [DBname] > backupFile.sql

1. Where username is the MySQL username, password is the MySQL password, DBname is the database for which you need to be backed and backupFile.sql is the name of the SQL file where the backup script is saved. By default, backupFile.sql is stored in the same folder from where the command is run. 

   Note: In the command line syntax, please give some space before the [username] but NOT before the [password] parameter. When the user wants to restore the database, replace the > with < in the command line syntax as follows: 
    

mysqldump -u [username] -p[password] [DBname] backupFile.sql

1. Using phpMyAdmin: phpMyAdmin comes pre-installed if the WAMP or XAMPP server runs on Linux based operating system. In other cases, you have to install phpMyAdmin and configure it for managing database dumps. After successful installation, login to the phpMyAdmin tool by entering the login credentials. 
    

1. After logging in, we have all the tables in the left column of the dashboard screen. 
    

1. Next, the user has to choose the WP prefixed database option from the left-hand side for the WordPress database. 
   After selecting the database, select the “Export” tab. 
    

1. Clicking on the WordPress database or whatever is the name of the database, the user can expand to list of all tables present in that particular DB. 

   Exporting of the backup file have two methods. They are as follows: 

   • Quick – display only the minimal options: 
     User can choose the default option of SQL format or pick any other formats like CSV, PDF to save your WordPress database. 
     Click on GO button and the final step is done for storing the backup file.
   • Custom – display all possible options: User can choose format specific options and many other in this custom option. 
     Note: If the main purpose is to take backup, then SQL, CSV, XML are preferable formats for backup files.
2. Using WordPress Plugins: For taking WordPress site database backups, using a backup plugin is a safe and quicker option. It is the simplest way to ensure that a website database is completely restored. Most of the plugins available in the market are easy to use, lightweight and well documented. 
   Some of them are as follows: 
   • All-in-One WP Migration
   • BlogVault
   • WP Database Backup
   • WP Remote
   • Import Export WordPress Users
3. Taking time-to-time backups manually: Create a Destination Folder for making WordPress database backup for downloading the required files. Creating a new folder for each backup is a better practice. It allows the user to be more organized and efficient whenever the user wants to restore from the backups.
4. Using File Transfer Protocol: You can create and choose a new folder in the dashboard of the FTP client. Download WordPress files or the required data located on the server. This can be done using FTP client such as FileZilla. 
   FileZilla is a free, open-source, reliable and powerful tool for file transfers to server. In FileZilla, navigate to the right-hand side quadrant, and select the public_html folder for the entire contents of the website. The public_html folder is also called the www, root folder named after your website. To login to the FTP client, the user needs Hostname, Username, Password, and Port. Click Quickconnect button to connect to the server. 
    

1. Once the connection is established, it will just give a message saying ‘Directory listing successful…’. The user should be able to locate his website folder on the right side of the panel ‘Remote Site: ‘. Select the files and folders from the server, and the user can drag and drop to the local system on the left hand side of the panel. The left-hand side of the panel actually shows the local system files and folders. 
    

1. Depending on the size of the website, it will take some time to export the WordPress database. 
    

Additional methods to take WordPress database backup: 

• Using automatic plugin to restore WordPress databases on any cloud service.
• Use of plugins like BackupBuddy which automatically stores on Google Drive and many other cloud services.
• Automatic backing up system installed on user’s website.
• Using self-developed manual scripts.
• Using website’s hosting company own backup system.
• Sending a copy of user’s backup files offsite.
• Schedule regular backups based on daily or weekly full backups.

Conclusions: Websites are great assets to site managers as well as users, so its worth securing and having good enough backup solutions. So the decision taken for keeping the whole system back into its original place is very crucial. This can be done by regularly running a backup of your WordPress database and it should not be overlooked at any cost. WordPress Backups should be taken in such a manner that it ends up restoring your site completely.