mysql5.7.初始化后,临时密码过期
安装mysql 5.7遇到一个特别纠结的问题,初始化成功之后,使用临时密码提示过期。反复初始化n次,还是临时密码过期。脑袋很大。下面贴出代码
[root@oracle11g data]# mysqld --initialize --user=mysql [root@oracle11g data]# service mysqld start Starting MySQL.. SUCCESS! [root@oracle11g data]# cat error.log 2020-01-14T10:07:56.315349+08:00 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more detail s). 100 100 100 100 2020-01-14T10:08:11.771346+08:00 0 [Warning] InnoDB: New log files created, LSN=45790 2020-01-14T10:08:11.883164+08:00 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2020-01-14T10:08:11.960205+08:00 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: b76a7b2a-3672- 11ea-9a85-080027651e78.2020-01-14T10:08:11.962281+08:00 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2020-01-14T10:08:11.965106+08:00 1 [Note] A temporary password is generated for root@localhost: Ei5PJ#K./fn-
登录mysql使用临时密码,这个过程我疑惑了好久,明明是刚初始化成功的,立马使用临时密码进行登录也不可以,提示密码过期
[root@oracle11g data]# mysql -uroot -p"Ei5PJ#K./fn-" ERROR 1862 (HY000): Your password has expired. To log in you must change it using a client that supports expired passwords. [root@oracle11g data]#
采用跳过密码验证,编辑/etc/my.cnf文件
[mysqld]
skip-grant-tables
重启mysql,使用免密码登录数据库,修改密码的时候提示mysql服务使用--skip-grant-tables,不能执行修改密码操作
[root@oracle11g data]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.25-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
root@localhost [(none)]>alter user 'root'@'localhost' identified by '123456';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
root@localhost [(none)]>
修改系统表,临时密码不过期
root@localhost [(none)]>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 root@localhost [mysql]>select * from user where user='root'\G; *************************** 1. row *************************** Host: localhost User: root Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y Process_priv: Y File_priv: Y Grant_priv: Y References_priv: Y Index_priv: Y Alter_priv: Y Show_db_priv: Y Super_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y Execute_priv: Y Repl_slave_priv: Y Repl_client_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Create_user_priv: Y Event_priv: Y Trigger_priv: Y Create_tablespace_priv: Y ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: mysql_native_password authentication_string: *7C9CB2A4F227FF764C10705941B03E2B1C378AF1 password_expired: Y password_last_changed: 2020-01-14 10:08:12 password_lifetime: NULL account_locked: N 1 row in set (0.00 sec) ERROR: No query specified root@localhost [mysql]>update user set password_expired='N' where user='root'; Query OK, 1 row affected (0.09 sec) Rows matched: 1 Changed: 1 Warnings: 0 root@localhost [mysql]>select * from user where user='root'\G; *************************** 1. row *************************** Host: localhost User: root Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y Process_priv: Y File_priv: Y Grant_priv: Y References_priv: Y Index_priv: Y Alter_priv: Y Show_db_priv: Y Super_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y Execute_priv: Y Repl_slave_priv: Y Repl_client_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Create_user_priv: Y Event_priv: Y Trigger_priv: Y Create_tablespace_priv: Y ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: mysql_native_password authentication_string: *7C9CB2A4F227FF764C10705941B03E2B1C378AF1 password_expired: N password_last_changed: 2020-01-14 10:08:12 password_lifetime: NULL account_locked: N 1 row in set (0.00 sec) ERROR: No query specified
修改/etc/my.cnf,删除--skip-grant-tables,重新启动mysql
[root@oracle11g data]# service mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL.. SUCCESS! [root@oracle11g data]# [root@oracle11g data]# mysql -uroot -p"Ei5PJ#K./fn-" Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.25-log MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. root@localhost [(none)]>
修改mysql root密码
root@localhost [(none)]>alter user 'root'@'localhost' identified by '123456'; Query OK, 0 rows affected (0.01 sec) root@localhost [(none)]>flush privileges; Query OK, 0 rows affected (0.10 sec)
退出登录,使用新密码进行登录
[root@oracle11g data]# mysql -uroot -p"123456" Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.25-log MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. root@localhost [(none)]>