CentOS automatically backs up MySQL and uploads it to a remote FTP script

One, scheduled backup MySQL database

For security purposes, execute directly with root:

The code is as follows	Copy Code
/root/mysql_backup.sh # everyday 3:00 AM Execute database backup 3 0 * * */root/mysql_backup.sh

The following is automatic automatic backup shell, only the latest 5 days

The code is as follows

Copy Code

#!/bin/sh # mysql_backup.sh:backup MySQL databases and keep newest 5 days backup. # # Db_user is MySQL username # db_passwd is MySQL password # db_host is MySQL host # —————————– Db_user= "Root" Db_passwd= "Zhoz.com" db_host= "localhost" # The directory for story your backup file. Backup_dir= "/home/zhozdbbackup" # Date format for backup file (dd-mm-yyyy) Time= "$ (date +"%d-%m-%y ")" # MySQL, mysqldump and some other bin ' s path Mysql= "/usr/bin/mysql" mysqldump= "/usr/bin/mysqldump" Mkdir= "/bin/mkdir" Rm= "/bin/rm" mv= "/BIN/MV" gzip= "/bin/gzip" # Check the directory for store backup is writeable Test! -W $backup _dir && echo "Error: $backup _dir is un-writeable." && exit 0 # The directory for story the newest backup Test! -D "$backup _dir/backup.0/" && $MKDIR "$backup _dir/backup.0/" # Get all databases All_db= "$ ($MYSQL-u $db _user-h $db _host-p$db_passwd-bse ' show databases ')" For DB in $all _db Todo $MYSQLDUMP-u $db _user-h $db _host-p$db_passwd $db | $GZIP-9 > "$backup _dir/backup.0/$time. $db. Gz" Done # Delete the oldest backup test-d "$backup _dir/backup.5/" && $RM-rf "$backup _dir/backup.5" # Rotate Backup Directory for int in 4 3 2 1 0 Todo if (test-d "$backup _dir"/backup.) $int ") Then Next_int= ' expr $int + 1 ' $MV "$backup _dir"/backup. " $int "" $backup _dir "/backup." $next _int " Fi Done Exit 0;

The above backup will not automatically upload to the FTP server, we can refer to the following example

Two, automatically backup MySQL and upload the remote FTP script

Production needs, under CentOS 5.4 automatically back up the MySQL database and upload to remote FTP, spent a little time, the script is as follows:

The code is as follows

Copy Code

#!/bin/bash #auto FTP Bakup #by Xhat 2010.06.08 #要备份的数据库 Dbname=wwwdbname #ftp账号信息 Ftphost= "test.com" Ftpuser=username ftppass=passwd #mysql账号信息 Dbuser=root dbpasswd=123456 backuppath=./ Newfile= "$BackupPath" db$ (date +%y%m%d). tar.gz Dumpfile= "$BackupPath" db$ (date +%y%m%d). sql Oldfile= "$BackupPath" db$ (date +%y%m%d--date= ' 1 days ago '). tar.gz #LogFile = "$BackupPath" db$ (date +%y%m%d--date= ' 1 days ago '). txt echo "---------------------------" echo $ (date + "%y-%m-%d%h:%m:%s") echo "---------------------------" #删除 If [f $OldFile]; Then Rm-f $OldFile echo "[$OldFile]delete old File success!" Fi #新 If [f $NewFile]; Then echo "[$NewFile] already exists and is no longer backed up!" Else /OPT/MYSQL/MYSQLDUMP-U$DBUSER-P$DBPASSWD $DBName > $DumpFile Sleep 5 Tar czvf $NewFile $DumpFile RM $DumpFile-RF echo "[$NewFile] Backup succeeded" Fi echo "Open ftp" Ftp-i-in < open $ftphost 21 User $ftpuser $ftppass CD/ Mput *.tar.gz Bye ! echo "Backup Complete"

After completing the script above, join the crontab in the morning to execute the time every day.

The code is as follows	Copy Code
3 * * */opt/bak/mybak

Summary

In fact, we can use rsync synchronization to synchronize the data has been backed up, in the FTP and rsync compared to rsync performance and stability is FTP can not compare.