MySQLデータベース自動バックアップ運用
概要:MySQLデータベースの自動バックアップを行ないます。
ここでは、MySQL全データベースをサーバー内の別ディレクトリへバックアップするようにします。
1. バックアップ設定
- ) バックアップスクリプト作成
# 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
table_count=`mysql -u root -p$ROOTPASS -B -e "show tables" $dbname|wc -l`
[ $table_count -ne 0 ] &&
mysqlhotcopy $dbname -u root -p $ROOTPASS $BACKDIR | logger -t mysqlhotcopy
done
# chmod 700 mysql-backup.sh ← rootのみ参照・実行できるようにパーミッション変更
- ) バックアップ確認
# ./mysql-backup.sh ← MySQLデータベースバックアップスクリプト実行
※mysqlhotcopyが以下のエラーメッセージを出力して異常終了してしまう場合の対処
Invalid db.table name 'mysql.mysql`.`columns_priv' at /usr/bin/mysqlhotcopy line 855.
# vi /usr/bin/mysqlhotcopy ← /usr/bin/mysqlhotcopy編集
my @dbh_tables = eval { $dbh->tables() };
map { s/^.*?\.//o } @dbh_tables; ← 追加
# ll /backup/mysql/ ← MySQLデータベースバックアップ確認
合計 8
drwxr-x--- 2 root root 4096 10月 28 21:27 mysql
- ) バックアップ定期自動実行設定
# echo "0 5 * * * root /root/mysql-backup.sh" > /etc/cron.d/backup
← バックアップ定期自動実行設定追加
2.バックアップ・リストア確認(削除されたデータベースの復元)
削除されたデータベースをバックアップからリストア(復元)できるか確認します。
- ) テスト用データベース作成
# 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
- ) テスト用データベースバックアップ
# ./mysql-backup.sh ← MySQLデータベースバックアップスクリプト実行
- ) テスト用データベース削除
# 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 35 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> drop table test; ← testテーブル削除
Query OK, 0 rows affected (0.01 sec)
mysql> drop database test; ← testデータベース削除
Query OK, 0 rows affected (0.00 sec)
mysql> show databases; ← データベース削除確認
+----------+
| Database |
+----------+
| mysql |
+----------+
1 rows in set (0.00 sec)
mysql> exit ← ログアウト
Bye
- ) テスト用データベース復元
# /bin/cp -Rf /backup/mysql/test/ /var/lib/mysql/ ← バックアップよりtestデータベースをコピー
# chown -R mysql:mysql /var/lib/mysql/test/ ← testデータベースの所有者をmysqlに変更
# chmod 700 /var/lib/mysql/test/ ← testデータベースのパーミッションを700に変更
# chmod 660 /var/lib/mysql/test/* ← testデータベース内データのパーミッションを660に変更
- ) テスト用データベース復元確認
# 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 7
Server version: 5.0.77 Source distribution
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
3.バックアップ・リストア確認(変更されたデータベースの復元)
変更されたデータベースをバックアップからリストア(復元)できるか確認します。
- ) テスト用データベース作成
# 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
- ) テスト用データベースバックアップ
# ./mysql-backup.sh ← MySQLデータベースバックアップスクリプト実行
- ) テスト用データベース変更
# 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='鈴木次郎'; ← データ変更
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
- ) テスト用データベース復元
# /bin/cp -Rf /backup/mysql/test/ /var/lib/mysql/ ← バックアップよりtestデータベースをコピー
- ) テスト用データベース復元確認
# 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