介绍

ProxySQL是一个开源的MySQL代理服务器,这意味着它作为MySQL服务器和访问其数据库的应用程序之间的中介服务器。

在本指南中,您将将 ProxySQL 设置为多个 MySQL 服务器的负载平衡器(https://andsky.com/tech/tutorials/what-is-load-balancing)。

前提条件

要遵循本教程,您将需要:

一个 Ubuntu 16.04 服务器安装了这个初始的 Ubuntu 16.04 服务器安装教程,包括一个 sudo 非根用户和防火墙. 这个服务器将成为您的 ProxySQL 实例。
三个 MySQL 服务器配置成一个多原始复制组。

第1步:安装ProxySQL

ProxySQL 的开发者在他们的 GitHub 发布页面上提供所有 ProxySQL 版本的官方 Ubuntu 包,所以我们会从那里下载最新的包版本并安装它。

你可以在 [发布列表]中找到最新的包(https://github.com/sysown/proxysql/releases)。命名公约是 proxysql_version-distribution.deb,其中 version 是版本 1.4.4 的类似字符串,而 distribution 是 64 位 Ubuntu 16.04 的类似字符串。

下载最新官方包,该包在写入时为 1.4.4 到 /tmp 目录。

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

安装使用 dpkg 的包,用于管理 .deb 软件包。

1sudo dpkg -i proxysql_*

此时,您不再需要.deb 文件,因此您可以删除它。

1rm proxysql_*

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

更新您的包库,以确保您获得最新的预组装版本,然后安装mysql-client包。

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

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

1sudo systemctl start proxysql

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

1systemctl status proxysql

结果将看起来像这样:

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

活跃(运行)行表示 ProxySQL 已安装并运行。

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

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

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

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

首先,访问管理界面. 您将被要求寻找密码,在默认安装时,它是admin。

1mysql -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' 以明确地说明

一旦连接,你会看到ProxySQLAdmin>提示:

 1[secondary_label ProxySQL administration console prompt]
 2Welcome to the MySQL monitor. Commands end with ; or \g.
 3Your MySQL connection id is 2
 4Server version: 5.5.30 (ProxySQL Admin Module)
 5
 6Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
 7
 8Oracle is a registered trademark of Oracle Corporation and/or its
 9affiliates. Other names may be trademarks of their respective
10owners.
11
12Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
13
14ProxySQLAdmin>

更改管理帐户密码,更新(‘UPDATE’)在‘global_variables’数据库中的‘admin-admin_credentials’配置变量。

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

1[secondary_label Output]
2Query OK, 1 row affected (0.00 sec)

由于ProxySQL配置系统的运作方式,这种变化不会立即生效,它由三个单独的层组成:

** 内存**,在从命令行接口进行更改时发生变化。
** 运行时间**,由ProxySQL作为有效配置使用。

现在,您所做的更改是在 memory. 要执行更改,您必须将 memory设置复制到 runtime领域,然后将其保存到 disk以使其持续。

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

这些ADMIN命令只处理与管理命令行接口相关的变量。ProxySQL暴露了类似的命令,如MYSQL,以处理其配置的其他部分。

现在ProxySQL已安装并使用新的管理密码运行,让我们设置3个MySQL节点,以便ProxySQL可以监控它们。

步骤 3 – 在 MySQL 中配置监控

ProxySQL 需要与 MySQL 节点进行通信,以便能够评估它们的状态. 要做到这一点,它必须能够与每个服务器连接到一个专用用户。

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

由于 MySQL 组复制已经在运行,所以以下步骤只需在 组中的一个成员上执行。

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

1[environment local]
2ssh sammy@your_mysql_server_ip_1

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

1[environment second]
2curl -OL https://gist.github.com/lefred/77ddbde301c72535381ae7af9f968322/raw/5e40b03333a3c148b78aa348fd2cd5b5dbb36e4d/addition_to_sys.sql

美元(注)

**注:此文件由ProxySQL作者提供,但以Ad-hoc方式:它是在个人GitHub存储库中的 gist,这意味着它可能会移动或过时,在未来,它可能会被添加为官方ProxySQL存储库的版本文件。

您可以阅读有关本文件的背景和内容的更多信息,请参阅作者关于 Native ProxySQL support for MySQL group replication的博客文章。

美元

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

當您準備好時,執行檔案中的命令,您將被要求使用 MySQL 管理密碼。

1[environment second]
2mysql -u root -p < addition_to_sys.sql

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

接下来,我们必须创建一个专用用户,该用户将被ProxySQL用于监控实例的状态。

打开 MySQL 交互式提示,它会再次提示您使用 root 密码。

1[environment second]
2mysql -u root -p

然后创建专用用户,我们在这里称之为 monitor. 请确保将密码更改为强大的密码。

1[environment second]
2CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitorpassword';

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

1[environment second]
2GRANT SELECT on sys.* to 'monitor'@'%';

最后,应用这些变化。

1[environment second]
2FLUSH PRIVILEGES;

由于群组复制,一旦您完成将用户添加到一个MySQL节点,将完全配置在所有三个节点上。

接下来,我们需要更新ProxySQL,以便该用户可以访问MySQL节点。

步骤 4 – 在 ProxySQL 中配置监控

要配置 ProxySQL 以便在监控节点时使用新用户帐户,我们将更新相应的配置变量,这与我们从步骤 2 设置管理员密码的方式非常相似。

在 ProxySQL 管理界面中,更新mysql-monitor_username变量到新帐户的用户名。

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

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

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

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

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

要让 ProxySQL 知道我们三个 MySQL 节点,我们需要告诉 ProxySQL 如何在其 _host 组中分配它们,这些节点被指定为集合。

在静态复制配置中,主机组可以任意设置,但ProxySQL的组复制支持会自动将复制组中的所有节点分为四个逻辑状态:

writers,这些是MySQL节点,可以接受改变数据的查询。ProxySQL确保在这个组中维持所有主要节点的最大定义数量。
backup writers,这些节点也是MySQL节点,可以接受改变数据的查询。然而,这些节点不被指定为编写者;超出所定义的编写者数量的主要节点被保留在这个组中,并被推广给编写者,如果其中一个编写者失败。
readers是MySQL节点,不能接受变更数据的查询,应该被用作只读节点。

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

在这里,我们使用1为离线主机组,2为writer主机组,3为reader主机组,4为backup writer主机组。

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

1INSERT 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);

以下是本行中设置的额外变量以及每个变量所做的:

*活跃设置为1允许ProxySQL对这些主机组进行监控。 *max_writers定义了有多少节点可以作为作家。我们在这里使用了3,因为在多元主机配置中,所有节点都可以被平等对待,所以在这里我们使用了3(节点总数)。 *writer_is_also_reader设置为1指示ProxySQL也将作家视为读者。

<$>[注] **注:**由于我们的示例使用了一种多主层 topology,其中所有节点都可以写到数据库中,我们将平衡所有SQL查询在 writer主机组中。

美元

现在ProxySQL 知道如何在主机组中分发节点,我们可以将我们的MySQL服务器添加到池中。为了做到这一点,我们需要将每个服务器的 IP 地址和初始主机组插入到mysql_servers表中,其中包含了 ProxySQL 可以互动的服务器列表。

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

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

在这里,2值将所有这些节点设置为最初的编写者,而3306则设置了默认的MySQL端口。

就像以前一样,将这些更改迁移到 ** Runtime**,并将其保存到 disk以使更改生效。

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

ProxySQL 现在应该按照规定的方式在主机组中分配我们的节点,让我们通过对runtim330e_mysql_servers表执行SELECT查询来验证,这显示了 ProxySQL 正在使用的服务器的当前状态。

1SELECT hostgroup_id, hostname, status FROM runtime_mysql_servers;

 1[secondary_label Output]
 2+--------------+-------------+--------+
 3| hostgroup_id | hostname    | status |
 4+--------------+-------------+--------+
 5| 2            | 203.0.113.1 | ONLINE |
 6| 2            | 203.0.113.2 | ONLINE |
 7| 2            | 203.0.113.3 | ONLINE |
 8| 3            | 203.0.113.1 | ONLINE |
 9| 3            | 203.0.113.2 | ONLINE |
10| 3            | 203.0.113.3 | ONLINE |
11+--------------+-------------+--------+
126 rows in set (0.01 sec)

在结果表中,每个服务器都列出两次:每次为主机组ID2和3,表示所有三个节点都是作家和读者。

然而,在我们能够使用它们之前,我们必须配置用户凭证来访问每个节点上的MySQL数据库。

第6步:创建MySQL用户

ProxySQL 作为负载平衡器;最终用户连接到 ProxySQL,而 ProxySQL 将连接转移到所选的 MySQL 节点。

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

与步骤 3 一样,以下步骤只必须在组中的一个成员中执行,您可以选择一个成员。

创建一个名为playgrounduser的新用户,用playgroundpassword的密码识别。

1[environment second]
2CREATE USER 'playgrounduser'@'%' IDENTIFIED BY 'playgroundpassword';

从原始组复制教程中完全访问游乐场测试数据库。

1[environment second]
2GRANT ALL PRIVILEGES on playground.* to 'playgrounduser'@'%';

然后应用更改并退出快递。

1[environment second]
2FLUSH PRIVILEGES;
3EXIT;

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

重新打开 MySQL 界面与新用户,这将提示您密码。

1[environment second]
2mysql -u playgrounduser -p

当您登录时,在游乐场数据库中执行测试查询。

1[environment second]
2SHOW TABLES FROM playground;

1[secondary_label Output]
2[environment second]
3+----------------------+
4| Tables_in_playground |
5+----------------------+
6| equipment            |
7+----------------------+
81 row in set (0.00 sec)

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

您现在可以脱离MySQL界面,但将终端与连接到服务器保持开放,我们将在最后一步中使用它来运行测试。

1[environment second]
2EXIT;

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

第7步:创建ProxySQL用户

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

要做到这一点,我们需要在mysql_users表中设置配置变量,其中包含用户凭证信息。在ProxySQL界面中,将用户名、密码和默认主机组添加到配置数据库(为writer**主机组的2)

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

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

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

要验证我们可以使用这些凭证连接到数据库节点,请打开另一个终端窗口和SSH到ProxySQL服务器。

1[environment local]
2ssh sammy@your_proxysql_server_ip

ProxySQL在端口6033上聆听到接入的客户端连接,所以尝试使用 playgrounduser和端口6033连接到实际数据库(而不是管理界面)。

1[environment third]
2mysql -u playgrounduser -p -h 127.0.0.1 -P 6033 --prompt='ProxySQLClient> '

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

提示应打开,这意味着凭证已被ProxySQL本身接受。

 1[secondary_label ProxySQL client prompt]
 2[environment third]
 3Welcome to the MySQL monitor. Commands end with ; or \g.
 4Your MySQL connection id is 31
 5Server version: 5.5.30 (ProxySQL)
 6
 7Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
 8
 9Oracle is a registered trademark of Oracle Corporation and/or its
10affiliates. Other names may be trademarks of their respective
11owners.
12
13Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
14
15ProxySQLClient>

让我们执行一个简单的陈述来验证ProxySQL是否将连接到其中一个节点. 这个命令会查询该服务器的托管名称的数据库,并返回服务器托管名称作为唯一的输出。

1[environment third]
2SELECT @@hostname;

根据我们的配置,这个查询应该被ProxySQL导向到我们分配给 writer主机组的三个节点之一,输出应该看起来如下,其中member1是MySQL节点之一的主机名。

1[secondary_label Output]
2[environment third]
3+------------+
4| @@hostname |
5+------------+
6| member1    |
7+------------+
81 row in set (0.00 sec)

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

在最后一步中,我们将验证ProxySQL能够在数据库中执行读写陈述,并且即使某些节点下降,它也会处理查询。

步骤 8 – 验证 ProxySQL 配置

我们知道ProxySQL和MySQL节点之间的连接是有效的,所以最后的测试是确保数据库权限允许从ProxySQL读取和写入陈述,并确保这些陈述仍然在组中的某些节点失败时执行。

在 ProxySQL 客户端提示中执行一个SELECT声明,以验证我们可以从游戏场数据库中读取数据。

1[environment third]
2SELECT * FROM playground.equipment;

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

 1[secondary_label Output]
 2[environment third]
 3+----+--------+-------+--------+
 4| id | type   | quant | color  |
 5+----+--------+-------+--------+
 6|  3 | slide  |     2 | blue   |
 7| 10 | swing  |    10 | yellow |
 8| 17 | seesaw |     3 | green  |
 9+----+--------+-------+--------+
103 rows in set (0.00 sec)

接下来,尝试通过将一些新数据插入表中,代表5个红色钻探。

1[environment third]
2INSERT INTO playground.equipment (type, quant, color) VALUES ("drill", 5, "red");

然后重新执行之前的SELECT命令,以验证数据已被插入。

1[environment third]
2SELECT * FROM playground.equipment;

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

 1[secondary_label Output]
 2[environment third]
 3+----+--------+-------+--------+
 4| id | type   | quant | color  |
 5+----+--------+-------+--------+
 6|  3 | slide  |     2 | blue   |
 7| 10 | swing  |    10 | yellow |
 8| 17 | seesaw |     3 | green  |
 9| 24 | drill  |     5 | red    |
10+----+--------+-------+--------+
114 rows in set (0.00 sec)

我们知道ProxySQL现在可以充分利用数据库,但如果服务器失败会发生什么?

从 MySQL 服务器中的一个命令行中,停止 MySQL 流程来模拟故障。

1[environment second]
2systemctl stop mysql

数据库停止后,尝试从 ProxySQL 客户端提示器中再次查询设备表中的数据。

1[environment third]
2SELECT * FROM playground.equipment;

输出不应该改变;你仍然应该像以前一样看到设备列表,这意味着ProxySQL注意到其中一个节点失败并切换到另一个节点来执行声明。

我们可以通过从 ProxySQL 管理提示中查询 runtime_mysql_servers 表来验证这一点,如在步骤 5 中所示。

1SELECT hostgroup_id, hostname, status FROM runtime_mysql_servers;

结果将是这样的:

 1[secondary_label Output]
 2+--------------+-------------+---------+
 3| hostgroup_id | hostname    | status  |
 4+--------------+-------------+---------+
 5| 1            | 203.0.113.1 | SHUNNED |
 6| 2            | 203.0.113.2 | ONLINE  |
 7| 2            | 203.0.113.3 | ONLINE  |
 8| 3            | 203.0.113.2 | ONLINE  |
 9| 3            | 203.0.113.3 | ONLINE  |
10+--------------+-------------+---------+
116 rows in set (0.01 sec)

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

ProxySQL 将不断监控此节点的状态,如果它正常行为,将其带回 online,或将其标记为 offline,如果它超过了我们在步骤 4 中设置的时限。

讓我們來測試這個監控,轉回MySQL伺服器,並將節點備份。

1[environment second]
2systemctl start mysql

等待一会儿,然后从 ProxySQL 管理提示器中再次查询runtime_mysql_servers表。

1SELECT hostgroup_id, hostname, status FROM runtime_mysql_servers;

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

 1[secondary_label Output]
 2+--------------+-------------+--------+
 3| hostgroup_id | hostname    | status |
 4+--------------+-------------+--------+
 5| 2            | 203.0.113.1 | ONLINE |
 6| 2            | 203.0.113.2 | ONLINE |
 7| 2            | 203.0.113.3 | ONLINE |
 8| 3            | 203.0.113.1 | ONLINE |
 9| 3            | 203.0.113.2 | ONLINE |
10| 3            | 203.0.113.3 | ONLINE |
11+--------------+-------------+--------+
126 rows in set (0.01 sec)

您可以用另一个节点(或两个节点)重复这个测试,以便看到如果至少有一个节点已启用,您将能够自由地使用您的数据库,无论是只读还是写入。

结论

在本教程中,您配置了 ProxySQL 以在多元主组复制 topology 中跨多个编写功能的 MySQL 节点加载 SQL 查询平衡。这种配置可以通过在多个服务器上分配负载来提高重型数据库使用的性能。

然而,我们这里只介绍了一个节点 topology 作为一个例子。ProxySQL 还为许多其他 MySQL topologies 提供强大的查询缓存、路由和性能分析,您可以在官方 ProxySQL 博客和 ProxySQL 维基上阅读更多关于 ProxySQL 的功能以及如何使用它们来解决不同的数据库管理问题。

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

介绍