MySql在生产环境中是用mysqldump还是xtrabackup备份和恢复数据详解数据库
如题，究竟该使用mysqldump还是xtrabackup，要说用，两个都能备份，都支持热备，但是生产环境我们要考虑的是效率，就是不管备份还是恢复，都要快，要稳定。
之前我在维护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作为基础进行研究描述。
下面介绍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数据库
[email protected]:/tmp# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or /g.
Your MySQL connection id is 40
Server version: 5.5.28-0ubuntu0.12.04.3-log (Ubuntu)
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type help; or /h for help. Type /c to clear the current input statement.
mysql drop database test;
Query OK, 3 rows affected (0.13 sec)
mysql create database test;
Query OK, 1 row affected (0.00 sec)
mysql use test;
Database changed
mysql create table test (id int);
Query OK, 0 rows affected (0.06 sec)
mysql insert into test values(1);
Query OK, 1 row affected (0.04 sec)
mysql insert into test values(2);
Query OK, 1 row affected (0.01 sec)
mysql insert into test values(3);
Query OK, 1 row affected (0.00 sec)
mysql insert into test values(4);
Query OK, 1 row affected (0.00 sec)
mysql insert into test values(5);
Query OK, 1 row affected (0.01 sec)
mysql select * from test;
+ +
| id |
+ +
|1 |
|2 |
|3 |
|4 |
|5 |
+ +
5 rows in set (0.00 sec)
mysql flush privileges;
Query OK, 0 rows affected (0.00 sec)
（2）然后备份test的整个数据库
使用下面的backup.sh脚本
[email protected]:/tmp# cat backup.sh
#!/bin/bash
user= root
passwd= 123456
database=test
my_config= /etc/mysql/my.cnf
log=$database-$(date +%Y%m%d%H%M).log
str=$database-$(date +%Y%m%d%H%M).tar.gz
backup_dir= /tmp/data
echo Start to backup at $(date +%Y%m%d%H%M)
if [ ! -d $backup_dir ];then
mkdir $backup_dir
fi
innobackupex user=$user password=$passwd defaults-file=$my_config database=$database stream=tar $backup_dir 2 $backup_dir/$log | gzip 1 $backup_dir/$str
if [ $? -eq 0 ];then
echo Backup is finish! at $(date +%Y%m%d%H%M)
exit 0
else
echo Backup is Fail! at $(date +%Y%m%d%H%M)
exit 1
fi
现在开始运行此脚本
[email protected]:/tmp# sh backup.sh
Start to backup at 201303072101
Backup is finish! at 201303072102
然后到data里查看结果
[email protected]:/tmp# cd data
[email protected]:/tmp/data# ll
total 3272
drwxr-xr-x 2 root root4096 Mar 7 21:01 ./
drwxrwxrwt 13 root root4096 Mar 7 21:02 ../
-rw-r r 1 root root3780 Mar 7 21:02 test-201303072101.log
-rw-r r 1 root root 3336909 Mar 7 21:02 test-201303072101.tar.gz
[email protected]:/tmp/data# cat test-201303072101.log
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.
130307 21:01:39 innobackupex: Starting mysql with options: defaults-file= /etc/mysql/my.cnf password=xxxxxxxx user= root unbuffered
130307 21:01:39 innobackupex: Connected to database with mysql child process (pid=12441)
130307 21:01:45 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.14 Distrib 5.5.28, for debian-linux-gnu (x86_64) using readline 6.2
innobackupex: Using mysql server version Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
innobackupex: Created backup directory /tmp/data
130307 21:01:45 innobackupex: Starting mysql with options: defaults-file= /etc/mysql/my.cnf password=xxxxxxxx user= root unbuffered
130307 21:01:45 innobackupex: Connected to database with mysql child process (pid=12471)
130307 21:01:47 innobackupex: Connection to database server closed
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
innobackupex: Waiting for ibbackup (pid=12478) to suspend
innobackupex: Suspend file /tmp/xtrabackup_suspended
xtrabackup: suspend-at-end is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
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
130307 21:01:47 InnoDB: Using Linux native AIO
xtrabackup: Stream mode.
log scanned up to (59605543)
130307 21:01:49 innobackupex: Continuing after ibbackup has suspended
innobackupex: Starting to backup InnoDB tables and indexes
innobackupex: from original InnoDB data directory /var/lib/mysql
innobackupex: Backing up as tar stream ibdata1
130307 21:01:52 innobackupex: Starting mysql with options: defaults-file= /etc/mysql/my.cnf password=xxxxxxxx user= root unbuffered
130307 21:01:52 innobackupex: Connected to database with mysql child process (pid=12494)
log scanned up to (59605543)
130307 21:01:54 innobackupex: Starting to lock all tables
log scanned up to (59605543)
log scanned up to (59605543)
130307 21:02:04 innobackupex: All tables locked and flushed to disk
130307 21:02:04 innobackupex: Starting to backup .frm, .MRG, .MYD, .MYI,
innobackupex: .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV and .opt files in
innobackupex: subdirectories of /var/lib/mysql
innobackupex: Backing up file /var/lib/mysql/test/test.frm
innobackupex: Backing up file /var/lib/mysql/test/db.opt
130307 21:02:04 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): 59605543
log scanned up to (59605543)
xtrabackup: Transaction log of lsn (59605543) to (59605543) was copied.
130307 21:02:06 innobackupex: All tables unlocked
130307 21:02:06 innobackupex: Connection to database server closed
innobackupex: Backup created in directory /tmp/data
innobackupex: MySQL binlog position: filename mysql-bin.000022 , position 107
innobackupex: You must use -i ( ignore-zeros) option for extraction of the tar stream.
130307 21:02:06 innobackupex: completed OK!
可以看到备份完成了
（3）恢复数据库
先关闭mysql服务，然后再删除test数据库文件
[email protected]:/tmp/data# service mysql stop
mysql stop/waiting
[email protected]:/tmp/data# cd /var/lib/mysql/
[email protected]:/var/lib/mysql# ll
total 77860
drwx 8 mysql mysql 4096 Mar 7 20:59 ./
drwxr-xr-x 38 root root 4096 Mar 7 19:52 ../
-rw-r r 1 root root 0 Jan 5 14:22 debian-5.5.flag
drwx 2 mysql mysql 4096 Feb 11 17:39 django/
-rw-rw - 1 mysql mysql 69206016 Mar 7 21:02 ibdata1
-rw-rw - 1 mysql mysql 5242880 Mar 7 21:02 ib_logfile0
-rw-rw - 1 mysql mysql 5242880 Mar 7 21:01 ib_logfile1
drwx 2 mysql mysql 4096 Jan 5 22:55 monitor/
drwx 2 mysql root 4096 Jan 5 14:22 mysql/
-rw-rw - 1 root root 6 Jan 5 14:22 mysql_upgrade_info
drwx 2 mysql mysql 4096 Jan 5 14:22 performance_schema/
drwx 2 mysql mysql 4096 Mar 7 21:00 test/
drwxr-xr-x 2 mysql mysql 4096 Mar 7 19:58 xtrbackup/
[email protected]:/var/lib/mysql# rm -rf test
[email protected]:/var/lib/mysql# ll
total 77856
drwx 7 mysql mysql 4096 Mar 7 21:03 ./
drwxr-xr-x 38 root root 4096 Mar 7 19:52 ../
-rw-r r 1 root root 0 Jan 5 14:22 debian-5.5.flag
drwx 2 mysql mysql 4096 Feb 11 17:39 django/
-rw-rw - 1 mysql mysql 69206016 Mar 7 21:02 ibdata1
-rw-rw - 1 mysql mysql 5242880 Mar 7 21:02 ib_logfile0
-rw-rw - 1 mysql mysql 5242880 Mar 7 21:01 ib_logfile1
drwx 2 mysql mysql 4096 Jan 5 22:55 monitor/
drwx 2 mysql root 4096 Jan 5 14:22 mysql/
-rw-rw - 1 root root 6 Jan 5 14:22 mysql_upgrade_info
drwx 2 mysql mysql 4096 Jan 5 14:22 performance_schema/
drwxr-xr-x 2 mysql mysql 4096 Mar 7 19:58 xtrbackup/
开始恢复数据库
先把之前/tmp/data里的数据库压缩版给解压
[email protected]:cd /tmp/data
[email protected]:/tmp/data# tar -izxvf test-201303072101.tar.gz -C /tmp/restore/
backup-my.cnf
ibdata1
xtrabackup_binlog_info
test/test.frm
test/db.opt
xtrabackup_logfile
xtrabackup_checkpoints
xtrabackup_binary
注意：这里tar解包必须使用-i参数，否则解压出来的文件只有一个backup-my.cnf
查看一下restore的内容
[email protected]:/tmp/data# cd /tmp/restore/
[email protected]:/tmp/restore# ll
total 67616
drwxr-xr-x 3 root root 4096 Mar 7 21:03 ./
drwxrwxrwt 14 root root 4096 Mar 7 21:03 ../
-rw-r r 1 root root 260 Mar 7 21:01 backup-my.cnf
-rw-rw - 1 mysql mysql 69206016 Mar 7 21:01 ibdata1
drwxr-xr-x 2 root root 4096 Mar 7 21:03 test/
-rw-r r 1 root root13 Mar 7 21:02 xtrabackup_binary
-rw-r r 1 root root23 Mar 7 21:02 xtrabackup_binlog_info
-rw-r r 1 root root79 Mar 7 21:02 xtrabackup_checkpoints
-rw-r r 1 root root 2560 Mar 7 21:02 xtrabackup_logfile
然后将备份文件中的日志应用到备份文件中的数据文件上
[email protected]:/tmp/restore# innobackupex user=root password=123456 apply-log /tmp/restore/
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! .
130307 21:04:18 innobackupex: Starting ibbackup with command: xtrabackup_55 defaults-file= /tmp/restore/backup-my.cnf prepare target-dir=/tmp/restore
xtrabackup_55 version 1.6.7 for Percona Server 5.5.16 Linux (x86_64) (revision id: undefined)
xtrabackup: cd to /tmp/restore
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(59605543)
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
130307 21:04:19 InnoDB: Using Linux native AIO
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by use-memory parameter)
130307 21:04:19 InnoDB: The InnoDB memory heap is disabled
130307 21:04:19 InnoDB: Mutexes and rw_locks use GCC atomic builtins
130307 21:04:19 InnoDB: Compressed tables use zlib 1.2.3
130307 21:04:19 InnoDB: Using Linux native AIO
130307 21:04:19 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead
130307 21:04:19 InnoDB: Initializing buffer pool, size = 100.0M
130307 21:04:19 InnoDB: Completed initialization of buffer pool
130307 21:04:19 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!
130307 21:04:19 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files
InnoDB: Last MySQL binlog file position 0 107, file name /var/log/mysql/mysql-bin.000022
130307 21:04:20 InnoDB: Waiting for the background threads to start
130307 21:04:21 Percona XtraDB (http://www.percona.com) 1.1.8-20.1 started; log sequence number 59605543
[notice (again)]
If you use binary log and don t use any hack of group commit,
the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 107, file name /var/log/mysql/mysql-bin.000022
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
130307 21:04:21 InnoDB: Starting shutdown
130307 21:04:25 InnoDB: Shutdown completed; log sequence number 59606758
130307 21:04:25 innobackupex: Restarting xtrabackup with command: xtrabackup_55 defaults-file= /tmp/restore/backup-my.cnf prepare target-dir=/tmp/restore
for creating ib_logfile*
xtrabackup_55 version 1.6.7 for Percona Server 5.5.16 Linux (x86_64) (revision id: undefined)
xtrabackup: cd to /tmp/restore
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
130307 21:04:25 InnoDB: Using Linux native AIO
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by use-memory parameter)
130307 21:04:25 InnoDB: The InnoDB memory heap is disabled
130307 21:04:25 InnoDB: Mutexes and rw_locks use GCC atomic builtins
130307 21:04:25 InnoDB: Compressed tables use zlib 1.2.3
130307 21:04:25 InnoDB: Using Linux native AIO
130307 21:04:25 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead
130307 21:04:25 InnoDB: Initializing buffer pool, size = 100.0M
130307 21:04:25 InnoDB: Completed initialization of buffer pool
130307 21:04:25 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
130307 21:04:25 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
130307 21:04:25 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!
130307 21:04:25 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files
InnoDB: Last MySQL binlog file position 0 107, file name /var/log/mysql/mysql-bin.000022
130307 21:04:26 InnoDB: Waiting for the background threads to start
130307 21:04:27 Percona XtraDB (http://www.percona.com) 1.1.8-20.1 started; log sequence number 59607052
[notice (again)]
If you use binary log and don t use any hack of group commit,
the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 107, file name /var/log/mysql/mysql-bin.000022
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
130307 21:04:27 InnoDB: Starting shutdown
130307 21:04:31 InnoDB: Shutdown completed; log sequence number 59607052
130307 21:04:31 innobackupex: completed OK!
这里的 apply-log指明是将日志应用到数据文件上，完成之后将备份文件中的数据恢复到数据库中：
然后再查看一下当前目录内容
[email protected]:/tmp/restore# ll
total 79904
drwxr-xr-x 3 root root 4096 Mar 7 21:04 ./
drwxrwxrwt 14 root root 4096 Mar 7 21:04 ../
-rw-r r 1 root root 260 Mar 7 21:01 backup-my.cnf
-rw-rw - 1 mysql mysql 69206016 Mar 7 21:04 ibdata1
-rw-r r 1 root root 5242880 Mar 7 21:04 ib_logfile0
-rw-r r 1 root root 5242880 Mar 7 21:04 ib_logfile1
drwxr-xr-x 2 root root 4096 Mar 7 21:03 test/
-rw-r r 1 root root13 Mar 7 21:02 xtrabackup_binary
-rw-r r 1 root root23 Mar 7 21:02 xtrabackup_binlog_info
-rw-r r 1 root root36 Mar 7 21:04 xtrabackup_binlog_pos_innodb
-rw-r r 1 root root79 Mar 7 21:04 xtrabackup_checkpoints
-rw-r r 1 root root 2097152 Mar 7 21:04 xtrabackup_logfile
然后把test目录复制到/var/lib/mysql目录
[email protected]:/tmp/restore# rsync -avz test ib* /var/lib/mysql/
sending incremental file list
test/
test/db.opt
test/test.frm
sent 381 bytes received 54 bytes 870.00 bytes/sec
total size is 8621 speedup is 19.82
[email protected]:/tmp/restore# cd /var/lib/mysql/
[email protected]:/var/lib/mysql# ll
total 77860
drwx 8 mysql mysql 4096 Mar 7 21:06 ./
drwxr-xr-x 38 root root 4096 Mar 7 19:52 ../
-rw-r r 1 root root 0 Jan 5 14:22 debian-5.5.flag
drwx 2 mysql mysql 4096 Feb 11 17:39 django/
-rw-rw - 1 mysql mysql 69206016 Mar 7 21:02 ibdata1
-rw-rw - 1 mysql mysql 5242880 Mar 7 21:02 ib_logfile0
-rw-rw - 1 mysql mysql 5242880 Mar 7 21:01 ib_logfile1
drwx 2 mysql mysql 4096 Jan 5 22:55 monitor/
drwx 2 mysql root 4096 Jan 5 14:22 mysql/
-rw-rw - 1 root root 6 Jan 5 14:22 mysql_upgrade_info
drwx 2 mysql mysql 4096 Jan 5 14:22 performance_schema/
drwxr-xr-x 2 root root 4096 Mar 7 21:03 test/
drwxr-xr-x 2 mysql mysql 4096 Mar 7 19:58 xtrbackup/
然后再修改test的用户与组为mysql
[email protected]:/var/lib/mysql# chown -R mysql:mysql test
[email protected]:/var/lib/mysql# ll
total 77860
drwx 8 mysql mysql 4096 Mar 7 21:06 ./
drwxr-xr-x 38 root root 4096 Mar 7 19:52 ../
-rw-r r 1 root root 0 Jan 5 14:22 debian-5.5.flag
drwx 2 mysql mysql 4096 Feb 11 17:39 django/
-rw-rw - 1 mysql mysql 69206016 Mar 7 21:02 ibdata1
-rw-rw - 1 mysql mysql 5242880 Mar 7 21:02 ib_logfile0
-rw-rw - 1 mysql mysql 5242880 Mar 7 21:01 ib_logfile1
drwx 2 mysql mysql 4096 Jan 5 22:55 monitor/
drwx 2 mysql root 4096 Jan 5 14:22 mysql/
-rw-rw - 1 root root 6 Jan 5 14:22 mysql_upgrade_info
drwx 2 mysql mysql 4096 Jan 5 14:22 performance_schema/
drwxr-xr-x 2 mysql mysql 4096 Mar 7 21:03 test/
drwxr-xr-x 2 mysql mysql 4096 Mar 7 19:58 xtrbackup/
然后启动mysql，并查看test数据库的表里内容
[email protected]:/var/lib/mysql# service mysql start
mysql start/running, process 12730
[email protected]:/var/lib/mysql# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or /g.
Your MySQL connection id is 36
Server version: 5.5.28-0ubuntu0.12.04.3-log (Ubuntu)
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type help; or /h for help. Type /c to clear the current input statement.
mysql use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql select * from test;
+ +
| id |
+ +
|1 |
|2 |
|3 |
|4 |
|5 |
+ +
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
所以在apply-log后直接复制数据目录到数据库的位置上吧。
三、对数据库的增量备份与恢复
为了进行增量备份，先对数据库添加一些数据
mysql insert into test values(11);
Query OK, 1 row affected (0.10 sec)
mysql insert into test values(12);
Query OK, 1 row affected (0.05 sec)
mysql insert into test values(13);
Query OK, 1 row affected (0.00 sec)
mysql insert into test values(14);
Query OK, 1 row affected (0.00 sec)
mysql insert into test values(15);
Query OK, 1 row affected (0.00 sec)
mysql flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql select * from test;
+ +
| id |
+ +
|1 |
|2 |
|3 |
|4 |
|5 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
+ +
10 rows in set (0.00 sec)
然后进行增量的备份
[email protected]:/var/lib/mysql# innobackupex user=root password=123456 database=test incremental incremental-basedir=/tmp/restore/ /tmp/data
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.
130307 21:13:38 innobackupex: Starting mysql with options: password=xxxxxxxx user= root unbuffered
130307 21:13:38 innobackupex: Connected to database with mysql child process (pid=12864)
130307 21:13:44 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.14 Distrib 5.5.28, for debian-linux-gnu (x86_64) using readline 6.2
innobackupex: Using mysql server version Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
innobackupex: Created backup directory /tmp/data/2013-03-07_21-13-44
130307 21:13:44 innobackupex: Starting mysql with options: password=xxxxxxxx user= root unbuffered
130307 21:13:44 innobackupex: Connected to database with mysql child process (pid=12891)
130307 21:13:46 innobackupex: Connection to database server closed
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/
innobackupex: Waiting for ibbackup (pid=12898) to suspend
innobackupex: Suspend file /tmp/data/2013-03-07_21-13-44/xtrabackup_suspended
xtrabackup_55 version 1.6.7 for Percona Server 5.5.16 Linux (x86_64) (revision id: undefined)
incremental backup from 59605543 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
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
130307 21:13:46 InnoDB: Using Linux native AIO
log scanned up to (59606124)
[01] Copying ./ibdata1
to /tmp/data/2013-03-07_21-13-44/ibdata1.delta
[01] done
130307 21:13:50 innobackupex: Continuing after ibbackup has suspended
130307 21:13:50 innobackupex: Starting mysql with options: password=xxxxxxxx user= root unbuffered
130307 21:13:50 innobackupex: Connected to database with mysql child process (pid=12913)
log scanned up to (59606124)
130307 21:13:52 innobackupex: Starting to lock all tables
log scanned up to (59606124)
log scanned up to (59606124)
130307 21:14:03 innobackupex: All tables locked and flushed to disk
130307 21:14:03 innobackupex: Starting to backup .frm, .MRG, .MYD, .MYI,
innobackupex: .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV and .opt files in
innobackupex: subdirectories of /var/lib/mysql
innobackupex: Backing up file /var/lib/mysql/test/test.frm
innobackupex: Backing up file /var/lib/mysql/test/db.opt
130307 21:14:03 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): 59606124
log scanned up to (59606124)
xtrabackup: Stopping log copying thread.
xtrabackup: Transaction log of lsn (59606124) to (59606124) was copied.
130307 21:14:05 innobackupex: All tables unlocked
130307 21:14:05 innobackupex: Connection to database server closed
innobackupex: Backup created in directory /tmp/data/2013-03-07_21-13-44
innobackupex: MySQL binlog position: filename mysql-bin.000023 , position 107
130307 21:14:05 innobackupex: completed OK!
其中， incremental指明是增量备份， incremental-basedir指定上次完整备份或者增量备份文件的位置。这里的增量备份其实只针对的是InnoDB，对于MyISAM来说，还是完整备份。
在进行增量备份的恢复之前，先关闭数据库，然后删除数据库test
[email protected]:/var/lib/mysql# service mysql stop
mysql stop/waiting
[email protected]:/var/lib/mysql# rm -rf test
[email protected]:/var/lib/mysql# ll
total 77856
drwx 7 mysql mysql 4096 Mar 7 21:17 ./
drwxr-xr-x 38 root root 4096 Mar 7 19:52 ../
-rw-r r 1 root root 0 Jan 5 14:22 debian-5.5.flag
drwx 2 mysql mysql 4096 Feb 11 17:39 django/
-rw-rw - 1 mysql mysql 69206016 Mar 7 21:17 ibdata1
-rw-rw - 1 mysql mysql 5242880 Mar 7 21:17 ib_logfile0
-rw-rw - 1 mysql mysql 5242880 Mar 7 21:11 ib_logfile1
drwx 2 mysql mysql 4096 Jan 5 22:55 monitor/
drwx 2 mysql root 4096 Jan 5 14:22 mysql/
-rw-rw - 1 root root 6 Jan 5 14:22 mysql_upgrade_info
drwx 2 mysql mysql 4096 Jan 5 14:22 performance_schema/
drwxr-xr-x 2 mysql mysql 4096 Mar 7 19:58 xtrbackup/
增量备份的恢复
[email protected]:/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/
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! .
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/
xtrabackup_55 version 1.6.7 for Percona Server 5.5.16 Linux (x86_64) (revision id: undefined)
incremental backup from 59605543 is enabled.
xtrabackup: cd to /tmp/restore
xtrabackup: This target seems to be already prepared.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(59606124)
xtrabackup: page size for /tmp/data/2013-03-07_21-13-44//ibdata1.delta is 16384 bytes
Applying /tmp/data/2013-03-07_21-13-44//ibdata1.delta
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 = /tmp/data/2013-03-07_21-13-44/
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 2097152
130307 21:18:20 InnoDB: Using Linux native AIO
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by use-memory parameter)
130307 21:18:20 InnoDB: The InnoDB memory heap is disabled
130307 21:18:20 InnoDB: Mutexes and rw_locks use GCC atomic builtins
130307 21:18:20 InnoDB: Compressed tables use zlib 1.2.3
130307 21:18:20 InnoDB: Using Linux native AIO
130307 21:18:20 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead
130307 21:18:20 InnoDB: Initializing buffer pool, size = 100.0M
130307 21:18:20 InnoDB: Completed initialization of buffer pool
130307 21:18:20 InnoDB: highest supported file format is Barracuda.
InnoDB: ##########################################################
InnoDB: WARNING!
InnoDB: The log sequence number in ibdata files is higher
InnoDB: than the log sequence number in the ib_logfiles! Are you sure
InnoDB: you are using the right ib_logfiles to start up the database?
InnoDB: Log sequence number in ib_logfiles is 59606124, log
InnoDB: sequence numbers stamped to ibdata file headers are between
InnoDB: 59607052 and 59607052.
InnoDB: ##########################################################
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
130307 21:18:20 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files
InnoDB: Last MySQL binlog file position 0 107, file name /var/log/mysql/mysql-bin.000023
130307 21:18:29 InnoDB: Waiting for the background threads to start
130307 21:18:30 Percona XtraDB (http://www.percona.com) 1.1.8-20.1 started; log sequence number 59606124
[notice (again)]
If you use binary log and don t use any hack of group commit,
the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 107, file name /var/log/mysql/mysql-bin.000023
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
130307 21:18:30 InnoDB: Starting shutdown
130307 21:18:34 InnoDB: Shutdown completed; log sequence number 59607339
innobackupex: Starting to copy non-InnoDB files in /tmp/data/2013-03-07_21-13-44/
innobackupex: to the full backup directory /tmp/restore
innobackupex: Copying /tmp/data/2013-03-07_21-13-44/xtrabackup_binlog_info to /tmp/restore/xtrabackup_binlog_info
innobackupex: Copying /tmp/data/2013-03-07_21-13-44/test/test.frm to /tmp/restore/test/test.frm
innobackupex: Copying /tmp/data/2013-03-07_21-13-44/test/db.opt to /tmp/restore/test/db.opt
130307 21:18:34 innobackupex: completed OK!
然后再进入恢复的目录/tmp/data
[email protected]:/var/lib/mysql# cd /tmp/data
[email protected]:/tmp/data# ll
total 3276
drwxr-xr-x 3 root root4096 Mar 7 21:13 ./
drwxrwxrwt 14 root root4096 Mar 7 21:18 ../
drwxr-xr-x 3 root root4096 Mar 7 21:18 2013-03-07_21-13-44/
-rw-r r 1 root root3780 Mar 7 21:02 test-201303072101.log
-rw-r r 1 root root 3336909 Mar 7 21:02 test-201303072101.tar.gz
[email protected]:/tmp/data# cd 2013-03-07_21-13-44/
[email protected]:/tmp/data/2013-03-07_21-13-44# ll
total 2288
drwxr-xr-x 3 root root4096 Mar 7 21:18 ./
drwxr-xr-x 3 root root4096 Mar 7 21:13 ../
-rw-r r 1 root root 260 Mar 7 21:13 backup-my.cnf
-rw-r r 1 root root 212992 Mar 7 21:13 ibdata1.delta
-rw-r r 1 root root 18 Mar 7 21:13 ibdata1.meta
drwxr-xr-x 2 root root4096 Mar 7 21:14 test/
-rw-r r 1 root root 13 Mar 7 21:14 xtrabackup_binary
-rw-r r 1 root root 23 Mar 7 21:14 xtrabackup_binlog_info
-rw-r r 1 root root 84 Mar 7 21:14 xtrabackup_checkpoints
-rw-r r 1 root root 2097152 Mar 7 21:18 xtrabackup_logfile
跟全部备份一样，把test恢复到/var/lib/mysql里
[email protected]:/tmp/data/2013-03-07_21-13-44# rsync -avz test ib* /var/lib/mysql/
sending incremental file list
test/
test/db.opt
test/test.frm
sent 381 bytes received 54 bytes 870.00 bytes/sec
total size is 8621 speedup is 19.82
[email protected]:/tmp/data/2013-03-07_21-13-44# cd /var/lib/mysql/
[email protected]:/var/lib/mysql# ll
total 77860
drwx 8 mysql mysql 4096 Mar 7 21:19 ./
drwxr-xr-x 38 root root 4096 Mar 7 19:52 ../
-rw-r r 1 root root 0 Jan 5 14:22 debian-5.5.flag
drwx 2 mysql mysql 4096 Feb 11 17:39 django/
-rw-rw - 1 mysql mysql 69206016 Mar 7 21:17 ibdata1
-rw-rw - 1 mysql mysql 5242880 Mar 7 21:17 ib_logfile0
-rw-rw - 1 mysql mysql 5242880 Mar 7 21:11 ib_logfile1
drwx 2 mysql mysql 4096 Jan 5 22:55 monitor/
drwx 2 mysql root 4096 Jan 5 14:22 mysql/
-rw-rw - 1 root root 6 Jan 5 14:22 mysql_upgrade_info
drwx 2 mysql mysql 4096 Jan 5 14:22 performance_schema/
drwxr-xr-x 2 root root 4096 Mar 7 21:14 test/
drwxr-xr-x 2 mysql mysql 4096 Mar 7 19:58 xtrbackup/
然后修改用户与组
[email protected]:/var/lib/mysql# chown -R mysql:mysql test/
[email protected]:/var/lib/mysql# ll
total 77860
drwx 8 mysql mysql 4096 Mar 7 21:19 ./
drwxr-xr-x 38 root root 4096 Mar 7 19:52 ../
-rw-r r 1 root root 0 Jan 5 14:22 debian-5.5.flag
drwx 2 mysql mysql 4096 Feb 11 17:39 django/
-rw-rw - 1 mysql mysql 69206016 Mar 7 21:17 ibdata1
-rw-rw - 1 mysql mysql 5242880 Mar 7 21:17 ib_logfile0
-rw-rw - 1 mysql mysql 5242880 Mar 7 21:11 ib_logfile1
drwx 2 mysql mysql 4096 Jan 5 22:55 monitor/
drwx 2 mysql root 4096 Jan 5 14:22 mysql/
-rw-rw - 1 root root 6 Jan 5 14:22 mysql_upgrade_info
drwx 2 mysql mysql 4096 Jan 5 14:22 performance_schema/
drwxr-xr-x 2 mysql mysql 4096 Mar 7 21:14 test/
drwxr-xr-x 2 mysql mysql 4096 Mar 7 19:58 xtrbackup/
然后启动msyql，并查看test数据库里test表的内容
[email protected]:/var/lib/mysql# service mysql start
mysql start/running, process 13109
[email protected]:/var/lib/mysql# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or /g.
Your MySQL connection id is 36
Server version: 5.5.28-0ubuntu0.12.04.3-log (Ubuntu)
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type help; or /h for help. Type /c to clear the current input statement.
mysql use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql select * from test;
+ +
| id |
+ +
|1 |
|2 |
|3 |
|4 |
|5 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
+ +
10 rows in set (0.00 sec)
可以看到增量备份已经恢复完成。
下面的链接
原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/4745.html
mysqloracle相关文章
- MySQL【第六章】——优化器及执行计划 - Explain
- MySQL Variables have_rtree_keys 数据库 参数变量解释及正确配置使用
- Mysql授予角色权限的实战指南（mysql给权限命令）
- MySQL中的时间数据类型及其应用（mysql时间数据类型）
- 有MySQL注册表：找到它的位置（mysql注册表在哪）
- 字段MySQL数据库中的ENUM字段简介（mysql数据库enum）
- 添加MySQL数据库列的注释（mysql给列加注释）
- 语句使用MySQL中的IN和OR语句（mysql中in和or）
- 数据库 从cmd中删除MySQL数据库.（cmd删除mysql）
- 使用VB操作MySQL：简便高效的数据库管理方式（vb操作mysql）
- MySQL实现用户授权的有效方式（mysql对用户授权）
- MySQL快捷清理：批量删除数据法（mysql批量删除数据）
- 高性能MySQL第3版：挖掘更快的数据库应用潜力（高性能mysql第3版）
- 轻松学习MySQL数据库搭建教程（mysql数据库搭建教程）
- MySQL半同步复制：实现高可用架构（mysql的半同步复制）
- 百度云搭载MySQL优化技巧，提升数据库性能（mysql优化百度云）
- MySQL如何查找重复数据（mysql查找数据重复）
- MySQL如何轻松解决排序问题（mysql排序问题）
- MySQL转换Oracle实现数据库迁移（mysql转换oracle）
- 如何在MySQL中去掉字符串的前后 t（mysql中去掉前后 t）
- 快速解决删除 MySQL 数据库中的表格教程（mysql中删除建表）
- MySQL 数据库三种关系解析（mysql三种关系）
- 了解MySQL三大范式，规范数据库设计（mysql三大范式的内容）
- 掌握MySQL万能密钥，轻松破解数据库限制（mysql万能密钥）
- 教你如何正确地写MySQL数据库的一个字段（mysql一个字段怎么写）
- Mysql数据库管理利器Xmind思维导图教你轻松学习（mysql xmind）
- MySQL数据库禁止同名问题解决方法（mysql不允许同名）
- MySQL通配符下划线的使用技巧（mysql下划线通配符）