MySQL database restoration error

So I was trying to restore a backup database from a .sql file through the following command:

mysql database < file.sql

And an error resulted:

Got a packet bigger than ‘max_allowed_packet’ bytes

This is most likely due to the fact that there are large images and other files in the database causing problems. I had to edit the MySQL configuration files and added the following:

vim /etc/my.cnf

set-variable=max_allowed_packet=xM

For my particular issue, I had to change x to 512. This may vary depending on how large your files are.

2010
05/06
POSTED BY
CATEGORY
Work
TAGS
1 comment
    • Stan
    • January 5th, 2011 5:24pm

    For compressed SQL files, use the following command:

    zcat DB_File_Name.sql.gz | mysql -u username -p Target_DB_Name

  1. No trackbacks yet.