1.修改登陆IP限制
update user set host = '%' where user ='root'; flush privileges;
2.增加用户
CREATE USER "test"@"%" IDENTIFIED BY "123456";
3.修改密码
update mysql.user set authentication_string = password(''), password_expired = 'N', password_last_changed = now() where user = 'root'; flush privileges;
4.创建数据库
create database test default charset utf8mb4 collate utf8mb4_general_ci;
5.为用户授予数据库权限
grant all privileges on test.* to "test"@"%" identified by "123456"; flush privileges;
6.去掉mysql的ONLY_FULL_GROUP_BY模式(该模式下join不必要的表会报错),首先查询出原来的模式
select @@global.sql_mode;
其查询结果为:STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY
使用以下语句去掉该模式
set @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; set sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';