资料备份型别
按业务划分：可分为完全备份，增量备份，差异备份。
完全备份：就是对整个资料库的资料和资料结构进行备份，好处是很直观，容易被人理解。不足之处：由于每天都对系统进行完全备份，因此在备份资料中大量是重复的，这些重复资料占用空间，增加成本，备份量大，所需时间长。
增量备份：就是每次备份的资料只是相当于上一次备份后增加和修改过的资料。优点：没有重复的资料，节省空间，缩短备份时间。缺点：发生灾难时，恢复资料麻烦。另外这种备份的可靠性也差，在这种备份下，各备份资料间的关系就像链子一样，一环套一环，其中任何一个备份资料出了问题都会导致整条链子脱节。
差异备份：就是每次备份的资料是相对于上一次全备份之后新增加的和修改过的资料。
资料备份方式：可分为热备，温备，冷备。
热备份：是指在资料库执行中直接备份，对正在执行的资料库没有任何影响。
冷备份：是指在资料库停止的情况下进行的备份，这种备份最为简单，一般只需要拷贝相关的资料库物理档案即可。
温备份：备份同样是在资料库执行时进行，但是会对当前资料库的操作有所影响，例如加一个全域性读锁以保证备份资料的一致性。
备份流程图
mysql 备份工具介绍
mysqldump: 逻辑备份工具，适用于所有储存引擎，可用于温备，能实现完全备份，部分备份，对 IonoDB 储存引擎支援热备；
cp,tar 等档案系统工具：物理备份工具，适用于所有储存引擎，用于冷备，能实现完全备份，部分备份；
lvm2 的快照：备几乎热，借助于档案系统工具实现物理备份。
mysqlhotcopy: 几乎冷备，仅适用于 myisam 储存引擎；
mysqldump+binlog 实现资料库的备份与恢复
1. 准备备份目录
1
[root@centos7 ~]# mkdir -p /backup/binlog
2. 准备备份的资料库及表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
MariaDB [(none)]> create database datas;
Query OK, 1 row affected (0.05 sec)
MariaDB [(none)]> use datas;
Database changed
MariaDB [datas]> create table tb1 (id int,name char(20));
Query OK, 0 rows affected (0.41 sec)
MariaDB [datas]> desc tb1;
+——-+———-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——-+———-+——+—–+———+——-+
| id | int(11) | YES | | NULL | |
| name | char(20) | YES | | NULL | |
+——-+———-+——+—–+———+——-+
MariaDB [datas]> insert into tb1 values (11,’haha’)
-> ;
Query OK, 1 row affected (0.03 sec)
MariaDB [datas]> select * from tb1;
+——+——+
| id | name |
+——+——+
| 11 | haha |
+——+——+
1 row in set (0.00 sec)
3. 对资料库进行完整备份
1
2
3
4
5
6
7
[root@centos7 ~]# mysqldump –all-databases –lock-all-tables –flush-log –master-data=2 > /backup/`date +%F_%T`-all.sql
–lock-all-tables: 锁定所有表
–flush-logs: 锁定表之后执行 flush logs 命令
–master-data={0|1|2}
0: 不记录
1：记录 change master to 语句；此语句未被注释
2：记录为注释语句
4. 向表中插入资料
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
MariaDB [datas]> show master status;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000007 | 245 | | |
+——————+———-+————–+——————+
1 row in set (0.00 sec)
MariaDB [datas]> insert into tb1 values (12,’hehe’),(13,’yaya’);
Query OK, 2 rows affected (0.23 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [datas]> select * from tb1;
+——+——+
| id | name |
+——+——+
| 11 | haha |
| 12 | hehe |
| 13 | yaya |
+——+——+
3 rows in set (0.00 sec)
5. 进行增量备份，备份二进位制日志
1
2
3
4
[root@centos7 mysql]# mysqlbinlog –start-position=245 –stop-position=451 mysql-bin.000008> /backup/binlog/`date +%F_%T`.sql
[root@centos7 mysql]# cd /backup/binlog/
[root@centos7 binlog]# ls
2017-12-03_16:43:29.sql
6. 继续插入资料，在没备份的情况下删除资料库，模拟误操作。
1
2
3
4
5
MariaDB [datas]> insert into tb1 values (16,’yuyu’);
Query OK, 1 row affected (0.00 sec)
MariaDB [datas]> drop database datas;
Query OK, 1 row affected (0.09 sec)
7. 恢复资料，由于在最后没有备份就删除了资料库，所以我们首先需要保护最后的二进位制日志，检视删除之前的 position 值。
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
[root@centos7 binlog]# cd /var/lib/mysql/
[root@centos7 mysql]# mysqlbinlog mysql-bin.000008
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#171203 16:35:29 server id 2 end_log_pos 245 Start: binlog v 4, server v 5.5.52-MariaDB created 171203 16:35:29 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG ‘
UbcjWg8CAAAA8QAAAPUAAAABAAQANS41LjUyLU1hcmlhREIAbG9nAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABRtyNaEzgNAAgAEgAEBAQEEgAA2QAEGggAAAAICAgCAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAikwNAA==
‘/*!*/;
# at 245
#171203 16:36:38 server id 2 end_log_pos 314 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1512290198/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 314
#171203 16:36:38 server id 2 end_log_pos 424 Query thread_id=2 exec_time=0 error_code=0
use `datas`/*!*/;
SET TIMESTAMP=1512290198/*!*/;
insert into tb1 values (14,’hehe’),(15,’yaya’)
/*!*/;
# at 424
#171203 16:36:38 server id 2 end_log_pos 451 Xid = 10
COMMIT/*!*/;
# at 451
#171203 16:54:34 server id 2 end_log_pos 520 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1512291274/*!*/;
BEGIN
/*!*/;
# at 520
#171203 16:54:34 server id 2 end_log_pos 618 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1512291274/*!*/;
insert into tb1 values (16,’yuyu’)
/*!*/;
# at 618
#171203 16:54:34 server id 2 end_log_pos 645 Xid = 20
COMMIT/*!*/;
# at 645
#171203 16:54:56 server id 2 end_log_pos 728 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1512291296/*!*/;
drop database datas
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
将最后的二进位制日志备份
1
[root@centos7 mysql]# mysqlbinlog –start-position=520 –stop-position=645 mysql-bin.000008> /backup/binlog/`date +%F_%T`.sql
8. 汇入之前的所有备份
1
2
3
4
[root@centos7 ~]# mysql < /backup/2017-12-03_15:05:00-all.sql
[root@centos7 ~]# mysql < /backup/binlog/2017-12-03_16:43:29.sql
[root@centos7 ~]# mysql < /backup/binlog/2017-12-03_16:51:11.sql
[root@centos7 ~]# mysql < /backup/binlog/2017-12-03_17:10:02.sql
9. 检视资料库及资料
1
2
3
4
5
6
7
8
9
10
11
12
MariaDB [datas]> select * from tb1;
+——+——+
| id | name |
+——+——+
| 11 | haha |
| 14 | hehe |
| 15 | yaya |
| 12 | hehe |
| 13 | yaya |
| 16 | yuyu |
+——+——+
6 rows in set (0.00 sec)
资料已经全部恢复了。