MySQL root用户无法登录原因及解决办法

最新推荐文章于 2024-05-09 12:07:42 发布

qq_39306047

最新推荐文章于 2024-05-09 12:07:42 发布

阅读量1.3w

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/qq_39306047/article/details/116480973

版权

mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

可以看到root用户的允许host被我在安装时修改为‘192.168.137.%’，这直接导致mysql在我使用root用户在localhost上登录时禁止登录，解决的方式也很简单，直接修改root用户的Host字段为‘%’就可以解决这个问题。
可以看到root用户的允许host被我在安装时修改为‘192.168.137.%’，这直接导致mysql在我使用root用户在localhost上登录时禁止登录，解决的方式也很简单，直接修改root用户的Host字段为‘%’就可以解决这个问题。

UPDATE mysql.user set Host = ‘%‘ WHERE user = ‘root‘;

然后再次登录：

[[email protected] bin]#mysql -h127.0.0.1 -uroot -p

https://www.cnblogs.com/mabingxue/p/12753590.html

为MySQL添加root远程登录权限（3306实例的登录密码是“111”）：

https://blog.csdn.net/lws123253/article/details/82832453

https://m.toutiao.com/i6808523221378269699/?traffic_source=CS1114&in_ogs=1&utm_source=HW&source=search_tab&utm_medium=wap_search&prevent_activate=1&original_source=1&in_tfs=HW&channel=

https://blog.csdn.net/ckxq47181/article/details/100252516

https://www.cnblogs.comgeek/p/12200546.html

https://www.cnblogs.com/cnzeno/p/6347158.html
暗山
关注
mysql 无法登陆_MySQL root用户无法登录原因及解决办法原创
2021-01-18 18:36:02

暗山

码龄3年

关注
MySQL root密码正确，却怎么也bai无法du从本地登录MySQL登录提示ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: YES)可能原因是mysql库中bai的user表缺少一个root指向host：localhost的数据项，只有一个root指向host：主机名的数据项，故怎么也无法利用root账户登录MySQL。解决办法1、首先kill掉MySQL进程2、在启动mysql的参数中加入 --skip-grant-tables这时无密码就可以登录mysql了3、修复root账户丢失的数据项这里有两种解决方案：　　第一种是因为root账户初始的时候有3条记录，包含root对应localhost，hostname，127.0.0.1三条账户数据，我们可以update host为其他两项中一项为localhost即可。　　第二种是直接insert一条记录，host为localhost即可　　即使root的host包含了主机名，127.0.0.1那么依然是无法正常登录的，这里必须要有localhost的host才行。　　如果上面办法还是无法正常登录我们可尝试另一种办法在本地用mysql命令直接回车可以进入mysql，但是里面只有test和information_schema数据库，没有mysql等数据库，使用use mysql报如下错：mysql> use mysql　　ERROR 1044 (42000): Access denied for user "@‘localhost’ to database ‘mysql’　　意思是说没有指定user，没有权限访问数据库mysql。　　那么用root登录呢，输入正确的密码报如下错：　　[root@228827 ~]# mysql -uroot -p123456　　ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: YES)　　密码正确的情况下，mysql数据库已经禁止了root用户在本地的登录权限了技术交流欢迎加入Q群：177428068

MySQL root密码正确，却怎么也bai无法du从本地登录MySQL登录提示ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: YES)可能原因是mysql库中bai的user表缺少一个root指向host：localhost的数据项，只有一个root指向host：主机名的数据项，故怎么也无法利用root账户登录MySQL。

重装系统

sudo mkdir -p /var/run/mysqld
sudo chown mysql /var/run/mysqld/
sudo service mysql restart

sudo apt-get install mysql-server mysql-client

update-alternatives: using /etc/mysql/my.cnf.fallback to provide /etc/mysql/my.cnf (my.cnf) in auto mode
update-alternatives: using /etc/mysql/mysql.cnf to provide /etc/mysql/my.cnf (my.cnf) in auto mode
Renaming removed key_buffer and myisam-recover options (if present)
mysqld will log errors to /var/log/mysql/error.log
mysqld is running as pid 590
sudo chown mysql /var/run/mysqld/
sudo service mysql restart

yyp@DESKTOP-VDMG8PM:~$ sudo chown mysql /var/run/mysqld/
yyp@DESKTOP-VDMG8PM:~$ sudo service mysql restart

Stopping MySQL database server mysqld [ OK ]
Starting MySQL database server mysqld su: warning: cannot change directory to /nonexistent: No such file or directory
[ OK ]
su: warning: cannot change directory to /nonexistent: No such file or directory
yyp@DESKTOP-VDMG8PM:~$

yyp@DESKTOP-VDMG8PM:~$ sudo mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.23-0ubuntu0.20.04.1 (Ubuntu)

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> select host,user,plugin,authentication_string from user;
±----------±-----------------±----------------------±-----------------------------------------------------------------------+
| host | user | plugin | authentication_string |
±----------±-----------------±----------------------±-----------------------------------------------------------------------+
| localhost | debian-sys-maint | caching_sha2_password | $A $005$ RZ(.h~w"??{Wg,9grdPCvLS5UQ6/oM9rdoq8InJWROdKDhWXeKhftdXxfkH0 |
| localhost | mysql.infoschema | caching_sha2_password | $A $005$ THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session | caching_sha2_password | $A $005$ THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys | caching_sha2_password | $A $005$ THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | root | auth_socket | |
±----------±-----------------±----------------------±-----------------------------------------------------------------------+
5 rows in set (0.00 sec)

select host,user,plugin,authentication_string from user; #root用户的plugin为auth_socket，密码为空

ALTER user ‘root’@‘localhost’ IDENTIFIED BY ‘yyp’;
ALTER USER ‘root’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘yyp’;

mysql> ALTER USER ‘root’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘yyp’;
Query OK, 0 rows affected (0.23 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.08 sec)

mysql> select host,user,authentication_string from mysql.user;
±----------±-----------------±-----------------------------------------------------------------------+
| host | user | authentication_string |
±----------±-----------------±-----------------------------------------------------------------------+
| localhost | debian-sys-maint | $A $005$ RZ(.h~w"??{Wg,9grdPCvLS5UQ6/oM9rdoq8InJWROdKDhWXeKhftdXxfkH0 |
| localhost | mysql.infoschema | $A $005$ THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session | $A $005$ THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys | $A $005$ THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | root | *767055A12C62022ADBEEACCA973D3E807458F010 |
±----------±-----------------±-----------------------------------------------------------------------+
5 rows in set (0.00 sec)
在MySQL 8.04前，执行：SET PASSWORD=PASSWORD(’[新密码]’);但是MySQL8.0.4开始，这样默认是不行的。因为之前，MySQL的密码认证插件是“mysql_native_password”，而现在使用的是“caching_sha2_password”。

修改远程连接
mysql> update user set host=’%’ where user=‘root’;
Query OK, 1 row affected (0.26 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.06 sec)

mysql> select host,user,authentication_string from mysql.user;
±----------±-----------------±-----------------------------------------------------------------------+
| host | user | authentication_string |
±----------±-----------------±-----------------------------------------------------------------------+
| % | root | *767055A12C62022ADBEEACCA973D3E807458F010 |
| localhost | debian-sys-maint | $A $005$ RZ(.h~w"??{Wg,9grdPCvLS5UQ6/oM9rdoq8InJWROdKDhWXeKhftdXxfkH0 |
| localhost | mysql.infoschema | $A $005$ THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session | $A $005$ THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys | $A $005$ THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
±----------±-----------------±-----------------------------------------------------------------------+
5 rows in set (0.00 sec)

mysql>
yyp@DESKTOP-VDMG8PM:/etc/mysql/mysql.conf.d$ sudo vi mysqld.cnf
mysql> grant all privileges on . to root@’%’ with grant option;
Query OK, 0 rows affected (0.69 sec)

mysql> flush privileges; # 刷新权限
Query OK, 0 rows affected (0.11 sec)

yp@DESKTOP-VDMG8PM:/etc/mysql/mysql.conf.d$ sudo mysql
ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: NO)
yyp@DESKTOP-VDMG8PM:/etc/mysql/mysql.conf.d$ mysql
ERROR 1045 (28000): Access denied for user ‘yyp’@‘localhost’ (using password: NO)
yyp@DESKTOP-VDMG8PM:/etc/mysql/mysql.conf.d$ sudo mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.23-0ubuntu0.20.04.1 (Ubuntu)

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>

yyp@DESKTOP-VDMG8PM:/etc/mysql/mysql.conf.d$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.23-0ubuntu0.20.04.1 (Ubuntu)

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>

【只要是root的用户名字，任何ip和身份都可以连接成功，ssh的连接显示已经成功！！！！】

下面是一些失败尝试！

https://blog.csdn.net/weixin_34474896/article/details/113271711

sudo service mysql start
sudo netstat -ntlp | grep 3306

MySQL的端口不止3306，还有33060、33061、33062等等
https://blog.csdn.net/qq_35421305/article/details/115484662?utm_medium=distribute.pc_aggpage_search_result.none-task-blog-2_{aggregatepage}first_rank_v2~rank_aggregation-2-115484662.pc_agg_rank_aggregation&utm_term=33060%E7%AB%AF%E5%8F%A3+mysql&spm=1000.2123.3001.4430
二、关于MySQL X plugin 的开启和关闭

MySQL启动之后监听了两个端口3306和33060，是为MySQL5.7.12 之后新增了X plugin。

这个插件默认是启用的，可以在配置配置文件/etc/my.cnf 添加mysqlx=0关闭X plugin，也可以在启动时指定 --mysqlx=0 或 --skip-mysqlx选项来禁用X插件。

Port 3306 is the default port for the MySQL Protocol, which is used by the mysql client, MySQL Connectors, and utilities such as mysqldump and mysqlpump.

Port 33060 is the default port for the MySQL Database Extended Interface (the MySQL X Protocol).

The X Plugin can be disabled at startup by either setting --mysqlx=0 or --skip-mysqlx when starting the MySQL server.

Alternatively, use the -DWITH_MYSQLX=OFF CMake option to compile MySQL Server without X Plugin.
————————————————
版权声明：本文为CSDN博主「weixin_39621669」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_39621669/article/details/113306106
举个例子：
小明找工作，投了一个简历给某个公司
这时候，某公司就主动打电话了，告诉他，我们需要笔试，笔试的时间，网址，以及一些别的相关信息，规则等

小明接收到这个消息之后，到了那个时间他就会请求那个网址，并将自己的信息告诉他

这时候公司验证你的信息，验证成功后，你就可以开始笔试了

大家可以类比这理解一下，就能体会出，这是非常有必要的。
端口3306是mysql协议的默认端口，mysql客户机、mysql连接器以及mysqldump和mysqlpump等实用程序使用该端口。端口33060是MySQL数据库扩展接口(MySQLX协议)的默认端口。

(base) yyp@DESKTOP-VDMG8PM:~$ sudo service mysql start

Starting MySQL database server mysqld
su: warning: cannot change directory to /nonexistent: No such file or directory【问题一】

由于我们安装elasticsearch 过程或者安装mysql 过程中，安装的操作系统非纯净的系统，可能之前安装过相关服务，或者卸载了服务
一般正常情况新安装的系统不会出现以上情况.

vim /etc/passwd

nobody❌65534:65534:nobody:/nonexistent:/usr/sbin/nologin
mysql❌112:119:MySQL Server,:/nonexistent:/bin/false
tcpdump❌108:113::/nonexistent:/usr/sbin/nologin
_apt❌105:65534::/nonexistent:/usr/sbin/nologin
messagebus❌103:106::/nonexistent:/usr/sbin/nologin

sudo userdel -r mysql
sudo useradd -m mysql
https://blog.csdn.net/weixin_40436144/article/details/112186603?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522162031145716780261941764%2522%252C%2522scm%2522%253A%252220140713.130102334.pc%255Fall.%2522%257D&request_id=162031145716780261941764&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2_allfirst_rank_v2~rank_v29-1-112186603.first_rank_v2_pc_rank_v29&utm_term=mysql++cannot+change+directory+to+%2Fnonexistent

(base) yyp@DESKTOP-VDMG8PM:~$ sudo userdel -r mysql
userdel: mysql mail spool (/var/mail/mysql) not found
userdel: mysql home directory (/nonexistent) not found
(base) yyp@DESKTOP-VDMG8PM:~$
(base) yyp@DESKTOP-VDMG8PM:~$ sudo useradd -m mysql
(base) yyp@DESKTOP-VDMG8PM:~$ sudo vim /etc/passwd
mysql❌1001:1001::/home/mysql:/bin/sh

4、mysql在启动时没有指定配置文件时会使用/etc/my.cnf配置文件，请打开这个文件查看在[mysqld]节下有没有指定数据目录(datadir)。
解决方法：请在[mysqld]下设置这一行：datadir = /usr/local/mysql/data
https://blog.csdn.net/x17809211858/article/details/106954072?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522162031213416780261986401%2522%252C%2522scm%2522%253A%252220140713.130102334.pc%255Fall.%2522%257D&request_id=162031213416780261986401&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2_allfirst_rank_v2~rank_v29-21-106954072.first_rank_v2_pc_rank_v29&utm_term=mysql%3Ax%3A112%3A119%3AMySQL+Server%2C%2C%2C%3A%2Fnonexistent%3A%2Fbin%2Ffalse
https://www.jianshu.com/p/9a66c896967a

对mysql.sock来说，其作用是程序与mysqlserver处于同一台机器，发起本地连接时可用。
例如你无须定义连接host的具体IP地址，只要为空或localhost就可以。
在此种情况下，即使你改变mysql的外部port也是一样可能正常连接。

文章目录
【MySQL学习】MySQL在WSL2上的安装及配置
MySQL 安装
SSH 到 WSL
本地连接WSL的MySQL
MySQL 安装
在 WSL2 上边直接使用 apt-get install mysql 会出错。

安装方式是先安装mysql5.7版本然后更新。参考的别人来博客安装wsl2 安装 mysql 8.0 后启动失败

SSH 到 WSL
在本地SSH连接WSL2不能成功，需要在WSL2上边重新配置一下SSH的文件。参考的使用SSH工具连接WSL

本地连接WSL的MySQL
MySQL workbench 远程连接 WSL上的MySQL环境，这要在配置好WSL上边的MySQL环境和本地的mysql workbench 之后。

因为MySQL在安装成功后，默认的用户是root。使用root用户在本地mysql workbench上创建连接失败了。

参考了Mysql 新建用户和密码的初始化发现可以创建新的用户连接。
————————————————
版权声明：本文为CSDN博主「renlei2293」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_41918762/article/details/112402106

sudo sed -i ‘/Port /c Port 22’ /etc/ssh/sshd_config

(base) yyp@DESKTOP-VDMG8PM:/etc/mysql/mysql.conf.d$ cat mysqld.cnf

The MySQL database server configuration file.

One can use all long options that the program supports.

Run program with --help to get a list of available options and with

–print-defaults to see which it would actually understand and use.

For explanations see

http://dev.mysql.com/doc/mysql/en/server-system-variables.html

Here is entries for some specific programs

The following values assume you have at least 32M ram

[mysqld]

* Basic Settings

user = mysql

pid-file = /var/run/mysqld/mysqld.pid

socket = /var/run/mysqld/mysqld.sock

port = 3306

datadir = /var/lib/mysql

If MySQL is running as a replication slave, this should be

changed. Ref https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmpdir

tmpdir = /tmp

Instead of skip-networking the default is now to listen only on

localhost which is more compatible and is not less secure.

bind-address = 127.0.0.1

mysqlx-bind-address = 127.0.0.1

* Fine Tuning

key_buffer_size = 16M

max_allowed_packet = 64M

thread_stack = 256K

thread_cache_size = -1

This replaces the startup script and checks MyISAM tables if needed

the first time they are touched

myisam-recover-options = BACKUP

max_connections = 151

table_open_cache = 4000

* Logging and Replication

Both location gets rotated by the cronjob.

Log all queries

Be aware that this log type is a performance killer.

general_log_file = /var/log/mysql/query.log

general_log = 1

Error log - should be very few entries.

log_error = /var/log/mysql/error.log

Here you can see queries with especially long duration

slow_query_log = 1

slow_query_log_file = /var/log/mysql/mysql-slow.log

long_query_time = 2

log-queries-not-using-indexes

The following can be used as easy to replay backup logs or for replication.

note: if you are setting up a replication slave, see README.Debian about

other settings you may need to change.

server-id = 1

log_bin = /var/log/mysql/mysql-bin.log

binlog_expire_logs_seconds = 2592000

max_binlog_size = 100M

binlog_do_db = include_database_name

binlog_ignore_db = include_database_name

(base) yyp@DESKTOP-VDMG8PM:/etc/mysql/mysql.conf.d$

https://blog.csdn.net/sgmcumt/article/details/79168960?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522162031573616780269868399%2522%252C%2522scm%2522%253A%252220140713.130102334…%2522%257D&request_id=162031573616780269868399&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2_allbaidu_landing_v2~default-1-79168960.first_rank_v2_pc_rank_v29&utm_term=mysqlx-bind
mysql 8.0 远程无法连接填坑实录
https://blog.csdn.net/Dreamfine/article/details/113902800?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522162031573616780269878271%2522%252C%2522scm%2522%253A%252220140713.130102334.pc%255Fall.%2522%257D&request_id=162031573616780269878271&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2_allfirst_rank_v2~rank_v29-7-113902800.first_rank_v2_pc_rank_v29&utm_term=mysqlx-bind

bind-address = 0.0.0.0 #全部IP V4地址

mysqlx-bind-address = 0.0.0.0 #全部IP V4地址

Linux 系统中设置远程访问权限
退出 MySQL 的控制台，使用命令\q
回到 Linux 的 shell

用 vim 编辑 mysql.cnf

sudo vim /etc/mysql/mysql.conf.d/mysql.cnf

找到 bind-address = 127.0.0.1 这一行，将其用 # 注释掉

重启mysql

service mysql restart
127.0.0.1 表示 localhost，bind-address 表示绑定了 localhost，所以要去掉该行才能进行远程访问，当然如果你设置成0.0.0.0也是可以的。

云服务器中设置远程访问 MySQL权限
去腾讯云服务器的控制台，安全组放行 3306端口

Sequal Pro 远程连接 MySQL

卸载
https://blog.csdn.net/iehadoop/article/details/82961264?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522162031665216780274188535%2522%252C%2522scm%2522%253A%252220140713.130102334…%2522%257D&request_id=162031665216780274188535&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2_allsobaiduend~default-1-82961264.first_rank_v2_pc_rank_v29&utm_term=ubuntu%E5%8D%B8%E8%BD%BD+mysql
sudo apt-get remove mysql-common
246 sudo apt-get autoremove --purge mysql-server-8.0
247 dpkg --list|grep mysql
248 sudo apt-get remove mysql-common
249 dpkg --list|grep mysql
250 sudo apt-get autoremove --purge mysql-common
251 dpkg --list|grep mysql

05:安装mysql

sudo apt install -y mysql-server mysql-client

在安装完 MySQL 之后，应该运行一下包含的安全脚本：在 Ubuntu 上安装 MySQL
$ sudo mysql_secure_installation

Error: Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)
https://blog.csdn.net/qq_31811537/article/details/89226096?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522162031795916780271522997%2522%252C%2522scm%2522%253A%252220140713.130102334.pc%255Fall.%2522%257D&request_id=162031795916780271522997&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2_allfirst_rank_v2~rank_v29-1-89226096.first_rank_v2_pc_rank_v29&utm_term=Error%3A+Can%27t+connect+to+local+MySQL+server+through+socket+%27%2Fvar%2Frun%2Fmysqld%2Fmysqld.sock%27+%282%29

执行 vim /etc/mysql/my.cnf
将会看到内容如下：

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
你的mysql的配置在/etc/mysql/mysql.conf.d目录下的mysqld.cnf文件，打开如下：
可以看到:bind-address = 127.0.0.1 socket = /var/run/mysqld/mysqld.sock 等信息

sudo mkdir -p /var/run/mysqld
sudo chown mysql /var/run/mysqld/
sudo service mysql restart
(base) yyp@DESKTOP-VDMG8PM:~$ sudo service mysql restart

Stopping MySQL database server mysqld [ OK ]
Starting MySQL database server mysqld
[ OK ]

(base) yyp@DESKTOP-VDMG8PM:~$ sudo mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root:

VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?

Press y|Y for Yes, any other key for No:

更改用户认证方式

虽然上面设置了 root 用户的密码，但当通过 MySQL 终端登录时，并不能通过密码进行认证：
$ mysql -u root -p
Enter password:
ERROR 1698 (28000): Access denied for user ‘root’@‘localhost’

(base) yyp@DESKTOP-VDMG8PM:~$ sudo netstat -ntlp | grep 3306
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 9632/mysqld
tcp 0 0 127.0.0.1:33060 0.0.0.0:* LISTEN 9632/mysqld

到安装的MySQL的目录下，找my.ini文件；#vim /etc/my.cnf(注：windows下修改的是my.ini) /etc/mysql/mysql.conf.d/
在[mysqld]后添加skip-grant-tables（使用 set password for设置密码无效，且此后登录无需键入密码）

skip-grant-tables #在my.ini，[mysqld]下添加一行，使其登录时跳过权限检查

Here is entries for some specific programs

The following values assume you have at least 32M ram

[mysqld]
skip-grant-tables #

* Basic Settings

(base) yyp@DESKTOP-VDMG8PM:/etc/mysql/mysql.conf.d$ pwd
/etc/mysql/mysql.conf.d
(base) yyp@DESKTOP-VDMG8PM:/etc/mysql/mysql.conf.d$ sudo service mysql restart

Stopping MySQL database server mysqld [ OK ]
Starting MySQL database server mysqld

进入到终端当中，敲入 mysql -u root -p 命令然后回车，当需要输入密码时，直接按enter键，便可以不用密码登录到数据库当中

(base) yyp@DESKTOP-VDMG8PM:/etc/mysql/mysql.conf.d$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.23-0ubuntu0.20.04.1 (Ubuntu)

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> SELECT user, authentication_string, plugin, host FROM mysql.user;
±-----------------±-----------------------------------------------------------------------±----------------------±----------+
| user | authentication_string | plugin | host |
±-----------------±-----------------------------------------------------------------------±----------------------±----------+
| debian-sys-maint | $A $005$ Ffk.AsznAtW/:!!4alb1PUCju31FhkCtx.r3p/ZGGPoucjXuKHvnd6e4Wj4 | caching_sha2_password | localhost |
| mysql.infoschema | $A $005$ THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
| mysql.session | $A $005$ THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
| mysql.sys | $A $005$ THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
±-----------------±-----------------------------------------------------------------------±----------------------±----------+
4 rows in set (0.01 sec)

use mysql -A;
show tables;
select * from user;
select authentication_string from user;

https://blog.csdn.net/suruoxun/article/details/109732997
https://blog.csdn.net/zhouzhiwengang/article/details/102909298?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522161996588416780265434015%2522%252C%2522scm%2522%253A%252220140713.130102334…%2522%257D&request_id=161996588416780265434015&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2_allbaidu_landing_v2~default-1-102909298.first_rank_v2_pc_rank_v29&utm_term=ERROR+1410+%2842000%29%3A+You+are+not+allowed+to+create+a+user+with+GRANT

ALTER USER ‘root’@‘localhost’ IDENTIFIED BY ‘yyp’;

mysql>update user set authentication_string=passworD(“yyp”) where user=‘root’;
update user set authentication_string=password(‘yyp’) where user=‘root’;

下面直接演示正确修改root密码的步骤：

一、如果当前root用户authentication_string字段下有内容，先将其设置为空，否则直接进行二步骤。
https://www.cnblogs.com/fps2tao/p/9950989.html
use mysql;
update user set authentication_string=‘yyp’ where user=‘root’;

use mysql;
update user set authentication_string=‘yyp1’ where user=‘root’;

ALTER USER ‘root’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘’;

set password for ‘root’@’%’=password(‘yyp’);

SET PASSWORD FOR ‘root’@’%’ = PASSWORD(‘123456’);

mysql>

(base) yyp@DESKTOP-VDMG8PM:/etc/mysql/mysql.conf.d$ mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: YES)
(base) yyp@DESKTOP-VDMG8PM:/etc/mysql/mysql.conf.d$

(base) yyp@DESKTOP-VDMG8PM:/etc$ rm -rm mysql
rm: invalid option – ‘m’
Try ‘rm --help’ for more information.
(base) yyp@DESKTOP-VDMG8PM:/etc$ rm -rf mysql
rm: cannot remove ‘mysql/my.cnf.fallback’: Permission denied
rm: cannot remove ‘mysql/debian.cnf’: Permission denied
rm: cannot remove ‘mysql/conf.d/mysqldump.cnf’: Permission denied
rm: cannot remove ‘mysql/conf.d/mysql.cnf’: Permission denied
(base) yyp@DESKTOP-VDMG8PM:/etc$

(base) yyp@DESKTOP-VDMG8PM:/etc/mysql$ sudo cat debian.cnf

Automatically generated for Debian scripts. DO NOT TOUCH!

[client]
host = localhost
user = debian-sys-maint
password = t2GQh7kw7EanToRi
socket = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host = localhost
user = debian-sys-maint
password = t2GQh7kw7EanToRi
socket = /var/run/mysqld/mysqld.sock
(base) yyp@DESKTOP-VDMG8PM:/etc/mysql$

(base) yyp@DESKTOP-VDMG8PM:/etc/mysql$ cat my.cnf.fallback

The MySQL database server configuration file.

You can copy this to one of:

- “/etc/mysql/my.cnf” to set global options,

- “~/.my.cnf” to set user-specific options.

One can use all long options that the program supports.

Run program with --help to get a list of available options and with

–print-defaults to see which it would actually understand and use.

For explanations see

http://dev.mysql.com/doc/mysql/en/server-system-variables.html

This will be passed to all mysql clients

It has been reported that passwords should be enclosed with ticks/quotes

escpecially if they contain “#” chars…

Remember to edit /etc/mysql/debian.cnf when changing the socket location.

Here is entries for some specific programs

The following values assume you have at least 32M ram

!includedir /etc/mysql/conf.d/

https://www.jianshu.com/p/fdfde7f66651

重装系统

sudo mkdir -p /var/run/mysqld
sudo chown mysql /var/run/mysqld/
sudo service mysql restart

sudo apt-get install mysql-server mysql-client

yyp@DESKTOP-VDMG8PM:~$ sudo chown mysql /var/run/mysqld/
yyp@DESKTOP-VDMG8PM:~$ sudo service mysql restart

Stopping MySQL database server mysqld [ OK ]
Starting MySQL database server mysqld su: warning: cannot change directory to /nonexistent: No such file or directory
[ OK ]
su: warning: cannot change directory to /nonexistent: No such file or directory
yyp@DESKTOP-VDMG8PM:~$