2

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

  • 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... – Nawaz Sohail Aug 29 '15 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 '15 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. – Nawaz Sohail Aug 29 '15 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 '15 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. – Nawaz Sohail Aug 29 '15 at 16:54
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
|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.