本文详细介绍了MySQL的日志管理、完全备份与恢复（包括物理冷备份、逻辑热备份和逻辑备份），以及增量备份、恢复方法，包括手动和脚本方式，还有数据库上云迁移和断点恢复的实战指南。
目录
一、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秒
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的完全备份与恢复
物理冷备份（完全备份）与恢复
[root@localhost /usr/local/mysql]#systemctl stop mysqld
[root@localhost /usr/local/mysql]#tar cf ~/backup/mysql_backup_$(date +%F).tar.gz ./data/
[root@localhost /usr/local/mysql]#cd -
/root/backup
[root@localhost ~/backup]#ls
mysql_backup_2023-12-26.tar.gz
[root@localhost ~/backup]#scp mysql_backup_2023-12-26.tar.gz 192.168.20.16:~/
[root@localhost ~]#systemctl stop mysqld
[root@localhost ~]#ls
anaconda-ks.cfg initial-setup-ks.cfg mysql_backup_2023-12-26.tar.gz 公共 模板 视频 图片 文档 下载 音乐 桌面
[root@localhost ~]#tar xf mysql_backup_2023-12-26.tar.gz
[root@localhost ~]#ls
anaconda-ks.cfg data initial-setup-ks.cfg mysql_backup_2023-12-26.tar.gz 公共 模板 视频 图片 文档 下载 音乐 桌面
[root@localhost ~]#cd /usr/local/mysql/
[root@localhost /usr/local/mysql]#ls
bin data docs include lib LICENSE man mysql-test README README-test share support-files usr
[root@localhost /usr/local/mysql]#mv data/ data_old
[root@localhost /usr/local/mysql]#mv ~/data/ data
[root@localhost /usr/local/mysql]#ls
bin data data_old docs include lib LICENSE man mysql-test README README-test share support-files usr
[root@localhost /usr/local/mysql]#systemctl start mysqld
[root@localhost /usr/local/mysql]#mysql -uroot -pabc123
数据库上云迁移的方案？
方案一：使用脱机冷备份
冷迁移----物理冷备
首先需要关闭数据库服务；
然后用tar对数据库进行打包
再将tar包传给另一台主机
然后将其解压再恢复方案二：
热迁移---阿里云自带的热迁移工具DTS（数据传输服务）
逻辑热备份（完全备份）与恢复
mysqldump实现逻辑备份
mysqldump -u用户名 -p密码 --databases 库1 [库2 ...] > /存储路径/xxx.sql
##备份库以及库中所有表的表数据及表结构
mysqldump -u用户名 -p密码 --all-databases > /存储路径/xxx.sql
##备份所有的库以及其表数据
mysqldump -u用户名 -p密码 库名 > /存储路径/xxx.sql
##只备份指定库中的所有表数据，不包含库对象
mysqldump -u用户名 -p密码 库名 表1 [表2 ] > /存储路径/xxx.sql
##备份指定库中的单个或多个表的数据和表结构，不包含库对象
mysqldump -u用户名 -p密码 -d 库名 表1 [表2 ] > /存储路径/xxx.sql
##只备份指定库中的单个或多个表的结构，不包含库对象，也不包含表的数据
方法一：
先登录到数据库，然后使用source +/存储路径/xxx.sql 进行恢复（如果sql文件是只备份了表数据的文件，那么需要先创建库，并use切换库后才能执行该命令）
方法二：
使用sql语句来进行恢复
mysql -u用户名 -p密码 < /存储路径/xxx.sql
cat /存储路径/xxx.sql | mysql -u用户名 -p密码
##恢复库
mysql -u用户名 -p密码 指定库名 < /存储路径/xxx.sql
cat /存储路径/xxx.sql | mysql -u用户名 -p密码 指定库名
##恢复库中的表，且需要库是存在的
三、MySQL的增量备份与恢复
增量备份是采用的物理二进制日志文件分割来完成增量备份的
二进制日志会定期的分割，比如当文件超过1G 会生成新的日志文件，mysql_bin.后面跟的是索引号，每次生成一个二进制文件，就会在mysql_bin.index文件中添加一个新的文件，mysql_bin.index文件中记录着所有的二进制日志文件的名称
1、手动增量备份
手动增量备份的方式有三种：
第一种：手动刷新
mysqladmin -uroot -pabc123 flush-logs
第二种：重启mysqld服务
systemctl restart mysqld
第三种：二进制日志文件的大小达到1G的时候会进行自动分割
与max_binlog_size大小相关
默认为1073741824字节等于1G
2、脚本增量备份
#!/bin/bash
##增量备份脚本
DIRLOGS=/var/log/mysql
LOGS_HOME=/usr/local/mysql/data
TODAY=$(data +%F)
YESTERDAY=$(date -d '-1 day' +%F)
USER=root
PASSWORD=abc123
#先判断收集备份文件的目录是否存在，不存在则创建目录
[ -d $DIRLOGS ]||mkdir -p $DIRLOGS
##备份二进制日志的索引文件到指定目录并加入时间标记
\cp $LOGS_HOME/mysql_bin.index $DIRLOGS/mysql_bin.index.$TODAY
#先判断昨天的二进制日志的索引文件是否存在，存在则作为过滤条件过滤出今天需要备份的二进制日志文件名称
if [ -f $DIRLOGS/mysql_bin.index.$YESTERDAY ];then
BINGLOG=$(cat $DIRLOGS/mysql_bin.index.$TODAY|grep -v $(cat $DIRLOGS/mysql_bin.index.$YESTERDAY)|awk -F/ '{print $2}')
else
BINGLOG=$(cat $DIRLOGS/mysql_bin.index.$TODAY|awk -F/ '{print $2}')
fi
##刷新生成新的二进制日志文件，便于收集最新的二进制日志
mysqladmin -u"$USER" -p"$PASSWORD" flush-logs &>/dev/null
##使用for循环，对所有的新产生的binlog文件进行备份
for i in $BINGLOG
do
mv $LOGS_HOME/$i $DIRLOGS/$i.$TODAY
done
3、增量备份恢复
mysqlbinlog --no-defaults 指定binlog |mysql -u用户名 -p密码 
4、增量备份断点恢复（请看上一篇博客）
第一种：基于位置恢复
基于位置点恢复
mysqlbinlog --no-defaults --start-position="" --stop-position="" 指定binlog |mysql -u用户名 -p密码 第二种：基于时间点恢复
基于时间点恢复
mysqlbinlog --no-defaults --start-datetime="" --stop-datetime="" 指定binlog |mysql -u用户名 -p密码 如果需要恢复到某条sql语句之前的所有数据，就stop在这个语句的位置点或时间点之前
如果需要恢复某条sql语句及其之后的所有数据，就从这个语句的位置点或时间点开始start
四、总结
备份方法
物理备份：直接对数据库的物理文件（数据文件、日志文件等）进行备份
逻辑备份：对数据库的库和表对象以SQL语言的形式导出进行备份
备份策略
完全备份：每次备份都备份完整的库或者表数据
差异备份：只备份上一次完全备份后的更新数据
增量备份：每次备份只备份上一次完全备份或增量备份后的更新数据
数据库上云迁移 冷迁移 物理冷备 先关闭数据库，再打包备份+恢复
热迁移 阿里云 DTS
完全备份
物理冷备：先关闭数据库，使用tar命令打包备份数据库的数据目录和文件 mysql/data/
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 备份指定库中的一个或多个表数据，不包含库对象
xtrabackup物理热备
完全恢复
先登录到数据库，再执行 source XXX.sql （如果sql文件是只备份了表数据的文件，需要先创建库并 use 切换库后再执行）
mysql -uXXX -pXXX < XXX.sql cat XXX.sql | mysql -uXXX -pXXX 恢复库
mysql -uXXX -pXXX 库名 < XXX.sql cat XXX.sql | mysql -uXXX -pXXX 库名 恢复表
增量备份
通过刷新二进制日志间接实现增量备份
mysqladmin -uXXX -pXXX flush-logs
查看二进制日志内容
mysqlbinlog --no-defaults --base64-output=decode-rows -v <BINLOG>
使用二进制日志增量恢复
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
扫一扫
1875
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
