When migrating a MySQL database from Server(A) to Server(B) which tables should be dumped and migrated to Server(B), including user grants? I don't want to have to manually re-enter user accounts and passwords. I wish to do this using a third part application like Navicat, without using mysqldump. My understanding is following tables contain user account (grant) data:

mysql > db
mysql > user

However, I've read that the mysql table does not need to be migrated from Server(A) to Server(B) as well as the information_scheme table. But, if the mysql table does not need to be migrated, unless that comment is incorrect, how do I migrate my user grant info with hosts, usernames, passwords, etc.

# adapted from (http://www.pyrosoft.co.uk/blog/2006/10/18/show-grants-for-all-users-on-mysql/)
 ( mysql -pPASSWORD --batch --skip-column-names -e "SELECT user, host FROM user" mysql 
 ) | while read user host
        echo "# $user @ $host"
        mysql -pPASSWORD --batch --skip-column-names -e"SHOW GRANTS FOR '$user'@'$host'"

This script will "dump" all of the users permissions and their hashed passwords. I've had mixed results from importing dumps of the mysql and information_schema databases.

| improve this answer | |
  • How would you tweak your script to target a specific db, among several other db's, that all share one mysql instance? – Steve Jun 6 '11 at 16:28
  • Are you asking how to export the users that have permissions on one specific database? – racyclist Jun 7 '11 at 14:01
  • Yes. But, how would I point your script at a specific database. – Steve Jun 7 '11 at 15:09
  • You can change the first query "mysql -pPASSWORD --batch --skip-column-names -e "SELECT user, host FROM user" mysql" to "mysql -pPASSWORD --batch --skip-column-names -e "select user,host from db where Db='DATABASE_NAME'" mysql" That will limit it to a specific database. It will only dump users who have been specifically granted permissions on that database and not users with global privileges who may also have permissions on that database. – racyclist Jun 7 '11 at 19:54

I would just do a mysqldump of the database(s) and then manually mirror the permissions for the database users. It'll be more direct and easier to do than trying to figure out a way to export (assuming it's feasible & reliable) user access and passwords.

| improve this answer | |

You need to migrate databases, not just tables. The information you have received is somewhat incorrect as the mysql database is the one you do need to migrate. That's the one that contains all your security information and will eliminate the need to manually reconstruct that data. However, the information_schema is a virtual database, which is created each time MySQL is started, so it's pointless bothering with that.

I suggest you use mysqldump rather than Navicat, which is unreliable and can produce some really strange and incomplete results at times, whereas mysqldump is designed specifically for the job.

| improve this answer | |

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.