Home Database Mysql Tutorial Introduction to the implementation ideas and methods of incremental backup of Mysql database

Introduction to the implementation ideas and methods of incremental backup of Mysql database

May 25, 2018 am 10:24 AM
mysql accomplish database

MySQL数据库增量备份,在这之前修改我们的数据库配置文件/etc/my.cnf开启bin-log日志功能即可,下面小编给大家分享Mysql数据库增量备份的思路详解,一起看看吧

MySQL数据库增量备份,在这之前修改我们的数据库配置文件/etc/my.cnf开启bin-log日志功能即可。接下来是我参考了下网上的一些方法,自己写的,主要还是要能学到他的一些思路和方法。

#function:MYSQL增量备份
#version:1.0.0
#author:wangyanlin
#date:2017/08/02
#-----------------------------------------------------------------------------------------------
#!/bin/sh
export LANG=en_US.UTF-8
#设置时间
DATE=`date +%Y%m%d`
#设置信息
USER=root
PASSWORD=withub
#设置路径
cd /
/usr/bin/mkdir -p mysql_bak/daily 
/usr/bin/mkdir -p mysql_bak/logs
BakDir=/mysql_bak/daily
BinDir=/var/lib/mysql
LogFile=/mysql_bak/logs/Daily_$DATE.log
BinlogFile=/var/lib/mysql/mysql-bin.index
/usr/bin/mysqladmin -u$USER -p$PASSWORD flush-logs #刷新日志
Counter=`wc -l $BinlogFile | awk '{print $1}'`
NextNum=0
start_time=`date +'%Y-%m-%d %H:%M:%S'`
echo `date +"%Y年%m月%d日 %H:%M:%S"` $Next1 Bakup start! >> $LogFile
#这个for循环用于比对$Counter,$NextNum这两个值来确定文件是不是存在或最新的。
for file in `cat $BinlogFile`
do
  base=`basename $file`
  #basename用于截取mysql-bin.00000*文件名,去掉./mysql-bin.000005前面的./
  NextNum=`expr $NextNum + 1`
  if [ $NextNum -eq $Counter ]
  then
    echo $base skip! >> $LogFile
  else
    dest=$BakDir/$base
    if(test -e $dest)
    #test -e用于检测目标文件是否存在,存在就写exist!到$LogFile去。
    then
      echo $base exist! >> $LogFile
    else
      cp $BinDir/$base $BakDir
      echo $base copying >> $LogFile
    fi
  fi
done
echo `date +"%Y年%m月%d日 %H:%M:%S"` $Next2 Bakup succ! >> $LogFile
end_time=`date +'%Y-%m-%d %H:%M:%S'`
start_seconds=$(date --date="$start_time" +%s);
end_seconds=$(date --date="$end_time" +%s);
echo "本次备份运行时间: "$((end_seconds-start_seconds))"s" >> $LogFile
Copy after login

添加计划任务:

crontab -e

00 03 * * * /root/MySQL_incrementbak.sh #每天的凌晨3点开始增量备份日

logs日志打印出来的效果:

PS:下面看下mysql全量和增量备份脚本

全量:

[root@master leo]# cat DBfullBak.sh 
#!/bin/bash
#use mysqldump to fully backup mysql data
BakDir=/root/leo/full
LogFile=/root/leo/full/bak.log
Date=`date +%Y%m%d`
Begin=`date +"%Y年%m月%d日 %H:%M:%S"`
cd $BakDir
DumpFile=$Date.sql
GZDumpFile=$Date.sql.tgz
mysqldump -uroot -p123456 --all-databases --lock-all-tables --routines --triggers --events --master-data=2 --flush-logs > $DumpFile
tar zcvf $GZDumpFile $DumpFile
rm $DumpFile
Last=`date +"%Y年%m月%d日 %H:%M:%S"`
echo 开始:$Begin 结束:$Last $GZDumpFile succ >> $LogFile
Copy after login

参数注释:

--all-databases #备份所有库
--lock-all-tables #为所有表加读锁
--routinge  #存储过程与函数
--triggers  #触发器
--events   #记录事件
--master-data=2 #在备份文件中记录当前二进制日志的位置,并且为注释的,1是不注释掉在主从复制中才有意义
--flush-logs  #日志滚动一次
Copy after login

结果如下:

[root@master full]# ls
20140728.sql.tgz bak.log
[root@master full]# cat bak.log 
开始:2014年07月28日 19:02:59 结束:2014年07月28日 19:02:59 20140728.sql.tgz succ
开始:2014年07月28日 19:12:01 结束:2014年07月28日 19:12:01 20140728.sql.tgz succ
[root@master full]#
Copy after login

增量备份:

[root@master leo]# cat DBDailyBak.sh 
#!/bin/bash
BakDir=/root/leo/binlog/
BinDir=/var/lib/mysql
LogFile=/root/leo/binlog/bak.log
BinFile=/var/lib/mysql/mysql-bin.index
mysqladmin -uroot -p123456 flush-logs
Counter=`wc -l $BinFile|awk '{print $1}'`
NextNum=0
for file in `cat $BinFile`
do 
 base=`basename $file`
 NextNum=`expr $NextNum + 1`
 if [ $NextNum -eq $Counter ]
 then
  echo $base skip! >> $LogFile
 else
  dest=$BakDir/$base
  if(test -e $dest)
  then
   echo $base exist! >> $LogFile
  else
   cp $BinDir/$base $BakDir/
   echo $base copying >> $LogFile
  fi
 fi
done
echo `date +"%Y年%m月%d日 %H:%M:%S"` $Next Bakup succ~ >> $LogFile
Copy after login

总结

The above is the detailed content of Introduction to the implementation ideas and methods of incremental backup of Mysql database. For more information, please follow other related articles on the PHP Chinese website!

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

How to open phpmyadmin How to open phpmyadmin Apr 10, 2025 pm 10:51 PM

You can open phpMyAdmin through the following steps: 1. Log in to the website control panel; 2. Find and click the phpMyAdmin icon; 3. Enter MySQL credentials; 4. Click "Login".

MySQL: An Introduction to the World's Most Popular Database MySQL: An Introduction to the World's Most Popular Database Apr 12, 2025 am 12:18 AM

MySQL is an open source relational database management system, mainly used to store and retrieve data quickly and reliably. Its working principle includes client requests, query resolution, execution of queries and return results. Examples of usage include creating tables, inserting and querying data, and advanced features such as JOIN operations. Common errors involve SQL syntax, data types, and permissions, and optimization suggestions include the use of indexes, optimized queries, and partitioning of tables.

How to use single threaded redis How to use single threaded redis Apr 10, 2025 pm 07:12 PM

Redis uses a single threaded architecture to provide high performance, simplicity, and consistency. It utilizes I/O multiplexing, event loops, non-blocking I/O, and shared memory to improve concurrency, but with limitations of concurrency limitations, single point of failure, and unsuitable for write-intensive workloads.

MySQL's Place: Databases and Programming MySQL's Place: Databases and Programming Apr 13, 2025 am 12:18 AM

MySQL's position in databases and programming is very important. It is an open source relational database management system that is widely used in various application scenarios. 1) MySQL provides efficient data storage, organization and retrieval functions, supporting Web, mobile and enterprise-level systems. 2) It uses a client-server architecture, supports multiple storage engines and index optimization. 3) Basic usages include creating tables and inserting data, and advanced usages involve multi-table JOINs and complex queries. 4) Frequently asked questions such as SQL syntax errors and performance issues can be debugged through the EXPLAIN command and slow query log. 5) Performance optimization methods include rational use of indexes, optimized query and use of caches. Best practices include using transactions and PreparedStatemen

Why Use MySQL? Benefits and Advantages Why Use MySQL? Benefits and Advantages Apr 12, 2025 am 12:17 AM

MySQL is chosen for its performance, reliability, ease of use, and community support. 1.MySQL provides efficient data storage and retrieval functions, supporting multiple data types and advanced query operations. 2. Adopt client-server architecture and multiple storage engines to support transaction and query optimization. 3. Easy to use, supports a variety of operating systems and programming languages. 4. Have strong community support and provide rich resources and solutions.

How to connect to the database of apache How to connect to the database of apache Apr 13, 2025 pm 01:03 PM

Apache connects to a database requires the following steps: Install the database driver. Configure the web.xml file to create a connection pool. Create a JDBC data source and specify the connection settings. Use the JDBC API to access the database from Java code, including getting connections, creating statements, binding parameters, executing queries or updates, and processing results.

phpmyadmin connection mysql phpmyadmin connection mysql Apr 10, 2025 pm 10:57 PM

How to connect to MySQL using phpMyAdmin? The URL to access phpMyAdmin is usually http://localhost/phpmyadmin or http://[your server IP address]/phpmyadmin. Enter your MySQL username and password. Select the database you want to connect to. Click the "Connection" button to establish a connection.

Solution to MySQL encounters 'Access denied for user' problem Solution to MySQL encounters 'Access denied for user' problem Apr 11, 2025 pm 05:36 PM

How to solve the MySQL "Access denied for user" error: 1. Check the user's permission to connect to the database; 2. Reset the password; 3. Allow remote connections; 4. Refresh permissions; 5. Check the database server configuration (bind-address, skip-grant-tables); 6. Check the firewall rules; 7. Restart the MySQL service. Tip: Make changes after backing up the database.

See all articles