MySQL-root用户密码登录不了解决思路
1. 环境查看
# 查看是什么操作系统?
[root@mysql-host ~]# cat /etc/os-release
NAME="CentOS Linux"
VERSION="7 (Core)"
ID="centos"
ID_LIKE="rhel fedora"
VERSION_ID="7"
PRETTY_NAME="CentOS Linux 7 (Core)"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:centos:centos:7"
HOME_URL="https://www.centos.org/"
BUG_REPORT_URL="https://bugs.centos.org/"
CENTOS_MANTISBT_PROJECT="CentOS-7"
CENTOS_MANTISBT_PROJECT_VERSION="7"
REDHAT_SUPPORT_PRODUCT="centos"
REDHAT_SUPPORT_PRODUCT_VERSION="7"
[root@mysql-host ~]#
2. 查看服务状态
# 查看mysql服务的启动情况
[root@mysql-host ~]# systemctl status mysqld.service
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Thu 2024-06-20 17:21:31 CST; 6min ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 8570 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 8548 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 8591 (mysqld)
CGroup: /system.slice/mysqld.service
└─8591 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
Jun 20 17:21:30 mysql-host systemd[1]: Starting MySQL Server...
Jun 20 17:21:31 mysql-host systemd[1]: Started MySQL Server.
[root@mysql-host ~]#
3. 查看日志
# 查看MySQL服务器的日志文件中root用户的密码。
[root@mysql-host ~]# cat /var/log/mysqld.log | grep -i password
2023-12-25T13:05:22.792559Z 1 [Note] A temporary password is generated for root@localhost: MydQ?XHOs1Bw # MySQL-root用户的密码
2023-12-25T13:09:12.985966Z 0 [Note] Shutting down plugin 'validate_password'
2023-12-25T13:09:14.296267Z 0 [Note] Shutting down plugin 'sha256_password'
2023-12-25T13:09:14.296268Z 0 [Note] Shutting down plugin 'mysql_native_password'
2023-12-25T13:12:09.837160Z 0 [Note] Shutting down plugin 'validate_password'
2023-12-25T13:12:11.549468Z 0 [Note] Shutting down plugin 'sha256_password'
2023-12-25T13:12:11.549469Z 0 [Note] Shutting down plugin 'mysql_native_password'
[root@mysql-host ~]#
4. 再一次登录
# 尝试用MySQL服务器日志文件中root用户的密码登录
[root@mysql-host ~]# mysql -uroot -pMydQ?XHOs1Bw
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@mysql-host ~]#
还是登录不了,解决办法,重置root用户的密码,刷新权限
5. 重置密码
# STEP1 编辑/etc/my.cnf文件,在 [mysqld] 部分下添加skip-grant-tables
[root@mysql-host ~]# vim /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#
skip-grant-tables # 禁用权限验证,跳过授权表检查
[root@mysql-host ~]#
# STEP2 重启服务,并修改密码
[root@mysql-host ~]# systemctl restart mysqld.service
[root@mysql-host ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.44 MySQL Community Server (GPL)
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema | |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.00 sec)
mysql> update user set authentication_string=PASSWORD("Admin@1234") where user='root';
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> exit;
Bye
[root@mysql-host ~]#
# STEP3 取消禁用授权,跳过授权表,并重启服务
[root@mysql-host ~]# vim /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#
#skip-grant-tables #注释掉这一行
~
[root@mysql-host ~]# systemctl restart mysqld.service
6. 登录测试
[root@mysql-host ~]# mysql -uroot -pAdmin@1234
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.44 MySQL Community Server (GPL)
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql>
[root@mysql-host ~]#
# 实际使用时,不建议把密码明文
[root@mysql-host ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.44 MySQL Community Server (GPL)
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> exit;
Bye
[root@mysql-host ~]#