Migrating mysql.user table from MySQL 5.5 to MySQL 5.6 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 ?
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&quot;SELECT CONCAT('SHOW GRANTS FOR ',QUOTE(user),'@',QUOTE(host),';') FROM mysql.user WHERE user&lt;&gt;''&quot; | mysql -u... -p... --skip-column-names -AN | sed 's/$/;/g' &gt; 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 ...