猿创征文 | 国产数据库之使用PXD在Docker环境下部署PolarDB-X集群
一、PolarDB-X介绍
1.PolarDB-X简介
1.PolarDB-X 是一款面向超高并发、海量存储、复杂查询场景设计的云原生分布式数据库系统。
2.PolarDB-X采用 Shared-nothing 与存储计算分离架构，支持水平扩展、分布式事务、混合负载等能力，具备企业级、云原生、高可用、高度兼容 MySQL 系统及生态等特点。
2.PolarDB-X特点
- 水平扩展
PolarDB-X 采用 Shared-nothing 架构进行设计，支持多种 Hash 和 Range 数据拆分算法，通过隐式主键拆分和数据分片动态调度，实现系统的透明水平扩展。
- 分布式事务
PolarDB-X 采用 MVCC + TSO 方案及 2PC 协议实现分布式事务。事务满足 ACID 特性，支持 RC/RR 隔离级别，并通过一阶段提交、只读事务、异步提交等优化实现事务的高性能。
- 混合负载
PolarDB-X 通过原生 MPP 能力实现对分析型查询的支持，通过 CPU quota 约束、内存池化、存储资源分离等实现了 OLTP 与 OLAP 流量的强隔离。
- 企业级
PolarDB-X 为企业场景设计了诸多内核能力，例如 SQL 限流、SQL Advisor、TDE、三权分立、Flashback Query 等。
- 云原生
PolarDB-X 在阿里云上有多年的云原生实践，支持通过 K8S Operator 管理集群资源，支持公有云、混合云、专有云等多种形态进行部署，并支持国产化操作系统和芯片。
- 高可用
通过多数派 Paxos 协议实现数据强一致，支持两地三中心、三地五副本等多种容灾方式，同时通过 Table Group、Geo-locality 等提高系统可用性。
- 兼容 MySQL 系统及生态
PolarDB-X 的目标是完全兼容 MySQL ，目前兼容的内容包括 MySQL 协议、MySQL 大部分语法、Collation、事务隔离级别、Binlog 等。
二、PolarDB-X的产品架构
1.产品架构图
2.PolarDB-X架构介绍
- 计算节点（CN, Compute Node）
计算节点是系统的入口，采用无状态设计，包括 SQL 解析器、优化器、执行器等模块。负责数据分布式路由、计算及动态调度，负责分布式事务 2PC 协调、全局二级索引维护等，同时提供 SQL 限流、三权分立等企业级特性。
- 存储节点（DN, Data Node）
存储节点负责数据的持久化，基于多数派 Paxos 协议提供数据高可靠、强一致保障，同时通过 MVCC 维护分布式事务可见性。
- 元数据服务（GMS, Global Meta Service）
元数据服务负责维护全局强一致的 Table/Schema, Statistics 等系统 Meta 信息，维护账号、权限等安全信息，同时提供全局授时服务（即 TSO）。
- 日志节点（CDC, Change Data Capture）
日志节点提供完全兼容 MySQL Binlog 格式和协议的增量订阅能力，提供兼容 MySQL Replication 协议的主从复制能力。
三、环境规划
| hostname | IP地址 | 角色 | 系统版本 | docker版本 |
|---|---|---|---|---|
| node01 | 192.168.3.80 | gms、cdc节点 | centos7.6 | 20.10.18 |
| node02 | 192.168.3.81 | cn/dn节点 | centos7.6 | 20.10.18 |
| node03 | 192.168.3.82 | cn/dn节点 | centos7.6 | 20.10.18 |
四、Docker安装
1.安装系统工具
yum install -y yum-utils device-mapper-persistent-data lvm2
2.配置docker的yum仓库源
yum-config-manager --add-repo https://mirrors.aliyun.com/docker-ce/linux/centos/docker-ce.repo
sed -i 's+download.docker.com+mirrors.aliyun.com/docker-ce+' /etc/yum.repos.d/docker-ce.repo
3.安装docker
yum -y install docker-ce
4.开启docker服务
systemctl start docker && systemctl enable docker
5.配置docker加速
echo '{ "registry-mirrors": ["https://sp4mg57h.mirror.aliyuncs.com"] }' > /etc/docker/daemon.json
systemctl daemon-reload
systemctl restart docker
6.查看docker状态
[root@node01 yum.repos.d]# systemctl status docker
● docker.service - Docker Application Container Engine
Loaded: loaded (/usr/lib/systemd/system/docker.service; enabled; vendor preset: disabled)
Active: active (running) since Sun 2022-10-09 15:43:11 CST; 44s ago
Docs: https://docs.docker.com
Main PID: 13698 (dockerd)
Tasks: 9
Memory: 27.7M
CGroup: /system.slice/docker.service
└─13698 /usr/bin/dockerd -H fd:// --containerd=/run/containerd/containerd.sock
Oct 09 15:43:10 node01 dockerd[13698]: time="2022-10-09T15:43:10.354711367+08:00" level=info msg="ccResolverWrapper: sending update to cc: {[{unix...dule=grpc
Oct 09 15:43:10 node01 dockerd[13698]: time="2022-10-09T15:43:10.354721851+08:00" level=info msg="ClientConn switching balancer to \"pick_first\"" module=grpc
Oct 09 15:43:10 node01 dockerd[13698]: time="2022-10-09T15:43:10.370117834+08:00" level=info msg="[graphdriver] using prior storage driver: overlay2"
Oct 09 15:43:10 node01 dockerd[13698]: time="2022-10-09T15:43:10.559156869+08:00" level=info msg="Loading containers: start."
Oct 09 15:43:10 node01 dockerd[13698]: time="2022-10-09T15:43:10.984797715+08:00" level=info msg="Default bridge (docker0) is assigned with an IP ... address"
Oct 09 15:43:11 node01 dockerd[13698]: time="2022-10-09T15:43:11.043510603+08:00" level=info msg="Loading containers: done."
Oct 09 15:43:11 node01 dockerd[13698]: time="2022-10-09T15:43:11.307372551+08:00" level=info msg="Docker daemon" commit=e42327a graphdriver(s)=ove...=20.10.18
Oct 09 15:43:11 node01 dockerd[13698]: time="2022-10-09T15:43:11.307446499+08:00" level=info msg="Daemon has completed initialization"
Oct 09 15:43:11 node01 systemd[1]: Started Docker Application Container Engine.
Oct 09 15:43:11 node01 dockerd[13698]: time="2022-10-09T15:43:11.335017717+08:00" level=info msg="API listen on /var/run/docker.sock"
Hint: Some lines were ellipsized, use -l to show in full.
五、安装前环境配置
1.配置/etc/hosts
[root@node01 ~]# cat /etc/hosts
192.168.3.80 node01
192.168.3.81 node02
192.168.3.82 node03
2.配置SSH免密
ssh-keygen -t rsa
ssh-copy-id -i ~/.ssh/id_rsa.pub root@192.168.3.80
ssh-copy-id -i ~/.ssh/id_rsa.pub root@192.168.3.81
ssh-copy-id -i ~/.ssh/id_rsa.pub root@192.168.3.82
3.测试shh免密
[root@node01 ~]# ssh root@192.168.3.81
Last login: Sun Oct 9 15:58:14 2022 from node01
[root@node02 ~]#
4.安装python3
yum install -y python3 #安装python
python3 -m venv venv # 创建一个 Python3 的 virtual environment 环境
source venv/bin/activate 激活virtual environment 环境
5.检查python3
[root@node01 ~]# which python3
/usr/bin/python3
六、安装 PXD工具
pip install --upgrade pip
pip install pxd
七、部署 PolarDB-X集群
1.编辑polardbx.yaml
[root@node01 polardb-x]# cat polardbx.yaml
version: v1
type: polardbx
cluster:
name: pxc_test
gms:
image: polardbx/galaxyengine:latest
host_group: [192.168.3.80]
cn:
image: polardbx/galaxysql:latest
replica: 2
nodes:
- host: 192.168.3.81
- host: 192.168.3.82
resources:
mem_limit: 4G
dn:
image: polardbx/galaxyengine:latest
replica: 2
nodes:
- host_group: [192.168.3.81]
- host_group: [192.168.3.82]
resources:
mem_limit: 4G
cdc:
image: polardbx/galaxycdc:latest
replica: 1
nodes:
- host: 192.168.3.80
resources:
mem_limit: 4G
2.一键部署 PolarDB-X集群
pxd create -file polardbx.yaml
Status: Image is up to date for polardbx/xstore-tools:latest
Processing [#########---------------------------] 25% create gms node
Processing [############------------------------] 33% create gms db and tables
Processing [###############---------------------] 41% create PolarDB-X root account
Processing [##################------------------] 50% create dn
Processing [#####################---------------] 58% register dn to gms
Processing [########################------------] 66% create cn
Processing [###########################---------] 75% wait cn ready
Processing [##############################------] 83% create cdc containers
Processing [#################################---] 91% wait PolarDB-X ready
Processing [####################################] 100%
PolarDB-X cluster create successfully, you can try it out now.
Connect PolarDB-X using the following command:
mysql -h192.168.3.81 -P5858 -upolardbx_root -pxMvRRABg
mysql -h192.168.3.82 -P3333 -upolardbx_root -pxMvRRABg
3.查看集群状态
(venv) [root@node01 polardb-x]# pxd list
/data/polardb-x/venv/lib64/python3.6/site-packages/paramiko/transport.py:33: CryptographyDeprecationWarning: Python 3.6 is no longer supported by the Python core team. Therefore, support for it is deprecated in cryptography and will be removed in a future release.
from cryptography.hazmat.backends import default_backend
/data/polardb-x/venv/lib/python3.6/site-packages/deployer
NAME CN DN CDC STATUS
pxc_test 2 2 1 running
八、连接PolarDB-X
1.连接CN节点
[root@node03 ~]# mysql -h192.168.3.81 -P5858 -upolardbx_root -pxMvRRABg
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.6.29 Tddl Server (ALIBABA)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]>
2.检查GMS
MySQL [(none)]> select * from information_schema.schemata;
+--------------+--------------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+--------------------+----------------------------+------------------------+----------+
| def | information_schema | utf8 | | NULL |
+--------------+--------------------+----------------------------+------------------------+----------+
1 row in set (0.61 sec)
3.创建数据库
MySQL [(none)]> create database polarx_example partition_mode='partitioning';
Query OK, 1 row affected (3.51 sec)
4.创建分区表
MySQL [(none)]> use polarx_example;
Database changed
MySQL [polarx_example]> create table example (
-> `id` bigint(11) auto_increment NOT NULL,
-> `name` varchar(255) DEFAULT NULL,
-> `score` bigint(11) DEFAULT NULL,
-> primary key (`id`)
-> ) engine=InnoDB default charset=utf8
-> partition by hash(id)
-> partitions 8;
Query OK, 0 rows affected (16.97 sec)
5.插入内容
MySQL [polarx_example]> insert into example values(null,'lily',375),(null,'lisa',400),(null,'ljh',500);
Query OK, 3 rows affected (0.59 sec)
6.查看分区表内容
MySQL [polarx_example]> select * from example;
+--------+------+-------+
| id | name | score |
+--------+------+-------+
| 100003 | ljh | 500 |
| 100001 | lily | 375 |
| 100002 | lisa | 400 |
+--------+------+-------+
3 rows in set (0.26 sec)
MySQL [polarx_example]> show topology from example;
+----+-----------------------------+--------------------+----------------+
| ID | GROUP_NAME | TABLE_NAME | PARTITION_NAME |
+----+-----------------------------+--------------------+----------------+
| 0 | POLARX_EXAMPLE_P00000_GROUP | example_qBAz_00000 | p1 |
| 1 | POLARX_EXAMPLE_P00000_GROUP | example_qBAz_00002 | p3 |
| 2 | POLARX_EXAMPLE_P00000_GROUP | example_qBAz_00004 | p5 |
| 3 | POLARX_EXAMPLE_P00000_GROUP | example_qBAz_00006 | p7 |
| 4 | POLARX_EXAMPLE_P00001_GROUP | example_qBAz_00001 | p2 |
| 5 | POLARX_EXAMPLE_P00001_GROUP | example_qBAz_00003 | p4 |
| 6 | POLARX_EXAMPLE_P00001_GROUP | example_qBAz_00005 | p6 |
| 7 | POLARX_EXAMPLE_P00001_GROUP | example_qBAz_00007 | p8 |
+----+-----------------------------+--------------------+----------------+
7.检查CDC
MySQL [polarx_example]> show master status ;
+---------------+----------+--------------+------------------+-------------------+
| FILE | POSITION | BINLOG_DO_DB | BINLOG_IGNORE_DB | EXECUTED_GTID_SET |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 | 37898 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.42 sec)
8.检查DN
MySQL [polarx_example]> show storage ;
+-----------------+--------------------+------------+-----------+----------+-------------+--------+-----------+
| STORAGE_INST_ID | LEADER_NODE | IS_HEALTHY | INST_KIND | DB_COUNT | GROUP_COUNT | STATUS | DELETABLE |
+-----------------+--------------------+------------+-----------+----------+-------------+--------+-----------+
| pxc_test-gms | 192.168.3.80:16058 | true | META_DB | 2 | 2 | 0 | false |
| pxc_test-dn-1 | 192.168.3.82:16507 | true | MASTER | 2 | 2 | 0 | true |
| pxc_test-dn-0 | 192.168.3.81:17140 | true | MASTER | 2 | 3 | 0 | false |
+-----------------+--------------------+------------+-----------+----------+-------------+--------+-----------+
3 rows in set (0.01 sec)
9.检查CN
MySQL [polarx_example]> show mpp ;
+----------+-------------------+------+--------+---------+
| ID | NODE | ROLE | LEADER | Latency |
+----------+-------------------+------+--------+---------+
| pxc_test | 192.168.3.81:5860 | W | Y | N |
| pxc_test | 192.168.3.82:3335 | W | N | N |
+----------+-------------------+------+--------+---------+
2 rows in set (0.00 sec)
九、PolarDB-X集群总结
1.PolarDB-X的生态兼容
PolarDB-X将兼容MySQL以及周边生态作为核心设计目标之一。
1.PolarDB-X通讯协议兼容MySQL协议。
2.PolarDB-X兼容MySQL的各种DML、DAL、DDL语法。
3.PolarDB-X账号权限系统的用法跟MySQL一致，支持GRANT、REVOKE、SHOW GRANTS、CREATE USER、DROP USER、SET PASSWORD等语句。
4.PolarDB-X兼容MySQL的常规备份策略。
5.PolarDB-X兼容MySQL binlog复制协议。
2.pxd 的更多指令及用法
(venv) [root@node01 polardb-x]# pxd --help
/data/polardb-x/venv/lib64/python3.6/site-packages/paramiko/transport.py:33: CryptographyDeprecationWarning: Python 3.6 is no longer supported by the Python core team. Therefore, support for it is deprecated in cryptography and will be removed in a future release.
from cryptography.hazmat.backends import default_backend
Usage: pxd [OPTIONS] COMMAND [ARGS]...
Options:
--help Show this message and exit.
Commands:
cleanup Clean up all PolarDB-X clusters.
create Create a full PolarDB-X cluster on multi hosts.
delete Delete specific PolarDB-X cluster.
list List PolarDB-X clusters.
tryout Create a minimum PolarDB-X cluster on local machine for tryout.
version Print pxd version.
3.检查各安装节点的docker容器
(venv) [root@node01 polardb-x]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
a27f14e82032 polardbx/galaxycdc:latest "/bin/sh -c /home/ad…" 2 hours ago Up 2 hours pxc_test-cdc-PvWo
8c8bd338a8a3 polardbx/galaxyengine:latest "bash -c '/tools/xst…" 2 hours ago Up 2 hours pxc_test-gms-Cand-16058
(venv) [root@node01 polardb-x]#
[root@node02 ~]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
7b7304858ef7 polardbx/galaxysql:latest "/home/admin/entrypo…" 2 hours ago Up 2 hours pxc_test-cn-jAXu
6e6f2e6a3407 polardbx/galaxyengine:latest "bash -c '/tools/xst…" 2 hours ago Up 2 hours pxc_test-dn-0-Cand-17140
[root@node03 ~]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
60dfd3c7e216 polardbx/galaxysql:latest "/home/admin/entrypo…" 2 hours ago Up 2 hours pxc_test-cn-qIsc
5b6d6b2a8f32 polardbx/galaxyengine:latest "bash -c '/tools/xst…" 2 hours ago Up 2 hours pxc_test-dn-1-Cand-16507
4.卸载清除PolarDB-X集群
pxd delete pxc_test