文章詳情頁

MariaDB Spider 數(shù)據(jù)庫分庫分表實踐記錄

瀏覽：306日期：2023-03-30 13:21:08

分庫分表

一般來說，數(shù)據(jù)庫分庫分表，有以下做法：

按哈希分片：根據(jù)一條數(shù)據(jù)的標識計算哈希值，將其分配到特定的數(shù)據(jù)庫引擎中；
按范圍分片：根據(jù)一條數(shù)據(jù)的標識（一般是值），將其分配到特定的數(shù)據(jù)庫引擎中；
按列表分片：根據(jù)某些字段的標識，如果符合條件則分配到特定的數(shù)據(jù)庫引擎中。

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

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

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

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

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

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

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

部署 MariaDB 實例

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

四個數(shù)據(jù)庫的關(guān)系如圖：

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

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

存儲數(shù)據(jù)的節(jié)點將被設(shè)計為Backend Nodes，并且可以是任何 MariaDB、MySQL、Oracle 服務(wù)器實例，使用后端內(nèi)可用的任何存儲引擎。

Docker 部署

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

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

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

直接創(chuàng)建四個數(shù)據(jù)庫實例，其中一個是 Spider 實例，實例使用端口區(qū)分。

docker run --name mariadbtest1 -e MYSQL_ROOT_PASSWORD=123456 -p 13306:3306 -d docker.io/library/mariadb:10.7docker run --name mariadbtest2 -e MYSQL_ROOT_PASSWORD=123456 -p 13307:3306 -d docker.io/library/mariadb:10.7docker run --name mariadbtest3 -e MYSQL_ROOT_PASSWORD=123456 -p 13308:3306 -d docker.io/library/mariadb:10.7docker 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文件，運行遠程訪問數(shù)據(jù)庫實例。由于容器中沒有 nano、vi 這些編輯命令，因此可以使用下面的命令快速替換文件內(nèi)容：

echo "[server][mysqld]pid-file= /run/mysqld/mysqld.pidbasedir = /usrdatadir = /var/lib/mysqltmpdir  = /tmplc-messages-dir = /usr/share/mysqllc-messages     = en_USskip-external-lockingbind-address    = 0.0.0.0expire_logs_days= 10character-set-server  = utf8mb4collation-server      = utf8mb4_general_ci[embedded][mariadb][mariadb-10.7]" > 50-server.cnf

然后查看每個容器的主機內(nèi) 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 updateapt install mariadb-plugin-spider

虛擬機部署

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

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

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

如果使用虛擬機部署安裝，需要替換國內(nèi)鏡像源，以便快速下載需要的包， Centos 服務(wù)器，可以直接以下命令快速更新鏡像源，如果是 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 wgetwget https://downloads.mariadb.com/MariaDB/mariadb_repo_setupecho "fd3f41eefff54ce144c932100f9e0f9b1d181e0edd86a6f6b8f2a0212100c32c mariadb_repo_setup" | sha256sum -c -chmod +x mariadb_repo_setupsudo ./mariadb_repo_setup --mariadb-server-version="mariadb-10.7"

再次更新鏡像源緩存：

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

安裝 MariaDB 社區(qū)服務(wù)器和軟件包依賴項：

sudo yum install MariaDB-server MariaDB-backup

接著，配置允許遠程訪問數(shù)據(jù)庫。

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/

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

打開終端，執(zhí)行以下命令：

mysql -u root -p

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

然后執(zhí)行 quit; 退出數(shù)據(jù)庫操作終端。

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

然后重啟數(shù)據(jù)庫實例：

systemctl restart mariadbsystemctl status mariadb

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

MariaDB 配置

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

字段說明bind_address綁定訪問地址max_connections最大連接數(shù)thread_handling設(shè)置 MariaDB 社區(qū)服務(wù)器如何處理客戶端連接的線程log_error錯誤日志輸出文件

MariaDB 基礎(chǔ)維護命令：

說明命令啟動sudo systemctl start mariadb停止sudo systemctl stop mariadb重新啟動sudo systemctl restart mariadb在啟動期間啟用sudo systemctl enable mariadb啟動時禁用sudo systemctl disable mariadb狀態(tài)sudo systemctl status mariadb

檢查每個實例

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

配置 Spider

打開 mariadbspider 數(shù)據(jù)庫實例，執(zhí)行以下命令，加載 spider 插件，將其設(shè)置為 Spider 數(shù)據(jù)庫實例。

INSTALL SONAME "ha_spider";

執(zhí)行命令查詢是否已經(jīng)啟動 Spider 插件：

SELECT * FROM mysql.plugin;

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

遠程表

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

在這個模式中，Spider 中的一個表對應一個數(shù)據(jù)庫實例中的同名數(shù)據(jù)庫的同名表，即數(shù)據(jù)庫名稱系統(tǒng)，表名稱相同。

首先在三個數(shù)據(jù)庫實例中，創(chuàng)建一個測試數(shù)據(jù)庫，名稱為 test1 ，然后執(zhí)行命令創(chuàng)建表：

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

然后在 mariadbspider 實例中，執(zhí)行命令，創(chuàng)建邏輯表，并將這個表綁定到 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。

如果沒有配置好，數(shù)據(jù)庫不對應等，可能會出現(xiàn)：

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

然后在 mariadbspider 中，插入四條數(shù)據(jù)：

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

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

基準性能測試

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

文件 i/o 性能
調(diào)度器性能
內(nèi)存分配和傳輸速度
POSIX 線程實現(xiàn)性能
數(shù)據(jù)庫服務(wù)器性能(OLTP 基準)

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

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

Debian/Ubuntu

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

RHEL/CentOS:

curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bashsudo 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

首先，在當前特定數(shù)據(jù)庫下創(chuàng)建模擬數(shù)據(jù)：

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:    112write:   32other:   16total:   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:      8Latency (ms): min: 1244.02 avg: 1253.36 max: 1267.87 95th percentile:     1258.08 sum:10026.85Threads 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

清理模擬生成的數(shù)據(jù)：

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 跑測試時，可選參數(shù)如下：

使用–time=<SECONDS>運行固定時間
使用–events=0對執(zhí)行的查詢不設(shè)置限制
使用–db-ps-mode=disable禁用準備好的語句
使用–report-interval=<SECONDS>獲取繪圖點
用--histogram得到一個直方圖

sysbench 有三個過程或執(zhí)行模式：

prepare：為需要它們的測試執(zhí)行準備操作，例如在磁盤上為fileio 測試創(chuàng)建必要的文件，或填充測試數(shù)據(jù)庫以進行數(shù)據(jù)庫基準測試。
run：運行使用testname 參數(shù)指定的實際測試。此命令由所有測試提供。
cleanup：在創(chuàng)建一個的測試中測試運行后刪除臨時數(shù)據(jù)。

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

加入后端數(shù)據(jù)庫

在遠程表一節(jié)中，我們是在創(chuàng)建表的時候，再綁定一個數(shù)據(jù)庫實例，其實也可以提前配置多個數(shù)據(jù)庫實例到 Spider 中，下面是在 Spider 中執(zhí)行的配置命令：

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

哈希分片

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

在三個數(shù)據(jù)節(jié)點數(shù)據(jù)庫中，在 test1 數(shù)據(jù)庫下，執(zhí)行命令，創(chuàng)建表：

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))

此時，三個數(shù)據(jù)庫實例都具有相同的表。

然后在 mariadbspider 實例中，執(zhí)行命令，創(chuàng)建邏輯表，并將此表通過切片的模式，連接到三個數(shù)據(jù)庫實例中。

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，找到 分片測試數(shù)據(jù).sql 這個文件，里面有很多模擬數(shù)據(jù)。

你可以觀察到，三個數(shù)據(jù)庫實例的數(shù)據(jù)是不同的。

根據(jù)值范圍分片

分片方式的選擇在于 PARTITION BY 屬性，例如哈希分片是根據(jù)一個鍵進行計算的，則配置命令為 PARTITION BY KEY (id)，如果是根據(jù)值范圍分片，則是 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"") ;

根據(jù)列表分片

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

) 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"") ;

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

到此這篇關(guān)于MariaDB Spider 數(shù)據(jù)庫分庫分表實踐的文章就介紹到這了,更多相關(guān)MariaDB Spider 分庫分表內(nèi)容請搜索以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持！

MariaDB

上一條：MariaDB的安裝與配置教程下一條：MariaDB表表達式之公用表表達式(CTE)

排行榜

					
					MySQL Delete 刪數(shù)據(jù)后磁盤空間未釋放的原因
oracle 使用雜記2
MySQL性能優(yōu)化之一條SQL在MySQL中執(zhí)行的過程詳解
淺談數(shù)據(jù)庫日期類型字段設(shè)計應該如何選擇
MySql如何使用not in實現(xiàn)優(yōu)化
MySQL創(chuàng)始人發(fā)郵件尋求中國幫助
Microsoft Office Access隱藏和顯示字段的方法
MySQL中文亂碼問題解決方案
mysql啟動時報錯 ERROR! Manager of pid-file quit without
數(shù)據(jù)庫Oracle9i的企業(yè)管理器簡介
使用MySqldump命令導出數(shù)據(jù)時的注意
				