安装:
1.更新apt
sudo apt update
2.安装mysql
sudo apt install mysql-server
3.安全配置
sudo mysql secure_installation
问题解决:
1.Error:SET PASSWORD has no significance for user 'root'@'localhost'as.....
解决方式:先设置root的初始密码:
-
进入mysql:
sudo mysql
-
修改密码:
alter user 'root'@'localhost' identified with mysql_native_password by '123456';
2.进行安全配置时如果需要输入密码,但输入后显示:Access denied for user ‘root‘@‘localhost‘ (using password:YES)
-
首先在配置文件里查看debian-sys-maint的密码
-
sudo cat /etc/mysql/debian.cnf
-
-
然后使用该用户登录:
-
mysql -u debian-sys-maint -p
-
use mysql;
-
update user set plugin="mysql_native_password";
-
alter user 'root'@'localhost' identified with mysql_native_password by '123456';
-
-
flush privileges;
3.设置密码是显示: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)
得到这样的结果,密码长度要求8位,验证策略是MEDIUM,就是长度,数字,大小写,特殊字符都得验证,因此出现如此所示的错误,就很正常了。我们可以修改validate_password_policy=0,这样就是只检查长度。另外我们觉着8位太长了,我们可以改为4。这样可以设置root密码为root。
经过如下修改,再次验证。
mysql> set global validate_password_policy=0; //修改验证策略 Query OK, 0 rows affected (0.00 sec) mysql> set global validate_password_length=4; //修改密码长度 Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'validate_password%'; +--------------------------------------+-------+ | Variable_name | Value | +--------------------------------------+-------+ | validate_password_check_user_name | OFF | | validate_password_dictionary_file | | | validate_password_length | 4 | | validate_password_mixed_case_count | 1 | | validate_password_number_count | 1 | | validate_password_policy | LOW | | validate_password_special_char_count | 1 | +--------------------------------------+-------+ 7 rows in set (0.00 sec)
4.mysql 登录报错1449-The user specified as a definer(‘mysql.infoschema’@localhost’) does not exist
-
drop user
mysql.infoschema
@"localhost"; // 这个部分mysql.infoschema
@"localhost"就是连接数据库报错提示的用户与地址 -
flush privileges; //刷新权限
-
create user
mysql.infoschema
@"localhost" identified by 'h102'; //mysql.infoschema
@"localhost" 这个也要改成你报错的用户与地址 -
flush privileges;
到这里用户创建成功,登录会报错:ERROR 1356 (HY000): View ‘information_schema. SCHEMATA’…
-
use mysql;
-
update user set Select_priv = 'Y' where User = 'mysql.infoschema'; //这里注意用户名,也是你报错的用户名
-
flush privileges;
5.解决navicat远程连接失败问题:记得打开防火墙3306端口
grant all privileges on . to ‘root‘@‘%‘ identified by ‘root‘ with grant optio语句报错
mysql8.0以后以上语句失效,采用以下语句:
-
CREATE USER '用户名'@'host' IDENTIFIED BY '密码';
-
grant all privileges on . to '用户名'@'%' with grant option;
-
记得对权限进行更新
-
flush privileges;
-
查看相关用户:
-
select user,host from user;
若无上图所示:root-%,可使用修该方式:
-
update user set host-='%' where user='root';
若任然连接失败可尝试以下方法:
1.修改相关权限(选作)
-
alter user 'root'@'%' identified with mysql_native_password by '密码';
-
刷新权限
-
flush privileges;
-
2.尝试连接
-
telnet [ip地址] [端口号]
3.无法连接时,修改默认绑定(终极绝招)
-
进入 /etc/mysql/mysql.conf.d/mysqld.cnf
-
sudo gedit /etc/mysql/mysql.conf.d/mysqld.cnf
-
-
修改绑定ip(127.0.0.0)为 0.0.0.0
成功!!!