问题:
困扰了很久的问题,,
使用apt-get来安装mysql,安装好之后发现安装的是 MariaDB,如下,无需密码既可以登录了。即使使用mysqladmin设置好密码,用密码登录可以,不用密码登录也可以
root@ubuntu:/etc/mysql# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 35
Server version: 10.0.31-MariaDB-0ubuntu0.16.04.2 Ubuntu 16.04Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 35
Server version: 10.0.31-MariaDB-0ubuntu0.16.04.2 Ubuntu 16.04Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
排查思路:
第一看看my.conf有没有skip-grant-tables,跳过密码验证
过滤了下没有
root@ubuntu:~# cd /etc/mysql/ root@ubuntu:/etc/mysql# pwd /etc/mysql root@ubuntu:/etc/mysql# ls -l 总用量 36 drwxr-xr-x 2 root root 4096 12月 7 18:05 conf.d -rw------- 1 root root 277 12月 7 17:31 debian.cnf -rw------- 1 root root 317 12月 7 17:05 debian.cnf-5.7 -rwxr-xr-x 1 root root 1426 7月 1 04:26 debian-start -rw-r--r-- 1 root root 869 7月 1 04:26 mariadb.cnf drwxr-xr-x 2 root root 4096 12月 7 18:08 mariadb.conf.d lrwxrwxrwx 1 root root 24 12月 7 17:18 my.cnf -> /etc/alternatives/my.cnf -rw-r--r-- 1 root root 839 1月 22 2017 my.cnf.fallback -rw-r--r-- 1 root root 682 2月 4 2017 mysql.cnf drwxr-xr-x 2 root root 4096 12月 7 18:08 mysql.conf.d root@ubuntu:/etc/mysql# grep "skip-grant-tables" -r root@ubuntu:/etc/mysql#
看看my.cnf里面是不是把密码写进去了,查找了相关.cnf文件去看了看也没有
root@ubuntu:~# find / -name "*.cnf" /usr/share/ssl-cert/ssleay.cnf /usr/share/dovecot/dovecot-openssl.cnf /usr/lib/ssl/openssl.cnf /etc/ssl/openssl.cnf /etc/alternatives/my.cnf /etc/mysql/my.cnf /etc/mysql/mariadb.cnf /etc/mysql/conf.d/mysqldump.cnf /etc/mysql/conf.d/mysql.cnf /etc/mysql/mariadb.conf.d/50-mysqld_safe.cnf /etc/mysql/mariadb.conf.d/50-mysql-clients.cnf /etc/mysql/mariadb.conf.d/50-client.cnf /etc/mysql/mariadb.conf.d/50-server.cnf /etc/mysql/debian.cnf /var/lib/dpkg/alternatives/my.cnf root@ubuntu:~#
不过有个小发现,
vim /etc/mysql/debian.cnf # Automatically generated for Debian scripts. DO NOT TOUCH! [client] host = localhost user = root password = socket = /var/run/mysqld/mysqld.sock [mysql_upgrade] host = localhost user = root password = socket = /var/run/mysqld/mysqld.sock basedir = /usr
最后的最后,,,,去google了很久,终于有发现了,是用户插件问题。
参见这里:https://nixmash.com/post/fix-for-mysql-rootlocalhost-access-denied-on-new-installs
第一我去跟安装正常的mysql来比较下,如下
正常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)
看到这里应该发现问题了,按照正常的修改就行了
如下:
sudo service mysql stop sudo 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) 先杀死mysql kill -9 pid 启动: sudo service mysql start
最后验证下:需要密码了
root@ubuntu:~# mysql ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) root@ubuntu:~#