SOLVED mysql databases (only) backup

tirrawarra

Registered
Sep 4, 2012
4
0
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
 

cPanelTristan

Quality Assurance Analyst
Staff member
Oct 2, 2010
7,607
41
348
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
 

tirrawarra

Registered
Sep 4, 2012
4
0
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


















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
 

cPanelTristan

Quality Assurance Analyst
Staff member
Oct 2, 2010
7,607
41
348
somewhere over the rainbow
cPanel Access Level
Root Administrator
Hi Steve,

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?
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!
 

anoop1285

Member
Jan 31, 2009
8
0
51
# 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
 

tirrawarra

Registered
Sep 4, 2012
4
0
1
cPanel Access Level
Root Administrator
thanks anoop1285

i'll try that - much appreciated.

thanks Steve




# 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
 

kjavitz

Member
Aug 25, 2005
5
1
153
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
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
 

g18c

Member
Jul 7, 2007
23
0
51
Was there any update to this in the end? I need the same script for the same rsync purpose
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,884
2,243
463