List:General Discussion« Previous MessageNext Message »
From:Dan Buettner Date:September 24 2007 1:23pm
Subject:Re: mysqldump of huge innodb database
View as plain text  
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 <b.schmidt@stripped> wrote:
>
> 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
> >
> >
> >
>
>

Thread
mysqldump of huge innodb databaseBenjamin Schmidt4 Sep
  • RE: mysqldump of huge innodb databaseHartleigh Burton5 Sep
    • Re: mysqldump of huge innodb databaseBenjamin Schmidt24 Sep
      • Re: mysqldump of huge innodb databaseDan Buettner24 Sep