1

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 ?
0

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:

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 ...

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.