Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This question might be already answered but it's almost impossible to google it.

When I perform full backup of mysql server using

mysqldump --all-databases

then reinstall it from scratch I want to import FULL dump of all databases, including "mysql".

I successfully done that by executing

mysql -u -p < dumpfile

but now even if all user records are in mysql.user these accounts are not working.

I can't even change their password, I always get

ERROR 1133 (42000): Can't find any matching row in the user table

Restarting mysqld is of no help

answer:

It was fixed by executing

MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.26 sec)

now all users work as before

share|improve this question

closed as too localized by Mark Storey-Smith, Mat, Max Vernon, dezso, StanleyJohns Apr 29 '13 at 17:40

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

    
How did you backup the data (did you use mysqldump, tar, binary copy) ??? How did you restore the data ??? –  RolandoMySQLDBA Feb 26 '13 at 15:13
    
Did you restore to a different server running a different mysql version ? Did you change any config option in my.cnf on the restore machine that is different from the backup source ? –  RolandoMySQLDBA Feb 26 '13 at 15:15
    
it is same machine and same configuration file, I used mysqldump to create a backup of databases and mysql command to import it. When I do select * from mysql.user I see all the users but they do not work –  Petr Feb 26 '13 at 15:16
add comment

2 Answers 2

mysqldump actually has an option to run FLUSH PRIVILEGES; after the dump is completed

[root@**** ~]# mysqldump --help | grep privileges
  --flush-privileges  Emit a FLUSH PRIVILEGES statement after dumping the mysql
flush-privileges                  FALSE

In light of this you would run

mysqldump --all-databases --flush-privileges > dumpfile

Running mysqldump against a MySQL Instance and reloading back into the same MySQL Instance should not annihilate user privileges requiring you to run FLUSH PRIVILEGES; by hand. This may be a problem with MariaDB. You should test this on a Dev Server. You should also see the open bug list of MariaDB to see if this is already a known issue.

I have recommended using --flush-privileges in my past posts

CAVEAT

Please keep in mind that grants are stored in RAM. Running FLUSH PRIVILEGES; simply reads mysql.user and reloads the grants into RAM. The error message you mentioned earlier

ERROR 1133 (42000): Can't find any matching row in the user table

may be not referring to the physical mysql.user table, but the in-memory copy of it. Again, I emphasize that MariaDB may be mishandling user grants upon reload.

What worries me further is the fact that you restarted mysql (MariaDB) and the grants still did not work? At the risk of sounding redundant, this may be a problem with MariaDB. You should test this on a Dev Server. You should also see the open bug list of MariaDB to see if this is already a known issue.

share|improve this answer
add comment

As per Petr updated Question

It was fixed by executing

MariaDB [mysql]> flush privileges;

,

Query OK, 0 rows affected (0.26 sec)

now all users work as before

share|improve this answer
add comment

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