背景
MariaDB作为MySQL的分支，其功能越来越受人关注。本文是针对MariaDB数据库平台产品提供功能支持测试整理
测试步骤
MariaDB 10.1.38
root@localhost : (none) 07:53:44> select version();
+-----------------+
| version() |
+-----------------+
| 10.1.38-MariaDB |
+-----------------+
1 row in set (0.00 sec)
[root@mariadb-10-1 ~]# /usr/local/xtrabackup/bin/innobackupex --version
...
innobackupex version 2.4.12 Linux (x86_64) (revision id: 170eb8c)
[root@mariadb-10-1 /]# /usr/local/xtrabackup/bin/innobackupex --defaults-file=/etc/my.cnf --user=root --password=abc123 /data/backup/
...
xtrabackup: Transaction log of lsn (1527453) to (1527453) was copied.
190424 20:01:21 completed OK!- MariaDB 10.1默认支持xtrabackup备份
使用压缩page
root@localhost : test 08:05:54> show variables like 'innodb_compression_algorithm';
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| innodb_compression_algorithm | zlib |
+------------------------------+-------+
1 row in set (0.00 sec)
root@localhost : test 08:06:17> show variables like 'innodb%format';
+---------------------------+-----------+
| Variable_name | Value |
+---------------------------+-----------+
| innodb_default_row_format | compact |
| innodb_file_format | Barracuda |
+---------------------------+-----------+
2 rows in set (0.00 sec)
root@localhost : (none) 08:03:23> use test
Database changed
root@localhost : test 08:03:26> CREATE TABLE users(user_id int not null, b varchar(200), primary key(user_id)) ENGINE=InnoDB PAGE_COMPRESSED=1;
Query OK, 0 rows affected (0.04 sec)
[root@mariadb-10-1 ~]# ./xtrabackup-2.4/bin/innobackupex --defaults-file=/etc/my.cnf --user=root --password=abc123 /data/backup/
...
190424 20:03:39 [01] ...done
190424 20:03:39 [01] Copying ./test/users.ibd to /data/backup/2019-04-24_20-03-35/test/users.ibd
[01] xtrabackup: Database page corruption detected at page 1, retrying...
[01] xtrabackup: Database page corruption detected at page 1, retrying...
[01] xtrabackup: Database page corruption detected at page 1, retrying...
[01] xtrabackup: Database page corruption detected at page 1, retrying...
[01] xtrabackup: Database page corruption detected at page 1, retrying...
[01] xtrabackup: Database page corruption detected at page 1, retrying...
[01] xtrabackup: Database page corruption detected at page 1, retrying...
[01] xtrabackup: Database page corruption detected at page 1, retrying...
190424 20:03:40 >> log scanned up to (1531308)
[01] xtrabackup: Database page corruption detected at page 1, retrying...
[01] xtrabackup: Error: failed to read page after 10 retries. File ./test/users.ibd seems to be corrupted.
[01] xtrabackup: Error: xtrabackup_copy_datafile() failed.
[01] xtrabackup: Error: failed to copy datafile.- 页面压缩是压缩表格的另一种方法，它与InnoDB COMPRESSED存储格式不同（但相似）。在页面压缩中，只有未压缩的页面存储在缓冲池中。这种方法与使用row_format = compressed的传统InnoDB压缩表有很大不同，其中未压缩和压缩页面都可以在缓冲池中。
- 在页面压缩中，页面实际上在写入文件空间之前被压缩。类似地，页面在放入缓冲池之前从表空间读取时是未压缩的。此外，页面压缩支持不同的压缩算法，而不仅仅是zlib。
- 目前，唯一完全支持页面压缩的引擎是XtraDB和InnoDB。如果页面压缩使用每个表的文件表空间，并且该表是使用innodb-file-format = Barracuda创建的，则页面压缩仅适用于给定的表。
修改encrypt
- 原实例修改innodb_page_size参数引发重新启动报错
root@localhost : test 08:07:37> show variables like 'innodb_encrypt_log';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| innodb_encrypt_log | OFF |
+--------------------+-------+
1 row in set (0.00 sec)
[root@10-10-30-232 ~]# vim /etc/mysql/my.cnf
innodb_encrypt_log=1
[root@10-10-30-232 ~]# tail -f /var/log/mysql/error.log
...
2019-04-24 20:46:28 140236265109312 [ERROR] InnoDB: cannot enable encryption, encryption plugin is not available
2019-04-24 20:46:28 140236265109312 [ERROR] Plugin 'InnoDB' init function returned error.
2019-04-24 20:46:28 140236265109312 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2019-04-24 20:46:28 140236265109312 [Note] Plugin 'FEEDBACK' is disabled.
2019-04-24 20:46:28 140236265109312 [ERROR] Unknown/unsupported storage engine: INNODB
2019-04-24 20:46:28 140236265109312 [ERROR] Aborting- 加密表使得几乎不可能访问或窃取硬盘并访问原始数据。 MariaDB使用MariaDB 10.1获得了静态数据加密。
- XtraDB和InnoDB存储引擎完全支持MariaDB加密。 Aria存储引擎也支持加密，但仅适用于使用ROW_FORMAT = PAGE（默认值）创建的表，以及二进制日志（复制日志）。
-
MariaDB允许用户灵活配置加密内容。在XtraDB或InnoDB中，可以选择加密：
- everything — all tablespaces (with all tables)
- individual tables
- everything, excluding individual tables
- encrypt XtraDB/InnoDB log files
修改page大小
- 原表存在数据的情况下修改innodb_page_size参数引发重新启动报错
[root@10-10-30-232 ~]# vim /etc/mysql/my.cnf
innodb_page_size=32k
[root@10-10-30-232 ~]# tail -f /var/log/mysql/error.log
...
2019-04-24 20:21:42 139637690066752 [ERROR] InnoDB: Data file '/var/lib/mysql/data/innodb_ts/ibdata1' uses page size 16384, but the innodb_page_size start-up parameter is 32768
2019-04-24 20:21:42 139637690066752 [ERROR] InnoDB: Corrupted page [page id: space=0, page number=0] of datafile '/var/lib/mysql/data/innodb_ts/ibdata1' could not be found in the doublewrite buffer.
2019-04-24 20:21:42 139637690066752 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2019-04-24 20:21:43 139637690066752 [Note] InnoDB: Starting shutdown...
2019-04-24 20:21:43 139637690066752 [ERROR] Plugin 'InnoDB' init function returned error.
2019-04-24 20:21:43 139637690066752 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2019-04-24 20:21:43 139637690066752 [Note] Plugin 'FEEDBACK' is disabled.
2019-04-24 20:21:43 139637690066752 [ERROR] Unknown/unsupported storage engine: INNODB
2019-04-24 20:21:43 139637690066752 [ERROR] Aborting小结
-
Percona XtraBackup可以与MariaDB 10.1一起使用：
- 如果不使用InnoDB页面压缩
- 如果未使用静态加密数据
- 如果innodb_page_size设置为16k
MariaDB 10.2.23
MariaDB [(none)]> select version();
+---------------------+
| version() |
+---------------------+
| 10.2.23-MariaDB-log |
+---------------------+
1 row in set (0.00 sec)
[root@10-10-30-232 ~]# /usr/local/xtrabackup/bin/innobackupex --version
...
innobackupex version 2.4.12 Linux (x86_64) (revision id: 170eb8c)
[root@10-10-30-232 backup]# /usr/local/xtrabackup/bin/innobackupex --defaults-file=/etc/mysql/my.cnf --user=root --password=abc123 /data/backup/
...
2019-04-24 20:25:59 0x7f89c30d2780 InnoDB: Using Linux native AIO
InnoDB: Number of pools: 1
InnoDB: Unsupported redo log format. The redo log was created with MariaDB 10.2.23. Please follow the instructions at http://dev.mysql.com/doc/refman/5.7/en/upgrading-downgrading.html- MariaDB 10.1默认支持xtrabackup备份；由于MDEV-14717引入的重做日志格式的更改而设置innodb_safe_truncate = ON，则Percona XtraBackup也可能无法完全与MariaDB 10.2.19及更高版本一起使用。
关闭参数innodb_safe_truncate
MariaDB [(none)]> show variables like 'innodb_safe_truncate';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| innodb_safe_truncate | OFF |
+----------------------+-------+
1 row in set (0.01 sec)
[root@10-10-30-232 backup]# /usr/local/xtrabackup/bin/innobackupex --defaults-file=/etc/mysql/my.cnf --user=root --password=abc123 /data/backup/
...
190424 20:32:48 completed OK!-
innodb_safe_truncate：
- 默认为ON。
- 在InnoDB中使用备份安全的TRUNCATE TABLE实现和崩溃安全的重命名操作。这与Mariabackup以外的热备份工具不兼容。需要使用此类工具的用户可将此设置为OFF。
- MariaDB 10.2.19加入；MariaDB 10.3.0移除。
小结
-
Percona XtraBackup可以与MariaDB 10.2一起使用：
- 如果不使用InnoDB页面压缩
- 如果未使用静态加密数据
- 如果innodb_page_size设置为16k
- 如果innodb_safe_truncate设置为0
MariaDB 10.3.14
MariaDB [(none)]> select version();
+---------------------+
| version() |
+---------------------+
| 10.3.14-MariaDB-log |
+---------------------+
1 row in set (0.020 sec)
[root@mariadb-10-3 ~]# /usr/local/xtrabackup/bin/innobackupex --defaults-file=/etc/mysql/my.cnf --user=root --password=abc123 /data/backup/
...
2019-04-24 20:38:37 0x7ff1edec7780 InnoDB: Using Linux native AIO
InnoDB: Number of pools: 1
InnoDB: Unsupported redo log format. The redo log was created with MariaDB 10.3.14. Please follow the instructions at http://dev.mysql.com/doc/refman/5.7/en/upgrading-downgrading.html小结
- Percona XtraBackup与MariaDB 10.3及更高版本不兼容，并且根据相关的Percona XtraBackup错误报告，似乎不会修复此问题。
总结
- 随着MySQL被oracle收购后，和MariaDB版本差异越来越大，尤其是MySQL8.0之后的版本。开源备份工具xtrabackup也被MariaDB官方修改源代码后，新的备份工具mariabackup横空出世，语法、参数和xtrabackup基本一致，熟悉MySQL同学也不会感到生疏，完美兼容MariaDB
- 当然，xtrabackup逐渐不兼容MariaDB，Galera cluster也是无法是xtrabackup做SST，自然也可以选用mariabackup
参考：
https://mariadb.com/kb/en/library/percona-xtrabackup-overview/
