说三道四技术文摘-感悟人生的经典句子
说三道四 > 文档快照

MySQL备份脚本大全

HTML文档下载 WORD文档下载 PDF文档下载
1、linux环境下的备份脚本2、linux环境下带邮件通知的备份脚本3、linux环境下压缩备份文件4、linux环境下的热备份5、windows环境下的离线备份6、windows平台完备的mysql定时备份脚本

 

1、linux环境下的备份脚本

2、linux环境下带邮件通知的备份脚本

3、linux环境下压缩备份文件

4、linux环境下的热备份

5、windows环境下的离线备份

6、windows平台完备的mysql定时备份脚本

 

1、linux环境下的备份脚本

 

##########################################################################mysqldump####################################################!/bin/sh# mysql_backup.sh: backup mysql databases and keep newest 5 days backup.# -----------------------------db_user="root"db_passwd="linuxtone"db_host="localhost"# the directory for story your backup file.backup_dir="/backup"# date format for backup file (dd-mm-yyyy)time="$(date +"%d-%m-%Y")"# mysql, mysqldump and some other bin's pathMYSQL="$(which mysql)"MYSQLDUMP="$(which mysqldump)"MKDIR="$(which mkdir)"RM="$(which rm)"MV="$(which mv)"GZIP="$(which gzip)"#针对不同系统,如果环境变量都有。可以去掉# check the directory for store backup is writeabletest ! -w $backup_dir && echo "Error: $backup_dir is un-writeable." && exit 0# the directory for story the newest backuptest ! -d "$backup_dir" && $MKDIR "$backup_dir"# get all databasesfor db in cdn cdn_viewdo        $MYSQLDUMP -u $db_user -h $db_host -p$db_passwd $db | $GZIP -9 > "$backup_dir/$time.$db.gz"done#delete the oldest backup 30 days agofind $backup_dir -name "*.gz" -mtime +30 |xargs rm -rfexit 0;

 

2、linux环境下带邮件通知的备份脚本

 

#############################################################################带邮件通知的mysqldump######################################!/bin/sh# Name:mysqlFullBackup.sh# PS:MySQL DataBase Full Backup.# Write by:i.Stone# Last Modify:2008-9-17## Use mysqldump --help get more detail.#scriptsDir=`pwd`mysqlDir=/usr/local/mysql ?user=rootuserPWD=111111dataBackupDir=/tmp/mysqlbackupeMailFile=$dataBackupDir/email.txteMail=liuyu@sohu.comlogFile=$dataBackupDir/mysqlbackup.logDATE=`date -I`echo "" > $eMailFileecho $(date +"%y-%m-%d %H:%M:%S") >> $eMailFilecd $dataBackupDirdumpFile=mysql_$DATE.sqlGZDumpFile=mysql_$DATE.sql.tar.gz$mysqlDir/bin/mysqldump -u$user -p$userPWD --opt --default-character-set=utf8 --extended-insert=false --triggers -R --hex-blob --all-databases --flush-logs --delete-master-logs --delete-master-logs -x > $dumpFileif [[ $? == 0 ]]; then  tar czf $GZDumpFile $dumpFile >> $eMailFile 2>&1  echo "BackupFileName:$GZDumpFile" >> $eMailFile  echo "DataBase Backup Success!" >> $eMailFile  rm -f $dumpFile# Delete daily backup files.  cd $dataBackupDir/daily  rm -f *# Delete old backup files(mtime>2).  $scriptsDir/rmBackup.sh# Move Backup Files To Backup Server.  $scriptsDir/rsyncBackup.sh     if (( !$? )); then      echo "Move Backup Files To Backup Server Success!" >> $eMailFile    else      echo "Move Backup Files To Backup Server Fail!" >> $eMailFile    fielse  echo "DataBase Backup Fail!" >> $emailFilefiecho "--------------------------------------------------------" >> $logFilecat $eMailFile >> $logFilecat $eMailFile | mail -s "MySQL Backup" $eMail

 

 

 

3、linux环境下压缩备份文件

 

 

################################################################################## tar ################################################!/bin/bash#15 3 * * * /usr/local/sbin/backup.sh#backup directoryBAK_DIR=/data/db_backupTAR="/bin/tar"TAR_FLAG="czvf"#BAKupif [ ! -d $BAK_DIR ];then          mkdir -p $BAK_DIRfiCOMM="$TAR $TAR_FLAG $BAK_DIR/linuxtone-`date +%Y%m%d`.tar.gz linuxtone/"cd /data/mysql/dataeval $COMMfind $BAK_DIR -name "linuxtone-*.tar.gz" -mtime +30 |xargs rm -rf

 

 

 

4、linux环境下的热备份

 

 

######################################################################################mysqlhotcopy#####################################!/bin/shDBS=`du /var/lib/mysql/linuxtone/ | awk '{ printf $1 }'`DFS=`df /myhotco | grep myhotco | awk '{ printf $3}'`let "DBS = $DBS / 1024" let "DFS = $DFS / 1024"# more than 100M free space upif [ `expr $DBS + 100` -lt $DFS ] ; then  echo "run mysqlhotcopy ( `expr $DFS - $DBS` ) ..."  /usr/bin/mysqlhotcopy linuxtone --allowold --flushlog /myhotco fi

 

 

 

5、windows环境下的离线备份

 

net stop mysqlxcopy E:\MySQL\Data\www.okbase.net\*.* D:\bak\www.okbase.net\%date:~0,10%\ /ynet start mysql

 

 

6、windows平台完备的mysql定时备份脚本

 

@echo offclscolor 1Etitle %date% %time:~,8% 备份MYSQL数据库 BY:REKFAN::::::::::::::::::::::::以下是需要配置的参数::::::::::::::::::::::::        rem 设置 MySql服务器root账号的密码,特殊符号需要在其前添加两个^,如!、>、|、^、&、* 等        SET MySql_pw=mysql 的root密码        rem 设置 数据库备份目录        SET BAK_Dir=f:db_bak        rem 设置 需要备份的myisam格式数据库,多数据库用逗号分隔        SET BAK_db_myisam=myisam_db1,myisam_db2,myisam_db3        rem 设置 需要备份的innodb格式数据库,多数据库用逗号分隔        SET BAK_db_innodb=innodb_1,innodb_2,innodb_3        rem 设置 自动删除几天的备份,0为删除所有,慎用        SET Bak_Time_ago=5        rem 设置 WinRAR压缩软件的路径        SET RAR_Dir="C:Program FilesWinRARWinRAR.exe"        rem 设置 以2001-01-01格式的日期为子目录(不必修改)        SET BAK_Dir2=%date:~0,4%-%date:~5,2%-%date:~8,2%        rem 设置 备份文件名        SET BAK_FILE=%%i_%BAK_Dir2%.sql        rem 设置 日志文件名        SET LOG_FILE=%BAK_Dir%%BAK_Dir2%MY_DBBAK.log::::::::::::::::::::::::以上是需要配置的参数::::::::::::::::::::::::echo. ≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡echo. ┃                                                                   ┃echo. ┃                   欢迎使用Mysql自动备份批处理                     ┃echo. ┃           本代码由rekfan编写 Http://blog.rekfan.com               ┃echo. ┃                                                                   ┃echo. ┃  …………………………………………………………………………………… ┃echo. ┃ 关于本脚本                                                        ┃echo. ┃ ::本脚本只需自定义MySql_pw、BAK_Dir、Bak_Time_ago、RAR_Dir的值    ┃echo. ┃ ::本脚本调用了临时VBS代码进行日期计算                             ┃echo. ┃ ::本脚本为兼容不同的日期格式,调用reg命令,统一设置日期格式        ┃echo. ┃ ::本脚本自动生成数据库.sql脚本,并自动压缩为.rar文件              ┃echo. ┃ ::本脚本自动生成日志文件在x:xxxx000-00-00MY_DBBAK.log         ┃echo. ┃ ::本脚本数据库备份路径为x:xxxx000-00-00数据库名_0000-00-00.rar┃echo. ┃ ::本脚本如果想放在windows计划任务里执行,请去掉脚本里的所有pause   ┃echo. ┃ ::因每个服务器的Mysql环境不一样,备份的核心语句自行更改            ┃echo. ┃ ::本脚本没有版权,可以任意改为自己想要的效果,转载请勿删除该注释语句┃echo. ┃                                          By REKFAN 2011-10-14     ┃echo. ≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡echo.if not defined MySql_pw (echo MySql_pw 尚未定义!)if not defined BAK_Dir (echo BAK_Dir 尚未定义!)if not defined Bak_Time_ago (echo Bak_Time_ago 尚未定义!)if not defined RAR_Dir (echo RAR_Dir 尚未定义!):: 取得当前计算机时间,以 yyyy-MM-dd 格式显示for /f "skip=2 delims=" %%a in ('reg query "HKEY_CURRENT_USERControl PanelInternational" /v sShortDate') do (SET RegDateOld=%%a)SET RegDateOld=%RegDateOld:~-8%::通过改变注册表改变计算机的日期格式reg add "HKEY_CURRENT_USERControl PanelInternational" /v sShortDate /t REG_SZ /d yyyy-M-d /f>nul>"%temp%DstDate.vbs" echo LastDate=date()-%Bak_Time_ago%>>"%temp%DstDate.vbs" echo FmtDate=right(year(LastDate),4) ^& right("0" ^& month(LastDate),2) ^& right("0" ^& day(LastDate),2)>>"%temp%DstDate.vbs" echo wscript.echo FmtDatefor /f %%a in ('cscript /nologo "%temp%DstDate.vbs"') do (SET DstDate=%%a)::删除指定时间前的备份SETlocal enabledelayedexpansionecho. 删除 %BAK_Dir2%〔%Bak_Time_ago%〕天前的备份文件for /f "delims= " %%i in ('dir /ad/b %BAK_Dir%????-??-??') do (SET t1=%%iSET t2=!t1:~0,4!!t1:~5,2!!t1:~8,2!if /i !t2! leq %DstDate% (DEL /F /A /Q \?%BAK_Dir%!t1!*.*rd /q /s \?%BAK_Dir%!t1!echo. 备份文件夹%BAK_Dir%!t1!删除完成!)):: 还原计算机注册表的日期格式reg add "HKEY_CURRENT_USERControl PanelInternational" /v sShortDate /t REG_SZ /d %RegDateOld% /f>nulecho. echo. 开始以当前日期创建文件夹if not exist %BAK_Dir%%BAK_Dir2% md %BAK_Dir%%BAK_Dir2%cd /d %BAK_Dir%%BAK_Dir2%echo. 开始建立今天(%BAK_Dir2%)的备份:: 记录时间日志echo 备份时间:%BAK_Dir2% %time:~0,8%  >> %LOG_FILE%echo /++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ >> %LOG_FILE%:: 备份的核心代码if not defined BAK_db_myisam (goto innodb)SetLocal DisableDelayedExpansionfor %%i in (%BAK_db_myisam%) do (mysqldump -hlocalhost -uroot -p%MySql_pw% --default-character-set=GB2312 -R --triggers --hex-blob -x %%i >%BAK_FILE%rem 以上的参数根据自己的需求更改,部分参数方法请看 http://blog.rekfan.com/?p=57%RAR_Dir% a %BAK_FILE:~0,-4%.rar %BAK_FILE%DEL /F /A /Q %BAK_FILE%echo 数据库【%%i】 已经备份到%BAK_Dir%%BAK_Dir2%%BAK_FILE%.rar >> %LOG_FILE%):innodbif not defined BAK_db_innodb (goto exitbat)echo. 开始以当前日期创建文件夹if not exist %BAK_Dir%%BAK_Dir2% md %BAK_Dir%%BAK_Dir2%cd /d %BAK_Dir%%BAK_Dir2%echo. 开始建立今天(%BAK_Dir2%)的备份SetLocal DisableDelayedExpansionfor %%i in (%BAK_db_innodb%) do (mysqldump -hlocalhost -uroot -p%MySql_pw% --default-character-set=GB2312 -R --triggers --hex-blob --single-transaction -B %%i >%BAK_FILE% %RAR_Dir% a %BAK_FILE:~0,-4%.rar %BAK_FILE%DEL /F /A /Q %BAK_FILE%echo 数据库【%%i】 已经备份到%BAK_Dir%%BAK_Dir2%%BAK_FILE%.rar >> %LOG_FILE%):exitbatecho ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++/ >> %LOG_FILE%echo. 所有备份建立完毕:: 清除变量        SET MySql_pw=        SET BAK_Dir=        SET Bak_Time_ago=        SET RAR_Dir=        SET BAK_Dir2=        SET BAK_FILE=        SET LOG_FILE=

 

 

 

备案号:鲁ICP备13029499号-2 说三道四 www.s3d4.cn 说三道四技术文摘