MySQL备份和恢复

2023-12-26 20:25:03

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

? vim /etc/my.cnf? ? ?#开启二进制日志功能

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秒
?
?
systemctl restart mysqld        #配置完重启服务
?
mysql -u root -p
show variables like 'general%'; ? ? ? ? ? ? ? ? ?  #查看通用查询日志是否开启
?
show variables like 'log_bin%'; ? ? ? ? ? ? ? ? ?  #查看二进制日志是否开启
?
show variables like '%slow%'; ? ? ? ? ? ? ? ? ? ?  #查看慢查询日功能是否开启
show variables like 'long_query_time'; ? ? ? ? ? ? #查看慢查询时间设置
?
set global show_query_log=ON; ? ? ? ? ? #在数据库中设置开启慢查询的方法

数据库备份的分类

物理备份

直接对数据库的物理文件(数据文件、日志文件等)进行备份

  • 物理备份方法

    • 冷备份(脱机备份)

    • 热备份(联机备份):依赖数据库的日志文件

    • 温备份:数据库锁定表格,进入只可读不可写的状态下进行备份操作

逻辑备份

  • 逻辑备份:对数据库逻辑组件(如:库、表等数据库对象),以SQL语句的形式导出进行备份。

数据库的备份策略角度

  • 完全备份:每次对数据库或表数据进行完整的备份

  • 差异备份:只备份上次完全备份后的更新数据

  • 增量备份:每次备份只备份上次完全备份或增量备份后的更新数据

数据库上云迁移

  • 冷迁移

    • 物理冷备

    • 先关闭数据库,再打包备份+恢复

  • 热迁移

    • 阿里云 DTS

数据库完全备份分类

1、物理冷备:

  • 关闭MySQL数据库

  • 使用tar命令直接打包数据库文件夹

  • 直接替换现有MySQL目录即可

  • #默认数据库文件/usr/local/mysql/data/

2、mysqldump逻辑热备



备份一个或多个库及库中所有的表数据
mysqldump -uXXX -pXXX  --databases 库1 [库2 ....] > XXX.sql      


备份所有的库
mysqldump -uXXX -pXXX  --all-databases > XXX.sql                  


备份指定库中的所有的表数据,不包含库对象
mysqldump -uXXX -pXXX  库名 > XXX.sql                            


备份指定库中的一个或多个表数据,不包含库对象
mysqldump -uXXX -pXXX  库名 表1 [表2 ....] > XXX.sql              

3、物理热备


xtrabackup

4、完全恢复

先登录到数据库,再执行source XXX.sql (如果sql文件只是备份了表数据的文件,需要先创建库并 use 切换库后再执行)

恢复库

mysql -u用户名  -p密码  < 文件名.sql         

cat 文件名.sql | mysql -u用户名 -p密码




恢复表

mysql -u用户名 -p密码 库名 < 文件名.sql    

cat 文件名.sql | mysql -u用户名 -p密码 库名

七、MySQL数据库增量恢复

1、一般恢复

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

2、基于位置恢复

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

3、基于时间点恢复

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

通过刷新二进制日志间接实现增量备份
mysqladmin -uXXX -pXXX flush-logs

查看二进制日志内容
mysqlbinlog --no-defaults --base64-output=decode-rows -v <BINLOG>

使用脚本增量备份mysql二进制日志

mysql数据库增量恢复

使用二进制日志增量恢复
mysqlbinlog --no-defaults <BINLOG> | mysql -uXXX -pXXX

断点恢复
基于位置点恢复
mysqlbinlog --no-defaults --start-position='开始位置点' --stop-position='结束位置点'  <BINLOG> | mysql -uXXX -pXXX

基于时间点恢复
mysqlbinlog --no-defaults --start-datetime='YYYY-mm-dd HH:MM:SS' --stop-position='YYYY-mm-dd HH:MM:SS'  <BINLOG> | mysql -uXXX -pXXX

如果需要恢复到某条sql语句之前的所有数据,就stop在这个语句的位置点或时间点之前
如果需要恢复某条sql语句及其之后的所有数据,就从这个语句的位置点或时间点开始start

文章来源:https://blog.csdn.net/HanYuHao11/article/details/135226511
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。