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

介绍

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

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

先决条件

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

两台 Ubuntu 16.04 服务器，具有非 root 启用 sudo 的用户，并启用了 UFW 防火墙，如使用 Ubuntu 16.04 的初始服务器设置中所述。
在其中一台服务器上，您需要安装 LEMP（Linux、Nginx、MySQL、PHP）堆栈。我们的教程如何在 Ubuntu 16.04 中安装 Linux、Nginx、MySQL、PHP（LEMP 堆栈）将指导您完成整个过程。您应该跳过关于安装 MySQL 的第 2 步。我们将在本教程中安装 MySQL。
您可以选择（但强烈推荐）使用 SSL 证书保护您的 LEMP Web 服务器。您需要一个域名，但证书是免费的。我们的指南 How To Secure Nginx with Let's Encrypt on Ubuntu 16.04 将向您展示如何操作。

第 1 步 — 在数据库服务器上安装 MySQL

当我们达到单机配置的性能上限时，将数据存储在单独的服务器上是一种优雅扩展的好方法。它还提供了负载平衡所需的基本结构，并在以后进一步扩展我们的基础架构。

首先，我们将在没有安装 LEMP 堆栈的服务器上安装 MySQL。登录到这个服务器，然后更新你的包缓存并安装 MySQL 服务器软件：

sudo apt-get update
sudo apt-get install mysql-server

在安装过程中，您将被要求设置并确认 MySQL 的 root 密码。选择一个强密码并记下它，因为我们稍后会需要它。

MySQL 现在应该已安装并运行。让我们使用 systemctl 进行检查：

systemctl status mysql

Output● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: active (running) since Tue 2017-05-23 14:54:04 UTC; 12s ago
 Main PID: 27179 (mysqld)
   CGroup: /system.slice/mysql.service
           └─27179 /usr/sbin/mysqld

Active: active (running) 行表示 MySQL 已安装并正在运行。现在我们将使安装更加安全。 MySQL 带有一个脚本，可以引导您锁定系统：

mysql_secure_installation

这将要求您输入我们刚刚设置的 MySQL root 密码。输入并按 ENTER。现在我们将回答一系列是或否的提示。让我们来看看它们：

首先，我们被问及 验证密码插件 ，该插件可以自动为您的 MySQL 用户强制执行某些密码强度规则。启用此功能是您需要根据您的个人安全需求做出的决定。输入 y 和 ENTER 启用它，或者直接点击 ENTER 跳过它。如果启用，系统还会提示您从 0 到 2 中选择一个级别，以了解密码验证的严格程度。选择一个数字并点击 ENTER 继续。

接下来会询问您是否要更改 root 密码。由于我们刚刚在安装 MySQL 时创建了密码，我们可以放心地跳过这个。点击 ENTER 继续而不更新密码。

其余提示可以回答是。系统将询问您删除 anonymous MySQL 用户、禁止远程 root 登录、删除 test 数据库以及重新加载权限表以确保之前的更改生效适当地。这些都是好主意。键入 y 并为每个点击 ENTER。

回答完所有提示后脚本将退出。现在我们的 MySQL 安装是相当安全的。在下一步中，我们将配置 MySQL 以允许从远程连接进行访问。

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

现在您的数据库已启动并运行，我们需要更改一些配置值以允许来自其他计算机的连接。

在编辑器中使用 root 权限打开 mysqld 配置文件：

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

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

mysqld.cnf

. . .
[mysqld]

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

目前，MySQL 被配置为仅查找本地连接。我们需要更改它以引用可以访问您的服务器的外部 IP 地址。

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

/etc/mysql/my.cnf

[mysqld]
. . .
bind-address = db_server_ip

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

/etc/mysql/my.cnf

. . .
require_secure_transport = on

完成后保存并关闭文件。

为了使 SSL 连接正常工作，我们需要创建一些密钥和证书。 MySQL 带有一个命令，可以自动设置我们需要的一切：

sudo mysql_ssl_rsa_setup --uid=mysql

这将创建必要的文件并使 MySQL 服务器 (--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

接下来，我们将设置远程访问服务器所需的用户和数据库。

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

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

首先使用 MySQL root 帐户连接到 MySQL：

mysql -u root -p

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

现在我们可以创建 WordPress 将使用的数据库。我们将称之为 wordpress 以便我们稍后可以轻松识别它：

CREATE DATABASE wordpress;

注意：所有SQL语句必须以分号结尾（;）。如果您在 MySQL 命令上点击 ENTER 并且只看到带有 -> 提示符的新行，那么您可能忘记了分号。只需在新行中输入，然后再次按 ENTER 继续。

现在我们有了一个数据库，我们需要创建我们的用户。创建用户的一个转折点是我们需要根据用户连接的位置定义两个不同的配置文件。我们将创建一个仅限本地用户，以及一个绑定到我们的 Web 服务器 IP 地址的远程用户。

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

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

让我们继续授予此帐户对我们数据库的完全访问权限：

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

该用户现在可以对 WordPress 的数据库进行任何操作，但该帐户不能远程使用，因为它只匹配来自本地计算机的连接。

现在创建一个配套帐户，它将专门匹配来自我们的 Web 服务器的连接。为此，您需要 Web 服务器的 IP 地址。我们可以为这个帐户命名任何名称，但为了获得更一致的体验，我们将使用与上面完全相同的用户名，仅修改主机部分。

请记住，您必须使用与您在 mysqld.cnf 文件中配置的相同网络的 IP 地址。这意味着如果您使用私有网络 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';

刷新权限以将它们写入磁盘并开始使用它们：

FLUSH PRIVILEGES;

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

exit

现在我们已经设置了一个新的数据库和启用远程的用户，让我们测试数据库和连接。

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

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

首先，通过尝试使用我们的新帐户登录来测试来自 数据库计算机 的本地连接：

mysql -u wordpressuser -p

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

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

exit

登录到您的 网络服务器 以测试远程连接。

在您的 Web 服务器上，您需要为 MySQL 安装一些客户端工具才能访问远程数据库。更新本地包缓存，然后安装客户端实用程序：

sudo apt-get update
sudo apt-get install mysql-client

现在，我们可以使用以下语法连接到我们的数据库服务器：

mysql -u wordpressuser -h db_server_ip -p

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

系统将要求您输入 wordpressuser 帐户的密码，如果一切顺利，您将收到 MySQL 提示。我们可以使用以下命令验证连接是否使用 SSL：

status

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
--------------

SSL: 行将指示 SSL 密码是否正在使用中。您现在可以继续并退出提示，因为您已验证可以远程连接：

exit

对于额外的检查，您可以尝试从第三台服务器执行相同的操作，以确保该另一台服务器是 而不是 授予访问权限。您已验证本地访问和来自 Web 服务器的访问，但尚未验证其他连接将被拒绝。

继续在您为而非配置特定用户帐户的服务器上尝试相同的过程。您可能必须像上面那样安装客户端实用程序：

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

这是我们期望的，也是我们想要的。

我们已经成功地测试了我们的远程连接，现在可以继续我们的 WordPress 安装。

第 5 步 — 安装 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',         '1jl/vqfs<XhdXoAPz9 DO NOT COPY THESE VALUES c_j{iwqD^<+c9.k<J@4H');
define('SECURE_AUTH_KEY',  'E2N-h2]Dcvp+aS/p7X DO NOT COPY THESE VALUES {Ka(f;rv?Pxf})CgLi-3');
define('LOGGED_IN_KEY',    'W(50,{W^,OPB%PB<JF DO NOT COPY THESE VALUES 2;y&,2m%3]R6DUth[;88');
define('NONCE_KEY',        'll,4UC)7ua+8<!4VM+ DO NOT COPY THESE VALUES #`DXF+[$atzM7 o^-C7g');
define('AUTH_SALT',        'koMrurzOA+|L_lG}kf DO NOT COPY THESE VALUES  07VC*Lj*lD&?3w!BT#-');
define('SECURE_AUTH_SALT', 'p32*p,]z%LZ+pAu:VY DO NOT COPY THESE VALUES C-?y+K0DK_+F|0h{!_xY');
define('LOGGED_IN_SALT',   'i^/G2W7!-1H2OQ+t$3 DO NOT COPY THESE VALUES t6**bRVFSD[Hi])-qS`|');
define('NONCE_SALT',       'Q6]U:K?j4L%Z]}h^q7 DO NOT COPY THESE VALUES 1% ^qUswWgn+6&xqHN&%');

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

nano ~/wordpress/wp-config.php

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

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

wp-config.php

. . .
/** 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 使用 SSL 连接到我们的 MySQL 数据库：

wp-config.php

define('MYSQL_CLIENT_FLAGS', MYSQLI_CLIENT_SSL);

保存并关闭文件。

接下来，我们需要将在我们的 ~/wordpress 目录中找到的文件和目录复制到 Nginx 的文档根目录。我们使用 -a 标志来确保我们的权限得到维护：

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

现在我们所有的文件都准备好了。剩下要做的就是修改文件所有权。我们将把文档根目录中的所有文件设置为我们的 Web 服务器用户 www-data 拥有：

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

WordPress 现在应该已安装并准备好通过其基于 Web 的设置例程运行。我们将在下一步中这样做。

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

WordPress 有一个基于 Web 的设置例程，它会询问一些问题并在我们的数据库中安装它需要的表。让我们现在开始吧。

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

http://example.com

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

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

结论

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