1. 首页
  2. IT资讯

Mysql数据库报ERROR 1045 (28000)报错及MySQL忘记密码找回

案例:Mysql数据库报 ERROR 1045 (28000)报错;同时该方法适用于忘记MySQL Password找回;重新安装mysql手动清空mysqld.log日志文件导致初始 Password丢失 Password修改;

 

案例1:

[root@zrbapp02 lib]# 

[root@zrbapp02 lib]# mysql -uroot -pmysql

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@zrbapp02 lib]# 

[root@zrbapp02 lib]# 

案例2:

[root@zrbapp02 ~]# echo "" > /var/log/mysqld.log   —-清空mysqld.log日志文件后查不到

[root@zrbapp02 ~]# 

[root@zrbapp02 ~]# grep 'temporary password' /var/log/mysqld.log   –mysqld.log日志文件查不到初始 Password

[root@zrbapp02 ~]# 

[root@zrbapp02 ~]# 

[root@zrbapp02 ~]# systemctl restart mysqld

[root@zrbapp02 ~]# 

解决方法:

1、修改/etc/my.cnf文件中[mysqld]段增加 skip-grant-tables跳过认证

[root@zrbapp02 lib]# vi /etc/my.cnf


#socket=/mysqldb/mysql/mysql.sock

# For advice on how to change settings please see

# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html


[mysqld]

skip-grant-tables

#

# 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

datadir=/mysqldb/mysql

socket=/var/lib/mysql/mysql.sock

#socket=/mysqldb/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

wq!  —-保存并退出

2、重启mysql数据库

[root@zrbapp02 lib]# systemctl restart mysqld

[root@zrbapp02 lib]# 


3、免密登录数据库并修改 Password

[root@zrbapp02 lib]# 

[root@zrbapp02 lib]# mysql 

Welcome to the MySQL monitor.  Commands end with ; or g.

Your MySQL connection id is 2

Server version: 5.7.25 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> show databases;

+——————–+

| Database           |

+——————–+

| information_schema |

| mysql              |

| performance_schema |

| sys                |

+——————–+

4 rows in set (0.24 sec)


mysql> 

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> 

mysql> 

mysql> update mysql.user set authentication_string=password('mysql') where user='root';

Query OK, 1 row affected, 1 warning (0.14 sec)

Rows matched: 1  Changed: 1  Warnings: 1


mysql> 

mysql>  flush privileges ;

Query OK, 0 rows affected (0.00 sec)


mysql> 

mysql> exit

Bye

[root@zrbapp02 lib]# 

[root@zrbapp02 lib]# 

4、 注释掉 skip-grant-tables

[root@zrbapp02 lib]# vi /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]

#skip-grant-tables

#

# 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

datadir=/mysqldb/mysql

socket=/var/lib/mysql/mysql.sock

#socket=/mysqldb/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

wq!   —-保存并退!

5、重启mysql数据库

[root@zrbapp02 lib]#  systemctl restart mysqld

[root@zrbapp02 lib]# 


6、使用新 Password登录数据库

[root@zrbapp01 ~]# 

[root@zrbapp01 ~]# mysql -uroot -pmysql

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.25


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> show databases;   –查看数据库时要求使用ALTER USER  rest  Password

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

mysql> 

mysql> alter user'root'@'localhost' identified by'mysql';   —-重置 Password 提示 Password规则问题,不能使用简单 Password

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

mysql> 

mysql> 


若需要设置简单 Password则按以下设置 Password规则即可;

mysql> set global validate_password_policy=0;

Query OK, 0 rows affected (0.00 sec)


mysql> set global validate_password_mixed_case_count=0;

Query OK, 0 rows affected (0.00 sec)


mysql> set global validate_password_number_count=3;

Query OK, 0 rows affected (0.00 sec)


mysql> set global validate_password_special_char_count=0;

Query OK, 0 rows affected (0.01 sec)


mysql> set global validate_password_length=3;

Query OK, 0 rows affected (0.00 sec)


mysql> alter user'root'@'localhost' identified by'mysql';

Query OK, 0 rows affected (0.00 sec)


mysql> 

mysql> 

mysql> show databases;

+——————–+

| Database           |

+——————–+

| information_schema |

| mysql              |

| performance_schema |

| sys                |

+——————–+

4 rows in set (0.00 sec)


mysql> 

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> 

mysql> 


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31520497/viewspace-2662225/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:℅傍ㄖ免沦陷dε鬼,转转请注明出处:http://www.cxybcw.com/190688.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code