数据库的备份
数据备份的意义
- 保护数据的安全。
- 在出现意外的时候（硬盘的损坏，断电，黑客的攻击），以便数据的恢复。
- 导出生产的数据以便研发人员或者测试人员测试学习。
- 高权限的人员操作失误导致数据丢失，以便恢复。
数据库的备份类型
- 完全备份：对整个数据库的数据进行备份。
- 部分备份：对部分数据进行备份（可以是一张表也可以是多张表）
增量备份：是以上一次备份为基础来备份变更数据的，节约空间。
差异备份：是以第一次完全备份的基础来备份变更备份的，浪费空间。
数据库备份的方式
逻辑备份效率低，恢复数据效率低，但是逻辑备份节约空间；物理备份浪费空间，但是相对逻辑备份而言效率比较高。
- 逻辑备份：直接生成 SQL 语句保存起来，在恢复数据的时候执行备份的 SQL 语句来实现数据的恢复。常见备份工具：mysqldump
- 物理备份：直接拷贝相关的物理数据
数据库备份的场景
- 热备份：备份时，数据库的读写操作不会受到影响。
- 温备份：备份时，数据库的读操作可以进行，但是写操作不能执行。
- 冷备份：备份时，不能进行任何操作。
mysqldump 数据备份（跨机器）
1# mysqldump使用语法
2mysqldump -u 用户 -h host -p 密码 dbname table > 路径
3
4# 远程备份单库
5mysqldump -uroot -p123 -h192.168.31.220 db1 | gzip > /mysql_data_back/db1_bak.sql.gz
6
7# 远程备份单库(保留创建库的sql语句，这样数据恢复的时候不需要指定库)
8mysqldump -uroot -p123 -h192.168.31.220 --databases db1 | gzip > /mysql_data_back/db1_bak.sql.gz
9
10# 远程备份单库单表：db1库中的test表
11mysqldump -uroot -p123 -h192.168.31.220 db1 test | gzip > /mysql_data_back/db1_test.sql.gz
12
13# 远程备份多库
14mysqldump -uroot -p123 -h192.168.31.220 db1 db2 | gzip > /mysql_data_back/dbs_bak.sql.gz
15
16# 远程备份全库
17mysqldump -uroot -p123 -h192.168.31.220 --all-databases | gzip > /mysql_data_back/all.sql.gz
MySQL 数据恢复（跨机器）
1# 远程恢复数据（备份的数据文件里有创建库的语句）
2mysql -uroot -p123 -h192.168.31.220 < db1_bak.sql
3
4# 远程恢复数据（备份的数据文件里没有创建库的语句,如果库已经丢失，需要手动创建库）
5mysql -uroot -p123 -h192.168.31.220 db1 < db1_bak.sql
MySQL 数据物理备份
- 务必先停止 MySQL 服务，再进行物理备份。
- 务必停止 MySQL 服务，再进行物理数据还原，再启动 MySQL 服务。
查找数据库源文件路径
MyISAM 表源文件
1db.opt：创建库的时候生成，主要存储着当前库的默认字符集和字符校验规则
2.frm ：记录着表结构信息的文件
3.MYI：记录着索引的文件
4.MYD :记录着表的数据
InnoDB 表源文件
InnoDB 有着共享表空间跟独立表空间的概念。
1db.opt：创建库的时候生成，主要存储着当前库的默认字符集和字符校验规则
2.frm ：记录着表结构信息的文件
3.ibd ：独立表空间，里边记录这个表的数据和索引
4ibdata1：共享表空间，里边记录表的数据和索引
物理备份
1################### MyISAM引擎的数据物理备份与恢复
2# 备份test_MyISAM库
3tar -zcf test_MyISAM.gz test_MyISAM
4rm -rf test_MyISAM
5# 恢复test_MyISAM库
6tar -xf test_MyISAM.gz
7
8################### InnoDB引擎的数据物理备份与恢复
9# 备份test_InnoDB库
10tar -zcf test_InnoDB.gz test_InnoDB
11cp ibdata1 ib_logfile0 ib_logfile1 /mysql_data_back/
12rm -rf test_InnoDB
13rm -rf ibdata1 ib_logfile0 ib_logfile1
14# 恢复test_InnoDB库
15tar -xf test_InnoDB
16cp /mysql_data_back/ib* .
17chown mysql:mysql ibdata1 ib_logfile0 ib_logfile1
18service mysql restart
请求全局读锁
如果不停止 MySQL，而且还要想要物理备份可以使用请求全局锁。
1# 请求全局读锁
2flush tables with read lock;
3# 解锁
4unlock tables;
二进制日志备份数据
二进制日志就是记录着 MySQL 数据库中的一些写入性操作，比如一些增删改，但是，不包括查询！
二进制日志功能
一般情况下，二进制日志有着数据复制和数据恢复的功能。开启二进制日志会有 1% 的性能消耗！
二进制日志备份恢复
1# 查看二进制日志文件 第二种：注释掉配置文件里边的default-character-set=utf8
2[root@localhost etc]# mysqlbinlog --no-defaults /usr/local/mysql/var/mysql-bin.000002
3
4# 把二进制日志文件导出成普通文件
5mysqlbinlog --no-defaults --base64-output=DECODE-ROWS -v /usr/local/mysql/var/mysql-bin.000002 > mysqlbin.sql
6
7# 使用grep过滤出delete财务部的操作
8[root@localhost test]# more mysqlbin.sql | grep -B 5 'DROP'
9/*!*/;
10# at 296
11 #在这里！！！！！！！！！！！！
12#190722 18:52:10 server id 1 end_log_pos 440 CRC32 0xf25c0850 Query thread_id=2 exec_time=0 error_code=0
13use `db1`/*!*/;
14SET TIMESTAMP=1563792730/*!*/;
15DROP table dept
16
17# 或者直接查看二进制日志文件也可以
18[root@localhost test]# mysqlbinlog --no-defaults /usr/local/mysql/var/mysql-bin.000002 | cat -n | grep -iw 'DROP'
19 35 drop table dept
20
21[root@localhost test]# mysqlbinlog --no-defaults /usr/local/mysql/var/mysql-bin.000002 | cat -n | sed -n '25,35p'
22 25 /*!\C utf8 *//*!*/;
23 26 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=45/*!*/;
24 27 SET @@session.lc_time_names=0/*!*/;
25 28 SET @@session.collation_database=DEFAULT/*!*/;
26 29 BEGIN
27 30 /*!*/;
28 31 # at 296
29 # 在这里！！！！！
30 32 #190722 18:52:10 server id 1 end_log_pos 440 CRC32 0xf25c0850 Query thread_id=2 exec_time=0 error_code=0
31 33 use `db1`/*!*/;
32 34 SET TIMESTAMP=1563792730/*!*/;
33 35 drop table dept
34
35# mysql恢复数据的逻辑备份
36mysql -uroot -p123 -h192.168.31.220 db1 < db1.sql
37
38# 数据库查看：数据还原至删除财务部门之前
39mysql> select * from dept;
40+--------+-----------+--------+
41| deptnu | dname | addr |
42+--------+-----------+--------+
43| 10 | 研发部 | 北京 |
44| 20 | 工程部 | 上海 |
45| 30 | 销售部 | 广州 |
46| 40 | 财务部 | 深圳 |
47+--------+-----------+--------+
484 rows in set (0.00 sec)
49
50# 利用上面找到的删除的位置，之后使用二进制日志进行恢复数据
51mysqlbinlog --no-defaults --set-charset=utf8 --stop-position="296
52" /usr/local/mysql/var/mysql-bin.000002 | mysql -uroot -p
53
54
55# 数据库查看：数据还原至删除财务部门之前
56mysql> select * from dept;
57+--------+-----------+--------+
58| deptnu | dname | addr |
59+--------+-----------+--------+
60| 10 | 研发部 | 北京 |
61| 20 | 工程部 | 上海 |
62| 30 | 销售部 | 广州 |
63| 40 | 财务部 | 深圳 |
64| 50 | 测试部 | 南京 |
65+--------+-----------+--------+
664 rows in set (0.00 sec)