MySql8.0修改密码
MySql8.0及以上修改密码
// 修改密码
ALTER user 'root'@'localhost' IDENTIFIED BY 'password'
#MySQL连接数据库报错plugin caching_sha2_password could not be loaded
// 修改密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;
//修改加密规则
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
//更新一下用户的密码
FLUSH PRIVILEGES;
//刷新权限重置密码:
Alter user 'root'@'localhost' identified by 'xzx123456';
安 装:mysqld --install
初始化:mysqld --initialize
MySQL实现远程连接MySql数据库
mysql>use mysql;
mysql>select host, user from user;
+-----------+------------------+
| host | user |
+-----------+------------------+
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+------------------+
4 rows in set (0.00 sec)
mysql>update user set host = '%' where user = 'root';
mysql>flush privileges;
mysql>select host, user from user;
+-----------+------------------+
| host | user |
+-----------+------------------+
| % | root |
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
+-----------+------------------+
4 rows in set (0.00 sec)
查看Mysql Data位置
mysql>show variables like '%datadir%';
设置Mysql账户权限
查看所有用户:
mysql>select host, user from user;
查看用户权限
show grants for 'FTSTAMP'@'%';
移除用户所有权限
revoke all on *.* from 'FTSTAMP'@'%';
flush privileges;//将权限信息从内存中写入数据库
将权限赋给FTSTAMP用户
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, CREATE TEMPORARY TABLES, EXECUTE ON `ftstamp`.* TO 'FTSTAMP'@'%';
flush privileges;//将权限信息从内存中写入数据库
Gourp By不识别问题
查看mysql是否开启了ONLY_FULL_GROUP_BY
select @@global.sql_mode
如果返回值包含 ONLY_FULL_GROUP_BY,那么说明mysql开启了ONLY_FULL_GROUP_BY。
解决方法:
去掉 返回值中的ONLY_FULL_GROUP_BY,将剩下的返回值拷贝出来,打开mysql的ini配置文件,将拷贝下来的配置追加到[mysqld]后面
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
注意要用单引号括起来。
然后重启mysql服务
mysql停止/重启命令