■概要

MySQLデータベースの自動バックアップを行なう。
ここでは、MySQL全データベースをサーバー内の別ディレクトリへバックアップする。


■バックアップ設定

（１）バックアップスクリプト作成 
[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
    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

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

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

※mysqlhotcopyが以下のエラーメッセージを出力して異常終了してしまう場合の対処
Invalid db.table name 'mysql.mysql`.`columns_priv' at /usr/bin/mysqlhotcopy line 855. 
[root@centos ~]# vi /usr/bin/mysqlhotcopy　←　/usr/bin/mysqlhotcopy編集
    my @dbh_tables = eval { $dbh->tables() };
    map { s/^.*?\.//o } @dbh_tables;　←　追加

[root@centos ~]# ll /backup/mysql/　←　MySQLデータベースバックアップ確認
合計 8
drwxr-x---  2 root root 4096 10月 28 21:27 mysql

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

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

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

（１）テスト用データベース作成 
[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

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

（３）テスト用データベース削除 
[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 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

（４）テスト用データベース復元 
[root@centos ~]# /bin/cp -Rf /backup/mysql/test/ /var/lib/mysql/　←　バックアップよりtestデータベースをコピー

[root@centos ~]# chown -R mysql:mysql /var/lib/mysql/test/　←　testデータベースの所有者をmysqlに変更

[root@centos ~]# chmod 700 /var/lib/mysql/test/　←　testデータベースのパーミッションを700に変更

[root@centos ~]# chmod 660 /var/lib/mysql/test/*　←　testデータベース内データのパーミッションを660に変更

（５）テスト用データベース復元確認 
[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

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

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

（１）テスト用データベース作成 
[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

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

（３）テスト用データベース変更 
[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

（４）テスト用データベース復元 
[root@centos ~]# /bin/cp -Rf /backup/mysql/test/ /var/lib/mysql/　←　バックアップよりtestデータベースをコピー

（５）テスト用データベース復元確認 
[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