MySQL upgrade from 5.0 to 5.5

Question

I would like to upgrade MySQL from version 5.0 (i have 5.086) to 5.5 (let's say 5.5.17) After some search, i found this method :

1. Dump the databases but not mysql databse in a backup_data.sql
2. This is the most important step: dump mysql database with the option --complete-insert and --no-create-info in a file (lets say mysql.sql) : the idea is to obtain an sql database as similar as if setup a 5.5 version from scratch and create users after.
3. Uninstall 5.0 - install 5.5
4. Recover mysql database and flush the privilege to restore it.
5. Recover the other databases.

My problem happen in step 4 : when i do :

mysql -u root mysql < mysql.sql

I got error about duplicate entry for key primary. When i dump mysql database without the options --complete-insert and --no-create-info, the recover works fine BUT i obtain an sql database different from if i created it with a new 5.5 setup and that's not i want.

Any idea about the problem or any suggestions.

Answer 1

Backup the mysql.* database. Re-export your mysql database with the --replace flag and re-import. If that doesn't have the intended effect, check out the scripts on this post for dumping grants.