ubuntu mysql access denied,Ubuntu安装/配置MySQL及root用户access denied报错解决

安装MySQL

sudo apt-get upgrade

sudo apt-get install mysql-server

sudo apt-get install mysql-client

运行截图

配置

a. 初始化配置

sudo mysql_secure_installation

Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

# 询问是否启用测试密码安全的插件

# 启用此插件后若密码过于简单则无法设置

VALIDATE PASSWORD PLUGIN 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 plugin?

Press y|Y for Yes, any other key for No: # (是/否)

Please set the password for root here.

# 设置密码

New password: #

Re-enter new password:

By default, a MySQL installation has an anonymous user, allowing anyone to log into MySQL without having to have a user account created for them.

This is intended only for testing, and to make the installation go a bit smoother.

You should remove them before moving into a production environment.

# 询问是否删除匿名用户

Remove anonymous users? (Press y|Y for Yes, any other key for No) : # (是/否)

... skipping.

Normally, root should only be allowed to connect from 'localhost'.

This ensures that someone cannot guess at the root password from the network.

# 通常root用户只能从本地访问数据库以增加安全

# 询问是否可以远程登陆root用户

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : # (是/否)

Success.

By default, MySQL comes with a database named 'test' that anyone can access.

This is also intended only for testing, and should be removed before moving into a production environment.

# MySQL默认创建test数据库

# 询问是否删除

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : # (是/否)

... skipping.

Reloading the privilege tables will ensure that all changes made so far will take effect immediately.

# 重新加载特权表可以保证更改生效

# 询问是否现在重载

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : # (是/否)

Success.

All done!

b. 检查MySql服务状态

systemctl status mysql.service

MySQL服务正在运行

c. (尝试连接数据库时报错)

mysql -u root -p

错误信息: ERROR 1698 (28000): Access denied for user 'root'@'localhost'

Workbench也无法连接

我的解决方案:

i. 检查3306端口占用

端口情况

端口✔

ii. 管理员登陆MySQL

sudo mysql -u root

mysql> GRANT ALL PRIVILEGES ON *.* to 'root'@'localhost';

mysql> flush privileges;

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '';

mysql> exit

TIP: MySQL版本5.7之后在mysql.user中用authentication_string代替了password

iii. 刷新MySQL服务

sudo service mysql restart

iv. 再次连接, 成功

Terminal

Workbench