0%

数据库备份管理:逻辑和物理备份

数据库备份管理:逻辑和物理备份

  • [一.备份的原因]
  • [二.备份的类型]
  • [三.备份的方式]
  • [四.备份策略]
  • [五.备份工具]

备份的原因

1)备份就是为了恢复。
2)尽量减少数据的丢失(公司的损失)

备份的类型

冷备份:
这些备份在用户不能访问数据时进行,因此无法读取或修改数据。这些脱机备份会阻止执行任何使用数据的活动。这些类型的备份不会干扰正常运行的系统的性能。但是,对于某些应用程序,会无法接受必须在一段较长的时间里锁定或完全阻止用户访问数据。

温备份:
这些备份在读取数据时进行,但在多数情况下,在进行备份时不能修改数据本身。这种中途备份类型的优点是不必完全锁定最终用户。但是,其不足之处在于无法在进行备份时修改数据集,这可能使这种类型的备份不适用于某些应用程序。在备份过程中无法修改数据可能产生性能问题。

热备份:
这些动态备份在读取或修改数据的过程中进行,很少中断或者不中断传输或处理数据的功能。使用热备份时,系统仍可供读取和修改数据的操作访问。

备份方式

1.逻辑备份

  • binlog
  • mysqldump
  • into outfile
  • replication
  • mysqlbinlog

2.物理备份

  • cp data
  • Xtrabackup(percona公司)

备份策略

1.全量备份

2.增量备份

3.差异备份

逻辑备份工具-mysqldump

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
mysqldump
-u:指定用户
-p:指定密码
-h:指定主机域
-P:指定端口
-S:指定socket文件

#-A(--all-databases):全库备份
[root@db01 mysql]# mysqldump -uroot -p1 -A > /tmp/full.sql

#-B:指定库备份(可以备份多个库)
[root@db01 mysql]# mysqldump -uroot -p1 -B world > /tmp/world.sql
[root@db01 mysql]# mysqldump -uroot -p1 -B world test > /tmp/world_test.sql

#什么都不加:备份表(只能备份表,不能备份多个库)
[root@db01 mysql]# mysqldump -uroot -p1 world > /tmp/world_1.sql
#单表备份
[root@db01 mysql]# mysqldump -uroot -p1 world city > /tmp/world_2.sql

#-F (--flush-logs):备份的同时刷新binlog 有多少个库,刷新多少个binlog
[root@db01 mysql]# mysqldump -uroot -p1 -A -F > /tmp/full_F.sql

#--master-data=2 (打点备份,温备)
2:打点备份并且注释change master to
1:打点备份不注释change master to
0:相当于不开启,等于没写

#--single-transaction(快照备份)
[root@db01 ~]# mysqldump -uroot -p1 -A --master-data=2 --single-transaction > /tmp/f.sql

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000030', MASTER_LOG_POS=20550;
mysqlbinlog --start-position=20550 --stop-position=25058

#-R:备份存储过程和函数
[root@db01 ~]# mysqldump -uroot -p1 -A -R --master-data=2 --single-transaction > /tmp/f.sql

#--triggers:备份触发器
[root@db01 ~]# mysqldump -uroot -p1 -A -R --triggers --master-data=2 --single-transaction > /tmp/f.sql

#gzip 压缩备份
#终极备份语句
[root@db01 ~]# mysqldump -uroot -p1 -A -R --triggers --master-data=2 --single-transaction|gzip > /tmp/full_$(date +%F).sql.gz

####了解即可
#-d:仅备份表结构
#-t:仅备份数据
#-x:锁表备份

#恢复压缩数据
[root@db01 tmp]# zcat /tmp/full_2019-11-13.sql.gz | mysql -uroot -p1

注意:

1)mysqldump在备份和恢复时都需要MySQL实例启动为前提
2)一般数据量级100G以内,大约15-30分钟可以恢复(PB、EB就需要考虑别的方式:物理备份)
3)mysqldump是以覆盖的形式恢复数据的

企业故障恢复案例

背景:
正在运行的网站系统,MySQL数据库,数据量25G,日业务增量10-15M。

备份策略:
每天23:00,计划任务调用mysqldump执行全备脚本

故障时间点:
上午10点开发人员误删除一个核心业务表,如何恢复?

思路:

1.停库,停止业务(避免二次伤害数据)

1
[root@db01 tmp]# /etc/init.d/mysqld stop

2.准备新环境

  • 多实例(物理服务器)
  • 再买一台云主机

3.将昨天晚上23点做的全备导入新环境

1
zcat full_2019-11-12.sql |mysql -uroot -p1

4.根据binlog截取昨晚23点到今天上午10点的数据

1
2
3
4
[root@db01 tmp]# zcat /tmp/full_2019-11-13.sql|head -25
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000030', MASTER_LOG_POS=1010290;

mysqlbinlog --start-position=1010290 --stop-position=xxx /application/mysql/data/mysql-bin.000030 > /tmp/inc_23_10.sql

5.将截取出来的数据发送到新环境

1
scp /tmp/inc_23_10.sql 172.16.1.52:/tmp

6.导入新增数据

1
2
set sql_log_bin=0
mysql -uroot -p1 < /tmp/inc_23_10.sql

7.恢复生产业务

  • 应用割接(修改连接数据库的代码)

  • 导出核心业务表,恢复到旧生产环境

    1
    2
    3
    mysqldump -uroot -p1 db1 tb1 > /tmp/tb1.sql
    scp /tmp/tb1.sql 172.16.1.51:/tmp
    mysql -uroot -p1 db1 < /tmp/tb1.sql

以上两种方式都可以,选择速度最快的一种方式

改代码:

1).开发提交gitlab,从gitlab拉取,上线

2).开发写好了配置文件

8.开启业务

1
/etc/init.d/mysqld start

故障模拟恢复

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql -uroot -p1

mysql> create database backup;

mysql> use backup

mysql> create table full select * from world.city;

mysql> create table full_1 select * from world.city;


mysql> show tables;
+------------------+
| Tables_in_backup |
+------------------+
| full |
| full_1 |
+------------------+

到时候了:23:00全备

1
[root@db01 mysql]# mysqldump -uroot -p1 -A -R --triggers --master-data=2 --single-transaction|gzip > /tmp/full_$(date +%F).sql.gz

模拟数据变化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
[root@db01 mysql]# mysql -uroot -p1

mysql> use backup

mysql> create table inc select * from mysql.user;

mysql> create table inc_1 select * from mysql.user;

mysql> show tables;
+------------------+
| Tables_in_backup |
+------------------+
| full |
| full_1 |
| inc |
| inc_1 |
+------------------+

mysql> insert into full_1 select * from world.city;
mysql> commit;

模拟删除核心业务表

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
mysql> drop table backup.full_1;

mysql> show tables;
+------------------+
| Tables_in_backup |
+------------------+
| full |
| inc |
| inc_1 |
+------------------+

mysql> drop database backup;

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| OD |
| QLS |
| ZLS |
| ageofoldboy |
| binlog |
| blog |
| db1 |
| db2 |
| linux5 |
| mysql |
| mysqldump |
| od |
| performance_schema |
| qwe |
| test |
| test1 |
| test2 |
| test3 |
| wordpress |
| world |
| xxx |
| zls1 |
| zls2 |
+--------------------+

恢复数据

mysqlbinlog –base64-output=decode-rows -vvv /application/mysql/data/mysql-bin.000032 |grep -i -B 5 ‘drop database shell’|sed -rn ‘s#.at (.)#\1#gp’

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
71
72
73
74
75
76
77
78
79
80
81
82
83
#1.停业务
#停止连接数据库的服务(php,tomcat,python)*****
[root@db01 mysql]# /etc/init.d/mysqld stop
Shutting down MySQL. SUCCESS!

#2.准备新的环境(初始化一个新的数据库实例,二进制安装MySQL)
[root@db02 scripts]# ./mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/application/mysql/data
[root@db02 scripts]# /etc/init.d/mysqld start
. SUCCESS!

[root@db02 mysql]# mysql
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+

#3.将全备的数据导入新环境
[root@db01 mysql]# scp /tmp/full_2019-11-14.sql.gz 172.16.1.52:/tmp/
[root@db02 mysql]# zcat /tmp/full_2019-11-14.sql.gz |mysql
mysql> show tables from backup;
+------------------+
| Tables_in_backup |
+------------------+
| full |
| full_1 |
+------------------+

mysql> select count(*) from backup.full_1;
+----------+
| count(*) |
+----------+
| 4079 |
+----------+

#4.截取binlog
[root@db02 mysql]# zcat /tmp/full_2019-11-14.sql.gz |head -25
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000031', MASTER_LOG_POS=268002;

[root@db01 data]# mysqlbinlog --base64-output=decode-rows -vvv /application/mysql/data/mysql-bin.000031|less
#搜索DROP
410758

[root@db01 data]# mysqlbinlog --start-position=268002 --stop-position=410758 /application/mysql/data/mysql-bin.000031 > /tmp/inc23_10.sql

#5.发送并导入sql文件
[root@db01 data]# scp /tmp/inc23_10.sql 172.16.1.52:/tmp/
[root@db02 mysql]# mysql < /tmp/inc23_10.sql

mysql> use backup

mysql> show tables;
+------------------+
| Tables_in_backup |
+------------------+
| full |
| full_1 |
| inc |
| inc_1 |
+------------------+

mysql> select count(*) from full_1;
+----------+
| count(*) |
+----------+
| 8158 |
+----------+

#6.恢复生产业务
- 1.修改代码
- 2.导出核心业务表,恢复到生产库

[root@db02 mysql]# mysqldump -B backup > /tmp/backup.sql
[root@db02 mysql]# scp /tmp/backup.sql 172.16.1.51:/tmp/

[root@db01 data]# /etc/init.d/mysqld start
[root@db01 data]# mysql -uroot -p1 < /tmp/backup.sql
#7.启动服务
tomcat php ...

结合:mysqldump全备+binlog增备 恢复数据。

物理备份-Xtrabackup

1
[root@db01 ~]# yum localinstall percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm -y

1)对于非innodb表(比如myisam)是直接锁表cp数据文件,属于一种温备。
2)对于innodb的表(支持事务),不锁表,cp数据页最终以数据文件方式保存下来,并且把redo和undo一并备走,属于热备方式。
3)备份时读取配置文件/etc/my.cnf

全量备份

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
[root@db01 ~]# mkdir /backup
[root@db01 ~]# innobackupex --user=root --password=1 /backup
completed OK!
#记录binlog名字和位置点
-rw-r----- 1 root root 21 Nov 15 00:29 xtrabackup_binlog_info
#
-rw-r----- 1 root root 119 Nov 15 00:29 xtrabackup_checkpoints
#备份类型(全备)
backup_type = full-backuped
#从哪个日志版本号开始
from_lsn = 0
#备份到哪个日志版本号
to_lsn = 1025046494
#最新的日志版本号
last_lsn = 1025046494
compact = 0
recover_binlog_info = 0

-rw-r----- 1 root root 466 Nov 15 00:29 xtrabackup_info
-rw-r----- 1 root root 2560 Nov 15 00:29 xtrabackup_logfile

恢复数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
#1.先停库
/etc/init.d/mysqld stop

#2.清空data目录或者备份data目录
[root@db01 mysql]# mv /application/mysql/data/ /tmp/

#3.先做redo和undo 模拟CSR
[root@db01 mysql]# innobackupex --user=root --password=1 --apply-log /backup/2019-11-15_00-35-57

#4.恢复data 在my.cnf里面必须有datadir
[root@db01 mysql]# innobackupex --copy-back /backup/2019-11-15_00-35-57

#5.授权
[root@db01 mysql]# chown -R mysql.mysql /application/mysql/data

增量备份

备份方式

1)基于上一次备份进行增量
2)增量备份无法单独恢复,必须基于全备进行恢复
3)所有增量必须要按顺序合并到全备当中

1
2
3
4
5
6
7
8
9
10
[root@db01 ~]# innobackupex --user=root --password=1 --no-timestamp /backup/full

#1.第一次增量备份
[root@db01 backup]# innobackupex --user=root --password=1 --no-timestamp --incremental --incremental-basedir=/backup/full /backup/inc1

#2.第二次增量备份
[root@db01 backup]# innobackupex --user=root --password=1 --no-timestamp --incremental --incremental-basedir=/backup/inc1 /backup/inc2

#3.第三次增量备份
[root@db01 backup]# innobackupex --user=root --password=1 --no-timestamp --incremental --incremental-basedir=/backup/inc2 /backup/inc3

恢复数据

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
1.增量备份无法单独恢复,必须基于全备进行恢复
2.所有增量必须要按顺序合并到全备当中
3.分步骤进行--apply-log

full + inc1 + inc2 + inc3
1)给全备,只做redo,不做undo
[root@db01 backup]# innobackupex --apply-log --redo-only /backup/full/
backup_type = log-applied
from_lsn = 0
to_lsn = 1025119769

2)将inc1合并到full中 只做redo 不做undo
[root@db01 backup]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc1 /backup/full/
from_lsn = 0
to_lsn = 1025140919

3)将inc2合并到full中 只做redo 不做undo
[root@db01 backup]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc2 /backup/full/

4)将inc3合并到full中 redo 和 undo 都做
[root@db01 backup]# innobackupex --apply-log --incremental-dir=/backup/inc3 /backup/full/
backup_type = full-prepared
from_lsn = 0
to_lsn = 1025168949

5)将整个full目录模拟一次CSR
[root@db01 backup]# innobackupex --apply-log /backup/full/

6)copy back
[root@db01 backup]# rm -fr /application/mysql/data/*
[root@db01 backup]# innobackupex --copy-back /backup/full/
[root@db01 backup]# chown -R mysql.mysql /application/mysql/data/

差异备份

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
[root@db01 backup]# innobackupex --user=root --password=1 --no-timestamp /backup/full_1
backup_type = full-backuped
from_lsn = 0
to_lsn = 1025176220

#1.第一次差异备份
[root@db01 backup]# innobackupex --user=root --password=1 --no-timestamp --incremental --incremental-basedir=/backup/full_1 /backup/chayi1
[root@db01 backup]# cat chayi1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 1025176220
to_lsn = 1025202474

#2.第二次差异备份
[root@db01 backup]# innobackupex --user=root --password=1 --no-timestamp --incremental --incremental-basedir=/backup/full_1 /backup/chayi2
backup_type = incremental
from_lsn = 1025176220
to_lsn = 1025208985

#3.第三次差异备份
[root@db01 backup]# innobackupex --user=root --password=1 --no-timestamp --incremental --incremental-basedir=/backup/full_1 /backup/chayi3

#4.第四次差异备份
[root@db01 backup]# innobackupex --user=root --password=1 --no-timestamp --incremental --incremental-basedir=/backup/full_1 /backup/chayi4
backup_type = incremental
from_lsn = 1025176220
to_lsn = 1025234244

恢复数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
#1.停数据库
[root@db01 backup]# /etc/init.d/mysqld stop

#2.删除data目录或者备份
[root@db01 backup]# rm -fr /application/mysql/data/*

#3.模拟CSR合并数据
full_1 + chayi1 + chayi2 + chayi3 + chayi4
1)full_1只做redo 不做undo
[root@db01 backup]# innobackupex --apply-log --redo-only /backup/full_1/
2)将chayi4合并到full_1 redo undo 都做
[root@db01 backup]# innobackupex --apply-log --incremental-dir=/backup/chayi4 /backup/full_1/
3)将full_1 redo undo 都做
[root@db01 backup]# innobackupex --apply-log /backup/full_1/

#4.copy back
[root@db01 backup]# innobackupex --copy-back /backup/full_1/
[root@db01 backup]# chown -R mysql.mysql /application/mysql/data/
1
2
3
4
5
6
#!/bin/bash

while true;do
mysql -uroot -p1 -e 'insert into mysqldump.mysqldump values(1);commit;'
sleep 2
done

优点:

1.备份的时候,方便

2.恢复的时候,也方便

缺点:

重复数据多,占用磁盘空间大