MySQL数据库远程连接配置

使用ifconfig命令查看服务器的IP地址如下：

[[e-mail] ~]# ifconfig
ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.1.11  netmask 255.255.255.0  broadcast 192.168.1.255
        inet6 fe80::24de:dca1:a1fb:f609  prefixlen 64  scopeid 0x20<link>
        ether 00:0c:29:3e:7b:d2  txqueuelen 1000  (Ethernet)
        RX packets 28577  bytes 8446017 (8.0 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 4881  bytes 470355 (459.3 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

把MySQL的3306端口加入防火墙（以CentOS7为例），命令如下：

[[e-mail] ~]# firewall-cmd --add-port 3306/tcp
success
[[e-mail] ~]# firewall-cmd --list-port
3306/tcp
[[e-mail] ~]# 

step1 使用root账号登录MySQL，创建一个管理员用户admin

mysql> grant all privileges on *.* to [e-mail] 
identified by '[e-mail]' with grant option;
Query OK, 0 rows affected, 1 warning (0.07 sec)

step2退出MySQL，然后使用admin账号登录MySQL

[[e-mail] ~]# mysql -uadmin -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.27-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

step3根据实际情况，添加远程连接用户并设置权限

--用户wang可以访问所有数据库，并具备所有操作权限，可以在任意主机上登录
grant all on *.* to 'wang'@'%' identified by '[e-mail]';  
--用户zhang可以访问所有数据库，并具备所有操作权限，但只能在和服务器同一个网络的主机上登录
grant all on *.* to 'zhang'@'192.168.1.%' identified by '[e-mail]';  
--用户liu可以访问hist数据库，具备访问hist数据库的所有操作权限，只能在和服务器同一个网络的主机上登录
grant all on *.* to 'liu'@'192.168.1.%' identified by '[e-mail]';  

mysql> flush privileges;  --刷新用户权限

step4打开客户机进行测试

测试客户机的IP和服务器在同一个网络，使用ifconfig查看结果如下：

[[e-mail] ~]# ifconfig
ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.1.12  netmask 255.255.255.0  broadcast 192.168.1.255
        inet6 fe80::5dc6:11dc:f5:715e  prefixlen 64  scopeid 0x20<link>
        ether 00:0c:29:0b:c0:71  txqueuelen 1000  (Ethernet)
        RX packets 1072  bytes 514197 (502.1 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 237  bytes 22685 (22.1 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

使用用户wang登录，其中参数-h指定登录的服务器地址，-p指定登录密码，-P指定登录的端口号。
登录命令如下：

[[e-mail] ~]# mysql -uwang -h192.168.1.11 -p -P3306;
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.27-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

OK！登录成功！

更换一台安装windows系统客户机，客户机的IP地址如下：


经过测试，使用Windows客户端也可以正常登陆！