电玩城打鱼捕鱼-专业24小时上下分

备份工具的使用,19实战案例

六月 7th, 2019  |  电玩城捕鱼系统简介

环境:CentOS 6.7  +
MysSQL 5.7.19 + Xtraback 2.4.8

MySQL利用xtrabackup进行增量备份详细过程汇总

实用的innodb备份工具,大家可以试试看。
近来好多人都在使用此备份工具。

innobackupex常用参数:
–user=USER                    指定备份用户,不指定的话为当前系统用户
–password=PASSWD              指定备份用户密码
–port=PORT                    指定数据库端口
–defaults-group=GROUP-NAME    在多实例的时候使用
–host=HOST                    指定备份的主机,可以为远程数据库服务器
–apply-log                    回滚日志
–database                     
指定需要备份的数据库,多个数据库之间以空格分开
–defaults-file                指定mysql的配置文件
–copy-back                    将备份数据复制回原始位置
–incremental                  增量备份,后面跟要增量备份的路径
–incremental-basedir=DIRECTORY
增量备份时使用指向上一次的增量备份所在的目录
–incremental-dir=DIRECTORY   
增量备份还原的时候用来合并增量备份到全量,用来指定全备路径
–redo-only                    对增量备份进行合并
–rsync                       
加快本地文件传输,适用于non-InnoDB数据库引擎。不与–stream共用
–no-timestamp                  生成的备份文件不以时间戳为目录.

1,创建mysql备份用户

mysql -uroot –password=”” -e”CREATE USER ‘backup’@’192.168.%’
IDENTIFIED BY ‘123456’”;

mysql -uroot –password=”” -e”GRANT RELOAD, LOCK TABLES, REPLICATION
CLIENT, CREATE TABLESPACE, SUPERON *.* TO ‘backup’@’192.168.%'”;

 

1,下载
http://www.percona.com/downloads/XtraBackup/LATEST/RPM/rhel6/x86\_64/
可以根据具体系统版本来选择

示例组1:全备还原

2,xtrabackup进行全量备份

备份命令:

xtrabackup –defaults-file=/etc/my.cnf–user=backup –password=”123456″
–port=3306 –backup–target-dir=/data/backups/mysql/full_incre_$(date
+%Y%m%d)

PS: 2>/tmp/fullbackup.log 将备份信息放入文件中。

 

备份过程信息如下:

 

[[email protected] ~]# xtrabackup--defaults-file=/etc/my.cnf --user=backup --password="123456"--port=3306 --backup --target-dir=/data/backups/mysql/full_incre_$(date+%Y%m%d)
xtrabackup version 2.2.9 based on MySQLserver 5.6.22 Linux (x86_64) (revision id: )
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /home/data/mysql/data
xtrabackup: open files limit requested 0,set to 1024
xtrabackup: using the following InnoDBconfiguration:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 3
xtrabackup:   innodb_log_file_size = 67108864
>> log scanned up to (423920168)
xtrabackup: Generating a list of tablespaces
[01] Copying ./IBdata1 to/data/backups/mysql/full_incre_20150319/IBdata1
>> log scanned up to (423920168)
>> log scanned up to (423920168)
[01]       ...done
[01] Copying ./IBdata2 to/data/backups/mysql/full_incre_20150319/IBdata2
>> log scanned up to (423920168)
>> log scanned up to (423920168)
[01]       ...done
[01] Copying ./mysql/slave_master_info.ibdto /data/backups/mysql/full_incre_20150319/mysql/slave_master_info.ibd
[01]       ...done
[01] Copying ./mysql/slave_relay_log_info.ibdto /data/backups/mysql/full_incre_20150319/mysql/slave_relay_log_info.ibd
[01]       ...done
[01] Copying ./mysql/innodb_index_stats.ibdto /data/backups/mysql/full_incre_20150319/mysql/innodb_index_stats.ibd
[01]       ...done
[01] Copying ./mysql/slave_worker_info.ibdto /data/backups/mysql/full_incre_20150319/mysql/slave_worker_info.ibd
[01]       ...done
[01] Copying ./mysql/innodb_table_stats.ibdto /data/backups/mysql/full_incre_20150319/mysql/innodb_table_stats.ibd
[01]       ...done
[01] Copying ./sbtest/sbtest1.ibd to/data/backups/mysql/full_incre_20150319/sbtest/sbtest1.ibd
[01]       ...done
[01] Copying ./sbtest/sbtest5.ibd to/data/backups/mysql/full_incre_20150319/sbtest/sbtest5.ibd
[01]       ...done
[01] Copying ./sbtest/sbtest8.ibd to/data/backups/mysql/full_incre_20150319/sbtest/sbtest8.ibd
[01]       ...done
[01] Copying ./sbtest/sbtest4.ibd to/data/backups/mysql/full_incre_20150319/sbtest/sbtest4.ibd
[01]       ...done
[01] Copying ./sbtest/sbtest2.ibd to/data/backups/mysql/full_incre_20150319/sbtest/sbtest2.ibd
[01]       ...done
[01] Copying ./sbtest/sbtest6.ibd to/data/backups/mysql/full_incre_20150319/sbtest/sbtest6.ibd
[01]       ...done
[01] Copying ./sbtest/sbtest7.ibd to /data/backups/mysql/full_incre_20150319/sbtest/sbtest7.ibd
[01]       ...done
[01] Copying ./sbtest/sbtest3.ibd to/data/backups/mysql/full_incre_20150319/sbtest/sbtest3.ibd
[01]       ...done
[01] Copying ./test/t1.ibd to/data/backups/mysql/full_incre_20150319/test/t1.ibd
[01]       ...done
[01] Copying ./test/t3.ibd to/data/backups/mysql/full_incre_20150319/test/t3.ibd
[01]       ...done
[01] Copying ./test/t.ibd to/data/backups/mysql/full_incre_20150319/test/t.ibd
[01]       ...done
[01] Copying ./user_db/t1.ibd to/data/backups/mysql/full_incre_20150319/user_db/t1.ibd
[01]       ...done
[01] Copying ./user_db/test.ibd to/data/backups/mysql/full_incre_20150319/user_db/test.ibd
[01]       ...done
[01] Copying ./user_db/test2.ibd to/data/backups/mysql/full_incre_20150319/user_db/test2.ibd
[01]       ...done
[01] Copying ./user_db/t3.ibd to/data/backups/mysql/full_incre_20150319/user_db/t3.ibd
[01]       ...done
[01] Copying ./user_db/UC_USER.ibd to/data/backups/mysql/full_incre_20150319/user_db/UC_USER.ibd
>> log scanned up to (423920168)
[01]       ...done
[01] Copying ./user_db/t.ibd to/data/backups/mysql/full_incre_20150319/user_db/t.ibd
[01]       ...done
xtrabackup: The latest check point (forincremental): '423920168'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (423920168)

xtrabackup: Transaction log of lsn(423920168) to (423920168) was copied.
[[email protected] ~]#

 

 

2,User’s Manual
http://www.percona.com/doc/percona-xtrabackup/2.1/manual.html

#数据目录:/data/mysql
$ mkdir -pv /backup/3306/20171107/full
#本地 单实例 全量备份
$ innobackupex –defaults-file=/etc/my.cnf  –user=root
–password=123456 –socket=/tmp/mysql.sock –no-timestamp  –parallel=4 
/backup/3306/20171107/full
#还原
$ innobackupex –apply-log  /backup/3306/20171107/full
$ service mysqld stop
$ mv /data/mysql  /data/mysql_bak_201711071    ##备份原数据目录
$ mkdir -pv /data/mysql
$ innobackupex –copy-back  /backup/3306/20171107/full
$ chown -R mysql:mysql /data/mysql
$ service mysqld start

3,xtrabackup进行增量备份

3,yum安装各种包
yum install libaio libaio-devel perl-Time-HiRes curl curl-devel
zlib-devel openssl-devel perl cpio expat-devel gettext-devel
perl-ExtUtils-MakeMaker perl-DBD-MySQL.* package bzr bison
ncurses-devel zlib-devel gcc gcc-c++ -y

示例组2:增量还原

3.1先录入一张表,实现第一次增量数据:

 

mysql> create table t4 select 'aaa' asa, 1 as b;
Query OK, 1 row affected (0.36 sec)
Records: 1 Duplicates: 0  Warnings: 0

mysql>
mysql> insert into t4 select 'bbb',2;
Query OK, 1 row affected (0.05 sec)
Records: 1 Duplicates: 0  Warnings: 0

mysql>

 

yum install perl-DB* perl-Time*

#数据目录:/data/mysql
$ mkdir -pv /backup/3306/20171107/{full,inc1,inc2,inc3}
#本地 单实例 全量备份
$ innobackupex –defaults-file=/etc/my.cnf  –user=root
–password=123456 –socket=/tmp/mysql.sock –no-timestamp  –parallel=4 
/backup/3306/20171107/full
#本地 单实例 增量备份1
$ mysql  -S /tmp/mysql.sock  -uroot -p123456 -e “create table
tpccdb.tb2(col1 varchar(16));”
$ mysql  -S /tmp/mysql.sock  -uroot -p123456 -e “insert into  tpccdb.tb2
values(‘1’),(‘2’),(‘3’),(‘第一次插入结束’);”
$ innobackupex –defaults-file=/etc/my.cnf  –user=root 
–password=123456  –socket=/tmp/mysql.sock –no-timestamp 
–parallel=4  –incremental-basedir=/backup/3306/20171107/full 
–incremental /backup/3306/20171107/inc1
#本地 单实例 增量备份2
$ mysql  -S /tmp/mysql.sock  -uroot -p123456 -e “insert into  tpccdb.tb2
values(‘4’),(‘5’),(‘6’),(‘第二次插入结束’);”
$ innobackupex –defaults-file=/etc/my.cnf  –user=root 
–password=123456  –socket=/tmp/mysql.sock –no-timestamp 
–parallel=4  –incremental-basedir=/backup/3306/20171107/inc1 
–incremental /backup/3306/20171107/inc2
#本地 单实例 增量备份3
$ mysql  -S /tmp/mysql.sock  -uroot -p123456 -e “insert into  tpccdb.tb2
values(‘7’),(‘8’),(‘9’),(‘第三次插入结束’);”
$ innobackupex –defaults-file=/etc/my.cnf  –user=root 
–password=123456  –socket=/tmp/mysql.sock –no-timestamp 
–parallel=4  –incremental-basedir=/backup/3306/20171107/inc2 
–incremental /backup/3306/20171107/inc3
#还原
$ innobackupex –apply-log  –redo-only /backup/3306/20171107/full
$ innobackupex –apply-log  –redo-only /backup/3306/20171107/full
–incremental-dir=/backup/3306/20171107/inc1
$ innobackupex –apply-log  –redo-only /backup/3306/20171107/full
–incremental-dir=/backup/3306/20171107/inc2
$ innobackupex –apply-log              /backup/3306/20171107/full
–incremental-dir=/backup/3306/20171107/inc3
$ innobackupex –apply-log  /backup/3306/20171107/full
$ service mysqld stop
$ mv /data/mysql  /data/mysql_bak_201711072    ##备份原数据目录
$ mkdir -pv /data/mysql
$ innobackupex –copy-back  /backup/3306/20171107/full
$ chown -R mysql:mysql /data/mysql
$ service mysqld start
$ mysql  -S /tmp/mysql.sock  -uroot -p123456 -e “select * from
tpccdb.tb2;”

3.2 再进行增量备份1

xtrabackup –defaults-file=/etc/my.cnf–backup –user=backup
–password=”123456″ –port=3306
–target-dir=/data/backups/mysql/incre_20150320/–incremental-basedir=/data/backups/mysql/full_incre_201503192>/tmp/incre_backup_1.log

 

查看备份日志信息:

 

[[email protected] ~]# more/tmp/incre_backup_1.log
xtrabackup version 2.2.9 based on MySQLserver 5.6.22 Linux (x86_64) (revision id: )
incremental backup from 423920168 isenabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /home/data/mysql/data
xtrabackup: open files limit requested 0,set to 1024
xtrabackup: using the following InnoDBconfiguration:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 3
xtrabackup:   innodb_log_file_size = 67108864
>> log scanned up to (423925813)
xtrabackup: Generating a list oftablespaces
xtrabackup: using the full scan forincremental backup
[01] Copying ./IBdata1 to/data/backups/mysql/incre_20150320/IBdata1.delta
>> log scanned up to (423925813)
[01]       ...done
[01] Copying ./IBdata2 to/data/backups/mysql/incre_20150320/IBdata2.delta
>> log scanned up to (423925813)
[01]       ...done
>> log scanned up to (423925813)
[01] Copying ./mysql/slave_master_info.ibdto /data/backups/mysql/incre_20150320/mysql/slave_master_info.ibd.delta
[01]       ...done
[01] Copying./mysql/slave_relay_log_info.ibd to/data/backups/mysql/incre_20150320/mysql/slave_relay_log_info.ibd.delta
[01]       ...done
[01] Copying ./mysql/innodb_index_stats.ibdto /data/backups/mysql/incre_20150320/mysql/innodb_index_stats.ibd.delta
[01]       ...done
[01] Copying ./mysql/slave_worker_info.ibdto /data/backups/mysql/incre_20150320/mysql/slave_worker_info.ibd.delta
[01]       ...done
[01] Copying ./mysql/innodb_table_stats.ibdto /data/backups/mysql/incre_20150320/mysql/innodb_table_stats.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest1.ibd to/data/backups/mysql/incre_20150320/sbtest/sbtest1.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest5.ibd to/data/backups/mysql/incre_20150320/sbtest/sbtest5.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest8.ibd to/data/backups/mysql/incre_20150320/sbtest/sbtest8.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest4.ibd to/data/backups/mysql/incre_20150320/sbtest/sbtest4.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest2.ibd to/data/backups/mysql/incre_20150320/sbtest/sbtest2.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest6.ibd to/data/backups/mysql/incre_20150320/sbtest/sbtest6.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest7.ibd to/data/backups/mysql/incre_20150320/sbtest/sbtest7.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest3.ibd to/data/backups/mysql/incre_20150320/sbtest/sbtest3.ibd.delta
[01]       ...done
[01] Copying ./test/t1.ibd to/data/backups/mysql/incre_20150320/test/t1.ibd.delta
[01]       ...done
[01] Copying ./test/t4.ibd to/data/backups/mysql/incre_20150320/test/t4.ibd.delta
[01]       ...done
[01] Copying ./test/t3.ibd to/data/backups/mysql/incre_20150320/test/t3.ibd.delta
[01]       ...done
[01] Copying ./test/t.ibd to/data/backups/mysql/incre_20150320/test/t.ibd.delta
[01]       ...done
[01] Copying ./user_db/t1.ibd to /data/backups/mysql/incre_20150320/user_db/t1.ibd.delta
[01]       ...done
[01] Copying ./user_db/test.ibd to/data/backups/mysql/incre_20150320/user_db/test.ibd.delta
[01]       ...done
>> log scanned up to (423925813)
[01] Copying ./user_db/test2.ibd to /data/backups/mysql/incre_20150320/user_db/test2.ibd.delta
[01]       ...done
[01] Copying ./user_db/t3.ibd to/data/backups/mysql/incre_20150320/user_db/t3.ibd.delta
[01]       ...done
[01] Copying ./user_db/UC_USER.ibd to /data/backups/mysql/incre_20150320/user_db/UC_USER.ibd.delta
[01]       ...done
[01] Copying ./user_db/t.ibd to/data/backups/mysql/incre_20150320/user_db/t.ibd.delta
[01]       ...done
xtrabackup: The latest check point (forincremental): '423925813'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (423925813)

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

 

 

4,使用rpm包安装xtrabackup
rpm -ivh ./percona-xtrabackup-2.1.8-733.rhel6.x86_64.rpm

示例组3:完全&定点&误操作恢复

3.3 再建立表2,实现第二天增量数据

建立一张表t_incre_2:

 

mysql> use test;
Database changed
mysql> create table t_incre_2 select'cc' as a,2 as b;
Query OK, 1 row affected (0.25 sec)
Records: 1 Duplicates: 0  Warnings: 0

mysql> insert into t_incre_2 select'dd',2;
Query OK, 1 row affected (0.05 sec)
Records: 1 Duplicates: 0  Warnings: 0

mysql> select * from t_incre_2;
+----+---+
| a | b |
+----+---+
| cc | 2 |
| dd | 2 |
+----+---+
2 rows in set (0.05 sec)

mysql>

 

 

5,检查服务器是否安装了mysql,若没有则需要提前安装。
且my.cnf 中必须有配置datadir=/var/lib/mysql
。若没有配置则可以拷贝一份线上的my.cnf,然后加上此项。

#数据目录:/data/mysql
$ mkdir -pv /backup/3306/20171107/{full,inc1,inc2}
#本地 单实例 全量备份
$ innobackupex –defaults-file=/etc/my.cnf  –user=root
–password=123456 –socket=/tmp/mysql.sock –no-timestamp  –parallel=4 
/backup/3306/20171107/full
#本地 单实例 增量备份1
$ mysql  -S /tmp/mysql.sock  -uroot -p123456 -e “create table
tpccdb.tb3(col1 varchar(16));”
$ mysql  -S /tmp/mysql.sock  -uroot -p123456 -e “insert into  tpccdb.tb3
values(‘1’),(‘2’),(‘3’),(‘第一次插入结束’);”
$ innobackupex –defaults-file=/etc/my.cnf  –user=root 
–password=123456  –socket=/tmp/mysql.sock –no-timestamp 
–parallel=4  –incremental-basedir=/backup/3306/20171107/full 
–incremental /backup/3306/20171107/inc1
#本地 单实例 增量备份2
$ mysql  -S /tmp/mysql.sock  -uroot -p123456 -e “insert into  tpccdb.tb3
values(‘4’),(‘5’),(‘6’),(‘第二次插入结束’);”
$ innobackupex –defaults-file=/etc/my.cnf  –user=root 
–password=123456  –socket=/tmp/mysql.sock –no-timestamp 
–parallel=4  –incremental-basedir=/backup/3306/20171107/inc1 
–incremental /backup/3306/20171107/inc2

3.4 进行第二天的增量备份

备份命令:

xtrabackup –defaults-file=/etc/my.cnf–backup –user=backup
–password=”123456″ –port=3306
–target-dir=/data/backups/mysql/incre_20150321/–incremental-basedir=/data/backups/mysql/incre_20150320
2>/tmp/incre_backup_2.log

 

备份信息记录如下:

 

[[email protected] ~]# more /tmp/incre_backup_2.log
xtrabackup version 2.2.9 based on MySQLserver 5.6.22 Linux (x86_64) (revision id: )
incremental backup from 423925813 isenabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /home/data/mysql/data
xtrabackup: open files limit requested 0,set to 1024
xtrabackup: using the following InnoDBconfiguration:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 3
xtrabackup:   innodb_log_file_size = 67108864
>> log scanned up to (423931610)
xtrabackup: Generating a list oftablespaces
xtrabackup: using the full scan forincremental backup
[01] Copying ./IBdata1 to/data/backups/mysql/incre_20150321/IBdata1.delta
>> log scanned up to (423931610)
>> log scanned up to (423931610)
>> log scanned up to (423931610)
>> log scanned up to (423931610)
>> log scanned up to (423931610)
[01]       ...done
>> log scanned up to (423931610)
[01] Copying ./IBdata2 to/data/backups/mysql/incre_20150321/IBdata2.delta
>> log scanned up to (423931610)
>> log scanned up to (423931610)
[01]       ...done
[01] Copying ./mysql/slave_master_info.ibdto /data/backups/mysql/incre_20150321/mysql/slave_master_info.ibd.delta
[01]       ...done
[01] Copying./mysql/slave_relay_log_info.ibd to/data/backups/mysql/incre_20150321/mysql/slave_relay_log_info.ibd.delta
[01]       ...done
>> log scanned up to (423931610)
[01] Copying ./mysql/innodb_index_stats.ibdto /data/backups/mysql/incre_20150321/mysql/innodb_index_stats.ibd.delta
[01]       ...done
[01] Copying ./mysql/slave_worker_info.ibdto /data/backups/mysql/incre_20150321/mysql/slave_worker_info.ibd.delta
[01]       ...done
[01] Copying ./mysql/innodb_table_stats.ibdto /data/backups/mysql/incre_20150321/mysql/innodb_table_stats.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest1.ibd to/data/backups/mysql/incre_20150321/sbtest/sbtest1.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest5.ibd to /data/backups/mysql/incre_20150321/sbtest/sbtest5.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest8.ibd to/data/backups/mysql/incre_20150321/sbtest/sbtest8.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest4.ibd to/data/backups/mysql/incre_20150321/sbtest/sbtest4.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest2.ibd to/data/backups/mysql/incre_20150321/sbtest/sbtest2.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest6.ibd to /data/backups/mysql/incre_20150321/sbtest/sbtest6.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest7.ibd to/data/backups/mysql/incre_20150321/sbtest/sbtest7.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest3.ibd to/data/backups/mysql/incre_20150321/sbtest/sbtest3.ibd.delta
[01]       ...done
[01] Copying ./test/t1.ibd to/data/backups/mysql/incre_20150321/test/t1.ibd.delta
[01]       ...done
[01] Copying ./test/t4.ibd to/data/backups/mysql/incre_20150321/test/t4.ibd.delta
[01]       ...done
[01] Copying ./test/t3.ibd to/data/backups/mysql/incre_20150321/test/t3.ibd.delta
[01]       ...done
>> log scanned up to (423931610)
[01] Copying ./test/t.ibd to/data/backups/mysql/incre_20150321/test/t.ibd.delta
[01]       ...done
[01] Copying ./test/t_incre_2.ibd to/data/backups/mysql/incre_20150321/test/t_incre_2.ibd.delta
[01]       ...done
[01] Copying ./user_db/t1.ibd to/data/backups/mysql/incre_20150321/user_db/t1.ibd.delta
[01]       ...done
[01] Copying ./user_db/test.ibd to /data/backups/mysql/incre_20150321/user_db/test.ibd.delta
[01]       ...done
[01] Copying ./user_db/test2.ibd to/data/backups/mysql/incre_20150321/user_db/test2.ibd.delta
[01]       ...done
[01] Copying ./user_db/t3.ibd to/data/backups/mysql/incre_20150321/user_db/t3.ibd.delta
[01]       ...done
[01] Copying ./user_db/UC_USER.ibd to/data/backups/mysql/incre_20150321/user_db/UC_USER.ibd.delta
[01]       ...done
[01] Copying ./user_db/t.ibd to/data/backups/mysql/incre_20150321/user_db/t.ibd.delta
[01]       ...done
>> log scanned up to (423931610)
xtrabackup: The latest check point (forincremental): '423931610'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (423931610)

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

 

下面开始重头戏讲一下备份的具体情况:

全量备份:innobackupex –user=root –password=”
–defaults-file=/data/mysql/server/3307/mysql-5536/my.cnf –port=3307
–socket=/data/mysql/server/3307/mysql-5536/mysql.sock –parallel=8
/data/alldata/

ls -l /data/alldata/2014-03-25_10-48-36 自动创建此时间名称目录
该语句将拷贝数据文件(由my.cnf里的变量datadir指定)至备份目录下(/data/alldata/),注意:如果不指定–defaults-file,默认值为/etc/my.cnf。我的默认配置文件中没有指定datadir,当是以为需要重启数据库,其实不必,只要将配置文件拷贝到某个位置,修改一下,备份时调用即可,我的拷贝到/data下,备份成功后,将在备份目录下创建一个时间戳目录(本例创建的目录为/data/alldata/2014-03-25_10-48-36),在该目录下存放备份文件

增量备份:
增量备份需要基于全备,先假设我们已经有了一个全备(ll
/data/alldata/2014-03-25_10-48-36/),我们需要在该全备的基础上做增量备份

innobackupex –defaults-file=/data/mysql/server/3307/mysql-5536/my.cnf
–user=root –password=” –port=3307
–socket=/data/mysql/server/3307/mysql-5536/mysql.sock
–incremental-basedir=/data/alldata/2014-03-25_10-48-36/ –incremental
/data/partdata

innobackupex –defaults-file=/data/mysql/server/3307/mysql-5536/my.cnf
–user=root –password=” –port=3307
–socket=/data/mysql/server/3307/mysql-5536/mysql.sock
–incremental-basedir=/data/partdata/2014-03-25_10-52-40/ –incremental
/data/partdata
[root@NYSJHL65-195 mysql-5536]# ll -tr /data/partdata/
total 8
drwxr-xr-x 6 root root 4096 Mar 25 10:54 2014-03-25_10-52-40 #
第1次基于全量的增量备份
drwxr-xr-x 6 root root 4096 Mar 25 10:57 2014-03-25_10-56-34 #
第2次基于第一增量的增量备份

$ mysql  -S /tmp/mysql.sock  -uroot -p123456 -e “insert into  tpccdb.tb3
values(‘7’),(‘8’),(‘9’),(‘第三次插入结束’);”  ##二进制日志恢复
$ mysql  -S /tmp/mysql.sock  -uroot -p123456 -e “flush logs;” 
$ mysql  -S /tmp/mysql.sock  -uroot -p123456 -e “insert into  tpccdb.tb3
values(’10’),(’11’),(’12’),(‘第四次插入结束’);”
$ mysql  -S /tmp/mysql.sock  -uroot -p123456 -e “flush logs;” 
$ mysql  -S /tmp/mysql.sock  -uroot -p123456 -e “insert into  tpccdb.tb3
values(’13’),(’14’),(’15’),(‘第五次插入结束’);”

4,xtrabackup进行增量恢复

恢复操作

标签:,

Your Comments

近期评论

    功能


    网站地图xml地图