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

介绍

随着应用程序或网站的发展，您可能会发现当前的设置已经无法满足您的需要。如果您当前在同一个 VPS 上托管 Web 服务器和数据库后端，那么将这两个功能分开可能是一个好主意，这样每个功能都可以在自己的机器上运行和发展。

在本指南中，我们将讨论如何配置您的 Web 服务器可以连接到的远程数据库服务器以获取动态内容。我们将使用 WordPress 作为示例，以便我们有一些可以使用的东西。我们将在我们的 Web 服务器上配置 Nginx，然后将其连接到远程计算机上的 MySQL 数据库。我们将在 Ubuntu 12.04 VPS 实例上执行所有这些操作以供演示。

在数据库服务器上安装 MySQL

首先，我们将配置一个 VPS 实例作为 MySQL 服务器。当您的单机配置达到上限时，将数据存储在单独的计算机上是一种优雅扩展的好方法。它还提供了负载平衡和以后进一步扩展设置所需的基本结构。

首先，我们需要在数据库服务器上安装一些基本软件包。这些步骤基本上与为传统 LEMP 堆栈设置数据库的步骤相同，但我们不需要所有组件（有些组件位于另一台服务器上）。

首先更新包缓存并安装 MySQL 服务器：

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

在安装过程中，系统会要求您选择并确认 MySQL 的 root 密码。

完成后，您需要运行数据库安装命令，它将生成适当的目录结构来管理您的组件。

sudo mysql_install_db

之后，我们应该通过运行一个脚本来稍微加强安全性，该脚本会询问我们是否禁用一些不安全的默认设置：

sudo mysql_secure_installation

您必须输入在上述步骤中设置的 MySQL 管理员密码。之后，系统会询问您是否要更改该密码。如果您对当前密码满意，请输入“N”表示否。

对于所有其他问题，您只需按 ENTER 键选择默认选项，这将删除一些测试数据库并锁定访问权限。

配置 MySQL 以允许远程访问

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

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

sudo nano /etc/mysql/my.cnf

此文件分为几个部分，用括号（[ 和 ]）中的单词表示。找到标有 的部分mysqld：

[mysqld]

在此部分中（在此标记和下一节标记之间的区域），您需要找到一个名为的参数bind-address。这基本上告诉数据库软件在哪个网络地址上监听连接。

目前，MySQL 配置为仅查找来自其自身计算机的连接。我们需要将其更改为引用可以访问您的服务器的外部IP 地址。

如果您在具有私有网络功能的数据中心中托管此服务器，请使用服务器的私有网络 IP。 否则，您可以在此处使用公共 IP 地址：

bind-address        = your_database_IP

完成后保存并关闭文件。

为了强制 MySQL 读取我们刚刚实现的新更改，我们可以重新启动数据库：

sudo service mysql restart

设置远程 WordPress 凭据和数据库

现在我们已经配置了 MySQL 监听外部地址，我们需要创建一个数据库并建立远程用户。尽管 MySQL 本身现在正在监听其他机器可以连接的 IP 地址，但目前还没有任何它可以访问的数据库。

这也是我们根据用户连接位置建立不同权限的机会。我们可以创建两个“用户”，它们实际上可以是相同的用户名，但与不同的主机相关联。

我的意思是，我们可以创建一个与数据库服务器本身绑定的用户，并授予其非常广泛的权限。然后，我们可以使用相同的用户名，但与我们的 Web 服务器相关联，并仅授予其 WordPress 所需的权限。

这样，我们就可以在登录数据库服务器的同时执行繁重的工作，同时只为 Web 服务器提供完成工作所需的最低限度的权限。这是一项很好的安全策略，可以在 Web 服务器受到攻击时部分保护数据库服务器。

首先使用您配置的根帐户和管理密码连接到 MySQL：

mysql -u root -p

系统将要求您输入 MySQL 根密码，然后您将获得 MySQL 提示。

首先，让我们创建 WordPress 将使用的数据库。我们只需调用它，wordpress以便稍后轻松识别它：

CREATE DATABASE wordpress;

现在我们有了数据库，我们需要创建本地用户，如果需要，该用户将用于执行更复杂的数据库操作。我们将调用此用户，wordpressuser并使该帐户仅匹配来自数据库服务器本身的连接尝试，方法是localhost在声明中使用：

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

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

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

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

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

请记住，您必须使用与文件中配置的网络相同的 IP 地址my.cnf。这意味着，如果您使用了私有网络 IP，则需要创建以下规则以使用 Web 服务器的私有 IP。如果您将 MySQL 配置为使用公共互联网，则应将其与 Web 服务器的公共 IP 地址进行匹配。

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

现在我们有了远程帐户，我们可以为其提供 WordPress 在正常情况下运行所需的部分权限。这些权限包括select、delete、insert和update。

虽然这是最终目标，但目前我们实际上无法实现这一点。这是因为在某些操作期间，您必须临时调整权限以允许更多访问。其中之一实际上是初始安装。最简单的方法是现在只授予所有权限，然后在安装完成后再限制它们。

作为参考，我们将使用锁定帐户的命令（不用担心，当您需要时我们会再次给您这个命令）是这样的：

GRANT SELECT,DELETE,INSERT,UPDATE ON wordpress.* TO 'wordpressuser'@'web_server_ip';

但目前，我们将暂时授予所有权限，这使得它暂时与本地帐户实际上相同：

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

配置完 WordPress 后，我们会再讨论这个问题。如果您实际上并未安装 WordPress，而只是使用本指南来了解如何将 Web 服务器与数据库分开，那么现在您可能可以使用更严格的设置。这取决于您的 Web 应用程序，因此请查找应用程序所需的最低数据库权限。

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

FLUSH PRIVILEGES;

现在，您可以通过输入以下命令退出 MySQL 提示符：

exit

测试远程和本地连接

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

首先，尝试使用我们的新帐户登录来测试数据库机器的连接：

mysql -u wordpressuser -p

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

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

exit

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

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

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

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

mysql -u wordpressuser -h database_server_IP -p

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

系统将要求您输入wordpressuser帐户的密码，如果一切顺利，您将收到一个 MySQL 提示。

如果成功，那么您可以继续并退出提示，因为您现在已经验证您可以远程连接。

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

继续在未配置特定用户帐户的服务器上尝试相同的过程。 您可能需要像上面一样安装客户端实用程序：

mysql -u wordpressuser -h database_server_IP -p

这不应该成功完成。它应该返回类似这样的错误：

ERROR 1130 (HY000): Host '11.111.111.111' is not allowed to connect to this MySQL server

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

设置Web服务器

现在我们已经验证我们的 Web 服务器可以访问 WordPress 数据库，我们需要通过配置 Nginx、PHP 和必要的组件来使其成为 Web 服务器。

由于您已更新软件包索引以运行上述测试，因此我们无需再次执行此操作。让我们安装所需的所有软件包：

sudo apt-get install nginx php5-fpm php5-mysql

当一切安装完成后，您可以开始配置软件。

配置 PHP

让我们从 PHP 开始，因为它很简单。

打开 的 PHP 配置文件php-fpm，它将处理我们的动态内容。我们只需要修改其中一个值：

sudo nano /etc/php5/fpm/php.ini

搜索参数cgi.fix_pathinfo。它很可能被“;”字符注释掉并设置为“1”。我们需要通过取消注释该行并将其设置为“0”来反转这两种情况：

cgi.fix_pathinfo=0

这是一项安全措施。通过设置此选项，我们告诉 PHP 如果未找到完全匹配的文件，则不要尝试猜测用户试图访问的文件。如果我们不设置此选项，恶意用户可能会利用这个机会并让我们的服务器执行我们不希望它执行的代码。

完成后保存并关闭文件。

接下来，我们需要打开另一个文件来修改 PHP 处理器和 Web 服务器的通信方式：

sudo nano /etc/php5/fpm/pool.d/www.conf

查找listen指令，它应该设置为127.0.0.1:9000。我们不使用端口，而是将其设置为 unix 域套接字：

listen = /var/run/php5-fpm.sock

完成后保存并关闭文件。

现在我们有了我们的值，重新启动我们的 PHP 处理器：

sudo service php5-fpm restart

配置 Nginx

现在我们已准备好配置 Nginx。我们可以先将默认虚拟主机文件复制到可以使用的新文件。我们将以我们网站的域名命名。我将使用占位符“ example.com ”：

sudo cp /etc/nginx/sites-available/default /etc/nginx/sites-available/example.com

现在，打开我们刚刚复制的文件：

sudo nano /etc/nginx/sites-available/example.com

在里面，我们将修改我们的服务器块（括号内的部分server）。首先取消注释监听端口 80 的指令。我们还将更改根目录，并让 Nginx 默认提供 PHP 索引文件：

server {
    listen 80;
    root /var/www/example.com;
    index index.php index.hmtl index.htm;

接下来，我们将修改server_name指令以使用我们的域名，确保我们的try_files设置正确（如果未找到文件则将请求传递给 PHP）并且我们的错误页面已配置：

server {
    listen 80;
    root /var/www/example.com;
    index index.php index.hmtl index.htm;
    server_name example.com;
    location / {
        try_files $uri $uri/ /index.php?q=$uri&$args;
    }
    error_page 404 /404.html;
    error_page 500 502 503 504 /50x.html;
    location = /50x.html {
        root /usr/share/nginx/www;
    }

最后，我们需要使用与所有 PHP 请求匹配的位置块来设置实际的 PHP 处理。如果未找到完全匹配，我们将立即返回 404。我们还将使用为 PHP 配置的套接字：

server {
    listen 80;
    root /var/www/example.com;
    index index.php index.hmtl index.htm;
    server_name example.com;
    location / {
        try_files $uri $uri/ /index.php?q=$uri&$args;
    }
    error_page 404 /404.html;
    error_page 500 502 503 504 /50x.html;
    location = /50x.html {
        root /usr/share/nginx/www;
    }
    location ~ \.php$ {
        try_files $uri =404;
        fastcgi_pass unix:/var/run/php5-fpm.sock;
        fastcgi_index index.php;
        include fastcgi_params;
    }
}

这是我们的服务器块配置的结束。保存并关闭文件。

现在，我们将其链接到我们的“启用”目录并删除默认服务器块文件的链接：

sudo rm /etc/nginx/sites-enabled/default
sudo ln -s /etc/nginx/sites-available/example.com /etc/nginx/sites-enabled/

重新启动 Nginx 以使这些更改生效：

sudo service nginx restart

安装 WordPress

现在我们已经设置了带有 PHP 处理的 Web 服务器，并且拥有了数据库服务器，我们需要安装一个应用程序来利用它并连接到我们的数据库。正如您现在所知，我们在本教程中使用 WordPress 来演示此功能。

将最新的 WordPress tarball 下载到您的主目录：

cd ~
wget http://wordpress.org/latest.tar.gz

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

tar xzvf latest.tar.gz

WordPress 包含一个示例配置文件，但它不是现成的。我们将重命名此文件，以便正确读取它，然后我们可以在文本编辑器中打开它以进行更改：

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

在里面，我们需要输入远程数据库的正确值。请记住使用之前在远程数据库测试中使用的相同 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', 'database_server_ip');

完成后关闭文件。这实际上是整个配置中唯一明确链接我们的 Web 服务器和数据库服务器的部分。

接下来，我们需要创建在 Nginx 服务器块配置中设置的目录结构。如果您还记得，我在演示中使用“ example.com ”，但您应该使用在 Nginx 配置中指定的任何内容：

sudo mkdir -p /var/www/example.com

然后，我们将目录中的文件和目录复制~/wordpress到我们刚刚创建的新文档根目录中：

sudo cp -r ~/wordpress/* /var/www/example.com

现在我们所有的文件都已就位。剩下要做的就是稍微修改一下我们的权限和文件所有权。我们应该首先进入服务器的文档根目录：

cd /var/www/example.com

我们将把此目录中的所有文件提供给我们的 Web 服务器用户，该用户称为www-data：

sudo chown -R www-data:www-data *

不过，我们仍然希望能够以普通非 root 用户的身份编辑这些文件，因此我们可以将 Web 服务器上的常规非 root 帐户添加到 Web 服务器组。然后，我们可以授予该组修改此目录中文件的权限：

sudo usermod -a -G www-data your_user
sudo chmod -R g+rw /var/www/example.com

通过 Web 界面设置站点

现在，您需要做的就是通过 Web 界面完成安装。

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

http://example.com

您应该会看到 WordPress 安装屏幕，您需要在其中填写相关信息：

设置配置后，您将需要使用刚刚创建的帐户登录应用程序：

您将被带到管理仪表板，您可以在其中开始配置您的网站：

限制远程数据库权限

完成 WordPress 的配置后，您应该返回并撤销远程数据库用户的一些权限。

大多数数据库权限在日常操作中都不需要，只需要在更新或安装插件时启用即可。请记住这一点，以防在执行这些步骤后执行管理操作时出现错误。

某些插件可能还需要额外的权限。调查每个插件以了解其需要什么，并考虑选择需要最少额外访问权限的插件。

登录数据库服务器。然后使用 MySQL 根账户登录 MySQL：

mysql -u root -p

输入您的密码以获取提示。

您可以通过输入以下命令查看远程用户的当前权限：

show grants for 'wordpressuser'@'web_server_IP';

+---------------------------------------------------------------------------------------------------------------------------+
| Grants for wordpressuser@xx.xxx.xxx.xxx                                                                                   |
+---------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wordpressuser'@'xx.xxx.xxx.xxx' IDENTIFIED BY PASSWORD '*5FD2B7524254B7F81B32873B1EA6D681503A5CA9' |
| GRANT ALL PRIVILEGES ON `wordpress`.* TO 'wordpressuser'@'xx.xxx.xxx.xxx'                                                 |
+---------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

“使用”权限实际上意味着没有真正的权限，所以我们不必担心这一点。第二行权限是我们最初设置的，允许对数据库的所有权限wordpress。

应用比当前特权更严格的新特权的过程实际上分为两个步骤。

首先，我们需要撤销所有当前权限。输入如下内容即可：

REVOKE ALL PRIVILEGES on wordpress.* FROM 'wordpressuser'@'web_server_IP';

如果我们现在请求当前的补助金，我们会看到第二行消失了：

show grants for 'wordpressuser'@'web_server_IP';

+---------------------------------------------------------------------------------------------------------------------------+
| Grants for wordpressuser@10.128.213.175                                                                                   |
+---------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wordpressuser'@'xx.xxx.xxx.xxx' IDENTIFIED BY PASSWORD '*5FD2B7524254B7F81B32873B1EA6D681503A5CA9' |
+---------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

现在，我们可以将想要的权限重新添加到帐户中。我们需要UPDATE、INSERT、SELECT和DELETE权限才能进行日常使用：

GRANT SELECT,DELETE,INSERT,UPDATE ON wordpress.* TO 'wordpressuser'@'web_server_ip';

如果我们再次检查，我们可以看到我们的细粒度权限现在已经设置好了。

要告诉 MySQL 重新读取权限表来实现我们的更改，我们可以输入：

FLUSH PRIVILEGES;

然后再次退出MySQL：

exit

结论

如果您一直在关注，那么您现在应该已经很好地了解如何让远程数据库与您的应用程序进行通信。虽然我们介绍了一些特定于 WordPress 的步骤，但一般思路（尤其是与 MySQL 配置和用户权限相关的思路）适用于大多数远程 MySQL 情况。