linux下mysql如何自动备份shell脚本
Linux服务器上的程序每天都在更新MySQL数据库，于是就想起写一个shell脚本，结合crontab，定时备份数据库。其实非常简单，主要就是使用MySQL自带的mysqldump命令。
#!/bin/bash #ShellscripttobackupMySqldatabase #TobackupNysqldatabasesfileto/backupdirandlaterpickupbyyour #script.Youcanskipfewdatabasesfrombackuptoo. #Formoreinfopleasesee(Installationinfo): #http://www.cyberciti.biz/nixcraft/vivek/blogger/2005/01/mysql-backup-script.html #Lastupdated:Aug-2005 #-------------------------------------------------------------------- #ThisisafreeshellscriptunderGNUGPLversion2.0orabove #Copyright(C)2004,2005nixCraftproject #Feedback/comment/suggestions:http://cyberciti.biz/fb/ #------------------------------------------------------------------------- #ThisscriptispartofnixCraftshellscriptcollection(NSSC) #Visithttp://bash.cyberciti.biz/formoreinformation. #------------------------------------------------------------------------- MyUSER="SET-MYSQL-USER-NAME"#USERNAME MyPASS="SET-PASSWORD"#PASSWORD MyHOST="localhost"#Hostname #Linuxbinpaths,changethisifitcannotbeautodetectedviawhichcommand MYSQL="$(whichmysql)" MYSQLDUMP="$(whichmysqldump)" CHOWN="$(whichchown)" CHMOD="$(whichchmod)" GZIP="$(whichgzip)" #BackupDestdirectory,changethisifyouhavesomeotherlocation DEST="/backup" #Maindirectorywherebackupwillbestored MBD="$DEST/mysql" #Gethostname HOST="$(hostname)" #Getdataindd-mm-yyyyformat NOW="$(date+"%d-%m-%Y")" #Filetostorecurrentbackupfile FILE="" #Storelistofdatabases DBS="" #DONOTBACKUPthesedatabases IGGY="test" [!-d$MBD]&&mkdir-p$MBD||: #Onlyrootcanaccessit! $CHOWN0.0-R$DEST $CHMOD0600$DEST #Getalldatabaselistfirst DBS="$($MYSQL-u$MyUSER-h$MyHOST-p$MyPASS-Bse'showdatabases')" fordbin$DBS do skipdb=-1 if["$IGGY"!=""]; then foriin$IGGY do ["$db"=="$i"]&&skipdb=1||: done fi if["$skipdb"=="-1"];then FILE="$MBD/$db.$HOST.$NOW.gz" #doallinonejobinpipe, #connecttomysqlusingmysqldumpforselectmysqldatabase #andpipeitouttogzfileinbackupdir:) $MYSQLDUMP-u$MyUSER-h$MyHOST-p$MyPASS$db|$GZIP-9>$FILE fi done
保存后将以上脚本加入crontab调度。如：每天早上四点半备份：304***/data/backup-db.sh
如果你使用mysql5.1，可能会提示mysqldump错误:
mysqldump:Couldn'texecute'showcreatetable`general_log`':Table'mysql.general_log'doesn'texist mysqldump:Couldn'texecute'showcreatetable`slow_log`':Table'mysql.slow_log'doesn'texist
原因是mysql库中没有show_log表和general_log表，需要手动创建：
CREATETABLEIFNOTEXISTSgeneral_log( event_timetimestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP, user_hostmediumtextNOTNULL, thread_idint(11)NOTNULL, server_idint(10)unsignedNOTNULL, command_typevarchar(64)NOTNULL, argumentmediumtextNOTNULL )ENGINE=CSVDEFAULTCHARSET=utf8COMMENT='Generallog'; CREATETABLEIFNOTEXISTSslow_log( start_timetimestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP, user_hostmediumtextNOTNULL, query_timetimeNOTNULL, lock_timetimeNOTNULL, rows_sentint(11)NOTNULL, rows_examinedint(11)NOTNULL, dbvarchar(512)NOTNULL, last_insert_idint(11)NOTNULL, insert_idint(11)NOTNULL, server_idint(10)unsignedNOTNULL, sql_textmediumtextNOTNULL )ENGINE=CSVDEFAULTCHARSET=utf8COMMENT='Slowlog';
方法二：
注意：
DumpFile=db$(date+%y%m%d)如果设置为这样一定要将此脚本放备份目录下才行。 DumpFile="$BackupPath"db$(date+%y%m%d)如果设置为这样，日志中会有这样的提示tar:Removingleading`/'frommembernames是因为备份的目录使用的是绝对路径，不过这样不影响数据，可以根据自己习惯而定。 -------------------------------------------------------------------start #!/bin/bash #ThisisaShellScriptForAutoDBBackup #Poweredbyaspbiz #2004-09 #Setting #设置数据库名，数据库登录名，密码，备份路径，日志路径，数据文件位置，以及备份方式 #默认情况下备份方式是tar，还可以是mysqldump,mysqldotcopy #默认情况下，用root(空)登录mysql数据库，备份至/root/dbxxxxx.tgz 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 ---------------------------------------------------------------------------------------------end
以上内容就是本文给大家介绍的linux下mysql如何自动备份shell脚本，希望大家喜欢。