cron mysql backup empty

[jpistone]

I'm running a cron script that is supposed to back up a database. It's appearing on schedule and is created where it's supposed to, but the file is empty each time it's run. It runs fine otherwise and there is plenty of records in the original db, just not the backup file. The command looks like this:

mysqldump -myaccount -thepassword crontestdb > /home/myaccount/public_html/cronbackups/crontest_backup.sql

Thanks.

[shadmego]

Try using this instead:



mysql -u "db_user" -p'Password' "db_name" > /path/to/database/dump.sql

[alienspaces]

mysqldump -u [username] -p[password] [databasename] > /path/to/database/dump.sql


Note: No spaces between -p and your password

[shadmego]

It's constantly changing and the last time I tried, I could not get mysqldump to work, which is why I had to go with mysql instead.

If you can get mysqldump to work more power to you, but I offer mine as an alternative if the first doesn't work.

[jpistone]

Thanks, I tried mysql and mysqldump and the first showed up but was 0B in size (empty). The second didn't even show up.

Here's what I have:

mysql -u myaccount -pmypassword crontest > /home/myaccount/public_html/cronbackups/crontest_backup.sql

Here's what in the cronbackups directory:

crontest_backup.sql Microsoft Document 0 bytes

The mySql file has a couple of hundred records in it, and after the backup, they're all still intact on the server.

[shadmego]

Are you using the quotes properly?

[jpistone]

Yes, exactly as you have them up above. It's showing up right on time (every top of the hour), but it's just a 0 byte sql file. I'm baffled. I have no problems with my other CRONS, just this one (which is the first one I've tried to backup a database automatically). Thanks, again. One thing I should add, this is a CRON job running under an add-on domain. Would that have an impact somehow?

[shadmego]

Hang on. I am having problems getting SSH access to my account or I would be testing this out as well.

I will post the line that I use (inside a php file using exec() command) that works for me. Of course, I am also going the other way (importing) but I originally did this by exporting my databases.

I will try to post something back later tonight.

[shadmego]

==UPDATE==

alienspaces had the right idea using mysqldump. I tried with the mysql command and I just got a blank file.

Use the following command (worked for me) to dump your database:



mysqldump --user=user_name --password=password --host=localhost db_name > db.sql


A note on the password:

If you are using special characters in your password, you will want to quote them properly. --password='P@$$W0rD!' as an example.

[jpistone]

Tried:

mysqldump --user=myaccount --password=mypassword --host=localhost crontest > /home/myaccount/public_html/cronbackups/crontest_backup.sql

Once again it worked but 0 bytes. No matter what config or coding I use it's not going to dump for me. Thanks, anyway, Shadmego, I appreciated the time you spent trying to get it work.

[shadmego]

If that isn't working for you, I would probably contact support and as why. I can't think of a reason it wouldn't work.

The only other suggestions I have are to:

1. Try to create the file in the same directory as the script instead of using an absolute path. Just as a way to test.
2. Make sure the user into which you are logging into the database has full rights to that database. I assume it does, but just in case ....

Let us know what you find.

[jpistone]

Okay, shadmego... got it working after contacting HM. It looks like something was corrupt in the username/password. I deleted it and re-entered them and it's working fine now. Interesting that it would create and time stamp the file before finding itself denied access to the database itself. Thanks for your time and help!

[shadmego]

I am very glad to hear you got it working. So support said there was some corruption in the username/password entry for that database and they had you delete and recreate the user?

As far as the behavior you saw with the file being created, it has to do with the way the command was parsed. Explaining it goes a bit beyond the scope of this thread, but just know it's supposed to be that way. There are ways around it, but you would have to write a much more complex bash or php script that basically checks to make sure the database connection is valid before creating the file.