如何在Ubuntu 16.04上使用ProxySQL作为MySQL的负载均衡器

介绍

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

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

准备

要学习本教程，您需要：

• 一个Ubuntu 16.04服务器，具有sudo权限的非root用户。该服务器将成为您的ProxySQL实例。
• 配置三个MySQL服务器以形成多主复制组。
• ProxySQL的基础知识

第一步 - 安装ProxySQL

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

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

将最新的官方软件包（编写本文时为1.4.4）下载到/tmp目录中。

$ 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命令行工具，它是mysql-client Ubuntu存储库中可用包的一部分。

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

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

您现在满足运行ProxySQL的所有要求，但该服务在安装后不会自动启动，因此请立即手动启动。

$ sudo systemctl start proxysql

现在，ProxySQL应该以其默认配置运行。你可以使用systemctl查看。

$ systemctl status proxysql

输出看起来像这样：

● 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管理界面的密码来提高安全性。

第二步 - 设置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假定的socket文件。
• -P指定要连接的端口。ProxySQL的管理界面监听6032。
• --prompt是一个可选标志，用于更改默认提示，通常是mysql>。在这里，我们将其更改为ProxySQLAdmin>显示我们已连接到ProxySQL管理界面。这将有助于避免以后在我们也将连接到复制数据库服务器上的MySQL接口时出现混淆。

连接后，您将看到ProxySQLAdmin>提示：

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

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

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

Query OK, 1 row affected (0.00 sec)

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

• 内存，在从命令行界面进行修改时会被更改。
• 运行时，ProxySQL使用它作为有效配置。
• 磁盘，用于使配置在重新启动时保持不变。

现在，你所做的改变是在内存中。要使更改生效，您必须将内存设置复制到运行时领域，然后将它们保存到磁盘以使其保持不变。

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

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

现在ProxySQL已经安装并运行了新的管理员密码，让我们设置3个MySQL节点，以便ProxySQL可以监控它们。但是，请保持ProxySQL接口处于打开状态，因为我们稍后会使用它。

第三步 - 在MySQL中配置监控

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

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

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

在第二个终端中，使用其中一个MySQL节点登录服务器。

$ ssh sammy\@your_mysql_server_ip_1

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

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

注意：此文件由ProxySQL作者提供，但是以临时方式提供，这意味着它可能会移动或过期。将来，它可能会作为官方ProxySQL存储库中的版本化文件添加。

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

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

$ mysql -u root -p < addition_to_sys.sql

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

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

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

$ mysql -u root -p

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

(member1) mysql> CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitorpassword';

授予用户权限以向监视器用户查询MySQL服务器的状况。

(member1) mysql> GRANT SELECT on sys.* to 'monitor'@'%';

最后，应用更改。

(member1) mysql> FLUSH PRIVILEGES;

由于组复制，一旦您将用于运行状况监视的用户添加到一个MySQL节点，它将在所有三个节点上完全配置。

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

第四步 - 在ProxySQL中配置监控

要在监控节点时将ProxySQL配置为使用新用户帐户，我们将UPDATE相应的配置变量。这与我们从第二步设置管理员密码的方式非常相似。

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

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

与以前一样，配置不会自动应用，因此将其迁移到运行时并保存到磁盘。这一次，请注意我们正在使用MYSQL而不是ADMIN更新这些变量，因为我们正在修改MySQL配置变量。

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

监控帐户在所有端配置，下一步是告诉ProxySQL节点本身。

第五步 - 将MySQL节点添加到ProxySQL服务器池

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

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

• 编写器，它是可以接受更改数据的查询的MySQL节点。ProxySQL确保将所有主节点维持到该组中的最大定义数量。
• 备份编写器，也是可以接受更改数据的查询的MySQL节点。但是，这些节点不被指定为编写器; 超过定义的维护编写器数量的主节点保留在该组中，如果其中一个编写器失败，则将其提升为编写器。
• 读取器是MySQL节点，不能接受更改数据的查询，应该用作只读节点。ProxySQL只在此处放置从属节点。
• 离线，用于由于缺乏连接或流量缓慢等问题而导致行为不正常的节点。

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

总而言之，我们需要告诉ProxySQL它应该为每个状态使用哪些标识符。在这里，我们使用1为离线主机组，2为编写器主机组，3为读取器主机组，4为备份编写器主机组。

要设置这些标识符，请在mysql_group_replication_hostgroups配置表中创建包含这些变量和值的新行。

ProxySQLAdmin> 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设置节点被分类为脱机之前的最大延迟事务数。

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

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

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

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

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

与以前一样，将这些更改迁移到运行时并将其保存到磁盘以使更改生效。

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

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

ProxySQLAdmin> SELECT hostgroup_id, hostname, status FROM runtime_mysql_servers;

+--------------+-------------+--------+
| 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数据库。

第六步 - 创建MySQL用户

ProxySQL充当负载均衡器; 最终用户连接到ProxySQL，ProxySQL依次将连接传递给所选的MySQL节点。要连接到单个节点，ProxySQL将重用其访问过的凭据。

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

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

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

(member1) mysql> CREATE USER 'playgrounduser'@'%' IDENTIFIED BY 'playgroundpassword';

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

(member1) mysql> GRANT ALL PRIVILEGES on playground.* to 'playgrounduser'@'%';

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

(member1) mysql>FLUSH PRIVILEGES;
(member1) mysql>EXIT;

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

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

$ mysql -u playgrounduser -p

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

(member1) mysql> SHOW TABLES FROM playground;

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

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

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

(member1) mysql> EXIT;

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

第七步 - 创建ProxySQL用户

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

为此，我们需要在mysql_users表中设置配置变量，其中包含用户凭据信息。在ProxySQL接口，添加用户名，密码，和默认主机组的配置数据库（其是2，对于编写器主机组）

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

将配置迁移到运行时并保存到磁盘以使新配置生效。

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

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

$ ssh sammy@your_proxysql_server_ip

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

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

在这里，我们将提示设置为ProxySQLClient>，以便我们可以将其与管理界面提示区分开来。

我们将在测试最终配置时使用它们。

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

ProxySQL client prompt
Welcome 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是否会连接到其中一个节点。此命令在数据库中查询运行的服务器的主机名，并返回服务器主机名作为唯一输出。

ProxySQLClient> SELECT @@hostname;

根据我们的配置中，这个查询应该由ProxySQL被引导到我们分配到三个节点的一个编写器主机组。输出应如下所示，其中member1是一个MySQL节点的主机名。

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

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

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

第八步 - 验证ProxySQL配置

我们知道ProxySQL和MySQL节点之间的连接正在工作，因此最终的测试是确保数据库权限允许来自ProxySQL的读写语句，并确保在某些节点中仍然执行这些语句小组失败了。

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

ProxySQLClient> SELECT * FROM playground.equipment;

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

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

接下来，尝试通过在代表5 red drills的表中插入一些新数据来编写。

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

然后重新执行上一个SELECT命令以验证是否已插入数据。

ProxySQLClient> SELECT * FROM playground.equipment;

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

+----+--------+-------+--------+
| 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表中的数据。

ProxySQLClient> SELECT * FROM playground.equipment;

输出不应改变; 你仍然应该像以前一样看到设备清单。这意味着ProxySQL注意到其中一个节点出现故障并切换到另一个节点以执行该语句。

我们可以通过从ProxySQL管理提示中查询runtime_mysql_servers表来检查它，就像在第五步中一样。

ProxySQLADmin> SELECT hostgroup_id, hostname, status FROM runtime_mysql_servers;

输出将如下所示：

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

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

ProxySQL将持续监视此节点的状态，如果行为正常则将其恢复为联机状态，或者如果超过我们在第四步中设置的超时阈值则将其标记为脱机。

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

$ systemctl start mysql

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

ProxySQLADmin> SELECT hostgroup_id, hostname, status FROM runtime_mysql_servers;

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

+--------------+-------------+--------+
| 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拓扑提供了强大的查询缓存，路由和性能分析。

参考文献：《How to Use ProxySQL as a Load Balancer for MySQL on Ubuntu 16.04》