一.备份的原因
运维工作的核心简单概括就两件事:
1）第一个是保护公司的数据.
2）第二个是让网站能7*24小时提供服务(用户体验)。
1）备份就是为了恢复。
2）尽量减少数据的丢失（公司的损失）
二.备份的类型
冷备份:
这些备份在用户不能访问数据时进行，因此无法读取或修改数据。这些脱机备份会阻止执行任何使用数据的活动。这些类型的备份不会干扰正常运行的系统的性能。但是，对于某些应用程序，会无法接受必须在一段较长的时间里锁定或完全阻止用户访问数据。
温备份:
这些备份在读取数据时进行，但在多数情况下，在进行备份时不能修改数据本身。这种中途备份类型的优点是不必完全锁定最终用户。但是，其不足之处在于无法在进行备份时修改数据集，这可能使这种类型的备份不适用于某些应用程序。在备份过程中无法修改数据可能产生性能问题。
热备份:
这些动态备份在读取或修改数据的过程中进行，很少中断或者不中断传输或处理数据的功能。使用热备份时，系统仍可供读取和修改数据的操作访问。
三.备份的方式
逻辑备份:
1. 逻辑备份
1）binlog
2）into outfile
mysql> select * from world.city into outfile '/tmp/world_city.data';
#需要先在配置文件添加参数，指定位置
[root@db01 ~]# vim /etc/my.cnf
secure_file_priv=/tmp
#重启数据库
[root@db01 ~]# /etc/init.d/mysqld stop
Shutting down MySQL.. SUCCESS!
[root@db01 ~]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS!
#连接
[root@db01 ~]# mysql -uroot -p1
mysql> show variables like 'secure_file_priv';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| secure_file_priv | /tmp/ |
+------------------+-------+
1 row in set (0.01 sec)
#把数据库里world.city表导出来
mysql> select * from world.city into outfile '/tmp/world_city.data';
Query OK, 4079 rows affected (0.01 sec)
#查看导出的表前十行
[root@db01 ~]# head /tmp/world_city.data
1 Kabul AFG Kabol 1780000
2 Qandahar AFG Qandahar 237500
3 Herat AFG Herat 186800
4 Mazar-e-Sharif AFG Balkh 127800
5 Amsterdam NLD Noord-Holland 731200
6 Rotterdam NLD Zuid-Holland 593321
7 Haag NLD Zuid-Holland 440900
8 Utrecht NLD Utrecht 234323
9 Eindhoven NLD Noord-Brabant 201843
10 Tilburg NLD Noord-Brabant 193238
3）mysqldump
4）replication（主从复制）
5）mysqlbinlog
2.物理备份:
基于数据文件的备份
1）cp data
2）Xtrabackup（percona公司）速度非常快
四.备份策略
1）全量备份 full
2）增量备份 increamental
3）差异备份
五.备份工具
1）mysqldump（逻辑）
mysql原生自带很好用的逻辑备份工具
2）mysqlbinlog（逻辑）
实现binlog备份的原生态命令
3）xtrabackup（物理）
precona公司开发的性能很高的物理备份工具
备份工具使用
mysqldump的备份
mysqldump常用参数
1）连接服务端参数(基本参数)：
mysqldump
-u：指定用户
-p：指定密码
-h：指定主机域
-P：指定端口
-S：指定socket文件
2）-A, --all-databases：全库备份
[root@db01 ~]# mysqldump -uroot -p123 -A > /tmp/full.sql
3）不加参数：单库、单表多表备份
#不加参数，只备份库里表的内容。
[root@db01 ~]# mysqldump -uroot -p123 db01 > /tmp/db01.sql
#导库的时候需要指定库名
[root@db02 ~]# mysql -uroot -p1 db01 < /tmp/db01.sql
#单表world.city表
[root@mysql-db01 backup]# mysqldump -uroot -p123 world city > /tmp/city.sql
4）-B：指定库备份
[root@db01 ~]# mysqldump -uroot -p123 -B db1 > /tmp/db1.sql
[root@db01 ~]# mysqldump -uroot -p123 -B db1 db2 > /tmp/db1_db2.sql
5）-F：flush logs在备份时自动刷新binlog（不常用，很鸡肋，有多少个库，刷新多少个binlog）
[root@db01 backup]# mysqldump -uroot -p123 -A -R –triggers -F > /backup/full_2.sql
[root@db01 mysql]# mysqldump -uroot -p1 -A -F > /tmp/full_F.sql
6）--master-data=2：备份时加入change master语句0没有1不注释2注释
#--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
7）-d：仅表结构
8）-t：仅数据
备份额外扩展项
1）-R, --routines：备份存储过程和函数数据 （开发xieden）
2）--triggers：备份触发器数据( 开发写的‘)
[root@db01 backup]# mysqldump -uroot -p123 -A -R --triggers > /tmp/full_2.sql
mysqldump特殊参数
1）-x：锁表备份（myisam温备份）
2）--single-transaction：快照备份
[root@db01 backup]# mysqldump -uroot -p123 -A -R --triggers --master-data=2 –single-transaction>/tmp/full.sql
找点
[root@db01 ~]# mysql -uroot -p1 -e "show binlog events in 'mysql-bin.000003'"|grep -i drop
3)gzip:压缩备份 (终极备份语句)
[root@db01 ~]# mysqldump -uroot -p123 -A -R --triggers --master-data=2 –single-transaction|gzip>/tmp/full.sql.gz
####了解即可
#-d：仅备份表结构
#-t：仅备份数据
#-x：锁表备份
#恢复压缩数据
[root@db01 tmp]# zcat /tmp/full_2019-11-13.sql.gz | mysql -uroot -p1
mysqldump的恢复
#先不记录二进制日志
mysql> set sql_log_bin=0;
#库内恢复操作
mysql> source /tmp/full.sql
#库外恢复操作
[root@db01 ~]# mysql -uroot -p123 < /tmp/full.sql
注意：
1）mysqldump在备份和恢复时都需要MySQL实例启动为前提
2）一般数据量级100G以内，大约15-30分钟可以恢复（PB、EB就需要考虑别的方式）
3）mysqldump是以覆盖的形式恢复数据的
六、企业故障恢复案例
背景：
正在运行的网站系统，MySQL数据库，数据量25G，日业务增量10-15M。
备份策略：
每天23：00，计划任务调用mysqldump执行全备脚本
故障时间点：
上午10点开发人员误删除一个核心业务表，如何恢复？
思路：
1）停业务避免数据的二次伤害，停止连接数据库的服务（php ,tomcat , python... ）
2）找一个临时的库，恢复前一天的全备
3）截取前一天23：00到第二天10点误删除之间的binlog，恢复到临时库
4）测试可用性和完整性
5）开启业务前的两种方式
a.直接使用临时库顶替原生产库，前端应用割接到新库
b.将误删除的表单独导出，然后导入到原生产环境
6）开启业务
故障模拟演练：
1.模拟环境
- .模拟环境准备，准备数据：
#刷新binlog使内容更清晰
mysql> flush logs;
#查看当前使用的binlog
mysql> show master status;
#创建backup库
mysql> create database backup;
#进入backup库
mysql> use backup
#创建full表
mysql> create table full select * from world.city;
#创建full_1表
mysql> create table full_1 select * from world.city;
#查看表
mysql> show tables;
- .模拟23:00 全备：
[root@db01 ~]# mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction|gzip > /tmp/full_$(date +%F).sql.gz
- .模拟第二天数据变化：
#进入backup库
mysql> use backup
#创建new表
mysql> create table new select * from mysql.user;
#创建new_1表
mysql> create table new_1 select * from world.country;
#查看表
mysql> show tables;
#查看full表中所有数据
mysql> select * from full;
#把full表中所有的countrycode都改成CHN
mysql> update full set countrycode='CHN' where 1=1;
#提交
mysql> commit;
#删除id大于200的数据
mysql> delete from full where id>200;
#提交
mysql> commit;
4).模拟故障：H E
#删除new表
mysql> drop table new;
#查看表
mysql> show tables;
2.模拟恢复数据过程：
1）.停库，停止业务（避免二次伤害数据）
[root@db01 tmp]# /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 ~]# /etc/init.d/mysqld stop
Shutting down MySQL.. SUCCESS!
[root@db02 ~]# rm -fr /application/mysql/data/*
[root@db02 ~]# cd /application/mysql/scripts/
[root@db02 ~]# ./mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/application/mysql/data
[root@db02 scripts]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/application/mysql/data/db02.err'.
SUCCESS!
[root@db02 mysql]# mysql
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
3）将昨天晚上23点做的全备导入新环境,拷贝数据到新库上
[root@db01 ~]# scp /tmp/full_2019-11-13.sql.gz root@10.0.0.52:/tmp
4）导入数据
[root@db02 ~]# zcat full_2019-11-13.sql.gz |mysql
mysql> show tables from backup;
+------------------+
| Tables_in_backup |
+------------------+
| full |
| full_1 |
+------------------+
mysql> select count(*) from backup.full_1;
+----------+
| count(*) |
+----------+
| 4079 |
+----------+
1.根据binlog截取昨晚23点到今天上午10点的数据
先找到起点
#找起点
[root@db01 tmp]# zcat /tmp/full_2019-11-13.sql|head -25
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.00005', MASTER_LOG_POS=542384;
#或者再过滤查找
[root@db01 scripts]# zcat /tmp/full_2019-11-13.sql.gz |head -25 |grep -i 'change master to'
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=542384;
找到结束点
方法一：
[root@db01 data]# mysqlbinlog --base64-output=decode-rows -vvv /application/mysql/data/mysql-bin.000005|less
#搜索DROP
679230
方法二：
[root@db01 ~]# mysqlbinlog --base64-output=decode-rows -vvv /application/mysql/data/mysql-bin.000005 |grep -i -B 5 'drop table'
截取
#截取二进制
[root@db01 tmp]#mysqlbinlog -uroot -p123 --start-position=542384 --stop-position=679230 /application/mysql/data/mysql-bin.000017 > /tmp/inc.sql
5）.将截取出来的数据发送到新环境
[root@db01 tmp]# scp /tmp/inc.sql 172.16.1.52:/tmp
6）.导入新增数据
[root@db02 mysql]# set sql_log_bin=0
[root@db02 mysql]# mysql -uroot -p1 < /tmp/inc.sql
方法二：
在新库内恢复数据
#不记录二进制日志
mysql> set sql_log_bin=0;
#恢复全备数据
mysql> source /tmp/full_2019-11-13.sql
#进入backup库
mysql> use backup
# 查看表
mysql> show tables;
#恢复增量数据
mysql> source /tmp/inc.sql
查看
mysql> use backup
mysql> show tables;
+------------------+
| Tables_in_backup |
+------------------+
| full |
| full_1 |
| inc |
| inc_1 |
+------------------+
mysql> select count(*) from full_1;
+----------+
| count(*) |
+----------+
| 8158 |
+----------+
7）.恢复生产业务
-
应用割接（修改连接数据库的代码）
-
导出核心业务表，恢复到旧生产环境
[root@db02 mysql]#mysqldump -uroot -p1 db1 tb1 > /tmp/tb1.sql [root@db02 mysql]#scp /tmp/tb1.sql 172.16.1.51:/tmp [root@db01 data]# /etc/init.d/mysqld start [root@db01 data]# mysql -uroot -p1 db1 < /tmp/tb1.sql
在生产库恢复数据
#方法一：
mysql -uroot -p1 db1 < /tmp/tb1.sql
#方法二：
启动数据库
#进入backup库
mysql> use backup
#在生产库恢复数据
mysql> source /tmp/new.sql
#查看表
mysql> show tables;
以上两种方式都可以，选择速度最快的一种方式
改代码：
1）.开发提交gitlab，从gitlab拉取，上线
2）.开发写好了配置文件
8.开启业务
结合：mysqldump全备+binlog增备 恢复数据。
#全备
[root@db01 ~]# mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction|gzip > /tmp/full_$(date +%F).sql.gz
#scp到对端
[root@db01 ~]# scp /tmp/full_2019-11-13.sql.gz root@10.0.0.52:/tmp
#对端导库
[root@db02 ~]# zcat full_2019-11-13.sql |mysql -uroot -p1
#mysql-bin数字
[root@db01 ~]# zcat /tmp/full_2019-11-13.sql|head -25 |sed -rn "s#.*mysql-bin.(.*)'.*#1#gp"
000005
#起点
[root@db01 ~]# zcat /tmp/full_2019-11-13.sql|head -25 |sed -rn 's#.*MASTER_LOG_POS=(.*);#1#gp'
542384
#结束点
[root@db01 ~]# mysqlbinlog --base64-output=decode-rows -vvv /application/mysql/data/mysql-bin.000005 |grep -i -B 5 'drop table `full_1`'|sed -rn 's#.*at (.*)#1#gp'
408174
#截取二进制
[root@db01 tmp]#mysqlbinlog -uroot -p123 --start-position=542384 --stop-position=679230 /application/mysql/data/mysql-bin.000017 > /tmp/inc.sql
#远程传到对端
[root@db01 tmp]# scp /tmp/inc.sql 172.16.1.52:/tmp
#导库
[root@db02 mysql]# mysql -uroot -p1 < /tmp/inc.sql