0

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 ?

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

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

1

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy