How to implement automatic MySQL backup _ MySQL

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, MySQL databaseOf BackupIt is one of the most important tasks. data backup in the MySQL database ensures absolute security of data in the MySQL database. data in the MySQL database will not be lost due to any minor faults, Automatic backupThis 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 = '5daysago '). 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, Can be 'tbackup! ">>> $ 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.