【初心者向け】MariaDB入門:バックアップ編〜mysqldumpの使い方,オプション,cron登録,innoDB確認〜

自動バックアップを行うユーザの作成について

MariaDB のバックアップ用スクリプトを cron で自動実行しようとする場合、スクリプトにはパスワードを平文で配置することになりますので、ユーザは root を利用するのは避けた方がよいでしょう。

このようなケースではまずユーザを新規作成します。以下の例では bkupusr というユーザを作成しています。バックアップには更新系の権限(UPDATE等)は不要なので付与しません。パスワードは仮に P@$$w0rd としていますが、適切なものを使って下さい。

[root@localhost ~]# mysql -u root -p

> GRANT RELOAD,SELECT,FILE,LOCK TABLES ON *.* TO 'bkupusr'@'localhost' IDENTIFIED BY 'P@$$w0rd';

cronで毎日実行するスクリプトファイルの作成

次に、バックアップ実行用スクリプトを作成します。例えば /bkup というディレクトリを作成し、その配下に毎日深夜にバックアップファイル (.sql) を吐き出すことを考えた場合、以下のスクリプトを配置します。このスクリプトでは実行ユーザ以外のアクセスを許可しません (umasuku 077)。また、7 日以前に dump したデータベースファイルを削除してくれます (最終行 find -exec)。

--- /root/dbdump.sh ---

umask 077

DATE=`date +%Y%m%d`
BkupDir="/dbdump"

mysqldump -u bkupusr -p P@$$w0rd -F --lock-all-tables --add-drop-database --all-databases > ${BkupDir}/${DATE}.sql

find ${BkupDir} -mtime +7 -exec rm -f {} \;

-F (--flush-logs)

-F オプションはバックアップ前にジャーナルログを flush します。そのため、きれいな状態でバックアップができるようになります。

--lock-all-tables

全テーブルをロックしてからバックアップを取るため、DB 内データの整合性を保つことができます。

--add-drop-database

データベースをリストアする際は、バックアップした .sql を空のデータベースにぶち込む想定となりますが、--add-drop-database や --add-drop-table のオプションがある場合、もし空じゃない場合はまずデータベースやテーブルを drop (削除) してからリストアしてくれます。

--all-databases

mysqldump コマンドは基本的にデータベース名を1つ指定します。しかし --all-databases オプションを付けると全てのデータベースをバックアップ(dump)してくれます。

MariaDBのオンラインバックアップ時の注意点

MariaDB をオンライン (稼働中) でバックアップ (dump) する場合、ロックをかけて実施するのが基本です。そのため、バックアップ中は更新系の DB アクセスは待たされる可能性があります。

なのでバックアップは利用の少ない深夜帯に cron 等で自動実行するのが望ましいです。

しかしながら、場合によっては DB 更新アクセスの多い時間帯にも実施したいケースもあるでしょう。

その場合は --single-transaction オプションを使います。mysqldump のコマンドを以下に差し替えます。

> mysqldump -u bkupusr -p P@$$w0rd -F -q --single-transaction --add-drop-database --all-databases > ${BkupDir}/${DATE}.sql

-q (--quick)

-q オプションはテーブルをメモリ上にバッファするのではなく、1 行ずつ dump していきます。これにより、メモリ負荷を抑えることができます。

なお、-q はデフォルトで有効になっていますが、 --single-transaction を付けると無効になるため、明示する必要があります。

なので特に大きなデータベースを dump する場合は必須です。man ページにも以下が記載されています。

--single-transaction

これは簡単に言うと、ロックをせずに、ある時点の一貫性を保った状態でのバックアップが可能になる機能です。具体的には "START TRANSACTION WITH CONSISTENT SNAPSHOT" というスナップショットコマンドを発行します。

参考ページ

Enhancements for START TRANSACTION WITH CONSISTENT SNAPSHOT

Enhancements for START TRANSACTION WITH ...

mariadb.com

【図解】snapshot(シャドウコピー)とバックアップの違い,VMwareやWindowsでの使い方,差分を使う仕組みについて

スナップショットとは スナップショットは「COW(Copy on Write)」...

milestone-of-se.nesuke.com

2020.07.25

ただし、--single-transaction を使うためには MariaDB のエンジンとして innoDB を使っている必要があります。最近のデフォルトでは innoDB となっているので問題ないかとは思いますが、確認方法を本記事の最後に記載します。

また、この実行時においては以下は実行されてはなりません。一貫性を保てなくなるためです。

crontab への登録

crontab へ登録します。以下の例では毎日 2:05 に実行するようになります。

[root@localhost ~]# crontab -e

5 2 * * * /root/dbdump.sh >> /var/log/dbdump_sh.log 2>&1

[Esc]:wq

innoDB 確認方法

前述の MariaDB のエンジンが innoDB となっているかを確認する方法です。

[root@localhost ~]# mysql -u root -p

> show engines;

"innoDB  DEFAULT" という箇所があれば、デフォルトエンジンが innoDB であることを示しています。

また、現在作成されているDBのエンジンが innoDB であるかどうかは、以下で確認できます。

> select table_schema, table_name, engine from information_schema.tables;

IT/インフラエンジニアの地位とスキル向上のために

【年収UPの体験談】あなたの IT/インフラエンジニアとしての価値

IT 技術の進化はとどまることを知りません。矢継ぎ早に新たな技術が出てきたり、数...

milestone-of-se.nesuke.com

【パケットキャプチャ】はNWエンジニアの価値を大きく高める

nesuke の考える NW エンジニアの2つの道 ネットワークエンジニアには ...

milestone-of-se.nesuke.com