mysql备份

docker安装mysql并持久化到宿主机,并开启二进制日志

一.首先执行命令,创建docker对mysql的映射文件

mkdir /home/mydata/mysql/{conf,data,logs,mysql-files} -p

二.在conf目录下添加配置文件，添加mysql的配置，log-bin和server_id需要，其他可以自行选择

cd /home/mydata/mysql/conf
vi my.cnf

[mysqld]
# 设置3306端口
port=3306
# 服务端使用的字符集默认为utf8mb4
character-set-server=utf8mb4
# 服务端使用的排序规则
collation-server=utf8mb4_general_ci
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
# mysql 8.0 需要设置 mysql_native_password
default_authentication_plugin=mysql_native_password
# 关闭 only_full_group_by
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

# mysql 时区
default-time-zone = '+8:00'

# 最大连接数量
max_connections=10000

# 不再进行反解析（ip不反解成域名），这样可以加快数据库的反应时间。
skip-name-resolve

# binlog 日志存放路径
log-bin=mysql-binlog
# 日志中记录每一行数据被修改的形式
binlog-format=ROW
# 当前机器的服务 ID, 如果为集群时不能重复，不要和 canal 的 slaveId 重复
server_id=1

三.使用docker启动容器，配置mysql名字，端口映射，mysql文件地址映射，密码，和mysql镜像名字

docker run -dti --name mysql -p 3306:3306 \
-v /home/mydata/mysql/conf/my.cnf:/etc/mysql/my.cnf \
-v /home/mydata/mysql/data:/var/lib/mysql \
-v /home/mydata/mysql/mysql-files:/var/lib/mysql-files \
-v /home/mydata/mysql/logs:/logs \
-e MYSQL_ROOT_PASSWORD=test123456 \
--restart=always mysql:5.7.24

四.执行备份策略前，先做好scp命令的准备

生成SSH公钥/私钥后，您需要将公钥添加到服务器上，从而使服务器可以使用该公钥来验证您的身份，一直回车就行

ssh-keygen -t rsa -b 4096

生成SSH公钥/私钥后，您需要将公钥添加到服务器上，从而使服务器可以使用该公钥来验证您的身份。-p指定端口

ssh-copy-id -p22 root@8.134.255.243

五.完全备份

因为是使用的docker部署的mysql，因此需要先进入mysql的docker容器内进行操作

docker exec -it hjt-mysql /bin/bash

在/opt目录下新建一个mysqlData文件作为备份的镜像文件夹

mkdir mysqlData

这个是备份一个数据库的命令

mysqldump -u root -p数据库密码 --databases 数据库名称 > /opt/mysqlData/数据库名称.sql

这个是备份整个数据库的命令

mysqldump -u root -p数据库密码 --all databases > /opt/mysqlData/保存后的名称.sql

备份的文件如何恢复呢？，命令如下

mysql -uroot -p 要恢复的数据库名称 </opt/mysqlData/之前备份这个要恢复的数据库名称.sql

六.增量备份

首先需要开启二进制日志功能

vim /etc/my.cnf

[mysqld]
# 添加这些，之前添加过的话这个就忽略吧
log-bin=mysql-bin
binlog_format = MIXED				# 指定二进制日志(binlog)的记录格式为 MIXED
server-id = 1

完全备份数据库的命令

# date是保存时间自动记录为当前的时间，方面以后恢复数据的时候定位
mysqldump -u root -p 数据库名称 > /opt/mysqlData/数据库名称_$(date +%F).sql

如果是备份某个数据库的某张表

mysqldump -u root -p  数据库名称 表名称  > /opt/mysqlData/数据库名称_表名称$(date +%F).sql

增量备份逻辑，先生成一个二进制日志，然后开始你的数据库操作，生成二进制日志之后的所有操作会被记录到这个日志中

mysqladmin -u root -p flush-logs

你可以通过这个命令来查看二进制日志里面记录的信息

mysqlbinlog --no-defaults --base64-output=decode-rows -v \
/var/lib/mysql/mysql-binlog.000001 #这个不是固定的，要查看生成的日志文件名具体是多少

如果想看到我们写入的sql语句格式，则需要设置 binlog_rows_query_log_events = 1

--start-position 可以通过偏移量进行查询

--start-datetime 通过开始时间来查询

--stop-datetime 通过结束时间查询

mysqlbinlog  --base64-output=decode-rows -v /var/lib/mysql/mysql-binlog.000001 --start-datetime='2022-11-23 09:18:22'

当我们想要恢复数据的时候，可以执行下面的命令，但是要注意，你执行上面的生成的binlog日志的时候，要观察生成的文件名字是多少，然后执行下面的命令来恢复

mysqlbinlog --no-defaults  /var/lib/mysql/mysql-binlog.000002  | mysql -u root -p

可以观察binlog日志内部的信息，这样可以精确的恢复到某个时间段的状态

mysqlbinlog --no-defaults  # 这里可以加start-datetime,stop-datetime,start-positon等\
/var/lib/mysql/mysql-binlog.000002  | mysql -u root -p

七.执行定时任务来定时备份

编写脚本

#!/bin/bash

# 定义变量
DB_NAME="test"
BACKUP_DIR="/root/mysql_save"
TARGET_SERVER="root@117.72.11.154" # 要进行输送的目标服务器的用户名称和ip地址
TARGET_DIR="/root/mysql_save_test"

# 当前日期
CURRENT_DATE=$(date +%F)
# 当前时间
CURRENT_TIME=$(date +%T)

# 全量备份函数
function full_backup() {
    echo "开始全量备份"
    mysqldump -u root -p$DB_PASSWORD $DB_NAME > $BACKUP_DIR/full_backup_$CURRENT_DATE.sql

    # 使用SCP传输备份文件
    scp $BACKUP_DIR/full_backup_$CURRENT_DATE.sql $TARGET_SERVER:$TARGET_DIR/
}

# 增量备份函数
function incremental_backup() {
    echo "开始增量备份"
    # 复制新的binlog文件到备份目录
    cp /home/mydata/mysql/data/mysql-bin.* $BACKUP_DIR/

    # 使用SCP传输增量备份文件
    scp $BACKUP_DIR/mysql-bin.* $TARGET_SERVER:$TARGET_DIR/
}

# 判断今天是否为周六（全量备份）
if [ $(date +%u) -eq 6 ]; then
    full_backup
else
    incremental_backup
fi

设置定时任务

crontab -e

# 每天中午12点进行增量备份
0 12 * * * /root/backup_full.sh

# 每天凌晨1点进行增量备份
0 1 * * * /root/backup_full.sh

数据恢复

SHOW BINARY LOGS;

提取binlog日志

mysqlbinlog --start-datetime="2024-03-16 22:00:00" --stop-datetime="2024-03-16 22:30:00" /path/to/binlog-file > /path/to/backup/restore.sql

将日志应用到数据库

mysql -u root -p < /path/to/backup/restore.sql