how to import mysql system "mysql" database [closed]

Question

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.

Closed 12 years ago.

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

• RolandoMySQLDBA: How did you backup the data (did you use mysqldump, tar, binary copy) ??? How did you restore the data ???
• RolandoMySQLDBA: 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 ?
• Petr: 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

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