Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to achieve incremental backup in Mysql. The article is rich in content and analyzes and describes for you from a professional point of view. I hope you can get something after reading this article.

Script description

Back up all data every 7 days, and back up binlog every day, that is, incremental backup.

(if there is little data, you can back up the full data once a day, and it may not be necessary to do an incremental backup.)

The author is not familiar with the shell script, so it is very stupid in many places.

Turn on bin log

In mysql version 4.1, there are only error logs and no other logs by default. You can open bin log. Net by modifying the configuration. There are many ways, one of which is to add to the mysqld section of / etc/my.cnf:

[mysqld]

Log-bin

The main purpose of this log is incremental backup or replication (and possibly for other purposes).

If you want to make incremental backups, you must open this log.

For frequent operations, this log will become very large, and there may be multiple.

In flush-logs in the database, or after calling flush-logs with mysqladmin,mysqldump and using the parameter delete-master-logs, these log files disappear and new log files are generated (initially empty).

So if you never back up, it may not be necessary to open the log.

Full backup can be called at the same time flush-logs, incremental backup before flush-logs, in order to back up the latest data.

Full backup script

If there is a lot of data in the database, we usually back up the data once a few days or once a week, so as not to affect the running of the application.

#! / bin/sh

# mysql data backup script

# by scud

# 2005-10-30

#

# use mysqldump-help,get more detail.

#

BakDir=/backup/mysql

LogFile=/backup/mysql/mysqlbak.log

DATE= `date +% Y% m% d`

Echo "" > > $LogFile

Echo "" > > $LogFile

Echo "- -" > > $LogFile

Echo $(date + "% y-%m-%d% H:%M:%S") > > $LogFile

Echo "--> > $LogFile

Cd $BakDir

DumpFile=$DATE.sql

GZDumpFile=$DATE.sql.tgz

Mysqldump-quick-all-databases-flush-logs

-delete-master-logs-lock-all-tables

> $DumpFile

Echo "Dump Done" > > $LogFile

Tar czvf $GZDumpFile $DumpFile > > $LogFile 2 > & 1

Echo "[$GZDumpFile] Backup Success!" > > $LogFile

Rm-f $DumpFile

# delete previous daily backup files: files backed up incrementally. Delete the files backed up incrementally after a full backup.

Cd $BakDir/daily

Rm-f *

Cd $BakDir

Echo "Backup Done!"

Echo "please Check $BakDir Directory!"

Echo "copy it to your local disk or ftp to somewhere!!"

Ls-al $BakDir

The above script backs up the mysql to the local / backup/mysql directory and the incremental backup files in the / backup/mysql/daily directory.

Note: the above script does not transfer backup files to other remote computers, nor does it delete backup files from a few days ago: users are required to add scripts or do so manually.

Incremental backup

The amount of data of incremental backup is relatively small, but to operate on the basis of full backup, users can weigh the time and cost and choose the way that is most beneficial to them.

Incremental backups use bin log with the following script:

#! / bin/sh

#

# mysql binlog backup script

#

/ usr/bin/mysqladmin flush-logs

DATADIR=/var/lib/mysql

BAKDIR=/backup/mysql/daily

# if you have made special settings, please modify here or modify the line to which this variable is applied: the default is the machine name, and the default is the machine name for mysql

HOSTNAME= `uname-n`

Cd $DATADIR

FILELIST= `cat $HOSTNAME- bin.index`

# # calculate the number of lines, that is, the number of files

COUNTER=0

For file in $FILELIST

Do

COUNTER= `expr $COUNTER + 1 `

Done

NextNum=0

For file in $FILELIST

Do

Base= `basename $file`

NextNum= `expr $NextNum + 1`

If [$NextNum-eq $COUNTER]

Then

Echo "skip lastest"

Else

Dest=$BAKDIR/$base

If (test-e $dest)

Then

Echo "skip exist $base"

Else

Echo "copying $base"

Cp $base $BAKDIR

Fi

Fi

Done

Echo "backup mysql binlog ok"

The incremental backup script is that before backup, flush-logs,mysql will automatically put the log in memory into the file and generate a new log file, so we only need to back up the first few, that is, not the last one.

Since there may be multiple log files generated from the last backup to this backup, you need to detect the files. If you have already backed up, you do not need to back up.

Note: similarly, users also need to send their own remote, but do not need to delete, the program will be automatically generated after the full backup.

Access settin

After the script has been written, in order to make the script run, we also need to set the corresponding user name and password. Both mysqladmin and mysqldump need a user name and password, of course, they can be written in the script, but it is not very convenient to modify. Suppose we use the root user of the system to run the script, then we need to create a .my.cnf file in / root (that is, the home directory of the root user), as follows

[mysqladmin]

Password = password

User= root

[mysqldump]

User=root

Password=password

Note: set this file to be readable only by root. (chmod 600.my.cnf)

The above is how to achieve incremental backup in the Mysql shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.