The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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:
    147
    Likes Received:
    0
    Trophy Points:
    16
    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,
     
  3. kjg

    kjg Well-Known Member

    Joined:
    Mar 2, 2004
    Messages:
    147
    Likes Received:
    0
    Trophy Points:
    16
    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 Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    676
    Trophy Points:
    113
    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.
     
  5. kjg

    kjg Well-Known Member

    Joined:
    Mar 2, 2004
    Messages:
    147
    Likes Received:
    0
    Trophy Points:
    16
    Exactly what I was looking for.

    Thank you very much.

    // kjg
     
  6. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    676
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
Loading...

Share This Page