From: Hartleigh Burton (hburtonmraentertainment.com)
Date: Tue Sep 04 2007 - 18:05:39 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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:b.schmidtt-p.com]
Sent: Tuesday, 4 September 2007 7:05 PM
To: mysqllists.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 rootXXXX \
         "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/mysql?unsub=hburtonmraentertainment.com

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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]