MySQL Backup Script

MySQL Backup Script

 

As you all know, the information stored in your database is very important, so backing it up regularly is an essential task.

Most WebHosting providers offer free daily backup service. However if you would like to be on the safe side and generate your MySQL backups manually, you can use this simple bash script which would export your database and store it on your server.

Before you get started you need to gather the following information:

  • MySQL Username
  • MySQL Password
  • MySQL Database name
  • MySQL Host (in most cases it would be localhost, however please double check that)

Once you have the above information you could simple use the following script.

# Using your favorite editor, open the following file:
vim ~/manualBackup.sh

# Then add the following content and update the required details:


#!/bin/bash
# Your Database login details
# Please update those accordingly

dbHost="localhost"
dbName="databasName"
dbUser="USER"
dbPass="PASS"

#How many days the backups should be kept for before being removed
keepBackupsFor="7"

if [ ! -d ~/manual_backups ]; then mkdir ~/manual_backups; fi

curTime=$(date +"%m-%d-%y_%H:%M")

mysqldump -h ${dbHost} -u ${dbUser} -p${dbPass} ${dbName} > ~/manual_backups/${curTime}-backup.sql

find ~/manual_backups -type f -ctime +${keepBackupsFor} -exec rm -f {} \;

#END

#Now if you are on a shared server please set secure permission:
chmod 600 ~/manualBackup.sh

Once we have the script ready, you can run it manually via your terminal to make sure that the database is actually being backed correctly.

bash ~/manualBackup.sh

Then check if the MySQL backup was generated as expected:

ls -lah ~/database_backups

If everything is working as expected, you could then setup a cron job which would trigger the script and backup your database daily. The cron job would look something like this:

* */12 * * * /bin/bash /path/to/your/script/manualBackup.sh

If you set -> * */12 * * * the script will be executed every minute at 12h and again at 24h. You can modify this so that the backups are ran at a convenient for you time.

This is pretty much it, now your database and your important data will be backed up regularly.

Coffee For Me
Recent Posts
Proxies Demystified
Proxies Demystified
2019-11-05 09:31:05