登录MySQL
mysql -u root -p
添加新用户
允许本地 IP 访问 localhost, 127.0.0.1
create user 'test'@'localhost' identified by '123456';
允许外网 IP 访问
create user 'test'@'%' identified by '123456';
刷新授权
flush privileges;
为用户创建数据库
create database testdb DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
为新用户分配权限
授予用户通过外网IP对于该数据库的全部权限
grant all privileges on `testdb`.* to 'test'@'%' identified by '123456';
如果是MySql8.0，则用一下命令：
grant all privileges on `testdb`.* to 'test'@'%';
为新用户分配存储过程和函数、触发器创建修改执行权限
grant alter routine on `testdb`.* to 'test'@'%' identified by '123456';
grant create routine on `testdb`.* to 'test'@'%' identified by '123456';
grant execute on `testdb`.* to 'test'@'%' identified by '123456';
grant trigger on `testdb`.* to 'test'@'%' identified by '123456';
如果是MySql8.0，则用一下命令：
grant alter routine on `testdb`.* to 'test'@'%';
grant create routine on `testdb`.* to 'test'@'%';
grant execute on `testdb`.* to 'test'@'%';
grant trigger on `testdb`.* to 'test'@'%';
授予用户在本地服务器对该数据库的全部权限
grant all privileges on `testdb`.* to 'test'@'localhost' identified by '123456';
如果是MySql8.0，则用一下命令：
grant all privileges on `testdb`.* to 'test'@'localhost';
为新用户分配存储过程和函数、触发器创建修改执行权限
grant alter routine on `testdb`.* to 'test'@'localhost' identified by '123456';
grant create routine on `testdb`.* to 'test'@'localhost' identified by '123456';
grant execute on `testdb`.* to 'test'@'localhost' identified by '123456';
grant trigger on `testdb`.* to 'test'@'localhost' identified by '123456';
如果是MySql8.0，则用一下命令：
grant alter routine on `testdb`.* to 'test'@'localhost';
grant create routine on `testdb`.* to 'test'@'localhost';
grant execute on `testdb`.* to 'test'@'localhost';
grant trigger on `testdb`.* to 'test'@'localhost';
刷新权限
flush privileges;
退出 root 重新登录
exit
用新帐号 test 重新登录，由于使用的是 % 任意IP连接，所以需要指定外部访问IP
mysql -u test -h 115.28.203.224 -p
在Ubuntu服务器下，MySQL默认是只允许本地登录，因此需要修改配置文件将地址绑定给注释掉：
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address = 127.0.0.1
#注释掉这一行就可以远程登录了
不然会报如下错误：
ERROR 2003 (HY000): Can't connect to MySQL server on 'host' (111)
如果使用的mysql客户端登陆mysql8.0版本出现authentication plugin caching_sha2错误，则执行以下动作即可：
mysql 8.0 默认使用 caching_sha2_password 身份验证机制 —— 从原来的 mysql_native_password 更改为 caching_sha2_password。
从 5.7 升级 8.0 版本的不会改变现有用户的身份验证方法，但新用户会默认使用新的 caching_sha2_password 。
//本地支持
ALTER USER 'test'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
//远程支持
ALTER USER 'test'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
修改windows下的my.ini或者linux的my.cnf
获取 sql_mode字段内容： SELECT @@sql_mode;
查询结果：ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
去掉 sql_mode字段中的 ONLY_FULL_GROUP_BY
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
在mysqld均添加一下内容，然后重启mysql服务
若是无法插入数据（小于mysql 8.0版本）sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
若是无法插入数据（mysql 8.0版本以后）
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
