数据库的备份与恢复
一、数据库备份的重要性
二、数据库的备份分类
三、完全备份、增量备份概述与对比
四、完全备份的实例
1.4.1、冷备份与数据恢复
1.4.2、mysqldump备份与恢复
五、增量备份的实例（基于完全备份）
1.5.1、一般恢复
1.5.2、时间点恢复

一、数据库备份的重要性
数据库备份的重要性主要体现在：

提高系统的高可用性和灾难可恢复性，在数据库系统崩溃是。没有数据库备份就没办法找到数据。
使用数据库备份还原数据库，是数据库崩溃是提供数据恢复最小代价的最优方案，如果让用户重新添加数据，代价太大。
没有数据就没有一切，数据库备份是一种防范灾难的强力手段。

使用数据库的过程中，有多种原因造成数据的丢失：

程序错误：指对数据库操作的程序难免有些错误，造成数据丢失。
人为错误：误操作造成的数据被破坏，也可能是黑客对系统攻击造成的。
计算机失败：指运行数据库的服务器操作系统或软件损坏，有可能造成数据的损坏。
磁盘失败：指硬盘等存储数据的硬件设备，长时间运行后可能损坏，造成数据丢失。

二、数据库的备份分类
从物理与逻辑的角度，备份可分为

物理备份：对数据库操作系统的物理文件（如数据文件、日志文件等）的备份

物理备份方法

冷备份（脱机备份）：是在关闭数据库的时候进行的
热备份（联机备份）：数据库处于运行状态，依赖于数据库的日志文件
温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作


逻辑备份：对数据库逻辑组件（如：表等数据库对象）的备份

从数据库的备份策略角度备份可分为：

完全备份：每次对数据库进行完整的备份
差异备份：备份自从上次完全备份之后被修改过的文件
增量备份：只有在上次完全备份或者增量备份后被修改的文件才会被备份

三、完全备份、增量备份概述与对比
完全备份：

是对整个数据库、数据库结构和文件结构的备份；
保存的是备份完成时刻的数据库；
是差异备份与增量备份的基础
每次对数据进行完整的备份

优点： 备份与恢复操作简单方便

缺点：

数据存在大量的重复；

占用大量的备份空间；

备份与恢复时间长。
增量备份：

MySQL增量备份是自上一次备份后增加/变化的文件或者内容  特点
没有重复数据，备份量不大，时间短；
依靠二进制日志文件进行逐次增量备份，单个文件丢失则数据不完整，安全性低

MySQL二进制日志对增量备份有重要的作用


二进制日志保存了所有更新或者可能更新数据库的操作；


二进制日志在启动MySQL服务器后开始记录，并在文件达到max_binlog_size所设置的大小或者接收到flush logs命令后重新创建新的日志文件；


只需定时执行flush logs方法重新创建新的日志，生成二进制文件序列，并及时把这些日志保存到安全的地方就完成了一个时间段的增量备份。
     				备份方式比较







备份方式
完全备份
差异备份
增量备份




完全备份时的状态
表1、表2
表1、表2
表1、表2


第1次添加内容
创建表3
创建表3
创建表3


备份内容
表1、表2、表3
表3
表3


第2次添加内容
创建表4
创建表4
创建表4


备份内容
表1、表2、表3、表4
表3、表4
表4


四、完全备份的实例
1.4.1、冷备份与数据恢复
备份前需要先停止数据库服务，在直接打包压缩数据库文件

数据库所有文件目录位置：/usr/local/mysql/data



故障时，停止服务，将故障的数据库文件移走到备份文件夹中，解压刚才备份数据库包到/restore目录下，再移动到/usr/local/mysql/data下，再重启服务





恢复成功
1.4.2、mysqldump备份与恢复
备份方法：
mysqldump -u root -p --all-databses > all-data-$(date +%F).sql
 ###备份所有数据库到当前目录下的all-data-$(date +%F).sql 文件
mysqldump -u root -p -databases auth mysql > auth-mysql.sql ###备份auth和mysql库
mysqldump -u root -p auth > auth-$(date +%F).sql ###备份auth数据库
mysqldump -u root -p mysql user > mysql-user-$(date +%F).sql ###备份mysql的user表
mysqldump -u root -p -d mysql user > /tmp/desc-mysql-user.sql ###备份mysql库user表的结构

实例一

单独备份一个auth库
mysql> drop database auth;
Query OK, 3 rows affected (0.02 sec)

mysql> create database test;#命名可根据需要
Query OK, 1 row affected (0.00 sec)
mysql> exit
Bye

方法一：在数据库外导入
[root@server1 ~]# mysql -u root -p test < auth-2020-11-02.sql #将刚刚备份的导入test
Enter password: 
方法二：在数据库内用source
#这里必须要use 数据库，否则无法有对应的数据库可以导入
mysql> use test;
mysql> source /auth-2020-11-02.sql;
查看恢复结果
mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| info              |
| test              |
| zf                |
+-------------------+
3 rows in set (0.00 sec)
mysql> select * from info;
+----+--------+---------+
| id | name   | address |
+----+--------+---------+
|  1 | lisi   | 苏州    |
|  2 | liqi   | 杭州    |
|  3 | wangwu | 北京    |
+----+--------+---------+
3 rows in set (0.00 sec)

实例2

备份多个数据库
[root@server1 ~]# mysqldump -uroot -pzbc123 --databases mysql student > mysql-student-bak.sql
#备份数据库mysql和student到当前目录下的mysql-student.sql，可查看到，也可加绝对路径保存
[root@server1 ~]# ll
总用量 48784
-rw-------.  1 root root      1878 8月  11 04:02 anaconda-ks.cfg
-rw-r--r--.  1 root root      1926 8月  11 04:49 initial-setup-ks.cfg
drwxr-xr-x. 38 7161 31415     4096 10月 22 11:11 mysql-5.7.20
-rw-r--r--.  1 root root  48833145 10月 22 10:31 mysql-boost-5.7.20.tar.gz   ####
-rw-r--r--.  1 root root   1101429 11月  2 12:14 mysql-student-bak.sql
......
误删除两个库
mysql> drop database mysql;
Query OK, 32 rows affected, 2 warnings (0.04 sec)
mysql> drop database student;
Query OK, 3 rows affected, 2 warnings (0.00 sec)

恢复：无需再创建数据库，可直接恢复
mysql> source /root/mysql-auth-bak.sql
查看恢复结果
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| student            |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> select * from auth.info;
+----+--------+---------+
| id | name   | address |
+----+--------+---------+
|  1 | lisi   | 苏州    |
|  2 | liqi   | 杭州    |
|  3 | wangwu | 北京    |
+----+--------+---------+
3 rows in set (0.00 sec)

五、增量备份的实例（基于完全备份）
增量备份分类：

一般恢复将所有备份的二进制日志内容全部恢复
断点恢复

基于位置恢复：

据库在某一时间点可能既有错误的操作也有正确的操作
可以基于精准的位置跳过错误的操作

基于时间点恢复：

跳过某个发生错误的时间点实现数据恢复

MySQL二进制日志对增量备份有重要的作用


二进制日志保存了所有更新或者可能更新数据库的操作；


二进制日志在启动MySQL服务器后开始记录，并在文件达到max_binlog_size所设置的大小或者接收到flush logs命令后重新创建新的日志文件；


只需定时执行flush logs方法重新创建新的日志，生成二进制文件序列，并及时把这些日志保存到安全的地方就完成了一个时间段的增量备份。


修改配置文件，开启二进制日志功能
vi /etc/my.cnf
.....
[root@server1 ~]# [mysqld]
末尾加
log-bin=mysql-bin   前面是功能名称，后面是二进制日志文件名称
#重启数据库
[root@server1 ~]# systemctl restart mysqld
[root@server1 ~]# cd /usr/local/mysql/data/
[root@server1 data]# ll
总用量 122924
-rw-r-----. 1 mysql mysql       56 10月 22 11:29 auto.cnf
-rw-r-----. 1 mysql mysql      917 11月  2 15:45 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 11月  2 15:45 ibdata1
-rw-r-----. 1 mysql mysql 50331648 11月  2 15:45 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 10月 22 11:29 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 11月  2 15:45 ibtmp1
drwxr-x---. 2 mysql mysql     4096 11月  2 12:51 mysql
-rw-r-----. 1 mysql mysql      154 11月  2 15:45 mysql-bin.000001 #生成二进制日志文件
-rw-r-----. 1 mysql mysql       19 11月  2 15:45 mysql-bin.index

以下所有的数据库操作都会记录在mysql-bin.000001这个二进制文件中
#原有的记录，已做了完全备份
ysql> select * from auth.info;
+----+--------+---------+
| id | name   | address |
+----+--------+---------+
|  1 | lisi   | 苏州    |
|  2 | liqi   | 杭州    |
|  3 | wangwu | 北京    |
+----+--------+---------+
3 rows in set (0.00 sec)

#先做一些数据库操作
mysql> insert into auth.info values(4,'yangli','南京');
Query OK, 1 row affected (0.02 sec)

#误删除了一条记录
mysql> delete from auth.info where name='lisi';
Query OK, 1 row affected (0.01 sec)

#又插入了一条数据
mysql> insert into auth.info values(5,'lili','南京');
Query OK, 1 row affected (0.01 sec)

mysql> select * from auth.info;
+----+--------+---------+
| id | name   | address |
+----+--------+---------+
|  2 | liqi   | 杭州    |
|  3 | wangwu | 北京    |
|  4 | yangli | 南京    |
|  5 | lili   | 南京    |
+----+--------+---------+
4 rows in set (0.00 sec)
恢复：先刷新二进制文件，mysql-bin.000002里会存储后续的数据库操作，而不会在存储在bysql-bin.000001中了
[root@server1 ~]# mysqladmin -uroot -p flush-logs
Enter password: 
[root@server1 ~]# ll /usr/local/mysql/data/
总用量 122928
-rw-r-----. 1 mysql mysql       56 10月 22 11:29 auto.cnf
-rw-r-----. 1 mysql mysql      917 11月  2 15:45 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 11月  2 16:49 ibdata1
-rw-r-----. 1 mysql mysql 50331648 11月  2 16:49 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 10月 22 11:29 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 11月  2 15:45 ibtmp1
drwxr-x---. 2 mysql mysql     4096 11月  2 12:51 mysql
-rw-r-----. 1 mysql mysql     1019 11月  2 16:57 mysql-bin.000001##刷新之前的操作存储在这里
-rw-r-----. 1 mysql mysql      154 11月  2 16:57 mysql-bin.000002##新增的二进制文件
-rw-r-----. 1 mysql mysql       38 11月  2 16:57 mysql-bin.index

1.5.1、一般恢复
直接把整个二进制文件的内容进行恢复
mysql> select * from auth.info;
+----+--------+---------+
| id | name   | address |
+----+--------+---------+
|  2 | liqi   | 杭州    |
|  3 | wangwu | 北京    |
|  4 | yangli | 南京    |
|  5 | lili   | 南京    |
+----+--------+---------+
4 rows in set (0.00 sec)
误删除表后，先用完全备份恢复，在用二进制文件恢复表至上述状态
mysql> drop database auth;
Query OK, 0 rows affected (0.01 sec)
#完全备份
mysql> source /root/mysql-auth-bak.sql;
mysql> select * from auth.info;
+----+--------+---------+
| id | name   | address |
+----+--------+---------+
|  1 | lisi   | 苏州    |
|  2 | liqi   | 杭州    |
|  3 | wangwu | 北京    |
+----+--------+---------+
#一般备份
[root@server1 ~]# cd /usr/local/mysql/data/
[root@server1 data]# mysqlbinlog --no-defaults mysql-bin.000001 |mysql -uroot -pzbc123
#查看恢复结果
mysql> select * from auth.info;
+----+--------+---------+
| id | name   | address |
+----+--------+---------+
|  2 | liqi   | 杭州    |
|  3 | wangwu | 北京    |
|  4 | yangli | 南京    |
|  5 | lili   | 南京    |
+----+--------+---------+
4 rows in set (0.00 sec)
1.5.2、时间点恢复
从日志开头截止到某个时间点的恢复
mysqlbinlog [–no-defaults] --stop-datetime=’年-月-日 小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码 
从某个时间点到日志结尾的恢复
mysqlbinlog [–no-defaults] --start-datetime=’年-月-日 小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码 
从某个时间点到某个时间点的恢复
mysqlbinlog [–no-defaults] --start-datetime=’年-月-日 小时:分钟:秒’ --stop-datetime=’年-月-日小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码

MySQL 数据库的备份与恢复
数据库的备份与恢复一直都是很重要的事情。因为数据库被删除了，里面的数据如果没备份的话，里面的数据很可能会带来损失。
1. MySQL 数据库备份
首先，我们从cmd进入MySQL。



对MySQL进行查询数据库操作。



我们记住我们要备份的数据库。我们查看一下数据库中有什么表，以及表的结构。


这里，我们是准备备份mydb数据库，然后我们退出MySQL操作界面。



然后，退出后，我们输入mysqldumb -u root -p 要备份的数据库 > 备份数据库的文件地址。之后按回车键，会提示你输入密码。输入后，我们去文件资源管理器中去查看文件路径我们可以看到备份文件。





这就说明备份完成了。现在我们打开备份的文件看一看有什么东西。



备份代码会删除原来的数据库中的表，如果要来的表存在的话。然后重新再建一个数据表。这是MySQL数据库备份的步骤。
2. MySQL数据表恢复
首先，我们删除了现在这个数据库。



然后，再新建一个同名的数据库。



然后，退出MySQL操作界面，输入mysql -u root -p 要恢复的数据库名 < 刚才备份的文件。



接着，我们回到MySQL的操作界面。使用数据库，查询数据表，以及查询表结构。



由上，可知我们数据库备份成功了。这就是MySQL数据库恢复的步骤。

    哈喽~亲爱的小伙伴们大家好啊！
    没想到吧十一假期我还在哈哈哈哈哈~
    我还是你们熟悉的那个程序袁啊，上一篇呢主要讲述了如何安装和使用mysql。
    而在今天教大家一个很厉害的东西，恢复和备份。
    在我们的日常工作中呢，往往会因为多种因素导致我们的数据库丢失
    数据的丢失，所以今天就来教大家如何恢复和备份！

首先我们要安装mysql数据库，创建一个grade数据库并且录入一些数据，记不清的小伙伴可以点击这里来查看如何安装mysql数据库以及基本的使用方法，当我们录入好数据以后呢，就开始我们的教程啦！

首先备份数据库grade所有数据


模拟删除数据库grade


通过完全备份恢复数据


查看一下


数据成功恢复

还有一种二进制恢复数据

首先我们删除一行张三的数据


然后查看日志


找到我们加入张三这条数据的记录


恢复数据


张三的数据就回来啦！

亲爱的小伙伴们，备份和恢复数据你们都学会了吗？本次的教程也就到此结束啦，我是一个梦想不掉头发，不穿格子衫的程序袁，希望我的教程给你带来帮助，那我们下次再见，beybey~

公司有个哥们不小心删除了数据库，之前也没有备份，幸好mysql已经开启了binlog
1、less /etc/mysql.cnf
log-bin=mysql-bin  如果前面加了#，表示注释了，也就是没开启binlog

binlog_format=mixed

datadir = /usr/local/mysql/var       binlog日志文件所在目录，重启一次mysql都会产生一个新的binlog文件

2、通过mysqlbinlog从指定位置提取sql
mysqlbinlog --stop-position="1420697" /usr/local/mysql/var/mysql-bin.000083 > /home/Backup_1.sql

mysqlbinlog --start-position="4382351" /usr/local/mysql/var/mysql-bin.000082 > /home/Backup_2.sql

--stop表示把指定位置前面的都导出sql，--start把指定位置之后的都导出，当然是针对当前的binlog文件，不是针对所有binlog文件
如果报mysqlbinlog命令找不到，可以先找到mysql的bin目录，ps -ef|grep mysql，通过这个命令可以找到下面红色的路劲，即为mysql bin目录，然后在bin目录下执行上的命令，注意前面加./
root     10026     1  0 16:36 pts/3    00:00:00 /bin/sh
/usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/var --pid-file=/usr/local/mysql/var/localhost.localdomain.pid

mysql    10409 10026  0 16:36 pts/3    00:00:15 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/var --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/var/log/mysql/error.log
 --pid-file=/usr/local/mysql/var/localhost.localdomain.pid --socket=/tmp/mysql.sock --port=3306

3、直接在服务器上执行导入sql，注意先后顺序，-f参数标识忽略错误继续执行
mysql -u root -p -f < Backup_2.sql

mysql -u root -p -f < Backup_2.sql

4、这样做可能会影响到其他库，因为binlog里面记录了所以库的执行日志，所以最好在之心sql之前备份一下其他库，然后关闭binlog，执行sql，再执行其他库的备份sql，最后再开启binlog，关闭和开启binlog都需要重启数据库service mysql restart
5、通过crontab每天自动备份数据库，下面是脚本


#!/bin/sh   

# mysql_backup.sh: backup mysql databases and keep newest 5 days backup.   

#   

# db_user is mysql username   

# db_passwd is mysql password   

# db_host is mysql host   

# -----------------------------   

db_user="root"   

db_passwd="zhoz.com"  

db_host="localhost"  

 

# the directory for story your backup file.   

backup_dir="/home/zhozdbbackup"  

 

# date format for backup file (dd-mm-yyyy)   

time="$(date +"%d-%m-%Y")"  

 

# mysql, mysqldump and some other bin's path  

MYSQL="/usr/bin/mysql"   

MYSQLDUMP="/usr/bin/mysqldump"   

MKDIR="/bin/mkdir"  

RM="/bin/rm"  

MV="/bin/mv"  

GZIP="/bin/gzip"  

 

# check the directory for store backup is writeable   

test ! -w $backup_dir && echo "Error: $backup_dir is un-writeable." && exit 0  

 

# the directory for story the newest backup   

test ! -d "$backup_dir/backup.0/" && $MKDIR "$backup_dir/backup.0/"  

 

# get all databases   

all_db="$($MYSQL -u $db_user -h $db_host -p$db_passwd -Bse 'show databases')"   

for db in $all_db   

do   

$MYSQLDUMP -u $db_user -h $db_host -p$db_passwd $db | $GZIP -9 > "$backup_dir/backup.0/$time.$db.gz"   

done  

 

# delete the oldest backup  

test -d "$backup_dir/backup.5/" && $RM -rf "$backup_dir/backup.5"  

 

# rotate backup directory  

for int in 4 3 2 1 0   

do   

if(test -d "$backup_dir"/backup."$int")   

then   

next_int=`expr $int + 1`   

$MV "$backup_dir"/backup."$int" "$backup_dir"/backup."$next_int"   

fi   

done   

exit 0;  
6、vi /etc/crontab  在最后一行添加0 0 * * * root /bak/bakmysql/backup.sh 。重启crontab：/sbin/service crond restart 。如果没有设置crontab为开机自启动可以如下设置其开机自启动：chkconfig
 --add crond