迁移还原Mysql问题

1、出现无法登陆，这里需要按照忘记密码的方式处理，更改密码就可以。

[root@sh-web ~]# mysql -u root -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

2、创建用户失败，应为迁移过程中升级了mysql版本，这里需要升级数据库的用户表。

mysql> create user name;
ERROR 1805 (HY000): Column count of mysql.user is wrong. Expected 45, found 42. The table is probably corrupted

升级用户表

[root@sh-web ~]# mysql_upgrade --force -uroot -p
Enter password: 
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
..............                                     OK
..............                                     OK
..............                                     OK
此处省略一万行..
..............                                     OK
sys.sys_config                                     OK
Upgrade process completed successfully.
Checking if update is needed.

# 随后再创建用户就成功了
mysql> create user name;
Query OK, 0 rows affected (0.00 sec)

创建一个远程登陆的数据库用户

mysql> create user name;
mysql> GRANT ALL PRIVILEGES ON *.* TO USERNAME@"%" IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;
mysql> flush privileges;

查询数据库中的用户

SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;