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

MySQL how to achieve automatic backup, I believe that many inexperienced people are helpless, this article summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

How to implement automatic backup of MySQL

Automatic MySql backups are critical, especially for DBAs. Here the main code to illustrate this problem, I hope to help you. You can put this script in crontab and execute it once a day in the early morning to make an automatic backup.

This script is executed at most once a day, and only the previous five days 'backups are kept on the server.

Code:

#!/ bin/bash

#ThisisaShellScriptForAutoDBBackup

#Poweredbyaspbiz

#2004-09

#Setting

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

#Default backup method is tar, also can be mysqldump,mysqldotcopy

#By default, log in to mysql database as root(empty) and backup to/root/dbxxxx.tgz

How to implement automatic backup of MySQL

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'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

After reading the above, do you know how to implement automatic backup in MySQL? If you still want to learn more skills or want to know more related content, welcome to pay attention to the industry information channel, thank you for reading!

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.