MySQL数据备份及恢复

备份原因

运维工作的核心简单概括就两件事:
	1）第一个是保护公司的数据.
	2）第二个是让网站能7*24小时提供服务(用户体验)。

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

备份类型

冷备：停服务做备份（mysqldump做不了）
温备：不停止服务做备份，但是备份过程中会锁表
热备：不停止服务做备份，也不锁表

备份方式

### 逻辑备份
mysqldump
into outfile   ：--- > # select * from world.city into outfile '/tmp/world_city.data';
mysqlbinlog
replication

### 物理备份
备份data目录  tar zcf
Xtrabackup

备份策略

全备：备份所有数据
增备：基于上一次备份后新增的数据
差异备份：基于全备后新增的数据

mysqldump 逻辑备份工具命令解析

## 客户端命令选项
-u：指定用户
-p：指定密码
-h：指定主机
-P：指定端口
-S：指定socket文件

#### 全备
-A --all-databases：备份MySQL的所有库和所有表
[root@db02 ~]# mysqldump -uroot -p123 -A > /tmp/full.sql

#### 库级别备份
mysqldump -uroot -p123 -B world > /tmp/world.sql

### 恢复方式
mysql -uroot -p123 < /tmp/world.sql

## 指定多个库备份
[root@db02 ~]# mysqldump -uroot -p123 -B A B  > /tmp/Ａ_B.sql

##### 表级别备份
## 不加选项直接写库名字
[root@db02 ~]# mysqldump -uroot -p123 world > /tmp/world2.sql     #  无创库语句

### 恢复方式
mysql -uroot -p123 linux8 < /tmp/world.sql

## mysqldump -uroot -p123 库名 表名 表名 表名 表名...
[root@db02 ~]# mysqldump -uroot -p123 linux8 city > /tmp/linux8_city.sql
[root@db02 ~]# mysqldump -uroot -p123 linux8 prod > /tmp/linux8_prod.sql

打点备份 -快照备份-备份函数和存储过程-备份触发器

##------- 打点备份 ---------#
--master-data=[0|1|2] ：（温备选项）This option will turn --lock-all-tables on 使
用该选项时会打开--lock-all-tables选项 会锁表
0：If equal to 0,不加这个选项，没有change master语句记录
1：If equal to 1, will print it as a CHANGE MASTER command # 打
印change master语句
2：if equal to 2, that command will be prefixed with a comment symbol #
注释change master语句


#--------- 快照备份--------#
--single-transaction：（热备选项）不锁表，不影响用户持续写入数据
mysqldump -uroot -p123 -A --master-data=2 --single-transaction >
/backup/full.sql


##------- 备份函数和存储过程  ---------#
-R：备份函数和存储过程（如果有就备份，如果没有就不备份）
mysqldump -uroot -p123 -A -R --master-data=2 --single-transaction > /backup/full.sql


## -------- 备份触发器 ---------- ##
--triggers：备份触发器（如果开发写了触发器就备份，如果没写就不备份）
mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction >/backup/full.sql
mysqldump -uroot -p123 -A -R --triggers --master-data=2 --singletransaction|gzip > /backup/full.sql.gz



#----------超级无敌热备打点---------#
[root@db01 ~]# mysqldump -uroot -p123 -A -R --triggers --master-data=2 --singletransaction|gzip > /backup/full_$(date +%F).sql.gz

刷新binlog备份（不常用）

-F：做备份的同时，每备份一个库刷新一个新的binlog出来（但不常用）
[root@db02 ~]# mysqldump -uroot -p123 -A -F > /tmp/test_binlog.sql

认识不用的选项

-d: 导出表结构   .frm
-t: 导出仅有数据 .idb

注意

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