Many website owners/ bloggers fail to back up their websites database. This is a very bad practice because you don’t know when your hosting company will decide to go out of business. They can decide to close down today leaving many websites stranded with nothing. You’ve lost all your websites data, your posts, images, everything.

The best way to avoid these problems is to back up your SQL database. Which is our main scope of this article. We’re going to show you a step by step process to back up your websites SQL database. we’ll be Making a backup of our own SQL database using our phpmyadmin tool. You can access your phpmyadmin tool through your websites cpanel.

Cpanel phpmyadmin tool
The phpmyadmin tool on cpanel

if it’s on localhost, first of all start the Apache and MySQL server in your XAMPP application. Then open your browser and type “localhost/phpmyadmin” on your address bar and you’ll find yourself in the phpmyadmin dashboard. This is the area you can manage your database.

Phpmyadmin home page
The phpmyadmin interface

Dangers of not backing up your database.

  • If your host closes down or crashes, you will lose all your websites data. If you’ve been blogging for more than a year, you’ll discover that all your previous posts which have been generating income for you over the years will dissapear and you’ll lose your source of income.

SQL backup database.

With the phpmyadmin tool, you can backup an entire database just with a few clicks. Simply select the database tab on the home page and select the database you want to backup.

SQL backup database
SQL backup database.

As you can see here, we have five databases in our phpmyadmin, just pick the database you wish to back up and a window will open showing you all the tables contained in that database.

Since we want to backup the entire database, it’ll include all the tables we need, so there’s no need to select any table. Just click the export button on the top nav to begin the export.

Phpmyadmin export tab
Phpmyadmin export tab

Once your at this point, you’ll be asked to choose an export method. I’ll advice you to select quick, unless you have some specific tables you want to omit, or changes you want to make.

Select “Go” and download your SQL database file In your preferred location. Once you’ve downloaded this file, you can simply upload it to Google drive or Dropbox for it to be stored and downloaded anytime.

SQL backup table.

Just as you can backup your entire database, you can also create a backup or local copy of any table of your choice in the database. I’m going to show you just how to do that.

To back up a specific database in your table, first select the database in your database section in phpmyadmin. Then select the specific table you want to backup. Click export, then make sure quick is selected and press go.

You’ll download your own copy of that table in a file format and upload it to any cloud storage platform of your choice.

Exporting a table is similar to exporting the entire database. The only difference is that in exporting the database, we didn’t select any table before clicking the export tab. This alone makes the difference.

SQL backup master.

Another way you can backup your database is using a free tool like SQL backup master

What is SQL backup master?

SQL Backup Master is a freemium webmaster tool backs up your SQL Server databases to any number of popular cloud storage services such as Dropbox, OneDrive, Amazon S3, Microsoft Azure, and Google Drive. It can also back up databases to an FTP server or a folder on your local machine, network server, or storage device.

It is an easy to use tool with a good user interface which helps to back up your SQL database with ease. You can download SQL backup master from their Official website . You can also view how to use SQL backup master Here.

MySQL backup scripts.

Another interesting way to backup your website is using MySQL backup scripts. This method is interesting for programmers because with this combined with their own scripts, they can easily automate their backups without having to go through the stress of creating backups Everytime you make a new post. Or every month which can be difficult to remember some times. Here’s the script which you can copy and paste in your websites code.

/bin/bash

################################################################
##
## MySQL Database Backup Script
## Written By: Rahul Kumar
## URL: https://tecadmin.net/bash-script-mysql-database-backup/
## Last Update: Jan 05, 2019
##
################################################################

export PATH=/bin:/usr/bin:/usr/local/bin
TODAY=`date +"%d%b%Y"`

################################################################
################## Update below values ########################

DB_BACKUP_PATH='/backup/dbbackup'
MYSQL_HOST='localhost'
MYSQL_PORT='3306'
MYSQL_USER='root'
MYSQL_PASSWORD='mysecret'
DATABASE_NAME='mydb'
BACKUP_RETAIN_DAYS=30 ## Number of days to keep local backup copy

#################################################################

mkdir -p ${DB_BACKUP_PATH}/${TODAY}
echo "Backup started for database - ${DATABASE_NAME}"


mysqldump -h ${MYSQL_HOST} \
-P ${MYSQL_PORT} \
-u ${MYSQL_USER} \
-p${MYSQL_PASSWORD} \
${DATABASE_NAME} | gzip > ${DB_BACKUP_PATH}/${TODAY}/${DATABASE_NAME}-${TODAY}.sql.gz

if [ $? -eq 0 ]; then
echo "Database backup successfully completed"
else
echo "Error found during backup"
exit 1
fi

That’s all the methods we can share for now. You can subscribe to our mailing list to receive more web development updates from us. You can also follow us on social media to get more web development updates or programming memes on our Facebook page.

Also remember to leave your feedback in the comments section, We’ll love that.

Your favorite web developers, kudos!


vic okeke

I'm just a web developer hoping to help out junior developers in algorithms for their projects.

0 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *