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

2018-01-08 分类：系统运维 阅读() 评论()

介绍

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

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

先决条件

要学习本教程，您将需要：

第1步 - 安装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命令行工具，它是Ubuntu存储库中可用的mysql-client软件包的一部分。

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

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

您现在具有运行ProxySQL的所有要求，但是该服务在安装后不会自动启动，因此现在就手动启动它。 

sudo systemctl start proxysql

ProxySQL现在应该在其默认配置下运行。 你可以使用systemctl来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>

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

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

OutputQuery OK, 1 row affected (0.00 sec)

由于ProxySQL的配置系统的工作原理，这个改变不会立即生效。 它由三个独立的层组成：

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

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

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

这些ADMIN命令仅处理与管理命令行界面相关的变量。 ProxySQL公开了像MYSQL一样的命令来处理其他配置的一部分。 我们稍后将在本教程中使用它们。

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

第3步 - 在MySQL中配置监控

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

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

由于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仓库中的要点 ，这意味着它有可能会移动或过时。 将来，它可能会作为版本化的文件添加到官方ProxySQL存储库中。

您可以在作者的博客文章中阅读关于本地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服务器的状态。 

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';

就像以前一样，配置不会自动应用，因此将其迁移到运行时并保存到磁盘 。 这一次，请注意，我们使用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的组复制支持自动将复制组中的所有节点分成四种逻辑状态：

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

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

综合起来，我们需要告诉ProxySQL每个状态应该使用哪个标识符。 在这里，我们使用1用于脱机主机组， 2用于写入主机组， 3用于读取器主机组， 4用于备用写入器主机组。

要设置这些标识符，请在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定义了多少节点可以充当max_writers者。 我们在这里使用3 ，因为在多主配置中，所有节点都可以被视为相等，所以这里我们使用了3 （节点总数）。
  • writer_is_also_reader设置为1指示ProxySQL将作者视为读者。
  • max_transactions_behind设置节点被分类为脱机之前的最大延迟事务数。

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

现在ProxySQL知道如何在主机组之间分配节点，我们可以将我们的MySQL服务器添加到池中。 为此，我们需要将每个服务器的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端口。

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

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)

在结果表中，每个服务器列出两次：每个主机组标识为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 ） 

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

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

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指向分配给写入主机组的三个节点之一。 输出应该如下所示，其中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)

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

ProxySQL将持续监视此节点的状态，如果它正常运行，则将其恢复到联机 状态，如果超出了第4步中设置的超时阈值，则将其标记为脱机状态 。

我们来测试这个监控。 切换回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博客和ProxySQL wiki上解决与他们不同的数据库管理问题。

赞(52) 打赏
未经允许不得转载：优客志 » 系统运维
分享到：
标签：

相关推荐

吐血推荐

推荐云主机，建站更安全

优客志推荐站长使用阿里云主机，安全可靠、轻松上云，从此告别主机无售后、主机各种难题。点击领取1000元代金券

热度排行

热门标签

Ubuntu (2170) Linux (1251) debian (936) centos (935) Security (686) Apache (652) Nginx (536) Desktop (533) MySQL (492) web-server (486) PHP (466) fedora (446) Linux Commands (383) virtualization (308) ispconfig (297) Ubuntu 16.04 (285) Storage (284) JAVA (279) monitoring (243) server (231) Open Source (210) suse (201) Control Panels (186) Email (182) python (182) shell (179) FTP (175) Postfix (174) Linux Tricks (149) Development (137)

Copyright © 2018 优客网 All Rights Reserved

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏