xtrabackup备份与恢复数据库
来源：互联网 发布：淘宝死号怎样恢复 编辑：程序博客网 时间：2024/05/23 00:10
之前我在维护mysql数据库的时候，使用mysqldump来进行备份与恢复，在备份的时候锁住表，然后全部备份，在数据少的时候没问题，但如果数据很多，不允许锁表，同时需要恢复数据块的情况，mysqldump就不适合了，我在恢复一个4G数据文件的数据库的时候，恢复的数据是使用mysqldump的数据，恢复了3个小时还没有反应，造成的影响很严重，所以我开始寻找其他的别发软件来满足以上的需求，幸好找到了，就是使用xtrabackup来进行备份与恢复，恢复4G数据文件的数据库，仅需要14秒，同时在备份的时候不会锁表，而且支持增量备份，所以把我的比较分享给大家，希望对大家有益！
Xtrabackup 是percona公司的开源项目，用以实现类似innodb官方的热备份工具InnoDB Hot Backup的功能，能够非常快速地备份与恢复mysql数据库。 Xtrabackup中包含两个工具：
xtrabackup是用于热备份innodb, xtradb表中数据的工具，不能备份其他类型的表，也不能备份数据表结构；
innobackupex是将xtrabackup进行封装的perl脚本，提供了备份myisam表的能力。
由于innobackupex的功能更为全面和完善，所以，本文以innobackupex作为基础进行研究描述。www.it165.net
下面介绍xtrabackup的全部、增量的备份与恢复。
一、下载与安装
1、下载
wget http://www.percona.com/redir/downloads/XtraBackup/XtraBackup-1.6.7/binary/Linux/x86_64/xtrabackup-1.6.7.tar.gz
2、安装依赖库
如果是debian系列的话
apt-get install debhelper autotools-dev libaio-dev wget automake libtool bison libncurses-dev libz-dev cmake bzr
如果是redhat系列的话
yum install cmake gcc gcc-c++ libaio libaio-devel automake autoconf bzr bison libtool ncurses-devel zlib-devel
3、解压
tar zxvf xtrabackup-1.6.7.tar.gz
4、进入目录
cd xtrabackup-1.6.7
5、复制
cd bin
cp * /usr/bin
然后就安装完成了，下面开始备份
其中，
innobackupex是我们要使用的备份工具；
xtrabackup是被封装在innobackupex之中的，innobackupex运行时需要调用它；
xtrabackup_51是xtrabackup运行时需要调用的工具；
tar4ibd是以tar流的形式产生备份时用来打包的工具。
6、对某个数据库进行全部备份的命令介绍
innobackupex --user=root --password=123456 --defaults-file=/etc/mysql/my.cnf --database=test --stream=tar /tmp/data/ 2>/tmp/data/err.log|gzip 1>/tmp/data/test.tar.gz
说明：
--database=test 单独对test数据库做备份 ，若是不添加此参数那就那就是对全库做备份
2>/tmp/data/err.log 输出信息写入日志中
1>/tmp/data/test.tar.gz 打包压缩存储到该文件中
二、对数据库的全部备份与恢复
下面开始测试xtrabackup的全部备份
（1）先进入mysql里创建一个新的test数据库
view sourceprint?
01.root@client2:/tmp# mysql -u root -p 02.Enter password: 03.Welcome to the MySQL monitor. Commands end with ; or \g. 04.Your MySQL connection id is 40 05.Server version: 5.5.28-0ubuntu0.12.04.3-log (Ubuntu) 06. 07.Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. 08. 09.Oracle is a registered trademark of Oracle Corporation and/or its 10.affiliates. Other names may be trademarks of their respective 11.owners. 12. 13.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 14.mysql> drop database test; 15.Query OK, 3 rows affected (0.13 sec) 16.mysql> create database test; 17.Query OK, 1 row affected (0.00 sec) 18. 19.mysql> use test; 20.Database changed 21.mysql> create table test (id int); 22.Query OK, 0 rows affected (0.06 sec) 23.mysql> insert into test values(1); 24.Query OK, 1 row affected (0.04 sec) 25. 26.mysql> insert into test values(2); 27.Query OK, 1 row affected (0.01 sec) 28. 29.mysql> insert into test values(3); 30.Query OK, 1 row affected (0.00 sec) 31. 32.mysql> insert into test values(4); 33.Query OK, 1 row affected (0.00 sec) 34. 35.mysql> insert into test values(5); 36.Query OK, 1 row affected (0.01 sec) 37. 38.mysql> select * from test; 39.+------+ 40.| id | 41.+------+ 42.| 1 | 43.| 2 | 44.| 3 | 45.| 4 | 46.| 5 | 47.+------+ 48.5 rows in set (0.00 sec) 49. 50.mysql> flush privileges; 51.Query OK, 0 rows affected (0.00 sec) （2）然后备份test的整个数据库
使用下面的backup.sh脚本
view sourceprint?
01.root@client2:/tmp# cat backup.sh 02.#!/bin/bash 03.user='root'04.passwd='123456'05.database=test06.my_config='/etc/mysql/my.cnf'07.log=$database-$(date +%Y%m%d%H%M).log 08.str=$database-$(date +%Y%m%d%H%M).tar.gz 09.backup_dir='/tmp/data'10.echo "Start to backup at $(date +%Y%m%d%H%M)" 11.if [ ! -d "$backup_dir" ];then 12.mkdir $backup_dir 13.fi 14.innobackupex --user=$user --password=$passwd --defaults-file=$my_config --database=$database --stream=tar $backup_dir 2>$backup_dir/$log | gzip1>$backup_dir/$str 15.if [ $? -eq 0 ];then 16.echo "Backup is finish! at $(date +%Y%m%d%H%M)" 17.exit 0 18.else 19.echo "Backup is Fail! at $(date +%Y%m%d%H%M)" 20.exit 1 21.fi 现在开始运行此脚本
view sourceprint?
1.root@client2:/tmp# sh backup.sh 2.Start to backup at 201303072101 3.Backup is finish! at 201303072102 然后到data里查看结果
view sourceprint?
01.root@client2:/tmp# cd data 02.root@client2:/tmp/data# ll 03.total 3272 04.drwxr-xr-x 2 root root 4096 Mar 7 21:01 ./ 05.drwxrwxrwt 13 root root 4096 Mar 7 21:02 ../ 06.-rw-r--r-- 1 root root 3780 Mar 7 21:02 test-201303072101.log 07.-rw-r--r-- 1 root root 3336909 Mar 7 21:02 test-201303072101.tar.gz 08.root@client2:/tmp/data# cat test-201303072101.log 09. 10.InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy 11.and Percona Inc 2009-2012. All Rights Reserved. 12. 13.This software is published under 14.the GNU GENERAL PUBLIC LICENSE Version 2, June 1991. 15. 16.130307 21:01:39 innobackupex: Starting mysql with options: --defaults-file='/etc/mysql/my.cnf' --password=xxxxxxxx --user='root' --unbuffered -- 17.130307 21:01:39 innobackupex: Connected to database with mysql child process (pid=12441) 18.130307 21:01:45 innobackupex: Connection to database server closed 19.IMPORTANT: Please check that the backup run completes successfully. 20.At the end of a successful backup run innobackupex 21.prints "completed OK!". 22. 23.innobackupex: Using mysql Ver 14.14 Distrib 5.5.28, for debian-linux-gnu (x86_64) using readline 6.2 24.innobackupex: Using mysql server version Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. 25. 26.innobackupex: Created backup directory /tmp/data 27.130307 21:01:45 innobackupex: Starting mysql with options: --defaults-file='/etc/mysql/my.cnf' --password=xxxxxxxx --user='root' --unbuffered -- 28.130307 21:01:45 innobackupex: Connected to database with mysql child process (pid=12471) 29.130307 21:01:47 innobackupex: Connection to database server closed 30. 31.130307 21:01:47 innobackupex: Starting ibbackup with command: xtrabackup_55 --defaults-file="/etc/mysql/my.cnf" --backup --suspend-at-end --log-stream --target-dir=/tmp 32.innobackupex: Waiting for ibbackup (pid=12478) to suspend 33.innobackupex: Suspend file '/tmp/xtrabackup_suspended' 34. 35.xtrabackup: suspend-at-end is enabled. 36.xtrabackup: uses posix_fadvise(). 37.xtrabackup: cd to /var/lib/mysql 38.xtrabackup: Target instance is assumed as followings. 39.xtrabackup: innodb_data_home_dir = ./ 40.xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend 41.xtrabackup: innodb_log_group_home_dir = ./ 42.xtrabackup: innodb_log_files_in_group = 243.xtrabackup: innodb_log_file_size = 524288044.130307 21:01:47 InnoDB: Using Linux native AIO 45.xtrabackup: Stream mode. 46.>> log scanned up to (59605543) 47. 48.130307 21:01:49 innobackupex: Continuing after ibbackup has suspended 49. 50.innobackupex: Starting to backup InnoDB tables and indexes 51.innobackupex: from original InnoDB data directory '/var/lib/mysql' 52.innobackupex: Backing up as tar stream 'ibdata1' 53.130307 21:01:52 innobackupex: Starting mysql with options: --defaults-file='/etc/mysql/my.cnf' --password=xxxxxxxx --user='root' --unbuffered -- 54.130307 21:01:52 innobackupex: Connected to database with mysql child process (pid=12494) 55.>> log scanned up to (59605543) 56.130307 21:01:54 innobackupex: Starting to lock all tables... 57.>> log scanned up to (59605543) 58.>> log scanned up to (59605543) 59.130307 21:02:04 innobackupex: All tables locked and flushed to disk 60. 61.130307 21:02:04 innobackupex: Starting to backup .frm, .MRG, .MYD, .MYI, 62.innobackupex: .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV and .opt files in 63.innobackupex: subdirectories of '/var/lib/mysql' 64.innobackupex: Backing up file '/var/lib/mysql/test/test.frm' 65.innobackupex: Backing up file '/var/lib/mysql/test/db.opt' 66.130307 21:02:04 innobackupex: Finished backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ, .CSV, .CSM and .opt files 67. 68.innobackupex: Resuming ibbackup 69. 70.xtrabackup: The latest check point (for incremental): '59605543' 71.>> log scanned up to (59605543) 72.xtrabackup: Transaction log of lsn (59605543) to (59605543) was copied. 73.130307 21:02:06 innobackupex: All tables unlocked 74.130307 21:02:06 innobackupex: Connection to database server closed 75. 76.innobackupex: Backup created in directory '/tmp/data' 77.innobackupex: MySQL binlog position: filename 'mysql-bin.000022', position 107 78.innobackupex: You must use -i (--ignore-zeros) option for extraction of the tarstream. 79.130307 21:02:06 innobackupex: completed OK! 可以看到备份完成了
（3）恢复数据库
先关闭mysql服务，然后再删除test数据库文件
view sourceprint?
01.root@client2:/tmp/data# service mysql stop 02.mysql stop/waiting 03.root@client2:/tmp/data# cd /var/lib/mysql/ 04.root@client2:/var/lib/mysql# ll 05.total 77860 06.drwx------ 8 mysql mysql 4096 Mar 7 20:59 ./ 07.drwxr-xr-x 38 root root 4096 Mar 7 19:52 ../ 08.-rw-r--r-- 1 root root 0 Jan 5 14:22 debian-5.5.flag 09.drwx------ 2 mysql mysql 4096 Feb 11 17:39 django/ 10.-rw-rw---- 1 mysql mysql 69206016 Mar 7 21:02 ibdata1 11.-rw-rw---- 1 mysql mysql 5242880 Mar 7 21:02 ib_logfile0 12.-rw-rw---- 1 mysql mysql 5242880 Mar 7 21:01 ib_logfile1 13.drwx------ 2 mysql mysql 4096 Jan 5 22:55 monitor/ 14.drwx------ 2 mysql root 4096 Jan 5 14:22 mysql/ 15.-rw-rw---- 1 root root 6 Jan 5 14:22 mysql_upgrade_info 16.drwx------ 2 mysql mysql 4096 Jan 5 14:22 performance_schema/ 17.drwx------ 2 mysql mysql 4096 Mar 7 21:00 test/ 18.drwxr-xr-x 2 mysql mysql 4096 Mar 7 19:58 xtrbackup/ 19.root@client2:/var/lib/mysql# rm -rf test 20.root@client2:/var/lib/mysql# ll 21.total 77856 22.drwx------ 7 mysql mysql 4096 Mar 7 21:03 ./ 23.drwxr-xr-x 38 root root 4096 Mar 7 19:52 ../ 24.-rw-r--r-- 1 root root 0 Jan 5 14:22 debian-5.5.flag 25.drwx------ 2 mysql mysql 4096 Feb 11 17:39 django/ 26.-rw-rw---- 1 mysql mysql 69206016 Mar 7 21:02 ibdata1 27.-rw-rw---- 1 mysql mysql 5242880 Mar 7 21:02 ib_logfile0 28.-rw-rw---- 1 mysql mysql 5242880 Mar 7 21:01 ib_logfile1 29.drwx------ 2 mysql mysql 4096 Jan 5 22:55 monitor/ 30.drwx------ 2 mysql root 4096 Jan 5 14:22 mysql/ 31.-rw-rw---- 1 root root 6 Jan 5 14:22 mysql_upgrade_info 32.drwx------ 2 mysql mysql 4096 Jan 5 14:22 performance_schema/ 33.drwxr-xr-x 2 mysql mysql 4096 Mar 7 19:58 xtrbackup/ 开始恢复数据库
先把之前/tmp/data里的数据库压缩版给解压
view sourceprint?
01.root@client2:cd /tmp/data 02.root@client2:/tmp/data# tar -izxvf test-201303072101.tar.gz -C /tmp/restore/ 03.backup-my.cnf 04.ibdata1 05.xtrabackup_binlog_info 06.test/test.frm 07.test/db.opt 08.xtrabackup_logfile 09.xtrabackup_checkpoints 10.xtrabackup_binary 注意：这里tar解包必须使用-i参数，否则解压出来的文件只有一个backup-my.cnf
查看一下restore的内容
view sourceprint?
01.root@client2:/tmp/data# cd /tmp/restore/ 02.root@client2:/tmp/restore# ll 03.total 67616 04.drwxr-xr-x 3 root root 4096 Mar 7 21:03 ./ 05.drwxrwxrwt 14 root root 4096 Mar 7 21:03 ../ 06.-rw-r--r-- 1 root root 260 Mar 7 21:01 backup-my.cnf 07.-rw-rw---- 1 mysql mysql 69206016 Mar 7 21:01 ibdata1 08.drwxr-xr-x 2 root root 4096 Mar 7 21:03 test/ 09.-rw-r--r-- 1 root root 13 Mar 7 21:02 xtrabackup_binary 10.-rw-r--r-- 1 root root 23 Mar 7 21:02 xtrabackup_binlog_info 11.-rw-r--r-- 1 root root 79 Mar 7 21:02 xtrabackup_checkpoints 12.-rw-r--r-- 1 root root 2560 Mar 7 21:02 xtrabackup_logfile 然后将备份文件中的日志应用到备份文件中的数据文件上
view sourceprint?
001.root@client2:/tmp/restore# innobackupex --user=root --password=123456 --apply-log /tmp/restore/ 002. 003.InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy 004.and Percona Inc 2009-2012. All Rights Reserved. 005. 006.This software is published under 007.the GNU GENERAL PUBLIC LICENSE Version 2, June 1991. 008. 009.IMPORTANT: Please check that the apply-log run completes successfully. 010.At the end of a successful apply-log run innobackupex 011.prints "completed OK!". 012. 013. 014. 015.130307 21:04:18 innobackupex: Starting ibbackup with command: xtrabackup_55 --defaults-file="/tmp/restore/backup-my.cnf" --prepare --target-dir=/tmp/restore 016. 017.xtrabackup_55 version 1.6.7 for Percona Server 5.5.16 Linux (x86_64) (revisionid: undefined) 018.xtrabackup: cd to /tmp/restore 019.xtrabackup: This target seems to be not prepared yet. 020.xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(59605543) 021.xtrabackup: Temporary instance for recovery is set as followings. 022.xtrabackup: innodb_data_home_dir = ./ 023.xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend 024.xtrabackup: innodb_log_group_home_dir = ./ 025.xtrabackup: innodb_log_files_in_group = 1026.xtrabackup: innodb_log_file_size = 2097152027.130307 21:04:19 InnoDB: Using Linux native AIO 028.xtrabackup: Starting InnoDB instance for recovery. 029.xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter) 030.130307 21:04:19 InnoDB: The InnoDB memory heap is disabled 031.130307 21:04:19 InnoDB: Mutexes and rw_locks use GCC atomic builtins 032.130307 21:04:19 InnoDB: Compressed tables use zlib 1.2.3 033.130307 21:04:19 InnoDB: Using Linux native AIO 034.130307 21:04:19 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead 035.130307 21:04:19 InnoDB: Initializing buffer pool, size = 100.0M 036.130307 21:04:19 InnoDB: Completed initialization of buffer pool 037.130307 21:04:19 InnoDB: highest supported file format is Barracuda. 038.InnoDB: The log sequence number in ibdata files does not match 039.InnoDB: the log sequence number in the ib_logfiles! 040.130307 21:04:19 InnoDB: Database was not shut down normally! 041.InnoDB: Starting crash recovery. 042.InnoDB: Reading tablespace information from the .ibd files... 043.InnoDB: Last MySQL binlog file position 0 107, file name /var/log/mysql/mysql-bin.000022 044.130307 21:04:20 InnoDB: Waiting for the background threads to start 045.130307 21:04:21 Percona XtraDB (http://www.percona.com) 1.1.8-20.1 started; log sequence number 59605543 046. 047.[notice (again)] 048.If you use binary log and don't use any hack of group commit, 049.the binary log position seems to be: 050.InnoDB: Last MySQL binlog file position 0 107, file name /var/log/mysql/mysql-bin.000022 051. 052.xtrabackup: starting shutdown with innodb_fast_shutdown = 1053.130307 21:04:21 InnoDB: Starting shutdown... 054.130307 21:04:25 InnoDB: Shutdown completed; log sequence number 59606758 055. 056.130307 21:04:25 innobackupex: Restarting xtrabackup with command: xtrabackup_55 --defaults-file="/tmp/restore/backup-my.cnf" --prepare --target-dir=/tmp/restore 057.for creating ib_logfile* 058. 059.xtrabackup_55 version 1.6.7 for Percona Server 5.5.16 Linux (x86_64) (revisionid: undefined) 060.xtrabackup: cd to /tmp/restore 061.xtrabackup: This target seems to be already prepared. 062.xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'. 063.xtrabackup: Temporary instance for recovery is set as followings. 064.xtrabackup: innodb_data_home_dir = ./ 065.xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend 066.xtrabackup: innodb_log_group_home_dir = ./ 067.xtrabackup: innodb_log_files_in_group = 2068.xtrabackup: innodb_log_file_size = 5242880069.130307 21:04:25 InnoDB: Using Linux native AIO 070.xtrabackup: Starting InnoDB instance for recovery. 071.xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter) 072.130307 21:04:25 InnoDB: The InnoDB memory heap is disabled 073.130307 21:04:25 InnoDB: Mutexes and rw_locks use GCC atomic builtins 074.130307 21:04:25 InnoDB: Compressed tables use zlib 1.2.3 075.130307 21:04:25 InnoDB: Using Linux native AIO 076.130307 21:04:25 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead 077.130307 21:04:25 InnoDB: Initializing buffer pool, size = 100.0M 078.130307 21:04:25 InnoDB: Completed initialization of buffer pool 079.130307 21:04:25 InnoDB: Log file ./ib_logfile0 did not exist: new to be created 080.InnoDB: Setting log file ./ib_logfile0 size to 5 MB 081.InnoDB: Database physically writes the file full: wait... 082.130307 21:04:25 InnoDB: Log file ./ib_logfile1 did not exist: new to be created 083.InnoDB: Setting log file ./ib_logfile1 size to 5 MB 084.InnoDB: Database physically writes the file full: wait... 085.130307 21:04:25 InnoDB: highest supported file format is Barracuda. 086.InnoDB: The log sequence number in ibdata files does not match 087.InnoDB: the log sequence number in the ib_logfiles! 088.130307 21:04:25 InnoDB: Database was not shut down normally! 089.InnoDB: Starting crash recovery. 090.InnoDB: Reading tablespace information from the .ibd files... 091.InnoDB: Last MySQL binlog file position 0 107, file name /var/log/mysql/mysql-bin.000022 092.130307 21:04:26 InnoDB: Waiting for the background threads to start 093.130307 21:04:27 Percona XtraDB (http://www.percona.com) 1.1.8-20.1 started; log sequence number 59607052 094. 095.[notice (again)] 096.If you use binary log and don't use any hack of group commit, 097.the binary log position seems to be: 098.InnoDB: Last MySQL binlog file position 0 107, file name /var/log/mysql/mysql-bin.000022 099. 100.xtrabackup: starting shutdown with innodb_fast_shutdown = 1101.130307 21:04:27 InnoDB: Starting shutdown... 102.130307 21:04:31 InnoDB: Shutdown completed; log sequence number 59607052 103.130307 21:04:31 innobackupex: completed OK! 这里的--apply-log指明是将日志应用到数据文件上，完成之后将备份文件中的数据恢复到数据库中：
然后再查看一下当前目录内容
view sourceprint?
01.root@client2:/tmp/restore# ll 02.total 79904 03.drwxr-xr-x 3 root root 4096 Mar 7 21:04 ./ 04.drwxrwxrwt 14 root root 4096 Mar 7 21:04 ../ 05.-rw-r--r-- 1 root root 260 Mar 7 21:01 backup-my.cnf 06.-rw-rw---- 1 mysql mysql 69206016 Mar 7 21:04 ibdata1 07.-rw-r--r-- 1 root root 5242880 Mar 7 21:04 ib_logfile0 08.-rw-r--r-- 1 root root 5242880 Mar 7 21:04 ib_logfile1 09.drwxr-xr-x 2 root root 4096 Mar 7 21:03 test/ 10.-rw-r--r-- 1 root root 13 Mar 7 21:02 xtrabackup_binary 11.-rw-r--r-- 1 root root 23 Mar 7 21:02 xtrabackup_binlog_info 12.-rw-r--r-- 1 root root 36 Mar 7 21:04 xtrabackup_binlog_pos_innodb 13.-rw-r--r-- 1 root root 79 Mar 7 21:04 xtrabackup_checkpoints 14.-rw-r--r-- 1 root root 2097152 Mar 7 21:04 xtrabackup_logfile 15.然后把test目录复制到/var/lib/mysql目录 16.root@client2:/tmp/restore# rsync -avz test ib* /var/lib/mysql/ 17.sending incremental file list 18.test/ 19.test/db.opt 20.test/test.frm 21. 22.sent 381 bytes received 54 bytes 870.00 bytes/sec 23.total size is 8621 speedup is 19.82 24.root@client2:/tmp/restore# cd /var/lib/mysql/ 25.root@client2:/var/lib/mysql# ll 26.total 77860 27.drwx------ 8 mysql mysql 4096 Mar 7 21:06 ./ 28.drwxr-xr-x 38 root root 4096 Mar 7 19:52 ../ 29.-rw-r--r-- 1 root root 0 Jan 5 14:22 debian-5.5.flag 30.drwx------ 2 mysql mysql 4096 Feb 11 17:39 django/ 31.-rw-rw---- 1 mysql mysql 69206016 Mar 7 21:02 ibdata1 32.-rw-rw---- 1 mysql mysql 5242880 Mar 7 21:02 ib_logfile0 33.-rw-rw---- 1 mysql mysql 5242880 Mar 7 21:01 ib_logfile1 34.drwx------ 2 mysql mysql 4096 Jan 5 22:55 monitor/ 35.drwx------ 2 mysql root 4096 Jan 5 14:22 mysql/ 36.-rw-rw---- 1 root root 6 Jan 5 14:22 mysql_upgrade_info 37.drwx------ 2 mysql mysql 4096 Jan 5 14:22 performance_schema/ 38.drwxr-xr-x 2 root root 4096 Mar 7 21:03 test/ 39.drwxr-xr-x 2 mysql mysql 4096 Mar 7 19:58 xtrbackup/ 然后再修改test的用户与组为mysql
view sourceprint?
01.root@client2:/var/lib/mysql# chown -R mysql:mysql test 02.root@client2:/var/lib/mysql# ll 03.total 77860 04.drwx------ 8 mysql mysql 4096 Mar 7 21:06 ./ 05.drwxr-xr-x 38 root root 4096 Mar 7 19:52 ../ 06.-rw-r--r-- 1 root root 0 Jan 5 14:22 debian-5.5.flag 07.drwx------ 2 mysql mysql 4096 Feb 11 17:39 django/ 08.-rw-rw---- 1 mysql mysql 69206016 Mar 7 21:02 ibdata1 09.-rw-rw---- 1 mysql mysql 5242880 Mar 7 21:02 ib_logfile0 10.-rw-rw---- 1 mysql mysql 5242880 Mar 7 21:01 ib_logfile1 11.drwx------ 2 mysql mysql 4096 Jan 5 22:55 monitor/ 12.drwx------ 2 mysql root 4096 Jan 5 14:22 mysql/ 13.-rw-rw---- 1 root root 6 Jan 5 14:22 mysql_upgrade_info 14.drwx------ 2 mysql mysql 4096 Jan 5 14:22 performance_schema/ 15.drwxr-xr-x 2 mysql mysql 4096 Mar 7 21:03 test/ 16.drwxr-xr-x 2 mysql mysql 4096 Mar 7 19:58 xtrbackup/ 然后启动mysql，并查看test数据库的表里内容
view sourceprint?
01.root@client2:/var/lib/mysql# service mysql start 02.mysql start/running, process 12730 03.root@client2:/var/lib/mysql# mysql -u root -p 04.Enter password: 05.Welcome to the MySQL monitor. Commands end with ; or \g. 06.Your MySQL connection id is 36 07.Server version: 5.5.28-0ubuntu0.12.04.3-log (Ubuntu) 08. 09.Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. 10. 11.Oracle is a registered trademark of Oracle Corporation and/or its 12.affiliates. Other names may be trademarks of their respective 13.owners. 14. 15.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 16. 17.mysql> use test; 18.Reading table information for completion of table and column names 19.You can turn off this feature to get a quicker startup with -A 20. 21.Database changed 22.mysql> select * from test; 23.+------+ 24.| id | 25.+------+ 26.| 1 | 27.| 2 | 28.| 3 | 29.| 4 | 30.| 5 | 31.+------+ 32.5 rows in set (0.01 sec) 可以看到数据库已经恢复完成
可能大家有个疑问，为什么我这里不像很多网上的文章里是在apply-log后，使用copy-back如果使用/usr/bin/innobackupex --copy-back命令后，会报Original data directory is not empty! at /usr/local/xtrabackup/bin/innobackupex line 538.恢复的目录必须为空。经查官网，这是xtrabackup的一个BUG。
innobackupex–copy-back was run. With this bug fix, innobackupex–copy-back operation if the destination is not empty, avoiding potential data loss or a strang combination of a restored backup and previous data. Bug Fixed: #737569 (Valentine Gostev) will now error out of the did not check that MySQL datadir was empty before
innobackupex–copy-back was run. With this bug fix, innobackupex–copy-back operation if the destination is not empty, avoiding potential data loss or a strang combination of a restored backup and previous data. Bug Fixed: #737569 (Valentine Gostev) will now error out of the did not check that MySQL datadir was empty before
所以在apply-log后直接复制数据目录到数据库的位置上吧。
三、对数据库的增量备份与恢复
为了进行增量备份，先对数据库添加一些数据
view sourceprint?
01.mysql> insert into test values(11); 02.Query OK, 1 row affected (0.10 sec) 03. 04.mysql> insert into test values(12); 05.Query OK, 1 row affected (0.05 sec) 06. 07.mysql> insert into test values(13); 08.Query OK, 1 row affected (0.00 sec) 09. 10.mysql> insert into test values(14); 11.Query OK, 1 row affected (0.00 sec) 12. 13.mysql> insert into test values(15); 14.Query OK, 1 row affected (0.00 sec) 15. 16.mysql> flush privileges; 17.Query OK, 0 rows affected (0.01 sec) 18. 19.mysql> select * from test; 20.+------+ 21.| id | 22.+------+ 23.| 1 | 24.| 2 | 25.| 3 | 26.| 4 | 27.| 5 | 28.| 11 | 29.| 12 | 30.| 13 | 31.| 14 | 32.| 15 | 33.+------+ 34.10 rows in set (0.00 sec) 然后进行增量的备份
view sourceprint?
01.root@client2:/var/lib/mysql# innobackupex --user=root --password=123456 --database=test --incremental --incremental-basedir=/tmp/restore/ /tmp/data 02. 03.InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy 04.and Percona Inc 2009-2012. All Rights Reserved. 05. 06.This software is published under 07.the GNU GENERAL PUBLIC LICENSE Version 2, June 1991. 08. 09.130307 21:13:38 innobackupex: Starting mysql with options: --password=xxxxxxxx --user='root' --unbuffered -- 10.130307 21:13:38 innobackupex: Connected to database with mysql child process (pid=12864) 11.130307 21:13:44 innobackupex: Connection to database server closed 12.IMPORTANT: Please check that the backup run completes successfully. 13.At the end of a successful backup run innobackupex 14.prints "completed OK!". 15. 16.innobackupex: Using mysql Ver 14.14 Distrib 5.5.28, for debian-linux-gnu (x86_64) using readline 6.2 17.innobackupex: Using mysql server version Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. 18. 19.innobackupex: Created backup directory /tmp/data/2013-03-07_21-13-44 20.130307 21:13:44 innobackupex: Starting mysql with options: --password=xxxxxxxx --user='root' --unbuffered -- 21.130307 21:13:44 innobackupex: Connected to database with mysql child process (pid=12891) 22.130307 21:13:46 innobackupex: Connection to database server closed 23. 24.130307 21:13:46 innobackupex: Starting ibbackup with command: xtrabackup_55 --backup --suspend-at-end --target-dir=/tmp/data/2013-03-07_21-13-44 --incremental-basedir='/tmp/restore/'25.innobackupex: Waiting for ibbackup (pid=12898) to suspend 26.innobackupex: Suspend file '/tmp/data/2013-03-07_21-13-44/xtrabackup_suspended' 27. 28.xtrabackup_55 version 1.6.7 for Percona Server 5.5.16 Linux (x86_64) (revisionid: undefined) 29.incremental backup from 59605543 is enabled. 30.xtrabackup: uses posix_fadvise(). 31.xtrabackup: cd to /var/lib/mysql 32.xtrabackup: Target instance is assumed as followings. 33.xtrabackup: innodb_data_home_dir = ./ 34.xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend 35.xtrabackup: innodb_log_group_home_dir = ./ 36.xtrabackup: innodb_log_files_in_group = 237.xtrabackup: innodb_log_file_size = 524288038.130307 21:13:46 InnoDB: Using Linux native AIO 39.>> log scanned up to (59606124) 40.[01] Copying ./ibdata1 41.to /tmp/data/2013-03-07_21-13-44/ibdata1.delta 42.[01] ...done 43. 44.130307 21:13:50 innobackupex: Continuing after ibbackup has suspended 45.130307 21:13:50 innobackupex: Starting mysql with options: --password=xxxxxxxx --user='root' --unbuffered -- 46.130307 21:13:50 innobackupex: Connected to database with mysql child process (pid=12913) 47.>> log scanned up to (59606124) 48.130307 21:13:52 innobackupex: Starting to lock all tables... 49.>> log scanned up to (59606124) 50.>> log scanned up to (59606124) 51.130307 21:14:03 innobackupex: All tables locked and flushed to disk 52. 53.130307 21:14:03 innobackupex: Starting to backup .frm, .MRG, .MYD, .MYI, 54.innobackupex: .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV and .opt files in 55.innobackupex: subdirectories of '/var/lib/mysql' 56.innobackupex: Backing up file '/var/lib/mysql/test/test.frm' 57.innobackupex: Backing up file '/var/lib/mysql/test/db.opt' 58.130307 21:14:03 innobackupex: Finished backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ, .CSV, .CSM and .opt files 59. 60.innobackupex: Resuming ibbackup 61. 62.xtrabackup: The latest check point (for incremental): '59606124' 63.>> log scanned up to (59606124) 64.xtrabackup: Stopping log copying thread. 65.xtrabackup: Transaction log of lsn (59606124) to (59606124) was copied. 66.130307 21:14:05 innobackupex: All tables unlocked 67.130307 21:14:05 innobackupex: Connection to database server closed 68. 69.innobackupex: Backup created in directory '/tmp/data/2013-03-07_21-13-44' 70.innobackupex: MySQL binlog position: filename 'mysql-bin.000023', position 107 71.130307 21:14:05 innobackupex: completed OK! 其中，--incremental指明是增量备份，--incremental-basedir指定上次完整备份或者增量备份文件的位置。这里的增量备份其实只针对的是InnoDB，对于MyISAM来说，还是完整备份。
在进行增量备份的恢复之前，先关闭数据库，然后删除数据库test
view sourceprint?
01.root@client2:/var/lib/mysql# service mysql stop 02.mysql stop/waiting 03.root@client2:/var/lib/mysql# rm -rf test 04.root@client2:/var/lib/mysql# ll 05.total 77856 06.drwx------ 7 mysql mysql 4096 Mar 7 21:17 ./ 07.drwxr-xr-x 38 root root 4096 Mar 7 19:52 ../ 08.-rw-r--r-- 1 root root 0 Jan 5 14:22 debian-5.5.flag 09.drwx------ 2 mysql mysql 4096 Feb 11 17:39 django/ 10.-rw-rw---- 1 mysql mysql 69206016 Mar 7 21:17 ibdata1 11.-rw-rw---- 1 mysql mysql 5242880 Mar 7 21:17 ib_logfile0 12.-rw-rw---- 1 mysql mysql 5242880 Mar 7 21:11 ib_logfile1 13.drwx------ 2 mysql mysql 4096 Jan 5 22:55 monitor/ 14.drwx------ 2 mysql root 4096 Jan 5 14:22 mysql/ 15.-rw-rw---- 1 root root 6 Jan 5 14:22 mysql_upgrade_info 16.drwx------ 2 mysql mysql 4096 Jan 5 14:22 performance_schema/ 17.drwxr-xr-x 2 mysql mysql 4096 Mar 7 19:58 xtrbackup/ 增量备份的恢复
view sourceprint?
01.root@client2:/var/lib/mysql# innobackupex -user=root --password=123456 --defaults-file=/etc/mysql/my.cnf --apply-log /tmp/restore/ --incremental-dir=/tmp/data/2013-03-07_21-13-44/ 02. 03.InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy 04.and Percona Inc 2009-2012. All Rights Reserved. 05. 06.This software is published under 07.the GNU GENERAL PUBLIC LICENSE Version 2, June 1991. 08. 09.IMPORTANT: Please check that the apply-log run completes successfully. 10.At the end of a successful apply-log run innobackupex 11.prints "completed OK!". 12. 13. 14. 15.130307 21:18:20 innobackupex: Starting ibbackup with command: xtrabackup_55 --defaults-file="/etc/mysql/my.cnf" --prepare --target-dir=/tmp/restore --incremental-dir=/tmp/data/2013-03-07_21-13-44/ 16. 17.xtrabackup_55 version 1.6.7 for Percona Server 5.5.16 Linux (x86_64) (revisionid: undefined) 18.incremental backup from 59605543 is enabled. 19.xtrabackup: cd to /tmp/restore 20.xtrabackup: This target seems to be already prepared. 21.xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(59606124) 22.xtrabackup: page size for /tmp/data/2013-03-07_21-13-44//ibdata1.delta is 16384 bytes 23.Applying /tmp/data/2013-03-07_21-13-44//ibdata1.delta ... 24.xtrabackup: Temporary instance for recovery is set as followings. 25.xtrabackup: innodb_data_home_dir = ./ 26.xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend 27.xtrabackup: innodb_log_group_home_dir = /tmp/data/2013-03-07_21-13-44/ 28.xtrabackup: innodb_log_files_in_group = 129.xtrabackup: innodb_log_file_size = 209715230.130307 21:18:20 InnoDB: Using Linux native AIO 31.xtrabackup: Starting InnoDB instance for recovery. 32.xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter) 33.130307 21:18:20 InnoDB: The InnoDB memory heap is disabled 34.130307 21:18:20 InnoDB: Mutexes and rw_locks use GCC atomic builtins 35.130307 21:18:20 InnoDB: Compressed tables use zlib 1.2.3 36.130307 21:18:20 InnoDB: Using Linux native AIO 37.130307 21:18:20 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead 38.130307 21:18:20 InnoDB: Initializing buffer pool, size = 100.0M 39.130307 21:18:20 InnoDB: Completed initialization of buffer pool 40.130307 21:18:20 InnoDB: highest supported file format is Barracuda. 41.InnoDB: ########################################################## 42.InnoDB: WARNING! 43.InnoDB: The log sequence number in ibdata files is higher 44.InnoDB: than the log sequence number in the ib_logfiles! Are you sure 45.InnoDB: you are using the right ib_logfiles to start up the database? 46.InnoDB: Log sequence number in ib_logfiles is 59606124, log 47.InnoDB: sequence numbers stamped to ibdata file headers are between 48.InnoDB: 59607052 and 59607052. 49.InnoDB: ########################################################## 50.InnoDB: The log sequence number in ibdata files does not match 51.InnoDB: the log sequence number in the ib_logfiles! 52.130307 21:18:20 InnoDB: Database was not shut down normally! 53.InnoDB: Starting crash recovery. 54.InnoDB: Reading tablespace information from the .ibd files... 55.InnoDB: Last MySQL binlog file position 0 107, file name /var/log/mysql/mysql-bin.000023 56.130307 21:18:29 InnoDB: Waiting for the background threads to start 57.130307 21:18:30 Percona XtraDB (http://www.percona.com) 1.1.8-20.1 started; log sequence number 59606124 58. 59.[notice (again)] 60.If you use binary log and don't use any hack of group commit, 61.the binary log position seems to be: 62.InnoDB: Last MySQL binlog file position 0 107, file name /var/log/mysql/mysql-bin.000023 63. 64.xtrabackup: starting shutdown with innodb_fast_shutdown = 165.130307 21:18:30 InnoDB: Starting shutdown... 66.130307 21:18:34 InnoDB: Shutdown completed; log sequence number 59607339 67.innobackupex: Starting to copy non-InnoDB files in '/tmp/data/2013-03-07_21-13-44/' 68.innobackupex: to the full backup directory '/tmp/restore' 69.innobackupex: Copying '/tmp/data/2013-03-07_21-13-44/xtrabackup_binlog_info' to '/tmp/restore/xtrabackup_binlog_info' 70.innobackupex: Copying '/tmp/data/2013-03-07_21-13-44/test/test.frm' to '/tmp/restore/test/test.frm' 71.innobackupex: Copying '/tmp/data/2013-03-07_21-13-44/test/db.opt' to '/tmp/restore/test/db.opt' 72.130307 21:18:34 innobackupex: completed OK! 然后再进入恢复的目录/tmp/data
view sourceprint?
01.root@client2:/var/lib/mysql# cd /tmp/data 02.root@client2:/tmp/data# ll 03.total 3276 04.drwxr-xr-x 3 root root 4096 Mar 7 21:13 ./ 05.drwxrwxrwt 14 root root 4096 Mar 7 21:18 ../ 06.drwxr-xr-x 3 root root 4096 Mar 7 21:18 2013-03-07_21-13-44/ 07.-rw-r--r-- 1 root root 3780 Mar 7 21:02 test-201303072101.log 08.-rw-r--r-- 1 root root 3336909 Mar 7 21:02 test-201303072101.tar.gz 09.root@client2:/tmp/data# cd 2013-03-07_21-13-44/ 10.root@client2:/tmp/data/2013-03-07_21-13-44# ll 11.total 2288 12.drwxr-xr-x 3 root root 4096 Mar 7 21:18 ./ 13.drwxr-xr-x 3 root root 4096 Mar 7 21:13 ../ 14.-rw-r--r-- 1 root root 260 Mar 7 21:13 backup-my.cnf 15.-rw-r--r-- 1 root root 212992 Mar 7 21:13 ibdata1.delta 16.-rw-r--r-- 1 root root 18 Mar 7 21:13 ibdata1.meta 17.drwxr-xr-x 2 root root 4096 Mar 7 21:14 test/ 18.-rw-r--r-- 1 root root 13 Mar 7 21:14 xtrabackup_binary 19.-rw-r--r-- 1 root root 23 Mar 7 21:14 xtrabackup_binlog_info 20.-rw-r--r-- 1 root root 84 Mar 7 21:14 xtrabackup_checkpoints 21.-rw-r--r-- 1 root root 2097152 Mar 7 21:18 xtrabackup_logfile 跟全部备份一样，把test恢复到/var/lib/mysql里
view sourceprint?
01.root@client2:/tmp/data/2013-03-07_21-13-44# rsync -avz test ib* /var/lib/mysql/ 02.sending incremental file list 03.test/ 04.test/db.opt 05.test/test.frm 06. 07.sent 381 bytes received 54 bytes 870.00 bytes/sec 08.total size is 8621 speedup is 19.82 09.root@client2:/tmp/data/2013-03-07_21-13-44# cd /var/lib/mysql/ 10.root@client2:/var/lib/mysql# ll 11.total 77860 12.drwx------ 8 mysql mysql 4096 Mar 7 21:19 ./ 13.drwxr-xr-x 38 root root 4096 Mar 7 19:52 ../ 14.-rw-r--r-- 1 root root 0 Jan 5 14:22 debian-5.5.flag 15.drwx------ 2 mysql mysql 4096 Feb 11 17:39 django/ 16.-rw-rw---- 1 mysql mysql 69206016 Mar 7 21:17 ibdata1 17.-rw-rw---- 1 mysql mysql 5242880 Mar 7 21:17 ib_logfile0 18.-rw-rw---- 1 mysql mysql 5242880 Mar 7 21:11 ib_logfile1 19.drwx------ 2 mysql mysql 4096 Jan 5 22:55 monitor/ 20.drwx------ 2 mysql root 4096 Jan 5 14:22 mysql/ 21.-rw-rw---- 1 root root 6 Jan 5 14:22 mysql_upgrade_info 22.drwx------ 2 mysql mysql 4096 Jan 5 14:22 performance_schema/ 23.drwxr-xr-x 2 root root 4096 Mar 7 21:14 test/ 24.drwxr-xr-x 2 mysql mysql 4096 Mar 7 19:58 xtrbackup/ 然后修改用户与组
view sourceprint?
01.root@client2:/var/lib/mysql# chown -R mysql:mysql test/ 02.root@client2:/var/lib/mysql# ll 03.total 77860 04.drwx------ 8 mysql mysql 4096 Mar 7 21:19 ./ 05.drwxr-xr-x 38 root root 4096 Mar 7 19:52 ../ 06.-rw-r--r-- 1 root root 0 Jan 5 14:22 debian-5.5.flag 07.drwx------ 2 mysql mysql 4096 Feb 11 17:39 django/ 08.-rw-rw---- 1 mysql mysql 69206016 Mar 7 21:17 ibdata1 09.-rw-rw---- 1 mysql mysql 5242880 Mar 7 21:17 ib_logfile0 10.-rw-rw---- 1 mysql mysql 5242880 Mar 7 21:11 ib_logfile1 11.drwx------ 2 mysql mysql 4096 Jan 5 22:55 monitor/ 12.drwx------ 2 mysql root 4096 Jan 5 14:22 mysql/ 13.-rw-rw---- 1 root root 6 Jan 5 14:22 mysql_upgrade_info 14.drwx------ 2 mysql mysql 4096 Jan 5 14:22 performance_schema/ 15.drwxr-xr-x 2 mysql mysql 4096 Mar 7 21:14 test/ 16.drwxr-xr-x 2 mysql mysql 4096 Mar 7 19:58 xtrbackup/然后启动msyql，并查看test数据库里test表的内容
view sourceprint?
01.root@client2:/var/lib/mysql# service mysql start 02.mysql start/running, process 13109 03.root@client2:/var/lib/mysql# mysql -u root -p 04.Enter password: 05.Welcome to the MySQL monitor. Commands end with ; or \g. 06.Your MySQL connection id is 36 07.Server version: 5.5.28-0ubuntu0.12.04.3-log (Ubuntu) 08. 09.Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. 10. 11.Oracle is a registered trademark of Oracle Corporation and/or its 12.affiliates. Other names may be trademarks of their respective 13.owners. 14. 15.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 16. 17.mysql> use test 18.Reading table information for completion of table and column names 19.You can turn off this feature to get a quicker startup with -A 20. 21.Database changed 22.mysql> select * from test; 23.+------+ 24.| id | 25.+------+ 26.| 1 | 27.| 2 | 28.| 3 | 29.| 4 | 30.| 5 | 31.| 11 | 32.| 12 | 33.| 13 | 34.| 14 | 35.| 15 | 36.+------+ 37.10 rows in set (0.00 sec) 可以看到增量备份已经恢复完成。
转自：http://www.it165.net/database/html/201303/3652.html
- xtrabackup备份与恢复数据库
- 使用xtrabackup来备份与恢复数据库
- xtrabackup备份与恢复
- xtrabackup备份与恢复
- xtrabackup备份与恢复
- Xtrabackup<四、备份与恢复>
- Xtrabackup 流备份与恢复
- xtrabackup增量备份与恢复
- xtrabackup安装备份与恢复
- mysql备份与恢复-xtrabackup
- Xtrabackup全量备份与恢复mysql数据库
- MySQL之Xtrabackup备份恢复数据库
- MySQL在线备份与恢复工具 --> Xtrabackup
- Xtrabackup备份恢复常用命令与压缩测试
- Percona Xtrabackup myql 快速备份与恢复
- xtrabackup备份恢复测试
- xtrabackup备份恢复测试
- mysql xtrabackup备份恢复
- XMPP学习记录二：关于在xcode配置xmpp.framework
- 大牛们的讨论，非常棒，推荐推荐--微信支付能颠覆支付宝吗？传统企业如何互联网化？
- acad中无法调试对话框后面的断点代码的解决方案
- matlab仿真正态分布曲线
- 灵魂最美的音乐是善良
- xtrabackup备份与恢复数据库
- python处理二进制数据
- C++标准库 list使用方法
- jquery学习笔记 之二 事件与效果
- SQL之常用时间函数练习
- Log4J通用配置|注意问题 笔记
- web前端-获取用户的来源是否为新浪微博
- 电脑显示Android手机屏幕——Android Screen Monitor
- PHP协程实现多任务合作
