• 1206、数据备份与恢复、binlog日志、使用binlog日志恢复数据、innobackupex

    文章目录

    一、数据备份与恢复

    1、备份概述

    1.1 相关概念

    数据备份： 就是把当前服务器的上数据拷贝一份 放到其他的存储设备里

    恢复数据： 放到其他的存储设备里备份 ， 还原丢失的数据。

    ​

    **数据备份方式：**物理备份 、逻辑备份

    ​

    数据备份策略 ：

    ​ **完全备份 ：**备份所有数据：

    ​ 可以是一台数据库服务器上的所有数据

    ​ 也可以是 一个数据库下所有表

    ​ 还可以仅一张表里的所有记录

    **增量备份：**备份上次备份后，新产生的数据。

    • 1

    ​ **差异备份：**备份自完全备份后，新产生的数据。

    备份策略的使用方式：

    ​ 完全备份+增量备份

    ​ 完全备份+差异备份

    比如：每周的周一完全备份，每周的周二到周日做增量备份或差异备份
通过计划 执行备份脚本实现		
00 23 *  * 1     执行完全备份脚本
59 23 *  * 2-7   执行备份新数据的脚本

    • 1
    • 2
    • 3
    • 4

    2、完全备份与恢复

    2.1 物理备份及恢复

    （1）备份操作
    cp	-r	/var/lib/mysql	备份目录/mysql.bak
tar	-zcvf	/root/mysql.tar.gz	/var/lib/mysql/

    • 1
    • 2
    （2）恢复操作
    cp	-r	备份目录/mysql.bak	/var/lib/mysql	
tar	-zxvf	/root/mysql.tar.gz	-C	/var/lib/mysql/
chown	-R	mysql:mysql  /var/lib/mysql

    • 1
    • 2
    • 3

    例子：

    1.备份
[root@host50 ~]# systemctl  stop   mysqld 
[root@host50 ~]# mkdir  /bakdir
[root@host50 ~]# cp -r /var/lib/mysql  /bakdir/mysql.bak
[root@host50 ~]# ls /bakdir/
mysql.bak

[root@host50 ~]# cd /var/lib/mysql
[root@host50 ~]# tar -zcvf  /bakdir/mysql.tar.gz ./*
[root@host50 ~]# ls /bakdir/
mysql.bak    mysql.tar.gz

2.模拟数据丢失
[root@host50 ~]# rm -rf /var/lib/mysql/*

3.恢复数据
[root@host50 ~]# cp -r /bakdir/mysql.bak/* /var/lib/mysql/
[root@host50 ~]# chown -R mysql:mysql /var/lib/mysql
[root@host50 ~]# systemctl  start mysqld
[root@host50 ~]# mysql -uroot -p密码
mysql> show  databases;   能够看所有的库

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21

    2.2 mysqldump备份及恢复(逻辑备份与恢复)

    （1）完全备份
    mysqldump	-uroot	-p密码	库名	>	/目录/xxx.sql

    • 1

    库名的表示方式：

    ​ 库名 表名 #仅备份库下一张的所有记录

    ​ 库名 表名1 表名2 #一起备份库下2张表的所有记录

    ​ -B 库名 #仅备份1个库里的所有表

    ​ -B 库名1 库名2 #一起备份2个库里的所有表

    ​ -A 或 --all-databases #备份服务器上的所有库所有表

    （2）完全恢复
    mysql	-uroot	-p密码	库名	<	/目录/xxx.sql

    • 1

    注意：恢复库时库名可以省略；

    ​ 备份和恢复数据库服务必须时运行状态。

    ​ ！！！！使用表的备份文件恢复数据的时必须写库名，不然数据库服务不知道把数据恢复那个库里！！！！

    mysqldump的备份缺点：

    ​ 1 mysqldump 在备份数据和恢复数据的时候会锁表

    Unlock table; # 释放表

    ​ 2 使用 mysqldump备份命令生成备份文件恢复数据，只能把数据恢复备份时刻的数据。

    例子：

    备份数据例子

    # 存储备份文件的目录要事先创建
[root@host50 ~]# mysqldump -uroot -p密码  db1 t41 > /bakdir/db1_t41.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
 
[root@host50 ~]# mysqldump -uroot -p密码  -B db1 > /bakdir/db1.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

[root@host50 ~]# mysqldump -uroot -p密码  -B db1 mysql > /bakdir/twodb.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

[root@host50 ~]# mysqldump -uroot -p密码  -A > /bakdir/allbak.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

[root@host50 ~]# ls /bakdir/
allbak.sql  db1.sql  db1_t41.sql  db1_user.sql  mysql.bak  mysql.tar.gz  twodb.sql

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    恢复数据例子:

    mysql> delete from db1.t3;
Query OK, 22 rows affected (0.04 sec)

mysql> drop table db1.t3;
Query OK, 0 rows affected (0.15 sec)

mysql> exit

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    [root@host50 ~]# mysql -uroot -p123456 < /bakdir/db1_t3.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1046 (3D000) at line 22: No database selected	 #没写库名报错 使用表的备份文件恢复数据 必须写库名

[root@host50 ~]# mysql -uroot -p123456 db1 < /bakdir/db1_t3.sql
mysql: [Warning] Using a password on the command line interface can be insecure.


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    使用库的备份文件恢复数据
mysql> drop database db1;
Query OK, 10 rows affected (1.05 sec)
mysql> exit;

    • 1
    • 2
    • 3
    • 4
    [root@host50 ~]# mysql -uroot -p654321  < /bakdir/db1.sql    #使用库的备份文件恢复数据 不需要写库名
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@host50 ~]# 
[root@host50 ~]# mysql -uroot -p654321 
mysql> show  databases;
mysql> use  db1;
mysql> show tables;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    编写计划任务每周周一晚上23点备份 服务的所有数据 用系统日期做备份文件命令

    ]#crontab  -e
00  23  * *  1   mysqldump -uroot -p654321 -A  > /bakdir/`date +%F`_allbak.sql

    • 1
    • 2

    二、binlog日志

    1、日志介绍：

    		也叫二进制日志  
		是MySQL服务日志文件的一种
		记录在数据库服务器上执行的除查询之外的sql命令
		启用日志能够达到对数据做备份的目的
		搭建MySQL主从同步存储结构的必要条件
		默认MySQL服务没有启用binlog日志

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    2、启用日志

    配置项用途
    server_id=数字指定id值（1-255）
    log_bin启用binlog日志
    max_binlog_size=数值m指定日志文件容量，默认1G
    ]# vim /etc/my.cnf
[mysqld]
......
log_bin					# 启用binlog日志
server_id=数字		   # 指定id值

]# systemctl restart mysqld

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    binlog相关文件：

    /var/lib/mysql # 默认存放目录

    主机名-bin.index # 索引文件

    主机名-bin.000001 # 日志文件

    例子：

    （1）启用binlog日志

    启用数据库50主机的binlog日志文件							        
]#vim  /etc/my.cnf
[mysqld]
server_id = 50
log_bin
:wq
]# systemctl restart  mysqld
]# mysql  -uroot  -p123456


mysql> show  master status ;   查看binlog日志信息 
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| host50-bin.000001 |      154 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)


mysql> system  ls /var/lib/mysql/host50*     #binlog日志默认存储在数据库目录下 
/var/lib/mysql/host50-bin.000001  /var/lib/mysql/host50-bin.index（记录当前已有的binlog日志文件）
mysql>
mysql> system cat /var/lib/mysql/host50-bin.index
./host50-bin.000001
mysql>	

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25

    （2）自定义日志信息

    在50 主机 启用binlog日志时，指定日志文件存放的目录和日志文件命令

    ]# vim /etc/my.cnf				
[mysqld]
server_id =  50
#log_bin
log_bin=/mylog/db50				
：wq
[root@host50 ~]# mkdir /mylog
[root@host50 ~]# chown  mysql /mylog

[root@host50 ~]# setenforce  0
setenforce: SELinux is disabled

[root@host50 ~]# systemctl  restart mysqld

[root@host50 ~]# mysql  -uroot -p123456 -e 'show master status'
+-------------+----------+--------------+------------------+-------------------+
| File        | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------+----------+--------------+------------------+-------------------+
| db50.000001 |      154 |              |                  |                   |
+-------------+----------+--------------+------------------+-------------------+
[root@host50 ~]# 
[root@host50 ~]# ls /mylog/
db50.000001  db50.index
					
[root@host50 ~]# cat /mylog/db50.index 
/mylog/db50.000001
[root@host50 ~]# 	

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27

    创建新的binlog日志文件:

    默认情况下日志文件记录的sql文件容量大于 1G 的时候数据服务会自动创建新的日志文件; 文件编号顺延。

    （3）手动创建新的binlog日志文件——记录sql命令如下：

    方法1 ：

     systemctl restart  mysqld 	#重启服务会产生新的日志文件

    • 1

    方法2：

    创建新日志文件的个数个完全备份数据库的个数相同

例子
[root@host50 ~]# mysqldump  -uroot -p123456 --flush-logs  -B tarena  > /bakdir/tarena.sql

[root@host50 ~]# mysqldump  -uroot -p123456 --flush-logs  -B tarena   db1  > /bakdir/two.sql

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    方法3：

    
mysql> flush logs;		#刷新日志会产生新的日志文件	

    • 1
    • 2

    3、日志管理命令

    #查看正在使用binlog日志名和偏移量（记录sql命令的编号）
mysql>  show  master  status；

#查看数据库服务器当前已有全部 binlog日志文件
mysql> show binary  logs;

#删除编号之前的所有日志文件
mysql> purge master logs to "db50.000004";

#删除当前所有的日志文件重新创建新日志文件和索引文件
mysql> reset master ;


查看日志文件内容 2 种方法：
方法1 使用系统命令 mysqlbinlog 查看
[root@host50 ~]# mysqlbinlog   /mylog/db50.000001   
						
方法2 使用sql命令查看
mysql> show binlog events in "db50.000001" ;


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20

    **例子：**测试 binlog日志 记录的命令类型(日志 记录除查询之外的所有sql命令)

    mysql> reset master ; 
mysql> show  master status;
mysql> select  count(*) from  tarena.user;  # 查看日志名和偏移量
mysql> show  master status;
mysql> insert into  tarena.user(name) values("boba");
mysql> show  master status;  #查看日志名和偏移量
mysql> insert into  tarena.user(name) values("boba");
mysql> show  master status;  #查看日志名和偏移量
mysql> show binlog events in  "db50.000001";  


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    三、使用binlog日志恢复数据

    把查看到的文件内容管道给连接mysql服务的命令

    1、命令格式：

    命令格式1 : 查看文件全部内容，适用于恢复所有数据。

    ]# mysqlbinlog   /目录名/日志文件名   |   mysql  -uroot   -p密码

    • 1

    命令格式2 : 查看文件指定范围内容，适合恢复部分数据。

    ]# mysqlbinlog  选项   /目录名/日志文件名   |   mysql  -uroot   -p密码

    • 1

    选项说明：

    选项用途
    –start-position=开始偏移量起始偏移量
    –stop-position=结束偏移量结束偏移量
    –start-datetime=“yyyy/mm/dd hh:mm:ss”起始时间
    –stop-datetime=“yyyy/mm/dd hh:mm:ss”结束时间

    2、例子：

    2.1 命令格式1

    查看日志的所有内容恢复数据 （恢复所有数据）

    适用于日志文件里只记录了 insert 和 update 命令 没有delete 命令的情况下

    ]# mysqlbinlog   /目录名/日志文件名   |   mysql  -uroot   -p密码       

    • 1

    例子：

    1.完全备份 tarena 库,并在完成备份后创建新的日志文件 
[root@host50 ~]# mysqldump -uroot -p123456 --flush-logs -B tarena  > /bakdir/`date +%F`_tarena.sql
[root@host50 ~]# mysql -uroot -p123456 -e 'show master status'  查看新日志文件名
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+----------+--------------+------------------+-------------------+
| File        | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------+----------+--------------+------------------+-------------------+
| db50.000002 |      154 |              |                  |                   |
+-------------+----------+--------------+------------------+-------------------+
[root@host50 ~]#



2.完全备份后管理员root登陆继续插入新的记录
[root@host50 ~]# mysql -uroot -p123456 
mysql>  
insert into tarena.user(name,uid)values("a",1111);
insert into tarena.user(name,uid)values("aa",1111);
insert into tarena.user(name,uid)values("aaa",1111);
insert into tarena.user(name,uid)values("ab",1111);
insert into tarena.user(name,uid)values("ac",1111);
mysql> show  master   status;   #查看日志文件的偏移量发生了改变


3.让数据库服务器51用备份文件恢复数据 

--把日志文件拷贝给51
[root@host50 ~]# scp /mylog/db50.000002   192.168.4.51:/root/

--把备份文件拷贝给51    
[root@host50 ~]# scp /bakdir/2021-10-18_tarena.sql    192.168.4.51:/root/  


4.使用50的备份文件恢复，
[root@host51 ~]# mysql -uroot -p123456 -e 'show databases'   #查看已有的库  #51主机就是没有tarena库的（如果有的话删除库） ；


5.查看拷贝文件 在  51 本机的位置和名字
[root@host51 ~]# ls /root/*.sql
/root/2021-10-18_tarena.sql
[root@host51 ~]# ls /root/db50*
/root/db50.000002
[root@host51 ~]# mysql -uroot -p123456  < /root/2021-10-18_tarena.sql    #完全备份恢复
[root@host51 ~]# which  mysqlbinlog     # 查看读取日志内容的命令
[root@host51 ~]# mysqlbinlog  /root/db50.000002 |  mysql -uroot -p123456    #读日志内容恢复数据
[root@host51 ~]# mysql -uroot -p123456    # 管理员登录后可以看到库和表记录


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47

    2.2 命令格式2

    查看日志的部分内容恢复数据（恢复部分数据） 适用于 既有insert 又有delete的情况

    ]# mysqlbinlog  选项   /目录名/日志文件名   |   mysql  -uroot   -p密码

    • 1

    例子：

    **准备工作：**修改binlog日志文件的格式（格式就是日志文件记录sql命令的方式）

    1.查看日志文件的格式
mysql> show    variables  like    "binlog_format";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |  		#行格式看不到具体的sql命令，想看到具体的sql命令要修改日志格式。
+---------------+-------+
1 row in set (0.00 sec)

2.修改日志文件的格式
host50]# vim /etc/my.cnf
   [mysqld]
    binlog_format="mixed"   #自己添加 其他行不需要动
:wq
[root@host50 ~]# systemctl  restart mysqld
mysql> show variables like "binlog_format";  #查看日志格式
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show master status;  #查看记录sql命令使用的日志文件
+-------------+----------+--------------+------------------+-------------------+
| File        | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------+----------+--------------+------------------+-------------------+
| db50.000003 |      154 |              |                  |                   |
+-------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)



3.执行对数据库做 select   insert  update  delete 命令

select  count(*)  from  tarena.user;
show master status; 
insert into  tarena.user(name,uid) values("FFF",7777);
insert into  tarena.user(name,uid) values("AFF",7777);
insert into  tarena.user(name,uid) values("CFF",7777);
insert into  tarena.user(name,uid) values("WFF",7777);
insert into  tarena.user(name,uid) values("GFF",7777);
select  count(*)  from  tarena.user;

mysql> show master status;
+-------------+----------+--------------+------------------+-------------------+
| File        | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------+----------+--------------+------------------+-------------------+
| db50.000003 |     1804 |              |                  |                   |
+-------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> delete from tarena.user where uid = 7777;
mysql> show master status;
+-------------+----------+--------------+------------------+-------------------+
| File        | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------+----------+--------------+------------------+-------------------+
| db50.000003 |     2089 |              |                  |                   |
+-------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> 
insert into  tarena.user(name,uid) values("wwww",7777);
insert into  tarena.user(name,uid) values("AAAwww",7777);
mysql> show master status;
+-------------+----------+--------------+------------------+-------------------+
| File        | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------+----------+--------------+------------------+-------------------+
| db50.000003 |     2753 |              |                  |                   |
+-------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70

    通过 指定偏移量范围恢复数据

    –start-position=偏移量

    –stop-position=偏移量

    1.查看执行命令的偏移量范围
mysql> show binlog events in "db50.000003";  


2.只读取范围内的命令管道给 连接数据库服务命令执行
[root@host50 ~]# mysqlbinlog --start-position=658 --stop-position=2794 /mylog/db50.000003 | mysql -uroot -p654321
mysql: [Warning] Using a password on the command line interface can be insecure.

[root@host50 ~]# mysql -uroot -p123456 -e 'select  * from tarena.user'




3.通过指定时间范围恢复数据
--start-datetime="yyyy/mm/dd  hh:mm:ss"   --stop-datetime="yyyy/mm/dd  hh:mm:ss"



4.删除数据
]# mysql -uroot -p123456 -e 'delete from  tarena.user where name="wwww"' 



]#  mysqlbinlog    /目录名/日志文件名    #即可以看到命令执行的时间又可以命令执行的偏移量
host50]#  mysqlbinlog  /mylog/db50.000003

5.查看日志恢复数据
[root@host50 ~]# mysqlbinlog  --start-datetime="2021/12/15 11:04:42"
--stop-datetime="2021/12/15 11:04:50"  /mylog/db50.000003 | mysql -uroot  -p123456

6.查看数据
[root@host50 ~]# mysql -uroot -p123456 -e  'select * from  tarena.user where name="wwww"' 


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33

    四、innobackupex

    1、概述

    1.1 软件介绍

    使用第三方软件percona提供的备份命令innobackupex 对数据做备份和恢复;

    **特点:**在线热备不锁表， 适合生产环境下备份业务。

    1.2 安装软件

    ]#yum -y install libev-4.15-1.el6.rf.x86_64.rpm    
]#yum -y install percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm
]#which  innobackupex
/usr/bin/innobackupex


    • 1
    • 2
    • 3
    • 4
    • 5

    2、完全备份与恢复

    2.1 完全备份

    ]#innobackupex  -uroot -p密码  /备份目录名  --no-timestamp

--no-timestamp	 可选项，不用系统的日期做存放备份文件的目录名

    • 1
    • 2
    • 3

    2.2 完全恢复

    第1步 准备恢复数据
]#innobackupex    --apply-log  /备份目录名 

第2步 拷贝数据
]#innobackupex    --copy-back  /备份目录名 

    • 1
    • 2
    • 3
    • 4
    • 5
    恢复数据的操作步骤：

    ​ 1） 停止数据库服务

    ​ 2） 清空数据库目录

    ​ 3） 准备恢复数据 --apply-log

    ​ 4） 拷贝数据 --copy-back

    ​ 5） 修改数据库目录的所有者者组用户为mysql

    ​ 6） 启动数据库服务

    ​ 7） 管理员root用户登录服务查看数据

    例子：

    1.对数据做完全备份

    [root@host51 ~]# innobackupex -uroot -p123456  /allbak  --no-timestamp

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

    • 1
    • 2
    • 3
    • 4
    • 5

    2.模拟数据误删除

    mysql>  drop database tarena ; drop database  mysql;
mysql> exit ;

    • 1
    • 2

    3.恢复数据

    操作步骤：
1）停止数据库服务
[root@host51 ~]# systemctl   stop   mysqld						
2）清空数据库目录
[root@host51 ~]# rm  -rf /var/lib/mysql/*
3）准备恢复数据
[root@host51 ~]# innobackupex  --apply-log /allbak/			
4）拷贝数据
[root@host51 ~]# innobackupex  --copy-back /allbak/			
5）修改数据库目录的所有者者组用户为mysql
[root@host51 ~]# chown  -R mysql:mysql /var/lib/mysql					
6）启动数据库服务
[root@host51 ~]# systemctl   start  mysqld					
7）管理员root用户登录服务查看数据
[root@host51 ~]# mysql -uroot -p123456 -e 'show databases'

-e 'show databases'   #在终端运行sql语句
system ls /var/lib/mysql	# 在mysql运行终端命令

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18

    3、恢复单张表

    例子：（在例子中学习）

    ​ 发生的情况：

    [root@host51 ~]# mysql -uroot -p123456
mysql> delete from tarena.user;		
mysql> select * from tarena.user;

    • 1
    • 2
    • 3

    3.1操作步骤 ：

    （1）删除表空间 (表名.ibd)

    用来存储表记录的文件 select * from 表;

    删除表空间就是把数据库目录下没有记录的表名.idb文件删除

    1.格式：
mysql> ALTER  TABLE  库名.表名  DISCARD  TABLESPACE;


#例子：
mysql> alter  table  tarena.user discard tablespace;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    （2) 导出表信息(生成备份目录下备份数据的信息文件)
    2.格式
innobackupex   --apply-log   --export    数据完全备份目录 


#例子：
host51~]# innobackupex  --apply-log --export  /allbak

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    （3）拷贝表信息文件到数据库目录下
    3.格式
cp  /备份目录/库名/表名.{ibd,cfg,exp}  /var/lib/mysql/库名


#例子：
[root@host51 db51]# cp /allbak/tarena/user.ibd /var/lib/mysql/tarena/
[root@host51 db51]# cp /allbak/tarena/user.cfg /var/lib/mysql/tarena/
[root@host51 db51]# cp /allbak/tarena/user.exp /var/lib/mysql/tarena/
或
[root@host51 db51]# cp /allbak/tarena/user.{ibd,cfg,exp} /var/lib/mysql/tarena/

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    （4）修改表信息文件的所有者及组用户为mysql
    4.格式：
chown mysql:mysql  /var/lib/mysql/库名


#例子：
[root@host51 db51]# chown mysql:mysql /var/lib/mysql/tarena/user.*			

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    （5）导入表空间
    5.格式
mysql> ALTER TABLE 库名.表名  IMPORT TABLESPACE;  


#例子：
mysql> alter table tarena.user import tablespace;	# 就是把.cfg .exp文件导入ib_logfile0 中		

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    （6） 删除数据库目录下的表信息文件
    6.格式
rm -rf 数据库目录/库名/表名.{cfg,exp}


#例子：
[root@host51 db51]# rm -rf /var/lib/mysql/tarena/user.cfg 
[root@host51 db51]# rm -rf /var/lib/mysql/tarena/user.exp 	

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    （7）查看表记录
    7. mysql> select * from  库名.表名

mysql> select * from tarena.user;	

    • 1
    • 2
    • 3

    4、增量备份与恢复

    **说明：**增量备份是备份上次备份后新产生的数据；

    所以在执行增量备份之前，必须得现有一次备份，不然的话无法得知那些数据是新数据；

    通常增量备份之前的备份，就应该是完全备份 。

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-I5KiSH6T-1663058876087)(/root/文档/12 RDBMS/图片/3.png)]

    4.1增量备份数据的命令格式

    --incremental  			# 备份新数据并定义新数据存放的目录名 
--incremental-basedir  	# 备份新数据参考的备份目录名
		
]#innobackupex   -uroot   -p密码   --incremental   /新数据存放目录                 --incremental-basedir=/备份数据的参考目录   --no-timestamp 

    • 1
    • 2
    • 3
    • 4

    4.2增量恢复数据的命令格式

    1.准备恢复数据
]#innobackupex   --apply-log   --redo-only   /首次备份目录名 

2.合并数据 合并的次数与增量备份的顺序要一致 
]#innobackupex   --apply-log   --redo-only   /首次备份目录名   --incremental-dir=/目录名 						
//--incremental-dir	增量备份数据存放的目录名

3.拷贝数据
]#innobackupex   --copy-back /完全备份目录


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    4.3使用增量备份文件恢复数据的具体操作步骤：

    1）停止数据库服务

2）清空数据库目录

3）准备恢复数据

4）合并数据 （合并的次数要增量备份的次数一样 并且合并的顺序也有与增量备份的顺序一致）

5）恢复数据

6）修改数据库目录的所有者和组用户为mysql

7）启动服务

8）查看数据

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    增量备份例子：

    1.周一 执行完全备份
[root@host51 ~]# innobackupex -uroot -p123456  /fullbak  --no-timestamp

[root@host51 ~]# ls /fullbak/
backup-my.cnf  ib_buffer_pool  mysql               sys                     xtrabackup_info
db51           ibdata1         performance_schema  xtrabackup_checkpoints  xtrabackup_logfile


向表里添加新数据 （多执行几遍）
mysql> insert into tarena.user(name,shell) values("fff","fff"); 


2.周二  执行增量备份
[root@host51 ~]# innobackupex -uroot -p123456  --incremental  /new1dir  --incremental-basedir=/fullbak  --no-timestamp
查看备份目录 列表
[root@host51 ~]# ls /new1dir/  
backup-my.cnf   ibdata1.delta  performance_schema      xtrabackup_info
db51            ibdata1.meta   sys                     xtrabackup_logfile
ib_buffer_pool  mysql          xtrabackup_checkpoints

向表里添加新数据  多执行几遍 
mysql> insert into tarena.user(name,shell)  values("zzz","zzz"); 



说明: 如果周二做的是差异备份的话 命令这样写   		# 差异备份都是以第一天的完全备份为依据的
[root@host51 ~]# innobackupex -uroot -p123456  --incremental  /new1dir -incremental-basedir=/fullbak --no-timestamp

3.周三  执行增量备份
[root@host51 ~]# innobackupex -uroot -p123456  --incremental  /new2dir  --incremental-basedir=/new1dir  --no-timestamp


[root@host51 ~]# ls /new2dir/  查看备份目录 列表
backup-my.cnf   ibdata1.delta  performance_schema      xtrabackup_info
db51            ibdata1.meta   sys                     xtrabackup_logfile
ib_buffer_pool  mysql          xtrabackup_checkpoints
[root@host51 ~]# 

向表里添加新数据  mysql>  （多执行几遍）

说明: 如果周三做的是差异备份的话 命令这样写
[root@host51 ~]# innobackupex -uroot -p123456  --incremental  /new2dir --incremental-basedir=/fullbak --no-timestamp


4.周四  执行增量备份
[root@host51 ~]# innobackupex -uroot -p123456  --incremental  /new3dir
--incremental-basedir=/new2dir --no-timestamp

[root@host51 ~]# ls /new3dir/  查看备份目录 列表
backup-my.cnf   ibdata1.delta  performance_schema      xtrabackup_info
db51            ibdata1.meta   sys                     xtrabackup_logfile
ib_buffer_pool  mysql          xtrabackup_checkpoints
[root@host51 ~]# 

说明: 如果周四做的是差异备份的话 命令这样写
[root@host51 ~]# innobackupex -uroot -p123456  --incremental  /new3dir  --cremental-basedir=/fullbak --no-timestamp

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56

    模拟数据丢失

    [root@host51 ~]# mysql -uroot -p123456  -e 'drop database tarena'
[root@host51 ~]# mysql -uroot -p123456  -e 'drop database mysql'

    • 1
    • 2

    增量恢复 （使用增量备份文件恢复数据）

    具体操作步骤：（host51 主机使用自己的增量备份文件恢复数据）
1）停止数据库服务  
[root@host51 ~]# systemctl  stop mysqld

2）清空数据库目录 
[root@host51 ~]# rm  -rf /var/lib/mysql/*

3）准备恢复数据 (告诉程序innobackupex从什么位置开始合并数据)
[root@host51 ~]# innobackupex --apply-log --redo-only /fullbak/
					
4）合并数据 （合并的次数要增量备份的次数一样 并且合并的顺序也有与增量备份的顺序一致）

合并第1次增量备份数据
[root@host51 ~]# innobackupex  --apply-log  --redo-only  /fullbak/	             --incremental-dir=/new1dir   
合并第2次增量备份数据
[root@host51 ~]# innobackupex --apply-log --redo-only /fullbak/	--incremental-dir=/new2dir   
合并第3次增量备份数据
[root@host51 ~]# innobackupex --apply-log --redo-only /fullbak/  --incremental-dir=/new3dir  

5）恢复数据  (把合并后的数据拷贝到数据库目录下)
[root@host51 ~]# innobackupex --copy-back /fullbak

6） 修改数据库目录的所有者和组用户为mysql
[root@host51 ~]# chown -R mysql:mysql /var/lib/mysql

7)  启动服务				
[root@host51 ~]# systemctl  start mysqld

8）查看数据 
[root@host51 ~]# mysql -uroot -p123456 -e 'show databases'	

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30

    作步骤：（host51 主机使用自己的增量备份文件恢复数据）
    1）停止数据库服务
    [root@host51 ~]# systemctl stop mysqld

    2）清空数据库目录
    [root@host51 ~]# rm -rf /var/lib/mysql/*

    3）准备恢复数据 (告诉程序innobackupex从什么位置开始合并数据)
    [root@host51 ~]# innobackupex --apply-log --redo-only /fullbak/

    4）合并数据 （合并的次数要增量备份的次数一样 并且合并的顺序也有与增量备份的顺序一致）

    合并第1次增量备份数据
    [root@host51 ~]# innobackupex --apply-log --redo-only /fullbak/ --incremental-dir=/new1dir
    合并第2次增量备份数据
    [root@host51 ~]# innobackupex --apply-log --redo-only /fullbak/ --incremental-dir=/new2dir
    合并第3次增量备份数据
    [root@host51 ~]# innobackupex --apply-log --redo-only /fullbak/ --incremental-dir=/new3dir

    5）恢复数据 (把合并后的数据拷贝到数据库目录下)
    [root@host51 ~]# innobackupex --copy-back /fullbak

    6） 修改数据库目录的所有者和组用户为mysql
    [root@host51 ~]# chown -R mysql:mysql /var/lib/mysql

    1. 启动服务
      [root@host51 ~]# systemctl start mysqld

    8）查看数据
    [root@host51 ~]# mysql -uroot -p123456 -e ‘show databases’

    • 相关阅读:
      C# CAD二次开发通过代码模拟 人工 画齿轮的算法思路
      matlab相机标定求得相机内参
      k8s Pod简介与探针实现零宕机发布
      《Fundamantals of Software Architecture》 Q&A Part2
      音频基础知识
      vulnhub靶机DC1
      PMP证书续证流程
      鼠标键盘自动化工具pyautogui
      对抗生成网络（GAN）中的损失函数
      用深度强化学习来玩Flappy Bird
    • 原文地址：https://blog.csdn.net/weixin_56619848/article/details/126836758