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

介绍

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

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

先决条件

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

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

第1步 - 配置MySQL以监听远程连接

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

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

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

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

. . .
[mysqld]
. . .

在此部分中，查找名为bind-address的参数。 这告诉数据库软件监听连接的网络地址。

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

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

[mysqld]
. . .
bind-address = db_server_ip

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

[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 （标准MySQL端口）的db_server_ip ，确认服务器正在监听相应的接口。

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

sudo ufw allow mysql

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

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

即使MySQL本身正在监听外部IP地址，但目前还没有配置启用远程的用户或数据库。 让我们为WordPress和一对可以访问它的用户创建一个数据库。

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

sudo mysql

注意：如果启用了密码身份验证，如前提条件MySQL教程的第3步中所述 ，则需要使用以下命令来访问MySQL shell：

mysql -u root -p

运行此命令后，将询问您的MySQL root密码，输入后，您将收到一个新的mysql>提示符。

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

CREATE DATABASE wordpress;

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

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

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

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

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

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

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

CREATE USER 'wordpressuser'@'web-server_ip' IDENTIFIED BY 'password';

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

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

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

FLUSH PRIVILEGES;

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

exit

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

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

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

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

mysql -u wordpressuser -p

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

如果给出MySQL提示，则本地连接成功。 您可以输入以下命令再次退出：

exit

接下来，登录Web服务器以测试远程连接：

ssh sammy@web_server_ip

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

sudo apt update

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

sudo apt install mysql-client

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

mysql -u wordpressuser -h db_server_ip -p

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

系统将要求您输入wordpressuser帐户的密码。 输入后，如果一切正常，您将看到My​​SQL提示符。 使用以下命令验证连接是否正在使用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:       wordpressuser@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服务器的本地访问和访问，但您尚未验证其他连接将被拒绝。 要进行额外检查，请尝试从未配置特定用户帐户的第三台服务器执行相同操作，以确保未授予此其他服务器访问权限。

请注意，在运行以下命令以尝试连接之前，您可能必须像上面那样安装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的安装。

在您的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

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

. . .
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地址：

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

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

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

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

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

define('MYSQL_CLIENT_FLAGS', MYSQLI_CLIENT_SSL);

保存并关闭文件。

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

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

在此之后，剩下要做的就是修改文件所有权。 将文档根目录中所有文件的所有权更改为WW-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应用程序，但具体的实现细节将有所不同。 有关更多信息，请参阅您的应用程序或语言的数据库文档。