数据库的备份和恢复-CSDN博客

数据库的备份和恢复

实验

mysql忘记密码该如何操作呢

skip-grant-tables

备份完全备份  增量备份

完全备份将整个数据库完整的进行备份

增量备份在完全备份的基础之上对后续新增的内容进行备份

备份的需求

1. 在我们的生产环境中数据的安全至关重要任何数据的丢失都可能产生非常严重的后果
2. 数据为什么会丢失可能是程序操作运算错误磁盘故障不可预期的事件地震之类人为操作

冷备份关机备份停止mysql的服务然后进行备份

热备份开机备份无需关闭mysql服务进行备份

物理备份对数据库系统的物理文件数据文件日志文件进行备份

逻辑备份只是对数据库的逻辑组件进行备份表结构以sql语句的形式把库表结构表数据进行备份保存如果直接在数据库系统中删除全部文件逻辑备份无法恢复

物理备份一般采用完全备份对整个数据库进行完整的打包备份

优点操作简单

缺点数据库文件占用量是很大的1、占用空间太大2、备份和恢复的时间都很长3、需要暂定数据库服务

打包备份最好是把服务关掉避免有新的数据进入被覆盖。也可能会导致恢复失败

面试题

如何把本地的数据库迁移上云

开放式问题除了上课演示的之外

dts工具支持热迁移

热备份当中的逻辑备份

这是mysql自带的工具

mysqldump

备份单个库mysqldump -u root -p --databases kgc > /opt/test.sql

备份多个库mysqldump -u root -p123456 --databases kgc kgc1 > /opt/test1.sql

备份所有库mysqldump -u root -p123456 --all-databases > /opt/test3.sql

mysql -u root -p123456 -e ‘show databases;’

-e指定连接执行完命令之后自动退出

删除root用户里面的kgc表mysql -u root -p123456 -e ‘drop databases kgc;’

恢复数据库mysql -u root -p < /opt/kgc.sql

备份多个表mysqldump -u root -p kgc info1 info2 > /opt/kgc_info1-2.sql

恢复单个表mysql -u root -p kgc1 < /opt/kgc_info1.sql

删除多个库mysql -u root -p -e ‘drop table info1;’

mysql -u root -p -e ‘drop table info2;’

恢复数据表mysql -u root -p < /opt/kgc_info1-2.sql

实验

mysql1的全部数据库的逻辑备份文件导入到mysql2那么有重名库是否会覆盖不重名的库是否还在

主机1mysqldump -u root -p --all-databases > /opt/all.sql

主机2scp root@192.168.233.110:/opt/all.sql /opt/

物理冷备份和热备份

特点简单

数据量占用的备份空间比较大

mysqldump这是mysql自带的备份文件的命令

特点方便简单。但是只能基于逻辑上的表结构和表数据恢复。物理删除之后再用逻辑恢复会报错。并且他也可以作为数据迁移。占用大空间。比较物理备份相对来说占的空间要小得多

全量库表

增量备份

mysqldump支持增量备份

优点没有重复数据备份量小时间短

mysqldump增量备份恢复表数据期间表会锁定

缺点备份时锁表必然会影响业务。超过10G耗时会比较长导致服务不可用

增量备份的过程

1、mysql提供的二进制日志间接的实现增量备份

二进制文件怎么来的

修改配置文件

vim /etc/my.cnf

在那个=1后面添加两行

log-bin=mysql-bin

binlog_format = MIXED

mysql二进制日志记录格式有三种

1、STATEMNET基于sql语句

记录修改的sql语句在高并发的情况下记录sql语句时候的顺序可能会出错恢复数据时可能会导致丢失和误差。效率比较高

2、ROW基于行

精准记录每一行的数据。准确率高但是恢复的时效率低

3、MIXED即可以根据sql语句也可以根据行

在正常情况下使用STATEMENT一旦发生高并发会智能自动切换到ROW行

查看备份的二进制文件的内容mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002

刷新命令mysqladmin

断点mysqladmin -u root -p flush-logs

基于位置点来进行恢复

从某一个点开始恢复到最后

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、从某个时间点开始

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

2、从开头到指定的结尾时间点

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

3、指定时间范围

mysqlbinlog --no-defaults --start-datetime=’时间点’ --stop-datetime=’时间点’ 文件名 | mysql -u root -p

总结

在生产中通过binlog进行增量恢复是非常好用的方法

我们只需要对binlog文件进行备份随时可以进行备份和恢复

查看通用查询日志是否开启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;

数据库迁移上云

1、整体备份

2、sql文件如果复制到另一个库不是重命名的库是否会被覆盖相同的表名会不会覆盖相同的库名会不会覆盖

3、增量备份位置节点和时间点注意一下断点

4、一个工具xtrabackup

5、使用工具要有完整的流程从安装 使用 备份 结果 报错记录下来形成文档

yum -y install xz

查看备份的二进制日志文件全部信息mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002

还原备份日志文件的内容mysqlbinlog --no-defaults mysql-bin.000001 | mysql -u root -p