Xtrabackup全量备份与恢复mysql数据库
浏览数：20 /
时间：2015年06月12日
一、Xtrabackup简单概述：
Percona Xtrabackup是开源免费的mysql数据库热备份软件，它能对InnoDB和XtraDB存储引擎的数据库非阻塞地备份（对于MyISAM的备份同样需要加表锁）。XtraBackup支持所有的Percona Server、MySQL、MariaDB和Drizzle。
XtraBackup优势 ：
1、无需停止数据库进行InnoDB热备
2、增量备份MySQL
3、流压缩到传输到其它服务器
4、能比较容易地创建主从同步
5、备份MySQL时不会增大服务器负载
二、Xtrabackup的安装
首先列出以下我当前的服务器环境
下载Xtrabackup
[root@localhost softs]# wget http://www.percona.com/downloads/XtraBackup/LATEST/RPM/rhel5/i386/percona-xtrabackup-2.1.6-702.rhel5.i386.rpm--2014-01-14 10:06:41-- http://www.percona.com/downloads/XtraBackup/LATEST/RPM/rhel5/i386/percona-xtrabackup-2.1.6-702.rhel5.i386.rpm
Resolving www.percona.com... 74.121.199.234……………………此处省略部分内容输出………………
100%[==========================>] 8,662,225 360K/s in 32s
2014-01-14 10:07:43 (1448 KB/s) - `percona-xtrabackup-2.1.6-702.rhel5.i386.rpm‘ saved [8662225/8662225]
rpm包直接安装
[root@localhost softs]# rpm -ivh percona-xtrabackup-2.1.6-702.rhel5.i386.rpm
error: Failed dependencies:
perl(DBD::mysql) is needed by percona-xtrabackup-2.1.6-702.rhel5.i386
这里需要解决依赖包的关系，我直接使用本地yum安装
[root@localhost softs]# yum install perl-DBD-MySQL -y
[root@localhost softs]# rpm -ivh percona-xtrabackup-2.1.6-702.rhel5.i386.rpm
Preparing... ########################################### [100%]
1:percona-xtrabackup ########################################### [100%]
再次尝试安装后成功
安装XtraBackup后，其实会有几个工具：
innobackupex：
这个是其实是下面三个工具的一个perl脚本封装，可以备份MyISAM, InnoDB, XtraDB表。
xtrabackup：
一个由C编译而来的二进制文件，只能备份InnoDB和XtraDB数据。
xbcrypt：
用来加密或解密备份的数据。
xbstream：
用来解压或压缩xbstream格式的压缩文件。
建议使用perl封装的innobackupex来作数据库备份，因为比较容易使用。
三、innobackupex相关参数说明
--defaults-file：指定my.cnf参数文件的位置
--apply-log：同xtrabackup的--prepare参数,一般情况下,在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据 文件仍处理不一致状态。--apply-log的作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态。
--copy-back：做数据恢复时将备份数据文件拷贝到MySQL服务器的datadir
--remote-host=HOSTNAME： 通过ssh将备份数据存储到进程服务器上
--stream=[tar]：备份文件输出格式, 该文件可在XtarBackup binary文件中获得. 在使用参数stream=tar备份的时候,你的xtrabackup_logfile可能会临时放在/tmp目录下,如果你备份的时候并发写入较大的话,xtrabackup_logfile可能会很大(5G+),很可能会占满你的/tmp目录,可以通过参数--tmpdir指定目录来解决这个问题.
--tmpdir=DIRECTORY：当有指定--remote-host or --stream时, 事务日志临时存储的目录, 默认采用MySQL配置文件中所指定的临时目录tmpdir
--redo-only --apply-log：强制备份日志时只redo,跳过rollback,这在做增量备份时非常必要
--use-memory=*：该参数在prepare的时候使用,控制prepare时innodb实例使用的内存
--databases=LIST：列出需要备份的databases,如果没有指定该参数,所有包含MyISAM和InnoDB表的database都会被备份
--slave-info：备份从库, 加上--slave-info备份目录下会多生成一个xtrabackup_slave_info 文件, 这里会保存主日志文件以及偏移, 文件内容类似于:CHANGE MASTER TO MASTER_LOG_FILE=‘‘, MASTER_LOG_POS=0
--socket=SOCKET：指定mysql.sock所在位置，以便备份进程登录mysql。
四、全量备份
[root@localhost tmp]# mkdir /backup
[root@localhost tmp]# innobackupex --user=root --password=123456 --defaults-file=/usr/local/mysql/etc/my.cnf --port=3306 /backup
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p
140119 05:46:11 innobackupex: Connecting to MySQL server with DSN ‘dbi:mysql:;mysql_read_default_file=/usr/local/mysql/etc/my.cnf;mysql_read_default_group=xtrabackup;port=3306‘ as ‘root‘ (using password: YES).
140119 05:46:11 innobackupex: Connected to MySQL server
140119 05:46:12 innobackupex: Executing a version check against the server...
140119 05:46:17 innobackupex: Done.
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".
innobackupex: Using mysql server version 5.6.12-debug-log
innobackupex: Created backup directory /backup/2014-01-19_05-46-19
140119 05:46:19 innobackupex: Starting ibbackup with command: xtrabackup_56 --defaults-file="/usr/local/mysql/etc/my.cnf" --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/backup/2014-01-19_05-46-19 --tmpdir=/tmp
innobackupex: Waiting for ibbackup (pid=2458) to suspend
innobackupex: Suspend file ‘/backup/2014-01-19_05-46-19/xtrabackup_suspended_2‘
xtrabackup_56 version 2.1.6 for MySQL server 5.6.11 Linux (i686) (revision id: 702)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /database
xtrabackup: using the following InnoDB configuration:
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 = 50331648
>> log scanned up to (2000813)
InnoDB: Allocated tablespace 2, old maximum was 0
[01] Copying ./ibdata1 to /backup/2014-01-19_05-46-19/ibdata1
>> log scanned up to (2000813)
>> log scanned up to (2000813)
>> log scanned up to (2000813)
>> log scanned up to (2000813)
>> log scanned up to (2000813)
[01] ...done
[01] Copying ./mysql/innodb_index_stats.ibd to /backup/2014-01-19_05-46-19/mysql/innodb_index_stats.ibd
[01] ...done
[01] Copying ./mysql/slave_worker_info.ibd to /backup/2014-01-19_05-46-19/mysql/slave_worker_info.ibd
[01] ...done
[01] Copying ./mysql/innodb_table_stats.ibd to /backup/2014-01-19_05-46-19/mysql/innodb_table_stats.ibd
[01] ...done
[01] Copying ./mysql/slave_relay_log_info.ibd to /backup/2014-01-19_05-46-19/mysql/slave_relay_log_info.ibd
[01] ...done
[01] Copying ./mysql/slave_master_info.ibd to /backup/2014-01-19_05-46-19/mysql/slave_master_info.ibd
…………………………此处省略大量内容输出…………………………
innobackupex: Backing up file ‘/database/supor/productnotes.MYI‘
innobackupex: Backing up file ‘/database/supor/productnotes.frm‘
innobackupex: Backing up file ‘/database/supor/orders.frm‘
innobackupex: Backing up file ‘/database/supor/customers.frm‘
innobackupex: Backing up file ‘/database/supor/orderitems.frm‘
innobackupex: Backing up file ‘/database/supor/productnotes.MYD‘
140119 05:46:26 innobackupex: Finished backing up non-InnoDB tables and files
140119 05:46:26 innobackupex: Waiting for log copying to finish
xtrabackup: The latest check point (for incremental): ‘2000813‘
xtrabackup: Stopping log copying thread.
.>> log scanned up to (2000813)
xtrabackup: Creating suspend file ‘/backup/2014-01-19_05-46-19/xtrabackup_log_copied‘ with pid ‘2458‘
xtrabackup: Transaction log of lsn (2000813) to (2000813) was copied.
140119 05:46:27 innobackupex: All tables unlocked
innobackupex: Backup created in directory ‘/backup/2014-01-19_05-46-19‘
140119 05:46:27 innobackupex: Connection to database server closed
140119 05:46:28 innobackupex: completed OK!
[root@localhost tmp]# innobackupex --user=root --password=123456 --defaults-file=/usr/local/mysql/etc/my.cnf --port=3306 /backup
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p
140119 05:46:11 innobackupex: Connecting to MySQL server with DSN ‘dbi:mysql:;mysql_read_default_file=/usr/local/mysql/etc/my.cnf;mysql_read_default_group=xtrabackup;port=3306‘ as ‘root‘ (using password: YES).
140119 05:46:11 innobackupex: Connected to MySQL server
140119 05:46:12 innobackupex: Executing a version check against the server...
140119 05:46:17 innobackupex: Done.
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".
innobackupex: Using mysql server version 5.6.12-debug-log
innobackupex: Created backup directory /backup/2014-01-19_05-46-19
140119 05:46:19 innobackupex: Starting ibbackup with command: xtrabackup_56 --defaults-file="/usr/local/mysql/etc/my.cnf" --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/backup/2014-01-19_05-46-19 --tmpdir=/tmp
innobackupex: Waiting for ibbackup (pid=2458) to suspend
innobackupex: Suspend file ‘/backup/2014-01-19_05-46-19/xtrabackup_suspended_2‘
xtrabackup_56 version 2.1.6 for MySQL server 5.6.11 Linux (i686) (revision id: 702)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /database
xtrabackup: using the following InnoDB configuration:
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 = 50331648
>> log scanned up to (2000813)
InnoDB: Allocated tablespace 2, old maximum was 0
[01] Copying ./ibdata1 to /backup/2014-01-19_05-46-19/ibdata1
>> log scanned up to (2000813)
>> log scanned up to (2000813)
>> log scanned up to (2000813)
>> log scanned up to (2000813)
>> log scanned up to (2000813)
[01] ...done
[01] Copying ./mysql/innodb_index_stats.ibd to /backup/2014-01-19_05-46-19/mysql/innodb_index_stats.ibd
[01] ...done
[01] Copying ./mysql/slave_worker_info.ibd to /backup/2014-01-19_05-46-19/mysql/slave_worker_info.ibd
[01] ...done
[01] Copying ./mysql/innodb_table_stats.ibd to /backup/2014-01-19_05-46-19/mysql/innodb_table_stats.ibd
[01] ...done
[01] Copying ./mysql/slave_relay_log_info.ibd to /backup/2014-01-19_05-46-19/mysql/slave_relay_log_info.ibd
[01] ...done
[01] Copying ./mysql/slave_master_info.ibd to /backup/2014-01-19_05-46-19/mysql/slave_master_info.ibd
…………………………此处省略大量内容输出…………………………
innobackupex: Backing up file ‘/database/supor/productnotes.MYI‘
innobackupex: Backing up file ‘/database/supor/productnotes.frm‘
innobackupex: Backing up file ‘/database/supor/orders.frm‘
innobackupex: Backing up file ‘/database/supor/customers.frm‘
innobackupex: Backing up file ‘/database/supor/orderitems.frm‘
innobackupex: Backing up file ‘/database/supor/productnotes.MYD‘
140119 05:46:26 innobackupex: Finished backing up non-InnoDB tables and files
140119 05:46:26 innobackupex: Waiting for log copying to finish
xtrabackup: The latest check point (for incremental): ‘2000813‘
xtrabackup: Stopping log copying thread.
.>> log scanned up to (2000813)
xtrabackup: Creating suspend file ‘/backup/2014-01-19_05-46-19/xtrabackup_log_copied‘ with pid ‘2458‘
xtrabackup: Transaction log of lsn (2000813) to (2000813) was copied.
140119 05:46:27 innobackupex: All tables unlocked
innobackupex: Backup created in directory ‘/backup/2014-01-19_05-46-19‘
140119 05:46:27 innobackupex: Connection to database server closed
140119 05:46:28 innobackupex: completed OK!
备份成功
五、全量备份恢复
现在我模拟将数据库的所有文件误删除，然后进行数据恢复
[root@localhost database]# rm -rf *
[root@localhost database]# ls
[root@localhost database]#
[root@localhost database]# innobackupex --defaults-file=/usr/local/mysql/etc/my.cnf --user=root --password=123456 --apply-log /backup/2014-01-19_05-57-08/
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.
IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints "completed OK!".
140119 06:00:47 innobackupex: Starting ibbackup with command: xtrabackup_56 --defaults-file="/usr/local/mysql/etc/my.cnf" --defaults-group="mysqld" --prepare --target-dir=/backup/2014-01-19_05-57-08 --tmpdir=/tmp
xtrabackup_56 version 2.1.6 for MySQL server 5.6.11 Linux (i686) (revision id: 702)
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Not using CPU crc32 instructions
……………………………………………………………………………………
[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
InnoDB: FTS optimize thread exiting.
I……………………………………………………………………………………
innobackupex: Creating directory ‘/database/mysql‘
innobackupex: Copying ‘/backup/2014-01-19_05-57-08/mysql/help_keyword.frm‘ to ‘/database/mysql/help_keyword.frm‘
innobackupex: Copying ‘/backup/2014-01-19_05-57-08/mysql/user.MYI‘ to ‘/database/mysql/user.MYI‘
innobackupex: Copying ‘/backup/2014-01-19_05-57-08/mysql/help_relation.frm‘ to ‘/database/mysql/help_relation.frm‘
innobackupex: Copying ‘/backup/2014-01-19_05-57-08/mysql/slow_log.CSV‘ to ‘/database/mysql/slow_log.CSV‘
innobackupex: Copying ‘/backup/2014-01-19_05-57-08/mysql/time_zone_leap_second.frm‘ to ‘/database/mysql/time_zone_leap_second.frm‘
innobackupex: Copying ‘/backup/2014-01-19_05-57-08/mysql/time_zone.MYI‘ to ‘/database/mysql/time_zone.MYI‘
innobackupex: Copying ‘/backup/2014-01-19_05-57-08/mysql/time_zone_transition.MYI‘ to ‘/database/mysql/time_zone_transition.MYI‘
innobackupex: Copying ‘/backup/2014-01-19_05-57-08/mysql/slave_master_info.frm‘ to ‘/database/mysql/slave_master_info.frm‘
innobackupex: Copying ‘/backup/2014-01-19_05-57-08/mysql/help_category.MYI‘ to ‘/database/mysql/help_category.MYI‘
innobackupex: Copying ‘/backup/2014-01-19_05-57-08/mysql/procs_priv.MYI‘ to ‘/database/mysql/procs_priv.MYI‘
innobackupex: Copying ‘/backup/2014-01-19_05-57-08/mysql/help_category.MYD‘ to ‘/database/mysql/help_category.MYD‘
innobackupex: Copying ‘/backup/2014-01-19_05-57-08/mysql/func.MYI‘ to ‘/database/mysql/func.MYI‘
innobackupex: Copying ‘/backup/2014-01-19_05-57-08/mysql/tables_priv.frm‘ to ‘/database/mysql/tables_priv.frm‘
innobackupex: Copying ‘/backup/2014-01-19_05-57-08/mysql/time_zone_transition.MYD‘ to ‘/database/mysql/time_zone_transition.MYD‘
…………………………………………此处省略大量数据输出………………………………
innobackupex: Copying ‘/backup/2014-01-19_05-57-08/performance_schema/performance_timers.frm‘ to ‘/database/performance_schema/performance_timers.frm‘
innobackupex: Creating directory ‘/database/test‘
innobackupex: Starting to copy InnoDB system tablespace
innobackupex: in ‘/backup/2014-01-19_05-57-08‘
innobackupex: back to original InnoDB data directory ‘/database‘
innobackupex: Copying ‘/backup/2014-01-19_05-57-08/ibdata1‘ to ‘/database/ibdata1‘
innobackupex: Starting to copy InnoDB undo tablespaces
innobackupex: in ‘/backup/2014-01-19_05-57-08‘
innobackupex: back to ‘/database‘
innobackupex: Starting to copy InnoDB log files
innobackupex: in ‘/backup/2014-01-19_05-57-08‘
innobackupex: back to original InnoDB log directory ‘/database‘
innobackupex: Copying ‘/backup/2014-01-19_05-57-08/ib_logfile0‘ to ‘/database/ib_logfile0‘
innobackupex: Copying ‘/backup/2014-01-19_05-57-08/ib_logfile1‘ to ‘/database/ib_logfile1‘
innobackupex: Finished copying back files.
140119 06:01:31 innobackupex: completed OK! ###恢复完成
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.
IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints "completed OK!".
140119 06:00:47 innobackupex: Starting ibbackup with command: xtrabackup_56 --defaults-file="/usr/local/mysql/etc/my.cnf" --defaults-group="mysqld" --prepare --target-dir=/backup/2014-01-19_05-57-08 --tmpdir=/tmp
xtrabackup_56 version 2.1.6 for MySQL server 5.6.11 Linux (i686) (revision id: 702)
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Not using CPU crc32 instructions
……………………………………………………………………………………
[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
InnoDB: FTS optimize thread exiting.
I……………………………………………………………………………………
innobackupex: Creating directory ‘/database/mysql‘
innobackupex: Copying ‘/backup/2014-01-19_05-57-08/mysql/help_keyword.frm‘ to ‘/database/mysql/help_keyword.frm‘
innobackupex: Copying ‘/backup/2014-01-19_05-57-08/mysql/user.MYI‘ to ‘/database/mysql/user.MYI‘
innobackupex: Copying ‘/backup/2014-01-19_05-57-08/mysql/help_relation.frm‘ to ‘/database/mysql/help_relation.frm‘
innobackupex: Copying ‘/backup/2014-01-19_05-57-08/mysql/slow_log.CSV‘ to ‘/database/mysql/slow_log.CSV‘
innobackupex: Copying ‘/backup/2014-01-19_05-57-08/mysql/time_zone_leap_second.frm‘ to ‘/database/mysql/time_zone_leap_second.frm‘
innobackupex: Copying ‘/backup/2014-01-19_05-57-08/mysql/time_zone.MYI‘ to ‘/database/mysql/time_zone.MYI‘
innobackupex: Copying ‘/backup/2014-01-19_05-57-08/mysql/time_zone_transition.MYI‘ to ‘/database/mysql/time_zone_transition.MYI‘
innobackupex: Copying ‘/backup/2014-01-19_05-57-08/mysql/slave_master_info.frm‘ to ‘/database/mysql/slave_master_info.frm‘
innobackupex: Copying ‘/backup/2014-01-19_05-57-08/mysql/help_category.MYI‘ to ‘/database/mysql/help_category.MYI‘
innobackupex: Copying ‘/backup/2014-01-19_05-57-08/mysql/procs_priv.MYI‘ to ‘/database/mysql/procs_priv.MYI‘
innobackupex: Copying ‘/backup/2014-01-19_05-57-08/mysql/help_category.MYD‘ to ‘/database/mysql/help_category.MYD‘
innobackupex: Copying ‘/backup/2014-01-19_05-57-08/mysql/func.MYI‘ to ‘/database/mysql/func.MYI‘
innobackupex: Copying ‘/backup/2014-01-19_05-57-08/mysql/tables_priv.frm‘ to ‘/database/mysql/tables_priv.frm‘
innobackupex: Copying ‘/backup/2014-01-19_05-57-08/mysql/time_zone_transition.MYD‘ to ‘/database/mysql/time_zone_transition.MYD‘
…………………………………………此处省略大量数据输出………………………………
innobackupex: Copying ‘/backup/2014-01-19_05-57-08/performance_schema/performance_timers.frm‘ to ‘/database/performance_schema/performance_timers.frm‘
innobackupex: Creating directory ‘/database/test‘
innobackupex: Starting to copy InnoDB system tablespace
innobackupex: in ‘/backup/2014-01-19_05-57-08‘
innobackupex: back to original InnoDB data directory ‘/database‘
innobackupex: Copying ‘/backup/2014-01-19_05-57-08/ibdata1‘ to ‘/database/ibdata1‘
innobackupex: Starting to copy InnoDB undo tablespaces
innobackupex: in ‘/backup/2014-01-19_05-57-08‘
innobackupex: back to ‘/database‘
innobackupex: Starting to copy InnoDB log files
innobackupex: in ‘/backup/2014-01-19_05-57-08‘
innobackupex: back to original InnoDB log directory ‘/database‘
innobackupex: Copying ‘/backup/2014-01-19_05-57-08/ib_logfile0‘ to ‘/database/ib_logfile0‘
innobackupex: Copying ‘/backup/2014-01-19_05-57-08/ib_logfile1‘ to ‘/database/ib_logfile1‘
innobackupex: Finished copying back files.
140119 06:01:31 innobackupex: completed OK! ###恢复完成
接下来检查数据是否恢复
[root@localhost database]# ls
ibdata1 ib_logfile0 ib_logfile1 mysql performance_schema test
[root@localhost database]# ls
ibdata1 ib_logfile0 ib_logfile1 mysql performance_schema test
[root@localhost database]# chown -R mysql.mysql /database/
[root@localhost ~]# /etc/init.d/mysql5 restart
Shutting down MySQL.. [ OK ]
Starting MySQL............ [ OK ]
全量备份恢复成功
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
