Unfortunately the additional parameters didn't solve my problem. But
thanks for your response!
ssh root@XYZ \
"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:b.schmidt@stripped]
> Sent: Tuesday, 4 September 2007 7:05 PM
> To: mysql@stripped
> 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 root@XXXX \
> "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
>
>
>
Attachment: [application/x-pkcs7-signature] S/MIME Cryptographic Signature smime.p7s
