backup all mysql databases every 4 hours

[kjg]

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

 

[es2alna]

It's not a professional way but it hits my mind when I read your post:

ls -1 /var/cpanel/users -I '.*'

This will list all your cPanel accounts without ./ and ../

Thanks,

 

[kjg]

Thank you for your reply es2alna

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

// kjg

 

[cPanelMichael]

Hello

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

# mysql
mysql> show databases;

You will find additional information about your databases in the YAML files for the accounts in the following directory:

/var/cpanel/databases/

These files could also be helpful for obtaining database user details:

/var/cpanel/databases/dbindex.db
/var/cpanel/databases/users.db

Thank you.

 

[kjg]

/var/cpanel/databases/dbindex.db

Exactly what I was looking for.

Thank you very much.

// kjg

 

[cPanelMichael]

You are very welcome. I am happy to see that information was helpful.