案例：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]#

案例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 ~]# 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)

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> 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]#

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

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>

若需要設定簡單 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> 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資料庫報ERROR 1045 (28000)報錯及MySQL忘記密碼找回

相關文章