如何解决MariaDB无密码就可以登录的问题(debian)

发表于 更新于 分类于

困扰了很久的问题,使用apt-get来安装mysql,安装好之后发现安装的是 MariaDB,如下,无需密码既可以登录了.即使使用mysqladmin设置好密码,用密码登录可以,不用密码登录也可以.

排查思路:

一、看看my.conf有没有skip-grant-tables,跳过密码验证. 去跟安装正常的mysql来比较下,如下:

正常mysql:

1
2
3
4
5
6
7
mysql> select user, plugin from mysql.user where plugin = 'mysql_native_password';
+-----------+-----------------------+
| user      | plugin                |
+-----------+-----------------------+
| root      | mysql_native_password |
+-----------+-----------------------+
8 rows in set (0.00 sec)

不正常的:

1
2
3
4
5
6
7
MariaDB [(none)]> select user, plugin from mysql.user;
+------+-------------+
| user | plugin      |
+------+-------------+
| root | unix_socket |
+------+-------------+
1 row in set (0.00 sec)

看到这里应该发现问题了,按照正常的修改就行了.

如下:

1
2
systemctl stop mysqld
mysqld_safe --skip-grant-tables

进去mysql执行如下命令:

1
2
MariaDB [(none)]> UPDATE mysql.user SET authentication_string = PASSWORD('mypassword'), plugin = 'mysql_native_password' WHERE User = 'root' AND Host = 'localhost';
MariaDB [(none)]> FLUSH PRIVILEGES;

验证:

1
2
3
4
5
6
7
MariaDB [(none)]> select user, plugin from mysql.user;
+------+-----------------------+
| user | plugin                |
+------+-----------------------+
| root | mysql_native_password |
+------+-----------------------+
1 row in set (0.01 sec)

ps -ef 查询mysql的pid并杀死:

1
kill -9 pid

启动:

1
systemctl start mysqld

最后验证下:需要密码了.

1
2
root@debian:~# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

二、网上还有说可以用另外一种办法:

MariaDB root 账户默认使用了 UNIX_SOCKET 插件,只允许 root 用户连接,无需密码. 可以使用 root 用户连接后创建新用户或者执行:

1
2
3
4
5
update mysql.user set plugin = ''where User='root'; 
flush privileges; 
or 
update mysql.user set plugin = 'mysql_native_password' where User='root'; 
flush privileges;

如果需要密码后,却无法进入,可按网上方法进行修改,如下:

方法1:

用SET PASSWORD命令:

1
2
mysql -u root
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass');

方法2:

用mysqladmin:

1
mysqladmin -u root password "newpass"

如果root已经设置过密码,采用如下方法:

1
mysqladmin -u root password oldpass "newpass"

方法3:

用UPDATE直接编辑user表:

1
2
3
4
mysql -u root
mysql> use mysql;
mysql> UPDATE user SET Password = PASSWORD('newpass') WHERE user = 'root';
mysql> FLUSH PRIVILEGES;

在丢失root密码的时候,可以这样:

1
2
3
4
mysqld_safe --skip-grant-tables&
mysql -u root mysql
mysql> UPDATE user SET password=PASSWORD("new password") WHERE user='root';
mysql> FLUSH PRIVILEGES;