FreeBSD 15
Sponsored Link

MariaDB 11.8 : バックアップ2026/01/20
 

MariaDB のデータベースを バックアップ/リストア する際は付属のツールで実行可能です。

[1] バックアップを実行します。
例として、[/home/mariadb_backup] ディレクトリ配下にバックアップを取得します。
root@www:~ #
mkdir /home/mariadb_backup

root@www:~ #
mariabackup --backup --target-dir /home/mariadb_backup -u root 


.....
.....
[00] 2026-01-20 09:14:27 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
[00] 2026-01-20 09:14:27 Finishing BACKUP STAGE BLOCK_COMMIT
[00] 2026-01-20 09:14:27 Starting BACKUP STAGE END
[00] 2026-01-20 09:14:27 Executing BACKUP STAGE END
[00] 2026-01-20 09:14:27 All tables unlocked
[00] 2026-01-20 09:14:27 Backup created in directory '/home/mariadb_backup/'
[00] 2026-01-20 09:14:27 Writing backup-my.cnf
[00] 2026-01-20 09:14:27         ...done
[00] 2026-01-20 09:14:27 Writing mariadb_backup_info
[00] 2026-01-20 09:14:27         ...done
[00] 2026-01-20 09:14:27 Redo log (from LSN 48858 to 48874) was copied.
[00] 2026-01-20 09:14:27 completed OK!
root@www:~ #
ls -l /home/mariadb_backup 

total 363
-rw-r-----  1 root wheel   417792 Jan 20 09:14 aria_log.00000001
-rw-r-----  1 root wheel       52 Jan 20 09:14 aria_log_control
-rw-r-----  1 root wheel      449 Jan 20 09:14 backup-my.cnf
-rw-r-----  1 root wheel    12304 Jan 20 09:14 ib_logfile0
-rw-r-----  1 root wheel 12582912 Jan 20 09:14 ibdata1
-rw-r-----  1 root wheel       97 Jan 20 09:14 mariadb_backup_checkpoints
-rw-r-----  1 root wheel      429 Jan 20 09:14 mariadb_backup_info
drwx------  2 root wheel       90 Jan 20 09:14 mysql
drwx------  2 root wheel        3 Jan 20 09:14 performance_schema
drwx------  2 root wheel      107 Jan 20 09:14 sys
drwx------  2 root wheel        5 Jan 20 09:14 test_database
-rw-r-----  1 root wheel 10485760 Jan 20 09:14 undo001
-rw-r-----  1 root wheel 10485760 Jan 20 09:14 undo002
-rw-r-----  1 root wheel 10485760 Jan 20 09:14 undo003
[2] バックアップしたデータを、他ホスト上の MariaDB サーバーへリストアします。
事前にバックアップしたデータを [tar] でまとめるなり [rsync] でそのまま転送する等して、バックアップデータをリストア先サーバーに転送しておいてください。
# MariaDB を停止して既存データは削除

root@node01:~ #
service mysql-server stop

root@node01:~ #
rm -rf /var/db/mysql/*
# 転送したバックアップデータ

root@node01:~ #
ls -l mariadb_backup.tar.gz

-rw-r--r-- 1 freebsd freebsd 708606 Jan 20 09:18 mariadb_backup.tar.gz
root@node01:~ #
tar zxvf mariadb_backup.tar.gz

# リストア事前タスク実行 ([completed OK] であれば OK)

root@node01:~ #
mariabackup --prepare --target-dir /root/mariadb_backup 

mariabackup based on MariaDB server 11.8.5-MariaDB FreeBSD15.0 (amd64)
[00] 2026-01-20 09:19:08 cd to /root/mariadb_backup/
[00] 2026-01-20 09:19:08 open files limit requested 0, set to 116190
[00] 2026-01-20 09:19:08 Loading encryption plugin from provider_bzip2=provider_bzip2;provider_lz4=provider_lz4;provider_lzma=provider_lzma
[00] 2026-01-20 09:19:08 Loading encryption plugin
[00] 2026-01-20 09:19:08         Encryption plugin parameter :  'mariabackup'
[00] 2026-01-20 09:19:08         Encryption plugin parameter :  '--plugin_load=provider_bzip2=provider_bzip2;provider_lz4=provider_lz4;provider_lzma=provider_lzma'
[00] 2026-01-20 09:19:08         Encryption plugin parameter :  '--innodb_encrypt_tables=OFF'
[00] 2026-01-20 09:19:08         Encryption plugin parameter :  '--prepare'
[00] 2026-01-20 09:19:08         Encryption plugin parameter :  '--target-dir'
[00] 2026-01-20 09:19:08         Encryption plugin parameter :  '/root/mariadb_backup'
[00] 2026-01-20 09:19:08 This target seems to be not prepared yet.
[00] 2026-01-20 09:19:08 mariabackup: using the following InnoDB configuration for recovery:
[00] 2026-01-20 09:19:08 innodb_data_home_dir = .
[00] 2026-01-20 09:19:08 innodb_data_file_path = ibdata1:12M:autoextend
[00] 2026-01-20 09:19:08 innodb_log_group_home_dir = .
[00] 2026-01-20 09:19:08 Starting InnoDB instance for recovery.
[00] 2026-01-20 09:19:08 mariabackup: Using 100663296 bytes for buffer pool (set by --use-memory parameter)
2026-01-20  9:19:08 0 [Note] InnoDB: Compressed tables use zlib 1.3.1
2026-01-20  9:19:08 0 [Note] InnoDB: Number of transaction pools: 1
2026-01-20  9:19:08 0 [Note] InnoDB: Using AVX512 instructions
2026-01-20  9:19:08 0 [Note] InnoDB: innodb_buffer_pool_size_max=96m, innodb_buffer_pool_size=96m
2026-01-20  9:19:08 0 [Note] InnoDB: Completed initialization of buffer pool
2026-01-20  9:19:08 0 [Note] InnoDB: End of log at LSN=48874
[00] 2026-01-20 09:19:08 Last binlog file , position 0
recovered pages: 0% 15% 36% 62% 100% (0.0 seconds); tables to flush: 1 0 (0.0 seconds);
[00] 2026-01-20 09:19:08 completed OK!
# リストア実行

root@node01:~ #
mariabackup --copy-back --target-dir /root/mariadb_backup 


.....
.....
[01] 2026-01-20 09:19:30 Copying ./sys/processlist.frm to /var/db/mysql/sys/processlist.frm
[01] 2026-01-20 09:19:30         ...done
[01] 2026-01-20 09:19:30 Copying ./sys/waits_global_by_latency.frm to /var/db/mysql/sys/waits_global_by_latency.frm
[01] 2026-01-20 09:19:30         ...done
[01] 2026-01-20 09:19:30 Copying ./sys/io_global_by_file_by_bytes.frm to /var/db/mysql/sys/io_global_by_file_by_bytes.frm
[01] 2026-01-20 09:19:30         ...done
[00] 2026-01-20 09:19:30 completed OK!
root@node01:~ #
chown -R mysql:mysql /var/db/mysql

root@node01:~ #
service mysql-server start

関連コンテンツ