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

介绍

随着您的应用程序或网站的增长，您可能会达到超出当前设置的地步。如果您当前将 Web 服务器和数据库后端托管在同一个 VPS 上，一个好主意可能是将这两个功能分开，以便每个功能都可以在自己的机器上运行和发展。

在本指南中，我们将讨论如何配置您的 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 服务器受到威胁的情况下部分保护数据库服务器。

首先使用您配置的 root 帐户和管理密码连接到 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 地址。我们可以为这个帐户命名任何名称，但为了获得更一致的体验，我们将使用与上面完全相同的用户名，仅修改主机部分。

请记住，您必须使用与您在 my.cnf 文件中配置的相同网络的 IP 地址。这意味着如果您使用私有网络 IP，您将需要创建以下规则以使用 Web 服务器的私有 IP。如果您将 MySQL 配置为使用公共 Internet，则应将其与 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

测试远程和本地连接

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

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

mysql -u wordpressuser -p

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

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

exit

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

在您的 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-fpm 的 PHP 配置文件，它将处理我们的动态内容。我们只需要在其中修改一个值：

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

查找应设置为 127.0.0.1:9000 的 listen 指令。我们将不使用端口，而是将其设置为 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 请求的 location 块来设置实际的 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 压缩包下载到您的主目录：

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 root 帐户登录 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 情况。