MySQL数据库root密码忘记,可通过–skip-grant-tables参数跳过权限表访问数据库。
忘记root密码,进不去数据库
[root@mysql1 ~]# mysql -uroot -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
查看MySQL进程号,kill掉MySQL进程,强制关闭数据库
[root@mysql1 ~]# ps -ef | grep mysql
avahi 838 1 0 14:57 ? 00:00:00 avahi-daemon: running [mysql1.local]
mysql 1884 1 0 15:11 ? 00:00:01 /mysql/base5738/bin/mysqld --defaults-file=/mysql/my3306/my.cnf --datadir=/mysqldata/my3306/data --socket=/mysql/my3306/mysql.sock --user=mysql
root 2377 1539 0 15:27 pts/0 00:00:00 grep --color=auto mysql
[root@mysql1 ~]#
[root@mysql1 ~]# kill -9 1884
[root@mysql1 ~]#
[root@mysql1 ~]# ps -ef | grep mysql
avahi 838 1 0 14:57 ? 00:00:00 avahi-daemon: running [mysql1.local]
root 2410 1539 0 15:28 pts/0 00:00:00 grep --color=auto mysql
添加跳过权限表参数,重启数据库
[root@mysql1 ~]# cd /mysql/base5738/bin/
[root@mysql1 bin]# ./mysqld_safe --defaults-file=/mysql/my3306/my.cnf --skip-grant-tables &
[1] 2577
[root@mysql1 bin]# 2022-08-22T07:30:50.476956Z mysqld_safe Logging to '/mysql/my3306/log/error.log'.
2022-08-22T07:30:50.509845Z mysqld_safe Starting mysqld daemon with databases from /mysqldata/my3306/data
[root@mysql1 bin]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.38-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, 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 schemas;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
给root用户设置新密码,并刷新权限
mysql> use mysq;
ERROR 1049 (42000): Unknown database 'mysq'
mysql>
mysql>
mysql> use mysql;
Database changed
mysql> update user set authentication_string=password('rootroot') where user='root';
Query OK, 0 rows affected, 1 warning (0.01 sec)
Rows matched: 3 Changed: 0 Warnings: 1
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
重启数据库,使用密码进入数据库。
mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
[root@mysql1 bin]#
[root@mysql1 bin]#
[root@mysql1 bin]# 2022-08-22T07:36:00.177170Z mysqld_safe mysqld from pid file /mysql/my3306/mysql.pid ended
[1]+ Done ./mysqld_safe --defaults-file=/mysql/my3306/my.cnf --skip-grant-tables
[root@mysql1 bin]#
[root@mysql1 bin]#
[root@mysql1 bin]# ps -ef | grep mysql
avahi 838 1 0 14:57 ? 00:00:00 avahi-daemon: running [mysql1.local]
root 4008 1539 0 15:36 pts/0 00:00:00 grep --color=auto mysql
[root@mysql1 bin]# systemctl start mysqld
[root@mysql1 bin]#
[root@mysql1 bin]# ps -ef | grep mysql
avahi 838 1 0 14:57 ? 00:00:00 avahi-daemon: running [mysql1.local]
mysql 4054 1 5 15:36 ? 00:00:00 /mysql/base5738/bin/mysqld --defaults-file=/mysql/my3306/my.cnf --datadir=/mysqldata/my3306/data --socket=/mysql/my3306/mysql.sock --user=mysql
root 4087 1539 0 15:36 pts/0 00:00:00 grep --color=auto mysql
[root@mysql1 bin]#
[root@mysql1 bin]# mysql -uroot -prootroot
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 2
Server version: 5.7.38-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, 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 schemas;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)