XServer の WordPress MySQL データベースを自前で自動バックアップ

XServer には、Web UI から使える MySQL データベースのバックアップ機能があるが、やっぱり、自分でバックアップ取っておくほうが安心なため、cron 実行で自動バックアップを構築した。
記事の終わりには、データベースの復旧方法をまとめている。

バックアップ方法の検討

XServer 標準の MySQL バックアップについて調査

先に、XServer の標準の MySQL DB バックアップのデータ形式がどうなっているのかを確認した。

Web UI から、データベース　→　MySQLバックアップ取得・復元　→　手動バックアップで、バックアップを取得

同時刻に、ssh でログインして、mysqldump を使って、バックアップを取得

mysqldump -u xs620615_wp1 --password=****** xs620615_wp1 > xs620615_wp1.sql

Filezilla でローカルに引っ張ってきて、比較してみると、

$ diff xs620615_wp1.sql xs620615_wp1_105_2025-01-12.sql 
1c1
< -- MariaDB dump 10.19  Distrib 10.5.22-MariaDB, for Linux (x86_64)
---
> -- MySQL dump 10.14  Distrib 5.5.68-MariaDB, for Linux (x86_64)
3c3
< -- Host: localhost    Database: xs620615_wp1
---
> -- Host: db-sv16278.xserver.jp    Database: xs620615_wp1
10c10
< /*!40101 SET NAMES utf8mb4 */;
---
> /*!40101 SET NAMES binary */;
557c557
< -- Dump completed on 2025-01-12 16:06:53
---
> -- Dump completed on 2025-01-12 16:06:56

ほぼ、差分が無く、同じものであることを確認した。

比較や検索で扱いやすいデータ形式の取得

上記の方法で、dump データは取得できるが、オプション無しだと、INSERT 文のデータの1行がものすごく長くなるという状態になる。
これだと、diff を取ったり、エディタで確認する際に扱いづらいため、INSERT文が１レコードずつ出力されるように、-c --skip-extended-insert を付加したものを併用することにした。
（２種類取る必要はないのだが、バックアップということも考えて、念のため）

mysqldump -u xs620615_wp1 --password=****** -c --skip-extended-insert xs620615_wp1 > xs620615_wp1-sei.sql

データベースのバックアップは、これで良さそうだ。

バックアップスクリプト作成

バックアップは、./my-backup/database にディレクトリをつくり、そこに mysqldump でデータを出力した後、tar で固めて、./my-backup/database-backup/ に置いておく仕様にした。
また、ファイルが増えすぎないよう、60ファイル以上になった場合に古いファイルを削除するようにした。

filename: backup-db-wp1.sh

#!/bin/bash -ex

PASSWORD=xxx

mkdir -p $HOME/my-backup/database
rm -f $HOME/my-backup/database/*

mysqldump -u xs620615_wp1 --password=${PASSWORD} xs620615_wp1 > $HOME/my-backup/database/xs620615_wp1.sql
mysqldump -u xs620615_wp1 --password=${PASSWORD} -c --skip-extended-insert xs620615_wp1 > $HOME/my-backup/database/xs620615_wp1-sei.sql

cd $HOME/my-backup
tar cf  database-backup/database-$(date +%Y%m%d-%H%M).tar  database

# delete more than 60 files
cd $HOME/my-backup/database-backup
ls *.tar | sort -r | tail -n +61 | while read line; do rm -v $line; done

cron で定期バックアップ

最後に、crontab にタスクを登録する。
Web UI からも登録できるが、使い慣れているコンソールから設定した。

[xs620615@sv16278 ~]$ crontab -e

32 */4 * * * $HOME/backup-db-wp1.sh >$HOME/my-backup/last-cron.txt 2>&1

タスクは、4時間間隔で実行。
2>&1 で、標準エラー出力を標準出力に繋げ、どちらも last-cron.txt に残るように設定した。

少し時間をおいて、last-cron.txt が作成されていることを確認、これでバックアップが取られるようになった。

データベースの復旧方法について

古いデータベースを一度削除して、作り直し、そこにデータを書き込むというステップを取る。
WordPress の php が走っている状態だと、中途半端にアクセスが発生してしまう可能性があるため、事前にリネームして動作を完全に止めておく。

XServer に ssh でログインして、index.php を適当にリネーム

[xs620615@sv16278 ~]$ cd it-notebox.com/public_html/
[xs620615@sv16278 public_html]$ mv index.php index.php2

既存のデータベース確認（試験目的で作成した xs620615_wp4 を使用）

[xs620615@sv16278 ~]$ PASSWORD=xxx
[xs620615@sv16278 ~]$ mysql -u xs620615_wp4 --password=${PASSWORD} -e "SHOW databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| xs620615_wp4       |
+--------------------+

データベースから xs620615_wp4 を削除する。

[xs620615@sv16278 ~]$ mysql -u xs620615_wp4 --password=${PASSWORD} -e "DROP DATABASE xs620615_wp4;"

データベースが消えていることを確認

[xs620615@sv16278 ~]$ mysql -u xs620615_wp4 --password=${PASSWORD} -e "SHOW databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+

新たに空のデータベースを作成

[xs620615@sv16278 ~]$ mysql -u xs620615_wp4 --password=${PASSWORD} -e "CREATE DATABASE xs620615_wp4;"

新しくデータベースが追加されていることを確認

[xs620615@sv16278 ~]$ mysql -u xs620615_wp4 --password=${PASSWORD} -e "SHOW databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| xs620615_wp4       |
+--------------------+

空のデータベースに復旧データを書き込み

[xs620615@sv16278 ~]$ mysql -u xs620615_wp4 --password=${PASSWORD} xs620615_wp4 < ./xs620615_wp4.sql

index.php2 のリネームを戻して、完了

[xs620615@sv16278 public_html]$ mv index.php2 index.php

復旧について補足

WordPress は、画像データをファイルとして保存するため、データベースだけ復旧しても、削除してしまったファイルは復旧されない。（削除していないなら、サーバ上にファイルが残っている。）
そのため、完全な復旧を目指す場合は、データベースと同時に、WordPress のディレクトリをまるごとバックアップしておくことが必要になる。
私の場合は、データベースのバックアップに続いて、tar でドメイン関連のデータをまるごとバックアップするようにした。

filename: backup-files.sh

#!/bin/bash -ex

cd $HOME
tar cf $HOME/my-backup/it-notebox.com-backup/it-notebox.com-$(date +%Y%m%d-%H%M).tar  it-notebox.com

# delete more than 30 files
cd $HOME/my-backup/it-notebox.com-backup
ls *.tar | sort -r | tail -n +31 | while read line; do rm -v $line; done

あとは、サーバ上だけにデータがあると、何かあったときに困るため、daily で rsync でローカルに持ってくるようにした。
MySQL DB のバックアップディレクトリも含めて同期するため、これで、万が一のサーバークラッシュにも対応できる。

filename: xserv-backup.sh

rsync -a --info=progress2 --delete "$@"  -e "ssh -p 10022 -i $HOME/.ssh/id_ed25519_xserv" xs620615@xs620615.xsrv.jp:./  ./xs620615.xsrv.jp

filename: crontab -e

30 4 * * * bash -c /mnt/data02/xserv-backup/xserv-backup.sh