Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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 ?

share|improve this question

migrated from stackoverflow.com Dec 7 '11 at 4:19

This question came from our site for professional and enthusiast programmers.

1  
The official reference you needed here: dev.mysql.com/doc/refman/5.5/en/upgrading.html –  Jerry Tian Dec 7 '11 at 3:37

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:

TECHNIQUE #1

Use one of the following Percona Tools

Echo the output of either program to a text file.

TECHNIQUE #2

You can emulate pt-show-grants as follows:

cd
MYSQL_CONN="-uroot -ppassword"
SQLSTMT="SELECT CONCAT('SHOW GRANTS FOR ',"
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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.