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.

mysql databases (only) backup

Discussion in 'Data Protection' started by tirrawarra, Sep 4, 2012.

  1. tirrawarra

    tirrawarra Registered

    Joined:
    Sep 4, 2012
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hello.

    On an account basis cPanel is fantastic for backups - but this is manual - you need to login and select the options etc.

    Is there an automated way to get regular backups of the mysql databases (only) on an account basis.
    I know I can get the whole database will all accounts easily but I need a way to get a regular copy of the individual account databases - preferably just like the type you would get if you logged into an account and used the account backup tools

    is it possible to backup the mysql databases only on an (BULK / ALL) account basis and dump them to a directory or FTP them out.

    in the configure backups interface I have these options:

    Backup SQL Databases (Per Account Only)
    Compress Account Backups
    Backup Accounts

    ----------------------------------------------------------------

    Im wondering if I can set the options as follows:

    • Backup Accounts x DISABLED
    • Backup SQL Databases (Per Account Only) x ENABLED
    • Compress Account Backups x ENABLED


    I might get the databases only?

    I use rysnc to get the webfiles daily and take regular snapshots with a rdiff type tool to manage content but I cant find a solution for mysql files. (I know I can get whole of database - but that does not suit my need)

    any help would be appreciated.

    ta Steve
     
  2. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Hello Steve,

    If you test your theory, you'll see it doesn't work to back up the MySQL databases, which might be why you are asking about it. Best in that scenario if you've tested it to state it doesn't work. In fact, even if you enable configuration files or if you enable the entire MySQL directory, it doesn't save /backup/cpbackup/daily|weekly|monthly/dirs/_var_lib_mysql.gz file for compressed files.

    In fact, the only way I could get it to back up was to do the following settings:

    Backup Status > Enabled
    Backup SQL Databases > Entire MySQL Directory > Enabled
    OR
    Backup SQL Databases > Per Account and Entire MySQL Directory > Enabled
    Backup Type > Standard > Enabled
    Backup Destination > /backup

    Even then, it is still only backing up a compressed file (yeah compressed at /backup/cpbackup/daily|weekly|monthly/dirs/_var_lib_mysql.tar.gz even though compressed is disabled) and does not provide individual user .sql backups for the accounts. Only the flat files of /var/lib/mysql location.

    If you believe this to be a product defect, please submit a bug report to http://go.cpanel.net/bugs

    I did see if I could find any existing report and the closest one was 32045 where MySQL backups weren't originally generating unless configuration files were enabled to be backed up. The fact these don't back up with compression selected is most likely an issue.

    Thanks!

    Everything else was set to disabled
     
  3. tirrawarra

    tirrawarra Registered

    Joined:
    Sep 4, 2012
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hey Tristan,

    thanks for the reply.

    Because its a live system I tend not to play/test until I hopefully have a reasonable understanding/procedure to follow.

    Im not sure from your response if there is a way for 'root' to generate individual account mysql backups.

    I know how to backup the whole MySQL system automatically - through the backup routine but if one client has an issue I dont want to:
    • sort through a massive aggregated file
    • or worse still roll back all my clients to solve one clients issues?
    • plus in most of my client cases the database is small and the surrounding files large.
    • and the files are unrecognisable - compared to the "account_name.sql" type available to the cpanel users front end
    • to keep multiple backups on server in expensive web-space is not cost effective

    What I'm hoping to find is a way to get a copy of all the individual 'account.sql' files OR the equivalent compressed versions like the cpanel front end generates- and put in one spot.

    But I want to do it as 'root' (or at least one user)

    I know it can be done now if I write a script with the:

    $mysqlhost[]='localhost';
    $mysqlusername[]='mysqluser';
    $mysqlpassword[]='mysqlpassword';
    $mysqldbname[]='mysqldatabasename';

    and run it via cron ...

    but this is problematic - because I have lots of accounts and passwords change.

    Im looking for a solution more similar to the individual style output that my end users (cPanel accounts) enjoy.

    ===============================================

    2 questions:

    Is it possible for the WHM user to generate in bulk or via one script a regular backup of all the cpanel user account's sql files (compressed or not)

    if so how?



    thanks Steve


















     
  4. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Hi Steve,

    No, it isn't possible to generate an sql file period in WHM using the WHM > Backup > Configure Backup interface when you are only generating MySQL backups (you can generate them if you select user account backups and MySQL backups there).

    Since it doesn't seem to work, you may wish to open up a bug report as I've already suggested. You are welcome to re-read my prior post on what does work and use the link in it to submit that bug report.

    Thanks!
     
  5. tirrawarra

    tirrawarra Registered

    Joined:
    Sep 4, 2012
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    backing up the individual accounts as root can not be done en masse - I have reported as a bug. Then requested as a new feature.

    Steve
     
  6. anoop1285

    anoop1285 Member

    Joined:
    Jan 31, 2009
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    1
    # mkdir /backup/db_backup
    # mysql -e "show databases" > /root/mysql.txt

    Open /root/mysql.txt and remove the first 2 lines ( Database, performance_schema & information_schema )

    then issue the following

    # for i in `cat /root/mysql.txt`; do mysqldump $i > /backup/db_backup/$i.sql; done

    you can put the above command in another file say mysql.sh and use cron to backup regularly. For date based you can use the following

    for i in `cat /root/mysql.txt`; do mysqldump $i > /backup/db_backup/`/bin/date +"%Y-%m-%d_%H:%M:%S"`_$i.sql; done
     
  7. tirrawarra

    tirrawarra Registered

    Joined:
    Sep 4, 2012
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    thanks anoop1285

    i'll try that - much appreciated.

    thanks Steve




     
  8. kjavitz

    kjavitz Registered

    Joined:
    Aug 25, 2005
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    any updates on this and where is the feature request? i need this too and even posted a script that does this as a paid project on freelancer
     
  9. g18c

    g18c Member

    Joined:
    Jul 7, 2007
    Messages:
    22
    Likes Received:
    0
    Trophy Points:
    1
    Was there any update to this in the end? I need the same script for the same rsync purpose
     
Loading...

Share This Page