How to realize automatic backup in MySQL
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.
