- 更新日: 2013年8月6日
- CentOS & Linux
MySQL データベースのバックアップ設定とリストア確認 〜 CentOS6
MySQL データベースのバックアップを自動で行う設定をして、バックアップからリストアができるか確認を行います。データベースを利用するサーバーの運用では、バックアップが自動で行われる環境を構築しておくことは必須であると思います。
画像付き英語辞書 Imagict | 英単語をイメージで暗記このエントリーは、CentOS 6.4 インストール～設定手順の目次 の一部です。
MySQL バックアップの方針
1. CentOS サーバー側（ローカルの同一マシン内）では、mysqldump によるシェルスクリプトを cron で定期実行して（午前3時） /var/backup/mysql 配下にバックアップ。データベース毎にバックアップします。
2. 手元のクライアント側の Mac にて、rsync によるシェルスクリプトを cron で定期実行して（午前5時）、CentOS サーバーの /var/backup/mysql 配下をクライアント側の Mac にダウンロードして保存します。
以上の方針で MySQL のバックアップを行います。cron 実行の時間はまあ適当に、都合に合わせて変更してください。
MySQL バックアップ用ユーザーを作成
MySQL バックアップ用の MySQL ユーザー：mysql_backup を追加する。MySQL に root でログイン。
|
1 2 3 4 |
# mysql -u root -p Enter password: |
ユーザー mysql_backup を作成して、mysqldump コマンドに必要な権限を付与。パスワードを作成して権限設定を反映させます。
|
1 2 3 4 5 |
mysql> grant select, show view, lock tables on *.* to mysql_backup@localhost; mysql> SET PASSWORD FOR mysql_backup@localhost=PASSWORD('パスワード'); mysql> flush privileges; |
作成したユーザーの確認。
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> SELECT host,user FROM mysql.user; +-----------+--------------+ | host | user | +-----------+--------------+ | 127.0.0.1 | root | | ::1 | root | | localhost | mysql_backup | | localhost | root | +-----------+--------------+ |
権限の確認とデーターベースを確認して終了。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
mysql> SHOW GRANTS FOR mysql_backup@localhost; +----------------------------+ | Grants for mysql_backup@localhost | ... 1 row in set (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.00 sec) mysql> quit |
mysqldump コマンドを用いて MySQL データベースをバックアップ
MySQL のバックアップ用ディレクトリを作成して、mysqldump コマンドでバックアップを行います。ここで行うバックアップはローカルのサーバー機自身へのバックアップとなります。
以下の mysqldump コマンドの書式でバックアップをとります。
|
1 2 3 |
# mysqldump -u ユーザー名 --password=パスワード データベース名 > バックアップ先となるファイルのパス |
実際に、mysql データベースをバックアップしてみます。
|
1 2 3 4 5 |
# mkdir -p /var/backup/mysql # mysqldump -u mysql_backup --password=パスワード mysql > /var/backup/mysql/mysql_dump.sql -- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly. |
Warning が出ましたが、一応以下のように sql の dump ファイルがバックアップとして作成されました。
|
1 2 3 4 |
# ls /var/backup/mysql mysql_dump.sql |
削除しておきます。
|
1 2 3 |
# rm /var/backup/mysql/mysql_dump.sql |
mysqldump コマンドを使ったシェルスクリプトを作成
次にこの mysqldump コマンドを使ったシェルスクリプトを作成します。MySQLデータベース自動バックアップ運用(mysqlhotcopy) – CentOSで自宅サーバー構築 を参考にして修正したものです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
# vi /usr/local/sbin/mysql-backup.sh #!/bin/bash # バックアップ先ディレクトリ BACKDIR=/var/backup/mysql # バックアップ用の MySQL ユーザーとパスワード DBUSER=mysql_backup DBPASS=password # バックアップ先ディレクトリ再作成 rm -rf $BACKDIR mkdir -p $BACKDIR # データベース名取得 DBLIST=`ls -p /var/lib/mysql | grep / | tr -d /` # データベースごとにバックアップ、バックアップファイルは日時を付ける for dbname in $DBLIST do [ $dbname = "performance_schema" ] && continue table_count=`mysql -u $DBUSER -p$DBPASS -B -e "show tables" $dbname | wc -l` [ $table_count -ne 0 ] && mysqldump -u $DBUSER --password=$DBPASS $dbname > $BACKDIR'/'$dbname'_'`date +%Y-%m-%d_%H:%M:%S`'.sql' done |
シェルスクリプトは以上。
パーミッション設定。
|
1 2 3 |
# chmod 700 /usr/local/sbin/mysql-backup.sh |
テスト実行して、バックアップファイルが作成されたのを確認。
|
1 2 3 4 5 |
# /usr/local/sbin/mysql-backup.sh # ls /var/backup/mysql mysql_2013-04-21_17:47:25.sql |
cron に登録して1日1回、午前3時に定期実行します。
|
1 2 3 4 |
# vi /etc/crontab 0 3 * * * root /usr/local/sbin/mysql-backup.sh |
クライアント側で rsync でダウンロードしてバックアップ
クライアント側（Mac または CentOS）で rsync でダウンロードするスクリプトを作成します。以下の作業は、Mac にて行いました。
前もって、ディレクトリを作成して書き込めるようにしておきます。
|
1 2 3 4 |
$ sudo mkdir -p /var/download/backup/mysql $ sudo chmod 777 /var/download/backup/mysql |
ディレクトリの所有者を rsync の実行ユーザーに変更。
|
1 2 3 4 |
$ sudo chown localuser:staff /var/download/backup/mysql $ sudo chown -R localuser:staff /var/download/backup |
サーバーからダウンロードして、バックアップを行うシェルスクリプトを作成します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
$ vi /usr/local/sbin/mysql-download.sh #!/bin/bash # バックアップ元（サーバー側） SVDIR=/var/backup/mysql/ # バックアップ先（クライアント側） CLDIR=/var/download/backup/mysql/ # SSHユーザー SSHUSER="username@centos" # rsync でバックアップ rsync -av -e ssh $SSHUSER:$SVDIR $CLDIR |
シェルスクリプトは以上です。
パーミッション設定。
|
1 2 3 |
$ sudo chmod 755 /usr/local/sbin/mysql-download.sh |
ダウンロードしてバックアップするテスト。
|
1 2 3 4 5 6 7 8 9 |
$ /usr/local/sbin/mysql-download.sh receiving file list ... done ./ mysql_2013-04-21_17:47:25.sql sent 33 bytes received 527698 bytes 351820.67 bytes/sec total size is 527515 speedup is 1.00 |
ダウンロードできたか確認。
|
1 2 3 4 |
$ ls /var/download/backup/mysql mysql_2013-04-21_17:47:25.sql |
続いて、クライアント側で rsync を用いたシェルスクリプトを自動実行するため cron を設定します。午前5時に1日1回実行。
|
1 2 3 4 |
$ crontab -e 0 5 * * * localuser /usr/local/sbin/mysql-download.sh |
Mac OS X Lion 10.7.5 の場合、crontab -e での設定は以下のファイルとなるようです。
|
1 2 3 4 |
$ sudo vi /usr/lib/cron/tabs/localuser 0 5 * * * localuser /usr/local/sbin/mysql-download.sh |
以上で、いちおう MySQL のバックアップは設定完了です。クライアント側（Mac側）には1日1ファイルずつずっと溜まっていきますので、必要であれば世代管理をしたり、古いデータを削除、別のディスクに移すなどを行う必要があります。その場合は、ここで紹介したシェルスクリプトを少々修正する必要があります。
データベースのバックアップをテスト
サーバー機でバックアップからリストアまでを通して、テストを行なってみます。まずはバックアップ。
テスト用のデータベースを作成して試してみます。サーバー機で MySQL にログインします。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
# mysql -u root -p Enter password: mysql> create database test; Query OK, 1 row affected (0.02 sec) mysql> use test; Database changed mysql> create table test_table(num int, name varchar(50)); Query OK, 0 rows affected (0.20 sec) mysql> insert into test_table values(1,'テスト'); Query OK, 1 row affected (0.06 sec) mysql> select * from test.test_table; +------+-----------+ | num | name | +------+-----------+ | 1 | テスト | +------+-----------+ 1 row in set (0.00 sec) mysql> quit |
バックアップ実行。
|
1 2 3 |
# /usr/local/sbin/mysql-backup.sh |
バックアップが取れたか確認。
|
1 2 3 4 |
# ls -la /var/backup/mysql test_2013-04-21_18:49:06.sql |
データベースを削除。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
# mysql -u root -p mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> drop table test_table; Query OK, 0 rows affected (0.06 sec) mysql> drop database test; Query OK, 0 rows affected (0.03 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.00 sec) mysql> quit; |
データベースのリストアをテスト
次にリストアを実行するテストを行います。特定のデータベースを mysqldump によるダンプファイルからリストアするには、まずリストアさせるデータベースを作成します。以下の手順で行います。
|
1 2 3 4 5 |
# mysql -u root -p mysql> CREATE DATABASE データベース名; mysql> quit; |
その後、リストアさせるには、mysql コマンドで行います。（※ mysqldump コマンドではありません。）
|
1 2 3 |
# mysql -u user -p データベース名 < dump.sql |
では、前節で取ったバックアップからリストアを実際に行なってみます。
|
1 2 3 4 5 6 |
# mysql -u root -p mysql> CREATE DATABASE test; mysql> quit; # mysql -u root -p test < /var/backup/mysql/test_2013-04-21_18:49:06.sql |
リストアされた test データベースを確認。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
# mysql -u root -p mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | redmine | | test | +--------------------+ 5 rows in set (0.00 sec) mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | test_table | +----------------+ 1 row in set (0.00 sec) mysql> select * from test.test_table; +------+-----------+ | num | name | +------+-----------+ | 1 | テスト | +------+-----------+ 1 row in set (0.00 sec) |
テスト終了後、最後にテスト用データベースを削除します。
|
1 2 3 4 5 6 |
mysql> drop table test_table; mysql> drop database test; mysql> show databases; mysql> quit; |
以上で、MySQL のバックアップとリストアの設定とテストは終了です。
- – 参考リンク –
- MySQL :: MySQL 4.1 リファレンスマニュアル :: 4.9.7 mysqldump（テーブル構造とデータのダンプ）
- MySQLデータベース自動バックアップ運用(mysqlhotcopy) – CentOSで自宅サーバー構築
- 接続中…
- さくらのVPS+CentOSで設定しておきたい自動バックアップ – むかぁ～ どっと こむ
- 「mysqldump」を使った「MySQL」のバックアップとリストア – FlatLabs
- mysqldump が生成したファイルからデータベースをリストアする – IT革命～
- CentOS & Linux の関連記事
- Job for nginx.service failedのNginxエラー
- upstream sent too big header while reading response header from upstream(Nginx/Rails)
- Can’t get information about user clamav(clamdエラー)
- STDERR: Exception in thread “main” java.lang.InternalErrorエラー
- Linuxサーバー容量を確認するコマンドdf,duをマスターする！
- rmでファイル削除後にdf -hで容量が減らない時の対処(Linux)
- Apacheをローカルネットワークのみに公開にする
- logwatchからのメールが来ないと思ったら…
- Linuxサーバの負荷や使用率を調査するコマンドと手順
- Bashの脆弱性もう一件CVE-2014-7169に対するパッチ適用
Leave Your Message!