MySql8修改

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停止/重启命令