介绍

ProxySQL 是一个开源 MySQL 代理服务器，这意味着它充当 MySQL 服务器和访问其数据库的应用程序之间的中介。 ProxySQL 可以通过在多个数据库服务器池之间分配流量来提高性能，还可以通过在一个或多个数据库服务器发生故障时自动故障转移到备用数据库来提高可用性。

在本指南中，您将 ProxySQL 设置为具有自动故障转移功能的多台 MySQL 服务器的负载平衡器。例如，本教程使用三个 MySQL 服务器的多主复制集群，但您也可以对其他集群配置使用类似的方法。

先决条件

要遵循本教程，您将需要：

一台 Ubuntu 16.04 服务器使用 this initial Ubuntu 16.04 server setup tutorial 设置，包括 sudo 非 root 用户和防火墙。此服务器将成为您的 ProxySQL 实例。
三台 MySQL 服务器配置为形成一个多主复制组。您可以按照如何在 Ubuntu 16.04 上配置 MySQL 组复制教程进行设置。在 Choosing Single Primary 或 Multi-Primary 部分中，按照 multi-primary 复制组的说明进行操作。

第 1 步 — 安装 ProxySQL

ProxySQL 的开发人员在他们的 GitHub 发布页面上为所有 ProxySQL 版本提供了官方的 Ubuntu 包，因此我们将从那里下载最新的包版本并安装它。

您可以在发布列表中找到最新的软件包。命名约定是 proxysql_version-distribution.deb，其中 version 是类似于 1.4.4 版本的 1.4.4 的字符串，distribution 是类似于 ubuntu16_amd64 的字符串] 适用于 64 位 Ubuntu 16.04。

将最新的官方包下载到 /tmp 目录中，在撰写本文时为 1.4.4。

cd /tmp
curl -OL https://github.com/sysown/proxysql/releases/download/v1.4.4/proxysql_1.4.4-ubuntu16_amd64.deb

使用dpkg安装包，用于管理.deb软件包。 -i 标志表明我们想从指定的文件安装。

sudo dpkg -i proxysql_*

此时，您不再需要 .deb 文件，因此可以将其删除。

rm proxysql_*

接下来，我们需要一个 MySQL 客户端应用程序来连接到 ProxySQL 实例。这是因为 ProxySQL 在内部使用与 MySQL 兼容的接口来执行管理任务。我们将使用 mysql 命令行工具，它是 Ubuntu 存储库中可用的 mysql-client 软件包的一部分。

更新您的包存储库以确保您获得最新的预捆绑版本，然后安装 mysql-client 包。

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

您现在拥有运行 ProxySQL 的所有要求，但该服务在安装后不会自动启动，所以现在手动启动它。

sudo systemctl start proxysql

ProxySQL 现在应该使用其默认配置运行。您可以使用 systemctl 进行检查。

systemctl status proxysql

输出将类似于以下内容：

Output● proxysql.service - LSB: High Performance Advanced Proxy for MySQL
   Loaded: loaded (/etc/init.d/proxysql; bad; vendor preset: enabled)
   Active: active (running) since Thu 2017-12-21 19:19:20 UTC; 5s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 12350 ExecStart=/etc/init.d/proxysql start (code=exited, status=0/SUCCESS)
    Tasks: 23
   Memory: 30.9M
      CPU: 86ms
   CGroup: /system.slice/proxysql.service
           ├─12355 proxysql -c /etc/proxysql.cnf -D /var/lib/proxysql
           └─12356 proxysql -c /etc/proxysql.cnf -D /var/lib/proxysql

active (running) 行表示 ProxySQL 已安装并正在运行。

接下来，我们将通过设置用于访问 ProxySQL 管理界面的密码来提高安全性。

第 2 步 — 设置 ProxySQL 管理员密码

第一次开始新的 ProxySQL 安装时，它使用包提供的配置文件来初始化其所有配置变量的默认值。初始化后，ProxySQL 将其配置存储在数据库中，您可以通过命令行管理和修改该数据库。

要在 ProxySQL 中设置管理员密码，我们将连接到该配置数据库并更新相应的变量。

首先，访问管理界面。系统会提示您输入密码，默认安装时密码为 admin。

mysql -u admin -p -h 127.0.0.1 -P 6032 --prompt='ProxySQLAdmin> '

-u 指定我们要连接的用户，这里是 admin，这是管理任务（如更改配置设置）的默认用户。
-h 127.0.0.1 告诉 mysql 连接到本地 ProxySQL 实例。我们需要明确地定义它，因为 ProxySQL 不侦听 mysql 默认假定的套接字文件。
-P 指定要连接的端口。 ProxySQL 的管理界面监听 6032。
--prompt 是一个可选标志，用于更改默认提示，通常为 mysql>。在这里，我们将其更改为 ProxySQLAdmin> 以明确我们已连接到 ProxySQL 管理界面。这将有助于避免稍后我们还将连接到复制数据库服务器上的 MySQL 接口时产生混淆...…

连接后会看到ProxySQLAdmin>提示：

ProxySQL administration console promptWelcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

ProxySQLAdmin>

通过更新 global_variables 数据库中的 admin-admin_credentials 配置变量 (UPDATE) 更改管理帐户密码。请记住将以下命令中的 password 更改为您选择的强密码。

UPDATE global_variables SET variable_value='admin:password' WHERE variable_name='admin-admin_credentials';

OutputQuery OK, 1 row affected (0.00 sec)

由于 ProxySQL 配置系统的工作方式，此更改不会立即生效。它由三个独立的层组成：

memory，从命令行界面进行修改时会更改。
runtime，ProxySQL 使用它作为有效配置。
disk，用于使配置在重新启动后保持不变。

现在，您所做的更改在 memory 中。要使更改生效，您必须将 memory 设置复制到 runtime 领域，然后将它们保存到 disk 以使其持久化。

LOAD ADMIN VARIABLES TO RUNTIME;
SAVE ADMIN VARIABLES TO DISK;

这些 ADMIN 命令仅处理与管理命令行界面相关的变量。 ProxySQL 公开了类似的命令，例如 MYSQL，以处理其配置的其他部分。我们将在本教程后面使用这些。

现在 ProxySQL 已安装并使用新的管理员密码运行，让我们设置 3 个 MySQL 节点，以便 ProxySQL 可以监控它们。不过，请保持 ProxySQL 接口打开，因为稍后我们将使用它。

第 3 步 — 在 MySQL 中配置监控

ProxySQL 需要与 MySQL 节点通信才能评估它们的状况。为此，它必须能够使用专用用户连接到每个服务器。

在这里，我们将在 MySQL 节点上配置必要的用户并安装允许 ProxySQL 查询组复制状态的附加 SQL 函数。

由于 MySQL 组复制已在运行，因此必须仅对组 的单个成员 执行以下步骤。

在第二个终端中，登录到具有 MySQL 节点之一的服务器。

ssh sammy@your_mysql_server_ip_1

下载包含 ProxySQL 组复制支持工作所需的一些必要功能的 SQL 文件。

curl -OL https://gist.github.com/lefred/77ddbde301c72535381ae7af9f968322/raw/5e40b03333a3c148b78aa348fd2cd5b5dbb36e4d/addition_to_sys.sql

注意：这个文件是由 ProxySQL 作者提供的，但是是以一种特别的方式：它是个人 GitHub 存储库中的一个 gist，这意味着它可能会被移动或被淘汰日期。将来，它可能会作为版本文件添加到官方 ProxySQL 存储库中。

您可以在作者关于 native ProxySQL 对 MySQL 组复制的支持的博客文章中阅读有关此文件的上下文和内容的更多信息。

您可以使用 less addition_to_sys.sql 查看文件的内容。

准备好后，执行文件中的命令。系统将提示您输入 MySQL 管理密码。

mysql -u root -p < addition_to_sys.sql

如果命令成功运行，它将不会产生任何输出。在这种情况下，所有 MySQL 节点现在都将公开 ProxySQL 识别组复制状态所需的函数。

接下来，我们必须创建一个专用用户，ProxySQL 将使用该用户来监控实例的运行状况。

打开 MySQL 交互式提示，它将再次提示您输入 root 密码。

mysql -u root -p

然后创建专用用户，我们在这里称为 monitor。确保将密码更改为强密码。

CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitorpassword';

将查询MySQL服务器状况的用户权限授予monitor用户。

GRANT SELECT on sys.* to 'monitor'@'%';

最后，应用更改。

FLUSH PRIVILEGES;

由于组复制，一旦您将健康监控用户添加到一个 MySQL 节点，它将在所有三个节点上完全配置。

接下来，我们需要使用该用户的信息更新 ProxySQL，以便它可以访问 MySQL 节点。

第 4 步 — 在 ProxySQL 中配置监控

要配置 ProxySQL 在监视节点时使用新用户帐户，我们将 UPDATE 适当的配置变量。这与我们在步骤 2 中设置管理员密码的方式非常相似。

返回 ProxySQL 管理界面，将 mysql-monitor_username 变量更新为新帐户的用户名。

UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';

和以前一样，配置不会自动应用，所以将其迁移到 runtime 并保存到 disk。这一次，请注意我们使用 MYSQL 而不是 ADMIN 来更新这些变量，因为我们正在修改 MySQL 配置变量。

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

监控账号在所有端都配置好了，下一步就是告诉ProxySQL节点本身。

第 5 步 — 将 MySQL 节点添加到 ProxySQL 服务器池

为了让 ProxySQL 知道我们的三个 MySQL 节点，我们需要告诉 ProxySQL 如何将它们分布在它的 主机组 中，这些主机组是指定的节点集。每个主机组由一个正数标识，如 1 或 2。使用 ProxySQL 查询路由时，主机组可以将不同的 SQL 查询路由到不同的主机集。

在静态复制配置中，主机组可以任意设置。但是，ProxySQL 的组复制支持自动将复制组中的所有节点分为四种逻辑状态：

writers，它们是 MySQL 节点，可以接受更改数据的查询。 ProxySQL 确保将所有主节点维护到该组中定义的最大数量。
备份写入器，它们也是 MySQL 节点，可以接受更改数据的查询。但是，这些节点没有被指定为写入者；超过定义的维护写入器数量的主节点保留在该组中，如果其中一个写入器失败，则将其提升为写入器。
readers 是 MySQL 节点，不能接受更改数据的查询，应该用作只读节点。 ProxySQL 在这里只放置从节点。
offline，适用于由于缺乏连接或流量缓慢等问题而行为不端的节点。

这四种状态中的每一种都有对应的主机组，但不会自动分配数字组标识符。

总而言之，我们需要告诉 ProxySQL 它应该为每个状态使用哪些标识符。在这里，我们将 1 用于 offline 主机组，2 用于 writer 主机组，3 用于 [X124X ]reader 主机组，4 用于 backup writer 主机组。

要设置这些标识符，请在 mysql_group_replication_hostgroups 配置表中使用这些变量和值创建一个新行。

INSERT INTO mysql_group_replication_hostgroups (writer_hostgroup, backup_writer_hostgroup, reader_hostgroup, offline_hostgroup, active, max_writers, writer_is_also_reader, max_transactions_behind) VALUES (2, 4, 3, 1, 1, 3, 1, 100);

这些是在这一行中设置的附加变量以及每个变量的作用：

active 设置为 1 启用 ProxySQL 对这些主机组的监控。
max_writers 定义了有多少节点可以充当写入者。我们在这里使用 3 是因为在多主配置中，所有节点都可以被平等对待，所以这里我们使用 3（节点总数）。
writer_is_also_reader 设置为 1 指示 ProxySQL 也将写入者视为读取者。
max_transactions_behind 设置节点被分类为离线之前的最大延迟事务数。

注意： 因为我们的示例使用多主拓扑，其中所有节点都可以写入数据库，所以我们将平衡 writer 主机组中的所有 SQL 查询。在其他拓扑上，writer（主）节点和 reader（辅助）节点之间的划分可以将只读查询路由到与写查询不同的节点/主机组。 ProxySQL 不会自动执行此操作，但您可以使用规则设置查询路由。

现在 ProxySQL 知道如何跨主机组分布节点，我们可以将 MySQL 服务器添加到池中。为此，我们需要将 INSERT 每个服务器的 IP 地址和初始主机组放入 mysql_servers 表中，其中包含 ProxySQL 可以与之交互的服务器列表。

添加三个 MySQL 服务器中的每一个，确保替换以下命令中的示例 IP 地址。

INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (2, '203.0.113.1', 3306);
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (2, '203.0.113.2', 3306);
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (2, '203.0.113.3', 3306);

在这里，2 值将所有这些节点最初设置为写入器，3306 设置默认 MySQL 端口。

和之前一样，将这些更改迁移到 runtime 并保存到 disk 以使更改生效。

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

ProxySQL 现在应该按照指定将我们的节点分布在主机组中。让我们通过对 runtim330e_mysql_servers 表执行 SELECT 查询来检查这一点，该表公开了 ProxySQL 正在使用的服务器的当前状态。

SELECT hostgroup_id, hostname, status FROM runtime_mysql_servers;

Output+--------------+-------------+--------+
| hostgroup_id | hostname    | status |
+--------------+-------------+--------+
| 2            | 203.0.113.1 | ONLINE |
| 2            | 203.0.113.2 | ONLINE |
| 2            | 203.0.113.3 | ONLINE |
| 3            | 203.0.113.1 | ONLINE |
| 3            | 203.0.113.2 | ONLINE |
| 3            | 203.0.113.3 | ONLINE |
+--------------+-------------+--------+
6 rows in set (0.01 sec)

在结果表中，每个服务器都列出了两次：主机组 ID 2 和 3 各一次，表明所有三个节点都是写入者和读取者。所有节点都标记为 ONLINE，表示它们已准备好使用。

但是，在我们可以使用它们之前，我们必须配置用户凭据以访问每个节点上的 MySQL 数据库。

第 6 步 — 创建 MySQL 用户

ProxySQL 充当负载均衡器；最终用户连接到 ProxySQL，ProxySQL 依次将连接传递给所选的 MySQL 节点。为了连接到单个节点，ProxySQL 会重复使用它访问时使用的凭据。

为了允许访问位于复制节点上的数据库，我们需要创建一个与 ProxySQL 具有相同凭据的用户帐户，并授予该用户必要的权限。

与步骤 3 一样，以下步骤必须仅对组 的单个成员 执行。您可以选择任何一名成员。

创建一个名为 playgrounduser 的新用户，使用密码 playgroundpassword 标识。

CREATE USER 'playgrounduser'@'%' IDENTIFIED BY 'playgroundpassword';

授予它从原始组复制教程完全访问playground测试数据库的权限。

GRANT ALL PRIVILEGES on playground.* to 'playgrounduser'@'%';

然后应用更改并退出提示。

FLUSH PRIVILEGES;
EXIT;

您可以通过直接在节点上尝试使用新配置的凭据访问数据库来验证用户是否已正确创建。

使用新用户重新打开 MySQL 界面，这将提示您输入密码。

mysql -u playgrounduser -p

登录后，对 playground 数据库执行测试查询。

SHOW TABLES FROM playground;

Output+----------------------+
| Tables_in_playground |
+----------------------+
| equipment            |
+----------------------+
1 row in set (0.00 sec)

显示在原始复制教程中创建的 equipment 表的数据库中表的可见列表确认用户已在节点上正确创建。

您现在可以断开与 MySQL 界面的连接，但保持与服务器的连接处于打开状态的终端。我们将在最后一步使用它来运行测试。

EXIT;

现在我们需要在 ProxySQL 服务器中创建相应的用户。

第 7 步 — 创建 ProxySQL 用户

最后的配置步骤是允许使用 playgrounduser 用户连接到 ProxySQL，并将这些连接传递给节点。

为此，我们需要在保存用户凭证信息的 mysql_users 表中设置配置变量。在ProxySQL界面中，将用户名、密码和默认主机组添加到配置数据库（即2，对于writer主机组）

INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('playgrounduser', 'playgroundpassword', 2);

将配置迁移到 runtime 并保存到 disk 以使新配置生效。

LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

要验证我们是否可以使用这些凭据连接到数据库节点，请打开另一个终端窗口并通过 SSH 连接到 ProxySQL 服务器。稍后我们仍然需要管理提示，所以暂时不要关闭它。

ssh sammy@your_proxysql_server_ip

ProxySQL 在端口 6033 上侦听传入的客户端连接，因此请尝试使用 playgrounduser 和端口 6033 连接到真实数据库（不是管理界面）。系统将提示您输入密码，在我们的示例中为 playgroundpassword。

mysql -u playgrounduser -p -h 127.0.0.1 -P 6033 --prompt='ProxySQLClient> '

在这里，我们将提示设置为 ProxySQLClient>，以便我们可以将其与管理界面提示区分开来。我们将在测试最终配置时使用两者。

提示应该打开，这意味着凭据已被 ProxySQL 本身接受。

ProxySQL client promptWelcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 31
Server version: 5.5.30 (ProxySQL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

ProxySQLClient>

让我们执行一个简单的语句来验证 ProxySQL 是否会连接到其中一个节点。此命令在数据库中查询它正在运行的服务器的主机名，并返回服务器主机名作为唯一的输出。

SELECT @@hostname;

根据我们的配置，这个查询应该由 ProxySQL 定向到我们分配给 writer 主机组的三个节点之一。输出应如下所示，其中 member1 是 MySQL 节点之一的主机名。

Output+------------+
| @@hostname |
+------------+
| member1    |
+------------+
1 row in set (0.00 sec)

这样就完成了允许 ProxySQL 对三个 MySQL 节点之间的连接进行负载平衡的配置。

在最后一步，我们将验证 ProxySQL 是否可以在数据库上执行读写语句，并且即使在某些节点出现故障时它也能处理查询。

第 8 步 — 验证 ProxySQL 配置

我们知道 ProxySQL 和 MySQL 节点之间的连接是正常的，所以最后的测试是确保数据库权限允许来自 ProxySQL 的读取和写入语句，并确保这些语句在某些节点仍然执行时小组失败。

在 ProxySQL 客户端提示符中执行 SELECT 语句，验证我们是否可以从 playground 数据库中读取数据。

SELECT * FROM playground.equipment;

输出应类似于以下内容，其中包含在组复制教程中创建的三个项目。这意味着我们成功地通过 ProxySQL 从 MySQL 数据库中读取了数据。

Output+----+--------+-------+--------+
| id | type   | quant | color  |
+----+--------+-------+--------+
|  3 | slide  |     2 | blue   |
| 10 | swing  |    10 | yellow |
| 17 | seesaw |     3 | green  |
+----+--------+-------+--------+
3 rows in set (0.00 sec)

接下来，尝试通过在代表 5 个红色钻头的表中插入一些新数据来编写。

INSERT INTO playground.equipment (type, quant, color) VALUES ("drill", 5, "red");

然后重新执行之前的SELECT命令，验证数据是否已经插入。

SELECT * FROM playground.equipment;

输出中的新钻行意味着我们通过 ProxySQL 成功地将数据写入 MySQL 数据库。

Output+----+--------+-------+--------+
| id | type   | quant | color  |
+----+--------+-------+--------+
|  3 | slide  |     2 | blue   |
| 10 | swing  |    10 | yellow |
| 17 | seesaw |     3 | green  |
| 24 | drill  |     5 | red    |
+----+--------+-------+--------+
4 rows in set (0.00 sec)

我们知道 ProxySQL 现在可以完全使用数据库，但是如果服务器出现故障会发生什么？

从其中一台 MySQL 服务器的命令行中，停止 MySQL 进程以模拟故障。

systemctl stop mysql

数据库停止后，尝试从 ProxySQL 客户端提示符再次查询 equipment 表中的数据。

SELECT * FROM playground.equipment;

输出不应改变；您仍然应该像以前一样看到设备列表。这意味着 ProxySQL 已经注意到其中一个节点发生故障并切换到另一个节点来执行该语句。

我们可以通过从 ProxySQL 管理提示中查询 runtime_mysql_servers 表来检查这一点，就像在步骤 5 中一样。

SELECT hostgroup_id, hostname, status FROM runtime_mysql_servers;

输出将如下所示：

Output+--------------+-------------+---------+
| hostgroup_id | hostname    | status  |
+--------------+-------------+---------+
| 1            | 203.0.113.1 | SHUNNED |
| 2            | 203.0.113.2 | ONLINE  |
| 2            | 203.0.113.3 | ONLINE  |
| 3            | 203.0.113.2 | ONLINE  |
| 3            | 203.0.113.3 | ONLINE  |
+--------------+-------------+---------+
6 rows in set (0.01 sec)

我们停止的节点现在是 shunned，这意味着它暂时被视为不可访问，因此所有流量将分布在剩余的两个在线节点上。

ProxySQL 会持续监控这个节点的状态，如果它运行正常，则将其恢复为 online，如果它超过了我们在步骤 4 中设置的超时阈值，则将其标记为 offline。

让我们测试一下这个监控。切换回 MySQL 服务器并重新启动节点。

systemctl start mysql

稍等片刻，然后再次从 ProxySQL 管理提示中查询 runtime_mysql_servers 表。

SELECT hostgroup_id, hostname, status FROM runtime_mysql_servers;

ProxySQL 会很快注意到节点再次可用并将其标记为在线：

Output+--------------+-------------+--------+
| hostgroup_id | hostname    | status |
+--------------+-------------+--------+
| 2            | 203.0.113.1 | ONLINE |
| 2            | 203.0.113.2 | ONLINE |
| 2            | 203.0.113.3 | ONLINE |
| 3            | 203.0.113.1 | ONLINE |
| 3            | 203.0.113.2 | ONLINE |
| 3            | 203.0.113.3 | ONLINE |
+--------------+-------------+--------+
6 rows in set (0.01 sec)

您可以对另一个节点（或其中两个）重复此测试，以查看是否至少有一个节点启动，您将能够自由地使用您的数据库进行只读和读写访问。

结论

在本教程中，您将 ProxySQL 配置为跨多主组复制拓扑中的多个启用写入的 MySQL 节点对 SQL 查询进行负载平衡。这种配置可以通过将负载分布在多个服务器上来提高大量数据库使用的性能。如果其中一台数据库服务器脱机，它还可以提供故障转移功能。

但是，我们在这里仅以一个节点拓扑为例。 ProxySQL 还为许多其他 MySQL 拓扑提供强大的查询缓存、路由和性能分析。您可以在官方 ProxySQL 博客和 ProxySQL wiki 上阅读更多关于 ProxySQL 的功能以及如何使用它们解决不同数据库管理问题的信息。

如何在Ubuntu16.04上使用ProxySQL作为MySQL的负载均衡器

介绍