• MySQL的备份恢复

    数据备份的重要性

    1.生产环境中，数据的安全至关重要

       任何数据的丢失都会导致非常严重的后果。

    2.数据为什么会丢失 ：程序操作，运算错误，磁盘故障，不可预期的事件（地震，海啸），人为操作

    数据库备份的分类和备份策略

    1.物理备份

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

    物理备份方法：

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


    2）逻辑备份

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

    即以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. #看到无法阅读，就说明没有给权限的问题

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

    1.

    Xshell与公网IP相连

    然后在云服务上进行编译安装

    将本地服务器当中需要备份的文件通过（sz   rz）拖入云服务器中

    热备份当中的逻辑备份

    用到mysql自带的工具 mysqldump

    备份命令

    1. 备份单个：
    2.  
    3. mysqldump -u root -p123456 --databases test > /opt/zzr.sql
    4.  
    5. 备份多个库：
    6.  
    7. mysqldump -u root -p 123456 --databases test test1 > /opt/test1.sql
    8.  
    9. 备份所有库：
    10.  
    11. mysqldump -u root -p123456 -all-databases > /opt/test3.sql

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

    mysql -u root -p123456 -e 'show databases;'

    进入mysql之后自动执行命令，结束后自动退出

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

    恢复命令

    mysql -u root -p < /opt/test.sql

    如何只恢复数据表

    1. 恢复单个表
    2. 先备份
    3. mysqldump -u root -p kgc info1 > /opt/kgc_info1.sql
    4. cd /opt
    5. ls
    6. mysql -u root -p -e 'drop table kgc.info1;'
    7. 指定库名恢复
    8. mysql -u -root -p kgc < /opt/kgc_info1.sql
    9.  
    10. mysqldump -u root -p kgc info1 info2 > /opt/kgc_info1-2.sql
    11. //重定向备份
    12.  
    13. mysql -u root -p -e 'drop table kgc.info1;'
    14. mysql -u root -p -e 'drop table kgc.info2;'
    15. //删表
    16.  
    17. mysql -u root -p kgc < /opt/kgc_info1-2.sql
    18. //恢复
    19.  
    20. MySQL1全部数据库的逻辑备份文件恢复到MySQL2。
    21.  
    22. scp root@20.0.0.110:/opt/all_database.sql /opt/
    23.  
    24. mysql -u root -p < all_database.sql
    25. #用sql语句的方式热备份直接转换。

    物理冷备份和物理热备份

    ：特点 简单

    数据量 占用备份空间较大

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

    特点：方便，简单，但是只能基于逻辑上的表结构和表数据恢复。物理删除之后再用逻辑恢复会报错。

    也可以作为数据迁移，必然会占用更大空间。比较物理备份，相对来说占的空间要小得多。

    增量备份

    mysqldump支持增量备份

    没有重复数据，备份量小，时间短。

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

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

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

    二进制文件怎么来？

    1. vim /etc/my.cnf
    2.  
    3. server-id=1
    4. log-bin=mysql-bin
    5. binlog_formst=MIXED
    6. wq

    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、 从某一点开始恢复到最后
    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.从某个时间点开始：

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

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

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

    3.指定时间范围

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

    时间格式：2023-11-06 11:44:32

    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. MySQL 的日志默认保存位置为 /usr/local/mysql/data
    2.  
    3. vim /etc/my.cnf
    4. [mysqld]
    5. ##错误日志，用来记录当MySQL启动、停止或运行时发生的错误信息，默认已开启
    6. log-error=/usr/local/mysql/data/mysql_error.log					#指定日志的保存位置和文件名
    7.  
    8. ##通用查询日志，用来记录MySQL的所有连接和语句，默认是关闭的
    9. general_log=ON
    10. general_log_file=/usr/local/mysql/data/mysql_general.log
    11.  
    12. ##二进制日志(binlog)，用来记录所有更新了数据或者已经潜在更新了数据的语句，记录了数据的更改，可用于数据恢复，默认已开启
    13. log-bin=mysql-bin				#也可以 log_bin=mysql-bin
    14.  
    15. ##慢查询日志，用来记录所有执行时间超过long_query_time秒的语句，可以找到哪些查询语句执行时间长，
    16. 以便于优化，默认是关闭的
    17. slow_query_log=ON
    18. slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
    19. long_query_time=5												#设置超过5秒执行的语句被记录，缺省时为10秒

    查询同用日志访问情况

    1. mysql -u root -p
    2. show variables like 'general%';									#查看通用查询日志是否开启
    3.  
    4. show variables like 'log_bin%';									#查看二进制日志是否开启
    5.  
    6. show variables like '%slow%';									#查看慢查询日功能是否开启
    7. show variables like 'long_query_time';							#查看慢查询时间设置
    8.  
    9. set global slow_query_log=ON;				#在数据库中设置开启慢查询的方法

    总结

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

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

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

  • 相关阅读:
    职场中的道德与伦理：如何在工作中坚守原则？
    html静态网站简单的学生网页作业源码 基于游戏网站设计与实现共计10个页面 (仿地下城与勇士游戏网页)
    考华为HCIE认证有什么好处？难考吗？
    南开大学漏洞报送证书【文尾有福利】
    总结开发中一些数据处理方法的封装
    Jmeter跨线程参数关联无需脚本
    时钟树综合（一）
    Maven打War包可能出现的问题
    vue2脚手架开发总结
    2.34 OrCAD中关于格点的操作是在哪里设置的？
  • 原文地址：https://blog.csdn.net/qq_51506982/article/details/134252639