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/


DATE=`date +%s`

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

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/ 

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/ >/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...

Install LAMP

How to install LAMP Stack on Ubuntu 20.04

What is LAMP? The LAMP acronym stands for Linux, Apache, MariaDB/MySQL, and PHP, all of which are free and open source. It is the most commonly used software stack for dynamic websites and web applications. The operating system is Linux, the... 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.