mysql.dbback,MariaDB物理备份与MySQL是否一样

背景

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