AutoMySQLBackup on VPS

dkap

dkap

New Member
Has anyone successfully installed AutoMySQLBackup on their VPS? My thinking is, databases are the only thing that really needs backing up, as all other files are maintained offline. (Of course, there's the hassle of restoring them manually. But weighed against the fact that I've never had to restore files in nearly 15 years of website hosting, it seems like a minor concern.) As such, AutoMySQLBackup seems like the perfect solution, with just the right feature set and good user reviews. Only problem is, I haven't quite figured out how to get it working.

(Looks like I can't post links until I have a post count of 5, so I guess I'll have to throw in a few dummy posts below this one so I can post the URL to the script...)

I tried installing it within the /etc/ directory of the main domain on my account, and placing the CONFIG_backup_dir path under that, so as to be able to easily FTP in and access them for occasional offline storage.

The installation said it completed successfully and I get no error logs mailed, but nothing runs at the scheduled (cron) time, either. Any help would be appreciated! If no one here has any experience with the script, I'll try at their support forums. I thought it would be a useful application here for VPS owners, though, so best to bring it up in this setting.

Dan

p.s. I've been with KnownHost for about a month now ... first post in the forums, loving the service!
 
Hi Dan,

I don't use this particular script but might be able to help point you in the right direction.

Did you make it executeable?
Did you edit the conf to put in the user/pass and DB names?
Did you run it manually in SSH to see if it ran?
Have you checked the cron log to see what it says? (pico /var/log/cron then search (ctrl-w) for the file name)

Let us know what you find :)
 
I can't think of a better named person to receive assistance from. :)

I checked via SSH and got the script to run -- it created backup sub-directories, but didn't actually create any backups within them. That's more than it did before, so you may be on the right track with the cron file not being executable.

Is there an issue with it being installed under the main domain on the account but using the root username/password for accessing all of the databases? I got a bit confused on that matter... The whole point is to back up all the databases on my VPS at once, rather than having to install this script on each of them, but maybe that doesn't work with the location I installed it?

edit: Aha, just received the email log file with some clues:

Email #1, Subject: MySQL Backup Log for localhost - 2012-08-10_00h10m
# Parsing databases ... Note: Parsed config file
/path/to/automysqlbackup/myserver.conf.
Note: /path/to/automysqlbackup/automysqlbackup.conf was not found - no global config file.
Error: The mysql server is empty, i.e. no databases found. Check if something is wrong. Exiting.

Email #2, Subject: ERRORS REPORTED: MySQL Backup error Log for localhost - 2012-08-10_00h10m
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Looks like there's an issue with the reported paths in the first email (I edited them out as shown above), so that gives me something to look into.

edit 2: Fixing the paths still results in the database access issues. When I originally trying installing it as recommended, I got error messages like: "cannot create regular file `/usr/local/bin/automysqlbackup': Permission denied"

Dan
 
Morning Dan!

Ok so for edit2 that's the executable file so if that file were not created you would just get file not found when you tried to run it. If you installed it as root you should not have had any problems with permissions.

You say you got the path problem sussed out so now it looks like maybe a login/password problem. I use a program to do MySQL backups as well and I run it as root using the root login/password as well. One difference that I see is I do not use 'root@localhost' I simply use 'root'.

I also noticed in the quick look at the link you posted that you have to put each database name into the conf file so if you have not done that you might need that too.

Give those a try with another manual run and let's see what we get :)
 
I was just heading back to post an update...

I had an error in the cron file, which fixed a secondary issue. Changing the root username/password to that of the account the script is installed under got it working, albeit with some issues. Each database being backed up resulted in:

chown: changing ownership of `/path/to/backups/daily/dbname': Operation not permitted

But, the backups were actually created, so I tried switching it back to the root login and that brought me back to the no databases, access denied error from previously. (It also looks like it ran into a roadblock with the chown warnings from above, but maybe it would have gotten past that if not for access being denied.)

One difference that I see is I do not use 'root@localhost' I simply use 'root'.
Click to expand...
Interesting. I tried it with CONFIG_mysql_dump_host='' instead of the default localhost setting, but that didn't seem to change the behavior. Still lots of chown errors and the failed log (access denied) email.

I also noticed in the quick look at the link you posted that you have to put each database name into the conf file so if you have not done that you might need that too.
Click to expand...
Actually, that's one of the nice things about this script: Leaving the db list blank makes it back up all of them. That seems much preferable to having to remember to update the list whenever new accounts or new domains are set up. I confirmed that that worked as it should on the single account's user/pswd test.

Maybe I should be asking the bigger question, however: How would I go about doing a root install? That would presumably take care of the root login issue, and might address the chown problem. I don't care where the script is installed, so long as I can easily FTP into the backup folder for occasional download.

The problem is, I haven't found an explanation of how to get the installation and config files on the server in the first place if doing so under root, as I don't have any FTP access for that. I'm sure there's some trick there that I'm missing... Ditto for setting up the cron job if it's not within the account folder for cPanel access. (I don't know if there's a /etc/cron.daily/ here like the script's instructions say to use.)

Dan
 
Also, I think there was a missing variable in the myserver.conf config file's default setup. I had to add in CONFIG_configfile to specify the override for my non-root path. The hard coded path kept showing up in errors until I added that in.

Dan
 
Hi Dan,

Ok I misunderstood completely, I was under the impression that you had installed the script as root! That explains your installation errors and a lot of the trouble you are seeing as account users do not have the permission to install scripts anywhere other than in their own account space in /home! Installing it as root will probably get rid of all the problems you are having.

At this point I would say either wipe it out and start over or chown the files to root:root. Personally I would go the starting over route.

To connect to ftp as root:
server: your server name
connection type: secure SFTP (via SSH)
port: 2200
username: root
pass: your root pass

Once it's uploaded just login to SSH, install, and configure and I bet you will see you troubles go away :)
 
Thanks for the SFTP info. I played around with PuTTY's SFTP client, but I couldn't figure out how to get that to work, so I switched over to FileZilla and had much more success. However, after completing the install (with no errors), I'm still getting access denied while running it under root ... with and without localhost specified.

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Error: The mysql server is empty, i.e. no databases found. Check if something is wrong. Exiting.

The only other thing I can think of is to hard code the databases, but that wasn't necessary on the account-level installation (when changing the user/pswd to be specific to that account). Of course, it looks like the "no databases" error is just a result of the access denied.

Dan
 
This may be a really stupid question, but the root password is the same for MySQL access, right? I know I can view all of the accounts' databases in phpMyAdmin when logged into WHM as root, so I assume that same password accesses them all.

Dan
 
Maybe there's a server configuration issue? When I try:

mysql -uroot -pXXX mysql

I get the same access denied error. (I tried it with both the root password and the encrypted version of it displayed in phpMyAdmin. I also confirmed that user root on localhost, host.mydomain.com, and 127.0.0.1 have all the MySQL permissions.

I tried a few other host/user/pswd combos unsuccessfully, but the above issue appears to be a major sticking point.

Dan
 
Problem solved! KH Support informed me that the root MySQL password is not the same as the root account password.

Thanks for the help.

Dan
 
Recapping things, for anyone wanting a MySQL backup solution in place of paid, full account backups. Again, my line of thinking is, restoring from backups is rarely needed and I have an offline copy of all files should the need arise, but database content is irrecoverable if routine backups are not made.

AutoMySQLBackup
http://www.debianhelp.co.uk/mysqlscript.htm
http://sourceforge.net/projects/automysqlbackup/

Install the script per the README instructions as root, using SFTP (port 2200) and your server name / IP. The instructions don't specify it, but you need to upload all the files, including LICENSE, README, etc., or it won't install. Log in with:

username: root
password: account root pswd

Move to the chosen directory and install via "./install.sh". The default options it presents will work fine. Download the myserver.conf file it creates and set the following; some can be left as default:

CONFIG_mysql_dump_username='root'
CONFIG_mysql_dump_password='XXX'
CONFIG_mysql_dump_host='localhost'
CONFIG_backup_dir='/path/to/where/you/want/the/backups'
CONFIG_mailcontent='log'
CONFIG_mail_address='you@yourdomain.com'

According to KH Support, your account root password and MySQL root password should be the same. That was not the case for me, however, so be aware that you may have to inquire with them as to what your MySQL root password is initially set to. (On further thought, I think it was a result of my cPanel migration to KH ... I shared a dedicated server elsewhere previously and didn't have full root access, so I don't know what the MySQL root password was there. That probably overwrote the pswd here.) Or, just use the WHM utility for changing it to something you know.

Make sure your cron file ('runmysqlbackup') is saved with Unix line endings (or run "dos2unix runmysqlbackup"), otherwise the cronjob will not work. I think it may have been a result of copying the file's contents from the README instructions, as opposed to uploading an already existing file.

It's probably best to just use the default backups dir path. I wanted mine under my main account for easy FTP access, as I wasn't yet up and running with SFTP, but to make that practical requires changing the owner settings within the cron file.

Everything appears to be working smoothly now with the daily and weekly backup rotation. Obviously, I haven't had it running long enough to test the monthly rotation. I hope the above helps other people!

Dan
 
Thanks for the follow up Dan and I am glad to hear you got everything up and running now! :)
 
You must log in or register to reply here.
Top