Automatic MySQL backup

The backup of MySQL database is one of the most important tasks. The data backup of MySQL database can achieve absolute security of data in MySQL database, and the data in MySQL database will not be lost due to any minor faults, automatic backup is a more convenient backup method. Detailed analysis is provided below.

MySql automatic backup is critical, especially for DBA. Here we mainly use code to illustrate this problem and hope it will be helpful to you. You can put this script into crontab and execute it once every morning for automatic backup.

This script can be executed only once a day, and only the backups of the last five days are retained on the server.

Code:

#! /Bin/bash

# ThisisaShellScriptForAutoDBBackup

 

# Poweredbyaspbiz

 

#2004-09

 

# Setting

# Set the database name, database login name, password, backup path, Log Path, data file location, and backup mode

 

# By default, the backup method is tar, mysqldump, or mysqldotcopy.

 

# By default, log on to the mysql database with root (empty) and back up to/root/dbxxxxx. tgz

 

DBName = mysql

 

DBUser = root

 

DBPasswd =

 

BackupPath =/root/

 

LogFile =/root/db. log

 

DBPath =/var/lib/mysql/

 

# BackupMethod = mysqldump

 

# BackupMethod = mysqlhotcopy

 

# BackupMethod = tar

 

# SettingEnd

 

 

NewFile = "$ BackupPath" db $ (date + % y % m % d). tgz

 

DumpFile = "$ BackupPath" db $ (date + % y % m % d)

 

OldFile = "$ BackupPath" db $ (date + % y % m % d -- date = 5 daysago). tgz

 

Echo "-----------------------------------------"> $ LogFile

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

 

Echo "--------------------------"> $ LogFile

 

# DeleteOldFile

 

If [-f $ OldFile]

 

Then

 

Rm-f $ OldFile >>$ LogFile2> & 1

 

Echo "[$ OldFile] DeleteOldFileSuccess! ">>> $ LogFile

 

Else

 

Echo "[$ OldFile] NoOldBackupFile! ">>> $ LogFile

 

Fi

 

If [-f $ NewFile]

Then

 

Echo "[$ NewFile] TheBackupFileisexists, CantBackup! ">>> $ LogFile

 

Else

 

Case $ BackupMethodin

 

Mysqldump)

 

If [-z $ DBPasswd]

 

Then

 

Mysqldump-u $ DBUser -- opt $ DBName> $ DumpFile

 

Else

 

Mysqldump-u $ DBUser-p $ DBPasswd -- opt $ DBName> $ DumpFile

 

Fi

 

Tarczvf $ NewFile $ DumpFile >>$ LogFile2> & 1

 

Echo "[$ NewFile] BackupSuccess! ">>> $ LogFile

 

Rm-rf $ DumpFile

 

;;

 

Mysqlhotcopy)

 

Rm-rf $ DumpFile

 

Mkdir $ DumpFile

 

If [-z $ DBPasswd]

 

Then

 

Mysqlhotcopy-u $ DBUser $ DBName $ DumpFile >>$ LogFile2> & 1

 

Else

 

Mysqlhotcopy-u $ DBUser-p $ DBPasswd $ DBName $ DumpFile >>$ LogFile2> & 1

 

Fi

 

Tarczvf $ NewFile $ DumpFile >>$ LogFile2> & 1

 

Echo "[$ NewFile] BackupSuccess! ">>> $ LogFile

 

Rm-rf $ DumpFile

 

;;

 

*)

 

/Etc/init. d/mysqldstop>/dev/null2> & 1

 

Tarczvf $ NewFile $ DBPath $ DBName >>$ LogFile2> & 1

 

/Etc/init. d/mysqldstart>/dev/null2> & 1

 

Echo "[$ NewFile] BackupSuccess! ">>> $ LogFile

 

;;

 

Esac

 

Fi

 

Echo "-----------------------------------------"> $ LogFile

We will introduce you to the automatic backup of MySQL databases. Follow the steps described above to perform automatic backup of MySQL databases, we believe that you will be able to complete the automatic backup of the MySQL database.