backup all mysql databases every 4 hours

kjg

• April 06, 2014 05:54

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

  • April 06, 2014 15:49

  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,

  0
• 

  kjg

  • April 06, 2014 16:01

  Thank you for your reply es2alna But I was looking for a way to list the accounts databases, not just the accounts. // kjg

  0
• 

  cPanelMichael

  • April 07, 2014 14:43

  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.

  0
• 

  kjg

  • April 07, 2014 14:55

  [quote="cPanelMichael, post: 1614422"> /var/cpanel/databases/dbindex.db
  
  Exactly what I was looking for. Thank you very much. // kjg

  0
• 

  cPanelMichael

  • April 07, 2014 18:34

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

  0

Please sign in to leave a comment.