Mysql routine backup and Incremental backup script

Intended audience

This article is tested in linux and mysql 4.1.14. After modification, it may be applicable to mysql 4.0, 5.0 and other versions.

This article is applicable to mysql that does not enable replication. If replication is started, you may not need to adopt this backup policy or modify relevant parameters.

Each person may have different backup policies. Therefore, Please modify the policies based on actual conditions to avoid copying them, which may cause unnecessary losses.

I hope you understand what the script is!

Script description

All data is backed up once every 7 days, and binlog is backed up every day, that is, Incremental backup.

(If there is little data, you can back up the complete data once a day, and there may be no need for Incremental Backup)

I am not familiar with shell scripts, so I am very stupid in many places :)

Enable bin log

In mysql 4.1, only error logs are generated by default, and no other logs exist. you can modify the configuration to open the bin log. there are many methods, one of which is in/etc/my. add the mysqld part in cnf:

[Mysqld]
Log-bin

This log is mainly used for Incremental backup or replication (it may be used for other purposes ).

To perform Incremental backup, you must enable this log.

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

Flush-logs in the database, or use mysqladmin and mysqldump to call flush-logs and use the delete-master-logs parameter. These log files will disappear, and generate a new log file (which is empty at the beginning ).

Therefore, it is unnecessary to enable the log if the backup is never performed.

You can call flush-logs at the same time as the full backup. Before the Incremental backup, flush-logs is used to back up the latest data.

Full backup script

If there is a large amount of database data, we usually back up the data once a day or a week to avoid affecting application operation. If the data volume is small, it doesn't matter if we back up the data once a day.

#! /Bin/sh

# Mysql data backup script
# By scud http://www.jscud.com
#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 in incremental mode. If the backup is complete, the files backed up in incremental mode are deleted.
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 mysql to the local/backup/mysql directory, and stores the Incremental backup files in the/backup/mysql/daily directory.

Note: The above script does not transmit the backup file to other remote computers, nor delete the backup file a few days ago: You need to add the relevant script or manually operate it.

Incremental Backup

The Incremental backup data volume is small, but must be performed on the basis of the complete backup, you can weigh the time and cost, and select the most advantageous method.

The Incremental Backup uses the bin log. The script is as follows:

 

#! /Bin/sh

#
# Mysql binlog backup script
#

/Usr/bin/mysqladmin flush-logs

DATADIR =/var/lib/mysql
BAKDIR =/backup/mysql/daily

### If you have made some special settings, modify this field or modify the row that applies this variable: The machine name is used by default, and the machine name is used by mysql by default.
HOSTNAME = 'uname-N'

Cd $ DATADIR

FILELIST = 'cat $ HOSTNAME-bin.index'

# Number of calculated rows, 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 flush-logs before the backup. mysql will automatically put the logs in the memory into the file and generate a new log file. Therefore, we only need to back up the first few logs, that is, do not back up the last one.
Because multiple log files may be generated from the last backup to the current backup, you do not need to back up the files if the files have been backed up.

Note: Likewise, you also need to remotely transmit the data by yourself, but you do not need to delete the data. After the complete backup, the program will automatically generate the data.

Access settings

The script has been written. In order to run the script, you also need to set the corresponding user name and password. Both mysqladmin and mysqldump require the user name and password. Of course, they can be written in the script, but the modification is not convenient, suppose we use the system root user to run this script, then we need to create one in/root (that is, the root user's home directory. my. the cnf file contains the following content:

 

[Mysqladmin]
Password = password
User = root
[Mysqldump]
User = root
Password = password
Note: This file is only readable by root. (chmod 600. my. cnf)

This file indicates that the program uses the root user of mysql to back up data, and the password is the corresponding settings, so you do not need to write the user name and password in the script.

Automatic Running

To make the backup program run automatically, we need to add it to crontab.

There are two methods. One is to put the script in the directory/etc/cron. daily,/etc/cron. weekly according to your choice.
One is to put crontab-e into the scheduled task of the root user. For example, the full backup runs at every Sunday and the daily backup runs at every Monday-Saturday.