From: Hartleigh Burton
Date: September 4 2007 11:05pm
Subject: RE: mysqldump of huge innodb database
List-Archive: http://lists.mysql.com/mysql/208910
Message-Id: <000901c7ef48$1e6ef360$5b4cda20$@com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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=3D1G (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=3D1G (or whatever you expect your largest packet to =
be, in my case was up to 1G)

Example: mysqldump -u mysqldump --password=3DXXXX --opt --verbose =
--net_buffer_length=3D1G --max_allowed_packet=3D1G --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=3DXXXX --opt =
--verbose --net_buffer_length=3D1G --max_allowed_packet=3D1G =
--single-transaction dbname > dbname.sql

Good luck, hope this helps.


Hartz.

-----Original Message-----
From: Benjamin Schmidt [mailto:b.schmidt@stripped]=20
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=20
database:


mysqldump: Error 2013: Lost connection to MySQL server during query when =

dumping table `dbmail_messageblks` at row: 174955

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D
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=20
command to backup is following:


ssh root@XXXX  \
         "mysqldump -u mysqldump --password=3DXXXX --quick=20
--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=3Dibdata1:10M:autoextend:max:183G
key_buffer =3D 16MB
max_allowed_packet =3D 64M
thread_stack =3D 128K
query_cache_limit =3D 1048576
query_cache_size =3D 16777216
query_cache_type =3D 1
set-variable =3D max_connections=3D1000
max_allowed_packet =3D 64M
...


As I wrote above, it worked this way a very long time. And it should=20
work again ;)

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



--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    =
http://lists.mysql.com/mysql?unsub=3Dhburton@stripped


No virus found in this incoming message.
Checked by AVG Free Edition.=20
Version: 7.5.485 / Virus Database: 269.13.3/986 - Release Date: =
3/09/2007 9:31 AM
=20

No virus found in this outgoing message.
Checked by AVG Free Edition.=20
Version: 7.5.485 / Virus Database: 269.13.3/986 - Release Date: =
3/09/2007 9:31 AM
=20