Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!

backup all mysql databases every 4 hours

Discussion in 'Data Protection' started by kjg, Apr 6, 2014.

  1. kjg

    kjg Well-Known Member

    Joined:
    Mar 2, 2004
    Messages:
    154
    Likes Received:
    3
    Trophy Points:
    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
     
  2. es2alna

    es2alna Well-Known Member

    Joined:
    Mar 30, 2014
    Messages:
    67
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Egypt
    cPanel Access Level:
    Root Administrator
    It's not a professional way but it hits my mind when I read your post:
    Code:
    ls -1 /var/cpanel/users -I '.*'
    This will list all your cPanel accounts without ./ and ../

    Thanks,
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  3. kjg

    kjg Well-Known Member

    Joined:
    Mar 2, 2004
    Messages:
    154
    Likes Received:
    3
    Trophy Points:
    168
    Thank you for your reply es2alna

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

    // kjg
     
  4. cPanelMichael

    cPanelMichael Technical Support Community Manager
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    44,698
    Likes Received:
    1,883
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    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.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  5. kjg

    kjg Well-Known Member

    Joined:
    Mar 2, 2004
    Messages:
    154
    Likes Received:
    3
    Trophy Points:
    168
    Exactly what I was looking for.

    Thank you very much.

    // kjg
     
  6. cPanelMichael

    cPanelMichael Technical Support Community Manager
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    44,698
    Likes Received:
    1,883
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    You are very welcome. I am happy to see that information was helpful.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
Loading...

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice