Hi Everyone,
I have had similar problems in the past, and have managed to usually
stumble my way around them. But now this officially is just not
making any sense... to me at least ;)
Problem: I want to backup a database from server1 and restore it to
server2. The database size is ~10GB, largest row at any time is ~100M.
Here is what I have been doing...
Creating the SQL backup
# mysqldump --host=192.168.1.61 --user=hburton -p --opt --
single_transaction --verbose --max_allowed_packet=100M --
net_buffer_length=100M mraentertainment > mraentertainment.sql
From my understanding, net_buffer_length should tell mysqldump the
maximum size that extended-insert rows are allowed to be before
beginning a new INSERT row set. I have however tried --skip-extended-
insert & --extended-insert=FALSE (believing that the
max_allowed_packet that is erring is actually multiple rows in a
single INSERT)
The backup completes successfully and I have myself a 9.2GB SQL dump
file ready to go.
Reloading the SQL backup
# mysql --host=localhost --user=hburton -p --max_allowed_packet=100M
mraentertainment < mraentertainment.sql
Error: when trying to load the SQL file into the empty database on
the new server I receive the message "ERROR 1153 (08S01) at line 92:
Got a packet bigger than 'max_allowed_packet' bytes". I see so many
people on forums everywhere that get the same issue... but can not
seem to find an answer for this.
As you can see, max_allowed_packet for mysqldump (backup) and mysql
(restore) are the same. I have also set max_allowed_packet=1G in the
config files for MySQL on both servers. I have tried using values up
to 1G, as this is apparently the largest value supported according to
the documentation.
If anyone can help me out with this the assistance is greatly
appreciated.
Regards,
Hartleigh Burton
Resident Geek.
