MariaDB 10.11 是当前 MariaDB 最新的长维护版本，已经发了4个小版本，并且已经 GA 了2个小版本，最新版本为 MariaDB 10.11.3，发布时间为 2023-5-10。
MariaDB 长维护版本信息
从Mariadb官网可以了解到，当前的长维护版本有四个大版本，分别是10.4,10.5,10.6,10.11
从10.6之后，mariadb改变了发版策略，所以10.7-10.10不在此表中，是为小周期版本，只发布新特性，不做长期维护。
目前推荐在生产环境中使用mariadb10.6的最新版，10.11才GA不久，建议再几个小版本更新再上生产环境。
MariaDB 10.11 安装
由于测试环境使用的是centos7，所以在配置好yum源之后，直接安装即可。
$ sudo yum install MariaDB-*
...
--> Finished Dependency Resolution
Dependencies Resolved
============================================================================================================================================================================================================================================
Package Arch Version Repository Size
============================================================================================================================================================================================================================================
Installing:
MariaDB-backup x86_64 10.11.3-1.el7.centos mariadb-aliyun 7.3 M
MariaDB-backup-debuginfo x86_64 10.11.3-1.el7.centos mariadb-aliyun 46 M
MariaDB-client x86_64 10.11.3-1.el7.centos mariadb-aliyun 16 M
MariaDB-client-debuginfo x86_64 10.11.3-1.el7.centos mariadb-aliyun 50 M
MariaDB-columnstore-cmapi x86_64 22.08.2-1 mariadb-aliyun 40 M
MariaDB-columnstore-engine x86_64 10.11.3_6.4.7-1.el7.centos mariadb-aliyun 6.6 M
MariaDB-columnstore-engine-debuginfo x86_64 10.11.3_6.4.7-1.el7.centos mariadb-aliyun 76 M
MariaDB-common x86_64 10.11.3-1.el7.centos mariadb-aliyun 82 k
MariaDB-common-debuginfo x86_64 10.11.3-1.el7.centos mariadb-aliyun 185 k
MariaDB-compat x86_64 10.11.3-1.el7.centos mariadb-aliyun 2.2 M
MariaDB-connect-engine x86_64 10.11.3-1.el7.centos mariadb-aliyun 2.6 M
MariaDB-connect-engine-debuginfo x86_64 10.11.3-1.el7.centos mariadb-aliyun 3.4 M
MariaDB-cracklib-password-check x86_64 10.11.3-1.el7.centos mariadb-aliyun 8.0 k
MariaDB-cracklib-password-check-debuginfo x86_64 10.11.3-1.el7.centos mariadb-aliyun 22 k
MariaDB-devel x86_64 10.11.3-1.el7.centos mariadb-aliyun 8.6 M
MariaDB-devel-debuginfo x86_64 10.11.3-1.el7.centos mariadb-aliyun 13 k
MariaDB-gssapi-server x86_64 10.11.3-1.el7.centos mariadb-aliyun 11 k
MariaDB-gssapi-server-debuginfo x86_64 10.11.3-1.el7.centos mariadb-aliyun 33 k
MariaDB-hashicorp-key-management x86_64 10.11.3-1.el7.centos mariadb-aliyun 32 k
MariaDB-hashicorp-key-management-debuginfo x86_64 10.11.3-1.el7.centos mariadb-aliyun 107 k
MariaDB-oqgraph-engine x86_64 10.11.3-1.el7.centos mariadb-aliyun 71 k
MariaDB-oqgraph-engine-debuginfo x86_64 10.11.3-1.el7.centos mariadb-aliyun 1.4 M
MariaDB-provider-bzip2 x86_64 10.11.3-1.el7.centos mariadb-aliyun 7.5 k
MariaDB-provider-bzip2-debuginfo x86_64 10.11.3-1.el7.centos mariadb-aliyun 20 k
MariaDB-provider-lz4 x86_64 10.11.3-1.el7.centos mariadb-aliyun 7.4 k
MariaDB-provider-lz4-debuginfo x86_64 10.11.3-1.el7.centos mariadb-aliyun 19 k
MariaDB-provider-lzma x86_64 10.11.3-1.el7.centos mariadb-aliyun 7.4 k
MariaDB-provider-lzma-debuginfo x86_64 10.11.3-1.el7.centos mariadb-aliyun 19 k
MariaDB-provider-lzo x86_64 10.11.3-1.el7.centos mariadb-aliyun 7.3 k
MariaDB-provider-lzo-debuginfo x86_64 10.11.3-1.el7.centos mariadb-aliyun 19 k
MariaDB-provider-snappy x86_64 10.11.3-1.el7.centos mariadb-aliyun 7.4 k
MariaDB-provider-snappy-debuginfo x86_64 10.11.3-1.el7.centos mariadb-aliyun 19 k
MariaDB-rocksdb-engine x86_64 10.11.3-1.el7.centos mariadb-aliyun 5.1 M
MariaDB-rocksdb-engine-debuginfo x86_64 10.11.3-1.el7.centos mariadb-aliyun 73 M
MariaDB-s3-engine x86_64 10.11.3-1.el7.centos mariadb-aliyun 1.0 M
MariaDB-s3-engine-debuginfo x86_64 10.11.3-1.el7.centos mariadb-aliyun 3.9 M
MariaDB-server x86_64 10.11.3-1.el7.centos mariadb-aliyun 27 M
MariaDB-server-debuginfo x86_64 10.11.3-1.el7.centos mariadb-aliyun 99 M
MariaDB-shared x86_64 10.11.3-1.el7.centos mariadb-aliyun 125 k
MariaDB-shared-debuginfo x86_64 10.11.3-1.el7.centos mariadb-aliyun 456 k
MariaDB-test x86_64 10.11.3-1.el7.centos mariadb-aliyun 42 M
MariaDB-test-debuginfo x86_64 10.11.3-1.el7.centos mariadb-aliyun 104 M
Installing for dependencies:
galera-4 x86_64 26.4.14-1.el7.centos mariadb-aliyun 9.9 M
Transaction Summary
============================================================================================================================================================================================================================================
Install 42 Packages (+1 Dependent package)
Total download size: 627 M
Installed size: 2.7 G
Is this ok [y/d/N]: y
...
安装速度取决于网络下载速度，这里会稍微慢一点。
Installed:
MariaDB-backup.x86_64 0:10.11.3-1.el7.centos MariaDB-backup-debuginfo.x86_64 0:10.11.3-1.el7.centos MariaDB-client.x86_64 0:10.11.3-1.el7.centos
MariaDB-client-debuginfo.x86_64 0:10.11.3-1.el7.centos MariaDB-columnstore-cmapi.x86_64 0:22.08.2-1 MariaDB-columnstore-engine.x86_64 0:10.11.3_6.4.7-1.el7.centos
MariaDB-columnstore-engine-debuginfo.x86_64 0:10.11.3_6.4.7-1.el7.centos MariaDB-common.x86_64 0:10.11.3-1.el7.centos MariaDB-common-debuginfo.x86_64 0:10.11.3-1.el7.centos
MariaDB-compat.x86_64 0:10.11.3-1.el7.centos MariaDB-connect-engine.x86_64 0:10.11.3-1.el7.centos MariaDB-connect-engine-debuginfo.x86_64 0:10.11.3-1.el7.centos
MariaDB-cracklib-password-check.x86_64 0:10.11.3-1.el7.centos MariaDB-cracklib-password-check-debuginfo.x86_64 0:10.11.3-1.el7.centos MariaDB-devel.x86_64 0:10.11.3-1.el7.centos
MariaDB-devel-debuginfo.x86_64 0:10.11.3-1.el7.centos MariaDB-gssapi-server.x86_64 0:10.11.3-1.el7.centos MariaDB-gssapi-server-debuginfo.x86_64 0:10.11.3-1.el7.centos
MariaDB-hashicorp-key-management.x86_64 0:10.11.3-1.el7.centos MariaDB-hashicorp-key-management-debuginfo.x86_64 0:10.11.3-1.el7.centos MariaDB-oqgraph-engine.x86_64 0:10.11.3-1.el7.centos
MariaDB-oqgraph-engine-debuginfo.x86_64 0:10.11.3-1.el7.centos MariaDB-provider-bzip2.x86_64 0:10.11.3-1.el7.centos MariaDB-provider-bzip2-debuginfo.x86_64 0:10.11.3-1.el7.centos
MariaDB-provider-lz4.x86_64 0:10.11.3-1.el7.centos MariaDB-provider-lz4-debuginfo.x86_64 0:10.11.3-1.el7.centos MariaDB-provider-lzma.x86_64 0:10.11.3-1.el7.centos
MariaDB-provider-lzma-debuginfo.x86_64 0:10.11.3-1.el7.centos MariaDB-provider-lzo.x86_64 0:10.11.3-1.el7.centos MariaDB-provider-lzo-debuginfo.x86_64 0:10.11.3-1.el7.centos
MariaDB-provider-snappy.x86_64 0:10.11.3-1.el7.centos MariaDB-provider-snappy-debuginfo.x86_64 0:10.11.3-1.el7.centos MariaDB-rocksdb-engine.x86_64 0:10.11.3-1.el7.centos
MariaDB-rocksdb-engine-debuginfo.x86_64 0:10.11.3-1.el7.centos MariaDB-s3-engine.x86_64 0:10.11.3-1.el7.centos MariaDB-s3-engine-debuginfo.x86_64 0:10.11.3-1.el7.centos
MariaDB-server.x86_64 0:10.11.3-1.el7.centos MariaDB-server-debuginfo.x86_64 0:10.11.3-1.el7.centos MariaDB-shared.x86_64 0:10.11.3-1.el7.centos
MariaDB-shared-debuginfo.x86_64 0:10.11.3-1.el7.centos MariaDB-test.x86_64 0:10.11.3-1.el7.centos MariaDB-test-debuginfo.x86_64 0:10.11.3-1.el7.centos
Dependency Installed:
galera-4.x86_64 0:26.4.14-1.el7.centos
Complete!
安装好之后用systemctl启动即可。
sudo systemctl start mariadb
$ systemctl status mariadb
* mariadb.service - MariaDB 10.11.3 database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)
Drop-In: /etc/systemd/system/mariadb.service.d
`-migrated-from-my.cnf-settings.conf
Active: active (running) since Wed 2023-06-07 14:51:18 JST; 5s ago
Docs: man:mariadbd(8)
https://mariadb.com/kb/en/library/systemd/
Process: 1388513 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 1388396 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ] && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
Process: 1388394 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Main PID: 1388462 (mariadbd)
Status: "Taking your SQL requests now..."
Tasks: 21
Memory: 81.4M
CGroup: /system.slice/mariadb.service
`-1388462 /usr/sbin/mariadbd
MariaDB 10.11 新特性
接下来快速体验一下10.11的新特性都有哪些，这里主要是相对于10.6来说的新特性。
1. json sst progress logging mdev-26971
当mariadb使用wsrep插件时，数据库初始化启动时，耗时较长，且没有任何日志输出，这并不利于监控，所以增加了json格式的日志文件，并通过新增的参数 wsrep_status_file 将文件指定位置。另外，增加了SST过程的相关日志。
本文为了测试，需要显示指定wsrep_status_file参数，
vi my.cnf
[mariadb]
log_error=/data/mariadb/alert_err.log
wsrep_status_file=/data/mariadb/wsrep-status.json
启动db后，可以看到文件中有记录，
$ more wsrep-status.json
{
"date": "2023-06-07 15:59:02.000",
"timestamp": 1686121142.00000000,
"errors": [
],
"warnings": [
{
"timestamp": 1686121142.00000000,
"msg": "Plugin 'OQGRAPH' is of maturity level gamma while the server is stable"
}
],
"events": [
],
"status": {
"state": "DISCONNECTED",
"comment": "Disconnected",
"progress": { "from": -1, "to": -1, "total": -1, "done": -1, "indefinite": -1 }
}
}
相关链接：
2. dynamic configuration
有些innodb相关参数需要重启才能生效，这个变更是将某些参数调整为可动态生效。
涉及到的参数有：innodb_log_file_size ,innodb_undo_tablespaces ,innodb_write_io_threads ,innodb_read_io_threads ,innodb_buffer_pool_chunk_size
另一方面，mysql 8.0.30 引入了新参数 innodb_redo_log_capacity，也是类似的作用，可以动态重新生成redo日志文件。
此外，从 MariaDB 10.11.0 开始，引入了新的动态参数 replicate_rewrite_db，再此之前一直作为一个选项，需要在my.cnf中进行定义。
相关链接：
3. asc/desc indexes
升序降序排列索引，这个语法很早就支持了，但是一直不曾实用，知道mariadb 10.8 才真正实现。
先来看下之前版本的效果：
MariaDB [test]> create index idx_t1_id_desc on t1(id desc);
Query OK, 0 rows affected, 1 warning (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 1
Note (Code 1831): Duplicate index `idx_t1_id_desc`. This is deprecated and will be disallowed in a future release
MariaDB [test]> select * from t1 use index (idx_t1_id);
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+
5 rows in set (0.001 sec)
MariaDB [test]> select * from t1 use index (idx_t1_id_desc);
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+
5 rows in set (0.001 sec)
创建降序索引会报出提醒，并且是否使用这个索引，查出的数据都是升序排列的。
而在最新版的mariadb中，可以看到支持该特性，演示效果如下。
create table t1 (id int);
create index idx_t1_id on t1(id);
create index idx_t1_id_desc on t1(id desc);
insert t1 select 1;
insert t1 select 2;
insert t1 select 3;
insert t1 select 4;
insert t1 select 5;
MariaDB [test]> select * from t1 use index (idx_t1_id);
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+
5 rows in set (0.001 sec)
MariaDB [test]> select * from t1 use index (idx_t1_id_desc);
+------+
| id |
+------+
| 5 |
| 4 |
| 3 |
| 2 |
| 1 |
+------+
5 rows in set (0.001 sec)
4. data types – uuid/inet4
- UUID
UUID 数据类型，从MariaDB 10.7开始支持。
通用唯一标识符(uuid)在计算中已经占有普遍地位。 128位值加上一个文本表示值和一系列版本类型的规定以确保唯一性，使其成为理想的数据类型。
多年来，UUID数据类型仅作为MDEV-4958中的一个特性请求而存在，随着投票和观察者的慢慢积累，我们很高兴地宣布UUID数据类型的预览。
Ref:
demo:
MariaDB [test]> create table t_uuid (id uuid not null default uuid() primary key);
Query OK, 0 rows affected (0.006 sec)
MariaDB [test]> insert t_uuid select uuid();
Query OK, 1 row affected (0.003 sec)
Records: 1 Duplicates: 0 Warnings: 0
MariaDB [test]> select * from t_uuid;
+--------------------------------------+
| id |
+--------------------------------------+
| 79093247-0686-11ee-895d-fa163e852b1d |
+--------------------------------------+
1 row in set (0.000 sec)
- INET4
INET4 数据类型，从MariaDB 10.10开始支持。
类似于 INET6， INET4用于存储 IPv4 地址，以4byte二进制字符串存储。
演示：
MariaDB [test]> create table t_inet4 (a inet4);
Query OK, 0 rows affected (0.009 sec)
MariaDB [test]> insert t_inet4 select ('0.0.0.0');
Query OK, 1 row affected (0.003 sec)
Records: 1 Duplicates: 0 Warnings: 0
MariaDB [test]> select hex(a), a from t_inet4;
+----------+---------+
| hex(a) | a |
+----------+---------+
| 00000000 | 0.0.0.0 |
+----------+---------+
1 row in set (0.001 sec)
5. TO PUBLIC
从 mariadb 10.11 开始，支持 to public 语法，
GRANT <privilege> ON <database>.<object> TO PUBLIC;
REVOKE <privilege> ON <database>.<object> FROM PUBLIC;
增加这个授权给所有用户，允许其登陆数据库。
demo:
MariaDB [(none)]> show grants for public;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for PUBLIC |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, EVENT, TRIGGER, DELETE HISTORY ON `test`.* TO PUBLIC |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, EVENT, TRIGGER, DELETE HISTORY ON `test\_%`.* TO PUBLIC |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)
总结
MariaDB 10.11 这个版本虽然已经GA，但时间不长，全网分享内容较少，生产案例不多，所以如果新项目要上生产环境，则仍建议使用最新版本的 MariaDB 10.6.
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
