MySQL无法启动，修改密码问题分析
遇到第一件事就是查看错误日志
位置：在mysql数据存放目录下的，主机名.err文件
[root@mysql ~]# tail /data/mysql/data/mysql.err
2020-03-06T03:00:07.267361Z 0 [Note] Shutting down plugin 'MEMORY'
2020-03-06T03:00:07.267394Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2020-03-06T03:00:07.267453Z 0 [Note] Shutting down plugin 'MRG_MYISAM'
2020-03-06T03:00:07.267459Z 0 [Note] Shutting down plugin 'MyISAM'
2020-03-06T03:00:07.267480Z 0 [Note] Shutting down plugin 'CSV'
2020-03-06T03:00:07.267486Z 0 [Note] Shutting down plugin 'sha256_password'
2020-03-06T03:00:07.267488Z 0 [Note] Shutting down plugin 'mysql_native_password'
2020-03-06T03:00:07.267781Z 0 [Note] Shutting down plugin 'binlog'
2020-03-06T03:00:07.268267Z 0 [Note] /server/tools/mysql/bin/mysqld: Shutdown complete
如果看日志内不显示，可以把日志显示到屏幕里
/server/tools/mysql/bin/mysqld --defaults-file=/etc/my.cnf
ctrl +\ -----结束
一般启动不了的情况：
- 配置文件my.cnf不正确
- 目录没有配置权限，或者没有递归配置
- /tmp/mysql.sock文件被删除或者被修改
- 参数改错了
管理员忘记mysql的密码怎么办
1.首先关闭数据库
[root@mysql ~]# systemctl stop mysqld
2.启动数据库到维修模式
mysqld_safe --skip-grant-tables --skip-networking &
--skip-grant-tables 跳过授权表
--skip-networking 禁止远程登陆
[root@mysql ~]# mysqld_safe --skip-grant-tables --skip-networking & [1] 10410 [root@mysql ~]# 2020-03-06T03:33:13.771897Z mysqld_safe Logging to '/data/mysql/data/mysql.err'. 2020-03-06T03:33:13.811456Z mysqld_safe Starting mysqld daemon with databases from /data/mysql/data [root@mysql ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.26 MySQL Community Server (GPL) Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. 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>
启动维修模式，直接敲mysql可以进入mysql
mysql> select user,host from mysql.user;
查看用户信息
mysql> select user,host from mysql.user; +---------------+-----------+ | user | host | +---------------+-----------+ | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +---------------+-----------+ 3 rows in set (0.00 sec)
查看用户字段里的 authentication_string
mysql> desc mysql.user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(64) | NO | | mysql_native_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| password_last_changed | timestamp | YES | | NULL | |
| password_lifetime | smallint(5) unsigned | YES | | NULL | |
| account_locked | enum('N','Y') | NO | | N | |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.00 sec)通过authentication_string这个字段查看密码
select user,host,authentication_string from mysql.user;
mysql> select user,host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user | host | authentication_string |
+---------------+-----------+-------------------------------------------+
| root | localhost | *9091D3171696090E18C89C525A8D22028CA2B479 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)修改root密码，和之前的密码进行对比检验是否成功
grant all on "*" to root@'localhost' identified by '123';
mysql> grant all on *.* to root@'localhost' identified by '123';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"*" to root@'localhost' identified by '123'' at line 1错误原因:
- 维修模式添加了--skip-networking参数无法进行远程修改
处理方法:
手工加载授权表
flush privileges;
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on *.* to root@'localhost' identified by '123';
Query OK, 0 rows affected, 1 warning (0.00 sec)进行检查是否有变化
mysql> select user,host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user | host | authentication_string |
+---------------+-----------+-------------------------------------------+
| root | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
3 rows in set (0.01 sec)对比：变化了
- root | localhost | *9091D3171696090E18C89C525A8D22028CA2B479 |
- root | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
现在就是验证，关闭维修模式，重启mysqld服务进行验证
重启使用这种方式：其它都不行
- /etc/init.d/mysqld restart
[root@mysql ~]# /etc/init.d/mysqld restart Shutting down MySQL.. 2020-03-06T05:39:22.647326Z mysqld_safe mysqld from pid file /data/mysql/data/mysql.pid ended SUCCESS! Starting MySQL. SUCCESS! [1]+ Done mysqld_safe --skip-grant-tables --skip-networking [root@mysql ~]# netstat -lntup|grep mysqld tcp6 0 0 :::3306 :::* LISTEN 12976/mysqld
输入密码123验证成功
[root@mysql ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
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>
继续阅读
我的微信
这是我的微信扫一扫
评论