Migrating mysql.user table from MySQL 5.5 to MySQL 5.6

Question

I have a tricky question.

• I am migrating from MySQL 5.5 to MySQL 5.6.
• I want to migrate 45 users in MySQL 5.5 to MySQL 5.6

Questions

• Should I just take a dump of the MySQL 5.5 user table and restore on MySQL 5.6 ?
• MySQL 5.6 user table has more columns then MySQL 5.5 so was wondering if that was doable.
• Any other advise ?

Answer 1

You could load the mysql schema into the 5.6 instance and run mysql_upgrade. I have a much cleaner method in mind: You could dump the grants as pure SQL from the MySQL 5.5. instance and then run it in the MySQL 5.6 instance.

You can do one of the following:

• Extract the grants as SQL using pt-show-grants.
• Run my personal emulation of pt-show-grants:

mysql -u... -p... --AN -e"SELECT CONCAT('SHOW GRANTS FOR ',QUOTE(user),'@',QUOTE(host),';') FROM mysql.user WHERE user<>''" | mysql -u... -p... --skip-column-names -AN | sed 's/$/;/g' > MySQLUserGrants.sql

Once you create the script from the 5.5. instance, run the script in the 5.6 instance.

GIVE IT A TRY !!!

Note : I have a post where I helped someone manually fix mysql.user (MySQL service stops after trying to grant privileges to a user). Try this only when everything else fails ...