how to import mysql system "mysql" database

Question

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

Answer 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

• Feb 03, 2012 : How to resolve the master server shut down/unavailability in mysql with master - slave replication
• Jan 19, 2012 : How do I replicate Mysql DB server to new server?
• Dec 05, 2011 : Set up a MySQL slave using mysqldump to get the initial data
• Jun 26, 2011 : How to enable all the possible backup options of MySQL

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.

Answer 2

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