Mariadb物理备份Mariabackup

Mariabackup是由MariaDB提供的一个开源工具，用于执行InnoDB, Aria和MyISAM表。它最初是从Percona XtraBackup2.3.8派生出来，它可以在Linux和Windows上使用。

创建备份用户

1）、在10.5及更高版本中，所需的特权如下:

CREATE USER 'mariabackup'@'localhost' IDENTIFIED BY 'mypassword';
GRANT RELOAD, PROCESS, LOCK TABLES, BINLOG MONITOR ON *.* TO 'mariabackup'@'localhost';

2）、在10.5之前，所需的权限是:

CREATE USER 'mariabackup'@'localhost' IDENTIFIED BY 'mypassword';
GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'mariabackup'@'localhost';

如果您的数据库服务器也使用MyRocks存储引擎，那么执行备份的用户帐户也需要SUPER全局特权。这是因为Mariabackup通过设置rocksdb_create_checkpoint系统变量来创建该数据的检查点，该系统变量需要此特权。

GRANT CREATE ON PERCONA_SCHEMA.* TO 'mariabackup'@'localhost';
GRANT INSERT ON PERCONA_SCHEMA.* TO 'mariabackup'@'localhost';

备份

$ mariabackup --backup \
--target-dir=/var/mariadb/backup/ \
--user=mariabackup --password=mypassword

恢复

$ mariabackup --prepare \
--target-dir=/var/mariadb/backup/

$ mariabackup --copy-back \
--target-dir=/var/mariadb/backup/

$ chown -R mysql:mysql /var/lib/mysql/

压缩备份

mariabackup --user=root --backup --stream=xbstream | gzip > backupstream.gz

解压

gunzip -c backupstream.gz | mbstream -x

恢复

$ mariabackup --prepare \
--target-dir=/var/mariadb/backup/

$ mariabackup --copy-back \
--target-dir=/var/mariadb/backup/

$ chown -R mysql:mysql /var/lib/mysql/