如何在Ubuntu18.04上使用MySQL设置远程数据库以优化站点性能

介绍

随着您的应用程序或网站的增长，您可能会超出当前的服务器设置。如果您将 Web 服务器和数据库后端托管在同一台计算机上，最好将这两个功能分开，以便每个功能都可以在自己的硬件上运行并分担响应访问者请求的负载。

在本指南中，我们将介绍如何配置您的 Web 应用程序可以连接到的远程 MySQL 数据库服务器。我们将使用 WordPress 作为示例，以便有一些东西可以使用，但该技术广泛适用于任何由 MySQL 支持的应用程序。

先决条件

在开始本教程之前，您需要：

两台 Ubuntu 18.04 服务器。每个人都应该有一个具有 sudo 权限的非 root 用户并启用 UFW 防火墙，如我们的使用 Ubuntu 18.04 的初始服务器设置教程中所述。其中一台服务器将托管您的 MySQL 后端，在本指南中，我们将其称为 数据库服务器 。另一个将远程连接到您的数据库服务器并充当您的 Web 服务器；同样，在本指南中，我们将其称为 网络服务器 。
Nginx 和 PHP 在您的 Web 服务器 上安装了 。我们的教程 How To Install Linux, Nginx, MySQL, PHP (LEMP stack) in Ubuntu 18.04 将指导您完成整个过程，但请注意您应该跳过本教程的第 2 步，该教程的重点是安装 MySQL，因为您将在数据库服务器上安装 MySQL。
MySQL 在您的数据库服务器 上安装了 。按照 How To Install MySQL on Ubuntu 18.04 进行设置。
可选（但强烈推荐），来自 Let's Encrypt 的 TLS/SSL 证书在您的 Web 服务器 上安装 。您需要购买域名并为您的服务器设置 DNS 记录，但证书本身是免费的。我们的指南 How To Secure Nginx with Let's Encrypt on Ubuntu 18.04 将向您展示如何获取这些证书。

第 1 步 — 配置 MySQL 以侦听远程连接

在达到单机配置的性能上限后，将数据存储在单独的服务器上是一种优雅地扩展的好方法。它还提供了负载平衡所需的基本结构，并在以后进一步扩展您的基础架构。按照先决条件教程安装 MySQL 后，您需要更改一些配置值以允许来自其他计算机的连接。

MySQL服务器的大部分配置更改都可以在mysqld.cnf文件中进行，该文件默认存储在/etc/mysql/mysql.conf.d/目录下。在您的首选编辑器中使用 root 权限在您的 数据库服务器 上打开此文件。在这里，我们将使用 nano：

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

该文件分为由方括号中的标签表示的部分（[ 和 ]）。找到标记为 mysqld 的部分：

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

. . .
[mysqld]
. . .

在本节中，查找名为 bind-address 的参数。这告诉数据库软件在哪个网络地址上监听连接。

默认情况下，设置为 127.0.0.1，这意味着 MySQL 配置为仅查找本地连接。您需要更改它以引用可以访问您的服务器的外部 IP 地址。

如果您的两台服务器都位于具有专用网络功能的数据中心，请使用您的数据库服务器的专用网络 IP。否则，您可以使用其公共 IP 地址：

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

[mysqld]
. . .
bind-address = db_server_ip

由于您将通过 Internet 连接到数据库，因此建议您需要加密连接以确保数据安全。如果您不加密您的 MySQL 连接，网络上的任何人都可以嗅您的 Web 和数据库服务器之间的敏感信息。要加密 MySQL 连接，请在您刚刚更新的 bind-address 行之后添加以下行：

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

[mysqld]
. . .
require_secure_transport = on
. . .

完成后保存并关闭文件。如果您使用的是 nano，请按 CTRL+X、Y，然后按 ENTER。

要使 SSL 连接正常工作，您需要创建一些密钥和证书。 MySQL 带有一个可以自动设置这些的命令。运行以下命令，这将创建必要的文件。它还通过指定 mysql 用户的 UID 使 MySQL 服务器可以读取它们：

sudo mysql_ssl_rsa_setup --uid=mysql

要强制 MySQL 更新其配置并读取新的 SSL 信息，请重新启动数据库：

sudo systemctl restart mysql

要确认服务器现在正在侦听外部接口，请运行以下 netstat 命令：

sudo netstat -plunt | grep mysqld

Outputtcp        0      0 db_server_ip:3306     0.0.0.0:*               LISTEN      27328/mysqld

netstat 打印有关服务器网络系统的统计信息。这个输出向我们展示了一个名为 mysqld 的进程连接到端口 3306 上的 db_server_ip，标准 MySQL 端口，确认服务器正在侦听适当的接口。

接下来，在防火墙上打开该端口以允许流量通过：

sudo ufw allow mysql

这些是您需要对 MySQL 进行的所有配置更改。接下来，我们将介绍如何设置数据库和一些用户配置文件，您将使用其中一个来远程访问服务器。

第 2 步 — 设置 WordPress 数据库和远程凭证

尽管 MySQL 本身现在正在侦听外部 IP 地址，但当前没有配置启用远程的用户或数据库。让我们为 WordPress 创建一个数据库，以及一对可以访问它的用户。

首先以 root MySQL 用户身份连接到 MySQL：

sudo mysql

注意： 如果您启用了密码验证，如必备 MySQL 教程的 Step 3 中所述，您将需要使用以下命令来访问 MySQL shell：

mysql -u root -p

运行此命令后，您将被要求输入 MySQL root 密码，输入后，您将获得一个新的 mysql> 提示。

在 MySQL 提示符下，创建 WordPress 将使用的数据库。给该数据库起一个可识别的名称可能会有所帮助，以便您以后可以轻松识别它。在这里，我们将其命名为 wordpress：

CREATE DATABASE wordpress;

现在您已经创建了数据库，接下来需要创建一对用户。我们将创建一个仅限本地用户以及一个绑定到 Web 服务器 IP 地址的远程用户。

首先，创建您的本地用户 wpuser，并通过在声明中使用 localhost 使该帐户仅匹配本地连接尝试：

CREATE USER 'wpuser'@'localhost' IDENTIFIED BY 'password';

然后授予此帐户对 wordpress 数据库的完全访问权限：

GRANT ALL PRIVILEGES ON wordpress.* TO 'wpuser'@'localhost';

该用户现在可以对 WordPress 的数据库进行任何操作，但该帐户不能远程使用，因为它只匹配来自本地计算机的连接。考虑到这一点，请创建一个配套帐户，该帐户将专门匹配来自您的 Web 服务器的连接。为此，您需要 Web 服务器的 IP 地址。

请注意，您必须使用与您在 mysqld.cnf 文件中配置的相同网络的 IP 地址。这意味着如果您在 mysqld.cnf 文件中指定了私有网络 IP，则需要在以下两个命令中包含 Web 服务器的私有 IP。如果您将 MySQL 配置为使用公共 Internet，则应将其与 Web 服务器的公共 IP 地址相匹配。

CREATE USER 'remotewpuser'@'web_server_ip' IDENTIFIED BY 'password';

创建远程帐户后，为其授予与本地用户相同的权限：

GRANT ALL PRIVILEGES ON wordpress.* TO 'remotewpuser'@'web_server_ip';

最后，刷新权限，以便 MySQL 知道开始使用它们：

FLUSH PRIVILEGES;

然后键入以下命令退出 MySQL 提示符：

exit

现在您已经设置了一个新数据库和一个启用远程的用户，您可以继续测试您是否能够从您的 Web 服务器连接到该数据库。

第 3 步 — 测试远程和本地连接

在继续之前，最好验证您是否可以从本地计算机（您的数据库服务器）和您的 Web 服务器连接到您的数据库。

首先，通过尝试使用新帐户登录来测试来自 数据库服务器 的本地连接：

mysql -u wpuser -p

出现提示时，输入您为此帐户设置的密码。

如果您收到 MySQL 提示，则本地连接成功。您可以通过键入以下内容再次退出：

exit

接下来，登录到您的 网络服务器 以测试远程连接：

ssh sammy@web_server_ip

您需要在 Web 服务器上安装一些 MySQL 客户端工具才能访问远程数据库。首先，如果您最近没有这样做，请更新您的本地包缓存：

sudo apt update

然后安装 MySQL 客户端实用程序：

sudo apt install mysql-client

在此之后，使用以下语法连接到您的数据库服务器：

mysql -u remotewpuser -h db_server_ip -p

同样，您必须确保为数据库服务器使用正确的 IP 地址。如果您将 MySQL 配置为侦听专用网络，请输入数据库的专用网络 IP。否则，请输入您的数据库服务器的公共 IP 地址。

系统将要求您输入 remotewpuser 帐户的密码。输入后，如果一切正常，您将看到 MySQL 提示。使用以下命令验证连接是否使用 SSL：

status

如果连接确实使用 SSL，SSL: 行将指示这一点，如下所示：

Output--------------
mysql  Ver 14.14 Distrib 5.7.18, for Linux (x86_64) using  EditLine wrapper

Connection id:      52
Current database:
Current user:       remotewpuser@203.0.113.111
SSL:          Cipher in use is DHE-RSA-AES256-SHA
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     5.7.18-0ubuntu0.16.04.1 (Ubuntu)
Protocol version:   10
Connection:     203.0.113.111 via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:       3306
Uptime:         3 hours 43 min 40 sec

Threads: 1  Questions: 1858  Slow queries: 0  Opens: 276  Flush tables: 1  Open tables: 184  Queries per second avg: 0.138
--------------

验证可以远程连接后，继续并退出提示：

exit

这样，您已经验证了本地访问和来自 Web 服务器的访问，但您尚未验证其他连接将被拒绝。要进行额外检查，请尝试从您为其 not 配置特定用户帐户的第三台服务器执行相同操作，以确保该另一台服务器被 not 授予访问权限。

请注意，在运行以下命令尝试连接之前，您可能必须像上面一样安装 MySQL 客户端实用程序：

mysql -u wordpressuser -h db_server_ip -p

这应该不会成功完成，并且应该返回一个类似于此的错误：

OutputERROR 1130 (HY000): Host '203.0.113.12' is not allowed to connect to this MySQL server

这是意料之中的，因为您还没有创建允许从该服务器连接的 MySQL 用户，并且也是期望的，因为您希望确保您的数据库服务器将拒绝未经授权的用户访问您的 MySQL 服务器。

成功测试远程连接后，您可以继续在 Web 服务器上安装 WordPress。

第 4 步 — 安装 WordPress

为了演示新的远程 MySQL 服务器的功能，我们将完成在您的 Web 服务器上安装和配置 WordPress（流行的内容管理系统）的过程。这将要求您下载并解压软件，配置您的连接信息，然后运行 WordPress 的基于 Web 的安装。

在您的 网络服务器 上，将最新版本的 WordPress 下载到您的主目录：

cd ~
curl -O https://wordpress.org/latest.tar.gz

提取文件，这将在您的主目录中创建一个名为 wordpress 的目录：

tar xzvf latest.tar.gz

WordPress 包含一个示例配置文件，我们将使用它作为起点。制作此文件的副本，从文件名中删除 -sample，以便 WordPress 加载它：

cp ~/wordpress/wp-config-sample.php ~/wordpress/wp-config.php

当您打开文件时，您的首要任务将是调整一些密钥，为您的安装提供更高的安全性。 WordPress 为这些值提供了一个安全的生成器，因此您不必自己尝试提出好的值。这些仅在内部使用，因此在这里使用复杂、安全的值不会损害可用性。

要从 WordPress 密钥生成器中获取安全值，请键入：

curl -s https://api.wordpress.org/secret-key/1.1/salt/

这将在您的输出中打印一些键。您将暂时将这些添加到您的 wp-config.php 文件中：

警告！ 每次都请求自己的唯一值很重要。不要复制这里显示的值！

Outputdefine('AUTH_KEY',         'L4|2Yh(giOtMLHg3#] DO NOT COPY THESE VALUES %G00o|te^5YG@)');
define('SECURE_AUTH_KEY',  'DCs-k+MwB90/-E(=!/ DO NOT COPY THESE VALUES +WBzDq:7U[#Wn9');
define('LOGGED_IN_KEY',    '*0kP!|VS.K=;#fPMlO DO NOT COPY THESE VALUES +&[%8xF*,18c @');
define('NONCE_KEY',        'fmFPF?UJi&(j-{8=$- DO NOT COPY THESE VALUES CCZ?Q+_~1ZU~;G');
define('AUTH_SALT',        '@qA7f}2utTEFNdnbEa DO NOT COPY THESE VALUES t}Vw+8=K%20s=a');
define('SECURE_AUTH_SALT', '%BW6s+d:7K?-`C%zw4 DO NOT COPY THESE VALUES 70U}PO1ejW+7|8');
define('LOGGED_IN_SALT',   '-l>F:-dbcWof%4kKmj DO NOT COPY THESE VALUES 8Ypslin3~d|wLD');
define('NONCE_SALT',       '4J(<`4&&F (WiK9K#] DO NOT COPY THESE VALUES ^ZikS`es#Fo:V6');

将收到的输出复制到剪贴板，然后在文本编辑器中打开配置文件：

nano ~/wordpress/wp-config.php

找到包含这些设置的虚拟值的部分。它看起来像这样：

/wordpress/wp-config.php

. . .
define('AUTH_KEY',         'put your unique phrase here');
define('SECURE_AUTH_KEY',  'put your unique phrase here');
define('LOGGED_IN_KEY',    'put your unique phrase here');
define('NONCE_KEY',        'put your unique phrase here');
define('AUTH_SALT',        'put your unique phrase here');
define('SECURE_AUTH_SALT', 'put your unique phrase here');
define('LOGGED_IN_SALT',   'put your unique phrase here');
define('NONCE_SALT',       'put your unique phrase here');
. . .

删除这些行并粘贴您从命令行复制的值。

接下来，输入远程数据库的连接信息。这些配置行位于文件的顶部，就在您粘贴密钥的位置上方。请记住使用之前在远程数据库测试中使用的相同 IP 地址：

/wordpress/wp-config.php

. . .
/** The name of the database for WordPress */
define('DB_NAME', 'wordpress');

/** MySQL database username */
define('DB_USER', 'remotewpuser');

/** MySQL database password */
define('DB_PASSWORD', 'password');

/** MySQL hostname */
define('DB_HOST', 'db_server_ip');
. . .

最后，在文件中的任意位置添加以下行，告诉 WordPress 使用 SSL 连接到我们的 MySQL 数据库：

/wordpress/wp-config.php

define('MYSQL_CLIENT_FLAGS', MYSQLI_CLIENT_SSL);

保存并关闭文件。

接下来，将在 ~/wordpress 目录中找到的文件和目录复制到 Nginx 的文档根目录。请注意，此命令包含 -a 标志以确保所有现有权限都被继承：

sudo cp -a ~/wordpress/* /var/www/html

在此之后，唯一剩下要做的就是修改文件所有权。将文档根目录中所有文件的所有权更改为 www-data，Ubuntu 的默认 Web 服务器用户：

sudo chown -R www-data:www-data /var/www/html

这样，WordPress 就安装好了，您就可以开始运行其基于 Web 的设置程序了。

第 5 步 — 通过 Web 界面设置 Wordpress

WordPress 有一个基于 Web 的设置过程。当你浏览它时，它会问一些问题并在你的数据库中安装它需要的所有表。在这里，我们将介绍设置 WordPress 的初始步骤，您可以将其用作构建您自己的使用远程数据库后端的自定义网站的起点。

导航到与您的 Web 服务器关联的域名（或公共 IP 地址）：

http://example.com

您将看到 WordPress 安装程序的语言选择屏幕。选择适当的语言并点击进入主安装屏幕：

提交信息后，您需要使用刚刚创建的帐户登录 WordPress 管理界面。然后，您将被带到一个仪表板，您可以在其中自定义您的新 WordPress 网站。

结论

通过遵循本教程，您已经设置了一个 MySQL 数据库以接受来自远程 Wordpress 安装的受 SSL 保护的连接。本指南中使用的命令和技术适用于以任何编程语言编写的任何 Web 应用程序，但具体实现细节会有所不同。有关详细信息，请参阅您的应用程序或语言的数据库文档。