Please Help With Mysql Backup (run W/ Cron Job)

[MarkMan]

I'm very new to this but I know I'm so close to making this work! There has been quite a bit documented on this topic and I've scoured these forums for hours trying to get this script running smoothly. Hope you don't mind one more...

 

I set up a Cron job to run a MySQL backup script listed in these TCH forums.

 

Here is the error I'm getting now:

ERROR 1045: Access denied for user: 'BUuser@server5.totalchoicehosting.com' (Using password: YES)

 

I have fiddled with different values for the different variables in the script, but keep getting errors. I've also set the script file to have full permissions at this point (777)

 

Here is the part of the script where the variables are set. I'm thinking the host name is the value that is incorrect: (Thanks for any help you can give! )

 

__________________________________________________________

 

# Username to access the MySQL server e.g. dbuser

USERNAME=BUuser

(this is a legitmate user for the DB and it has full permissions)

 

# Username to access the MySQL server e.g. password

PASSWORD=<PW>

(I put in the correct password for the user above... Note: comment seems to have a typo? --should be pw not username)

 

# Host name (or IP address) of MySQL server e.g localhost

DBHOST=localhost

(I also tried my domain ---> mydomain.org )

 

# List of DBNAMES for Daily/Weekly Backup e.g. "DB1 DB2 DB3"

#DBNAMES="phpbb1" (tried the exact DB name also, ended up going with 'all' which is described in complete script)

DBNAMES=all

 

# Backup directory location e.g /backups

BACKUPDIR=/home/username/BU/MySQL

(sub'd in my username)

 

# Mail setup

# What would you like to be mailed to you?

# - log : send only log file

# - files : send log file and sql files as attachments (see docs)

# - stdout : will simply output the log to the screen if run manually.

MAILCONTENT="stdout"

 

# Set the maximum allowed email size in k. (4000 = approx 5MB email [see docs])

MAXATTSIZE="4000"

 

# Email Address to send mail to? (user@domain.com)

MAILADDR="username@domain.org"

(put in my email)

[TCH-Don]

I have a few things different

 

The script automysqlbackup.sh in the top folder is set to 755

 

I created the folder backupmydatabase in public_html and set it to 777

 

# Backup directory location e.g /backups

BACKUPDIR="/home/tch-cpanel-name/public_html/backupmydatabase

 

I assume you created the folders BU in your top folder and then MySQL folder inside BU

and the case is correct

# Backup directory location e.g /backups

BACKUPDIR=/home/username/BU/MySQL

 

For db names I used

 

# List of DBNAMES for Daily/Weekly Backup e.g. "DB1 DB2 DB3"

DBNAMES="tch-cpanel-name_database1 tch-cpanel-name_database2"

 

But for the output you need to change it to files I would think not stdout

if you want the backup mailed to you.

 

# Mail setup

# What would you like to be mailed to you?

# - log : send only log file

# - files : send log file and sql files as attachments (see docs)

# - stdout : will simply output the log to the screen if run manually.

MAILCONTENT="files"

[TCH-Thomas]

Welcome to the forum, MarkMan.

[TCH-Don]

where are my manors

Welcome to the forum, MarkMan

[TweezerMan]

Welcome to the forums, MarkMan!

[TCH-Bruce]

Welcome to the forum, MarkMan

[cajunman4life]

Welcome to the forum, MarkMan

[MarkMan]

Thanks everyone for the warm welcome. BTW, i've been 'lurking' in these forums for a couple months and they've been of tremendous help.

 

I think I have tried just about every combo, except the right one .

 

Just the automysqlbackup.sh script was set to 777, so I changed it and the top the top folder to 755.

 

Yep, my backup folder is created and set to 755. This seems to be ok because the script created sub-folders within.

 

The DBNAME variable name has been both 'all' and the exact db name.

 

I've tried all three of the mail options, it's now set back to 'files'

 

Other threads said not to place the script in the public_html directory because it contains a password; the preferred directory seemed to be "etc". They also suggested to limit the permission to 700.

 

I did give it a go in the public_html directory and received the same type error as before. I'm back to running it outside of the public_html dir and this is the error:

mysqldump: Got error: 1045: Access denied for user: 'BUuser@server5.totalchoicehosting.com' (Using password: YES) when trying to connect

/home/<username>/etc/automysqlbackup.sh: line 568: mutt: command not found

 

Don, what do you have "DBHOST" set to? Does 'BUuser@server5.totalchoicehosting.com' look correct in the error output?

[TCH-Bruce]

# Username to access the MySQL server e.g. dbuser

USERNAME=BUuser

Your username should be your-cpanel-login_BUuser

 

Also your database names will be prefaced by your-cpanel-login_ and will have to be listed separately I believe.

[MarkMan]

Your username should be your-cpanel-login_BUuser

 

Also your database names will be prefaced by your-cpanel-login_ and will have to be listed separately I believe.

 

Thanks Bruce, I gave that a try. I'm still getting the access denied error, but now it says something about 'mutt' as well?

 

mysqldump: Got error: 1045: Access denied for user: '<cpanel_username>_BUuser@server5.totalchoicehosting.com' (Using password: YES) when trying to connect

/home/cpanel_username/etc/automysqlbackup.sh: line 568: mutt: command not found

 

FYI... line 568 of the script reads:

mutt -s "MySQL Backup Log and SQL Files for $HOST - $DATE" $BACKUPFILES $MAILADDR < $LOGFILE #send via mutt

[MarkMan]

Whew! I am soooo close now.

 

Thanks Bruce, i set the db name and user as you suggested then set my domain back to 'localhost'. It looks like the backup is being made now. So it appears the access error is fixed. There is still this 'mutt' issue. Does this have to do with some type of email notification?

 

Error:

/home/<TCH_username>/etc/automysqlbackup.sh: line 568: mutt: command not found

 

 

Line 568 of the script:

mutt -s "MySQL Backup Log and SQL Files for $HOST - $DATE" $BACKUPFILES $MAILADDR < $LOGFILE #send via mutt

Edited November 5, 2005 by MarkMan

[TCH-Bruce]

That would be a program the script is trying to call to send you the email. You can check with the help desk and see if they will install it on the server for you or you can change the script to call sendmail in place of it.

[MarkMan]

Sorry for having to be spoonfed here...

 

Any ideas on how to change the following line of code to use sendmail instead of mutt? Replacing mutt w/ sendmail did not seem to work either.

 

mutt -s "MySQL Backup Log and SQL Files for $HOST - $DATE" $BACKUPFILES $MAILADDR < $LOGFILE #send via mutt

[TCH-Rob]

Welcome to the forums MarkMan.

[TweezerMan]

Just changing 'mutt' to 'sendmail' won't work, as mutt and sendmail have different command lines and different options. Sendmail does not allow specifying the file name of an attachment in its command line, so the script would need to construct the e-mail itself (which would require lines of additional code) before calling sendmail.

 

If mutt is not available on your server, and the Help Desk will not install it, and you'd like to be e-mailed your database backups, I'd suggest looking for a different script (probably one written in PHP, rather than a shell script).

[MarkMan]

Sounds good, thanks for the info. I'll look into it.

[MarkMan]

I've found that running a batch file which FTPs the files from the webserver to my home computer is much easier. It can be automated to eliminate the need for user interaction and there's no need to fuss with email messages and attachments.

 

I also have this setup for my site backup file. After FTPing it to my home computer it then deletes the file off the webserver to keep things clean.