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