【初心者向け】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ページにも以下が記載されています。

To dump large tables, you should combine the --single-transaction option with --quick.

--single-transaction

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

参考ページ

【図解】スナップショット(シャドウコピー)の仕組み、バックアップとの違い、注意点、VMwareやWindows等での使い方
【図解】スナップショット(シャドウコピー)の仕組み、バックアップとの違い、注意点、VMwareやWindows等での使い方
スナップショットとは スナップショットは「COW(Copy on Write)...

https://mariadb.com/kb/en/library/enhancements-for-start-transaction-with-consistent-snapshot/

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

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

ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE

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;

シェアする

  • このエントリーをはてなブックマークに追加

フォローする