Linux MySQL database backup script code

You can put this script into the crontab, his configuration file in the/etc/crontab every morning, the automatic backup of this script only once a day, and keep only the last five days of backup on the server.

The code is as follows

Copy Code

#!/bin/bash #This is a shellscript for Auto DB Backup #Powered by Aspbiz #2004-09 #Setting #设置数据库名, database login name, password, backup path, log path, data file location, #以及备份方式 #默认情况下备份方式是tar, it could be mysqldump,mysqldotcopy. #默认情况下, log in to the MySQL database with root (empty), back up to/root/namexxxxx.tgz Dbname=mysql Dbuser=root Dbpasswd=password backuppath=/root/ Logfile=/root/db.log dbpath=/var/local/mysql5/var/ #BackupMethod =mysqldump #BackupMethod =mysqlhotcopy #BackupMethod =tar #Setting End newfile= "$BackupPath" "$DBName" $ (date +%y%m%d). tgz dumpfile= "$BackupPath" "$DBName" $ (date +%y%m%d) oldfile= "$BackupPath" "$DBName" $ (date +%y%m%d–date= ' 5 days ago '). tgz echo "——————————————-" >> $LogFile echo $ (date + "%y-%m-%d%h:%m:%s") >> $LogFile echo "———————— –" >> $LogFile #Delete Old File If [f $OldFile] Then Rm-f $OldFile >> $LogFile 2>&1 echo "[$OldFile]delete old File success!" >> $LogFile Else echo "[$OldFile]no old Backup file!" >> $LogFile Fi If [f $NewFile] Then echo [$NewFile]the Backup File is Exists,can ' t backup! ' >> $LogFile Else Case $BackupMethod in mysqldump) If [-Z $DBPasswd] Then Mysqldump-u $DBUser –opt $DBName > $DumpFile Else Mysqldump-u $DBUser-p$dbpasswd–opt $DBName > $DumpFile Fi Tar czvf $NewFile $DumpFile >> $LogFile 2>&1 echo "[$NewFile]backup success!" >> $LogFile RM-RF $DumpFile ;; Mysqlhotcopy) RM-RF $DumpFile mkdir $DumpFile If [-Z $DBPasswd] Then Mysqlhotcopy-u $DBUser $DBName $DumpFile >> $LogFile 2>&1 Else Mysqlhotcopy-u $DBUser-P $DBPasswd $DBName $DumpFile >> $LogFile 2>&1 Fi Tar czvf $NewFile $DumpFile >> $LogFile 2>&1 echo "[$NewFile]backup success!" >> $LogFile RM-RF $DumpFile ;; *) /etc/init.d/mysqld Stop >/dev/null 2>&1 Tar czvf $NewFile $DBPath $dbname >> $LogFile 2>&1 /etc/init.d/mysqld Start >/dev/null 2>&1 echo "[$NewFile]backup success!" >> $LogFile ;; Esac Fi echo "——————————————-" >> $LogFile

Example 2. If you are dissatisfied with the above script, refer to the MySQL database backup script below.

The code is as follows

Copy Code

#!/bin/bash #Mysql autobackup Shell#write by tuspark.cn #-------------------Database-related user name, password, database name to be backed up, backup directory, etc. Dbuser=root Dbpasswd=xxxxx Dbserver=localhost Dbname=xxxxx Dbopt=--opt backupdir=/dcbackup/ #-------------------whether to open FTP remote backup, 0 is no, 1 is. Copytoftp=1 Ftpserver=xxxxx Ftpuser=xxxxx Ftppasswd=xxxxx #-------------------The following parameters Fileprefix=dcradiusdump Filename= $backupdir ' Date +%f '. sql newfile= $fileprefix-' date +%f '. tar.gz keepdays=10 #-------------------The following log for backup Logfile=/var/log/mysqlbackup.log Logtmp=/var/log/mybackup.tmp #=============================================== if [!-D $backupdir] Then echo "$backupdir isn't exist, then make ..." >> $logfile Mkdir-p $backupdir Fi echo "start====================================>" >> $logfile echo "Beginning backup ' Date ' +%f%T '" >> $logfile echo "Delete $keepdays days ago Files ..." >> $logfile Find $backupdir-name $fileprefix *-mtime + $keepdays-fls $logtmp-exec rm {}; echo "Deleted Backup file is:" >> $logfile Cat $logtmp >> $logfile echo "Delete old file success!" >> $logfile If [f $backupdir $newfile] Then echo "$newfile backup exist, backup stop ..." >> $logfile Else If [-Z $dbpasswd] Then Mysqldump-u$dbuser-h$dbserver $dbopt $dbname > $dumpfilename Else Mysqldump-u$dbuser-p$dbpasswd-h$dbserver $dbopt $dbname > $dumpfilename Fi Tar czvf $backupdir $newfile $dumpfilename >> $logfile 2>&1 echo "$backupdir $newfile Backup success!" >> $logfile RM-FR $dumpfilename if [$copytoftp = 1]; Then If [-Z $ftpserver];then echo "ftp Server not set,copy to ftp Failed ..." >> $logfile Exit 1 elif [-Z $ftpuser];then echo "FTP user not set, Copy to Ftp Failed ..." >> $logfile Exit 2 Elif [Z $ftppasswd]; Then echo "ftp password not set, Copy to FTP Failed ..." >> $logfile Exit 3 Else echo "Start copy to FTP server ..." >> $logfile Ftp-n> $logfile Fi

Example Three

The code is as follows

Copy Code

First step: Configure the backup directory code on the server: -------------------------------------------------------------------------------- Mkdir/var/lib/mysqlbackup Cd/var/lib/mysqlbackup -------------------------------------------------------------------------------- Step Two: Write backup script code: -------------------------------------------------------------------------------- VI dbbackup.sh -------------------------------------------------------------------------------- Paste the following code, be sure to change the Username,password and dbname. Code: -------------------------------------------------------------------------------- #!/bin/sh #Username to access the MySQL server username= "Username" # Username to access the MySQL server password= "Passwor" D "# List of Dbnames for Backup dbname=" dbname "#date timestamp to log message date= '/bin/date +%y-%m-%d_%hh%mm ' #output File outdir= "/var/lib/mysqlbackup/" outfile= "Ip_bindass". $DATE. " sql.gz "#working directory dir="/var/lib/mysqlbackup/"#cd $DIR # MySQL backup/usr/bin/mysqldump--database $DBNAME--opt --single-transaction-u$username-p$password | /usr/bin/gzip-9 > $OUTDIR $outfile -------------------------------------------------------------------------------- Change Backup Script Permissions Code: -------------------------------------------------------------------------------- chmod +x dbbackup.sh -------------------------------------------------------------------------------- Step three: Use Crontab to execute backup script code regularly: -------------------------------------------------------------------------------- Crontab-e -------------------------------------------------------------------------------- If you back up 3 o'clock in the afternoon 20 daily, add the following code, Code: -------------------------------------------------------------------------------- * * */var/lib/mysqlbackup/dbbackup.sh -------------------------------------------------------------------------------- Get!