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 -eEnter the following line to run the script every 8 hours.
0 */8 * * * /root/scripts/backupdb.sh >/dev/null 2>&1That’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...
