mysql5.6.50物理迁移后root登录不显示mysql库和业务库

问题描述

mysql5.6.50通过拷贝数据目录的方式实现物理迁移，启动目标库root登录查询数据库不显示mysql库和其他业务库。

--登录
[root@kaiping01 data]#mysql
或
[root@kaiping01 data]#mysql -uroot -p

--查看数据库,发现mysql和其他业务库不显示
root@localhost :(none) 17:54:16>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.01 sec)

root@localhost :(none) 18:13:34>select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

--查看权限
root@localhost :(none) 17:54:20>show grants;
+--------------------------------------+
| Grants for @localhost                |
+--------------------------------------+
| GRANT USAGE ON *.* TO ''@'localhost' |
+--------------------------------------+
1 row in set (0.00 sec)

root@localhost :(none) 17:54:33>exit

--切换user库报错
root@localhost :(none) 17:59:22>use mysql
ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql'
root@localhost :(none) 18:03:12>select user,host from mysql.user;
ERROR 1142 (42000): SELECT command denied to user ''@'localhost' for table 'user'

问题分析

--采用host 127.0.0.1登录
[root@kaiping01 log]# mysql -uroot -h 127.0.0.1

--查看权限
root@127.0.0.1 :(none) 18:01:11>show grants;
+---------------------------------------------------------------------+
| Grants for root@127.0.0.1                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' WITH GRANT OPTION |
+---------------------------------------------------------------------+

--查看user表
root@127.0.0.1 :(none) 18:06:18>select user,host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | %         |
| root | 127.0.0.1 |
| root | ::1       |
|      | kaiping02 |
| root | kaiping02 |
|      | localhost |
+------+-----------+

尝试的解决办法如下，均失败收尾：

--采用host 127.0.0.1登录
[root@kaiping01 log]# mysql -uroot -h 127.0.0.1

--查看主机名
[root@kaiping01 data]# hostname
kaiping01

--更改user表中host和操作系统的主机名一致
select user,host from mysql.user where host='kaiping02';
update mysql.user set host='kaiping01' where user in ('','root') and host='kaiping02';
FLUSH PRIVILEGES;
未解决问题

--创建root@localhost用户
root@127.0.0.1 :mysql 18:28:01>create user 'root'@'localhost' identified  by 'root';
ERROR 1396 (HY000): Operation CREATE USER failed for 'root'@'localhost'
创建失败未解决问题

问题解决

--采用host 127.0.0.1登录
[root@kaiping01 log]# mysql -uroot -h 127.0.0.1

--更新user表，将''@'localhost'更改为'root'@'localhost'
select user,host from mysql.user where user='' and host='localhost';
update mysql.user set user='root' where user='' and host='localhost';
FLUSH PRIVILEGES;

--赋予权限
grant all privileges on *.* to 'root'@'localhost' with grant option;
FLUSH PRIVILEGES;

--采用mysql或mysql -uroot -p方式登录，查询数据库
[root@kaiping01 log]# mysql
或
[root@kaiping01 log]# mysql -uroot -p

--查询数据库
root@localhost :(none) 18:35:22>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| xzspkp             |
+--------------------+