环境：MySQL 8.0.20安装在Ubuntu20.04虚拟机上

这篇博客记录着我从初学MySQL开始遇到的所有错误和解决方案 安装命令： sudo apt-get install mysql-server sudo apt-get install mysql-client

1. ERROR 1698 (28000)

1.1 问题：

进入mysql时需要我们使用sudo mysql -uroot -p******才能登陆成功，否则提示ERROR 1896。发现这个的原因其实是root用户的权限问题。

1.2 解决方法：

1.2.1 先使用 你的原始密码（首次安装为空）登陆

sudo mysql -uroot -p

1.2.2 在输入以下命令查看你的root账号是如何登陆

select User,Host,plugin from mysql.user;

1.2.3 修改root用户的登录方式

如果步骤2中的plugin是auth_socket那就说明你这个root的登陆方式不是普通登陆，需要加sudo。将其修改为mysql_native_password。

update mysql.user set plugin = 'mysql_native_password';

1.2.4 刷新修改

flush privileges;

这样如果成功了 你就可以退出去重新登陆，就不需要加sudo了。

2. ERROR 1396 (HY000)

修改密码时报错：“ERROR 1396 (HY000): Operation ALTER USER failed for ‘root’@’%’”解决方案：mysql -uroot -p # 登录MySQL Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 19 Server version: 8.0.20-0ubuntu0.20.04.1 (Ubuntu) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use mysql; Database changed mysql> ALTER USER 'root'@'%' IDENTIFIED BY '123456'; ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'%' mysql> use mysql; Database changed mysql> select user,host from user; +------------------+-----------+ | user | host | +------------------+-----------+ | debian-sys-maint | localhost | | mysql.infoschema | localhost | | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +------------------+-----------+ 5 rows in set (0.00 sec) ############注释############ 我们可以看到，root用户对应的host为localhost，因此修改密码的语句应该写成： alter user 'root'@'localhost' identified by '123456'; ##########注释完了########## mysql> alter user 'root'@'localhost' identified by '123456'; Query OK, 0 rows affected (0.01 sec) mysql>

3. ERROR 1449 (HY000):

3.1 Navicat 初次连接刚配置好的MySQL报错1449

具体情况为：用户已经创建，支持远程登录，并且分配了权限，在Navicat中使用该用户连接时，测试连接成功，但是真正连接的时候就报错1449： 1449 - The user specified as a definer (‘mysql.infoschema’@‘localhost’) does not exist 解决方案： ‘mysql.infoschema’@‘localhost’ 一看就是一个用户，检查时发现该用户虽然可以被查询到，但是其实并不存在，而且不能直接创建该用户，需要先删除然后再创建，接着进行赋予权限等操作。具体代码和执行过程如下：xiaoyao@Ubuntu2004:~$ mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 24 Server version: 8.0.20-0ubuntu0.20.04.1 (Ubuntu) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use mysql; Database changed mysql> show tables; ERROR 1449 (HY000): The user specified as a definer ('mysql.infoschema'@'localhost') does not exist mysql> drop user 'mysql.infoschema'@'localhost'; Query OK, 0 rows affected (0.01 sec) mysql> flush privileges; Query OK, 0 rows affected (0.01 sec) mysql> create user 'mysql.infoschema'@'localhost' identified by '12345678'; Query OK, 0 rows affected (0.01 sec) mysql> flush privileges; Query OK, 0 rows affected (0.01 sec) mysql> grant all privileges on *.* to 'mysql.infoschema'@'localhost' with grant option; Query OK, 0 rows affected (0.01 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | component | | db | ***************************** 至此，Navicat即可完成连接：

4. 10060 “Unknown error”

Navicat 远程连接虚拟机中的MySQL，测试连接时报错 10060 "Unknown error"，如下图: 问题可能处在防火墙上，可以查看防火墙的相关规则，并配置允许远程访问相关端口，本文中使用的时ubuntu20.10+ufw，执行如下命令后，Navicat即可正常连接MySQL。