I would like to upgrade MySQL from version 5.0 (I have 5.0.86) 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 this problem or any suggestions ?


2 Answers 2


You cannot mysqldump the mysql schema and simply import to the higher version. Why ? In the DBA StackExchange, I answered this question a little over a year ago: Cannot GRANT privileges as root. In short, the table mysql.user has a different number of columns for each major release of MySQL:

What you can do is dump mysql.user using one of two techniques:


Use one of the following Percona Tools

Echo the output of either program to a text file.


You can emulate pt-show-grants as follows:

MYSQL_CONN="-uroot -ppassword"
SQLSTMT="${SQLSTMT} QUOTE(user),'@',QUOTE(host),';') "
SQLSTMT="${SQLSTMT} FROM mysql.user WHERE user<>''"
mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" > GetGrants.sql
echo "SET sql_log_bin = 0;" > MySQLUserGrants.sql
mysql ${MYSQL_CONN} -AN < GetGrants.sql | sed 's/$/;/g' >> MySQLUserGrants.sql
rm -f GetGrants.sql

That is the cleanest and safest way to migrate grants from version to version. I have successfully upgraded MySQL 5.0 to MySQL 5.1/5.5 many times. You cannot go MySQL 5.0 to MySQL 5.6 because IMHO the 16-character PASSWORD() function hash is not available via old_password. If you have the plain text passwords for all users, simply edit MySQLUserGrants.sql replacing IDENTIFIED BY PASSWORD '16-char hash' with IDENTIFIED BY 'plaintext password'. Then you can execute it in MySQL 5.6. When done, quickly delete MySQLUserGrants.sql.


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.

You must log in to answer this question.