backup all mysql databases every 4 hours

kjg

Well-Known Member
Mar 2, 2004
160
3
168
Hi
I'm trying to create a simple script to run as cron to backup all mysql databases on each server every 4 hours and keep it for a day so there will always be 4 copies of each database.

It works fine except that it is copying all mysql databases and I really only need the account databases. As it is now, it copies also all system databases such as cphulkd, ibdata1, ib_logfile0, exinstats, modsec, mysql, mysql_upgrade_info, etc etc)

I could add a test for the system databases and avoid copying them, but I would prefer if there is a way to find/list only account databases.

So the line I would need help with is where I find the databases to loop through:
"for DBNAME in `ls -1A /var/lib/mysql/ | egrep -v "\.sock|\.user|\,/" | cut -d/ -f1`"

The script we use:

#!/bin/bash
#
# Backup directory.
backup_dir="/backup_x/mysql_backup/"
#
#Date string to add to backup-file
backup_date=`date +%H`
#
#How long to keep backups of database (days)
number_of_days=1
#
for DBNAME in `ls -1A /var/lib/mysql/ | egrep -v "\.sock|\.user|\,/" | cut -d/ -f1`
do
echo Dumping $DBNAME to $backup_dir$DBNAME\_$backup_date.sql
mysqldump -h localhost $DBNAME > $backup_dir$DBNAME\_$backup_date.sql
done
find $backup_dir -type f -prune -mtime +$number_of_days -exec rm -f {} \;

//kjg
 

kjg

Well-Known Member
Mar 2, 2004
160
3
168
Thank you for your reply es2alna

But I was looking for a way to list the accounts databases, not just the accounts.

// kjg
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,904
2,220
463
Hello :)

You can run the following command if you simply want a list of all databases:

Code:
# mysql
mysql> show databases;
You will find additional information about your databases in the YAML files for the accounts in the following directory:

Code:
/var/cpanel/databases/
These files could also be helpful for obtaining database user details:

Code:
/var/cpanel/databases/dbindex.db
/var/cpanel/databases/users.db
Thank you.
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,904
2,220
463
You are very welcome. I am happy to see that information was helpful.