2安装第3方软件提供的命令 ***
percona 软件名——真正意义的增量备份
特点:开源软件 可以在线热备
第三方软件提供的两条命令:
innobackupex——支持以per脚本封装xtrabackup
xtrabackup ——c程序,支持innodb /xtradb 两种数据存储引擎
1.安装软件包:
错误:依赖检测失败:
libev.so.4()(64bit) 被 percona-xtrabackup-24-2.4.7-1.el7.x86_64 需要
perl(DBD::mysql) 被 percona-xtrabackup-24-2.4.7-1.el7.x86_64 需要
perl(Digest::MD5) 被 percona-xtrabackup-24-2.4.7-1.el7.x86_64 需要
装包的时候,要解除相关的依赖包的安装:
[root@host50 ~]# rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm ——这是一个模块的依赖包
[root@host50 ~]# yum -y install perl-DBD-mysql perl-Digest-MD5
[root@host50 ~]# rpm -ivh percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm——主软件包
[root@host50 ~]# rpm -ql percona-xtrabackup-24
/usr/bin/innobackupex
/usr/bin/xbcloud
/usr/bin/xbcloud_osenv
/usr/bin/xbcrypt
/usr/bin/xbstream
/usr/bin/xtrabackup
/usr/share/doc/percona-xtrabackup-24-2.4.7
/usr/share/doc/percona-xtrabackup-24-2.4.7/COPYING
/usr/share/man/man1/innobackupex.1.gz
/usr/share/man/man1/xbcrypt.1.gz
/usr/share/man/man1/xbstream.1.gz
/usr/share/man/man1/xtrabackup.1.gz
两个命令的使用方法的帮助介绍:
[root@host50 ~]# man xtrabackup
[root@host50 ~]# man innobackupex
命令格式:innobackupex <选项>
<选项>
--user
--password
--host
--databases 数据库名
--no-timestamp 不用时间日期命名备份文件存储子目录
--port
[root@host50 ~]# systemctl stop mysqld
[root@host50 ~]# vim /etc/my.cnf
#default-storage-engine=myisam
:wq
[root@host50 ~]# systemctl restart mysqld
mysql> show engines;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 3
Current database: db3
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it
一。完全备份和完全恢复
1.实验环境准备:
准备两张表
mysql> select * from t1; select * from t2;
+--------+
| id |
+--------+
| 11 |
| 1111 |
| 2222 |
| 33333 |
| 122222 |
| 33333 |
| 11111 |
| 222222 |
+--------+
8 rows in set (0.00 sec)
+--------+
| id |
+--------+
| 11111 |
| 222222 |
| 33333 |
| 444444 |
+--------+
2.备份数据
[root@host50 ~]# innobackupex --user root --password 123456 --databases="king3" /allbak
[root@host50 ~]# ls /allbak/
2018-07-17_17-18-52 2018-07-17_17-20-22
[root@host50 ~]# rm -rf /allbak
[root@host50 ~]# innobackupex --user root --password 123456 --databases="mysql performance_schema information_schema king3" /allbak --no-timestamp
[root@host50 ~]# ls /allbak/(备份目录下即有
backup-my.cnf ibdata1 mysql xtrabackup_binlog_info xtrabackup_info
ib_buffer_pool king3 performance_schema xtrabackup_checkpoints xtrabackup_logfile
3.完全恢复
*要求数据库目录必须为空:
3.1准备恢复数据——把日志文件回滚
[root@host50 allbak]# cat xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 3733797
last_lsn = 3733806
compact = 0
recover_binlog_info = 0
[root@host50 allbak]# innobackupex --user root --password 123456 --databases="mysql performance_schema information_schema king3" --apply-log /allbak
[root@host50 allbak]# cat xtrabackup_checkpoints
backup_type = full-prepared
from_lsn = 0
to_lsn = 3733797
last_lsn = 3733806
compact = 0
recover_binlog_info = 0
3.2把备份目录下数据库拷贝回数据库目录下
[root@host50 allbak]# innobackupex --user root --password 123456 --databases="mysql performance_schema information_schema king3" --copy-back /allbak
innobackupex version 2.4.7 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 6f7a799)
Original data directory /var/lib/mysql is not empty!
[root@host50 allbak]# rm -rf /var/lib/mysql/*
[root@host50 allbak]# systemctl stop mysqld
[root@host50 allbak]# innobackupex --user root --password 123456 --databases="mysql performance_schema information_schema king3" --copy-back /allbak
180717 17:47:49 completed OK!
3.3 修改数据库目录所属组和所属者为mysql
[root@host50 ~]# ll /var/lib/mysql
总用量 188444
-rw-r-----. 1 mysql root 561 7月 17 17:51 ib_buffer_pool
-rw-r-----. 1 mysql root 79691776 7月 17 17:51 ibdata1
-rw-r-----. 1 mysql root 50331648 7月 17 17:51 ib_logfile0
-rw-r-----. 1 mysql root 50331648 7月 17 17:51 ib_logfile1
-rw-r-----. 1 mysql root 12582912 7月 17 17:51 ibtmp1
drwxr-x---. 2 mysql root 20 7月 17 17:51 king3
drwxr-x---. 2 mysql root 4096 7月 17 17:51 mysql
drwxr-x---. 2 mysql root 8192 7月 17 17:51 performance_schema
-rw-r-----. 1 mysql root 17 7月 17 17:51 xtrabackup_binlog_pos_innodb
-rw-r-----. 1 mysql root 546 7月 17 17:51 xtrabackup_info
[root@host50 ~]# chown -R mysql:mysql /var/lib/mysql
[root@host50 ~]# ll /var/lib/mysql
总用量 188452
-rw-r-----. 1 mysql mysql 56 7月 17 18:02 auto.cnf
-rw-r-----. 1 mysql mysql 561 7月 17 17:51 ib_buffer_pool
-rw-r-----. 1 mysql mysql 79691776 7月 17 18:02 ibdata1
-rw-r-----. 1 mysql mysql 50331648 7月 17 18:02 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 7月 17 17:51 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 7月 17 18:02 ibtmp1
drwxr-x---. 2 mysql mysql 20 7月 17 17:51 king3
drwxr-x---. 2 mysql mysql 4096 7月 17 17:51 mysql
srwxrwxrwx. 1 mysql mysql 0 7月 17 18:02 mysql.sock
-rw-------. 1 mysql mysql 5 7月 17 18:02 mysql.sock.lock
drwxr-x---. 2 mysql mysql 8192 7月 17 17:51 performance_schema
-rw-r-----. 1 mysql mysql 17 7月 17 17:51 xtrabackup_binlog_pos_innodb
-rw-r-----. 1 mysql mysql 546 7月 17 17:51 xtrabackup_info
3.4 重起数据库服务
[root@host50 ~]# systemctl start mysqld
3.5登陆查看数据
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| king3 |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
增量备份是从上一次,后产生的新数据
innobackupex增量备份
实验步骤:
先做一个完全备份
[root@host50 ~]# innobackupex --user root --password 123456 /fullbak --no-timestamp
[root@host50 ~]# ls /fullbak/
backup-my.cnf king3 xtrabackup_binlog_info xtrabackup_logfile
ib_buffer_pool mysql xtrabackup_checkpoints
ibdata1 performance_schema xtrabackup_in
完全备份后,向表里写入新的数据:
mysql> insert into a values (9090),(8806);
Query OK, 1 row affected (0.04 sec)
mysql> insert into b values(456),(2356);
Query OK, 1 row affected (0.05 sec)
select * from a; select * from b;
对数据进行增量备份:
[root@host50 ~]# innobackupex --user root --password 123456 --incremental /new1dir --incremental-basedir=/fullbak --no-timestamp
[root@host50 ~]# ls /new1dir/
backup-my.cnf ibdata1.meta performance_schema xtrabackup_info
ib_buffer_pool king3 xtrabackup_binlog_info xtrabackup_logfile
ibdata1.delta mysql xtrabackup_checkpoints
[root@host50 ~]# ls /fullbak/
backup-my.cnf king3 xtrabackup_binlog_info xtrabackup_logfile
ib_buffer_pool mysql xtrabackup_checkpoints
ibdata1 performance_schema xtrabackup_info
增量备份后,继续向表里写入新的数据:
mysql> insert into a values (999),(8888),(777);
Query OK, 3 rows affected (0.09 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into b values (666),(5558),(447);
Query OK, 3 rows affected (0.10 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from a; select * from b;
[root@host50 ~]# innobackupex --user root --password 123456 --incremental /new2dir --incremental-basedir=/new1dir --no-timestamp
[root@host50 ~]# ls /new1dir/
backup-my.cnf ibdata1.meta performance_schema xtrabackup_info
ib_buffer_pool king3 xtrabackup_binlog_info xtrabackup_logfile
ibdata1.delta mysql xtrabackup_checkpoints
[root@host50 ~]# ls /new2dir/
backup-my.cnf ibdata1.meta performance_schema xtrabackup_info
ib_buffer_pool king3 xtrabackup_binlog_info xtrabackup_logfile
ibdata1.delta mysql xtrabackup_checkpoints
[root@host50 ~]# ls /new1dir/king3
a.frm a.ibd.delta a.ibd.meta b.frm b.ibd.delta b.ibd.meta db.opt
[root@host50 ~]# ls /new2dir/king3
a.frm a.ibd.delta a.ibd.meta b.frm b.ibd.delta b.ibd.meta db.opt
编写计划任务脚本
[root@host50 ~]# vim /root/allbak.sh
#!/bin/bash
day=`date+%F`
innobackupex --user root --password 123456 /fullbak_${day}
:wq
[root@host50 ~]# chmod +x /root/allbak.sh
[root@host50 ~]# vim /root/newallbak.sh
#!/bin/bash
jt=`date +%d`
zt=`expr $jt-1 `
m=`date +%m`
innobackupex --user root --password 123456 --incremental /new${m}_${jt}dir --incremental-basedir=/new${m}_${zt}dir --no-timestamp
:wq
[root@host50 ~]# chmod +x /root/newallbak.sh
crantab -e
00 23 * * 1 /root/allbak.sh
00 23 * * 2-7 /root/newallbak.sh
:wq
innobackupex增量恢复
实验步骤:
[root@host50 ~]# cat /new1dir/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 3747185
to_lsn = 3752351 lsn 是日志序列号,是命令记录新数据的标志
last_lsn = 3752360
compact = 0
recover_binlog_info = 0
[root@host50 ~]# cat /new2dir/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 3752351
to_lsn = 3756357
last_lsn = 3756366
compact = 0
recover_binlog_info = 0
准备恢复数据,将数据进行合并:
206 innobackupex --user root --password 123456 --apply-log --redo-only /fullbak
207 innobackupex --user root --password 123456 --apply-log --redo-only /fullbak --incremental-dir=/new1dir
208 innobackupex --user root --password 123456 --apply-log --redo-only /fullbak --incremental-dir=/new2dir
增量备份的时候,一定要按照备份顺序一致,不然就回出错。
[root@host50 ~]# rm -rf /new1dir/
[root@host50 ~]# rm -rf /new2dir/
把备份数据的目录拷贝到数据库目录下
[root@host50 ~]# systemctl stop mysqld
[root@host50 ~]# rm -rf /var/lib/mysql/*
[root@host50 ~]# innobackupex --user root --password 123456 --copy-back /fullbak/
修改目录的所属者和所属组为mysql
[root@host50 ~]# ll /var/lib/mysql
总用量 77852
-rw-r-----. 1 root root 561 7月 18 10:50 ib_buffer_pool
.......
-rw-r-----. 1 root root 533 7月 18 10:50 xtrabackup_info
[root@host50 ~]# chown -R mysql:mysql /var/lib/mysql
[root@host50 ~]# ll /var/lib/mysql
总用量 77852
-rw-r-----. 1 mysql mysql 561 7月 18 10:50 ib_buffer_pool
........
-rw-r-----. 1 mysql mysql 533 7月 18 10:50 xtrabackup_info
启动数据库服务
[root@host50 ~]# systemctl restart mysqld
登陆查看数据
mysql >show databases;
mysql>use king3;
mysql> show tables;
mysql> select * from a;
mysql> select * from b;
恢复后写入数据继续实行增量备份:
mysql> insert into a values (999);
Query OK, 1 row affected (0.04 sec)
[root@host50 ~]# innobackupex --user root --password 123456 --incremental /dir1 --incremental-basedir=/fullbak --no-timestamp
[root@host50 ~]# ls /dir1
backup-my.cnf ibdata1.meta performance_schema xtrabackup_info
ib_buffer_pool king3 xtrabackup_binlog_info xtrabackup_logfile
ibdata1.delta mysql xtrabackup_checkpoints
使用innobackupex 从完全备份文件里恢复单表
注意细节:
数据库目录不需要清空
必须要有完全备份
恢复数据时,恢复的表必须要与备份的表是一致的(表名。表结构)
实验步骤:
准备一个完全备份
228 innobackupex --user root --password 123456 /fullbak --no-timestamp
对库进行备份
230 innobackupex --user root --password 123456 --databases="king" --apply-log --export /fullback
查看备份目录
232 ls /fullbak/king/
模拟数据丢失
mysql> drop table a;
Query OK, 0 rows affected (0.18 sec)
创建表结构:
mysql> create table a(id int);
Query OK, 0 rows affected (0.24 sec)
删除表空间结构
mysql> alter table a discard tablespace;
Query OK, 0 rows affected (0.09 sec)
把导出的表信息文件,拷贝到对应的数据库目录下,并修改所有者和组为mysql
233 cp /fullbak/king/a.{cfg,exp,ibd} /var/lib/mysql/king/
234 ll /var/lib/mysql/king/
235 ll /var/lib/mysql/king/a*
236 chown -R mysql:mysql /var/lib/mysql/king/a*
导入表空间
mysql> alter table studb.a import tablespace;
删除表信息文件
237 rm -rf /var/lib/mysql/king/a.cfg
进入库查看恢复信息
mysql > select * from a;