Migrate MySQL 4.0 to 5.1
Got a job to migrate a set of old mysql databases from MySQL server 4.0.15a-standard in Solaris x86 to MySQL server 5.1.41 on CentOS 5.6 x64. Due to the big difference in MySQL version , I can’t use the mysqldump bundled in MySQL 4.0.15 to export the database (or else mysql 5.1.41 will refuse to import because of syntax error) . So I do a remote full dump of all databases from the destination machine with MySQL 5.1.41 :
% mysqldump -h mysql4-server -u root -p –all-databases –single-transaction > dump.sql
As there are BLOB fields in certain databases , so the I have to increase the server’s max_allowed_packet (which is of default value 1MB) because I will surely exceed this value . And if this value is not increased , an error code below during the import of database (My table with blob field import on line 110 of dump.sql ) will be output :
ERROR 1153 (08S01) at line 110 : Got a packet bigger than ‘max_allowed_packet’ bytes
To increase the value for this parameter , edit the /etc/my.cnf for the mysql server :
[mysqld]
max_allowed_packet=64M
Restart the mysql server in the destination server to let this parameter effective . As for the mysql client , the default max_allowed_packet variable is 16MB. But I need a bigger value in my case , so need to set a larger value for it , like 64M for the import of database :
% mysql –max_allowed_packet=64 -u root -p < dump.sql
