mariadb直接可以mysql登录_解决 MariaDB无密码就可以登录的问题

问题：

困扰了很久的问题，，

使用apt-get来安装mysql，安装好之后发现安装的是 MariaDB，如下，无需密码既可以登录了。即使使用mysqladmin设置好密码，用密码登录可以，不用密码登录也可以

1 root@ubuntu:/etc/mysql# mysql

2 Welcome to the MariaDB monitor. Commands end with ; or \g.

3 Your MariaDB connection id is 35

4 Server version: 10.0.31-MariaDB-0ubuntu0.16.04.2 Ubuntu 16.04

5

6 Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

7

8 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

排查思路：

第一看看my.conf有没有skip-grant-tables，跳过密码验证

过滤了下没有

1 root@ubuntu:~# cd /etc/mysql/

2 root@ubuntu:/etc/mysql# pwd

3 /etc/mysql

4 root@ubuntu:/etc/mysql# ls -l

5 总用量 36

6 drwxr-xr-x 2 root root 4096 12月 7 18:05 conf.d

7 -rw------- 1 root root 277 12月 7 17:31 debian.cnf

8 -rw------- 1 root root 317 12月 7 17:05 debian.cnf-5.7

9 -rwxr-xr-x 1 root root 1426 7月 1 04:26 debian-start

10 -rw-r--r-- 1 root root 869 7月 1 04:26 mariadb.cnf

11 drwxr-xr-x 2 root root 4096 12月 7 18:08 mariadb.conf.d

12 lrwxrwxrwx 1 root root 24 12月 7 17:18 my.cnf -> /etc/alternatives/my.cnf

13 -rw-r--r-- 1 root root 839 1月 22 2017 my.cnf.fallback

14 -rw-r--r-- 1 root root 682 2月 4 2017 mysql.cnf

15 drwxr-xr-x 2 root root 4096 12月 7 18:08 mysql.conf.d

16 root@ubuntu:/etc/mysql# grep "skip-grant-tables" -r

17 root@ubuntu:/etc/mysql#

看看my.cnf里面是不是把密码写进去了，查找了相关.cnf文件去看了看也没有

1 root@ubuntu:~# find / -name "*.cnf"

2 /usr/share/ssl-cert/ssleay.cnf

3 /usr/share/dovecot/dovecot-openssl.cnf

4 /usr/lib/ssl/openssl.cnf

5 /etc/ssl/openssl.cnf

6 /etc/alternatives/my.cnf

7 /etc/mysql/my.cnf

8 /etc/mysql/mariadb.cnf

9 /etc/mysql/conf.d/mysqldump.cnf

10 /etc/mysql/conf.d/mysql.cnf

11 /etc/mysql/mariadb.conf.d/50-mysqld_safe.cnf

12 /etc/mysql/mariadb.conf.d/50-mysql-clients.cnf

13 /etc/mysql/mariadb.conf.d/50-client.cnf

14 /etc/mysql/mariadb.conf.d/50-server.cnf

15 /etc/mysql/debian.cnf

16 /var/lib/dpkg/alternatives/my.cnf

17 root@ubuntu:~#

不过有个小发现，

1 vim /etc/mysql/debian.cnf

2 # Automatically generated for Debian scripts. DO NOT TOUCH!

3 [client]

4 host = localhost

5 user = root

6 password =

7 socket = /var/run/mysqld/mysqld.sock

8 [mysql_upgrade]

9 host = localhost

10 user = root

11 password =

12 socket = /var/run/mysqld/mysqld.sock

13 basedir = /usr

14

15 看了说明是以上由脚本生成，不要改动，

16 虽然这样写，我也去改了下，加上密码，重启还是不行

最后的最后，，，，去google了很久，终于有发现了,是用户插件问题。

第一我去跟安装正常的mysql来比较下，如下

1 正常mysql

2 mysql> select user, plugin from mysql.user where plugin = 'mysql_native_password';

3 +-----------+-----------------------+

4 | user | plugin |

5 +-----------+-----------------------+

6 | root | mysql_native_password |

7 +-----------+-----------------------+

8 8 rows in set (0.00 sec)

1 不正常的

2

3 MariaDB [(none)]> select user, plugin from mysql.user;

4 +------+-------------+

5 | user | plugin |

6 +------+-------------+

7 | root | unix_socket |

8 +------+-------------+

9 1 row in set (0.00 sec)

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

如下：

1 sudo service mysql stop

2 sudo mysqld_safe --skip-grant-tables

3 进去mysql执行如下命令：

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

5 MariaDB [(none)]> FLUSH PRIVILEGES;

6 验证：

7 MariaDB [(none)]> select user, plugin from mysql.user

8 -> ;

9 +------+-----------------------+

10 | user | plugin |

11 +------+-----------------------+

12 | root | mysql_native_password |

13 +------+-----------------------+

14 1 row in set (0.01 sec)

15

16 先杀死mysql kill -9 pid

17 启动：

18 sudo service mysql start

最后验证下：需要密码了

root@ubuntu:~# mysql

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

root@ubuntu:~#