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

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

排查思路:

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

1 正常mysql:

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)

不正常的:

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

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

systemctl stop mysqld
mysqld_safe --skip-grant-tables

进去mysql执行如下命令:

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

验证:

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并杀死:

kill -9 pid

启动:

systemctl start mysqld

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

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

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

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

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命令:

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

方法2:用mysqladmin:

mysqladmin -u root password "newpass"

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

mysqladmin -u root password oldpass "newpass"

方法3: 用UPDATE直接编辑user表:

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

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

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