环境: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’ 一看就是一个用户,检查时发现该用户虽然可以被查询到,但是其实并不存在,而且不能直接创建该用户,需要先删除然后再创建,接着进行赋予权限等操作。具体代码和执行过程如下:
至此,Navicat即可完成连接: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 | *****************************
4. 10060 “Unknown error”
Navicat 远程连接虚拟机中的MySQL,测试连接时报错 10060 "Unknown error"
,如下图:
问题可能处在防火墙上,可以查看防火墙的相关规则,并配置允许远程访问相关端口,本文中使用的时ubuntu20.10+ufw,执行如下命令后,Navicat即可正常连接MySQL。