Backuping up mysql 4.018 database and restoring to Mysql 5.0 problems

[dominick32]

OK, I have a very big vbulletin database backup that I am using the command mysqldump to create a backup file.

I purchased a new dedicated server but my host has all new software including a newer version of Mysql 5.0.

When I goto use the mysql command to restore the database it gets about half way done than produces an error saying "at line 2016 there is an invalid UTF-8 character string. After this happens, it will not restore and crashes.

Anyhelp will be greatly appreciated.

[Wayne Luke]

4.0.18 does not support character sets and collations. I suggest making the backup with the --opt and --compatible=mysql40 flags. Once this is done, then you can attempt the restoration again.

[Hossie]

As recommended by gentoo:

mysqldump \
-uroot \
--password='your_password' \
-hlocalhost \
--all-databases \
--opt \
--allow-keywords \
--flush-logs \
--hex-blob \
--master-data \
--max_allowed_packet=16M \
--quote-names \
--result-file=BACKUP_MYSQL_4.0.SQL

then:

# cat BACKUP_MYSQL_4.0.SQL \
| mysql \
-uroot \
--password='your_password' \
-hlocalhost \
--max_allowed_packet=16M

# mysql_fix_privilege_tables \
--defaults-file=/etc/mysql/my.cnf \
--user=root \
--password='your_password'

http://www.gentoo.org/doc/en/mysql-upgrading.xml

[dominick32]

4.0.18 does not support character sets and collations. I suggest making the backup with the --opt and --compatible=mysql40 flags. Once this is done, then you can attempt the restoration again.

Would you be able to do me a big favor and give me the exact command line code I would need to use in FreeBSD? Thanks a million:

Would it be:

mysqldump --opt --compatible=mysql40 -u admin -p vbulletin <backup.sql

[Steve Machol]

That should work, but put a space between < and backup.sql.

[Steve Machol]

Oops, maybe not. Since you are going from MySQL4 to MySQL5, that is not applicable. That would only be used going the other way. I'm afraid I do not know what to do in this case. I'll ask the other staff.

[Jerry]

Would it be:

mysqldump --opt --compatible=mysql40 -u admin -pvbulletin > backup.sql

No space between the -p and password and the > goes into the file you want, so points the other way as your dumping a database to that file, opposed to restoring one from a file, then it goes the other way <.

[Jake Bunce]

I recently migrated from MySQL 4 to MySQL 5. I didn't have any problems and didn't have to use any special parameters in the mysqldump command:

mysqldump --opt -uusername -ppassword dbname > dump.sql

...and to restore:

mysql -uusername -ppassword dbname < dump.sql