View all posts

Script to backup databases without table locking on cPanel servers

It may be necessary to back up databases more frequently than those that are executed via WHM, or you might want to back up a specific database.

Create a file with the following content /root/scripts/backupdb.sh:

#!/bin/bash

BACKUPDIR="/backup/databases/"
DATE=`date +%s`

if [ ! -e $BACKUPDIR ]; then
mkdir -p $BACKUPDIR
chmod 700 $BACKUPDIR
fi

for x in `mysqlshow | grep -v \_schema | awk -F "| " '{print $2}'`; do mysqldump --single-transaction=TRUE $x | bzip2 -9czq > $BACKUPDIR$x-sql-$DATE.bz2; done

/usr/bin/find $BACKUPDIR ! -mtime -3|/usr/bin/xargs rm -f

chmod 600 $BACKUPDIR/*

Then set the permissions so that it can be executed:

chmod 750 /root/scripts/backupdb.sh 

Afterwards, this can be added to the root’s crontab so that it is executed at the desired interval.

Edit your crontab with the following command:

crontab -e

Enter the following line to run the script every 8 hours.

0 */8 * * * /root/scripts/backupdb.sh >/dev/null 2>&1

That’s it!

The script will create /backup/databases/ if it does not exist, backup all databases to that path without table locking, remove those older than 3 days, and then change permissions to more restricted permissions on those SQL dumps. If you want to change the number of days, simply change -mtime -3 accordingly.

Related Articles...

How to sync server time

How to sync server time in CentOS & AlmaLinux

The NTP protocol synchronises the clocks of computers over a network. In this article, we will show you how to quickly sync the server time. Many systems-related tasks and processes require the correct timezone. For example, the cron daemon uses... Read more
Disable Currency WHMCS

Disable Currency in WHMCS

In order to force a particular currency in WHMCS, we can create a hook file. First, create a hook file like so: /home/user/path-to-whmcs/includes/hooks/myhook.php With the following code: From now on, users cannot change their currency from... Read more

This website uses cookies

We use cookies for the analysis of our visitor data, to improve our website, and to give you a great website experience. For more information about the cookies we use, please see our cookie policy.