MySQL无法启动,修改密码问题分析

root
233
文章
0
评论
2020年3月6日13:56:09 评论 6585字阅读21分57秒

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> 

 

 

 

继续阅读
weinxin
我的微信
这是我的微信扫一扫
  • 文本由 发表于 2020年3月6日13:56:09
  • 除非特殊声明,本站文章均为原创,转载请务必保留本文链接
Mysql 创建表 MySQL

Mysql 创建表

Mysql 创建表   Mysql创建数据库 CREATE DATABASE db_name //字符集 CHARACTER SET charset_name //校验规则 COLLATE ...
Mysql数据类型 MySQL

Mysql数据类型

Mysql数据类型   INT整形类型---属性 unsigned/signed 无符号/有符号 unsigned无符号,创建一个无符号的列 mysql> create table z...
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: