• MySQL数据库的备份和恢复

    备份

    完全备份和完全备份

    完全备份

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

    增量备份

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

    备份的需求

    1、 在生产环境中数据的安全至关重要，任何数据的丢失都可能产生严重的后果。

    2、 数据为什么会丢失，例如：可能是程序操作、运算错误、磁盘故障、不可预测事件（地震、海啸之类）、人为操作等。

    备份的方法

    冷备份：关机备份。停止MySQL的服务，然后进行备份

    热备份：开机备份。无需关闭MySQL的服务，进行备份

    物理备份：对数据系统的物理文件(数据文件、日志文件)进行备份

    逻辑备份：对组件进行备份(表结构)，以sql语句的形式，把库，表结构，表数据进行备份保存。（直接在数据库中删除全部文件，逻辑备份无法恢复）

    物理备份

    物理备份：完全备份，对整个数据库进行完整的打包备份。

    优点：操作简单 缺点：数据库文件占用量很大。占用空间太大，备份和恢复的时间都很长，而且需要站厅数据库服务。

    如何进行物理备份

    1. systemctl stop mysqld
    2. #关闭MySQL1的服务器。因为关闭服务可以避免新的数据插入或者数据覆盖
    3.  
    4. cd usr/local/mysql/
    5.  
    6. cd data
    7.  
    8. cd kgc
    9.  
    10. 安装软件包
    11.  
    12. tar Jcvf /opt/mysql_all_$(data +%F).tar.xz /usr/local/mysql/data/
    13. #打包data的内容
    14.  
    15. ls
    16.  
    17. tar -Jxvf mysql_all_
    18. #解压文件
    19.  
    20. cd usr/local/mysql/
    21.  
    22. rm -rf data
    23. #删库跑路
    24.  
    25. cd /opt/
    26.  
    27. cd usr/local/mysql
    28.  
    29. cp -a data/ /usr/local/mysql
    30. #保留权限复制
    31.  
    32. systemctl restart mysqld
    33. #重启mysql服务
    34.  
    35. 到另外一台库
    36.  
    37. cd /usr/local/mysql/
    38.  
    39. cp -a data/ /opt/
    40. #MySQL2自己先做一个文件备份
    41.  
    42. scp -r root@20.0.0.60:/opt/usr/local/mysql/data /opt/
    43. #远程复制到MySQL2
    44.  
    45. cp -a data/ /usr/local/mysql/data
    46. #保留权限复制到MySQL2
    47.  
    48. systemctl restart mysqld
    49. #重启服务登录一下
    50.  
    51. chown -R mysel.mysql kgc kgc1
    52. #递归给两个库更改归属
    53. #看到无法阅读，就说明没有给权限的问题

    到另外一台主机MySQL2

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

    基于热备份的逻辑备份

    MySQL自带的工具： MySQLdump：用于逻辑恢复库、表结构表数据。

    1. 在命令行操作：
    2. mysqldump -u root -p --databases 库名 > /opt/文件名.sql
    3. #备份单个库
    4. mysqldump -u root -p --databases 库1 库2 > /opt/文件名2.sql
    5. #多个备份
    6. mysqldump -u root -p --all-databases > /opt/文件名3.sql
    7. #备份所有库

    只是逻辑上的备份

    1. systemctl restart mysqld
    2.  
    3. netstart -antp | grep 3306
    4.  
    5. mysqldump -u root -p --databases kgc > /opt/kgc.sql
    6. #把kgc库备份到/opt、目录下
    7.  
    8. cd /usr/local/mysql/data
    9. rm -rf kgc
    10. #物理删库
    11.  
    12. mysql -u root -p < /opt/kgc.sql
    13. #会直接报错，库会回来，但是kgc库里面的表结构消失了。只能使用逻辑删除（在命令行删除）
    14.  
    15. mysql -u root -p123456 -e 'show databases;'
    16. #-e：表示连接指定MySQL之后执行完命令后就自动退出
    17. #进入MySQL执行一次show databases命令后就退出
    18.  
    19. mysql -u root -p -e 'drop database kgc;'
    20. #需要手动数据密码
    21.  
    22. mysql -u root -p < /opt/kgc.sql
    23. #手动输入密码
    24.  
    25. 对多个库一次性备份
    26. 先备份多个库
    27. mysqldump -u root -p --databases kgc kgc1 > /opt/kgc_all.sql
    28. #手动输入密码。保存多个库
    29.  
    30. mysql -u root -p -e 'drop database kgc;'
    31. mysql -u root -p -e 'drop database kgc1;'
    32. #手动输入密码。模拟删除库
    33.  
    34. mysqldump -u root -p < /opt/kgc_all.sql
    35. #手动输入密码。一次性恢复多个库
    36.  
    37. 对全部库一次性备份
    38. mysqldump -r root -p --all-databases > /opt/all_database.sql
    39. #备份全部

    备份单个库还原

    备份多个库还原

    1. mysql -u root -p123456 -e 'show databases;'
    2. #-e：表示连接指定MySQL之后执行完命令后就自动退出
    3. ​​​​​​​#进入MySQL执行一次show databases命令后就退出

    如何恢复数据表

    1. 只恢复单个表或者多个表：
    2. 准备另外一张表插入数据
    3. 恢复一张表：
    4. mysqldump -u root -p kgc info1 > opt/kgc_info1.sql
    5.  
    6. msql -u root -p -e 'drop table kgc.info1;'
    7. #指定库删除表
    8.  
    9. mysql -u root -p kgc < /opt/kgc_info1.sql
    10. #指定库名恢复
    11. #刷新一下查看一下
    12.  
    13. 恢复多个表：
    14. mysqldump -u root -p kgc info1 info2 > /opt/kgc_info1-2.sql
    15.  
    16. msql -u root -p -e 'drop table kgc.info1;'
    17.  
    18. msql -u root -p -e 'drop table kgc.info2;'
    19.  
    20. mysql -u root -p kgc < /opt/kgc_info1-2.sql
    21. #指定库名恢复
    22. #刷新一下查看一下
    23.  
    24. MySQL1全部数据库的逻辑备份文件恢复到MySQL2。
    25.  
    26. scp root@20.0.0.60:/opt/all_database.sql /opt/
    27.  
    28. mysql -u root -p < all_database.sql
    29. #用sql语句的方式热备份直接转换。
    30. #会覆盖掉MySQL2的全部数据

    恢复单个表

    恢复多个表：

    物理冷备份和热备份 特点：简单 数据量，占用的备份空间大 mysqldump：这是MySQL自带的备份文件命令 特点：方便，简单。但是只能是基于逻辑上的表结构和表数据恢复。物理删除之后再用逻辑恢复会报错。也可也作为数据迁移。会占用大空间。比较物理备份，相对来说，占用的空间小得多。

    百G之内使用逻辑备份。百G以上建议使用工具。

    增量备份

    增量备份： mysqldump：支持增量备份 特点：没有重复数据，备份量小，时间短。 MySQL增量备份恢复表数据期间，表会锁定。可以查但是无法写入。 缺点：备份时锁表，必然会影响业务。超过10G，耗时会比较长。导致服务不可用。

    增量备份的过程

    1、 MySQL提供的二进制日志来间接的实现增量备份 二进制文件怎么来？ 答：修改配置文件

    1. vim /etc/my.conf
    2. #插入内容
    3. log-bin=mysql-bin
    4.  
    5. binlog_format=MIXED
    6. #表示混合模式
    7. systemctl restart mysqld

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

    1、 STATEMNET：基于sql语句。是记录修改的sql语句，在高并发情况下记录sql语句时的顺序可能会出错。数据恢复可能会导致丢失或者误差。效率比较高，但是不适合高并发场景。

    2、 ROW：基于行。会精准记录每一行的数据，准确率高但是回复的时效率低。

    3、 MIXED：混合模式。既可以根据语句，也可也根据行。在正常情况下使用STATEMNET，一旦发生高并发，会智能的切换到ROW模式，虽然效率低但是一定不会出错。

    1. 表数据设置多一点
    2.  
    3. select * from info1;
    4. #查看数据是否写入
    5.  
    6. cd /usr/local/mysql/data/
    7. #会生成了两个文件
    8. mysql -bin.index
    9. mysql-bin.000001
    10. 表内写入信息后再查看日志文件 mysql-bin.000001
    11. mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000001
    12. #查看新插入表的日志
    13.  
    14. mysqladmin -u root -p flush-logs
    15. #刷新日志.
    16. #此时data目录下会生成一个新的日志文件mysql-bin.000002
    17.  
    18. mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002
    19. #刷新之后更新的内容会更新在2里面这就是断点
    20.  
    21. 如何恢复：
    22. mysqlbinlog --no-defaults mysql-bin00000.1 | mysql -u root -p
    23. #增量备份，恢复之前表内插入的数据。这个叫断点恢复。
    24.  
    25.  
    26. 此时再对表插入信息。此时新插入的数据再000002里面。只要没有刷新日志就不会出现断点。会先插入再删除。
    27. mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002
    28.  
    29. 如果需要备份新的数据之前需要再刷新一次。
    30. mysqladmin -u root -p flush-logs
    31. #刷新日志.
    32.  
    33. 重新在表内插入数据
    34. 此时断点之后数据都在新生成的000003里面
    35. mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002
    36.  
    37. mysqladmin -u root -p flush-logs
    38. #刷新日志断点
    39.  
    40. 再删除表内数据，这时候删除的操作会保存到000004里面
    41.  
    42. mysqlbinlog --no-defaults --base64-out=decode-rows -v mysql-bin.000003

    位置恢复

    三种方式：

    1、 从某一点开始恢复到最后

    2、 从开头一直恢复到某个位置

    3、 从指定点开始恢复到指定的结束点

    1. 删除表内数据测试
    2. 基于位置点进行恢复：
    3. 1、 从某一点开始恢复到最后
    4. 格式：
    5. mysqlbinlog --no-defaults --start-position='位置点' 文件名 | mysql -u root -p
    6.  
    7. 2、 从开头一直恢复到某个位置
    8. 格式：
    9. mysqlbinlog --no-defaults --stop-position='位置点' 文件名 | mysql -u root -p
    10.  
    11. 3、 从指定点开始恢复到指定的结束点
    12. 格式：
    13. mysqlbinlog --no-defaults --start-position='位置点' --stop-position='位置点' 文件名 | mysql -u root -p
    14.  
    15. mysqladmin -u root -p flush-logs
    16. #刷新日志断点
    17.  
    18. 删除表内数据此时数据再000005上
    19.  
    20. mysqlbinlog --no-defaults --start-position='6171' mysql-bin.000004 | mysql -u root -p
    21.  
    22. 查看位置点
    23. #at后面的数字就是位置点。要选择commit后面的位置点
    24. mysqlbinlog --no-defaults --base64-out=decode-rows -v mysql-bin.000004
    25. #查看000004日志内的位置点
    26.  
    27. 从开头一直恢复到某个位置：
    28. mysqlbinlog --no-defaults --stop-position='6106' mysql-bin.000004 | mysql -u root -p
    29. #从头恢复到指定点
    30.  
    31. 范围恢复：
    32. mysqlbinlog --no-defaults --start-position='6106' --stop-position='6748' mysql-bin.000004 | mysql -u root -p
    33. #指定位置到指定位置恢复

    时间恢复

    基于时间点进行恢复

    1、 从某个时间点开始

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

    3、 指定时间范围

    1. 基于时间点恢复：
    2. YYY-MM-DD 11:11:11 时间格式要按照标准格式来写
    3. 1、 从某个时间点开始
    4. 格式：mysqlbinlog --no-defaults --start-datettime='时间点' 文件名 | mysql -u root -p
    5.  
    6. 2、 从开头到指定结尾时间点
    7. 格式：mysqlbinlog --no-defaults --stop-datettime='时间点' 文件名 | mysql -u root -p
    8.  
    9. 3、 指定时间范围
    10. 格式：mysqlbinlog --no-defaults --start-datettime='时间点' --stop-datettime='时间点' 文件名 | mysql -u root -p
    11.  
    12. 从某个时间点开始
    13. mysqlbinlog --no-defaults --start-datetime='2023-11-06 11:44:32' mysql-bin.000004 | mysql -u root -p
    14. #从某个时间点开始
    15.  
    16. 从开头到指定结尾时间点
    17. mysqlbinlog --no-defaults --stop-datetime='2023-11-06 11:44:32' mysql-bin.000004 | mysql -u root -p
    18. #从头开始到某个时间结束
    19.  
    20. 指定时间范围
    21. mysqlbinlog --no-defaults --start-datetime='2023-11-06 11:44:37' --stop-datetime='2023-11-06 11:49:05' mysql-bin.000004 | mysql -u root -p
    22. #从某个时间开始到某个时间结束

    日志单独存放的规则

    1. 单独存放错误日志
    2. vim /etc/my.conf
    3. log-error=/usr/local/mysql/data/mysql_error.log
    4. #单独存放错误日志
    5.  
    6. 单独存放通用访问日志
    7. general_log=ON
    8. general_log_file=/usr/local/mysql/data/mysql_general.log
    9. #单独存放通用访问日志
    10.  
    11. 开启慢查询日志
    12. slow_query_log=ON
    13. slow_query_log_file=/usr/local/mysql/data/mysql_show_query.log
    14. long_query_time=5
    15. #设置超时时间5秒
    16. #开启慢查询日志.所有超过5秒的都会被记录在内
    17.  
    18. systemctl restart mysqld

    查询通用日志开始情况

    1. show variables like 'general%';
    2. #查询是否开启通用日志访问记录
    3.  
    4. show variables like 'log_bin%';
    5. #查询二进制日志是否开启
    6.  
    7. show variables like 'slow%';
    8. #查询是是否开启慢查询访问日志

    总结

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

    我们只需要对binlog文件进行备份，随时可以进行备份和恢复。（只能是root用户来进行操作）

    除非有特殊需要一般不打额外断点，具体根据需求来，一般是通过脚本按月来设置断点。

  • 相关阅读:
    深度学习之基于YoloV5抽烟检测系统
    开源软件的崛起：历史与未来
    JMeter之入门
    【Java笔试强训】Day9（CM72 另类加法、HJ91 走方格的方案数）
    SQLAlchemy学习-8.query查找之filter()和filter_by()区别
    Channel 是什么？
    oracle异常处理
    LQ0250 n进制小数【程序填空】
    【C++编程语言】之类和对象---对象初始化和清除
    1700*C. Number of Ways（贪心&前缀和）
  • 原文地址：https://blog.csdn.net/m0_75209491/article/details/134245060