mysql备份和恢复

目录

一. 数据库备份

1 数据备份的重要性

2 数据库备份的分类

1：物理备份:对数据库操作系统的物理文件(如数据文件、日志文件等)的备份

2：逻辑备份:对数据库逻辑组件(如:表等数据库对象)的备份

3：完全备份:每次对数据库进行完整的备份

 4：差异备份:备份自从上次完全备份之后被修改过的文件

 5：增量备份:只有在上次完全备份或者增量备份后被修改的文件才会被备份,缺点：恢复操作会比完全备份和差异备份复杂

 3 备份方法

1 物理冷备

2 专用备份工具 mysqldump 或 mysqlhotcopy

3 启用二进制日志进行增量备份

4 第三方工具备份

二 MySQL完全备份与恢复

1 MySQL完全备份

2 MySQL完全备份优缺点

3 数据库完全备份分类

3.1 物理冷备份与恢复

3.2 mysqldump备份与恢复

4 MySQL物理冷备份及恢复

4.1 物理冷备份

4.2 数据库被误删除恢复操作

5 mysqldump备份数据库

 5.1 mysqldump命令对单个库进行完全备份

5.2 mysqldump命令对多个库进行完全备份

 5.3 对所有库进行完全备份

 5.4 mysqldump可针对库内特定的表进行备份

 6 mysqldump完全恢复

6.1 恢复数据库

6.2 恢复数据表

三. MySQL日志管理

四. MySQL增量备份与恢复

1 增量备份

1.1 开启二进制日志功能

1.2 可每周对数据库或表进行完全备份

1.3 可每天进行增量备份操作，刷新二进制日志文件

1.4 插入新数据，以模拟数据的增加和变更

1.5 再次生成新的二进制日志文件

1.6 备份二进制日志文件 并查看内容

2 增量恢复

2.1 一般恢复

2.2 断点恢复

2.3 基于时间点的断点恢复

3 使用脚本备份

3.1 获取最新的二进制文件名称

3.2 完全备份（每周三完全备份）

3.3 增量备份文件（每天凌晨3点进行备份）

---

一. 数据库备份

1 数据备份的重要性

• 备份的主要目的是灾难恢复
• 在生产环境中，数据的安全性至关重要
• 任何数据的丢失都可能产生严重的后果
• 造成数据丢失的原因
  • 程序错误
  • 人为操作错误
  • 运算错误
  • 磁盘故障
  • 灾难(如火灾、地震)和盗窃

2 数据库备份的分类

1：物理备份:对数据库操作系统的物理文件(如数据文件、日志文件等)的备份

• 物理备份方法

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

2：逻辑备份:对数据库逻辑组件(如:表等数据库对象)的备份

3：完全备份:每次对数据库进行完整的备份

 4：差异备份:备份自从上次完全备份之后被修改过的文件

 5：增量备份:只有在上次完全备份或者增量备份后被修改的文件才会被备份,缺点：恢复操作会比完全备份和差异备份复杂

 3 备份方法

1 物理冷备

• 备份时数据库处于关闭状态，直接打包数据库文件
• 备份速度快，恢复时也是最简单的

2 专用备份工具 mysqldump 或 mysqlhotcopy

• mysqldump 常用的逻辑备份工具
• mysqlhotcopy 仅拥有备份 MyISAM 和 ARCHIVE表

3 启用二进制日志进行增量备份

进行增量备份，需要刷新二进制日志

4 第三方工具备份

免费的MySQL热备份软件Percona、XtraBackup

二 MySQL完全备份与恢复

1 MySQL完全备份

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

2 MySQL完全备份优缺点

优点

备份与恢复操作简单方便

缺点

数据存在大量的重复 占用大量的备份空间 备份与恢复时间长

3 数据库完全备份分类

3.1 物理冷备份与恢复

• 关闭MySQl数据库
• 使用tar命令直接打包数据库文件夹
• 直接替换现有MySQL目录即可

3.2 mysqldump备份与恢复

• MySQL自带的备份工具，可方便实现对MySQL的备份
• 可以将指定的库、表导出为SQL脚本
• 使用命令mysql导入备份的数据

4 MySQL物理冷备份及恢复

4.1 物理冷备份

使用物理冷备份必须先关闭MySQL服务

[root@localhost ~]# systemctl stop mysqld.service

[root@localhost ~]# mkdir /backup

[root@localhost ~]# tar Jcvf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/

4.2 数据库被误删除恢复操作

[root@localhost ~]# cd /usr/local/mysql/

[root@localhost mysql]# mv data data.bak

[root@localhost mysql]# tar Jxf /backup/mysql_all-2022-10-26.tar.gz -C /opt/ [root@localhost ~]# mv /opt/usr/local/mysql/data/ /usr/local/mysql/

[root@localhost ~]# systemctl start mysqld

[root@localhost ~]# mysql -uroot -pabc123

show databases;

5 mysqldump备份数据库

[root@localhost backup]# mysql -uroot -pabc123 -e "show databases;"

 5.1 mysqldump命令对单个库进行完全备份

mysqldump -u 用户名 -p [密码] [选项] [数据库名] > /备份路径/备份文件名

单库备份的实例

mysqldump -u root -pabc123 can > /backup/can.sql

mysqldump -u root -pabc123 mysql > /bakcup/mysql.sql

 mysqldump 在备份库的时候加 --databases 与不加的区别

//加--databases

[root@localhost backup]# mysqldump -u root -pabc123 --databases can > /backup/can01.sql

[root@localhost backup]# cat can01.sql | egrep -v "(^--|^/|^$)"

 

//不加

mysqldump -u root -pabc123 can > /backup/can02.sql

[root@localhost backup]# cat can02.sql | egrep -v "(^--|^/|^$)"

 

5.2 mysqldump命令对多个库进行完全备份

mysqldump -u用户名 -p [密码] [选项] --databases 库名1 [库名2]... > /备份路径/备份文件名

多库备份的示例

mysqldump -u root -pabc123 --databases can mysql > /backup/databases_can_mysql.sql

 5.3 对所有库进行完全备份

mysqldump -u 用户名-p [密码] [选项] --all-databases > /备份路径/备份文件名

所有库备份的示例

mysqldump -u root -pabc123 --all-databases > /backup/all-data.sql

 5.4 mysqldump可针对库内特定的表进行备份

使用mysqldump备份表的操作

mysqldump -u 用户名 -p [密码] [选项] 数据库名表名> /备份路径/备份文件名

备份表的示例

mysqldump -u root -pabc123 mysql user > /backup/mysql-user.sql

-d 只保存表结构

 6 mysqldump完全恢复

6.1 恢复数据库

先删除数据库，之后进行恢复

##删除数据库sly##

mysql -u root -p -e 'drop database sly;'

#"-e"选项，用于指定连接MySQL后执行的命令，命令执行完后自动退出

mysql -u root -p -e 'show databases;'         #查看当前有哪些数据库

##恢复数据库sly##

mysql -u root -p < /opt/backup/bbc.sql     #重定向输入库文件

mysql -u root -p -e 'show databases;'       #查看当前有哪些数据库

6.2 恢复数据表

当备份文件中只包含表的备份，而不包含创建的库的语句时，执行导入操作时必须指定库名，且目标库必须存在

##备份sly库中的account表##

mysqldump -u root -p sly account > /opt/backup/sly_account.sql

##删除sly库中的account表##

mysql -u root -p -e 'drop table can.account;'

mysql -u root -p -e 'show tables from sly;'     #查看sly库中的表，已无account表

##恢复sly库中的account表##

mysql -u root -p sly < /opt/backup/can_account.sql   #重定向导入备份文件，必须指定库名，且目标库必须存在

mysql -u root -p -e 'show tables from sly;'

三. MySQL日志管理

MySQL的日志默认保存位置为/usr/local/mysql/data

vim /etc/my.cnf

[mysqld]

##错误日志，用来记录当MySQL启动、停止或运行时发生的错误信息，默认已开启

log-error=/usr/local/mysql/data/mysql_error.log #指定日志的保存位置和文件名

##通用查询日志，用来记录MySQL的所有连接和语句，默认足关闭的

general_log=ON

general_log_file=/usr/local/mysql/data/mysql_general.log

##二进制日志(binlog)，用来记录所有更新了数据或者已经潜在更新了数据的语句，记录了数据的更改，可用于数据恢复，默认已开启:

log-bin=mysql-bin #也可以log_bin=mysql-bin

##慢查询日志，用来记录所有执行时问超过long_query_time秒的语句， 可以找到哪些查询语句执行时间长，以便于优化，默认是关闭的

slow_query_log=ON

slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log

long_query_time=5 #设置超过5秒执行的语句被记录，缺省时为10秒

[mysqld]

log-bin=mysql-bin

binlog_format=STATEMENT|ROW|MIXED

STATEMENT(基于SQL语句记录二进制日志，写入和恢复速度较快，在高并发的情况可能会出SQL语句顺序记录偏差导致恢复时数据丢失) 老版本的默认模式

ROW（基于行记录二进制日志，写入和恢复速度较STATEMENT慢些，日志文件占用空间也较大，但是准确性较高）5.7的默认模式

MIXED（混合模式，平时负载压力较小时使用SQL语句记录二进制日志，在高并发的情况会切换成基于行记录二进制日志）

systemctl restart mysqld

mysql -uroot -p

show variables like 'general%'; //查看通用查询日志是否开启

 show variables like 'log_bin%'; //查看二进制日志是否开启

 show variables like '%slow%'; //查看慢查询日功能是否开启

 show variables like 'long_query_time'; //查看慢查询时间设置 set global slow_query_log=ON; //在数据库中设置开启慢查询的方法

四. MySQL增量备份与恢复

1 增量备份

1.1 开启二进制日志功能

vim /etc/my.cnf

 [mysqld]  log-bin=mysql-bin   #开启二进制日志。如果使用相对路径，则保存在/usr/local/mysql/data/目录下  

binlog_format = MIXED     #可选，指定二进制日志(binlog)的记录格式为MIXED

1.2 可每周对数据库或表进行完全备份

mysqldump -u root -p can test01 > /opt/backup/can_test01_$(date +%F).sql

mysqldump -u root -p --databases can > /opt/backup/can_$(date +%F).sql

1.3 可每天进行增量备份操作，刷新二进制日志文件

mysqladmin -u root -p flush-logs

1.4 插入新数据，以模拟数据的增加和变更

user can;

insert into test01 values(4,'zhangan',25,'男');

insert into test01 values(5,'lisi',18,'女');

1.5 再次生成新的二进制日志文件

mysqladmin -u root -p flush-logs

#之前的步骤4的数据库操作会保存到mysql-bin.000002文件中，之后数据库数据再发生变化则保存在mysql-bin.00003文件中

1.6 备份二进制日志文件 并查看内容

mv /usr/local/mysql/data/mysql-bin.000002 /opt/backup

mysqlbinlog --no-defaults --base64-output=decode-rows -v /bak/mysql-bin.000002

#--base64-output=decode-rows：使用64位编码机制去解码并按行读取

#-v：显示详细内容

2 增量恢复

一般恢复

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

基于位置恢复

数据库在某一时间点可能既有错误的操作也有正确的操作

可以基于精准的位置跳过错误的操作

基于时间点恢复

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

2.1 一般恢复

模拟丢失更改数据的恢复

user can;

delete from class where id=4; ##删除新增数据

delete from class where id=5;

##增量恢复

mysqlbinlog --no-defaults /opt/backup/mysql-bin.000002 | mysql -u root -p

mysqlbinlog --no-defaults /opt/backup/mysql-bin.000002 | mysql -u root -p

模拟丢失表中的所有的数据的恢复步骤

mysql -u root -p -e 'drop table can.test01;' ##删除整个test01表

mysql -u root -p -e 'show tables from can;'   #查看can库中的表

##先完全恢复历史数据

mysql -u root -p can < /opt/backup/can_2022.10.27.sql

##再进行增量恢复今天新增的两条数据

mysqlbinlog --no-defaults /opt/backup/mysql-bin.000002 | mysql -u root -p

mysql -u root -p -e 'select * from can.test01;' ##查看表中数据

2.2 断点恢复

use can;

insert into class values(6,'哈哈哈',29,'男');

insert into class values(7,'嘿嘿嘿',19,'男');

insert into class values(8,'吼吼吼',49,'男');

mysqladmin -u root -p flush-logs       #刷新日志，生成新的二进制日志

mv /usr/local/mysql/data/mysql-bin.000003 /opt/backup/   #将前一个日志移动到/opt/backup/目录下

mysqlbinlog --no-defaults --base64-output=decode-rows -v /bak/mysql-bin.000003 > /opt/backup/binlog.txt #将二进制文件内容重定向到binlog.txt文件中

基于位置的断点恢复

mysqlbinlog --no-defaults --start-position='位置点' 文件名 | mysql -u root -p

#从某一个位置点开始恢复，一直到日志结尾

mysqlbinlog --no-defaults --stop-position='位置点' 文件名 | mysql -u root -p

#从日志开头，一直恢复到某一个位置点前结束

mysqlbinlog --no-defaults --start-position='位置点' --stop-position='位置点' 文件名 | mysql -u root -p

#从某一个位置点开始恢复，一直到某一个位置点前结束

示例1：

use can;

delete from class where id=6;

delete from class where id=7; ##删除id为6-8的数据记录

delete from class where id=8;

cat /opt/backup/binlog.txt ##查看二进制文件内容，找到id=6数据的位置点

##从6的位置点恢复数据

mysqlbinlog --no-defaults --start-position='位置点' /opt/backup/mysql-bin.000003 | mysql -u root -p

mysql -u root -p -e 'select * from can.test01;' ##查看表中数据

示例2

只恢复到id=7的数据

mysqlbinlog --no-defaults --stop-position='位置点' 文件名 | mysql -u root -p

mysql -u root -p -e 'select * from can.test01;' ##查看表中数据

示例3

恢复6-7行数据

mysqlbinlog --no-defults --start-position='位置点' --stop-position='位置点' 文件名 | mysql -u root -p

mysql -u root -p -e 'select * from can.test01;' ##查看表中数据

2.3 基于时间点的断点恢复

注意：日期格式为：yyyy-mm-dd hh:mm:ss

mysqlbinlog --no-defults --start-datetime='时间点' 文件名 | mysql -u root -p

##从某一个时间点开始恢复，一直到日志结尾

mysqlbinlog --no-defults --stop-datetime='时间点' 文件名 | mysql -u root -p

##从日志开头，一直恢复到某一个时间点前结束

mysqlbinlog --no-defults --start-datetime='开始时间点' --stop-datetime='结束时间点' 文件名 | mysql -u root -p

#从某一个时间点开始恢复，一直到某一个位置点前结束

示例1

use bbc;

delete from class where id=6;

delete from class where id=7; ##删除id为6-8的数据记录

delete from class where id=8;

cat /opt/backup/binlog.txt ##查看二进制文件内容，找到id=6数据的时间点

##从6的时间点恢复数据

mysqlbinlog --no-defaults --start-datetime='位置点' 文件名 | mysql -u root -p

mysql -u root -p -e 'select * from bbc.class;' ##查看表中数据

示例2：

只恢复到id=7的数据

mysqlbinlog --no-defults --stop-datetime='时间点' 文件名 | mysql -u root -p

mysql -u root -p -e 'select * from can.test01;' ##查看表中数据

示例3：

mysqlbinlog --no-defults --start-datetime='开始时间点' --stop-datetime='结束时间点' 文件名 | mysql -u root -p

mysql -u root -p -e 'select * from bbc.class;' ##查看表中数据

3 使用脚本备份

使用脚本每周三进行完全备份，之后每天进行增量备份

3.1 获取最新的二进制文件名称

#查看索引文件 获取最新的二进制文件

tail -1 /usr/local/mysql/data/mysql-bin.index

或者

sed -n '$p' /usr/local/mysql/data/mysql-bin.index

3.2 完全备份（每周三完全备份）

vim a.sh

#!/bin/bash

mysqldump -u root -p --all-databases > /bak/all_data_$(date +%F).sql

chmod +x wanquan.sh

crontab -e

0 3 * * 3 /root/a.sh ##每周三凌晨三点进行完全备份

3.3 增量备份文件（每天凌晨3点进行备份）

vim b.sh

#!/bin/bash

##使用tail命令打印出日志文件的最后一行内容作为name变量的值

name=$(tail -1 /usr/local/mysql/data/mysql-bin.index | awk -F'/' '{print $2}')

##生成新的二进制文件

mysqladmin -uroot -pabc123 flush-logs

##将二进制文件移动到备份目录下 并加上日期

mv /usr/local/mysql/data/$name /opt/backup/bin_$(data_ +%F)

chmod +x b.sh

crontab -e

0 3 * * 3 /root/a.sh ##每周三凌晨三点进行完全备份

0 3 * * * /root/b.sh ##每天凌晨三点进行增量备份