mysql8安装后初始化密码

环境:

Ubuntu19.10

Mysql版本：$ dpkg --list|grep mysql
ii  mysql-client                               8.0.18-0ubuntu0.19.10.1                all          MySQL database client (metapackage depending on the latest version)
ii  mysql-client-8.0                           8.0.18-0ubuntu0.19.10.1                amd64        MySQL database client binaries
ii  mysql-client-core-8.0                      8.0.18-0ubuntu0.19.10.1                amd64        MySQL database core client binaries
ii  mysql-common                               5.8+1.0.5ubuntu2                       all          MySQL database common files, e.g. /etc/mysql/my.cnf
ii  mysql-server                               8.0.18-0ubuntu0.19.10.1                all          MySQL database server (metapackage depending on the latest version)
ii  mysql-server-8.0                           8.0.18-0ubuntu0.19.10.1                amd64        MySQL database server binaries and system database setup
ii  mysql-server-core-8.0                      8.0.18-0ubuntu0.19.10.1                amd64        MySQL database server binaries
 

＃－－－－－－－－－1045 (28000)问题有以下两种情况－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

ERROR 1045 (28000): Access denied for user 'ODBC'@'localhost' (using password: NO)

ERROR 1045 (28000): Access denied for user 'ODBC'@'localhost' (using password: YES)-----这次是这个问题

#############################下面的记录是失败的（只适用于mysql5.7）############################

1.

Root权限下面:

subl /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]

下面加入

skip-grant-tables

2.

service mysql restart

3.

新开一个终端，输入mysql即可进入mysql

＃－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

旧版本的mysql(5.X)，因为我一开始误以为mysql5.x和mysql8.x是一致的:

  "mysql>update mysql.user set password=PASSWORD('新密码') where User='root'; "

下面是修改plugin(大多数情况下不需要这一步，如果修改root权限后无效则需要这一步进行确认):

mysql> use mysql;
mysql> select plugin from user where user = 'root';
+-----------------------+
| plugin                |
+-----------------------+
| mysql_native_password |
+-----------------------+
1 row in set (0.00 sec)

mysql> update user set plugin='mysql_native_password';

mysql> select plugin from user where user = "root";

然后

mysql> FLUSH PRIVILEGES;

查看修改过得密码:

mysql> select User,Host, authentication_string from user
    -> ;
+------------------+-----------+------------------------------------------------------------------------+
| User             | Host      | authentication_string                                                  |
+------------------+-----------+------------------------------------------------------------------------+
| root             | %         | appleyuchi                                                             |

确保上述表格中没有空用户,若有，则先按照[2]操作，然后回到本文

mysql>quit

＃－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

subl /etc/mysql/mysql.conf.d/mysqld.cnf

删除skip-grant-tables

在终端输入下面的命令刷新:

service mysql restart

然后按照[1]链接操作

就可以一键登录mysql了

###############下面是成功的完整记录（适用于mysql8,来自[3]， 但是与[3]略有不同）##########################

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
chmod -R 777 /var/lib/mysql
否则会出现：
 [Warning] [MY-012573] [InnoDB] './#innodb_temp/temp_1.ibt' permission error, can't delete!
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

# gedit /etc/mysql/mysql.conf.d/mysqld.cnf 

[mysqld]下面加入skip-grant-tables保存.
service mysql restart
systemctl restart mysql
mysql(启动)
mysql> use mysql;
 

mysql> select user,host from user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| debian-sys-maint | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)

mysql> flush privileges;

mysql> CREATE USER 'appleyuchi'@'%' IDENTIFIED with mysql_native_password  BY 'appleyuchi';

mysql> SHOW VARIABLES LIKE 'validate_password%'; 
mysql>  flush privileges; 

mysql> GRANT ALL PRIVILEGES ON *.* TO 'appleyuchi'@'%' WITH GRANT OPTION;

mysql> flush privileges;

# gedit /etc/mysql/mysql.conf.d/mysqld.cnf 

[mysqld]下面用#注释掉skip-grant-tables保存.

#service mysql restart

###################################################################

最终效果如下:

$ mysql -uappleyuchi -p
Enter password: (这里输入的是appleyuchi)
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.18-0ubuntu0.19.10.1 (Ubuntu)

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> 

－－－－－－－－－－－－一键登录设置－－－－－－－－－－－－－－－－－－－－－－－－－

/usr/bin/mysqlz.sh

chmod u+x /usr/bin/mysqlz.sh

chmod 777 /usr/bin/mysqlz.sh

#!/usr/bin/expect  -f     
spawn mysql -u appleyuchi -h localhost -p
expect "Enter password: "
send "appleyuchi\r\n"
interact 

然后输入mysqlz即可顺利登录

Reference:

[1]expect一键登录mysql_微电子学与固体电子学-俞驰的博客-CSDN博客

[2]mysql登录报错提示：ERROR 1045 (28000)的解决方法_慕课手记

[3]MySQL8 解决ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)_在奋斗的大道的博客-CSDN博客