percona mysql備份與恢複(一)

author：skate

time：2012/06/27

percona mysql備份與恢複(一)

XtraBackup 是開源的熱備mysql的軟體，可以備份InnoDB, XtraDB, and MyISAM類型的表，它是由如下三個工具組成的

1.innobackupex：有perl腳本封裝的，可以備份MyISAM, InnoDB, and XtraDB類型表，推薦使用此工具

2.xtrabackup：由c語言編譯的，隻copy表InnoDB和XtraDB的資料

3.xbstream：允許從streaming and extracting檔案壓縮/抽取xbstream格式檔案

innobackupex

innobackupex有更強的功能，它整合了xtrabackup和其他的一些功能，他不但可以全量備份/恢複，還可以基于時間的增量備份與恢複。

innobackupex備份原理

innobackupex首先調用xtrabackup來備份innodb資料檔案，當xtrabackup完成後，innobackupex就檢視檔案xtrabackup_suspended ；然後執行“FLUSH TABLES WITH READ LOCK”來備份其他的檔案

innobackupex恢複原理

innobackupex首先讀取my.cnf，檢視變量(datadir,innodb_data_home_dir,innodb_data_file_path,innodb_log_group_home_dir)對應的目錄是存在，确定相關目錄存在後，然後先copy myisam表和索引，然後在copy innodb的表、索引和日志

詳細參考：http://www.percona.com/doc/percona-xtrabackup/innobackupex/how_innobackupex_works.html

下載下傳：

[[email protected] data]# wget http://www.percona.com/redir/downloads/XtraBackup/XtraBackup-2.0.0/RPM/rhel5/i386/percona-xtrabackup-2.0.0-417.rhel5.i386.rpm

安裝：

[[email protected] data]# rpm -ivh percona-xtrabackup-2.0.0-417.rhel5.i386.rpm

全量的備份與恢複

步驟

# innobackupex --user=root   /data/backup/

# innobackupex --apply-log   /data/backup/2012-06-27_16-32-57/

先把datadir目錄更名，因為恢複時，datadir目錄不為空，恢複會終止，防止意外覆寫

# innobackupex --copy-back   /data/backup/2012-06-27_16-32-57/

備份：

[[email protected] data]# innobackupex --user=root   /data/backup/

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy

and Percona Inc 2009-2012.  All Rights Reserved.

This software is published under

the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

120627 16:32:51  innobackupex: Starting mysql with options:  --user='root' --unbuffered --

120627 16:32:51  innobackupex: Connected to database with mysql child process (pid=14905)

120627 16:32:57  innobackupex: Connection to database server closed

IMPORTANT: Please check that the backup run completes successfully.

           At the end of a successful backup run innobackupex

           prints "completed OK!".

innobackupex: Using mysql  Ver 14.12 Distrib 5.0.95, for redhat-linux-gnu (i686) using readline 5.1

innobackupex: Using mysql server version Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

innobackupex: Created backup directory /data/backup/2012-06-27_16-32-57

120627 16:32:57  innobackupex: Starting mysql with options:  --user='root' --unbuffered --

120627 16:32:57  innobackupex: Connected to database with mysql child process (pid=14929)

120627 16:32:59  innobackupex: Connection to database server closed

120627 16:32:59  innobackupex: Starting ibbackup with command: xtrabackup_55 --backup --suspend-at-end --target-dir=/data/backup/2012-06-27_16-32-57

innobackupex: Waiting for ibbackup (pid=14936) to suspend

innobackupex: Suspend file '/data/backup/2012-06-27_16-32-57/xtrabackup_suspended'

xtrabackup_55 version 2.0.0 for Percona Server 5.5.16 Linux (i686) (revision id: 417)

xtrabackup: uses posix_fadvise().

xtrabackup: cd to /data/mysql/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 = 5242880

120627 16:32:59 InnoDB: Using Linux native AIO

>> log scanned up to (1597964)

[01] Copying ./ibdata1 to /data/backup/2012-06-27_16-32-57/ibdata1

[01]        ...done

120627 16:33:01  innobackupex: Continuing after ibbackup has suspended

120627 16:33:01  innobackupex: Starting mysql with options:  --user='root' --unbuffered --

120627 16:33:01  innobackupex: Connected to database with mysql child process (pid=14950)

120627 16:33:03  innobackupex: Starting to lock all tables...

>> log scanned up to (1597964)

>> log scanned up to (1597964)

120627 16:33:13  innobackupex: All tables locked and flushed to disk

120627 16:33:13  innobackupex: Starting to backup .frm, .MRG, .MYD, .MYI,

innobackupex: .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV and .opt files in

innobackupex: subdirectories of '/data/mysql/usr/local/mysql/data/'

innobackupex: Backing up files '/data/mysql/usr/local/mysql/data//performance_schema/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (18 files)

innobackupex: Backing up files '/data/mysql/usr/local/mysql/data//mysql/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (72 files)

>> log scanned up to (1597964)

120627 16:33:14  innobackupex: Finished backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ, .CSV, .CSM and .opt files

innobackupex: Resuming ibbackup

xtrabackup: The latest check point (for incremental): '1597964'

xtrabackup: Stopping log copying thread.

.>> log scanned up to (1597964)

xtrabackup: Transaction log of lsn (1597964) to (1597964) was copied.

120627 16:33:17  innobackupex: All tables unlocked

120627 16:33:17  innobackupex: Connection to database server closed

innobackupex: Backup created in directory '/data/backup/2012-06-27_16-32-57'

innobackupex: MySQL binlog position: filename 'mysql-bin.000003', position 107

120627 16:33:17  innobackupex: completed OK!

[[email protected] data]#

在建立全量備份後，備份檔案是不能restore的，因為這個初始建立的備份隻是簡單的copy過程，而innodb是事務引擎，是以還需要通過log檔案replay committed的事務和復原uncommitted事務，這樣資料庫就達到一緻的狀态，可以restore了。

[[email protected] data]# innobackupex --apply-log   /data/backup/2012-06-27_16-32-57/

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy

and Percona Inc 2009-2012.  All Rights Reserved.

This software is published under

the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

IMPORTANT: Please check that the apply-log run completes successfully.

           At the end of a successful apply-log run innobackupex

           prints "completed OK!".

120627 16:34:56  innobackupex: Starting ibbackup with command: xtrabackup_55 --prepare --target-dir=/data/backup/2012-06-27_16-32-57

xtrabackup_55 version 2.0.0 for Percona Server 5.5.16 Linux (i686) (revision id: 417)

xtrabackup: cd to /data/backup/2012-06-27_16-32-57

xtrabackup: This target seems to be not prepared yet.

xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(1597964)

xtrabackup: Temporary instance for recovery is set 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 = 1

xtrabackup:   innodb_log_file_size = 2097152

120627 16:34:56 InnoDB: Using Linux native AIO

xtrabackup: Starting InnoDB instance for recovery.

xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)

120627 16:34:56 InnoDB: The InnoDB memory heap is disabled

120627 16:34:56 InnoDB: Mutexes and rw_locks use GCC atomic builtins

120627 16:34:56 InnoDB: Compressed tables use zlib 1.2.3

120627 16:34:56 InnoDB: Using Linux native AIO

120627 16:34:56 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead

120627 16:34:56 InnoDB: Initializing buffer pool, size = 100.0M

120627 16:34:56 InnoDB: Completed initialization of buffer pool

120627 16:34:56 InnoDB: highest supported file format is Barracuda.

120627 16:34:57  InnoDB: Waiting for the background threads to start

120627 16:34:58 Percona XtraDB (http://www.percona.com) 1.1.8-20.1 started; log sequence number 1597964

[notice (again)]

  If you use binary log and don't use any hack of group commit,

  the binary log position seems to be:

xtrabackup: starting shutdown with innodb_fast_shutdown = 1

120627 16:34:58  InnoDB: Starting shutdown...

120627 16:35:02  InnoDB: Shutdown completed; log sequence number 1597964

120627 16:35:02  innobackupex: Restarting xtrabackup with command: xtrabackup_55 --prepare --target-dir=/data/backup/2012-06-27_16-32-57

for creating ib_logfile*

xtrabackup_55 version 2.0.0 for Percona Server 5.5.16 Linux (i686) (revision id: 417)

xtrabackup: cd to /data/backup/2012-06-27_16-32-57

xtrabackup: This target seems to be already prepared.

xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.

xtrabackup: Temporary instance for recovery is set 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 = 5242880

120627 16:35:02 InnoDB: Using Linux native AIO

xtrabackup: Starting InnoDB instance for recovery.

xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)

120627 16:35:02 InnoDB: The InnoDB memory heap is disabled

120627 16:35:02 InnoDB: Mutexes and rw_locks use GCC atomic builtins

120627 16:35:02 InnoDB: Compressed tables use zlib 1.2.3

120627 16:35:02 InnoDB: Using Linux native AIO

120627 16:35:02 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead

120627 16:35:02 InnoDB: Initializing buffer pool, size = 100.0M

120627 16:35:02 InnoDB: Completed initialization of buffer pool

120627 16:35:02  InnoDB: Log file ./ib_logfile0 did not exist: new to be created

InnoDB: Setting log file ./ib_logfile0 size to 5 MB

InnoDB: Database physically writes the file full: wait...

120627 16:35:02  InnoDB: Log file ./ib_logfile1 did not exist: new to be created

InnoDB: Setting log file ./ib_logfile1 size to 5 MB

InnoDB: Database physically writes the file full: wait...

120627 16:35:02 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!

120627 16:35:02  InnoDB: Database was not shut down normally!

InnoDB: Starting crash recovery.

InnoDB: Reading tablespace information from the .ibd files...

120627 16:35:02 Percona XtraDB (http://www.percona.com) 1.1.8-20.1 started; log sequence number 1598476

[notice (again)]

  If you use binary log and don't use any hack of group commit,

  the binary log position seems to be:

xtrabackup: starting shutdown with innodb_fast_shutdown = 1

120627 16:35:02  InnoDB: Starting shutdown...

120627 16:35:07  InnoDB: Shutdown completed; log sequence number 1598476

120627 16:35:07  innobackupex: completed OK!

[[email protected] data]#

restore備份檔案要保證datadir檔案為空，否則會報如下的錯誤

[[email protected] data]# innobackupex --copy-back   /data/backup/2012-06-27_16-32-57/

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy

and Percona Inc 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

           prints "completed OK!".

Original data directory is not empty! at /usr/bin/innobackupex line 568.

[[email protected] data]#

更名datadir目錄

[[email protected] data]# mv /data/mysql/usr/local/mysql/data /data/mysql/usr/local/mysql/data.bak1

[[email protected] data]# mkdir /data/mysql/usr/local/mysql/data

restore備份檔案，達到恢複的狀态

[[email protected] data]# innobackupex --copy-back   /data/backup/2012-06-27_16-32-57/

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy

and Percona Inc 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

           prints "completed OK!".

innobackupex: Starting to copy files in '/data/backup/2012-06-27_16-32-57'

innobackupex: back to original data directory '/data/mysql/usr/local/mysql/data/'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//xtrabackup_checkpoints'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//ib_logfile1'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//ib_logfile0'

innobackupex: Creating directory '/data/mysql/usr/local/mysql/data//performance_schema'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//performance_timers.frm'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//setup_timers.frm'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//rwlock_instances.frm'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//cond_instances.frm'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//events_waits_summary_by_instance.frm'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//mutex_instances.frm'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//file_summary_by_event_name.frm'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//events_waits_history.frm'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//events_waits_current.frm'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//db.opt'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//events_waits_summary_by_thread_by_event_name.frm'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//file_summary_by_instance.frm'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//setup_instruments.frm'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//file_instances.frm'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//setup_consumers.frm'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//threads.frm'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//events_waits_history_long.frm'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//events_waits_summary_global_by_event_name.frm'

innobackupex: Creating directory '/data/mysql/usr/local/mysql/data//mysql'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//help_topic.frm'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//time_zone_transition.MYD'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//host.MYD'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//servers.MYI'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//tables_priv.MYI'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//proc.frm'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//plugin.MYD'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//func.frm'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//host.MYI'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//user.MYD'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//db.MYD'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//time_zone_leap_second.frm'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//proxies_priv.MYI'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//slow_log.CSV'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//time_zone_name.frm'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//event.MYI'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//time_zone_transition_type.MYI'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//columns_priv.MYD'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//help_category.MYI'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//general_log.frm'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//time_zone_transition_type.MYD'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//time_zone_name.MYD'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//help_keyword.MYD'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//help_topic.MYD'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//procs_priv.frm'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//help_relation.MYI'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//procs_priv.MYD'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//help_keyword.MYI'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//help_keyword.frm'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//user.MYI'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//help_category.MYD'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//time_zone_transition.frm'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//event.frm'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//func.MYD'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//slow_log.frm'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//db.frm'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//db.MYI'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//host.frm'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//time_zone_leap_second.MYD'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//help_relation.MYD'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//procs_priv.MYI'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//func.MYI'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//time_zone_name.MYI'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//help_topic.MYI'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//proc.MYI'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//servers.frm'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//proc.MYD'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//time_zone.frm'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//general_log.CSM'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//time_zone_leap_second.MYI'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//time_zone.MYI'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//columns_priv.MYI'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//time_zone.MYD'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//ndb_binlog_index.frm'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//tables_priv.frm'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//user.frm'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//proxies_priv.MYD'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//plugin.frm'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//ndb_binlog_index.MYD'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//help_category.frm'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//servers.MYD'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//tables_priv.MYD'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//time_zone_transition_type.frm'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//columns_priv.frm'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//help_relation.frm'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//ndb_binlog_index.MYI'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//proxies_priv.frm'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//general_log.CSV'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//plugin.MYI'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//event.MYD'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//slow_log.CSM'

innobackupex: Copying file '/data/mysql/usr/local/mysql/data//time_zone_transition.MYI'

innobackupex: Creating directory '/data/mysql/usr/local/mysql/data//test'

innobackupex: Starting to copy InnoDB system tablespace

innobackupex: in '/data/backup/2012-06-27_16-32-57'

innobackupex: back to original InnoDB data directory '/data/mysql/usr/local/mysql/data/'

innobackupex: Copying file '/data/backup/2012-06-27_16-32-57/ibdata1'

innobackupex: Starting to copy InnoDB log files

innobackupex: in '/data/backup/2012-06-27_16-32-57'

innobackupex: back to original InnoDB log directory '/data/mysql/usr/local/mysql/data/'

innobackupex: Finished copying back files.

120627 16:36:30  innobackupex: completed OK!

[[email protected] data]#

---------end-------

參考：http://www.percona.com/doc/percona-xtrabackup/innobackupex/innobackupex_script.html