Mysql遇到的问题记录

登录时报错

命令登录入数据库时报错2002 (HY000): Can’t connect to local MySQL server through socket

[root@VM_5_17_centos ~]# mysql -u root
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

原因：mysql没启动，执行“systemctl start mysql”
启动后’/var/lib/mysql/mysql.sock’则存在了，如下所示

[root@VM_5_17_centos mysql]# pwd
/var/lib/mysql
[root@VM_5_17_centos mysql]# ls | grep .sock
mysql.sock
mysql.sock.lock

创建用户时报错

报错：ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
原因：主要是密码太简单了，修改下即可

mysql> create user 'admin'@'%' identified by '12345678';
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    | ON     |
| 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> create user 'admin'@'%' identified by '4adb%SDA'
    -> ;
Query OK, 0 rows affected (0.00 sec)

远程连接错误-mysql8为例

解决

// 登录
[root@iZuf6agiqhn4s48doletzuZ ~]# mysql -u root -p
Enter password: 
// 创建对象赋予权限
mysql> create user 'opt2'@'%' identified with mysql_native_password by '4rwe%SFC';
Query OK, 0 rows affected (0.01 sec)
mysql> grant all privileges on *.* to 'opt2'@'%' with grant option;
Query OK, 0 rows affected (0.01 sec)
// 对于已存在的用户，则需要修改host、加密方式、密码
mysql> update user set host= '%' where user = 'opt2';
alter user 'opt2'@'%' identified with mysql_native_password by '4rwe%SFC';
alter user 'admin1'@'%' identified with mysql_native_password by '4rfv%TGB';
alter user 'admin1'@'211.161.244.251' identified with mysql_native_password by '4rfv%TGB';
alter user 'admin1'@'211.161.244.251' identified with mysql_native_password by '4rfv%TGB';

报错类型

2059

mysql8 之前的版本中加密规则是mysql_native_password,而在mysql8之后,加密规则是caching_sha2_password,

mysql> select user,mmysql> select user,plugin from user;
| admin            | caching_sha2_password |

mysql> update user set plugin = 'mysql_native_password' where user = 'admin';

mysql> select user,plugin from user;
| admin            | mysql_native_password |

mysql> flush privileges;

1130

修改用户加密方式时报错-1396

问题：

mysql> alter user 'admin1'@'%' identified with mysql_native_password by 'v%T';
ERROR 1396 (HY000): Operation ALTER USER failed for 'admin1'@'%'

原因: mysql8加密方式为caching_sha2_password，被我用update语句修改为mysql_native_password，加密方式等的修改还是用alter！

解决1：把加密方式改回来，再进行修改操作

mysql> update user set plugin='caching_sha2_password' where user='admin1' and host='%';
mysql> flush privileges;
mysql> mysql> alter user 'admin1'@'%' identified with mysql_native_password by 'v%T';

解决2：删除用户后，重新创建用户

mysql> alter user 'admin1'@'%' identified with mysql_native_password by 'v%T';
ERROR 1396 (HY000): Operation ALTER USER failed for 'admin1'@'%'
// 删除用户
mysql> drop user 'admin1'@'%';
mysql> flush privileges;
// 重新创建用户
mysql> create user 'admin1'@'%' identified with mysql_native_password by '4rfv%TGB';
mysql> grant all privileges on *.* to 'admin1'@'%' with grant option;

1045 - Access denied for user…

解决：原因挺多，可能是权限问题，我这边是密码错误的原因

总结

• mysql8创建用户时，需要指定加密方式；

create user 'opt2'@'%' identified with mysql_native_password by '4rwe%SFC';
grant all privileges on *.* to 'opt2'@'%' with grant option;

• 修改操作用alter，直接update可能无法更新数据；
• 删除用户：drop user ‘admin1’@’%’;