解决MariaDB或MySQL登录时忘记密码或出现Access denied for user

遇到问题

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

解决方案

1. 修改 my.cnf 配置文件
   在 /etc/my.cnf 文件末尾加入 skip-grant-tables配置

   [mysqld]
   datadir=/var/lib/mysql
   socket=/var/lib/mysql/mysql.sock
   # Disabling symbolic-links is recommended to prevent assorted security risks
   symbolic-links=0
   # Settings user and group are ignored when systemd is used.
   # If you need to run mysqld under a different user or group,
   # customize your systemd unit file for mariadb according to the
   # instructions in http://fedoraproject.org/wiki/Systemd
   
   [mysqld_safe]
   log-error=/var/log/mariadb/mariadb.log
   pid-file=/var/run/mariadb/mariadb.pid
   
   #
   # include all files from the config directory
   #
   !includedir /etc/my.cnf.d
   #增加下面配置使无密码登录
   skip-grant-tables       
   

2. 重启MariaDB服务

   [root@cdh01 ~]# systemctl restart mariadb
   

3. 登录MariaDB

   在命令行中,输入 mysql -u root -p命令然后回车，当需要输入密码时，直接按enter键，便可以不用密码登录到数据库当中。
   修改密码后，执行flush privileges后退出。

   [root@cdh01 yum.repos.d]# mysql -uroot -p
   Enter password: 
   Welcome to the MariaDB monitor.  Commands end with ; or \g.
   Your MariaDB connection id is 3
   Server version: 5.5.60-MariaDB MariaDB Server
   
   Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
   
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   
   MariaDB [(none)]> update mysql.user set password=password("123456") where user="root";
   Query OK, 0 rows affected (0.00 sec)
   
   MariaDB [(none)]> flush privileges;
   Query OK, 0 rows affected (0.00 sec)
   
   MariaDB [(none)]> quit;
   Bye
   

4. 重新修改 my.cnf 配置文件

   将skip-grant-tables配置删除或注释掉后，重启mysql服务

   [root@cdh01 ~]# systemctl restart mariadb
   

5. 利用修改后的密码登录 MariaDB 进行测试

   [root@cdh01 ~]# mysql -uroot -p123456
   Welcome to the MariaDB monitor.  Commands end with ; or \g.
   Your MariaDB connection id is 3
   Server version: 5.5.60-MariaDB MariaDB Server
   
   Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
   
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   
   MariaDB [(none)]> 
   

   MySQL解决方法同上。