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

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

1. バックアップ設定

  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のみ参照・実行できるようにパーミッション変更
    
  2. ) バックアップ確認
    # ./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
    
  3. ) バックアップ定期自動実行設定
    # echo "0 5 * * * root /root/mysql-backup.sh" > /etc/cron.d/backup
      ← バックアップ定期自動実行設定追加
    

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

  1. ) テスト用データベース作成
    # 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. ) テスト用データベースバックアップ
    # ./mysql-backup.sh ← MySQLデータベースバックアップスクリプト実行
    
  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 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
    
  4. ) テスト用データベース復元
    # /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に変更
    
  5. ) テスト用データベース復元確認
    # 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. バックアップ・リストア確認(変更されたデータベースの復元)
変更されたデータベースをバックアップからリストア(復元)できるか確認する

  1. ) テスト用データベース作成
    # 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. ) テスト用データベースバックアップ
    # ./mysql-backup.sh ← MySQLデータベースバックアップスクリプト実行
    
  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 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
    
  4. ) テスト用データベース復元
    # /bin/cp -Rf /backup/mysql/test/ /var/lib/mysql/ ← バックアップよりtestデータベースをコピー
    
  5. ) テスト用データベース復元確認
    # 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
    

最終更新のRSS
Last-modified: 2014-03-11 (火) 01:59:57 (1838d)