MySQL(MariaDB)をオンラインバックアップしてみる

2018/7/18 at Small Office

MySQL()でオンラインバックアップを実行してみましょう

CMSや管理ソフトウェアのパッケージでは、SQLが動作していることが多いかと思います。バックアップしなくても壊れることなかったという幸運に恵まれている方もいるのではないでしょうか? でも、不測の事態は急にやってくるものです。また、動作しているSQLも種類があり、バックアップと言っても、なにをすればいいのか?ということがあるのではないでしょうか。

MySQL(MariaDB)のオンラインバックアップするには?

MariaDBを取り上げることに、特別な理由がある訳ではないですが、今回はMariaDBのオンラインバックアップを試してみました。
MariaDBはMySQLからforkしたもので、MySQLと同じコマンドなどが使用できます。

MariaDBのバージョン

検証で使用するのは、7上で動作するMariaDB ver15.1です。なぜ、MariaDB ver15.1なのか? それは標準リポジトリでインストール出来るバージョンだったからという理由です。

# mysql --version
mysql  Ver 15.1 Distrib 5.5.56-MariaDB, for  (x86_64) using readline 5.1

簡単なMariaDBのインストール

ここではMariaDBのインストール方法を簡単に記述しておきます。

# yum install -y mariadb mariadb-server

mariadbサービスが開始している前提ですが、状態確認します。

[root@centos7-104 ~]# systemctl status mariadb.service
● mariadb.service - MariaDB database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
   Active: active (running) since 金 2018-06-08 10:37:45 JST; 4min 36s ago
  Process: 1017 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS)
  Process: 929 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)
 Main PID: 1016 (mysqld_safe)
   CGroup: /system.slice/mariadb.service
           tq1016 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
           mq1379 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/l...

 6月 08 10:37:41 centos7-104.vsample.com systemd[1]: Starting MariaDB database server...
 6月 08 10:37:41 centos7-104.vsample.com mariadb-prepare-db-dir[929]: Database MariaDB is probab...
 6月 08 10:37:42 centos7-104.vsample.com mysqld_safe[1016]: 180608 10:37:42 mysqld_safe Logging....
 6月 08 10:37:42 centos7-104.vsample.com mysqld_safe[1016]: 180608 10:37:42 mysqld_safe Startin...l
 6月 08 10:37:45 centos7-104.vsample.com systemd[1]: Started MariaDB database server.
Hint: Some lines were ellipsized, use -l to show in full.

検証環境のDatabase

ここでは、簡易的にtest、test2、test3というデータベースを作成してあります。各データベースには適当なテーブルとデータを挿入してあります。

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| test2              |
| test3              |
+--------------------+

データベースのサイズ

MariaDB [(none)]> select table_schema, sum(data_length+index_length) /1024 /1024 as MB from information_schema.tables  group by table_schema order by sum(data_length+index_length) desc;
+--------------------+------------+
| table_schema       | MB         |
+--------------------+------------+
| mysql              | 0.62661934 |
| information_schema | 0.07031250 |
| test2              | 0.03125000 |
| test3              | 0.01562500 |
| test               | 0.01562500 |
| performance_schema | 0.00000000 |
+--------------------+------------+
6 rows in set (0.01 sec)

testuser@localhostに権限を付与しています。

MariaDB [(none)]> show grants for testuser@localhost;
+-----------------------------------------------------------------------------------------------------------------+
| Grants for testuser@localhost                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'localhost' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
| GRANT ALL PRIVILEGES ON `test`.* TO 'testuser'@'localhost'                                                      |
| GRANT ALL PRIVILEGES ON `test2`.* TO 'testuser'@'localhost'                                                     |
| GRANT ALL PRIVILEGES ON `test3`.* TO 'testuser'@'localhost'                                                     |
+-----------------------------------------------------------------------------------------------------------------+

バックアップの種類

mariadbのバックアップには、大きな分類としてオンラインバックアップとオフラインバックアップがあります。オンラインバックアップはmariadbが動作している状態でバックアップを取得することで、オフラインバックアップはmariadbをシャットダウンした状態でバックアップを取得することです。オフラインバックアップはmariadbをシャットダウンしているので、mariadbのデータディレクトリ(デフォルトでインストールしていれば/var/lib/mysqlなどになっているかと思います)と配置されているファイルを任意のディレクトリにコピーすることをイメージしていただければ判るかと思います。

オンライン/オフライン以外にフルバックアップ、差分、増分という種類が存在します。他に、少しバックアップとは異なりますがレプリケーションなどもあります。

ここでは、オンラインバックアップでフルバックアップの取得を行います。理由として、運用するデータベースは、データベースサイズがそれほど大きいものではなく、バックアップ頻度も時々バックアップを取得する程度を考えていることから、増分、差分ではなく、フルバックアップを行うことを想定しています。

バックアップを取得する

MariaDBｄオンラインバックアップを実行するには、mysqldumpコマンドを使用します。
もっと正しいコマンドがあると思いますが、ここでは簡単なコマンドで実施できるようにしています。

特定のデータベースをバックアップする場合

  1. バックアップを格納するディレクトリを作成します
    ここではrootのhomeディレクトリにdumpというディレクトリを作成しています
    2. # mkdir /sqldump
# ll / (必要箇所のみ抜粋)
drwxr-xr-x.  2 root root    6  6月  8 11:51 sqldump
  2. データベースtestをバックアップします
    正常な場合は何もメッセージが出ずに完了します
    3. # mysqldump -u root -p test > /sqldump/test.sql
  3. バックアップ先のファイルにはSQLコマンドが書かれています
    ここで記載している/sqldump/test.sqlにバックアップしていると考えてください
    4. # cat /sqldump/test.sql

すべてのデータベースをバックアップする場合

ここでは、特定のデータベースをバックアップする場合と同じディレクトリにall_databases.sqlというファイルにバックアップします

  1. すべてのデータベースをバックアップします
    正常な場合は何もメッセージが出ずに完了します
    2. # mysqldump -u root -p -x --all-databases > /sqldump/all_databases.sql
  2. バックアップ先のファイルにはSQLコマンドが書かれています
    ここで記載している/sqldump/test.sqlにバックアップしていると考えてください
    3. # cat /sqldump/all_databases.sql

バックアップデータからリストアする

バックアップしたらリストアです。

すべてのデーtベースをリストアする

# mysql -u root -p < /sqldump/test.sql

特定のデータベースだけをリストアする

ここではデータベースtestだけをリストアする場合です。

mysql -u root test <  /sqldump/test.sql

というように、個人の小規模データならば、この程度で、サンプル環境は本当に適当なデータが少しだけあるデータベースなため、バックアップもリストアも一瞬で終わりますが、実際は時間も掛かりますし、実際にグループで運用しているような場合は、バックアップ取得もリストア方法も事前に考えておかなくてはなりません。手順を知っておくだけでも気分的には楽かもしれません。