全新安装 MariaDB 10.4 (fedora 31)。我以 root 身份登录 mysql：
MariaDB [mysql]> select User, Host,Password from user;
+-------+-----------+-------------------------------------------+
| User | Host | Password |
+-------+-----------+-------------------------------------------+
| root | localhost | *A4B6157319038724E3560894F7F932C8886EBFCF |
| mysql | localhost | invalid |
+-------+-----------+-------------------------------------------+
2 rows in set (0.001 sec)
MariaDB [mysql]
如果我尝试创建一个新用户：
MariaDB [mysql]> create user 'developer'@'localhost' identified by 'pippo';
ERROR 1396 (HY000): Operation CREATE USER failed for 'developer'@'localhost'
从第一张表可以看出，该用户developer不存在。root 的权限似乎没有问题：
MariaDB [(none)]> SHOW GRANTs;
+-------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+-------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED VIA mysql_native_password OR unix_socket WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION |
+-------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)
答案1
找到问题了。在创建用户之前，我从转储中创建了一个数据库。我检查了 mysql.db 表，发现用户“developer”在该表中，但不在 mysql.user 中。
MariaDB [mysql]> select * from db;
+------------+----------+-----------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| Host | Db | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv | Event_priv | Trigger_priv |
+------------+----------+-----------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| % | mydb | developer | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
| 10.8.148.% | postal | developer | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
| 10.8.148.% | mydb | developer | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
| miyazaki | mydb | developer | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
| % | helpdesk | develop | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
| % | helpdesk | developer | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
+------------+----------+-----------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
这就是问题所在。我毁掉了令人不安的数据库
MariaDB [mysql]> create user 'developer'@'localhost' identified by 'pippo';
Query OK, 0 rows affected (0.002 sec)
因此，解决方案是：
1-在这些表中检查有罪的用户；
columns_priv, db, procs_priv, tables_priv
2 – 转储时，不要导出不相关的表。