mysqldump

[jc94062]

I've looked around (will be using the code in a cron job eventually) and found this

mysqldump [OPTIONS] database [tables]

Well, I want all the tables.

mysqldump [--password=password --all] dbname > dbbackup.sql

It gives an error,
mysqldump: unrecognised option '--all]'

I can create the file on my server by just doing

mysqldump --password=password > dbbackup.sql

but it just gives a nice little file on mysqldump, maybe useful (I was reading it from a webpage before) but not what I'm looking for!

Thanks for any help

[Anon]

try doing
mysqldump --add-drop-table -c -u your-user-name -p database-name > your_file.sql

this will add a drop table before each create, which will let you go to a backup easily. the -c uses complete inserts, it specifies the column names in the insert statement.

it will prompt you for a password, so if you can't do this interactively, you can use
-pyour-password

just replace your-password w/ the actual pass for that user..

p.

[jc94062]

close, but...

mysqldump --add-drop-table -c -u username -p dbname > test.sql

mysqdump: Got error: 1045: Access denied for user: 'username@localhost' (Using password: YES)

Any ideas on this one? I've tried all four passwords (I have different ones for ftp, shell, db, and cp access) but I get the same error each time, incidently, if I don't put in the db name it will create that mysqldump help doc thingy, which hasn't been really helpful as of yet, doesn't ask for a pw either.

Thanks again!

[Anon]

what username/password do you use with the mysql command line interpreter? is this the same thing you are trying?

maybe try it as root user as well, just to make sure it works, then grant permissions on the db to other users.

I've never had a problem, so I don't know what else to tell you.

[jc94062]

adding -h host helps :-)
Contacted the hosting company in the end.

Thanks for the help though.

[jc94062]

mysqldump --add-drop-table -h host -c -f -u username -ppassword dbname | /usr/bin/sendmail [e-mail]

That works, it sends a mail with all the db information in it.

However, I do into the editor with crontab -e

and put...

30 18 * mysqldump --add-drop-table -h host -c -f -u username -ppassword dbname | /usr/bin/sendmail [e-mail]

and I get a mail at 18:30 (testing, I'll change this to 00 04 in the end of course) but it doesn't have anything in it!

I then tried to echo "" the mysqldump stuff, which send me a mail saying mysqldump --add-drop-table ... so that didn't work!

Any help much appreciated.

[jc94062]

Sorted after many hours.
For cronjob the full path to mysql had to be given.

[Anon]

I have tried the following but keep getting empty emails!

$command = "mysqldump --add-drop-table -h localhost -c -f -u myusername -p mypass mydbname | /usr/sbin/sendmail [e-mail]";
$result = exec($command, $errorcode);

Any help appreciated
Mike.