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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.485 / Virus Database: 269.13.3/986 - Release Date: 3/09/2007 9:31 AM No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.485 / Virus Database: 269.13.3/986 - Release Date: 3/09/2007 9:31 AM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
