如何设置远程数据库以优化基于Ubuntu 16.04的MySQL的站点性能

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

介绍

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

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

先决条件

在开始本教程之前,您将需要:

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

将我们的数据存储在单独的服务器上是一个很好的方法,当我们达到单机配置的性能上限时,可以优雅地扩展。 它还提供了负载平衡所需的基本结构,并在以后更多地扩展我们的基础设施。

要开始,我们将在安装LEMP的服务器上安装MySQL。 登录到这个服务器,然后更新你的包缓存并安装MySQL服务器软件:

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

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

现在应该安装并运行MySQL。 我们来检查一下使用systemctl

systemctl status mysql
● 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 密码。 输入它,然后按ENTER 现在我们将回答一系列是或否的提示。 我们来看看他们:

首先,我们询问有效的密码插件 ,一个可以自动为您的MySQL用户强制执行某些密码强度规则的插件。 使您能够根据您的个人安全需求做出决定。 键入yENTER启用它,或者只需ENTER即可跳过它。 如果启用,您还将提示您从0-2选择一个级别,以便密码验证的严格程度。 选择一个数字,然后ENTER继续。

接下来,系统会询问您是否要更改root密码。 由于我们在安装MySQL时刚刚创建了密码,所以我们可以安全的跳过这个。 ENTER继续,不更新密码。

其余的提示可以回答 您将被要求删除匿名 MySQL用户,不允许远程root登录,删除测试数据库,并重新加载权限表,以确保以前的更改生效。 这些都是个好主意。 键入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

由于我们将通过互联网连接到数据库,我们将需要加密的连接来保护我们的数据安全。 如果您不加密您的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
tcp        0      0 db_server_ip:3306     0.0.0.0:*               LISTEN      27328/mysqld

netstat打印有关我们服务器的网络系统的统计信息。 这个输出显示了一个称为mysqld的进程连接到标准MySQL端口3306db_server_ip

现在在防火墙上打开该端口以允许流量通过:

sudo ufw allow mysql

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

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

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

开始使用MySQL 帐户连接到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服务器以测试远程连接。

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

这不应该成功完成。 它应该抛出一个看起来像这样的错误:

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

这是我们期望的和我们想要的。

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

第5步 - 安装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文件中wp-config.php配置。

警告! 每次请求唯一值很重要。 不要复制下面显示的值!

define('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安装屏幕

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

结论

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


分享按钮