DataBase
/
RDBMS1_DAY05

在50主机安装percona软件提供备份命令 innobackupex
[root@mysql50 upload]# rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm
[root@mysql50 upload]# yum -y install percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm
[root@mysql50 upload]# which  innobackupex
/usr/bin/innobackupex
[root@mysql50 upload]#


准备练习环境 ：
1  删除50主机自己创建的新库 ，只保留数据库服务器默认的4个库
2 创建数据备份练习使用的库
mysql> create database db5;
Query OK, 1 row affected (0.02 sec)

mysql> create table db5.a (id int);
Query OK, 0 rows affected (0.02 sec)

mysql> create table db5.b (name char(10));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into  db5.a values(1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> insert into  db5.b values("tom"),("lucy"),("jerry"),("lili"),("meimei");
Query OK, 5 rows affected (2.06 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql>
mysql> show databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db5                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

mysql> select count(*) from db5.a;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.50 sec)

mysql> select count(*) from db5.b;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

mysql>


在50主机做完全备份
[root@mysql50 ~]# innobackupex --user root  --password 123bbb...A   /allbak  --no-timestamp

[root@mysql50 ~]# ls /allbak/
backup-my.cnf   ibdata1             sys                     xtrabackup_info
db5             mysql               xtrabackup_binlog_info  xtrabackup_logfile
ib_buffer_pool  performance_schema  xtrabackup_checkpoints
[root@mysql50 ~]#

[root@mysql50 ~]# scp -r /allbak  root@192.168.4.51:/root/

在51主机恢复数据，步骤如下：
安装提供命令的软件包
					停止数据库服务
					清空数据库目录
					准备恢复数据
					拷贝数据
					修改数据库目录下的所有者和组用户为mysql
				    启动数据库服务

[root@mysql51 ~]# which  innobackupex
/usr/bin/which: no innobackupex in (/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin)

[root@mysql51 upload]# rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm
[root@mysql51 upload]# yum -y install percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm

[root@mysql51 upload]# which  innobackupex
/usr/bin/innobackupex
[root@mysql51 upload]#

[root@mysql51 ~]# systemctl  stop mysqld
[root@mysql51 ~]# rm -rf /var/lib/mysql/*
[root@mysql51 ~]#
[root@mysql51 ~]# ls /var/lib/mysql
[root@mysql51 ~]#

]# cd /root/allbak
[root@mysql51 allbak]# ls
backup-my.cnf   ibdata1             sys                     xtrabackup_info
db5             mysql               xtrabackup_binlog_info  xtrabackup_logfile
ib_buffer_pool  performance_schema  xtrabackup_checkpoints
[root@mysql51 allbak]#
[root@mysql51 allbak]# cat xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 3146638
last_lsn = 3146647
compact = 0
recover_binlog_info = 0
[root@mysql51 allbak]#

[root@mysql51 ~]# innobackupex  --apply-log /root/allbak/

[root@mysql51 ~]# cat  /root/allbak/xtrabackup_checkpoints
backup_type = full-prepared
from_lsn = 0
to_lsn = 3146638
last_lsn = 3146647
compact = 0
recover_binlog_info = 0
[root@mysql51 ~]#

[root@mysql51 ~]# ls /var/lib/mysql
[root@mysql51 ~]#
[root@mysql51 ~]# innobackupex  --copy-back /root/allbak/

[root@mysql51 ~]# chown  -R mysql:mysql /var/lib/mysql
[root@mysql51 ~]# ls  -l /var/lib/mysql
总用量 188456
drwxr-x---. 2 mysql mysql       72 5月  21 00:09 db5
-rw-r-----. 1 mysql mysql      530 5月  21 00:09 ib_buffer_pool
-rw-r-----. 1 mysql mysql 79691776 5月  21 00:09 ibdata1
-rw-r-----. 1 mysql mysql 50331648 5月  21 00:09 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 5月  21 00:09 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 5月  21 00:09 ibtmp1
drwxr-x---. 2 mysql mysql     4096 5月  21 00:09 mysql
drwxr-x---. 2 mysql mysql     8192 5月  21 00:09 performance_schema
drwxr-x---. 2 mysql mysql     8192 5月  21 00:09 sys
-rw-r-----. 1 mysql mysql       17 5月  21 00:09 xtrabackup_binlog_pos_innodb
-rw-r-----. 1 mysql mysql      485 5月  21 00:09 xtrabackup_info
[root@mysql51 ~]#
[root@mysql51 ~]# systemctl  start mysqld
[root@mysql51 ~]# mysql -uroot -p123bbb...A

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db5                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.06 sec)

mysql> select  * from db5.a;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.06 sec)

mysql> select  * from db5.b;
+--------+
| name   |
+--------+
| tom    |
| lucy   |
| jerry  |
| lili   |
| meimei |
+--------+
5 rows in set (0.01 sec)

mysql>


恢复单张表的所有数据
51]#
mysql> delete from db5.b;
mysql>  select  * from  db5.b;

表空间？存储表里数据的文件
	  /var/lib/mysql/库名/表名.ibd

具体操作如下：

删除表空间
mysql>
mysql> alter table db5.b discard  tablespace;
Query OK, 0 rows affected (0.10 sec)

mysql> select  * from db5.b;
ERROR 1814 (HY000): Tablespace has been discarded for table 'b'
mysql>
mysql> system ls /var/lib/mysql/db5/b.*
/var/lib/mysql/db5/b.frm
mysql>

导出表信息

[root@mysql51 ~]# innobackupex --apply-log --export /root/allbak

[root@mysql51 ~]# ls /root/allbak/db5/
a.cfg  a.exp  a.frm  a.ibd  b.cfg  b.exp  b.frm  b.ibd  db.opt
[root@mysql51 ~]#
拷贝表信息文件到数据库目录下
[root@mysql51 ~]# cp /root/allbak/db5/b.{cfg,exp,ibd} /var/lib/mysql/db5/
[root@mysql51 ~]# ls -l /var/lib/mysql/db5/b.*
-rw-r--r--. 1 root  root    369 5月  21 01:02 /var/lib/mysql/db5/b.cfg
-rw-r-----. 1 root  root  16384 5月  21 01:02 /var/lib/mysql/db5/b.exp
-rw-r-----. 1 mysql mysql  8560 5月  21 00:09 /var/lib/mysql/db5/b.frm
-rw-r-----. 1 root  root  98304 5月  21 01:02 /var/lib/mysql/db5/b.ibd
[root@mysql51 ~]#

修改表信息文件的所有者及组用户为mysql
[root@mysql51 ~]# chown  mysql:mysql /var/lib/mysql/db5/b.*

导入表空间
mysql> select  * from db5.b;
ERROR 1814 (HY000): Tablespace has been discarded for table 'b'
mysql>
mysql> alter  table db5.b   import  tablespace;
Query OK, 0 rows affected (0.03 sec)

mysql> select  * from db5.b; 查看表记录
+--------+
| name   |
+--------+
| tom    |
| lucy   |
| jerry  |
| lili   |
| meimei |
+--------+
5 rows in set (0.00 sec)

mysql>

删除数据库目录下的表信息文件
[root@mysql51 ~]# rm -rf /var/lib/mysql/db5/b.cfg
[root@mysql51 ~]# rm -rf /var/lib/mysql/db5/b.exp
[root@mysql51 ~]#
[root@mysql51 ~]# ls /var/lib/mysql/db5/b.*
/var/lib/mysql/db5/b.frm  /var/lib/mysql/db5/b.ibd
[root@mysql51 ~]#


3、innobackupex 数据增量备份与恢复
				数据增量备份命令格式

]#innobackupex --user root --password  密码   --incremental  /new1dir
   --incremental-basedir=上次备份文件存储的目录   --no-timestamp


首次完全备份数据
[root@mysql50 ~]# innobackupex --user root --password 123bbb...A  /fullbak --no-timestamp
[root@mysql50 ~]# ls /fullbak
backup-my.cnf   ibdata1             sys                     xtrabackup_info
db5             mysql               xtrabackup_binlog_info  xtrabackup_logfile
ib_buffer_pool  performance_schema  xtrabackup_checkpoints
[root@mysql50 ~]#

向表里写新数据
mysql> insert into db5.a values(333); 多写几条
mysql> insert into db5.b values("plj");多写几条

增量备份
[root@mysql50 ~]# innobackupex --user root --password 123bbb...A  --incremental /new1dir  --incremental-basedir=/fullbak --no-timestamp

[root@mysql50 ~]# ls /new1dir/
backup-my.cnf   ibdata1.delta  performance_schema      xtrabackup_checkpoints
db5             ibdata1.meta   sys                     xtrabackup_info
ib_buffer_pool  mysql          xtrabackup_binlog_info  xtrabackup_logfile
[root@mysql50 ~]#

向表里写新数据
mysql> insert into db5.a values(444); 多写几条
mysql> insert into db5.b values("yaya");多写几条

[root@mysql50 ~]# innobackupex --user root --password 123bbb...A  --incremental /new2dir  --incremental-basedir=/new1dir --no-timestamp

[root@mysql50 ~]# ls /new2dir/
backup-my.cnf   ibdata1.delta  performance_schema      xtrabackup_checkpoints
db5             ibdata1.meta   sys                     xtrabackup_info
ib_buffer_pool  mysql          xtrabackup_binlog_info  xtrabackup_logfile
[root@mysql50 ~]#


innobackupex 增量备份的工作过程？
	innobackupex  如何知道在所有的数据，那些数据是新产生？
	innobackupex 如何判断备份过程中是否有新数据写入？

                记录数据备份方式 和 数据的备份范围  xtrabackup_checkpoints  (相当于书签)

                lsn  日志序列号

                事务日志文件 /var/lib/mysql/
 	ib_logfile0   ib_logfile1


innobackupex 增量恢复数据
	增量恢复数据的命令格式：
]# innobackupex  --apply-log --redo-only  完全备份目录   --incremental-dir=增量备份目录

	恢复数据的步骤
		停止本机数据库服务器
		清空数据库目录
		准备恢复数据     （恢复数据的起始位置）
		增量恢复（合并数据）  周一 + 周二 + 周三
		拷贝备份数据到数据库目录下
		修改文件的所有者和组用户为mysql
		启动服务
		查看数据

[root@mysql50 ~]# scp  -r  /fullbak root@192.168.4.51:/root/
[root@mysql50 ~]# scp  -r  /new1dir/ root@192.168.4.51:/root/
[root@mysql50 ~]# scp  -r  /new2dir/ root@192.168.4.51:/root/


[root@mysql51 ~]# systemctl  stop mysqld
[root@mysql51 ~]# rm  -rf /var/lib/mysql/*

[root@mysql51 ~]# cat /root/fullbak/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 2559675
last_lsn = 2559684
compact = 0
recover_binlog_info = 0
[root@mysql51 ~]#

[root@mysql51 ~]# innobackupex  --apply-log --redo-only /root/fullbak/
[root@mysql51 ~]# cat /root/fullbak/xtrabackup_checkpoints
backup_type = log-applied
from_lsn = 0
to_lsn = 2559675
last_lsn = 2559684
compact = 0
recover_binlog_info = 0
[root@mysql51 ~]# lsn


[root@mysql51 ~]# innobackupex  --apply-log --redo-only /root/fullbak/   --incremental-dir=/root/new1dir/


[root@mysql51 ~]# cat /root/fullbak/xtrabackup_checkpoints
backup_type = log-applied
from_lsn = 0
to_lsn = 2581005
last_lsn = 2581014
compact = 0
recover_binlog_info = 0
[root@mysql51 ~]#


[root@mysql51 ~]# innobackupex  --apply-log --redo-only /root/fullbak/   --incremental-dir=/root/new2dir/

[root@mysql51 ~]# cat /root/new2dir/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 2581005
to_lsn = 2618152
last_lsn = 2618161
compact = 0
recover_binlog_info = 0
[root@mysql51 ~]#

[root@mysql51 ~]# innobackupex  --copy-back /root/fullbak/

[root@mysql51 ~]# chown  -R mysql:mysql /var/lib/mysql
[root@mysql51 ~]# ls -l /var/lib/mysql
total 12324
drwxr-x---. 2 mysql mysql       72 May 22 18:39 db5
-rw-r-----. 1 mysql mysql      413 May 22 18:39 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 May 22 18:39 ibdata1
drwxr-x---. 2 mysql mysql     4096 May 22 18:39 mysql
drwxr-x---. 2 mysql mysql     8192 May 22 18:39 performance_schema
drwxr-x---. 2 mysql mysql     8192 May 22 18:39 sys
-rw-r-----. 1 mysql mysql      494 May 22 18:39 xtrabackup_info
[root@mysql51 ~]#