This sounds like you need to raise max_allowed_packet for mysqldump (and possibly mysqld) - these are separate settings for both the client and the server. You can do this via the my.cnf (or ~/.my.cnf) or specify it as an option on the command line "mysqldump --opt ... --max_allowed_packet=1G dbname > backup-file".
On Tue, Jan 13, 2009 at 2:58 PM, Dan <d...@entropy.homelinux.org> wrote: > On Tue, 2009-01-13 at 12:19 +0530, Chandru wrote: > >> Hi, >> >> Did u try using this command >> >> >> mysqldump --opt db_name > db_name.sql -p 2>>bkp.err > > Not quite. Firstly, I had to alter the normal backup cron job, and that > doesn't happen until late at night. > > Secondly, yes I added the redirection to capture errors. There were none > ( empty file this time ). > > Thirdly, I didn't use '--opt'. I had no other suggestions yesterday > ( before I went to bed anyway - there's 1 in my inbox this morning ), so > I did some experimenting of my own and changed the dump command to: > > mysqldump --skip-opt --add-drop-table --add-locks --create-options > --quick --lock-tables --set-charset --disable-keys dbmail > dbmail.sql > -pSOME_PASSWORD 2>>bkp.err > > This made mysql do 1 insert per record. > > The backup *appears* to have completed successfully. At least the end of > the dump file looks valid. It ends dumping the last table, then a view, > then I get: > > -- Dump completed on 2009-01-13 17:23:13 > > Previously it just finished part-way through dumping a blob. > > I have yet to do extensive testing on it. I suppose I should try > importing the dump file into another server and see if I get the correct > number of rows in each table ... > > The only issue now is that the dump file is much smaller than I would > have expected. When using --opt, I was getting 30GB dump files. I would > have expected the current format ( 1 insert statement per record ) to be > much bigger, but it's 23GB. Now having said that, I did email the > current DB administrator and ask him to get people to archive all emails > with huge attachments somewhere on a network share ( people have some > pretty big attachments ). Also I asked him to get people to clean out > their Trash ( which happens only when we tell them to ). So I suppose > it's not completely infeasible that this alone is responsible for the > difference. > > Anyway, it's been a very disconcerting experience. It goes without > saying that people would expect that anything that gets into a MySQL > database should be able to be backed up by mysqldump. And it's worrying > that the default --opt can't do that. When I get some time I'll enter a > bug ... > > Thanks for you help Chandru. > > Dan > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=andrew.b.gar...@gmail.com > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
