mysql 远程连接

注：数据库环境为linux

防火墙

• 查看防火墙状态：sudo ufw status
• 关闭防火墙：sudo ufw enable
• 打开防火墙： sudo ufw disable

查看数据库端口

• 登录数据库 mysql -uroot -p
• 查询端口命令 show global variables like 'port';

允许数据库端口进行数据传输

• 假设数据库端口为3306 iptables -I INPUT -s 0/0 -p tcp --dport 3306 -j ACCEPT
• 检查命令： iptables -L -n|grep 3306， 如果结果为： ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:3306,表示以允许

管理员赋权给远程用户

• 登录数据库 mysql -uroot -p
• 赋权： grant all privileges on *.* to 'newname'@'%' identified by 'password' with grant option; （newname表示远程用户名，password为密码 .赋予表示所有权利，’%’表示所有远程机器ip都可以访问）
• 刷新权限： flush privileges;
• 检查用户

  mysql> use mysql;
  Reading table information for completion of table and column names
  You can turn off this feature to get a quicker startup with -A
  
  Database changed
  mysql> select host, user from user;
  +-----------+------------------+
  | host      | user             |
  +-----------+------------------+
  | %         | ksir             |
  | %         | root             |
  | localhost | debian-sys-maint |
  | localhost | mysql.session    |
  | localhost | mysql.sys        |
  +-----------+------------------+
  5 rows in set (0.00 sec)

修改~/etc/mysql下的mysql配置文件my.cnf, 有些版本配置文件为mysql.conf.d/mysql.cnf，将bind-address = 127.0.0.1改成bind-address = 0.0.0.0

重启mysql服务service mysql restart

远程连接:

C:\Users\ksir>mysql -h 192.168.***.*** -uksir -p
Enter password: *******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.25-0ubuntu0.16.04.2 (Ubuntu)

Copyright (c) 2000, 2018, 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>

enjoy!