[root@centos01 ~]# rpm -Uvh http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm Retrieving http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm Preparing... (########################################### [100%] 1:percona-release (########################################### [100%]
[root@centos01 ~]# rpm -ql percona-release /etc/pki/rpm-gpg /etc/pki/rpm-gpg/RPM-GPG-KEY-percona /etc/yum.repos.d /etc/yum.repos.d/Percona.repo /usr/share/doc/percona-release-0.0 /usr/share/doc/percona-release-0.0/RPM-GPG-KEY-percona You have new mail in /var/spool/mail/root
Xtrabackup是由percona开发的一个开源软件，此软件可以说是innodb热备工具ibbackup的一个开源替代品。这个软件是由2个部 分组成的:xtrabackup和innobackupex。Xtrabackup专门用于innodb引擎和 xtraDB引擎；而innobackupex是专门用于myisam和innodb引擎，及混合使用的引擎。
wget http://www.percona.com/redir/downloads/XtraBackup/XtraBackup-2.0.0/binary/Linux/x86_64/percona-xtrabackup-2.0.0.tar.gz
http://pan.baidu.com/s/1dEOr75R #网盘资源percona-xtrabackup-2.0.0.tar.gz
[root@centos01 ~]# tar -xvf percona-xtrabackup-2.0.0.tar.gz
[root@centos01 ~]# cd percona-xtrabackup-2.0.0 [root@centos01 percona-xtrabackup-2.0.0]# pwd /root/percona-xtrabackup-2.0.0 [root@centos01 percona-xtrabackup-2.0.0]# ls bin share
[root@centos01 percona-xtrabackup-2.0.0]# cp bin/innobackupex /usr/bin/
[root@centos01 percona-xtrabackup-2.0.0]# cp bin/xtrabackup* /usr/bin/
[root@centos01 percona-xtrabackup-2.0.0]# which innobackupex /usr/bin/innobackupex
注意my.cnf中datadir参数，如果没有会报错！ [root@DB~]# grep datadir /etc/my.cnf datadir = /alidata/server/mysql-5.5.40/data/
[root@centos01 dbdata]# innobackupex --user=root --password=123456 --defaults-file=/etc/my.cnf --database=sx --stream=tar /data/dbdata/ 2>/data/dbdata/sx`date +%F`.log | gzip 1>/data/dbdata/sx`date +%F`.tar.gz You have new mail in /var/spool/mail/root [root@centos01 dbdata]# ll -h total 1.9M -rw-rw----. 1 mysql root 361 Apr 30 10:31 centos01.err -rw-r--r--. 1 root root 3.7K Apr 30 11:39 sx2016-04-30.log -rw-r--r--. 1 root root 1.9M Apr 30 11:39 sx2016-04-30.tar.gz -rw-r--r--. 1 root root 1.1K Apr 30 10:29 xtrabk.log [root@centos01 dbdata]# pwd /data/dbdata
说明：
--database=sx 单独对zztx数据库做备份 ，若是不添加此参数那就那就是对全库做备份
2>/data/dbdata/sx.log 输出信息写入日志中
1>/data/dbdata/sx.tar.gz 打包压缩存储到该文件中
脚本：
#!/bin/bash #backup.sh echo "开始备份..."`date` log=sx`date +%y%m%d%H%M`.log str=sx`date +%y%m%d%H%M`.tar.gz innobackupex --user=root --password=123456 --defaults-file=/etc/my.cnf --database=sx --stream=tar /data/dbdata/ 2>/data/dbdata/$log | gzip 1>/data/dbdata/$str echo "备份完毕..."`date`
----------------------------------------------------------
[root@centos01 dbdata]# innobackupex --user=root --password=123456 --database=sx /data/dbdata
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.
160430 12:34:53 innobackupex: Starting mysql with options: --password=xxxxxxxx --user='root' --unbuffered --
160430 12:34:53 innobackupex: Connected to database with mysql child process (pid=26859)
160430 12:34:59 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.20, for Linux (x86_64) 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/dbdata/2016-04-30_12-34-59
160430 12:34:59 innobackupex: Starting mysql with options: --password=xxxxxxxx --user='root' --unbuffered --
160430 12:34:59 innobackupex: Connected to database with mysql child process (pid=26886)
160430 12:35:01 innobackupex: Connection to database server closed
160430 12:35:01 innobackupex: Starting ibbackup with command: xtrabackup_55 --backup --suspend-at-end --target-dir=/data/dbdata/2016-04-30_12-34-59
innobackupex: Waiting for ibbackup (pid=26903) to suspend
innobackupex: Suspend file '/data/dbdata/2016-04-30_12-34-59/xtrabackup_suspended'
xtrabackup_55 version 2.0.0 for Percona Server 5.5.16 Linux (x86_64) (revision id: undefined)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /alidata/server/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
160430 12:35:01 InnoDB: Using Linux native AIO
>> log scanned up to (24098851)
[01] Copying ./ibdata1 to /data/dbdata/2016-04-30_12-34-59/ibdata1
[01] ...done
160430 12:35:05 innobackupex: Continuing after ibbackup has suspended
160430 12:35:05 innobackupex: Starting mysql with options: --password=xxxxxxxx --user='root' --unbuffered --
160430 12:35:05 innobackupex: Connected to database with mysql child process (pid=26919)
>> log scanned up to (24098861)
160430 12:35:07 innobackupex: Starting to lock all tables...
>> log scanned up to (24098861)
>> log scanned up to (24098861)
160430 12:35:17 innobackupex: All tables locked and flushed to disk
160430 12:35:17 innobackupex: Starting to backup .frm, .MRG, .MYD, .MYI,
innobackupex: .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV and .opt files in
innobackupex: subdirectories of '/alidata/server/mysql/data'
innobackupex: Backing up files '/alidata/server/mysql/data/sx/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (109 files)
160430 12:35:18 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): '24098861'
xtrabackup: Stopping log copying thread.
..>> log scanned up to (24098861)
xtrabackup: Transaction log of lsn (24098851) to (24098861) was copied.
160430 12:35:20 innobackupex: All tables unlocked
160430 12:35:20 innobackupex: Connection to database server closed
innobackupex: Backup created in directory '/data/dbdata/2016-04-30_12-34-59'
innobackupex: MySQL binlog position: filename 'mysql-bin.000014', position 203
160430 12:35:20 innobackupex: completed OK!
You have new mail in /var/spool/mail/root
[root@centos01 dbdata]# ls
2016-04-30_12-34-59 centos01.err sx2016-04-30.tar.gz
backup-my.cnf sx2016-04-30.log xtrabk.log
[root@centos01 dbdata]# cd 2016-04-30_12-34-59/
[root@centos01 2016-04-30_12-34-59]# ls
backup-my.cnf sx xtrabackup_binlog_info xtrabackup_logfile
ibdata1 xtrabackup_binary xtrabackup_checkpoints
[root@centos01 2016-04-30_12-34-59]# ll -h
total 27M
-rw-r--r--. 1 root root 310 Apr 30 12:34 backup-my.cnf
-rw-r-----. 1 root root 26M Apr 30 12:35 ibdata1
drwxr-xr-x. 2 root root 4.0K Apr 30 12:35 sx
-rw-r--r--. 1 root root 13 Apr 30 12:35 xtrabackup_binary
-rw-r--r--. 1 root root 23 Apr 30 12:35 xtrabackup_binlog_info
-rw-r-----. 1 root root 79 Apr 30 12:35 xtrabackup_checkpoints
-rw-r-----. 1 root root 2.5K Apr 30 12:35 xtrabackup_logfile
[root@centos01 2016-04-30_12-34-59]# cat xtrabackup_binlog_info mysql-bin.000014 203#mysql服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置
[root@centos01 2016-04-30_12-34-59]# cat xtrabackup_binary xtrabackup_55 ]# 备份中用到的xtrabackup的可执行文件
[root@centos01 2016-04-30_12-34-59]# cat backup-my.cnf #备份命令用到的配置选项信息 # This MySQL options file was generated by innobackupex. # The MySQL server [mysqld] datadir=/data/dbdata/2016-04-30_12-34-59 innodb_data_home_dir=/data/dbdata/2016-04-30_12-34-59 innodb_log_files_in_group=2 innodb_log_file_size=5242880 innodb_fast_checksum=0 innodb_page_size=16384 innodb_log_block_size=512
另外还需注意：备份数据库的用户需要具有相应权限，如果要使用一个最小权限的用户进行备份，则可基于如下命令创建此类用户：
mysql> create user 'bkpuser'@'localhost' identified by"123456"; Query OK, 0 rows affected (0.23 sec)
mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM ’bkpuser’;#如果报错，可以不写
mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO ’bkpuser’@’localhost’;
mysql> flush privileges; Query OK, 0 rows affected (0.06 sec)
接下来做个实验：
mysql> create database ceshi; Query OK, 1 row affected (0.03 sec) mysql> USE ceshi; Database changed mysql> create table users (id int primary key auto_increment,name varchar(20) not null unique,password varchar(100) not null,address varchar(200))engine=MyISAM; Query OK, 0 rows affected (0.12 sec)
mysql> show create table users; +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | users | CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, `password` varchar(100) NOT NULL, `address` varchar(200) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql>
mysql> insert into users(id,name,password,address) values(1,
-> 'zhang','1234',null),(2,'wang','4321','湖北武汉'),(3,'li','5678','北京海 淀');
Query OK, 3 rows affected (0.07 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> create table articles (id int primary key auto_increment,content longtext not null)ENGINE=InnoDB; Query OK, 0 rows affected (0.14 sec)
mysql> insert into articles(id,content) values(11,'hahahaha'),(12,'xixixi'),(13,'aiaiaiaiaiai'),(14,'hohohoho'); Query OK, 4 rows affected (0.17 sec) Records: 4 Duplicates: 0 Warnings: 0
#++++++++++++++++++++++全库备份于恢复过程=============================
[root@centos01 dbdata]# mkdir -p /backup/{full_data,dk_data,zl_data}
[root@centos01 dbdata]# tree /backup/ /backup/ ├── dk_data #单库备份目录 ├── full_data #全库备份目录 └── zl_data #增量备份目录 3 directories, 0 files
[root@centos01 /]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 /backup/full_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.
160430 14:21:10 innobackupex: Starting mysql with options: --defaults-file='/etc/my.cnf' --password=xxxxxxxx --user='root' --unbuffered --
160430 14:21:10 innobackupex: Connected to database with mysql child process (pid=28554)
160430 14:21:16 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.20, for Linux (x86_64) 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 /backup/full_data/2016-04-30_14-21-16
160430 14:21:16 innobackupex: Starting mysql with options: --defaults-file='/etc/my.cnf' --password=xxxxxxxx --user='root' --unbuffered --
160430 14:21:16 innobackupex: Connected to database with mysql child process (pid=28581)
160430 14:21:18 innobackupex: Connection to database server closed
160430 14:21:18 innobackupex: Starting ibbackup with command: xtrabackup_55 --defaults-file="/etc/my.cnf" --backup --suspend-at-end --target-dir=/backup/full_data/2016-04-30_14-21-16
innobackupex: Waiting for ibbackup (pid=28589) to suspend
innobackupex: Suspend file '/backup/full_data/2016-04-30_14-21-16/xtrabackup_suspended'
xtrabackup_55 version 2.0.0 for Percona Server 5.5.16 Linux (x86_64) (revision id: undefined)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /alidata/server/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
160430 14:21:18 InnoDB: Using Linux native AIO
>> log scanned up to (24102641)
[01] Copying ./ibdata1 to /backup/full_data/2016-04-30_14-21-16/ibdata1
[01] ...done
160430 14:21:20 innobackupex: Continuing after ibbackup has suspended
160430 14:21:20 innobackupex: Starting mysql with options: --defaults-file='/etc/my.cnf' --password=xxxxxxxx --user='root' --unbuffered --
160430 14:21:20 innobackupex: Connected to database with mysql child process (pid=28603)
160430 14:21:22 innobackupex: Starting to lock all tables...
>> log scanned up to (24102641)
>> log scanned up to (24102641)
160430 14:21:32 innobackupex: All tables locked and flushed to disk
160430 14:21:32 innobackupex: Starting to backup .frm, .MRG, .MYD, .MYI,
innobackupex: .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV and .opt files in
innobackupex: subdirectories of '/alidata/server/mysql/data'
innobackupex: Backing up file '/alidata/server/mysql/data/ceshi/users.frm'
innobackupex: Backing up file '/alidata/server/mysql/data/ceshi/users.MYI'
innobackupex: Backing up file '/alidata/server/mysql/data/ceshi/db.opt'
innobackupex: Backing up file '/alidata/server/mysql/data/ceshi/users.MYD'
innobackupex: Backing up file '/alidata/server/mysql/data/image/t1.frm'
innobackupex: Backing up file '/alidata/server/mysql/data/image/db.opt'
innobackupex: Backing up file '/alidata/server/mysql/data/test2/articles.frm'
innobackupex: Backing up file '/alidata/server/mysql/data/test2/db.opt'
innobackupex: Backing up file '/alidata/server/mysql/data/test/t1.frm'
innobackupex: Backing up files '/alidata/server/mysql/data/sx/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (109 files)
innobackupex: Backing up files '/alidata/server/mysql/data/performance_schema/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (18 files)
innobackupex: Backing up files '/alidata/server/mysql/data/mysql/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (72 files)
160430 14:21:33 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): '24102641'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (24102641)
xtrabackup: Transaction log of lsn (24102641) to (24102641) was copied.
160430 14:21:35 innobackupex: All tables unlocked
160430 14:21:35 innobackupex: Connection to database server closed
innobackupex: Backup created in directory '/backup/full_data/2016-04-30_14-21-16'
innobackupex: MySQL binlog position: filename 'mysql-bin.000014', position 2601
160430 14:21:35 innobackupex: completed OK!
You have new mail in /var/spool/mail/root
[root@centos01 2016-04-30_14-24-51]# ls backup-my.cnf ceshi ibdata1 image mysql performance_schema sx test test2 xtrabackup_binary xtrabackup_binlog_info xtrabackup_checkpoints xtrabackup_logfile [root@centos01 2016-04-30_14-24-51]# pwd /backup/full_data/2016-04-30_14-24-51
[root@centos01 data]# rm -rf * [root@centos01 data]# pwd /alidata/server/mysql/data mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.00 sec)
[root@centos01 data]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --apply-log /backup/full_data/2016-04-30_14-24-51
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!".
160430 14:31:14 innobackupex: Starting ibbackup with command: xtrabackup_55 --defaults-file="/etc/my.cnf" --prepare --target-dir=/backup/full_data/2016-04-30_14-24-51
xtrabackup_55 version 2.0.0 for Percona Server 5.5.16 Linux (x86_64) (revision id: undefined)
xtrabackup: cd to /backup/full_data/2016-04-30_14-24-51
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(24102651)
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
160430 14:31:14 InnoDB: Using Linux native AIO
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
160430 14:31:14 InnoDB: The InnoDB memory heap is disabled
160430 14:31:14 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160430 14:31:14 InnoDB: Compressed tables use zlib 1.2.3
160430 14:31:14 InnoDB: Using Linux native AIO
160430 14:31:14 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead
160430 14:31:14 InnoDB: Initializing buffer pool, size = 100.0M
160430 14:31:14 InnoDB: Completed initialization of buffer pool
160430 14:31:14 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 24102651
160430 14:31:14 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Doing recovery: scanned up to log sequence number 24102661 (0 %)
InnoDB: Last MySQL binlog file position 0 2601, file name ./mysql-bin.000014
160430 14:31:15 InnoDB: Waiting for the background threads to start
160430 14:31:16 Percona XtraDB (http://www.percona.com) 1.1.8-20.1 started; log sequence number 24102661
[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 2601, file name ./mysql-bin.000014
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
160430 14:31:16 InnoDB: Starting shutdown...
160430 14:31:20 InnoDB: Shutdown completed; log sequence number 24103876
160430 14:31:20 innobackupex: Restarting xtrabackup with command: xtrabackup_55 --defaults-file="/etc/my.cnf" --prepare --target-dir=/backup/full_data/2016-04-30_14-24-51
for creating ib_logfile*
xtrabackup_55 version 2.0.0 for Percona Server 5.5.16 Linux (x86_64) (revision id: undefined)
xtrabackup: cd to /backup/full_data/2016-04-30_14-24-51
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
160430 14:31:20 InnoDB: Using Linux native AIO
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
160430 14:31:20 InnoDB: The InnoDB memory heap is disabled
160430 14:31:20 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160430 14:31:20 InnoDB: Compressed tables use zlib 1.2.3
160430 14:31:20 InnoDB: Using Linux native AIO
160430 14:31:20 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead
160430 14:31:20 InnoDB: Initializing buffer pool, size = 100.0M
160430 14:31:20 InnoDB: Completed initialization of buffer pool
160430 14:31:20 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...
160430 14:31:20 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...
160430 14:31:20 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!
160430 14:31: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 2601, file name ./mysql-bin.000014
160430 14:31:21 InnoDB: Waiting for the background threads to start
160430 14:31:22 Percona XtraDB (http://www.percona.com) 1.1.8-20.1 started; log sequence number 24103948
[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 2601, file name ./mysql-bin.000014
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
160430 14:31:22 InnoDB: Starting shutdown...
160430 14:31:26 InnoDB: Shutdown completed; log sequence number 24103948
160430 14:31:26 innobackupex: completed OK!
[root@centos01 data]# ls #此时还是空的
[root@centos01 data]# ps -ef |grep mysqld mysql 21570 1 0 10:32 ? 00:00:41 /alidata/server/mysql/bin/mysqld --basedir=/alidata/server/mysql --datadir=/alidata/server/mysql/data --plugin-dir=/alidata/server/mysql/lib/plugin --user=mysql --log-error=/alidata/server/mysql/data/centos01.err --pid-file=/alidata/server/mysql/data/centos01.pid --socket=/tmp/mysql.sock --port=3306 root 29433 26814 0 14:34 pts/0 00:00:00 grep mysqld [root@centos01 data]# kill -9 21570 [root@centos01 data]# ps -ef |grep mysqld root 29435 26814 0 14:34 pts/0 00:00:00 grep mysqld
[root@centos01 data]# cp -ap /backup/full_data/2016-04-30_14-24-51/* /alidata/server/mysql/data/
[root@centos01 data]# chown -R mysql.mysql /alidata/server/mysql/data/
[root@centos01 data]# service mysqld start Starting MySQL.. SUCCESS!
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | ceshi | | image | | mysql | | performance_schema | | sx | | test | | test2 | +--------------------+ 8 rows in set (0.03 sec)
mysql> use ceshi
Database changed
mysql> show tables;
+-----------------+
| Tables_in_ceshi |
+-----------------+
| users |
+-----------------+
1 row in set (0.06 sec)
mysql> select * from users;
+----+-------+----------+--------------+
| id | name | password | address |
+----+-------+----------+--------------+
| 1 | zhang | 1234 | NULL |
| 2 | wang | 4321 | 湖北武汉 |
| 3 | li | 5678 | 北京海淀 |
+----+-------+----------+--------------+
3 rows in set (0.03 sec)
mysql> use test2
Database changed
mysql> show tables;
+-----------------+
| Tables_in_test2 |
+-----------------+
| articles |
+-----------------+
1 row in set (0.00 sec)
mysql> select * from articles;
+----+--------------+
| id | content |
+----+--------------+
| 11 | hahahaha |
| 12 | xixixi |
| 13 | aiaiaiaiaiai |
| 14 | hohohoho |
+----+--------------+
4 rows in set (0.00 sec)
##==========================单库增量备份与恢复操作过程===========================##
单库备份跟全库用法是一样的,只不过单库在备份里,要指定要备份的数据库名,即:
--databases=LIST
注:如果是备份从库的话,需要添加参数:--slave-info,即:
[root@centos01 ~]# innobackupex --defaults-file=/etc/my.cnf --user=bkpuser --password=123456 --slave-info --databases=test2 /backup/dk_data
[root@centos01 ~]# innobackupex --defaults-file=/etc/my.cnf --user=bkpuser --password=123456 --databases=test2 /backup/dk_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.
160430 14:59:28 innobackupex: Starting mysql with options: --defaults-file='/etc/my.cnf' --password=xxxxxxxx --user='bkpuser' --unbuffered --
160430 14:59:28 innobackupex: Connected to database with mysql child process (pid=30036)
160430 14:59:34 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.20, for Linux (x86_64) 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 /backup/dk_data/2016-04-30_14-59-34
160430 14:59:34 innobackupex: Starting mysql with options: --defaults-file='/etc/my.cnf' --password=xxxxxxxx --user='bkpuser' --unbuffered --
160430 14:59:34 innobackupex: Connected to database with mysql child process (pid=30063)
160430 14:59:36 innobackupex: Connection to database server closed
160430 14:59:36 innobackupex: Starting ibbackup with command: xtrabackup_55 --defaults-file="/etc/my.cnf" --backup --suspend-at-end --target-dir=/backup/dk_data/2016-04-30_14-59-34
innobackupex: Waiting for ibbackup (pid=30071) to suspend
innobackupex: Suspend file '/backup/dk_data/2016-04-30_14-59-34/xtrabackup_suspended'
xtrabackup_55 version 2.0.0 for Percona Server 5.5.16 Linux (x86_64) (revision id: undefined)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /alidata/server/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
160430 14:59:36 InnoDB: Using Linux native AIO
>> log scanned up to (24104048)
[01] Copying ./ibdata1 to /backup/dk_data/2016-04-30_14-59-34/ibdata1
[01] ...done
160430 14:59:38 innobackupex: Continuing after ibbackup has suspended
160430 14:59:38 innobackupex: Starting mysql with options: --defaults-file='/etc/my.cnf' --password=xxxxxxxx --user='bkpuser' --unbuffered --
160430 14:59:38 innobackupex: Connected to database with mysql child process (pid=30085)
160430 14:59:40 innobackupex: Starting to lock all tables...
>> log scanned up to (24104048)
innobackupex: Error: mysql child process has died: ERROR 1044 (42000) at line 3: Access denied for user 'bkpuser'@'localhost' to database 'mysql'
while waiting for reply to MySQL request: 'USE mysql;' at /usr/bin/innobackupex line 371.
[root@centos01 ~]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --databases=test2 /backup/dk_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.
160430 15:00:16 innobackupex: Starting mysql with options: --defaults-file='/etc/my.cnf' --password=xxxxxxxx --user='root' --unbuffered --
160430 15:00:16 innobackupex: Connected to database with mysql child process (pid=30107)
160430 15:00:22 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.20, for Linux (x86_64) 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 /backup/dk_data/2016-04-30_15-00-22
160430 15:00:22 innobackupex: Starting mysql with options: --defaults-file='/etc/my.cnf' --password=xxxxxxxx --user='root' --unbuffered --
160430 15:00:22 innobackupex: Connected to database with mysql child process (pid=30134)
160430 15:00:24 innobackupex: Connection to database server closed
160430 15:00:24 innobackupex: Starting ibbackup with command: xtrabackup_55 --defaults-file="/etc/my.cnf" --backup --suspend-at-end --target-dir=/backup/dk_data/2016-04-30_15-00-22
innobackupex: Waiting for ibbackup (pid=30142) to suspend
innobackupex: Suspend file '/backup/dk_data/2016-04-30_15-00-22/xtrabackup_suspended'
xtrabackup_55 version 2.0.0 for Percona Server 5.5.16 Linux (x86_64) (revision id: undefined)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /alidata/server/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
160430 15:00:24 InnoDB: Using Linux native AIO
>> log scanned up to (24104048)
[01] Copying ./ibdata1 to /backup/dk_data/2016-04-30_15-00-22/ibdata1
[01] ...done
160430 15:00:26 innobackupex: Continuing after ibbackup has suspended
160430 15:00:26 innobackupex: Starting mysql with options: --defaults-file='/etc/my.cnf' --password=xxxxxxxx --user='root' --unbuffered --
160430 15:00:26 innobackupex: Connected to database with mysql child process (pid=30156)
160430 15:00:28 innobackupex: Starting to lock all tables...
>> log scanned up to (24104048)
>> log scanned up to (24104048)
160430 15:00:38 innobackupex: All tables locked and flushed to disk
160430 15:00:38 innobackupex: Starting to backup .frm, .MRG, .MYD, .MYI,
innobackupex: .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV and .opt files in
innobackupex: subdirectories of '/alidata/server/mysql/data'
innobackupex: Backing up file '/alidata/server/mysql/data/test2/articles.frm'
innobackupex: Backing up file '/alidata/server/mysql/data/test2/db.opt'
160430 15:00:38 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): '24104048'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (24104048)
xtrabackup: Transaction log of lsn (24104048) to (24104048) was copied.
160430 15:00:41 innobackupex: All tables unlocked
160430 15:00:41 innobackupex: Connection to database server closed
innobackupex: Backup created in directory '/backup/dk_data/2016-04-30_15-00-22'
innobackupex: MySQL binlog position: filename 'mysql-bin.000001', position 203
160430 15:00:41 innobackupex: completed OK
[root@centos01 dk_data]# tree 2016-04-30_15-00-22/ 2016-04-30_15-00-22/ ├── backup-my.cnf ├── ibdata1 ├── test2 │ ├── articles.frm │ └── db.opt ├── xtrabackup_binary ├── xtrabackup_binlog_info ├── xtrabackup_checkpoints └── xtrabackup_logfile 1 directory, 8 files [root@centos01 dk_data]# pwd /backup/dk_data
mysql> drop database test2; Query OK, 1 row affected (0.16 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | ceshi | | image | | mysql | | performance_schema | | sx | | test | +--------------------+ 7 rows in set (0.00 sec)
[root@centos01 data]# service mysqld stop Shutting down MySQL.. SUCCESS!
2)恢复日志文件apply-log
[root@centos01 server]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --apply-log /backup/dk_data/2016-04-30_15-47-05
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!".
160430 18:35:31 innobackupex: Starting ibbackup with command: xtrabackup_55 --defaults-file="/etc/my.cnf" --prepare --target-dir=/backup/dk_data/2016-04-30_15-47-05
xtrabackup_55 version 2.0.0 for Percona Server 5.5.16 Linux (x86_64) (revision id: undefined)
xtrabackup: cd to /backup/dk_data/2016-04-30_15-47-05
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(24103978)
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
160430 18:35:31 InnoDB: Using Linux native AIO
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
160430 18:35:31 InnoDB: The InnoDB memory heap is disabled
160430 18:35:31 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160430 18:35:31 InnoDB: Compressed tables use zlib 1.2.3
160430 18:35:31 InnoDB: Using Linux native AIO
160430 18:35:31 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead
160430 18:35:31 InnoDB: Initializing buffer pool, size = 100.0M
160430 18:35:31 InnoDB: Completed initialization of buffer pool
160430 18:35:31 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 24103978
160430 18:35:31 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Doing recovery: scanned up to log sequence number 24103988 (0 %)
InnoDB: Last MySQL binlog file position 0 2601, file name ./mysql-bin.000014
160430 18:35:31 InnoDB: Waiting for the background threads to start
160430 18:35:32 Percona XtraDB (http://www.percona.com) 1.1.8-20.1 started; log sequence number 24103988
[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 2601, file name ./mysql-bin.000014
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
160430 18:35:32 InnoDB: Starting shutdown...
160430 18:35:36 InnoDB: Shutdown completed; log sequence number 24103988
160430 18:35:36 innobackupex: Restarting xtrabackup with command: xtrabackup_55 --defaults-file="/etc/my.cnf" --prepare --target-dir=/backup/dk_data/2016-04-30_15-47-05
for creating ib_logfile*
xtrabackup_55 version 2.0.0 for Percona Server 5.5.16 Linux (x86_64) (revision id: undefined)
xtrabackup: cd to /backup/dk_data/2016-04-30_15-47-05
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
160430 18:35:36 InnoDB: Using Linux native AIO
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
160430 18:35:36 InnoDB: The InnoDB memory heap is disabled
160430 18:35:36 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160430 18:35:36 InnoDB: Compressed tables use zlib 1.2.3
160430 18:35:36 InnoDB: Using Linux native AIO
160430 18:35:36 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead
160430 18:35:36 InnoDB: Initializing buffer pool, size = 100.0M
160430 18:35:36 InnoDB: Completed initialization of buffer pool
160430 18:35:36 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...
160430 18:35:37 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...
160430 18:35:37 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!
160430 18:35:37 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 2601, file name ./mysql-bin.000014
160430 18:35:37 InnoDB: Waiting for the background threads to start
160430 18:35:38 Percona XtraDB (http://www.percona.com) 1.1.8-20.1 started; log sequence number 24104460
[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 2601, file name ./mysql-bin.000014
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
160430 18:35:38 InnoDB: Starting shutdown...
160430 18:35:42 InnoDB: Shutdown completed; log sequence number 24104460
160430 18:35:42 innobackupex: completed OK!
3)把备份文件拷贝至原数据目录下
[root@centos01 data]# cp -ap /backup/dk_data/2016-04-30_15-47-05/test2/ /alidata/server/mysql/data/
[root@centos01 data]# chown -R mysql.mysql /alidata/server/mysql/data/test2
[root@centos01 data]# service mysqld restart ERROR! MySQL server PID file could not be found! Starting MySQL... SUCCESS!
mysql> show databases like '%test2'; +-------------------+ | Database (%test2) | +-------------------+ | test2 | +-------------------+ 1 row in set (0.01 sec)
直接从备份中拷贝被drop表的相关文件来恢复是不行的，即使文件存在，也会报表不存在；
mysql> select count(*) from articles; ERROR 1146 (42S02): Table 'test2.articles' doesn't exist [root@centos01 data]# ls /alidata/server/mysql/data/test2/ articles.frm db.opt