如何远程连接 MySQL 数据库,阿里云腾讯云外网连接教程

A kitten
蒋川
B 端产品经理,卡拉云联合创始人
阅读时长 1 分钟

head

我们经常会碰到需要远程访问数据库的场景,比如使用 Sequel Pro 等本地工具远程连接,又或者,网站与数据库分离,服务器远程访问数据库等场景。默认情况下,MySQL 是不允许远程连接的,本教程将详细讲解如何开启 MySQL 数据库远程连接权限。

先决条件

  • 一台 Ubuntu 服务器(或 Linux 服务器)
  • 安装有 MySQL server
  • 本地和远程机器上有 root 权限

开启 MySQL 远程连接权限步骤

  1. 编辑 MySQL 配置文件
  2. 配置服务器内置防火墙
  3. 配置阿里云/腾讯云等安全组允许外网连接
  4. 多种方式远程连接 MySQL

1.编辑 MySQL 配置文件

在默认情况下,MySQL 数据库仅监听本地连接。如果想让外网远程连接到数据库,我们需要修改配置文件,让 MySQL 可以监听远程固定 ip 或者监听所有远程 ip。

首先打开 mysqld.cnf 配置文件。

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

找到 bind - address 这一行,如下图所示。

mysqld

默认情况下, bind - address 的值为 127.0.0.1 ,所以只能监听本地连接。我们需要将这个值改为远程连接 ip 可访问,可使用通配符 ip 地址 *::0.0.0.0 ,当然也可以是单独的固定 ip,这样就仅允许指定 ip 连接,更加安全。

mysqld-bind-address

提示:在某些 MySQL 版本的配置文件中,没有 bind - address 这一行,这种情况下,在合适的位置加上就可以了。

提示:在某些 MySQL 版本的配置文件中,没有 bind - address 这一行,这种情况下,在合适的位置加上就可以了。

更改后,保存并退出编辑器(使用 CTRL+X 保存并退出 nano 编辑器。)

然后重启 MySQL 服务,使刚刚编辑的 mysqld.cnf 文件生效:

sudo systemctl restart mysql

如果你想用现有账号来作为远程登录账号的话,那么需要重新配置这个账号,好让它有远程访问的权限。我们使用 root 账号来登录 mysql 进行设置。

sudo mysql

如果你开启了 root 密码验证,那么使用这个命令来登录

mysql -u root -p

将用做远程登录的账号 host 改为任意主机(%)或者是固定主机 ip 。可以使用 RENAME USER 命令来实现:

mysql> RENAME USER 'kalacloud'@'localhost' TO 'kalacloud'@'%';

rename-user

当然你也可以创建一个新账号专门用于远程登录,可以使用这个命令创建账号:

CREATE USER 'kalacloud-remote'@'%' IDENTIFIED BY 'password';

create-user

提示:有关caching_sha2_password 报错的问题。MySQL 8.0 默认认证方式改为 SHA2 了,如果不支持 SHA2 插件认证方式,那么就会报错,比如使用 Workbench 和 Squel Pro 登录时,如下图。

MySQL said: Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/lib/plugin/caching_sha2_password.so, 2): image not found

sequel-pro

解决办法

方法一:你可以创建一个使用密码作为认证方式的账号。

CREATE USER 'kalacloud-remote'@'%' IDENTIFIED WITH mysql_native_password BY 'password';

方法二:当然你也可以把现有使用 caching_sha2_plugin 认证的账号改为使用密码认证。

ALTER USER 'kalacloud-remote'@'%' IDENTIFIED WITH mysql_native_password BY 'password';

接着,根据自己的需要,给你用于远程访问的账号赋予权限。下面的例子是给账号全局权限,包括创建(CREATE)、修改(ALTER)、删除(DROP) 数据库、表、用户,任意表的插入(INSERT)、更新(UPDATE)、删除(DELETE)操作权限。可以使用 SELECT 查询数据,使用 REFERENCES 建立外键关系权限,以及使用 RELOAD 权限执行 FLUSH 操作的权限。当然,你也可以根据自己都需求,对账号权限进行调整。

GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'kalacloud-remote'@'%' WITH GRANT OPTION;

最后,运行 FLUSH PRIVILEGES 命令,刷新 MySQL 的系统权限相关表,更新缓存。

mysql> FLUSH PRIVILEGES;

全部完成,现在退出 MySQL:

mysql> exit

2.配置服务器内置防火墙

MySQL 默认端口为 3306 ,我们需要告诉防火墙,允许 3306 端口通讯。

如果你仅希望某一台服务器可远程访问数据库,则可以使用以下命令授权某一台(ip)服务器访问。

sudo ufw allow from remote_IP_address to any port 3306

当然,你也可以允许任意计算机远程访问数据库。

sudo ufw allow 3306

到这里,让 MySQL 允许远程连接在服务器的部分就全部设置完成了。但仍然有很多同学依然无法远程访问,卡在这里。这种情况,大多数是因为你没有配置云服务的防火墙。也就是说,除了服务器内置的防火墙外,还有云服务的防火墙需要被开启,接着我们讲讲阿里云 / 腾讯云配置 MySQL 远程连接的步骤 。

3.置配阿里云 / 腾讯云等云服务 MySQL 外网访问

如果你的数据库 / 服务器 host 在云服务中,那么开启 MySQL 外网访问的配置步骤前边都一样,只是在最后,我们还需要在云服务的安全组里,添加一条规则,允许 3306 端口连接。很多同学卡在这里了,一定要注意。

aliyun

阿里云服务器实例中的防火墙设置

腾讯云与阿里云类似,也是在安全组里设置。如果你使用的是腾讯云纯 MySQL 数据库,那么可以在实例详情中找到开启外网的选项。如果还无法连接,也可查阅 无法连接实例 的手册文档。

tencent-cloud

腾讯云 MySQL 开启外网连接方式

4.远程连接到 MySQL 数据库

(1)使用命令行远程访问 MySQL 数据库

~: mysql -u username -h mysql_server_ip -p

remote-mysql.jpg

使用命令行访问远程 MySQL 数据库

特别注意 :如果你的 MySQL 服务端口不是默认的 3306 ,那么指定端口登录,只要在命令后面增加 -P XXXX 即可。即将以上命令:mysql -u root -p 改为 mysql -u root -p -P [指定端口] 即可,注意指定端口的字母 P 为大写。

(2)使用 Sequel Pro 等本地软件远程连接 MySQL 数据库**

remote-mysql-sequel-pro.

使用 Sequel Pro 等数据库操作软件远程访问 MySQL 数据库。

Sequel Pro、Workbench 等本地软件并不支持caching_sha2_plugin 插件认证方式,你可使用本教程前文所述的方法,将远程登录账号改为密码认证方式或新建一个使用密码认证的新账号用于远程登录。

(3)使用卡拉云远程访问 MySQL 数据库**

卡拉云是新一代低代码开发工具,可轻松连接远程数据库的同时,你还可以针对自己的应用场景快速搭建 CRUD 操作工具。

kalacloud

一分钟快速搭建 CRUD 小工具,可分享给需要的同学一起使用

某些周期性的查询需求,你可以通过简单拖拽前端组件快速搭一个小工具,不用每次都敲一遍命令。卡拉云也支持多用户使用,你可以把做好的小工具,一键分享给需要的同学,一起使用。

kalacloud-sql-api

卡拉云支持快速接入常见的数据库类型及 API,几行代码即可快速构建针对自己应用场景的小工具

至此,我们已经可以远程访问 MySQL 数据库了。 如果你还有什么疑问,欢迎一起讨论。我的微信 HiJiangChuan。更多数据库相关教程可访问 卡拉云 查看。

卡拉云创始人蒋川的微信

有关 MySQL 教程,可继续拓展学习:

© 2021 卡拉云 Built with ❤️ in Beijing

京ICP备15049164号-9