SEO

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:1 83G
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

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@t-p.com]
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 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:1 83G
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=h...ertainment.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

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@t-p.com> 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@t-p.com]
> > 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 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:1 83G
> > 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
> >
> >
> >
>
>