mysql远程登录

MySQL5.7授权用户远程访问

做个记录，每次弄环境的时候，特别是弄mysql环境，时不时都要用到下面的命令

命令如下:

grant all privileges on *.* to 'root'@'%' identified by 'oa123456' with grant option;
flush privileges;
quit;  

注意:
上面的命令原型如下:

grant all privileges on *.* to 'username'@'%' identified by 'password' with grant option;  

命令中的“%”相当于授权任意主机。

另外还有就是通常授权用户远程连接，还需要修改配置文件，以Ubuntu16.04为例，需要修改mysqld.cnf配置文件，将里面的bind=127.0.0.1注释掉即可

文件位置：/etc/mysql/mysql.conf.d/mysqld.cnf
然后重启一下mysql服务，这时你就可以通过sqlyong或navicat连接mysql服务。

 mysql cmd常用命令：

# 创建cacti数据库
1. CREATE DATABASE cacti DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
# 创建用户
1.1. CREATE USER 'username'@'host' IDENTIFIED BY 'password';
例子：
{
CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456';
CREATE USER 'pig'@'192.168.1.101' IDENTIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '';
CREATE USER 'pig'@'%';
       }
# 授权用户对数据库的权限
2. GRANT ALL PRIVILEGES ON cacti.* TO 'cacti'@'localhost' IDENTIFIED BY 'cacti';
例子： 授权test用户拥有所有数据库的某些权限：
2.1. grant select,delete,update,create,drop on *.* to 'test'@'%' identified by 'test123';
# 授权对数据库的操作权限
2.1 GRANT privileges ON databasename.tablename TO 'username'@'host';
例子：
{
privileges：用户的操作权限，如SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL
databasename：数据库名
tablename：表名，如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如*.*
GRANT SELECT, INSERT ON test.user TO 'pig'@'%';
GRANT ALL ON *.* TO 'pig'@'%';
GRANT ALL ON maindataplus.* TO 'pig'@'%'; 
注意:
用以上命令授权的用户不能给其它用户授权，如果想让该用户可以授权，用以下命令:
GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;
     }
# 撤销用户权限
REVOKE privilege ON databasename.tablename FROM 'username'@'host';
例子：REVOKE SELECT ON *.* FROM 'pig'@'%';
# 删除用户
DROP USER 'username'@'host';
# 数据库时区设置
3. GRANT SELECT ON mysql.time_zone_name TO cacti@localhost;
# 更改数据库编码
4. ALTER DATABASE cacti CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# 写入my.cnf配置文件
set global collation_server=utf8mb4_unicode_ci;
set global character_set_client=utf8mb4;
set global max_allowed_packet=17700000;
set global join_buffer_size=20971520;
set global innodb_file_per_table=ON;
set global innodb_file_format=Barracuda;
set global innodb_large_prefix=ON;
set global innodb_flush_log_at_trx_commit=2;
FLUSH PRIVILEGES;
exit;
# 设置和更改密码
5. SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');
如果是当前登陆用户用:
SET PASSWORD = PASSWORD("newpassword");
例子: SET PASSWORD FOR 'pig'@'%' = PASSWORD("123456");
# 创建远程登录用户
grant all privileges on *.* to 'username'@'%' identified by 'password' with grant option;
# 刷新
FLUSH PRIVILEGES;

参考：https://www.cnblogs.com/youcong/p/10887068.html