[原創(chuàng)] mysql的備份腳本

oknet

備份期間需要鎖定整個(gè)庫，因此請(qǐng)避開高負(fù)載時(shí)間。
腳本采用FLUSH TABLES WITH READ LOCK指令鎖庫
直接拷貝數(shù)據(jù)文件，不采用dump的方式
UNLOCK TABLES之前輸出SHOW MASTER STATUS信息，因此備份出的內(nèi)容可以直接用來搭建Slave DB

1. 
   
2. #!/bin/bash
   
3. DBA_USER=root
   
4. DBA_PASSWD=dba
   
5. DB_HOST=127.0.0.1
   
6. DB_PORT=3306
   
7. DBLIST="mysql test"
   
8. MYSQL="/path/to/bin/mysql -u${DBA_USER} -p${DBA_PASSWD} -h ${DB_HOST} -P ${DB_PORT} -t -A -B"
   
9. DATAPATH=/path/to/mysql/datadir
   
10. BAKPATH=/path/to/backup.db
    
11. 
    
12. today=`date +%Y%m%d`
    
13. LOGFILE=${BAKPATH}/backup.mysql.${today}.log
    
14. BCMD=""
    
15. 
    
16. [ ! -d ${DATAPATH} ] && mkdir -p ${DATAPATH}
    
17. [ ! -d ${BAKPATH} ] && mkdir -p ${BAKPATH}
    
18. 
    
19. echo "" >>$LOGFILE
    
20. echo "" >>$LOGFILE
    
21. echo "=================================================" >>$LOGFILE
    
22. echo "=                                               =" >>$LOGFILE
    
23. echo "=  MySQL BACKUP                                 =" >>$LOGFILE
    
24. echo "=                                               =" >>$LOGFILE
    
25. echo "=================================================" >>$LOGFILE
    
26. 
    
27. echo "[`date "+%Y/%m/%d %H:%M:%S"`] Starting backup task ("$today") ... " >>$LOGFILE
    
28. 
    
29. echo -n "[`date "+%Y/%m/%d %H:%M:%S"`] Checking backup dir ("$BAKPATH/${today}") ... " >>$LOGFILE
    
30. [ -d $BAKPATH/${today} ] && echo -e "Already existed! please rename or delete it.\nEXIT." >>$LOGFILE && exit 1;
    
31. echo "Empty" >>$LOGFILE
    
32. 
    
33. echo -n "[`date "+%Y/%m/%d %H:%M:%S"`] Enter backup dir ("$BAKPATH/${today}") ... " >>$LOGFILE
    
34. mkdir -p $BAKPATH/${today}
    
35. cd $BAKPATH/${today} || { echo -e "Failed! \nEXIT" >>$LOGFILE; exit 1; }
    
36. echo "OK" >>$LOGFILE
    
37. 
    
38. BCMD=$BCMD"FLUSH TABLES WITH READ LOCK; system echo [\`date \"+%Y/%m/%d %H:%M:%S\"\`] All tables flushed and locked; "
    
39. BCMD=$BCMD"FLUSH LOGS; system echo [\`date \"+%Y/%m/%d %H:%M:%S\"\`] Binlog flushed.; SHOW MASTER STATUS; "
    
40. for DBNAME in $DBLIST ;
    
41. do
    
42.     BCMD=$BCMD"system echo -n [\`date \"+%Y/%m/%d %H:%M:%S\"\`] Backup database: "$DBNAME" ... ; system cp -a "$DATAPATH/$DBNAME" "'./'"; system echo finished.; "
    
43. done
    
44. 
    
45. echo "[`date "+%Y/%m/%d %H:%M:%S"`] Backup command is $BCMD" >>$LOGFILE
    
46. $MYSQL -e "$BCMD" >>$LOGFILE 2>&1
    
47. 
    
48. echo "[`date "+%Y/%m/%d %H:%M:%S"`] Finished!" >>$LOGFILE
    
49. mv $LOGFILE ./
    
50. echo "delete 5 days ago files" >> $LOGFILE
    
51. echo "the 5 days ago file is `find $BAKPATH -maxdepth 1 -type d -mtime +5` "  >> $LOGFILE
    
52. find $BAKPATH -maxdepth 1 -type d -mtime +5  -exec rm -rf {} \;
    
53. 
    

復(fù)制代碼

pxf520

FLUSH TABLES WITH READ LOCK對(duì)innodb不好使，你這樣的方式適合myisam引擎。