Ubuntu18.04.1下MySQL5.7的安装和卸载以及如何解决MySQL Error 1698(28000)错误:Access denied for user'root'@'localhost'

一、MySQL5.7的安装

1.安装Mysql
     #命令1:
     sudo apt-get update
     #命令2:
     sudo apt-get install mysql-server

2.配置MySQL
     #命令:
     sudo mysql_secure_installation
     详情:

#1
VALIDATE PASSWORD PLUGIN can be used to test passwords...
Press y|Y for Yes, any other key for No: N (我的选项)

#2
Please set the password for root here...
New password: (输入密码) Re-enter new password: (重复输入)

#3
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them...
Remove anonymous users? (Press y|Y for Yes, any other key for No) : N (我的选项)

#4
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network...
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y (我的选项)

#5
By default, MySQL comes with a database named 'test' that
anyone can access...
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : N (我的选项)

#6
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y (我的选项)

3.检查MySQL服务状态
   #命令:systemctl status mysql.service

 

二、MySQL的使用

1.首先使用根用户root进入mysql
#命令1:
sudo mysql -uroot -p
默认的MySQL安装之后根用户是没有密码的，命令执行后会让你设置root的密码，进入root后才能进行其他设置
#命令2:
use mysql;
输入这个命令后就可以开始操作mysql了

2.可以进入mysql后更换root用户的密码
#命令:
GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY "a12345";

3.新建数据库和用户，用root用户新建数据和用作远程访问的用户

#1 创建数据库spider
CREATE DATABASE spider;
#2 创建用户Ferdinand(密码a12345) 并赋予其spider数据库的所有权限
GRANT ALL PRIVILEGES ON spider.* TO Ferdinand@localhost IDENTIFIED BY "a12345";

4.配置远程访问或控制

在 /etc/mysql/mysql.conf.d 路径下输入命令:  sudo gedit mysqld.cnf
注释此行:
bind-address = 127.0.0.1

至此，MySQL的安装及其配置就完成了。

三、MySQL的卸载

我们在配置过程中肯定会遇到各种各样的问题，但是我们可以选择卸载重装来再尝试。

1.首先先删除mysql:
#命令：sudo apt-get remove mysql-*

2.清理残留的数据：
#命令：dpkg -l |grep ^rc|awk '{print $2}' |sudo xargs dpkg -P

这样，MySQL就已经卸载成功了。

四、解决MySQL Error 1698(28000)错误:Access denied for user'root'@'localhost'

当你在使用workbench或者第三方库pymysql尝试用root连接数据库是出现上述错误，你可以通过下述操作去解决这个问题。

mysql> select user, plugin from mysql.user;
+-----------+-----------------------+
| user      | plugin                |
+-----------+-----------------------+
| root      | auth_socket           |
| mysql.sys | mysql_native_password |
| dev       | mysql_native_password |
+-----------+-----------------------+
3 rows in set (0.01 sec)

通过这里我们可以看到root用户的plugin是auth_socket，我们将其改为mysql_native_password就可以解决了。

a.输入命令：sudo mysql -uroot -p
输入密码后进入mysql

b.输入命令: use mysql;
进入mysql操作模式

c.输入命令:ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'test';

d.输入命令:GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY "a12345";
重新更改密码

e.输入命令:flush privileges;
更新一下配置

f.关闭并重启mysql后就解决问题了