01.修改登陆IP限制

UPDATE USER SET host = '%'  WHERE USER ='root';
FLUSH PRIVILEGES;

02.增加用户

CREATE USER "test"@"%" IDENTIFIED BY "123456";

03.修改密码

mysql5.6及以下版本

SET PASSWORD FOR ‘username’@‘host’ = PASSWORD(‘new_password’);

mysql5.7及以上版本

ALTER USER ‘username’@‘host’IDENTIFIED BY ‘new_password’;

修改密码后，需要刷新权限才能使更改生效

FLUSH PRIVILEGES;

04.创建数据库

CREATE DATABASE test DEFAULT charset utf8mb4 COLLATE utf8mb4_general_ci;

05.为用户授予数据库权限

授予指定数据库的全部权限

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'host';

授予指定数据库，指定表的指定权限

GRANT SELECT, INSERT, UPDATE ON database_name.table_name TO 'username'@'host';

授予所有数据库的权限

GRANT ALL PRIVILEGES ON *.* TO 'username'@'host';

刷新权限使更改生效

FLUSH PRIVILEGES;

06.去掉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';