ubuntu安装MySQL并解决远程及其他用户登录问题 修改密码问题

总结写在前面

一般情况照着这里三步执行 就实现所有用户都要用密码登录

sudo apt install mysql-server
update user set authentication_string=password("你的密码"),plugin='mysql_native_password' where user='root';
use mysql;
update user set host = '%' where user = 'root';
exit;
cd /etc/mysql/mysql.conf.d
sudo vim mysqld.cnf
# 注释掉里面的bind 127.0.0.1
service restart mysql

安装

一行命令就可以

sudo apt install mysql-server

问题

所有问题解决都需要先切换到系统root用户 或者sudo使用root用户权限

use mysql;
select user, plugin from user; 

在mysql中执行上面两行
如果root对应的 pluginauth_socket 就按照 问题三 解决
如果是 mysql_native_password 就按照 问题一二 解决

问题一

ruoke@ruoke:~$ mysql -u root -p
Enter password:
ERROR 1698 (28000): Access denied for user ‘root’@‘localhost’

解决:

use mysql;
#通常这一步会出现问题二错误
update user set authentication_string=password("你的密码") where user="root";
flush privileges;

问题二

密码策略问题异常信息:
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

解决:

#查看mysql密码策略
SHOW VARIABLES LIKE 'validate_password%';
#设置 validate_password_policy 密码验证强度 的全局参数为 LOW
set global validate_password_policy=LOW;
#设置密码长度 我默认使用 root 为密码 所以设置成4
set global validate_password_length=4;
#设置密码
update user set authentication_string=password("你的密码") where user="root";

bash记录:

root@ruoke:/etc/mysql# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.30-0ubuntu0.18.04.1 (Ubuntu)

Copyright (c) 2000, 2020, 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> update user set authentication_string=password("root~") where user="root";
ERROR 1046 (3D000): No database selected
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> update user set authentication_string=password("root") where user="root";
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    |
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.00 sec)

mysql> set global validate_password_policy=LOW;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_length=4;
Query OK, 0 rows affected (0.00 sec)

mysql> update user set authentication_string=password("root") where user="root";
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql>

问题三

update user set authentication_string=password("你的密码"),plugin='mysql_native_password' where user='root';

如果还是出错

ERROR 1819 (HY000): Your password does not satisfy the current policy require

就再次

SHOW VARIABLES LIKE 'validate_password%';

查看自己最短的密码是不是设置好了

再次执行问题二的解决办法就可以了

可以多试试

flush privileges;

没准是玄学问题

问题四

远程用户不允许链接
ERROR 1130 (HY000): Host ‘192.168.43.38’ is not allowed to connect to this MySQL server

解决:

use mysql;
update user set host = '%' where user = 'root';
exit;

然后

service mysql restart

就可以了


至此基本所有情况都能解决了 QWQ 有问题欢迎讨论

问题五

ERROR 2003 (HY000): Can‘t connect to MySQL server on ‘192.168.136.8‘ (10061)
问题原因:3306端口默认本地访问 要设置允许远程访问

如果你从没用碰过防火墙 那这跟防火墙必然没用关系

网上大部分都是 让修改my.cnf 注释bind 127.0.0.1
然后事实上my.cnf 这个文件干净的很

正确姿势是
/etc/mysql/mysql.conf.d
下的 mysqld.cnf
里面注释掉
bind 127.0.0.1

参考博客:
https://blog.csdn.net/ls0111/article/details/75113970
https://www.cnblogs.com/cpl9412290130/p/9583868.html

©️2020 CSDN 皮肤主题: 游动-白 设计师:上身试试 返回首页