总有人间一两风,填我十万八千梦

Linux/Ubuntu下安装新版MySQL 设置root远程登陆,以及auth_socket的使用, ERROR 1698 (28000): Access denied for user ‘root’@’localhost’

软件技巧 Zero、J 1315℃ 0评论

本文将会介绍在Ubuntu下MySQL的安装操作,在安装之后,通过配置使得我们的MySQL可以被远程访问到。如果不进行配置使用root登陆的时候 mysql -uroot -p 将会提示 ERROR 1698 (28000): Access denied for user ‘root’@’localhost’ 。

系统环境

  • 操作系统:Ubuntu Server x64 18.04 LTS
  • 数据库:MySQL v14 ( auth_socket是5.7之后出现的 )
  • 本文中为了方便,使用的是root权限账户,因此特权操作无需sudo

安装MySQL

  • 使用命令 apt update 更新软件源信息。
  • 使用命令 apt install -y mysql-server 安装数据库,-y表示直接确认安装。
root@dev:~# apt update
root@dev:~# apt install -y mysql-server

配置MySQL

全新安装MySQL之后,你需要运行 mysql_secure_installation 来配置一些安全选项,为了方便这里我们并没有设置VALIDATE PASSWORD PLUGIN来测试密码的强度,就是简单的密码123456。

######################执行mysql_secure_installation
root@dev:~# mysql_secure_installation

Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?
######################输入非y|Y任意字符,不安装VALIDATE PASSWORD plugin,因为我用的是弱密码
######################测试发现,这里设置的root密码没有卵用~~
Press y|Y for Yes, any other key for No: N
Please set the password for root here.
######################输入密码
New password:
######################重复密码
Re-enter new password:
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. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
####################是否移除匿名用户,y|Y表示移除,其余跳过
Remove anonymous users? (Press y|Y for Yes, any other key for No) : N

 ... skipping.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
###################禁用root在非localhost本地登陆,Y|y表示确认,其余字符表示允许
###################(这里并不能然你root远程登陆~~2333)
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : N

 ... skipping.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.

###################移除测试数据库
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : N

 ... skipping.
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
Success.

All done!

修改MySQL监听的网卡地址

配置文件位于 /etc/mysql/mysql.conf.d/mysqld.cnf ,默认情况下bind-address = 127.0.0.1 ,这里我们修改为0.0.0.0 表示监听所有的网卡。

修改 bind-address 为0.0.0.0

完成之后,输入命令 service mysql restart 重启一下MySQL的服务。(注意,如果你开启了防火墙,请务必开启MySQL端口入站放行,默认是3306)

修改MySQL登陆用户配置

终端输入mysql,命令将会直接打开mysql的控制台。(为什么这里能够直接打开?那是因为默认的root是auth_socket授权,后续会说明这是个啥玩意儿)。输入查询语句 SELECT user,authentication_string,plugin,host FROM mysql.user; 将会显示出当前用户表的这几个字段。

root@dev:~# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.29-0ubuntu0.18.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> SELECT user,authentication_string,plugin,host FROM mysql.user;
+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             |                                           | auth_socket           | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *F9E317C1350960693D8AD271C2D3513444F6A460 | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)

从输出的内容中可以看到,当前root用户的host是localhost,且plugin是auth_socket ,此时root账户不允许通过账户密码登陆。当我们在在终端输入mysql命令的时候,实际就是使用的是这个root账户,是没有密码的,但是也仅限于在本地登陆(因为用的是auth_socket ),我们前面mysql_secure_installation设置的密码没有啥用。

我们最终的目标是是的root账户能够远程登陆,因此我们需要设置一下root的登陆密码,以及修改host为%,表示任意主机。

将root设置为密码认证,ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的密码';

使用update更新host为%,update mysql.user set host='%' where user='root' and host='localhost';

操作完成之后,再次 SELECT user,authentication_string,plugin,host FROM mysql.user;进行查看

##############设置为密码认证,这里密码是 123456
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.00 sec)
##############修改host为 %
mysql> update mysql.user set host='%' where user='root' and host='localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
##############查看
mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;
+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | mysql_native_password | %         |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *20C4BE3C818A2475CE9748D6566AF2066472D4DA | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)

使用SQL语句 FLUSH PRIVILEGES ; 刷新一下权限表(不刷新,通过update更新的host不生效!);

完成之后,使用Ctrl+Z或者输入exit退出我们的MySQL控制台,此时我们直接使用mysql命令已经不能登录了。此时我们使用远程登陆,我这里的主机IP地址是 192.168.179.135 ,使用命令 mysql -h 192.168.179.135 -u root -p ,然后输入密码即可登陆成功。

root@dev:~# mysql -h 192.168.179.135 -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.29-0ubuntu0.18.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>

使用Navicat测试,如图所示。

测试连接

如果你想要将root重新修改为auth_socket

首先设置认证为auth_socket,然后设置host为localhost(测试发现这一步不更新也没问题),最后刷新权限。

###############修改为auth_socket 
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH auth_socket;
Query OK, 0 rows affected (0.00 sec)
##############重新修改host为 localhost,这个不更新也没关系
mysql> update mysql.user set host='localhost' where user='root' and host='%';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
##############刷新一下
mysql> FLUSH PRIVILEGES ;
Query OK, 0 rows affected (0.00 sec)

如何添加auth_socket认证的用户

这个玩意儿是新增的一个认证方式,我们用一个简单的例子来说明其用法。假设当前登录的用户是root,我们的主机上有一个用户test,当我们在终端中使用了命令 mysql -u test 的时候(不带有p密码参数,默认是auth_socket认证,不带有u参数,默认是root用户),此时MySQL将会使用test用户名进行认证,如果当前终端登陆用户也是test且用户表中存在test的auth_socket认证,那么就认证成功,否则提示禁止登陆,即使你终端是root也不行,因为这个要对比当前登录用户。

为了验证我们的描述,我们首先来创建一个test用户。CREATE USER 'test'@'localhost' IDENTIFIED WITH auth_socket; (关于用户授权,这里我们不讨论)

退出MySQL控制台,使用命令mysql -u test 尝试使用test无密码登陆(如果你不指定-u 用户名参数,将会使用root作为用户名),此时将会提示禁止登陆。

root@dev:~# mysql -u test
ERROR 1698 (28000): Access denied for user 'test'@'localhost'

我们在系统中增加一个test用户,useradd test ,然后切换到 test用户,su test ,最后使用命令mysql -u test ,可以看到登陆成功。

root@dev:~# useradd test
root@dev:~# su test
$ mysql -u test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 5.7.29-0ubuntu0.18.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>

转载请注明:悠然品鉴 » Linux/Ubuntu下安装新版MySQL 设置root远程登陆,以及auth_socket的使用, ERROR 1698 (28000): Access denied for user ‘root’@’localhost’

喜欢 (0)or分享 (0)
发表我的评论
取消评论

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址