3

I have a Percona MySQL database 5.6 running in a linux machine. I have created new users called test1 and test2. What I did was I imported mysql database from my test setup to this new setup. After which when I run the query

SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user;

I'm not able to see the new users which I created before the import was done but I'm able to connect to MySQL with the users I created before import.

Even after import my new users exist then it has to show in the mysql database right? What is wrong?

Thanks!!

5
  • Flush privileges would sync mysql users tables but then you would not be able to connect with those which are not present in mysql db and need to re-create them and assign them privileges. If you want to have the missing ones may have to restore mysql db on another db and get missing users from there... Aug 29, 2015 at 13:10
  • No i dont get it. Im able to login with the users which i created before the import. But im not able to see the users from the query after the import.
    – tesla747
    Aug 29, 2015 at 14:21
  • that may be because you did import and haven't run flush privileges yet because of which you may be seeing in memory privileges for previous users which are not there in mysql db. Aug 29, 2015 at 14:38
  • I did run flush privileges but still im unable to see the new users which I created. It is only showing the users belonging to the dump with which it was imported to mysql db.
    – tesla747
    Aug 29, 2015 at 16:10
  • The actual problem is mysql maintains its users in mysql db in user and db table collectively, you have imported the mysql db from another system and seeing the users from the backed up mysql db. do you have the old mysql db backup? if so would let you know how to merge both of them. Aug 29, 2015 at 16:54

2 Answers 2

0

Best is to to : show grants for user@'10.%' ; each user on the original server. Then apply them to the new.

Or you can use pt-show-grants -hhostname -u -p which will list all the grants. You can do that in one line of code:

pt-show-grants -horigalHost -u -p | mysql -hnewHost -u -p
0

Your mysqldump exported mysql.user table, probably because you used --all-databases option (or explicitly selected only mysql schema for export). After the import, your old mysql.user table was replaced with this new one, but MySQL was still keeping the previous table content in memory and you were able to authenticate with those old users. If you executed FLUSH PRIVILEGES, or re-started the server, MySQL would have re-read mysql.user table after which you wouldn't have be able to authenticate with old users.

To prevent this disparity (and the need to manually flush privileges), you can add --flush-privileges whenever you use --all-databases mysqldump option or dump mysql database (schema). This appends FLUSH PRIVILEGES; to the dump file making sure that grant table is re-read at the end of the dump import.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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