MariaDB?Spider數據庫分庫分表的方法

本文小編為大家詳細介紹“MariaDB Spider數據庫分庫分表的方法”，內容詳細，步驟清晰，細節處理妥當，希望這篇“MariaDB Spider數據庫分庫分表的方法”文章能幫助大家解決疑惑，下面跟著小編的思路慢慢深入，一起來學習新知識吧。

分庫分表

一般來說，數據庫分庫分表，有以下做法：

• 按哈希分片：根據一條數據的標識計算哈希值，將其分配到特定的數據庫引擎中；

• 按范圍分片：根據一條數據的標識（一般是值），將其分配到特定的數據庫引擎中；

• 按列表分片：根據某些字段的標識，如果符合條件則分配到特定的數據庫引擎中。

分庫分表的做法有很多種，例如編寫代碼庫，在程序中支持多數據庫，程序需要知道每個數據庫的地址，并要編寫代碼進行支持；使用中間件將多個數據庫引擎連接起來，程序只需要知道中間件地址。

但是分庫分表后，因為任意兩個表可能在不同的數據庫實例中，兩個表進行連接查詢時，兩個數據庫實例之間的交互變得復雜起來，當集群中的數據量較大時，便不能隨意 join 了，可能需要其他方式支撐聚合查詢。

分庫分表有優點有缺點，這里就不再多說，先學會再打算。

MariaDB Server 是開源的，目前最流行的關系型數據庫之一，MariaDB 是從 Mysql 的分支開發而來，一直保持對 Mysql 的兼容性。因為甲骨文的收購，MySQL 屬于 Oracle 所有，存在閉源的可能，以及逐漸商業化，變得不清真，于是 Mysql之父創建了 MariaDB，目的是完全兼容 Mysql，并且開源、免費。

MariaDB 使用 Spider 插件進行分庫分表的支持，Spider 存儲引擎是一個內置分片功能的存儲引擎。它支持分區和xa 事務，并允許處理不同 MariaDB 實例的表，就好像它們在同一個實例上一樣。

請參考資料：https://mariadb.com/kb/en/spider/

在這篇文章中，筆者將使用 MariaDB Spider 進行分庫分表的實踐。

部署 MariaDB 實例

為了更好地創建分庫分表實踐環境，這里需要三個 “物理”數據庫，一個邏輯數據庫，即四個 MariaDB 實例。MariaDB 實際占用的內存并不大，筆者 4G 內存的服務器裝了 Kubernetes ，用 Docker 部署四個 MariaDB 數據庫，運行速度正常，對于我們測試練習 4G 內存足以。

四個數據庫的關系如圖：

其中，邏輯數據庫實例稱為 Spider Proxy Node，實際存儲數據的數據庫實例被稱為 Backend Node。

典型的 Spider 部署具有無共享的集群架構。該系統適用于任何廉價的硬件，并且對硬件或軟件的特定要求最低。它由一組計算機組成，具有一個或多個 MariaDB 進程，稱為節點。

存儲數據的節點將被設計為Backend Nodes，并且可以是任何 MariaDB、MySQL、Oracle 服務器實例，使用后端內可用的任何存儲引擎。

Docker 部署

如果機器不夠，使用虛擬機部署便會顯得很麻煩，這里筆者使用 Docker 快速部署練習。

參考資料：https://mariadb.com/kb/en/installing-and-using-mariadb-via-docker/

查看 MariaDB 鏡像版本列表：https://hub.docker.com/_/mariadb/

直接創建四個數據庫實例，其中一個是 Spider 實例，實例使用端口區分。

docker run --name mariadbtest1 -e MYSQL_ROOT_PASSWORD=123456 -p 13306:3306 -d docker.io/library/mariadb:10.7

docker run --name mariadbtest2 -e MYSQL_ROOT_PASSWORD=123456 -p 13307:3306 -d docker.io/library/mariadb:10.7
docker run --name mariadbtest3 -e MYSQL_ROOT_PASSWORD=123456 -p 13308:3306 -d docker.io/library/mariadb:10.7
docker run --name mariadbspider -e MYSQL_ROOT_PASSWORD=123456 -p 13309:3306 -d docker.io/library/mariadb:10.7

接著，進入每個容器實例中，進入 /etc/mysql/mariadb.conf.d 目錄，修改50-server.cnf文件，運行遠程訪問數據庫實例。由于容器中沒有 nano、vi 這些編輯命令，因此可以使用下面的命令快速替換文件內容：

echo '
[server]
[mysqld]
pid-file                = /run/mysqld/mysqld.pid
basedir                 = /usr
datadir                 = /var/lib/mysql
tmpdir                  = /tmp
lc-messages-dir         = /usr/share/mysql
lc-messages             = en_US
skip-external-locking
bind-address            = 0.0.0.0
expire_logs_days        = 10
character-set-server  = utf8mb4
collation-server      = utf8mb4_general_ci
[embedded]
[mariadb]
[mariadb-10.7]
' > 50-server.cnf

然后查看每個容器的主機內 IP：

docker inspect --format='{{.NetworkSettings.IPAddress}}' mariadbtest1 mariadbtest2 mariadbtest3 mariadbspider

172.17.0.2
172.17.0.3
172.17.0.4
172.17.0.5

接著打開名為 mariadbspider 的容器，在里面按照 Spider 插件：

apt update
apt install mariadb-plugin-spider

虛擬機部署

這里需要四個虛擬機，每個虛擬機都需要先安裝 MariaDB 數據庫引擎以及一些工具包。

可參考：https://mariadb.com/kb/en/spider-installation/

首先在每個虛擬安裝 MariaDB Community Server，即數據庫引擎。

如果使用虛擬機部署安裝，需要替換國內鏡像源，以便快速下載需要的包， Centos 服務器，可以直接以下命令快速更新鏡像源，如果是 Debain 系列，可自行查找對應的鏡像源。

wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo
#清除緩存
yum clean all
#生成新的緩存
yum makecache

接著，配置 MariaDB 官方的軟件包存儲庫：

sudo yum install wget
wget https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
echo "fd3f41eefff54ce144c932100f9e0f9b1d181e0edd86a6f6b8f2a0212100c32c mariadb_repo_setup" | sha256sum -c -
chmod +x mariadb_repo_setup
sudo ./mariadb_repo_setup --mariadb-server-version="mariadb-10.7"

再次更新鏡像源緩存：

#清除緩存
yum clean all
#生成新的緩存
yum makecache

安裝 MariaDB 社區服務器和軟件包依賴項：

sudo yum install MariaDB-server MariaDB-backup

接著，配置允許遠程訪問數據庫。

MariaDB 的配置文件都在 /etc/my.cnf 中，打開 /etc/my.cnf.d/ 目錄后，修改 server.cnf 文件，允許遠程訪問。找到 bind-address 屬性，去掉 # 。

#bind-address=0.0.0.0
↓
bind-address=0.0.0.0

如需了解每個配置的作用，請參考資料： https://mariadb.com/docs/deploy/community-spider/

修改密碼。因為裸機部署的數據庫，本身沒有密碼，所以需要手動配置。

打開終端，執行以下命令：

mysql -u root -p

set password for root @localhost = password('123456');

然后執行 quit; 退出數據庫操作終端。

如果提示 root 不存在，則請使用 mysql -u mysql -p ，密碼為空，直接按下回車鍵即可。如果不行，則參考：https://www.whuanle.cn/archives/1385

然后重啟數據庫實例：

systemctl restart mariadb
systemctl status mariadb

接著檢查防火墻配置，或執行 sudo iptables -F 清理防火墻配置。

MariaDB 配置

MariaDB 配置文件中，部分主要屬性的說明如下如下：

MariaDB 基礎維護命令：

檢查每個實例

部署數據庫后，需要連接每個數據庫進行測試，以便檢查數據庫是否正常。

配置 Spider

打開 mariadbspider 數據庫實例，執行以下命令，加載 spider 插件，將其設置為 Spider 數據庫實例。

INSTALL SONAME 'ha_spider';

執行命令查詢是否已經啟動 Spider 插件：

SELECT * FROM mysql.plugin;

請參考資料：https://mariadb.com/kb/en/spider-installation/

遠程表

MariaDB Spider 模式已經搭建好了，這里開始進行實踐。

在這個模式中，Spider 中的一個表對應一個數據庫實例中的同名數據庫的同名表，即數據庫名稱系統，表名稱相同。

首先在 三個數據庫實例中，創建一個測試數據庫，名稱為 test1 ，然后執行命令創建表：

CREATE TABLE s(
  id INT NOT NULL AUTO_INCREMENT,
  code VARCHAR(10),
  PRIMARY KEY(id));

然后在 mariadbspider 實例中，執行命令，創建邏輯表，并將這個表綁定到 mariadbtest1 實例中。

CREATE TABLE s(
  id INT NOT NULL AUTO_INCREMENT,
  code VARCHAR(10),
  PRIMARY KEY(id)
)
ENGINE=SPIDER 
COMMENT 'host "172.17.0.2", user "root", password "123456", port "3306"';

注意替換你的 IP，另外注意端口，如果是容器訪問容器，直接使用 3306。

如果沒有配置好，數據庫不對應等，可能會出現：

> 1046 - No database selected
> 時間: 0.062s

然后在 mariadbspider 中，插入四條數據：

INSERT INTO s(code) VALUES ('a');
INSERT INTO s(code) VALUES ('b');
INSERT INTO s(code) VALUES ('c');
INSERT INTO s(code) VALUES ('d');

如果分別打開三個實例，你會發現，插入的數據只會出現在 mariadbtest1 中出現，因為這個表只綁定了它。你還可以在 mariadbspider 上對這個表進行增刪查改，所有操作都會同步到對應數據庫實例中。

基準性能測試

SysBench 是一個模塊化、跨平臺和多線程的基準測試工具，支持 Windows 和 Linux，用于評估對于在高負載下運行數據庫的系統非常重要的操作系統參數。這個基準測試套件的想法是，在不設置復雜的數據庫基準或甚至根本不安裝數據庫的情況下，快速獲得系統性能的印象。它可以測試出：

• 文件 i/o 性能

• 調度器性能

• 內存分配和傳輸速度

• POSIX 線程實現性能

• 數據庫服務器性能(OLTP 基準)

項目地址：https://github.com/akopytov/sysbench

Linux 可以直接安裝二進制包。

Debian/Ubuntu

curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.deb.sh | sudo bash
sudo apt -y install sysbench

RHEL/CentOS:

curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
sudo yum -y install sysbench

Fedora:

curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash	
sudo dnf -y install sysbench

Arch Linux:

sudo pacman -Suy sysbench

sysbench 命令格式：

sysbench <TYPE> --threads=2 --report-interval=3 --histogram --time=50 --db-driver=mysql --mysql-host=<HOST> --mysql-db=<SCHEMA> --mysql-user=<USER> --mysql-password=<PASSWORD> run

首先，在當前特定數據庫下創建模擬數據：

sysbench oltp_read_write --db-driver=mysql --mysql-user=root --mysql-password=123456 --mysql-host=123.123.123.123 --mysql-port=13309  --mysql-db=test1 prepare

sysbench 1.0.18 (using system LuaJIT 2.1.0-beta3)

Creating table 'sbtest1'...
Inserting 10000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...

接著運行測試：

sysbench oltp_read_write --db-driver=mysql --mysql-user=root --mysql-password=123456 --mysql-host=123.123.123.123 --mysql-port=13309  --mysql-db=test1 run

SQL statistics:
    queries performed:
        read:                            112
        write:                           32
        other:                           16
        total:                           160
    transactions:                        8      (0.80 per sec.)
    queries:                             160    (15.96 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          10.0273s
    total number of events:              8
Latency (ms):
         min:                                 1244.02
         avg:                                 1253.36
         max:                                 1267.87
         95th percentile:                     1258.08
         sum:                                10026.85
Threads fairness:
    events (avg/stddev):           8.0000/0.00
    execution time (avg/stddev):   10.0269/0.00

或者每 3 秒生成一次直方圖：

sysbench oltp_read_write --threads=2 --report-interval=3 --histogram --time=50 --table-size=1000000 --db-driver=mysql --mysql-user=root --mysql-password=123456 --mysql-host=123.123.123.123 --mysql-port=13309  --mysql-db=test1 run

清理模擬生成的數據：

sysbench oltp_read_write --db-driver=mysql --mysql-user=root --mysql-password=123456 --mysql-host=123.123.123.123 --mysql-port=13309  --mysql-db=test1 cleanup

sysbench 跑測試時，可選參數如下：

• 使用&ndash;time=<SECONDS>運行固定時間

• 使用–events=0對執行的查詢不設置限制

• 使用–db-ps-mode=disable禁用準備好的語句

• 使用&ndash;report-interval=<SECONDS>獲取繪圖點

• 用--histogram得到一個直方圖

sysbench 有三個過程或執行模式：

1. prepare：為需要它們的測試執行準備操作，例如在磁盤上為fileio 測試創建必要的文件，或填充測試數據庫以進行數據庫基準測試。

2. run：運行使用testname 參數指定的實際測試。此命令由所有測試提供。

3. cleanup：在創建一個的測試中測試運行后刪除臨時數據。

你也可以參考筆者的另一篇文章，使用別的方法做基準測試：https://www.whuanle.cn/archives/1388

加入后端數據庫

在遠程表一節中，我們是在創建表的時候，再綁定一個數據庫實例，其實也可以提前配置多個數據庫實例到 Spider 中，下面是在 Spider 中執行的配置命令：

CREATE SERVER mariadbtest1 
  FOREIGN DATA WRAPPER mysql 
OPTIONS( 
  HOST '172.17.0.2', 
  DATABASE 'test1',
  USER 'root',
  PASSWORD '123456',
  PORT 3306
);

CREATE SERVER mariadbtest2 
  FOREIGN DATA WRAPPER mysql 
OPTIONS( 
  HOST '172.17.0.3', 
  DATABASE 'test1',
  USER 'root',
  PASSWORD '123456',
  PORT 3306
);

CREATE SERVER mariadbtest3 
  FOREIGN DATA WRAPPER mysql 
OPTIONS( 
  HOST '172.17.0.4', 
  DATABASE 'test1',
  USER 'root',
  PASSWORD '123456',
  PORT 3306
);

哈希分片

在這一小節中，我們將一個表進行分片，在插入數據時，數據自動分片到三個數據庫實例中。

在三個數據節點數據庫中，在 test1 數據庫下，執行命令，創建表：

CREATE  TABLE shardtest
(
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  k int(10) unsigned NOT NULL DEFAULT '0',
  c char(120) NOT NULL DEFAULT '',
  pad char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (id),
  KEY k (k)
)

此時，三個數據庫實例都具有相同的表。

然后在 mariadbspider 實例中，執行命令，創建邏輯表，并將此表通過切片的模式，連接到三個數據庫實例中。

CREATE TABLE test1.shardtest
(
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  k int(10) unsigned NOT NULL DEFAULT '0',
  c char(120) NOT NULL DEFAULT '',
  pad char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (id),
  KEY k (k)
) ENGINE=spider COMMENT='wrapper "mysql", table "shardtest"'
 PARTITION BY KEY (id) 
(
 PARTITION pt1 COMMENT = 'srv "mariadbtest1"',
 PARTITION pt2 COMMENT = 'srv "mariadbtest2"',
 PARTITION pt3 COMMENT = 'srv "mariadbtest3"' 	
) ;

然后打開 https://github.com/whuanle/write_share_database，找到 分片測試數據.sql 這個文件，里面有很多模擬數據。

你可以觀察到，三個數據庫實例的數據是不同的。

根據值范圍分片

分片方式的選擇在于 PARTITION BY 屬性，例如哈希分片是根據一個鍵進行計算的，則配置命令為 PARTITION BY KEY (id)，如果是根據值范圍分片，則是 PARTITION BY range columns (<字段名稱>)。

) ENGINE=spider COMMENT='wrapper "mysql", table "shardtest"'
 PARTITION BY range columns (k)
(
 PARTITION pt1 values less than (5000) COMMENT = 'srv "mariadbtest1"',
 PARTITION pt2 values less than (5100) COMMENT = 'srv "mariadbtest2"'
 PARTITION pt3 values less than (5200) COMMENT = 'srv "mariadbtest3"'
) ;

根據列表分片

根據列表分片，一般是某個字段，可以將數據劃分為不同類型，可以根據這個字段的內容對數據進行分組。

) ENGINE=spider COMMENT='wrapper "mysql", table "shardtest"'
 PARTITION BY list columns (k)
(
 PARTITION pt1 values in ('4900', '4901', '4902') COMMENT = 'srv "mariadbtest1"',
 PARTITION pt2 values in ('5000', '5100') COMMENT = 'srv "mariadbtest2"'
 PARTITION pt3 values in ('5200', '5300') COMMENT = 'srv "mariadbtest3"'
) ;

當數據的 k 字段，值是 4900 、4901 或 4902 時，將被分片到 mariadbtest1 實例中。

讀到這里，這篇“MariaDB Spider數據庫分庫分表的方法”文章已經介紹完畢，想要掌握這篇文章的知識點還需要大家自己動手實踐使用過才能領會，如果想了解更多相關內容的文章，歡迎關注億速云行業資訊頻道。