読者です 読者をやめる 読者になる 読者になる

Recovery Manager for MySQLを使う

Linux MySQL Recovery Manager for MySQL

Recovery Manager for MySQLの使い方をメモしておきます。


今回の環境は以下の通りです。

インストールする


まずはRPMファイルをダウンロードします。

# wget http://www.zmanda.com/downloads/community/ZRM-MySQL/3.0/RPM/MySQL-zrm-3.0-1.noarch.rpm
# wget http://www.zmanda.com/downloads/community/ZRM-MySQL/3.0/RPM/MySQL-zrm-client-3.0-1.noarch.rpm


その後、RPMパッケージをインストールします。

# yum install MySQL-zrm-3.0-1.noarch.rpm
# yum install MySQL-zrm-client-3.0-1.noarch.rpm


また、バックアップ処理用のユーザを予め作成しておきます。

mysql> GRANT SELECT, INSERT, UPDATE, CREATE, DROP, RELOAD, SHUTDOWN, ALTER, SUPER, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backup'@'localhost' IDENTIFIED BY 'YOUR PASS';


設定ファイルを用意します。以下のようにしました。
「/etc/mysql-zrm/mybackup/mysq-zrm.conf」として保存します。
ロジカルバックアップで全てのデータベースを取得する設定です。

# vi /etc/mysql-zrm/mybackup/mysql-zrm.conf
backup-mode=logical
destination=/var/lib/mysql-zrm
retention-policy=2W
all-databases=1
user="backup"
password="YOUR PASS"
port=3306
socket=/var/lib/mysql/mysql.sock
mysql-binlog-path="/var/lib/mysql"
tmpdir=/tmp
verbose=1


コンフィグをチェックします。

# mysql-zrm-check --backup-set mybackup
check:INFO: ZRM for MySQL - version built from source
mybackup:check:INFO: The quick backup-type is supported only for snapshot backups. Setting backup-type to 'regular'
mybackup:check:INFO: ZRM Temporary configuration file = /etc/mysql-zrm/mybackup/tmpqV6cJ.conf
mybackup:check:INFO: {
mybackup:check:INFO:    verbose=1
mybackup:check:INFO:    retention-policy=2W
mybackup:check:INFO:    backup-level=0
mybackup:check:INFO:    tmpdir=/tmp
mybackup:check:INFO:    all-databases=1
mybackup:check:INFO:    destination=/var/lib/mysql-zrm
mybackup:check:INFO:    port=3306
mybackup:check:INFO:    socket=/var/lib/mysql/mysql.sock
mybackup:check:INFO:    mail-policy=always
mybackup:check:INFO:    backup-mode=logical
mybackup:check:INFO:    password=******
mybackup:check:INFO:    backup-type=regular
mybackup:check:INFO:    user=backup
mybackup:check:INFO:    mysql-binlog-path=/var/lib/mysql
mybackup:check:INFO: }
mybackup:check:INFO: Getting mysql variables
mybackup:check:INFO: mysqladmin --user="backup" --password="*****" --port="3306" --socket="/var/lib/mysql/mysql.sock" variables
mybackup:check:INFO: datadir is /var/lib/mysql/
mybackup:check:INFO: mysql_version is 5.1.71-log
mybackup:check:INFO: InnoDB data file are /var/lib/mysql/ibdata1
mybackup:check:INFO: InnoDB log dir is /var/lib/mysql/.
mybackup:check:INFO: Configuration check successful


問題ないようです。

バックアップしてみる


テスト用に、データベースとテーブルを作ります。

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mybackup           |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> SELECT id, name FROM sample;
+------+--------+
| id   | name   |
+------+--------+
|    1 | test   |
|    2 | sample |
+------+--------+
2 rows in set (0.01 sec)


手動でフルバックアップを取得してみます。

# mysql-zrm-scheduler --now --backup-level 0 --backup-set mybackup
schedule:INFO: ZRM for MySQL - version built from source
Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log
backup:INFO: ZRM for MySQL - version built from source
mybackup:backup:INFO: START OF BACKUP
mybackup:backup:INFO: PHASE START: Initialization
mybackup:backup:INFO: The quick backup-type is supported only for snapshot backups. Setting backup-type to 'regular'
mybackup:backup:INFO: ZRM Temporary configuration file = /etc/mysql-zrm/mybackup/tmpyuZ3W.conf
mybackup:backup:INFO: {
mybackup:backup:INFO:   quiet=0
mybackup:backup:INFO:   verbose=1
mybackup:backup:INFO:   retention-policy=2W
mybackup:backup:INFO:   backup-level=0
mybackup:backup:INFO:   tmpdir=/tmp
mybackup:backup:INFO:   extra-mysqldump-options=--skip-events --ignore-table=mysql.event
mybackup:backup:INFO:   all-databases=1
mybackup:backup:INFO:   destination=/var/lib/mysql-zrm
mybackup:backup:INFO:   port=3306
mybackup:backup:INFO:   socket=/var/lib/mysql/mysql.sock
mybackup:backup:INFO:   logical-parallel=0
mybackup:backup:INFO:   mail-policy=always
mybackup:backup:INFO:   backup-mode=logical
mybackup:backup:INFO:   password=******
mybackup:backup:INFO:   backup-type=regular
mybackup:backup:INFO:   user=backup
mybackup:backup:INFO:   mysql-binlog-path=/var/lib/mysql
mybackup:backup:INFO: }
mybackup:backup:INFO: Getting mysql variables
mybackup:backup:INFO: mysqladmin --user="backup" --password="*****" --port="3306" --socket="/var/lib/mysql/mysql.sock" variables
mybackup:backup:INFO: datadir is /var/lib/mysql/
mybackup:backup:INFO: mysql_version is 5.1.71-log
mybackup:backup:INFO: InnoDB data file are /var/lib/mysql/ibdata1
mybackup:backup:INFO: InnoDB log dir is /var/lib/mysql/.
mybackup:backup:INFO: backup set being used is mybackup
mybackup:backup:INFO: backup-set=mybackup
mybackup:backup:INFO: backup-date=20140113210514
mybackup:backup:INFO: mysql-server-os=Linux/Unix
mybackup:backup:INFO: backup-type=regular
mybackup:backup:INFO: host=localhost
mybackup:backup:INFO: backup-date-epoch=1389614714
mybackup:backup:INFO: retention-policy=2W
mybackup:backup:INFO: mysql-zrm-version=ZRM for MySQL - version built from source
mybackup:backup:INFO: mysql-version=5.1.71-log
mybackup:backup:INFO: backup-directory=/var/lib/mysql-zrm/mybackup/20140113210514
mybackup:backup:INFO: backup-level=0
mybackup:backup:INFO: backup-mode=logical
mybackup:backup:INFO: PHASE END: Initialization
mybackup:backup:INFO: PHASE START: Running pre backup plugin
mybackup:backup:INFO: Executing pre-backup-plugin
mybackup:backup:INFO: PHASE END: Running pre backup plugin
mybackup:backup:INFO: PHASE START: Flushing logs
mybackup:backup:INFO: Flushing the logs 
mybackup:backup:INFO: mysqladmin --user="backup" --password="*****" --port="3306" --socket="/var/lib/mysql/mysql.sock" flush-logs
mybackup:backup:INFO: Getting master logname using command mysql --user="backup" --password="*****" --port="3306" --socket="/var/lib/mysql/mysql.sock" -e "show master status"
mybackup:backup:INFO: PHASE END: Flushing logs
mybackup:backup:INFO: PHASE START: Creating logical backup
mybackup:backup:INFO: Getting list of Databases
mybackup:backup:INFO: mysql --user="backup" --password="*****" --port="3306" --socket="/var/lib/mysql/mysql.sock" -e "show databases;"
mybackup:backup:INFO: backup of the following databases will be done mybackup mysql test
mybackup:backup:INFO: Command used for logical backup is mysqldump --opt --extended-insert --create-options --default-character-set=utf8 --single-transaction --skip-events --ignore-table=mysql.event --user="backup" --password="*****" --port="3306" --socket="/var/lib/mysql/mysql.sock"bash -c 'mysqldump --opt --extended-insert --create-options --default-character-set=utf8 --single-transaction --skip-events --ignore-table=mysql.event --user="backup" --password="eco_bk_11" --port="3306" --socket="/var/lib/mysql/mysql.sock" --all-databases  > "/var/lib/mysql-zrm/mybackup/20140113210514/backup.sql" 2>>/tmp/xU4GoBSyLL;echo ${PIPESTATUS[@]} > /tmp/tnxz9xM3eO 2>>/tmp/xU4GoBSyLL'
mybackup:backup:INFO: Output of command: 'mysqldump output for command : bash -c 'mysqldump --opt --extended-insert --create-options --default-character-set=utf8 --single-transaction --skip-events --ignore-table=mysql.event --user="backup" --password="*****" --port="3306" --socket="/var/lib/mysql/mysql.sock" --all-databases  > "/var/lib/mysql-zrm/mybackup/20140113210514/backup.sql" 2>>/tmp/xU4GoBSyLL;echo ${PIPESTATUS[@]} > /tmp/tnxz9xM3eO 2>>/tmp/xU4GoBSyLL'' is {
0
}
mybackup:backup:INFO: Logical backup done for the following database(s)
mybackup mysql test 
mybackup:backup:INFO: logical-databases=mybackup mysql test 
logical-parallel=0
mybackup:backup:INFO: PHASE END: Creating logical backup
mybackup:backup:INFO: PHASE START: Calculating backup size & checksums 
mybackup:backup:INFO: next-binlog=mysql-bin.000009
mybackup:backup:INFO: backup-size=0.50 MB
mybackup:backup:INFO: PHASE END: Calculating backup size & checksums 
mybackup:backup:INFO: read-locks-time=00:00:00
mybackup:backup:INFO: flush-logs-time=00:00:00
mybackup:backup:INFO: backup-time=00:00:00
mybackup:backup:INFO: backup-status=Backup succeeded
mybackup:backup:INFO: Backup succeeded
mybackup:backup:INFO: PHASE START: Running post backup plugin
mybackup:backup:INFO: Executing post-backup-plugin
mybackup:backup:INFO: PHASE END: Running post backup plugin
mybackup:backup:INFO: PHASE START: Cleanup
mybackup:backup:INFO: PHASE END: Cleanup
mybackup:backup:INFO: END OF BACKUP
/usr/bin/mysql-zrm started successfully


バックアップ取得に成功したようです。

# LANG=C mysql-zrm-reporter --show backup-status-info --where backup-set=mybackup
reporter:INFO: Reporter configuration file not found, assigning default values 

REPORT TYPE : backup-status-info 

backup_set       backup_date      backup_level  backup_status    backup_type       comment
---------------------------------------------------------------------------------------------
mybackup         Mon Jan 13       0             Backup           regular           ----
                 21:21:49 2014                  succeeded


バックアップデータの検証も大丈夫なようです。

# mysql-zrm-verify-backup --backup-set mybackup --source-directory /var/lib/mysql-zrm/mybackup/20140113210
847/
verify-backup:INFO: ZRM for MySQL - version built from source
mybackup:verify-backup:INFO: Verification successful
リストアしてみる


まずは先ほど作成したデータベースを消します。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mybackup           |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mybackup           |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> DROP DATABASE mybackup;
Query OK, 1 row affected (0.02 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)


リストア対象とするバックアップデータを確認します。

# LANG=C mysql-zrm-reporter --show restore-info --where backup-set=mybackup
reporter:INFO: Reporter configuration file not found, assigning default values 

REPORT TYPE : restore-info 

backup_set       backup_date      backup_level  backup_directory           backup_status    comment
------------------------------------------------------------------------------------------------------
mybackup         Mon Jan 13       0             /var/lib/mysql-zrm/myback  Backup           ----
                 21:21:49 2014                  up/20140113212149          succeeded


リストアします。

# mysql-zrm-restore --backup-set mybacukp --source-directory /var/lib/mysql-zrm/mybackup/20140113212149/
restore:INFO: ZRM for MySQL - version built from source
mybacukp:restore:INFO: The quick backup-type is supported only for snapshot backups. Setting backup-type to 'regular'
mybacukp:restore:INFO: ZRM Temporary configuration file = /etc/mysql-zrm/mybacukp/tmpjTCuK.conf
mybacukp:restore:INFO: {
mybacukp:restore:INFO:  verbose=1
mybacukp:restore:INFO:  retention-policy=2W
mybacukp:restore:INFO:  backup-level=0
mybacukp:restore:INFO:  tmpdir=/tmp
mybacukp:restore:INFO:  extra-mysqldump-options=--skip-events --ignore-table=mysql.event
mybacukp:restore:INFO:  all-databases=1
mybacukp:restore:INFO:  destination=/var/lib/mysql-zrm
mybacukp:restore:INFO:  source-directory=/var/lib/mysql-zrm/mybackup/20140113212149/
mybacukp:restore:INFO:  port=3306
mybacukp:restore:INFO:  socket=/var/lib/mysql/mysql.sock
mybacukp:restore:INFO:  mail-policy=always
mybacukp:restore:INFO:  backup-mode=logical
mybacukp:restore:INFO:  password=******
mybacukp:restore:INFO:  backup-type=regular
mybacukp:restore:INFO:  user=backup
mybacukp:restore:INFO:  mysql-binlog-path=/var/lib/mysql
mybacukp:restore:INFO: }
mybacukp:restore:INFO: Getting mysql variables
mybacukp:restore:INFO: mysqladmin --user="backup" --password="*****" --port="3306" --socket="/var/lib/mysql/mysql.sock" variables
mybacukp:restore:INFO: datadir is /var/lib/mysql/
mybacukp:restore:INFO: mysql_version is 5.1.71-log
mybacukp:restore:INFO: InnoDB data file are /var/lib/mysql/ibdata1
mybacukp:restore:INFO: InnoDB log dir is /var/lib/mysql/.
mybacukp:restore:INFO: Executing pre-restore-plugin
mybacukp:restore:INFO: restoring using command mysql --user="backup" --password="*****" --port="3306" --socket="/var/lib/mysql/mysql.sock" -e "set character_set_client=utf8;set character_set_connection=utf8;set character_set_database=utf8;set character_set_results=utf8;set character_set_server=utf8;source /tmp/QvI6VPPj5y;"
mybacukp:restore:INFO: Restored database(s) from logical backup: mybackup mysql test 
mybacukp:restore:INFO: Executing post-restore-plugin
mybacukp:restore:INFO: Restore done in 1 seconds.


リストアできたようです。

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mybackup           |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> use mybackup;
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 id, name FROM sample;
+------+--------+
| id   | name   |
+------+--------+
|    1 | test   |
|    2 | sample |
+------+--------+
2 rows in set (0.00 sec)
おまけ

デフォルトでは「--single-transaction」オプションが付与されますので、ストレージエンジンにMyISAMを含む場合は、
以下のオプションをコンフィグに指定しておくとよさそうです。

single-transaction=never


今日はこんなところで。