MYSQLオンラインバックアップ
基本的にWeb系のサービスではオンラインバックアップが基本だと思うので、
改めてMYSQLのオンラインバックアップの選択肢を確認。
ハードウエアやOSの機能を利用
LVMやストレージが提供しているSnapShot機能を利用する。
自分で簡単なスクリプトを作成すれば瞬時にオンラインで
MYSQLバックアップをする事が可能。
ソフトウエアの機能を利用したバックアップ
mysqldump
mMySQL Enterprise Backup
InnoDB Hot Backup
Xtra Backup
Xtra Backupの動作確認
32bit版はi686 64bit版はx86_64
http://www.percona.com/downloads/XtraBackup/XtraBackup-2.0.5/
http://www.percona.com/software/percona-xtrabackup
OS確認
[root@HOME001 mysql]# uname -m i686 [root@HOME001 mysql]#
ダウンロード
[root@HOME001 mysql]# wget http://www.percona.com/redir/downloads/XtraBackup/XtraBackup-2.0.5/binary/Linux/i686/percona-xtrabackup-2.0.5-499.tar.gz --2013-01-24 13:31:20-- http://www.percona.com/redir/downloads/XtraBackup/XtraBackup-2.0.5/binary/Linux/i686/percona-xtrabackup-2.0.5-499.tar.gz www.percona.com をDNSに問いあわせています... 74.121.199.234 www.percona.com|74.121.199.234|:80 に接続しています... 接続しました。 HTTP による接続要求を送信しました、応答を待っています... 302 Found 場所: /downloads/XtraBackup/XtraBackup-2.0.5/binary/Linux/i686/percona-xtrabackup-2.0.5-499.tar.gz [続く] --2013-01-24 13:31:21-- http://www.percona.com/downloads/XtraBackup/XtraBackup-2.0.5/binary/Linux/i686/percona-xtrabackup-2.0.5-499.tar.gz www.percona.com:80 への接続を再利用します。 HTTP による接続要求を送信しました、応答を待っています... 200 OK 長さ: 22328480 (21M) [application/x-gzip] `percona-xtrabackup-2.0.5-499.tar.gz' に保存中 100%[==========================================================>] 22,328,480 1.40M/s 時間 15s 2013-01-24 13:31:36 (1.45 MB/s) - `percona-xtrabackup-2.0.5-499.tar.gz' へ保存完了 [22328480/22328480] [root@HOME001 mysql]#
展開
MYSQLのホームディレクトリーのbinに展開したファイルをコピーしないとエラーが出たのでコピーしてます。
[root@HOME001 mysql]# tar zxvf percona-xtrabackup-2.0.5-499.tar.gz percona-xtrabackup-2.0.5/ percona-xtrabackup-2.0.5/bin/ percona-xtrabackup-2.0.5/bin/xtrabackup_55 percona-xtrabackup-2.0.5/bin/xtrabackup_51 percona-xtrabackup-2.0.5/bin/innobackupex percona-xtrabackup-2.0.5/bin/xtrabackup 省略… percona-xtrabackup-2.0.5/share/percona-xtrabackup-test/inc/bug723097.sql percona-xtrabackup-2.0.5/share/percona-xtrabackup-test/inc/ib_stream_common.sh percona-xtrabackup-2.0.5/share/percona-xtrabackup-test/inc/incremental_sample-db/ percona-xtrabackup-2.0.5/share/percona-xtrabackup-test/inc/incremental_sample-db/incremental_sample-schema.sql percona-xtrabackup-2.0.5/share/percona-xtrabackup-test/inc/common.sh [root@HOME001 mysql]# [root@HOME001 percona-xtrabackup-2.0.5]# pwd /home/mysql/percona-xtrabackup-2.0.5 [root@HOME001 percona-xtrabackup-2.0.5]# ls -l 合計 8 drwxr-xr-x. 2 root root 4096 1月 18 05:37 2013 bin drwxr-xr-x. 4 root root 4096 1月 18 05:37 2013 share [root@HOME001 percona-xtrabackup-2.0.5]# ls -l bin/ 合計 32272 -rwxr-xr-x. 1 root root 106390 1月 18 05:37 2013 innobackupex lrwxrwxrwx. 1 root root 12 1月 24 13:34 2013 innobackupex-1.5.1 -> innobackupex -rwxr-xr-x. 1 root root 2031819 1月 18 05:37 2013 xbstream -rwxr-xr-x. 1 root root 9809963 1月 18 05:34 2013 xtrabackup -rwxr-xr-x. 1 root root 8375751 1月 18 05:37 2013 xtrabackup_51 -rwxr-xr-x. 1 root root 12713916 1月 18 05:29 2013 xtrabackup_55 [root@HOME001 percona-xtrabackup-2.0.5]#cd bin [root@HOME001 bin]# cp -rp * /usr/local/mysql/bin/ [root@HOME001 bin]# cd /usr/local/mysql/bin/ [root@HOME001 bin]# mkdir /home/mysql/backup
InnoDBのオンラインバックアップ実行
パスワードは変数を入れてます。
[root@HOME001 bin]# ./innobackupex-1.5.1 --user root --password $b_pass /home/mysql/backup/ InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy and Percona Ireland Ltd 2009-2012. All Rights Reserved. This software is published under the GNU GENERAL PUBLIC LICENSE Version 2, June 1991. 130124 14:44:51 innobackupex-1.5.1: Starting mysql with options: --password=xxxxxxxx --user='root' --unbuffered -- 130124 14:44:51 innobackupex-1.5.1: Connected to database with mysql child process (pid=4736) 130124 14:44:57 innobackupex-1.5.1: Connection to database server closed IMPORTANT: Please check that the backup run completes successfully. At the end of a successful backup run innobackupex-1.5.1 prints "completed OK!". innobackupex-1.5.1: Using mysql Ver 14.14 Distrib 5.5.29, for linux2.6 (i686) using readline 5.1 innobackupex-1.5.1: Using mysql server version Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. innobackupex-1.5.1: Created backup directory /home/mysql/backup/2013-01-24_14-44-57 130124 14:44:57 innobackupex-1.5.1: Starting mysql with options: --password=xxxxxxxx --user='root' --unbuffered -- 130124 14:44:57 innobackupex-1.5.1: Connected to database with mysql child process (pid=4763) 130124 14:44:59 innobackupex-1.5.1: Connection to database server closed 130124 14:44:59 innobackupex-1.5.1: Starting ibbackup with command: xtrabackup_55 --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/home/mysql/backup/2013-01-24_14-44-57 innobackupex-1.5.1: Waiting for ibbackup (pid=4771) to suspend innobackupex-1.5.1: Suspend file '/home/mysql/backup/2013-01-24_14-44-57/xtrabackup_suspended' xtrabackup_55 version 2.0.5 for Percona Server 5.5.16 Linux (i686) (revision id: undefined) xtrabackup: uses posix_fadvise(). xtrabackup: cd to /usr/local/mysql/data xtrabackup: Target instance is assumed as followings. xtrabackup: innodb_data_home_dir = ./ xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend xtrabackup: innodb_log_group_home_dir = ./ xtrabackup: innodb_log_files_in_group = 2 xtrabackup: innodb_log_file_size = 8388608 >> log scanned up to (1656933) [01] Copying ./ibdata1 to /home/mysql/backup/2013-01-24_14-44-57/ibdata1 [01] ...done 130124 14:45:03 innobackupex-1.5.1: Continuing after ibbackup has suspended 130124 14:45:03 innobackupex-1.5.1: Starting mysql with options: --password=xxxxxxxx --user='root' --unbuffered -- 130124 14:45:03 innobackupex-1.5.1: Connected to database with mysql child process (pid=4785) >> log scanned up to (1656933) 130124 14:45:05 innobackupex-1.5.1: Starting to lock all tables... >> log scanned up to (1656933) >> log scanned up to (1656933) 130124 14:45:15 innobackupex-1.5.1: All tables locked and flushed to disk 130124 14:45:15 innobackupex-1.5.1: Starting to backup non-InnoDB tables and files innobackupex-1.5.1: in subdirectories of '/usr/local/mysql/data' innobackupex-1.5.1: Backing up files '/usr/local/mysql/data/performance_schema/*.{frm,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (18 files) innobackupex-1.5.1: Backing up file '/usr/local/mysql/data/DB_REPLICATION/db.opt' innobackupex-1.5.1: Backing up file '/usr/local/mysql/data/test/TABLE001.frm' innobackupex-1.5.1: Backing up files '/usr/local/mysql/data/mysql/*.{frm,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (72 files) 130124 14:45:15 innobackupex-1.5.1: Finished backing up non-InnoDB tables and files 130124 14:45:15 innobackupex-1.5.1: Waiting for log copying to finish xtrabackup: The latest check point (for incremental): '1656933' xtrabackup: Stopping log copying thread. .>> log scanned up to (1656933) xtrabackup: Transaction log of lsn (1656933) to (1656933) was copied. 130124 14:45:18 innobackupex-1.5.1: All tables unlocked 130124 14:45:18 innobackupex-1.5.1: Connection to database server closed innobackupex-1.5.1: Backup created in directory '/home/mysql/backup/2013-01-24_14-44-57' innobackupex-1.5.1: MySQL binlog position: filename 'mysql-bin.000050', position 107 130124 14:45:18 innobackupex-1.5.1: completed OK! [root@HOME001 bin]# [root@HOME001 bin]# ls -l /home/mysql/backup/ 合計 4 drwxr-xr-x. 6 root root 4096 1月 24 14:45 2013 2013-01-24_14-44-57 [root@HOME001 bin]#
並列処理を指定する事も可能
[root@HOME001 bin]# ./innobackupex-1.5.1 --user root --password $b_pass --parallel=4 /home/mysql/backup/ InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy and Percona Ireland Ltd 2009-2012. All Rights Reserved. This software is published under the GNU GENERAL PUBLIC LICENSE Version 2, June 1991. 130124 15:23:25 innobackupex-1.5.1: Starting mysql with options: --password=xxxxxxxx --user='root' --unbuffered -- 130124 15:23:25 innobackupex-1.5.1: Connected to database with mysql child process (pid=5010) 130124 15:23:31 innobackupex-1.5.1: Connection to database server closed IMPORTANT: Please check that the backup run completes successfully. At the end of a successful backup run innobackupex-1.5.1 prints "completed OK!". 省略・・・ xtrabackup: Starting 4 threads for parallel data files transfer [03] Copying ./ibdata1 to /home/mysql/backup/2013-01-24_15-23-31/ibdata1 [03] ...done 省略・・・ innobackupex-1.5.1: Backup created in directory '/home/mysql/backup/2013-01-24_15-23-31' innobackupex-1.5.1: MySQL binlog position: filename 'mysql-bin.000050', position 107 130124 15:23:52 innobackupex-1.5.1: completed OK! [root@HOME001 bin]#
リストアを試してみる
リストア時にフルバックアップで取得したデータをリストアすると、
データフォルダーが空では無いとエラーになる。
バイナリーログは差分リカバリーで必要になるので残しておく。
Oracleでいうテーブルスペースのオンライバックアップをリストアして、
ArchiveログやRedoを適用してロールフォワードするような感じ。
[root@HOME001 bin]# mysql -u root -p$b_pass -e "select count(*) from test.TABLE001;" +----------+ | count(*) | +----------+ | 28 | +----------+ [root@HOME001 bin]# mysql -u root -p$b_pass -e "truncate table test.TABLE001;" [root@HOME001 bin]# mysql -u root -p$b_pass -e "select count(*) from test.TABLE001;" +----------+ | count(*) | +----------+ | 0 | +----------+ [root@HOME001 bin]# [root@HOME001 bin]# /etc/init.d/mysql stop Shutting down MySQL.. [ OK ] [root@HOME001 bin]# mv /usr/local/mysql/data /home/mysql/backup/ [root@HOME001 bin]# mkdir /usr/local/mysql/data [root@HOME001 bin]# chown -R mysql:mysql /usr/local/mysql/data
コピーバックしてリストア
注意:レプリケーション環境ではきちんと必要なファイル等を理解した上でリカバリーして下さい。
[root@HOME001 bin]# ./innobackupex-1.5.1 --user root --password $b_pass --copy-back /home/mysql/backup/2013-01-24_15-23-31 InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy and Percona Ireland Ltd 2009-2012. All Rights Reserved. This software is published under the GNU GENERAL PUBLIC LICENSE Version 2, June 1991. IMPORTANT: Please check that the copy-back run completes successfully. At the end of a successful copy-back run innobackupex-1.5.1 prints "completed OK!". innobackupex-1.5.1: Starting to copy files in '/home/mysql/backup/2013-01-24_15-23-31' innobackupex-1.5.1: back to original data directory '/usr/local/mysql/data' innobackupex-1.5.1: Creating directory '/usr/local/mysql/data/performance_schema' innobackupex-1.5.1: Copying '/home/mysql/backup/2013-01-24_15-23-31/performance_schema/file_summary_by_event_name.frm' to '/usr/l ocal/mysql/data/performance_schema/file_summary_by_event_name.frm' innobackupex-1.5.1: Copying '/home/mysql/backup/2013-01-24_15-23-31/performance_schema/rwlock_instances.frm' to '/usr/local/mysql /data/performance_schema/rwlock_instances.frm' ....省略 innobackupex-1.5.1: Copying '/home/mysql/backup/2013-01-24_15-23-31/mysql/time_zone_transition.MYI' to '/usr/local/mysql/data/mys ql/time_zone_transition.MYI' innobackupex-1.5.1: Copying '/home/mysql/backup/2013-01-24_15-23-31/mysql/time_zone_transition_type.frm' to '/usr/local/mysql/dat a/mysql/time_zone_transition_type.frm' innobackupex-1.5.1: Creating directory '/usr/local/mysql/data/DB_REPLICATION' innobackupex-1.5.1: Copying '/home/mysql/backup/2013-01-24_15-23-31/DB_REPLICATION/db.opt' to '/usr/local/mysql/data/DB_REPLICATI ON/db.opt' innobackupex-1.5.1: Creating directory '/usr/local/mysql/data/test' innobackupex-1.5.1: Copying '/home/mysql/backup/2013-01-24_15-23-31/test/TABLE001.frm' to '/usr/local/mysql/data/test/TABLE001.frm' innobackupex-1.5.1: Starting to copy InnoDB system tablespace innobackupex-1.5.1: in '/home/mysql/backup/2013-01-24_15-23-31' innobackupex-1.5.1: back to original InnoDB data directory '/usr/local/mysql/data' innobackupex-1.5.1: Copying '/home/mysql/backup/2013-01-24_15-23-31/ibdata1' to '/usr/local/mysql/data/ibdata1' innobackupex-1.5.1: Starting to copy InnoDB log files innobackupex-1.5.1: in '/home/mysql/backup/2013-01-24_15-23-31' innobackupex-1.5.1: back to original InnoDB log directory '/usr/local/mysql/data' innobackupex-1.5.1: Finished copying back files. 130124 15:38:03 innobackupex-1.5.1: completed OK! [root@HOME001 bin]#
サービス再起動とデータの確認
[root@HOME001 bin]# /etc/init.d/mysql start Starting MySQL.... [ OK ] [root@HOME001 bin]# mysql -u root -p$b_pass -e "select count(*) from test.TABLE001;" +----------+ | count(*) | +----------+ | 28 | +----------+ [root@HOME001 bin]#
リカバリーログの内容(ログファイル再作成、ロールフォワードの履歴)
130124 15:40:42 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
130124 15:40:42 [Note] Plugin ‘FEDERATED’ is disabled.
130124 15:40:42 InnoDB: The InnoDB memory heap is disabled
130124 15:40:42 InnoDB: Mutexes and rw_locks use InnoDB’s own implementation
130124 15:40:42 InnoDB: Compressed tables use zlib 1.2.3
130124 15:40:42 InnoDB: Using Linux native AIO
130124 15:40:42 InnoDB: Initializing buffer pool, size = 32.0M
130124 15:40:42 InnoDB: Completed initialization of buffer pool
130124 15:40:42 InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 8 MB
InnoDB: Database physically writes the file full: wait…
130124 15:40:42 InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 8 MB
InnoDB: Database physically writes the file full: wait…
130124 15:40:43 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
130124 15:40:43 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files…
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer…
InnoDB: Last MySQL binlog file position 0 3917, file name ./mysql-bin.000035
130124 15:40:43 InnoDB: Waiting for the background threads to start
130124 15:40:44 InnoDB: 1.1.8 started; log sequence number 1657356
130124 15:40:44 [Note] Semi-sync replication initialized for transactions.
130124 15:40:44 [Note] Semi-sync replication enabled on the master.
130124 15:40:44 [Note] Server hostname (bind-address): ‘0.0.0.0’; port: 3306
130124 15:40:44 [Note] – ‘0.0.0.0’ resolves to ‘0.0.0.0’;
130124 15:40:44 [Note] Server socket created on IP: ‘0.0.0.0’.
130124 15:40:44 [Note] Event Scheduler: Loaded 0 events
130124 15:40:44 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: ‘5.5.29-log’ socket: ‘/tmp/mysql.sock’ port: 3306 MySQL Community Server (GPL)
130124 15:40:46 [Note] Start semi-sync binlog_dump to slave (server_id: 2), pos(mysql-bin.000050, 194)
130124 15:40:46 [Note] Stop semi-sync binlog_dump to slave (server_id: 2)
その他LVM のスナップショット機能を利用したバックアップに関しては後日検証
LVMの現状確認
[root@HOME001 ~]# fdisk -l /dev/sda ディスク /dev/sda: 60.0 GB, 60011642880 バイト ヘッド 255, セクタ 63, シリンダ 7296 Units = シリンダ数 of 16065 * 512 = 8225280 バイト セクタサイズ (論理 / 物理): 512 バイト / 512 バイト I/O size (minimum/optimal): 512 bytes / 512 bytes ディスク識別子: 0x1ecfb89b デバイス ブート 始点 終点 ブロック Id システム /dev/sda1 * 1 64 512000 83 Linux パーティション 1 は、シリンダ境界で終わっていません。 /dev/sda2 64 7296 58091520 8e Linux LVM [root@HOME001 ~]# [root@HOME001 ~]# pvscan PV /dev/sda2 VG vg_home001 lvm2 [55.40 GiB / 0 free] Total: 1 [55.40 GiB] / in use: 1 [55.40 GiB] / in no VG: 0 [0 ] [root@HOME001 ~]# [root@HOME001 ~]# vgscan Reading all physical volumes. This may take a while... Found volume group "vg_home001" using metadata type lvm2 [root@HOME001 ~]# [root@HOME001 ~]# lvscan ACTIVE '/dev/vg_home001/lv_root' [46.86 GiB] inherit ACTIVE '/dev/vg_home001/lv_home' [4.57 GiB] inherit ACTIVE '/dev/vg_home001/lv_swap' [3.97 GiB] inherit [root@HOME001 ~]# [root@HOME001 ~]# vgs VG #PV #LV #SN Attr VSize VFree vg_home001 1 3 0 wz--n- 55.40g 0 [root@HOME001 ~]# [root@HOME001 ~]# lvs LV VG Attr LSize Pool Origin Data% Move Log Copy% Convert lv_home vg_home001 -wi-ao-- 4.57g lv_root vg_home001 -wi-ao-- 46.86g lv_swap vg_home001 -wi-ao-- 3.97g [root@HOME001 ~]# [root@HOME001 ~]# df Filesystem 1K-ブロック 使用 使用可 使用% マウント位置 /dev/mapper/vg_home001-lv_root 48360312 2618204 43285532 6% / tmpfs 969472 0 969472 0% /dev/shm /dev/sda1 495844 36136 434108 8% /boot /dev/mapper/vg_home001-lv_home 4721020 441796 4039404 10% /home tmpfs 65536 0 65536 0% /usr/local/tmp [root@HOME001 ~]# [root@HOME001 ~]# swapon -s Filename Type Size Used Priority /dev/dm-1 partition 4161528 0 -1 [root@HOME001 ~]# もしボリューム名を変更したら起動、fstabの変更も忘れずに [root@HOME001 ~]# cat /boot/grub/grub.conf # grub.conf generated by anaconda # # Note that you do not have to rerun grub after making changes to this file # NOTICE: You have a /boot partition. This means that # all kernel and initrd paths are relative to /boot/, eg. # root (hd0,0) # kernel /vmlinuz-version ro root=/dev/mapper/vg_home001-lv_root # initrd /initrd-[generic-]version.img #boot=/dev/sda default=0 timeout=5 splashimage=(hd0,0)/grub/splash.xpm.gz hiddenmenu title CentOS (2.6.32-279.el6.i686) root (hd0,0) kernel /vmlinuz-2.6.32-279.el6.i686 ro root=/dev/mapper/vg_home001-lv_root rd_NO_LUKS rd_LVM_LV=vg_home001/lv_swap rd_NO_MD rd_LVM_LV=vg_home001/lv_root crashkernel=auto KEYBOARDTYPE=pc KEYTABLE=jp106 LANG=ja_JP.UTF-8 rd_NO_DM rhgb quiet initrd /initramfs-2.6.32-279.el6.i686.img [root@HOME001 ~]#
参考
MySQLバックアップ頂上決戦!! LVMスナップショット vs InnoDB Hot Backup
Percona XtraBackup
Percona XtraBackupの基本的な使い方
XtraBackupを使ってMySQLをバックアップしよう
MySQLバックアップツール比較 XtraBackup / mysqldump / Mydumper
MySQL – XtraBackupを使ったバックアップ&リストアについて
レプリケーション作成を簡単にする mysql40dump という mysqldump の wrapper を作った話
keepalived + MySQL-MHA + xtrabackupで自動フェイルオーバーと手動フェイルバック(その1 keepalived編)
Percona XtraBackupの抽出と圧縮の並列処理
LVM を導入する
LVM のパーティションを管理する
LVM のスナップショット機能を使ってみる
How to setup a slave for replication in 6 simple steps with Xtrabackup