[wellylug] Mysqldump problem

[Michael Dittmer]

Thanks all for your ideas.

I now have it fixed and it works from the script.

Thanks

Michael

-----Original Message-----
From: wellylug-admin at lists.wellylug.org.nz
[mailto:wellylug-admin at lists.wellylug.org.nz] On Behalf Of Martin
Wehipeihana
Sent: Monday, 12 September 2005 1:13 p.m.
To: wellylug at lists.wellylug.org.nz
Subject: Re: [wellylug] Mysqldump problem

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


--
Wellington Linux Users Group Mailing List:
wellylug at lists.wellylug.org.nz To Leave:
http://lists.wellylug.org.nz/mailman/listinfo/wellylug