mariabackup 备份 MariaDB 数据库

1、安装mariabackup

?

1	dnf -y install mariadb-backup

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

# 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、通过备份恢复数据库

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57

# 停止数据库服务 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