如何让删库跑路的损失降到最低
哈喽，大家好，我是Java小面。
最近公司一直在招有经验的数据库人才，原因就是因为公司起初刚搞软件的时候，对于数据库方面的管理可谓是九窍通了八窍，一窍不通啊。所有开发人员都直接使用root账户，去客户部署的时候，账户权限设置和管理也是最初始的，甚至没有做数据备份和开启Binlog。
搞的后期遇到与数据库数据相关的事情的时候，手忙脚乱的，前阵子从其他部门请来了个大佬，教了我们一些基础知识，才让我们不至于活的太惨啊。今天就来分享一下关于数据库数据安全的知识吧！
前言
对于任何一个互联网企业来说，数据是每个业务部门的重中之重，甚至有些还私自下买卖用户数据，这也是为什么现在天天收到保险电话，垃圾短信的原因。对于大企业来说，客户的数据安全的重要性就更加不言而喻了，凡是与数据安全擦边的问题，都有可能是未来导致损失惨重的大问题。
影响数据安全的事件，在生活中几乎很少碰见，但是一旦遇见了就是大问题。比如：数据库宕机、磁盘损坏甚至机房着火，还有就是程序员中一直以来都存在的梗“程序员不满老板删库跑路”，但是这些事一旦发生了，那么影响就是极大的。
数据安全常见情况主要有两种情况，一是数据丢失导致财产无法追溯，比如大量的坏账；二是由于系统损坏，数据库崩溃导致的停止服务而带来的损失。
今天说说，如何将“删库跑路”这类问题导致的数据丢失影响尽可能的降到最小。
如何做数据备份和恢复？
为了数据安全，最简单的手段自然就是定期的去备份我们的重要数据，只有这样，一旦发生数据丢失问题，都能通过备份来恢复数据。但是，如何备份，才能最大程度地保证数据安全，并不是一个简单的事儿。
但并不是简单地把数据备份一下就可以放心了。我们拿最常用的 MySQL 来举例说一下，如何更安全地来做数据备份和恢复。
每个数据库都具备备份数据的能力，MySQL就提供了mysqldump关键字，他会生成一个SQL脚本，把创库，创表，数据的新增指令都会被保存在里面。
我们最常见最常用的备份方式就是全量备份。哪怕是不怎么懂数据库的小白，都可以借助Navicat等数据库管理工具进行备份，把所有的数据复制一份，需要恢复的时候再把数据复制回去。接下来我们使用mysqldump命令来执行全量备份。
比如全量备份数据库 dataBase并指定备份文件名为test
$mysqldump -uroot -p datasBase > test.sql
这个后缀为sql的文件里面就是创建数据库、创建该数据库内的所有表，写入所有数据等等SQL，如果发生问题，想要恢复到备份时的那个时间点的数据，只需要直接执行这个备份的 SQL 文件就可以了：
$mysql -uroot datasBase < test.sql
但是我一般并不建议使用全量备份，为什么呢？
- 全量备份的备份文件包含该数据库中所有数据，磁盘空间被备份数据占据大半。
- 全量备份操作是拷贝大量数据，为了保证每时每刻的数据，我们需要频繁备份，但是频繁大量数据备份会占用服务器大多数的 CPU、磁盘 IO 资源，并且数据库本身为了保证数据一致性的机制，还有可能对大表在备份时进行锁表，这些都会导致备份期间，数据库本身的性能严重下降。
既然频繁地全量备份不可取，可能导致系统资源不够，那么有没有plan B备份方案，能让我们省点心不丢数据呢？
还真有plan B，那就是增量备份。
对比全量备份的大量数据备份，增量备份，顾名思义，备份的是多出来的那份数据，指的是我们每次备份的数据是上一次备份中没有的或者有变化的那部分数据，所以一般需要备份的数据量会比较少，因此增量备份的速度会比较快。
说到增量备份，我们就必须提一下Binlog了，因为只有Binlog的存在，增量备份才会有意义。
MYSQL特有的Binlog日志,它并非存储引擎的日志，它是MYSQL Server自己的日志文件。它本身就是一种增量备份，每次数据库的数据的变更操作都会被记录进去。而且Binlog本身支持回放，回放的意思是就是把之前做过的操作按顺序重新执行一遍，回放结束数据也就恢复了。
使用Binlog配合全量备份方案，我们只可以将一个意外丢失被删的数据库，先经过全量备份的文件恢复到今天早上，然后再通过Binlog恢复数据到现在即可。
下面我们做一个简单的备份恢复演示。我们先模拟一次“删库跑路”的场景，直接表清空：
//假设现在是2022-08-13 11:10:00，记住这是删库表的时间 mysql> truncate table test_table; Query OK, 0 rows affected (0.02 sec) mysql> select * from test_table; Empty set (0.00 sec)
然后我们来进行数据恢复，首先执行一次全量恢复，把数据库恢复到今天凌晨(我们定时凌晨进行全量备份)的状态。
$mysql -uroot datasBase < test.sql mysql> select * from test_table; +---------+---------+---------------------+--------+ | id | balance | timestamp | log_id | +---------+---------+---------------------+--------+ | 0| 100 | 2022-08-12 20:24:33 | 3 | +---------+---------+---------------------+--------+
我们可以看到，表里面的数据已经恢复到上次备份时的数据了，但还是比较旧的数据。然后我们再用 Binlog 把数据恢复到删库跑路之前的那个时刻：
//恢复范围从零点开始到执行删库动作前的所有数据 $mysqlbinlog --start-datetime "2022-08-13 00:00:00" --stop-datetime "2022-08-13 11:09:00" /usr/local/var/mysql/binlog.000001 | mysql -uroot mysql> select * from test_table; +---------+---------+---------------------+--------+ | id | balance | timestamp | log_id | +---------+---------+---------------------+--------+ | 0 | 200 | 2022-08-13 10:08:12 | 0 | +---------+---------+---------------------+--------+
现在数据已经恢复到当天的 11 点09分的数据了。通过定期的全量备份，配合 Binlog，我们就可以把数据恢复到任意一个时间点，再也不怕程序员删库跑路了。
在执行备份和恢复的时候，有几点需要特别注意：
- 不要把轻视备份文件的安全问题，无论是全量备份还是 增量备份，都不要把备份的数据文件和数据库存放在同一个服务器上。最好做到存储在不同电源的机房、不同个省市分部，越远越好，保证备份文件的安全。
- 回放 Binlog 的时候，指定的时间范围尽可能包含需要恢复的数据操作时间且不能包含删库操作的时间。用这种方式来保证数据都可以恢复到位。因为删库的动作也在Binlog里面，如果执行的起止点有误，你可能会重新执行一遍删库的操作，所以找准起止位点极为重要。
关于Binlog的使用问题
一、binlog已经记录了所有的数据的操作呀，为啥还需要定期的全量备份呢，直接拿binlog进行数据恢复不就可以了吗？
首先、binlog记录的是每次的操作。
- 使用mysqldump进行全量备份，直接基于内存中的数据，只需生成对应的SQL的语句
- 多条删除语句的binlog可以合并为 mysqldump的一条删除语句，对于数据库的性能会轻松一点
其次、此外binlog每行可能只记录影响的一行数据，一条更改sql语句对应多行日志。例如：删除多行就会在日志中记录多行update 合并为 一条删除语句。
最好、全量备份为SQL语句文件，可以直接执行，而binlog为二进制文件，需要逐条解析，再顺序执行。
综上所述、在还原数据时，mysqldump全量备份比binlog全量备份会快很多。
二、如果跑路的人比较"变态"，执行脚本来删除，删除语句和正常的业务语句都混合在一起了.....那是不是就没办法恢复了？
如果真遇到这种情况，那么恢复就是比较困难的了，但也不是没有办法，需要人为来从binlog中识别出来哪些是删库跑路的语句并剔除掉。
三、关于怎么定时备份问题
我这边会使用linux系统自带的crontab服务，编写一个写好的备份脚本，命名mysql_backup.sh。
#!/bin/bash mysqldump -uusername -ppassword database | gzip > /home/backup/database_$(date +%Y%m%d_%H%M%S).sql.gz
然后chmod赋予它执行权限:
chmod u+x mysql_backup.sh
使用crontab -e设置它每天两点执行备份指令
0 2 * * * /root/mysql_backup.sh
查看定时任务是否生效：
crontab -l
重启crontab服务：
service crond restart
这样，我们的定时任务就设置完成了。
如果你想通过Java代码去执行备份，我有一个建议，那就是使用一个叫woostju的依赖。他可以在代码里建立SSH连接，通过SSH访问数据库服务器，然后向服务器发送MYSQL备份语句进行备份。具体可以查询一下ssh-client-pool这个依赖的使用。
四、为什么不能通过binlog进行恢复？
想通过binlog进行恢复的前提必须是已经开启了binlog，否则binlog没有任何执行日志提供数据恢复。
执行 show variables like '%log_bin%'
mysql> show variables like '%log_bin%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | log_bin | OFF | | log_bin_basename | | | log_bin_index | | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+-------+ 6 rows in set (0.01 sec)
如未开启，则需要编辑 mysql 的配置文件 vim /etc/my.cnf，在 mysqld 下面添加下面2条配置
[mysqld] log-bin=/var/lib/mysql/mysql-bin server_id=152
然后重启
service mysqld restart
再执行 show variables like '%log_bin%'，就可以看到binlog已经开启了。
mysql> show variables like '%log_bin%'; +---------------------------------+--------------------------------+ | Variable_name | Value | +---------------------------------+--------------------------------+ | log_bin | ON | | log_bin_basename | /var/lib/mysql/mysql-bin | | log_bin_index | /var/lib/mysql/mysql-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+--------------------------------+ 6 rows in set (0.01 sec)
做好权限管理机制
除了技术方面，我觉得删库跑路也是一个管理机制上的问题，也要当成不可抗因素去对待。
- 针对人为事件的发生，对于数据库的权限上，我们理应建立权限分明的账号权限体系，对于生产环境，作为管理员的root账号不能对任何人开放，运维人员只能拥有查看表数据的权限，无法执行删除、新增、修改等操作。
- 对于备份文件的存放以及安全性，不仅仅针对地震，火灾的意外进行防范，对于人为的破坏依旧需要下功夫去保护。不能让一个人有能接触到所有备份的权限，否则就跟单机故障一样出现"单人故障"
给予用户权限
MySQL 赋予用户权限命令指令为：grant 权限 on 数据库对象 to 用户
一、创建用户
create user common_user@'localhost' identified by '123456';
二、grant 普通数据用户，查询、插入、更新、删除 数据库中所有表数据的权利
grant select, insert, update, delete on testdb.* to common_user@'localhost'
三、grant 创建、修改、删除 MySQL 数据表结构权限
grant create on testdb.* to developer@'192.168.0.%' ; grant alter on testdb.* to developer@'192.168.0.%' ; grant drop on testdb.* to developer@'192.168.0.%' ;
小结
所以，经过这次学习，我们建议对于数据备份的建议，使用低频度的全量备份配合 Binlog 增量备份是一种常用而且非常实用的方法。使用配合方式，我们能把数据备份精确地恢复到过去的任意一个时间点，不仅能解决数据损坏的问题，也再也不用怕误操作、删库跑路这些事儿了。
特别要注意的是，让备份数据尽量地远离数据库。对于用户权限分配上面，为了数据安全，尽可能只开放select查询权限即可。
