TiDB 与 ProxySQL 集成

本文以 CentOS 7 为例,简单介绍 TiDBProxySQL 的集成方法。如果你有其他系统的集成需求,可参考快速体验使用 Docker 及 Docker Compose 部署测试集成环境。你也可以参考以下链接,以获得更多信息:

1. 启动 TiDB

测试环境

  • TiDB Cloud
  • 编译源码
  • TiUP
  1. 下载 TiDB 源码,进入 tidb-server 目录后,使用 go build 进行编译。

    git clone git@github.com:pingcap/tidb.git cd tidb/tidb-server go build
  2. 使用配置文件 tidb-config.toml 来启动 TiDB,命令如下所示:

    ${TIDB_SERVER_PATH} -config ./tidb-config.toml -store unistore -path "" -lease 0s > ${LOCAL_TIDB_LOG} 2>&1 &

TiUP 在 TiDB 中承担着包管理器的角色,管理着 TiDB 生态下众多的组件,如 TiDB、PD、TiKV 等。

  1. 安装 TiUP

    curl --proto '=https' --tlsv1.2 -sSf https://tiup-mirrors.pingcap.com/install.sh | sh
  2. 启动测试环境 TiDB

    tiup playground

正式环境

  • TiDB Cloud
  • TiUP 本地安装

在需要托管 TiDB 服务的前提下(如无法自行运维、需要云原生环境等),建议直接使用 TiDB Cloud。你可以参考 TiDB Cloud 的 Create a TiDB Cluster 在 TiDB Cloud 中部署正式环境下的 TiDB。

正式环境相对测试环境会复杂许多,建议参考使用 TiUP 部署 TiDB 集群并根据硬件条件部署。

2. 启动 ProxySQL

yum 安装

  1. 添加 RPM 仓库:

    cat > /etc/yum.repos.d/proxysql.repo << EOF [proxysql] name=ProxySQL YUM repository baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/centos/\$releasever gpgcheck=1 gpgkey=https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/repo_pub_key EOF
  2. 安装:

    yum install proxysql
  3. 启动:

    systemctl start proxysql

其他安装方式

参考 ProxySQL 的 Github 页面ProxySQL 官方文档进行安装。

3. 配置 ProxySQL

需要将 ProxySQL 内的配置指向 TiDB,以此将 ProxySQL 作为 TiDB 的代理。下面列举必需的配置项,其余配置项可参考 ProxySQL 官方文档

ProxySQL 配置简介

ProxySQL 使用一个单独的端口进行配置管理,另一个端口进行代理。其中,配置管理的入口称为 ProxySQL Admin interface,代理的入口称为 ProxySQL MySQL Interface

  • ProxySQL Admin interface:可以使用具有 admin 权限的用户连接到管理界面,以读取和写入配置,或者使用具有 stats 权限的用户,只能读取某些统计数据(不读取或写入配置)。默认凭证是 admin:adminstats:stats,但出于安全考虑,可以使用默认凭证进行本地连接。要远程连接,需要配置一个新的用户,通常它被命名为 radmin
  • ProxySQL MySQL Interface:用于代理,将 SQL 转发到配置的服务中。

proxysql config flow

ProxySQL 有三层配置:runtimememorydisk。你仅能更改 memory 层的配置。在更改配置后,可以使用 LOAD xxx TO runtime 来生效这个配置,也可以使用 SAVE xxx TO DISK 落盘,防止配置丢失。

proxysql config layer

配置 TiDB 后端

在 ProxySQL 中添加 TiDB 后端,此处如果有多个 TiDB 后端,可以添加多条。请在 ProxySQL Admin interface 进行此操作:

INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (0, '127.0.0.1', 4000); LOAD mysql servers TO runtime; SAVE mysql servers TO DISK;

字段解释:

  • hostgroup_id:ProxySQL 是以 hostgroup 为单位管理后端服务的,可以将需要负载均衡的几个服务配置为同一个 hostgroup,这样 ProxySQL 将均匀地分发 SQL 到这些服务上。而在需要区分不同后端服务时(如读写分离场景等),可将其配置为不同的 hostgroup,以此配置不同的代理条件。
  • hostname:后端服务的 IP 或域名。
  • port:后端服务的端口。

配置 Proxy 登录账号

在 ProxySQL 中添加 TiDB 后端的登录账号。ProxySQL 将允许此账号来登录 ProxySQL MySQL Interface,而且 ProxySQL 将以此创建与 TiDB 之间的连接,因此,请确保此账号在 TiDB 中拥有相应权限。请在 ProxySQL Admin interface 进行此操作:

INSERT INTO mysql_users(username, password, active, default_hostgroup, transaction_persistent) VALUES ('root', '', 1, 0, 1); LOAD mysql users TO runtime; SAVE mysql users TO DISK;

字段解释:

  • username:用户名。
  • password:密码。
  • active:是否生效。1 为生效,0 为不生效,仅 active = 1 的用户可登录。
  • default_hostgroup:此账号默认使用的 hostgroup,SQL 将被发送至此 hostgroup 中,除非查询规则将流量发送到不同的 hostgroup。
  • transaction_persistent:值为 1 时,表示事务持久化,即:当该用户在连接中开启了一个事务后,那么在事务提交或回滚之前,所有的语句都路由到同一个 hostgroup 中,避免语句分散到不同 hostgroup。

配置文件配置

除了使用 ProxySQL Admin interface 配置,也可以使用配置文件进行配置。ProxySQL 文档中,配置文件仅应该被视为是一种辅助初始化的方式,而并非主要配置的手段。配置文件仅在 SQLite 数据库未被创建时读取,后续将不会继续读取配置文件。因此,使用配置文件配置时,你应进行 SQLite 数据库的删除,这将丢失你在 ProxySQL Admin interface 中对配置进行的更改:

rm /var/lib/proxysql/proxysql.db

另外,也可以运行 LOAD xxx FROM CONFIG,用配置文件中的配置覆盖当前内存中的配置。

配置文件的位置为 /etc/proxysql.cnf,我们将上方的必需配置转换为配置文件方式,仅更改 mysql_serversmysql_users 这两个配置节点,其余配置可自行查看 /etc/proxysql.cnf

mysql_servers = ( { address="127.0.0.1" port=4000 hostgroup=0 max_connections=2000 } ) mysql_users: ( { username = "root" password = "" default_hostgroup = 0 max_connections = 1000 default_schema = "test" active = 1 transaction_persistent = 1 } )

随后使用 systemctl restart proxysql 进行服务重启后即可生效,配置生效后将自动创建 SQLite 数据库,后续将不会再次读取配置文件。

其余配置项

仅以上配置为必需配置项,其余配置项并非必需。你可在 ProxySQL Global Variables 中获取全部配置项的名称及作用。

4. 快速体验

在测试环境中,你可以使用 Docker 及 Docker Compose 快速进行集成后的环境体验,请确认 40006033 端口未被占用,然后执行如下命令:

git clone https://github.com/Icemap/tidb-proxysql-integration-test.git cd tidb-proxysql-integration-test && docker-compose pull # Get the latest Docker images sudo setenforce 0 # Only on Linux docker-compose up -d

这样就已经完成了一个集成了 TiDB 与 ProxySQL 环境的启动,这将启动两个容器。你可以使用用户名为 root,密码为空的账号,登录到本机的 6033 端口 (ProxySQL)。容器具体配置可见 docker-compose.yaml,ProxySQL 具体配置可见 proxysql-docker.cnf

运行如下命令:

mysql -u root -h 127.0.0.1 -P 6033 -e "SELECT VERSION()"

运行结果:

+--------------------+ | VERSION() | +--------------------+ | 5.7.25-TiDB-v6.1.0 | +--------------------+

5. 配置示例

配置示例的前提条件:

  • Docker
  • Docker Compose
  • MySQL Client

下载示例源码并进入目录:

git clone https://github.com/Icemap/tidb-proxysql-integration-test.git cd tidb-proxysql-integration-test

下面的示例均以 tidb-proxysql-integration-test 目录做为根目录。

使用 Admin Interface 配置负载均衡

进入本示例目录:

cd example/proxy-rule-admin-interface

脚本运行

ProxySQL Admin Interface 为配置入口,配置负载均衡场景为例。可使用以下命令运行脚本:

./test-load-balance.sh

逐步运行

  1. 通过 Docker Compose 启动三个 TiDB 容器实例,容器内部端口均为 4000,映射宿主机端口为 400140024003。TiDB 容器实例启动后,再启动一个 ProxySQL 实例,容器内部 ProxySQL MySQL Interface 端口为 6033,映射宿主机端口为 6034。不暴露 ProxySQL Admin Interface 端口,因为其仅可在本地(即容器内)登录 ProxySQL Admin Interface。此流程被写在 docker-compose.yaml 中。

    docker-compose up -d
  2. 在 3 个 TiDB 实例内,创建相同的表结构,但写入不同的数据:'tidb-0''tidb-1''tidb-2',以便分辨不同的数据库实例:

    mysql -u root -h 127.0.0.1 -P 4001 << EOF DROP TABLE IF EXISTS test.test; CREATE TABLE test.test (db VARCHAR(255)); INSERT INTO test.test (db) VALUES ('tidb-0'); EOF mysql -u root -h 127.0.0.1 -P 4002 << EOF DROP TABLE IF EXISTS test.test; CREATE TABLE test.test (db VARCHAR(255)); INSERT INTO test.test (db) VALUES ('tidb-1'); EOF mysql -u root -h 127.0.0.1 -P 4003 << EOF DROP TABLE IF EXISTS test.test; CREATE TABLE test.test (db VARCHAR(255)); INSERT INTO test.test (db) VALUES ('tidb-2'); EOF
  3. 使用 docker-compose exec 命令,在 ProxySQL Admin Interface 中运行事先准备好的配置 ProxySQL 的 SQL 文件

    docker-compose exec proxysql sh -c "mysql -uadmin -padmin -h127.0.0.1 -P6032 < ./proxysql-prepare.sql"

    此 SQL 文件将会运行:

    1. 添加 3 个 TiDB 后端的地址,并且 hostgroup_id 均为 0
    2. 令 TiDB 后端配置生效,并落盘保存。
    3. 添加用户 root,密码为空,default_hostgroup0,对应上方的 TiDB 后端 hostgroup_id
    4. 生效用户配置,并落盘保存。
  4. 使用 root 用户登录 ProxySQL MySQL Interface,连续查询 5 次数据,预期结果将有 'tidb-0''tidb-1''tidb-2' 三种不同的返回。

    mysql -u root -h 127.0.0.1 -P 6034 -t << EOF SELECT * FROM test.test; SELECT * FROM test.test; SELECT * FROM test.test; SELECT * FROM test.test; SELECT * FROM test.test; EOF
  5. 停止并清除 Docker Compose 启动的容器、网络拓扑等资源。

    docker-compose down

预期输出

因为负载均衡的原因,预期输出将有 'tidb-0''tidb-1''tidb-2' 三种不同的返回。但具体顺序未知。其中一种预期输出为:

# ./test-load-balance.sh Creating network "load-balance-admin-interface_default" with the default driver Creating load-balance-admin-interface_tidb-1_1 ... done Creating load-balance-admin-interface_tidb-2_1 ... done Creating load-balance-admin-interface_tidb-0_1 ... done Creating load-balance-admin-interface_proxysql_1 ... done +--------+ | db | +--------+ | tidb-2 | +--------+ +--------+ | db | +--------+ | tidb-0 | +--------+ +--------+ | db | +--------+ | tidb-1 | +--------+ +--------+ | db | +--------+ | tidb-1 | +--------+ +--------+ | db | +--------+ | tidb-1 | +--------+ Stopping load-balance-admin-interface_proxysql_1 ... done Stopping load-balance-admin-interface_tidb-0_1 ... done Stopping load-balance-admin-interface_tidb-2_1 ... done Stopping load-balance-admin-interface_tidb-1_1 ... done Removing load-balance-admin-interface_proxysql_1 ... done Removing load-balance-admin-interface_tidb-0_1 ... done Removing load-balance-admin-interface_tidb-2_1 ... done Removing load-balance-admin-interface_tidb-1_1 ... done Removing network load-balance-admin-interface_default

使用 Admin Interface 配置用户分离

进入本示例目录:

cd example/proxy-rule-admin-interface

脚本运行

ProxySQL Admin Interface 为配置入口,配置负载均衡配置用户分离场景为例,不同用户将使用不同的 TiDB 后端。可使用以下命令运行脚本:

./test-user-split.sh

逐步运行

  1. 通过 Docker Compose 启动两个 TiDB 容器实例,容器内部端口均为 4000,映射宿主机端口为 40014002。TiDB 实例启动后,再启动一个 ProxySQL 实例,容器内部 ProxySQL MySQL Interface 端口为 6033,映射宿主机端口为 6034。不暴露 ProxySQL Admin Interface 端口,因为其仅可在本地(即容器内)登录 ProxySQL Admin Interface。此流程被写在 docker-compose.yaml 中。

    docker-compose up -d
  2. 在 2 个 TiDB 实例内,创建相同的表结构,但写入不同的数据'tidb-0''tidb-1',以便分辨不同的数据库实例:

    mysql -u root -h 127.0.0.1 -P 4001 << EOF DROP TABLE IF EXISTS test.test; CREATE TABLE test.test (db VARCHAR(255)); INSERT INTO test.test (db) VALUES ('tidb-0'); EOF mysql -u root -h 127.0.0.1 -P 4002 << EOF DROP TABLE IF EXISTS test.test; CREATE TABLE test.test (db VARCHAR(255)); INSERT INTO test.test (db) VALUES ('tidb-1'); EOF
  3. 为 ProxySQL 在 tidb-1 实例中新建一个用户:

    mysql -u root -h 127.0.0.1 -P 4002 << EOF CREATE USER 'root1' IDENTIFIED BY ''; GRANT ALL PRIVILEGES ON *.* TO 'root1'@'%'; FLUSH PRIVILEGES; EOF
  4. 使用 docker-compose exec 命令,在 ProxySQL Admin Interface 中运行事先准备好的配置 ProxySQL 的 SQL 文件

    docker-compose exec proxysql sh -c "mysql -uadmin -padmin -h127.0.0.1 -P6032 < ./proxysql-prepare.sql"

    此 SQL 文件将会运行:

    1. 添加 2 个 TiDB 后端的地址,其中,tidb-0hostgroup_id0tidb-1hostgroup_id1
    2. 生效 TiDB 后端配置,并落盘保存。
    3. 添加用户 root,密码为空,default_hostgroup0,即默认将路由至 tidb-0
    4. 添加用户 root1,密码为空,default_hostgroup1,即默认将路由至 tidb-1
    5. 生效用户配置,并落盘保存。
  5. 分别使用 root 用户及 root1 用户登录 ProxySQL MySQL Interface,预期结果将为 'tidb-0''tidb-1'

    mysql -u root -h 127.0.0.1 -P 6034 -e "SELECT * FROM test.test;" mysql -u root1 -h 127.0.0.1 -P 6034 -e "SELECT * FROM test.test;"
  6. 停止并清除 Docker Compose 启动的容器、网络拓扑等资源。

    docker-compose down

预期输出

# ./test-user-split.sh Creating network "user-split-admin-interface_default" with the default driver Creating user-split-admin-interface_tidb-1_1 ... done Creating user-split-admin-interface_tidb-0_1 ... done Creating user-split-admin-interface_proxysql_1 ... done +--------+ | db | +--------+ | tidb-0 | +--------+ +--------+ | db | +--------+ | tidb-1 | +--------+ Stopping user-split-admin-interface_proxysql_1 ... done Stopping user-split-admin-interface_tidb-0_1 ... done Stopping user-split-admin-interface_tidb-1_1 ... done Removing user-split-admin-interface_proxysql_1 ... done Removing user-split-admin-interface_tidb-0_1 ... done Removing user-split-admin-interface_tidb-1_1 ... done Removing network user-split-admin-interface_default

使用 Admin Interface 配置代理规则

进入本示例目录:

cd example/proxy-rule-admin-interface

脚本运行

ProxySQL Admin Interface 为配置入口,代理规则场景中,常见的读写分离配置为例,将使用规则匹配将要运行的 SQL,从而将读、写 SQL 转发至不同的 TiDB 后端(若均未匹配,则使用用户的 default_hostgroup)。可使用以下命令运行脚本:

./proxy-rule-split.sh

逐步运行

  1. 通过 Docker Compose 启动两个 TiDB 容器实例,容器内部端口均为 4000,映射宿主机端口为 40014002。TiDB 实例启动后,再启动一个 ProxySQL 实例,容器内部 ProxySQL MySQL Interface 端口为 6033,映射宿主机端口为 6034。不暴露 ProxySQL Admin Interface 端口,因为其仅可在本地(即容器内)登录 ProxySQL Admin Interface。此流程被写在 docker-compose.yaml 中。

    docker-compose up -d
  2. 在 2 个 TiDB 实例内,创建相同的表结构,但写入不同的数据 'tidb-0''tidb-1',以便分辨不同的数据库实例。此处展示向其中一个 TiDB 实例写入数据的命令,另一实例同理:

    mysql -u root -h 127.0.0.1 -P 4001 << EOF DROP TABLE IF EXISTS test.test; CREATE TABLE test.test (db VARCHAR(255)); INSERT INTO test.test (db) VALUES ('tidb-0'); EOF mysql -u root -h 127.0.0.1 -P 4002 << EOF DROP TABLE IF EXISTS test.test; CREATE TABLE test.test (db VARCHAR(255)); INSERT INTO test.test (db) VALUES ('tidb-1'); EOF
  3. 使用 docker-compose exec 命令,在 ProxySQL Admin Interface 中运行事先准备好的配置 ProxySQL 的 SQL 文件

    docker-compose exec proxysql sh -c "mysql -uadmin -padmin -h127.0.0.1 -P6032 < ./proxysql-prepare.sql"

    此 SQL 文件将会运行:

    1. 添加 2 个 TiDB 后端的地址,其中,tidb-0hostgroup_id0tidb-1hostgroup_id1
    2. 生效 TiDB 后端配置,并落盘保存。
    3. 添加用户 root,密码为空,default_hostgroup0,即默认将路由至 tidb-0
    4. 生效用户配置,并落盘保存。
    5. 添加规则 ^SELECT.*FOR UPDATE$rule_id1destination_hostgroup0,即匹配此规则的 SQL 语句将被转发至 hostgroup0 的 TiDB 中(这条规则是为了将 SELECT ... FOR UPDATE 语句转发至写的数据库中)。
    6. 添加规则 ^SELECTrule_id2destination_hostgroup1,即匹配此规则的 SQL 语句将被转发至 hostgroup1 的 TiDB 中。
    7. 生效规则配置,并落盘保存。
  4. 使用 root 用户登录 ProxySQL MySQL Interface

    mysql -u root -h 127.0.0.1 -P 6034

    登录后可运行以下语句:

    • SELECT 语句:

      SELECT * FROM test.test;

      预计匹配 rule_id2 的规则,从而转发至 hostgroup1 的 TiDB 后端 tidb-1 中。

    • SELECT ... FOR UPDATE 语句:

      SELECT * FROM test.test for UPDATE;

      预计匹配 rule_id1 的规则,从而转发至 hostgroup0 的 TiDB 后端 tidb-0 中。

    • 事务语句:

      BEGIN; INSERT INTO test.test (db) VALUES ('insert this and rollback later'); SELECT * FROM test.test; ROLLBACK;

      BEGIN 语句预计不会匹配所有规则,因此将使用用户的 default_hostgroup(为 0),从而转发至 hostgroup0 的 TiDB 后端 tidb-0 中。而因为 ProxySQL 默认开启用户的 transaction_persistent,这将使同一个事务内的所有语句运行在同一个 hostgroup 中,因此,这里的 INSERT 语句和 SELECT * FROM test.test; 也将转发至 hostgroup0 的 TiDB 后端 tidb-0 中。

  5. 停止并清除 Docker Compose 启动的容器、网络拓扑等资源。

    docker-compose down

预期输出

# ./proxy-rule-split.sh Creating network "proxy-rule-admin-interface_default" with the default driver Creating proxy-rule-admin-interface_tidb-1_1 ... done Creating proxy-rule-admin-interface_tidb-0_1 ... done Creating proxy-rule-admin-interface_proxysql_1 ... done +--------+ | db | +--------+ | tidb-1 | +--------+ +--------+ | db | +--------+ | tidb-0 | +--------+ +--------------------------------+ | db | +--------------------------------+ | tidb-0 | | insert this and rollback later | +--------------------------------+ Stopping proxy-rule-admin-interface_proxysql_1 ... done Stopping proxy-rule-admin-interface_tidb-0_1 ... done Stopping proxy-rule-admin-interface_tidb-1_1 ... done Removing proxy-rule-admin-interface_proxysql_1 ... done Removing proxy-rule-admin-interface_tidb-0_1 ... done Removing proxy-rule-admin-interface_tidb-1_1 ... done Removing network proxy-rule-admin-interface_default

使用配置文件配置负载均衡

以配置文件为配置入口,配置负载均衡场景为例,运行如下命令:

cd example/load-balance-config-file ./test-load-balance.sh

此配置实现效果与使用 Admin Interface 配置负载均衡完全一致,仅改为使用配置文件进行 ProxySQL 初始化配置。