[wellylug] Mysqldump problem

[Martin Wehipeihana]

Jo Booth wrote:

<chomp>

>>>
>>> #bin/bash
>>> rm /root/MySQL_BackUp/backup.sql
>>> mysqldump -p --password=********* -alldatabases >
>>> /root/MySQL_BackUp/backup.sql
>>>
.....

> 
> I'll probably just confuse the issue - and this box doesn't have the  
> man page -- but why are you mixing -'s and --'s in your arguments?   
> maybe it's expanding the -alldatabases to -a -l -l -d -a -t -a -b...  
> etc? I dunno.
> 
> -Jo.

Michael

why are you specifying the password option twice ?
-p
--password

You probably need to specify a user as well ( -u option ) when you 
execute mysqldump from the command line it will use your shell user as 
the mysql user.

If you execute this from a cron job it will try to use the background 
user ... whoever/whatever that is on your system

you might wanna try
--add-drop-table
Makes it easier to recreate/copy the database to somewhere else

--allow-keywords
You may need this option which quotes field names table names that may 
be reserved words. Whcih can be the case if you're accessing a third 
party database where they havent been to careful about observing naming 
standards. If you dont, then your restores will fail with reserved word 
errors

heres a chunk of my db backup script (edited)

for database in `ls /var/lib/mysql`; do
   if [ -d /var/lib/mysql/$database ]; then
     /usr/bin/mysqldump -uUSERIDHERE -pPASSWORDHERE --add-drop-table 
--allow-keywords $database > /BACKUPDIR/mysql/$database.sql
   fi
done

it works ... every night (and restores too)

Finally, there are some good examples on the mysql.com site .. have you 
tried any of those??

Martin