MySQL[3] 在线热备工具Xtrabackup
1、实验前的准备工作# service mysqld stop
# rm -rf /mydata/data/* //删除原来的备份文件
# rm -rf /backup/*
# /usr/local/mysql/scripts/mysql_install_db --user=mysql --datadir=/mydata/data/ --basedir=/usr/local/mysql/
# service mysqld start
# mysqladmin -uroot -p password 123456
# mysql -uroot -p123456
mysql> create database jiaowu;
mysql> use jiaowu;
mysql> set sql_log_bin = 0;
mysql> source /root/tutor.sql; //导入tutor数据表
mysql> set sql_log_bin = 1;
mysql> select * from tutor;
+------+---------------+--------+------+
| TID| Tname | Gender | Age|
+------+---------------+--------+------+
| 1 | ZhengYansheng | M | 25 |
| 2 | LiJian | M | 26 |
| 3 | OuYangyu | M | 27 |
| 4 | LuoChenghui | M | 25 |
| 5 | LiuYunbo | M | 25 |
| 6 | FuJian | M | 24 |
| 7 | LiMenglu | F | 23 |
| 8 | BaoYintu | M | 28 |
| 9 | WangYana | F | 25 |
+------+---------------+--------+------+
9 rows in set (0.00 sec)
2、innobackupex对DB进行完全备份
# innobackupex --user=root --password=123456 /backup/
如果执行正确，其最后输出的几行信息通常如下：
innobackupex: Backup created in directory '/backup/2015-03-18_21-14-49'
innobackupex: MySQL binlog position: filename 'mysql-bin.000003', position 332
150318 21:14:54innobackupex: Connection to database server closed
150318 21:14:54innobackupex: completed OK!
3、仅查看备份目录
# ls /backup/
2015-03-18_21-14-49
4、操作数据库并插入数据
# mysql jiaowu;
mysql> insert into tutor(TID) values(11);
mysql> insert into tutor(TID) values(12);
mysql> insert into tutor(TID) values(13);
mysql> select * from tutor;
+------+---------------+--------+------+
| TID| Tname | Gender | Age|
+------+---------------+--------+------+
| 1 | ZhengYansheng | M | 25 |
| 2 | LiJian | M | 26 |
| 3 | OuYangyu | M | 27 |
| 4 | LuoChenghui | M | 25 |
| 5 | LiuYunbo | M | 25 |
| 6 | FuJian | M | 24 |
| 7 | LiMenglu | F | 23 |
| 8 | BaoYintu | M | 28 |
| 9 | WangYana | F | 25 |
| 11 | NULL | NULL | NULL |
| 12 | NULL | NULL | NULL |
| 13 | NULL | NULL | NULL |
+------+---------------+--------+------+
12 rows in set (0.00 sec)
5、执行第一次增量备份并查看备份目录
# innobackupex --user=root --password=123456 --incremental /backup/ --incremental-basedir=/backup/2015-03-18_21-14-49/
# ls /backup/
2015-03-18_21-14-492015-03-18_21-18-45
6、再次操作数据库并插入多条数据
# mysql jiaowu;
mysql> insert into tutor(TID) values(21);
mysql> insert into tutor(TID) values(22);
mysql> insert into tutor(TID) values(23);
mysql> select * from tutor;
+------+---------------+--------+------+
| TID| Tname | Gender | Age|
+------+---------------+--------+------+
| 1 | ZhengYansheng | M | 25 |
| 2 | LiJian | M | 26 |
| 3 | OuYangyu | M | 27 |
| 4 | LuoChenghui | M | 25 |
| 5 | LiuYunbo | M | 25 |
| 6 | FuJian | M | 24 |
| 7 | LiMenglu | F | 23 |
| 8 | BaoYintu | M | 28 |
| 9 | WangYana | F | 25 |
| 11 | NULL | NULL | NULL |
| 12 | NULL | NULL | NULL |
| 13 | NULL | NULL | NULL |
| 21 | NULL | NULL | NULL |
| 22 | NULL | NULL | NULL |
| 23 | NULL | NULL | NULL |
+------+---------------+--------+------+
15 rows in set (0.00 sec)
7、执行第二次增量备份并查看备份文件
# innobackupex --user=root --password=123456 --incremental /backup/ --incremental-basedir=/backup/2015-03-18_21-18-45/
# ls /backup/
2015-03-18_21-14-492015-03-18_21-18-452015-03-18_21-22-31
解释：
2015-03-18_21-14-49：为innobackupex的完全备份目录
2015-03-18_21-18-45：为innobackupex的第一次增量备份目录
2015-03-18_21-22-31：为innobackupex的第二次增量备份目录
8、开始准备innobackupex
首先执行完全备份redo-only
# innobackupex --user=root --password=123456 --apply-log --redo-only /backup/2015-03-18_21-14-49/
执行第一个增量备份redo-only
# innobackupex --user=root --password=123456 --apply-log --redo-only /backup/2015-03-18_21-14-49/ --incremental-dir=/backup/2015-03-18_21-18-45/
执行第二个增量备份redo-only
# innobackupex --user=root --password=123456 --apply-log --redo-only /backup/2015-03-18_21-14-49/ --incremental-dir=/backup/2015-03-18_21-22-31/
#####模拟数据库故障#####
9、这里还是采用老方法直接删除所有的数据文件
# service mysqld stop
# rm -rf /mydata/data/*
10、从完全备份中恢复数据库
# innobackupex --user=root --password=123456 --copy-back /backup/2015-03-18_21-14-49/
11、修改数据目录权限
# chown -R mysql.mysql /mydata/data/
12、启动mysqld服务
# service mysqld start
13、登陆mysql查看是否是否一致
# mysql -e 'use jiaowu;select * from tutor;'
+------+---------------+--------+------+
| TID| Tname | Gender | Age|
+------+---------------+--------+------+
| 1 | ZhengYansheng | M | 25 |
| 2 | LiJian | M | 26 |
| 3 | OuYangyu | M | 27 |
| 4 | LuoChenghui | M | 25 |
| 5 | LiuYunbo | M | 25 |
| 6 | FuJian | M | 24 |
| 7 | LiMenglu | F | 23 |
| 8 | BaoYintu | M | 28 |
| 9 | WangYana | F | 25 |
| 11 | NULL | NULL | NULL |
| 12 | NULL | NULL | NULL |
| 13 | NULL | NULL | NULL |
| 21 | NULL | NULL | NULL |
| 22 | NULL | NULL | NULL |
| 23 | NULL | NULL | NULL |
+------+---------------+--------+------+
#两次增量添加的数据也已经成功添加到数据库当中。恢复成功
页:
[1]