mysql备份与恢复

mysql数据库备份与恢复

一、为什么要备份

灾难恢复：硬件故障、软件故障、自然灾害、黑客攻击、误操作测试等数据丢失场景

备份注意要点

能容忍最多丢失多少数据

恢复数据需要在多长时间内完成

需要恢复哪些数据

还原要点

做还原测试，用于测试备份的可用性
还原演练

二、备份类型

备份类型：

完全备份，部分备份
完全备份：整个数据集
部分备份：只备份数据子集，如部分库或表
完全备份、增量备份、差异备份
增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，备份较快，还原复杂
差异备份：仅备份最近一次完全备份以来变化的数据，备份较慢，还原简单
//注意：二进制日志文件不应该与数据文件放在同一磁盘

三、备份种类

冷、温、热备份

冷备：读写操作均不可进行
温备：读操作可执行；但写操作不可执行
热备：读写操作均可执行
MyISAM引擎：温备，不支持热备

InnoDB引擎: 都支持

物理和逻辑备份
物理备份：直接复制数据文件进行备份，与存储引擎有关，占用较多的空间，速度快
逻辑备份：从数据库中“导出”数据另存而进行的备份，与存储引擎无关，占用空间少，速度慢，可能丢失精度

四、逻辑备份

逻辑备份特点

1.备份的都是SQL语句，数据量大的话SQL语句备份快，但是恢复数据的时候会很慢
2.备份的是建表、建库、插入等操作所执行SQL语句（DDL DML DCL），适用于中小型数据库。
3.效率相对较低

用法

mysqldump   -h 服务器(默认本机) -u..  -p..  数据库名>name.sql

日常用法
备份所有数据库

先配置用户名和密码
在任意文件内配置
[mysqldump]
user=root
password=root
...
文件内容配好的话，在备份的时候可以用 --defaults-file直接读取文件内容，方便快捷

mysqldump --defaults-file=~/.mysql_user --all-databases > xxx.sql
# 不包含 INFORMATION_SCHEMA,performance_schema,sys

备份单个库

mysqldump --defaults-file=/root/.mysql_user -hlocalhost  --databases servers >servers.sql
mysqldump --defaults-file=/root/.mysql_user -hlocalhost   servers>servers.sql
加与不加--databases的区别：   
         加上--databases的话，备份文件内会有创库与进库语句
        如果不加的话，必须自己创建库并且进入库

进入数据库内使用    source  backup.sql

备份多个库

mysqldump --defaults-file=/root/.mysql_user -hlocalhost  --databases db1 db2  db3  .  >db.sql

备份一个库的几个表

mysqldump  --defaults-file=/root/.mysql_user  -hlocalhost   db1 t1 t2 t3  >backup.sql

恢复数据

1.   mysql -uroot  -proot   db < backup.sql

2.   进入数据库
source   backup.sql

五、物理备份

5.1、全量备份

知识

对数据库在线进行全备的时候，用户可以对数据库进行增删改查，如果数据库已经完成备份后，数据并不会同步，如果不处理的话数据就会造成损失

这时会有一个事件用来监控那些已经备份完成之后的数据库，这些内容会存储在xtrabackup_logfile这个二进制文件内，但是这个文件并不能数据库解析，所以数据备份之后就要对这个文件进行恢复，将其转换为可以被mysql识别的文件，然后在进行恢复数据，也就是复制文件
###下载xtrabackup

1.  yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
2.  yum  install -y libev  
3.  yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
4.  yum install percona-xtrabackup-24

全备的大致流程

1.    创建好存储备份的目录
2.    对数据库进行备份
3.    将事件产生的日志转换为数据库可以识别的
4.    将数据库服务停止
5.    进行备份
6.    开启数据库服务进行验证

全备的操作

1.   shell> xtrabackup --backup --user=root --password='123'  --target-dir=/backups/full

# 备份完成后，可以看到备份时的LSN号，当下次进行增量备份时，xtrabackup就只备份大于此号的page即可。
2.   shell> xtrabackup --prepare --target-dir=/backups/full
3.  shell> systemctl stop mysqld.service
4.  shell> rm -rf /var/lib/mysql/*
5.  shell> xtrabackup --copy-back --datadir=/var/lib/mysql --target-dir=/backups/ful
6.   systemctl start mysqld     开启数据库服务进行检验

5.2、增量备份

增量备份是在全备的基础之上进行增量备份
特点：是在上一次备份的基础上进行增量备份
###创建增量备份

1. 进行全量备份

xtrabackup --backup --user=root --password=root  --target-dir=/backup/full

2. 进行增量备份

手动增加数据之后进行增量备份
第一次增量备份
xtrabackup --backup --user=root --password=root  --target-dir=/backup/inc1  --incremental-basedir=/backup/full
在第一次全备的基础上进行增量备份，备份后的文件放在inc1目录内
第二次增量备份
xtrabackup --backup --user=root --password=root  --target-dir=/backup/inc2  --incremental-basedir=/backup/inc1
在第一次增量备份的基础上进行增量备份，备份后的文件放在inc2目录内

3. 恢复数据

1. 准备全备的数据
xtrabackup  --prepare  --apply-log-only --target-dir=/backup/full
2.把第一次备份的数据合并到全备的数据内
xtrabackup  --prepare  --apply-log-only --target-dir=/backup/full  --
incremental-dir=/backup/incl
3.把第一次备份的数据合并到全备的数据内
xtrabackup  --prepare   --target-dir=/backup/full  --
incremental-dir=/backup/inc2
注意最后一次合并不加 --apply-log-only

4. 停止服务，删除数据

systemctl stop mysqd
rm -rf /var/lib/mysqld/*

5.恢复数据

xtrabackup --copy-back --datadir=/var/lib/mysql  --target-dir=/backup/full

6.修改权限之后开启服务就可以了

#将数据库恢复默认

1.  关闭服务   将数据库内的内容全部清空
2.  mysqld --initialize --user=mysql