MySQLデータベース自動バックアップ運用(mysqldump)

最終更新日: 2017.02.18

<<トップページ <<新着情報 <<リンク集 <<サイト内検索 <<Scientific Linuxで自宅サーバー構築 <<Fedoraで自宅サーバー構築

■概要

MySQLデータベースの自動バックアップを行なう。
ここでは、すべてのストレージエンジン形式に対応したmysqldumpコマンドを使用して、MySQL全データベースをサーバー内の別ディレクトリへバックアップする。


■バックアップ設定

(1)バックアップスクリプト作成
[root@centos ~]# vi mysql-backup.sh ← MySQLデータベースバックアップスクリプト作成
#!/bin/bash

PATH=/usr/local/sbin:/usr/bin:/bin

# バックアップ先ディレクトリ
BACKDIR=/backup/mysql

# MySQLrootパスワード
ROOTPASS=xxxxxxxx

# バックアップ先ディレクトリ再作成
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 root -p$ROOTPASS -B -e "show tables" $dbname|wc -l`
    [ $table_count -ne 0 ] &&
    mysqldump --events -u root -p$ROOTPASS $dbname > $BACKDIR/$dbname.sql | logger -t mysqldump
done

[root@centos ~]# chmod 700 mysql-backup.sh ← rootのみ参照・実行できるようにパーミッション変更

(2)バックアップ確認
[root@centos ~]# ./mysql-backup.sh ← MySQLデータベースバックアップスクリプト実行

[root@centos ~]# ll /backup/mysql/ ← MySQLデータベースバックアップ確認
合計 536
-rw-r--r-- 1 root root 514526  9月 10 19:56 mysql.sql

(3)バックアップ定期自動実行設定
[root@centos ~]# echo "0 5 * * * root /root/mysql-backup.sh" > /etc/cron.d/backup ← バックアップ定期自動実行設定追加

■バックアップ・リストア確認(削除されたデータベースの復元)

削除されたデータベースをバックアップからリストア(復元)できるか確認する

(1)テスト用データベース作成
[root@centos ~]# mysql -u root -p ← MySQLへrootでログイン
Enter password:  ← MySQLのrootパスワード応答
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 28 to server version: 4.1.12

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database test; ← testデータベース作成
Query OK, 1 row affected (0.00 sec)

mysql> use test ← testデータベースへ接続
Database changed
mysql> create table test(num int, name varchar(50)); ← testテーブル作成
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values(1,'山田太郎'); ← データ登録
Query OK, 1 row affected (0.00 sec)

mysql> select * from test; ← データ確認
+------+----------+
| num  | name     |
+------+----------+
|    1 | 山田太郎 |
+------+----------+
1 row in set (0.00 sec)

mysql> exit ← ログアウト
Bye

(2)テスト用データベースバックアップ
[root@centos ~]# ./mysql-backup.sh ← MySQLデータベースバックアップスクリプト実行

(3)テスト用データベース削除
[root@centos ~]# mysqladmin -uroot -p -f drop test ← testデータベース削除
Enter password:  ← MySQLのrootパスワード応答
Database "test" dropped

(4)テスト用データベース復元
[root@centos ~]# mysqladmin -uroot -p create test ← testデータベース作成
Enter password:  ← MySQLのrootパスワード応答

[root@centos ~]# mysql -u root -p test < /backup/mysql/test.sql ← testデータベース復元
Enter password:  ← MySQLのrootパスワード応答

(5)テスト用データベース復元確認
[root@centos ~]# mysql -u root -p ← MySQLへrootでログイン
Enter password:  ← MySQLのrootパスワード応答
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 38 to server version: 4.1.12

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases; ← testデータベース復元確認
+----------+
| Database |
+----------+
| mysql    |
| test     |
+----------+
2 rows in set (0.00 sec)

mysql> use test ← 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; ← testテーブル復元確認
+----------------+
| Tables_in_test |
+----------------+
| test           |
+----------------+
1 row in set (0.00 sec)

mysql> select * from test; ← データ復元確認
+------+----------+
| num  | name     |
+------+----------+
|    1 | 山田太郎 |
+------+----------+
1 row in set (0.00 sec)

mysql> drop table test; ← testテーブル削除
Query OK, 0 rows affected (0.00 sec)

mysql> drop database test; ← testデータベース削除
Query OK, 0 rows affected (0.00 sec)

mysql> show databases; ← testデータベース削除確認
+----------+
| Database |
+----------+
| mysql    |
+----------+
1 rows in set (0.00 sec)

mysql> exit ← ログアウト
Bye

■バックアップ・リストア確認(変更されたデータベースの復元)

変更されたデータベースをバックアップからリストア(復元)できるか確認する

(1)テスト用データベース作成
[root@centos ~]# mysql -u root -p ← MySQLへrootでログイン
Enter password:  ← MySQLのrootパスワード応答
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18 to server version: 4.1.12

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database test; ← testデータベース作成
Query OK, 1 row affected (0.00 sec)

mysql> use test ← testデータベースへ接続
Database changed
mysql> create table test(num int, name varchar(50)); ← testテーブル作成
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values(1,'山田太郎'); ← データ登録
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)

mysql> select * from test; ← データ確認
+------+----------+
| num  | name     |
+------+----------+
|    1 | 山田太郎 |
+------+----------+
1 row in set (0.00 sec)

mysql> exit ← ログアウト
Bye

(2)テスト用データベースバックアップ
[root@centos ~]# ./mysql-backup.sh ← MySQLデータベースバックアップスクリプト実行

(3)テスト用データベース変更
[root@centos ~]# mysql -u root -p ← MySQLへrootでログイン
Enter password:  ← MySQLのrootパスワード応答
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 25 to server version: 4.1.12

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test ← 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> update test set name='山田次郎'; ← データ変更
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test; ← データ確認
+------+----------+
| num  | name     |
+------+----------+
|    1 | 山田次郎 |
+------+----------+
1 row in set (0.00 sec)

mysql> exit ← ログアウト
Bye

(4)テスト用データベース復元
[root@centos ~]# mysql -u root -p test < /backup/mysql/test.sql ← testデータベース復元
Enter password:  ← MySQLのrootパスワード応答

(5)テスト用データベース復元確認
[root@centos ~]# mysql -u root -p ← MySQLへrootでログイン
Enter password:  ← MySQLのrootパスワード応答
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 26 to server version: 4.1.12

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test ← 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> select * from test; ← データ復元確認
+------+----------+
| num  | name     |
+------+----------+
|    1 | 山田太郎 |
+------+----------+
1 row in set (0.00 sec)

mysql> drop table test; ← testテーブル削除
Query OK, 0 rows affected (0.00 sec)

mysql> drop database test; ← testデータベース削除
Query OK, 0 rows affected (0.00 sec)

mysql> show databases; ← testデータベース削除確認
+----------+
| Database |
+----------+
| mysql    |
+----------+
1 rows in set (0.00 sec)

mysql> exit ← ログアウト
Bye



▲このページのトップへ戻る

LPIロゴ Copyright© 2005-2017 fallenangels, All rights reserved.
ご自由にリンクしてください(連絡は不要です)
本ページへのご意見・ご要望、誤字・脱字・リンク切れ等のご連絡はこちらからお願いします