mariabackup 备份 MariaDB 数据库

1、安装mariabackup

dnf -y install mariadb-backup

2、创建备份目录并执行备份操作

# mkdir /data/backup -p
# mariabackup --backup --target-dir /data/backup/ -u root
[00] 2023-04-13 11:50:31 Connecting to MySQL server host: localhost, user: root, password: not set, port: 3306, socket: /var/lib/mysql/mysql.sock
[00] 2023-04-13 11:50:31 Using server version 10.5.16-MariaDB-log
mariabackup based on MariaDB server 10.5.16-MariaDB Linux (x86_64)
...
[00] 2023-04-13 11:50:34 >> log scanned up to (45130)
[00] 2023-04-13 11:50:34 Executing BACKUP STAGE END
[00] 2023-04-13 11:50:34 All tables unlocked
[00] 2023-04-13 11:50:34 Copying /var/lib/mysql/ib_buffer_pool to /data/backup/ib_buffer_pool
[00] 2023-04-13 11:50:34         ...done
[00] 2023-04-13 11:50:34 Backup created in directory '/data/backup/'
[00] 2023-04-13 11:50:34 MySQL binlog position: filename 'mysql-bin.000004', position '385', GTID of the last change '0-1-59'
[00] 2023-04-13 11:50:34 Writing backup-my.cnf
[00] 2023-04-13 11:50:34         ...done
[00] 2023-04-13 11:50:34 Writing xtrabackup_info
[00] 2023-04-13 11:50:34         ...done
[00] 2023-04-13 11:50:34 Redo log (from LSN 45118 to 45130) was copied.
[00] 2023-04-13 11:50:34 completed OK!

3、通过备份恢复数据库

# 停止数据库服务
systemctl stop mariadb

# 删除原数据
rm -rf /var/lib/mysql/*

# 在恢复任务之前运行准备任务
# mariabackup --prepare --target-dir /data/backup/
mariabackup based on MariaDB server 10.5.16-MariaDB Linux (x86_64)
[00] 2023-04-13 11:56:32 cd to /data/backup/
[00] 2023-04-13 11:56:32 open files limit requested 0, set to 1000000
[00] 2023-04-13 11:56:32 This target seems to be not prepared yet.
[00] 2023-04-13 11:56:32 mariabackup: using the following InnoDB configuration for recovery:
[00] 2023-04-13 11:56:32 innodb_data_home_dir = .
[00] 2023-04-13 11:56:32 innodb_data_file_path = ibdata1:12M:autoextend
[00] 2023-04-13 11:56:32 innodb_log_group_home_dir = .
[00] 2023-04-13 11:56:32 InnoDB: Using Linux native AIO
[00] 2023-04-13 11:56:32 Starting InnoDB instance for recovery.
[00] 2023-04-13 11:56:32 mariabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
2023-04-13 11:56:32 0 [Note] InnoDB: Uses event mutexes
2023-04-13 11:56:32 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2023-04-13 11:56:32 0 [Note] InnoDB: Number of pools: 1
2023-04-13 11:56:32 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2023-04-13 11:56:32 0 [Note] InnoDB: Using Linux native AIO
2023-04-13 11:56:32 0 [Note] InnoDB: Initializing buffer pool, total size = 104857600, chunk size = 104857600
2023-04-13 11:56:32 0 [Note] InnoDB: Completed initialization of buffer pool
2023-04-13 11:56:32 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=45118,45118
[00] 2023-04-13 11:56:32 Last binlog file , position 0
[00] 2023-04-13 11:56:32 completed OK!

# 运行恢复
# mariabackup --copy-back --target-dir /data/backup/
mariabackup based on MariaDB server 10.5.16-MariaDB Linux (x86_64)
[01] 2023-04-13 11:57:06 Copying ibdata1 to /var/lib/mysql/ibdata1
[01] 2023-04-13 11:57:06         ...done
[01] 2023-04-13 11:57:06 Copying ./mysql/innodb_table_stats.ibd to /var/lib/mysql/mysql/innodb_table_stats.ibd
[01] 2023-04-13 11:57:06         ...done
...
[01] 2023-04-13 11:57:06 Copying ./mysql-bin.000001 to /var/lib/mysql/mysql-bin.000001
[01] 2023-04-13 11:57:06         ...done
[01] 2023-04-13 11:57:06 Copying ./mysql-bin.000002 to /var/lib/mysql/mysql-bin.000002
[01] 2023-04-13 11:57:06         ...done
[01] 2023-04-13 11:57:06 Copying ./mysql-bin.000003 to /var/lib/mysql/mysql-bin.000003
[01] 2023-04-13 11:57:06         ...done
[01] 2023-04-13 11:57:06 Copying ./mysql-bin.000004 to /var/lib/mysql/mysql-bin.000004
[01] 2023-04-13 11:57:06         ...done
[01] 2023-04-13 11:57:06 Copying ./xtrabackup_info to /var/lib/mysql/xtrabackup_info
[01] 2023-04-13 11:57:06         ...done
[01] 2023-04-13 11:57:06 Copying ib_buffer_pool to /var/lib/mysql/ib_buffer_pool
[01] 2023-04-13 11:57:06         ...done
[00] 2023-04-13 11:57:06 completed OK!

# 修改数据库目录权限
chown -R mysql. /var/lib/mysql

# 启动数据库服务
systemctl start mariadb