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

冷不防 2022-01-28 00:55 351阅读 0赞

**遇到问题**

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解决方法同上。