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
今日はこんなところで。
