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

This article shows you how to achieve automatic backup in MySQL, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Automatic backup of MySql is critical, especially for DBA. Here mainly use the code to illustrate this problem, I hope it will be helpful to you. You can put this script into crontab and execute it in the early hours of every morning to back up automatically.

This script can only be executed once a day at most, and only the backup for the last five days is kept on the server.

Code:

#! / bin/bash

# ThisisaShellScriptForAutoDBBackup

# Poweredbybiz

# 2004-09

# Setting

# setting name, database login name, password, backup path, log path, data file location, and backup method

# by default, the backup method is tar or dump,mysqldotcopy

# by default, log in to my 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,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

The above is how to achieve automatic backup in MySQL. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, 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.