1

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

3
  • How did you backup the data (did you use mysqldump, tar, binary copy) ??? How did you restore the data ??? Feb 26, 2013 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 ? Feb 26, 2013 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, 2013 at 15:16

2 Answers 2

1

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.

0

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

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