MySQL数据库增量备份

realmadrid_juejin
696 阅读3分钟

前言

数据库是一个系统的核心，保证数据库数据安全非常重要。常用的数据库备份方法有全量备份、增量备份。mysql的增量备份依赖于binlog，binlog中保存了每一步的修改操作。增量备份就是对binlog日志进行备份。

开启binlog

mysql8.0默认开启binlog，不需要修改配置。对于mysql5.7需要修改配置文件/etc/my.cnf

log-bin=/data/mysql/mysql-binlog

重启mysql服务器

service mysql restart

查看是否开启biglog

mysql> show variables like '%log_bin%';
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |
| log_bin_basename                | /var/lib/mysql/binlog       |
| log_bin_index                   | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF                         |
| log_bin_use_v1_row_events       | OFF                         |
| sql_log_bin                     | ON                          |
+---------------------------------+-----------------------------+

设置login-path

mysql_config_editor set --login-path=dumppath --user=root  --host=127.0.0.1 --port=3306 --passwo
rd

全量备份脚本allBackMysql.sh

#!/bin/bash
dumptime=$(date +%H%M%S)
dumpdate=$(date +%y%m%d)
basePath=/usr/local/src/mysql/backup
path=$basePath/all/$dumpdate
echo $dumpdate $dumptime start... >> $basePath/back.log
# 创建目录
if [ ! -d $path  ];then
 mkdir -p $path
fi
/usr/bin/mysqldump --login-path=dumppath  --all-databases --quick --events --flush-logs --delete-source-logs --single-transaction > ${path}/all-${dumptime}.sql
echo $dumpdate $dumptime end... >> $basePath/back.log

--quick，-q 该选项在导出大表时很有用，它强制 MySQLdump 从服务器查询取得记录直接输出而不是取得所有记录后将它们缓存到内存中。

--events, -E 导出事件

--all-databases , -A 出全部数据库。

--flush-logs 开始导出之前刷新日志，必须,我们需要开启一个新的日志文件。

--delete-source-logs MySQL 8.0.26引入，备份之前刷新日志，相当于执行flush logs，执行dump操作之后通过发送perge binary logs删除binlog，该选项自动激活--source-data选项

--single-transaction 该选项在导出数据之前提交一个 BEGIN SQL 语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于多版本存储引擎，仅 InnoDB。本选项和—lock-tables 选项是互斥的，因 LOCK TABLES 会使任何挂起的事务隐含提交。要想导出大表的话，应结合使用--quick选项。

其它选项

--no-create-db，  ---取消创建数据库sql(默认存在)
--no-create-info，---取消创建表sql(默认存在)
--no-data         ---不导出数据(默认导出)
--add-drop-database ---增加删除数据库sql（默认不存在）
--skip-add-drop-table  ---取消每个数据表创建之前添加drop数据表语句(默认每个表之前存在drop语句)
--skip-add-locks       ---取消在每个表导出之前增加LOCK TABLES（默认存在锁）
--skip-comments        ---注释信息(默认存在)

添加可执行权限

chmod +x allBackMysql.sh

自己可以顺便测试一下脚本是否可以执行

[root@localhost mysql]# ls /var/lib/mysql
auto.cnf       ca.pem             #ib_16384_1.dblwr  ib_logfile1   mysql.ibd           private_key.pem  sys
binlog.000001  client-cert.pem    ib_buffer_pool     ibtmp1        mysql.sock          public_key.pem   undo_001
binlog.index   client-key.pem     ibdata1            #innodb_temp  mysql.sock.lock     server-cert.pem  undo_002
ca-key.pem     #ib_16384_0.dblwr  ib_logfile0        mysql         performance_schema  server-key.pem
[root@localhost mysql]# ./allBackMysql.sh
[root@localhost mysql]# ls /var/lib/mysql
auto.cnf       ca.pem             #ib_16384_1.dblwr  ib_logfile1   mysql.ibd           private_key.pem  sys
binlog.000002  client-cert.pem    ib_buffer_pool     ibtmp1        mysql.sock          public_key.pem   undo_001
binlog.index   client-key.pem     ibdata1            #innodb_temp  mysql.sock.lock     server-cert.pem  undo_002
ca-key.pem     #ib_16384_0.dblwr  ib_logfile0        mysql         performance_schema  server-key.pem
[root@localhost mysql]# head backup/all/220620/all-201408.sql
-- MySQL dump 10.13  Distrib 8.0.29, for Linux (x86_64)
--
-- Host: 127.0.0.1    Database:
-- ------------------------------------------------------
-- Server version       8.0.29

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;

增量备份的脚本

#!/bin/bash
export LANG=en_US.UTF-8
basePath=/usr/local/src/mysql/backup
bakAddDir=$basePath/add/`date +"%Y%m"`
# 创建目录
mkdir -p $bakAddDir
# 日志文件
logFile=$basePath/back.log
binlogDir=/var/lib/mysql
indexFile=$binlogDir/binlog.index
echo add start $bakAddDir >> $logFile
# 刷新日志
/usr/bin/mysqladmin --login-path=dumppath flush-logs
#这个是用于产生新的mysql-bin.00000*文件
index=`wc -l $indexFile |awk '{print $1}'`
next=0
#循环处理
for file in `cat $indexFile`
do
    base=`basename $file`
    #basename用于截取mysql-bin.00000*文件名，去掉./mysql-bin.000002前面的./
    next=`expr $next + 1`
    if [ $next -eq $index ]
    then
        echo $base skip... >> $logFile
    else
     dest=$bakAddDir/$base
     if test -e $dest
        then
            echo $base exist file >> $logFile
        else
            cp $binlogDir/$base $bakAddDir
            echo $base copying file success >> $logFile
        fi
    fi
done
echo `date +"%Y%m%d %H:%M:%S"` back success >> $logFile
echo add end $bakAddDir >> $logFile

增加可执行权限

 chmod +x addBackMysql.sh

添加定时

[root@localhost mysql]# crontab -l
0 1 * * 0 /usr/local/src/mysql/allBackMysql.sh
0 1 * * 1-6 /usr/local/src/mysql/addBackMysql.sh

每周星期天全量备份，周一到周六增量备份

恢复

根据实际情况需要先恢复指定日期的全量备份，然后执行全量日期后的增量备份日志

# 使用命令查看执行语句
mysqlbinlog binlog.000001 | grep -n  "drop database"
# 使用mysqlbinlog命令恢复指定位置范围的数据
mysqlbinlog -v  binlog.000001  --start-position=233  --stop-position=1371 | mysql
avatar
文章
阅读
粉丝