I see one conflict that could be causing your "lost connection" message - you are specifying a 1 GB "max_allowed_packet" for the client, but the server is configured to only support 64 MB.
You should adjust the "max_allowed_packet = 64M" setting on the server to match or exceed what you specify on the mysql or mysqldump command line client, then try again. HTH, Dan On 9/24/07, Benjamin Schmidt <[EMAIL PROTECTED]> wrote: > > Unfortunately the additional parameters didn't solve my problem. But > thanks for your response! > > ssh [EMAIL PROTECTED] \ > "mysqldump -u XYZ --verbose --password=XYZ --quick > --single-transaction --net_buffer_length=1G --max_allowed_packet=1G > dbmail | /bin/gzip" \ > > /Backup/HD-Save/XYZ/data_mysql/XYZ_mysqldump.tar.gz_tmp > > > I don't thinks the problem and also following command didn't work > > mysqldump -h XYZ -u XYZ --verbose --password=XYZ --quick > --single-transaction --net_buffer_length=1G --max_allowed_packet=1G > dbmail | gzip > /Backup/HD-Save/XYZ/data_mysql/XYZ_mysqldump.tar.gz_tmp > > > Always get the result: > > mysqldump: Error 2013: Lost connection to MySQL server during query when > dumping table `dbmail_messageblks` at row: 177912 > > > ================================================================================ > Script ended at: Sat Sep 22 06:32:16 CEST 2007 (1190435536) > Execution Time: > Hours: 4 > Minutes: 269 > Seconds: 16155 > > OR > > mysqldump: Error 2013: Lost connection to MySQL server during query when > dumping table `dbmail_messageblks` at row: 189738 > > > ================================================================================ > Script ended at: Sun Sep 23 06:30:30 CEST 2007 (1190521830) > Execution Time: > Hours: 4 > Minutes: 267 > Seconds: 16048 > > OR > > mysqldump: Error 2013: Lost connection to MySQL server during query when > dumping table `dbmail_messageblks` at row: 137554 > > > ================================================================================ > Script ended at: Mon Sep 24 06:30:01 CEST 2007 (1190608201) > Execution Time: > Hours: 4 > Minutes: 267 > Seconds: 16020 > > > I know these two other solutions: > - Setting up a replication service > - Stopping mysql, copying db-files, and restart mysql > > Doing replication is not possible cause of the huge size of the > database. Hard-core copy of db-files causes a downtime of up to 8 hours > so it would be possible. > > Or does somebody has another (hope better) solution? > > With best regards, > Benjamin Schmidt > > > Hartleigh Burton wrote: > > Hiya, > > > > I was backing up a 95GB InnoDB database and forever had problems. It > ended up working and I never really worked out exactly what the cause was... > but try using the following: > > > > --opt (does --quick + extended-insert + others) > > --net_buffer_length=1G (set this to whatever you want, 1G is the largest > it will support. I was backing up uncompressed audio so had it at 1G. When > --opt is set it also uses --extended-insert, the net_buffer_length tells > mysqldump when to break the extended insert and create a new insert. Useful > when dealing with large packets) > > --max_allowed_packet=1G (or whatever you expect your largest packet to > be, in my case was up to 1G) > > > > Example: mysqldump -u mysqldump --password=XXXX --opt --verbose > --net_buffer_length=1G --max_allowed_packet=1G --single-transaction dbname > > dbname.sql > > > > If this still fails... try running the backup from a remote computer > either by using MySQL Administrator or mysqldump. Occasionally I would get > the same error you received when running mysqldump on localhost, however it > would complete when run from either my workstation or on another server. I > can't really explain why this would happen, but now I just run all of my > backups straight to a mirrored server. > > > > Example: mysqldump -h 192.168.x.x -u mysqldump --password=XXXX --opt > --verbose --net_buffer_length=1G --max_allowed_packet=1G > --single-transaction dbname > dbname.sql > > > > Good luck, hope this helps. > > > > > > Hartz. > > > > -----Original Message----- > > From: Benjamin Schmidt [mailto:[EMAIL PROTECTED] > > Sent: Tuesday, 4 September 2007 7:05 PM > > To: mysql@lists.mysql.com > > Subject: mysqldump of huge innodb database > > > > Hello list members > > > > Since a few days I get this error message when making a backup of my > > database: > > > > > > mysqldump: Error 2013: Lost connection to MySQL server during query when > > dumping table `dbmail_messageblks` at row: 174955 > > > > > ================================================================================ > > Script ended at: Tue Sep 4 06:45:37 CEST 2007 (1188881137) > > Execution Time: > > Hours: 4 > > Minutes: 282 > > Seconds: 16956 > > > > > > The ibdata1 file now has a size of 42GB (I use the innodb engine). The > > command to backup is following: > > > > > > ssh [EMAIL PROTECTED] \ > > "mysqldump -u mysqldump --password=XXXX --quick > > --single-transaction dbmail | /bin/gzip" > /Backup/mysqldump.tar.gz > > > > > > And this is my config-file (default values from the debian package): > > > > > > ... > > innodb_data_file_path=ibdata1:10M:autoextend:max:183G > > key_buffer = 16MB > > max_allowed_packet = 64M > > thread_stack = 128K > > query_cache_limit = 1048576 > > query_cache_size = 16777216 > > query_cache_type = 1 > > set-variable = max_connections=1000 > > max_allowed_packet = 64M > > ... > > > > > > As I wrote above, it worked this way a very long time. And it should > > work again ;) > > > > Does anyone know this problem or has an idea? > > Many thanks in advance, > > Benjamin Schmidt > > > > > > > >
