Re: mysqldump of huge innodb database

[Benjamin Schmidt]

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




Always get the result:



================================================================================
Script ended at: Sat Sep 22 06:32:16 CEST 2007 (1190435536)
Execution Time:
 Hours:   4
 Minutes: 269
 Seconds: 16155

OR



================================================================================
Script ended at: Sun Sep 23 06:30:30 CEST 2007 (1190521830)
Execution Time:
 Hours:   4
 Minutes: 267
 Seconds: 16048

OR



================================================================================
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



Or does somebody has another (hope better) solution?

With best regards,
Benjamin Schmidt


Hartleigh Burton wrote:

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 mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `dbmail_messageblks` at row: 177912 mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `dbmail_messageblks` at row: 189738 mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `dbmail_messageblks` at row: 137554 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.

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






================================================================================
Script ended at: Tue Sep  4 06:45:37 CEST 2007 (1188881137)
Execution Time:
  Hours:   4
  Minutes: 282
  Seconds: 16956





ssh [EMAIL PROTECTED]  \



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
...




Does anyone know this problem or has an idea?
Many thanks in advance,
Benjamin Schmidt

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 The ibdata1 file now has a size of 42GB (I use the innodb engine). The command to backup is following: "mysqldump -u mysqldump --password=XXXX --quick --single-transaction dbmail | /bin/gzip" > /Backup/mysqldump.tar.gz As I wrote above, it worked this way a very long time. And it should work again ;)

smime.p7s
Description: S/MIME Cryptographic Signature