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

最終更新日： 2014.02.19

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

■概要

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


■バックアップ設定

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

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

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

# MySQLrootパスワード
ROOTPASS=Mysqlrootパスワード

# バックアップ先ディレクトリ再作成
rm  -rf $BACKDIR
mkdir -p $BACKDIR

# データベース名取得
DBLIST=`ls -p /var/lib/mysql | grep / | tr -d /`

# データベースごとにバックアップ
for dbname in $DBLIST
do
    [ $dbname = "information_schema" ] && continue
    [ $dbname = "performance_schema" ] && continue
    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@fedora ~]# chmod 700 mysql-backup.sh　←　rootのみ参照・実行できるようにパーミッション変更

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

※mysqlhotcopyが以下のエラーメッセージを出力して異常終了してしまう場合の対処
DBD::mysql::db do failed: You can't use locks with log tables. at /usr/bin/mysqlhotcopy line 452.

[root@fedora ~]# vi /usr/bin/mysqlhotcopy　←　/usr/bin/mysqlhotcopy編集
sub get_list_of_tables {
    my ( $db ) = @_;

    my $tables =
        eval {
            $dbh->selectall_arrayref('SHOW TABLES FROM ' .
                                     $dbh->quote_identifier($db))
        } || [];
    warn "Unable to retrieve list of tables in $db: $@" if $@;

    #return (map { $_->[0] } @$tables);　←　行頭に#を追加してコメントアウト
    −−追加(ここから)−−
    my @ignore_tables = ();

    # Ignore tables for the mysql database
    if ($db eq 'mysql') {
        @ignore_tables = qw(general_log slow_log schema apply_status);
    }

    my @res = ();
    if ($#ignore_tables > 1) {
       my @tmp = (map { $_->[0] } @$tables);
       for my $t (@tmp) {
           push(@res, $t) if not exists { map { $_=>1 } @ignore_tables }->{$t};
       }
    } else {
       @res = (map { $_->[0] } @$tables);
    }

    return @res;
    −−追加(ここまで)−−
}

※mysqlhotcopyが以下のエラーメッセージを出力して異常終了してしまう場合の対処
Invalid db.table name 'mysql.mysql`.`columns_priv' at /usr/bin/mysqlhotcopy line 855. 
[root@fedora ~]# vi /usr/bin/mysqlhotcopy　←　/usr/bin/mysqlhotcopy編集
    my @dbh_tables = eval { $dbh->tables() };
    map { s/^.*?\.//o } @dbh_tables;　←　追加
    
[root@fedora ~]# ll /backup/mysql/　←　MySQLデータベースバックアップ確認
合計 8
drwxr-x---  2 root root 4096 10月 28 21:27 mysql

（３）バックアップ定期自動実行設定 
[root@fedora ~]# echo "0 5 * * * root /root/mysql-backup.sh" > /etc/cron.d/backup　←　バックアップ定期自動実行設定追加
※毎日5:00にバックアップを実行する

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

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

（１）テスト用データベース作成 
[root@fedora ~]# 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@fedora ~]# ./mysql-backup.sh　←　MySQLデータベースバックアップスクリプト実行

（３）テスト用データベース削除 
[root@fedora ~]# 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@fedora ~]# /bin/cp -Rf /backup/mysql/test/ /var/lib/mysql/　←　バックアップよりtestデータベースをコピー

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

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

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

（５）テスト用データベース復元確認 
[root@fedora ~]# 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@fedora ~]# 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@fedora ~]# ./mysql-backup.sh　←　MySQLデータベースバックアップスクリプト実行

（３）テスト用データベース変更 
[root@fedora ~]# 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@fedora ~]# /bin/cp -Rf /backup/mysql/test/ /var/lib/mysql/　←　バックアップよりtestデータベースをコピー

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

■MySQLバグ対処

MySQLバグにより、mysqlhotcopyが下記のようなエラーメッセージを出力する場合の対処
DBI::db=HASH(0x954d4a0)->disconnect invalidates 1 active statement handle (either destroy statement handles or call finish on them before disconnecting) at /usr/bin/mysqlhotcopy line 537. 
[root@fedora ~]# vi /usr/bin/mysqlhotcopy　←　mysqlhotcopy編集
# --- get variables from database ---
my $sth_vars = $dbh->prepare("show variables like 'datadir'");
$sth_vars->execute;
while ( my ($var,$value) = $sth_vars->fetchrow_array ) {
    $mysqld_vars{ $var } = $value;
}
$sth_vars->finish();　←　追加
my $datadir = $mysqld_vars{'datadir'}
    || die "datadir not in mysqld variables";
    $datadir= $opt{chroot}.$datadir if ($opt{chroot});
$datadir =~ s:/$::;

# --- resolve database names from regexp ---
if ( defined $opt{regexp} ) {
    my $t_regex = '.*';
    if ( $opt{regexp} =~ s{^/(.+)/\./(.+)/$}{$1} ) {
        $t_regex = $2;
    }

    my $sth_dbs = $dbh->prepare("show databases");
    $sth_dbs->execute;
    while ( my ($db_name) = $sth_dbs->fetchrow_array ) {
        next if $db_name =~ m/^information_schema$/i;
        push @db_desc, { 'src' => $db_name, 't_regex' => $t_regex } if ( $db_name =~ m/$opt{regexp}/o );
    }
    $sth_dbs->finish();　←　追加
}


■関連コンテンツ




▲このページのトップへ戻る
プライバシーポリシー