對于MySQL備份方法有很多種,下面我來給大家整理在我們WEB運維過程中一些常用的數據庫備份方法,希望對各位同學會有所幫助.

總結下幾種常用的mysql備份方法.

一、直接拷貝數據庫文件

首先把內存中的數據都刷新到磁盤中,同時鎖定數據表,以保證拷貝過程中不會有新的數據寫入,代碼如下:

mysql>FLUSH TABLES WITH READ LOCK;

使用tar或cp等命令備份數據庫文件,這里使用tar:

tar zcvf /backup/mysql_$(date "+%Y%m%d").tar.gz /var/lib/mysql

c、備份完后解鎖數據表

mysql> unlock tables;這種方法備份出來的數據恢復也很簡單,直接拷貝回原來的數據庫目錄下即可,需要注意,對于 Innodb 類型表來說,還需要備份其日志文件,即 ib_logfile* 文件,因為當 Innodb 表損壞時,就可以依靠這些日志文件來恢復.

二、使用mysqlhotcopy備份

mysqlhotcopy 是一個 PERL 程序,使用 LOCK TABLES、FLUSH TABLES 和 cp 或 scp 來快速備份數據庫,它是備份數據庫或單個表的最快的途徑,但它只能運行在數據庫文件,包括數據表定義文件、數據文件、索引文件,所在的機器上.

mysqlhotcopy 只能用于備份 MyISAM.

例,把數據庫test備份到/backup目錄下,代碼如下:

/usr/local/mysql/bin/mysqlhotcopy -uroot -pxxx test /backup

mysqlhotcopy在執行前將會鎖定數據庫,執行完畢后將自動釋放鎖定.

還原:mysqlhotcopy 備份出來的是整個數據庫目錄,還原時直接拷貝覆蓋就行了,注意權限問題,代碼如下:

cp -rf /backup/test /var/lib/mysql/

注意,想要使用 mysqlhotcopy,必須要有 SELECT、RELOAD(要執行 FLUSH TABLES) 權限,并且還必須要能夠有讀取 datadir/db_name 目錄的權限.

三、mysqldump

mysqldump 是SQL級別的備份機制,它將數據表導成 SQL 腳本文件,可以方便的在不同的mysql版本間進行恢復或升級,這也是最常用的備份方法.

例,使用mysqldump備份,代碼如下:

/usr/local/mysql/bin/mysqldump -uroot -pxxx --opt test >/backup/test.sql

還原:/usr/local/mysql/bin/mysqldump -uroot -pxxx test

四、使用主從復制機制(replication)（實現數據庫實時備份）推薦

五、mysql自動備份腳本,使用mysqldump工具,代碼如下:

1. #!/bin/bash 
2. #caishzh 20120509 
3.  
4. BAKDIR="/backup/mysql"     #mysql備份目錄 
5. MAIL="xxx@xxx.com"          #郵箱地址 
6. USER="root"                         #mysql用戶名 
7. PASSWD="jjj"                         #mysql密碼 
8. DB="test"                              #數據庫名 
9. DATE=$(date "+%Y%m%d")     #日期 
10.  
11. [ -d "$BAKDIR" ] || mkdir -p "$BAKDIR"     #如果備份目錄不存在則創建 
12.  
13. mysqldump -opt -u$USER -p$PASSWD $DB |gzip > $BKDIR/$DB_$DATA.gz     #備份并使用gzip打包 
14.  
15. if [ $? -eq 0 ];then 
16.      #echo "$DB mysql backup" |mutt $MAIL -a $BKDIR/$DB_$DATA.sql -s "$DB mysql backup" 
17.      ls -lh $BAKDIR/*.gz>$BAKDIR/messages.txt 
18.      mutt  $MAIL -a $BKDIR/$DB_$DATA.gz -s "$DATE：$DB mysql backup" < $BAKDIR/messages.txt 
19. else 
20.      echo "$DATE mysql backup failed"|mail  -s "$DATE msyql bacup failed"  $MAIL 
21. fi 

六,此方法只可用于windows主機

假想環境,MySQL 安裝位置：C:/MySQL,數據庫名稱為:bbs,數據庫備份目的地:d:/db_bak/.

新建db_bak.bat,寫入以下代碼:

1. net stop mysql 
2. xcopy c:/mysql/data/bbs/*.* d:/db_bak/bbs/%date:~0,3%/ /y 
3. net start mysql 
4. 或者 
5. net stop mysql 
6. xcopy D:/Mysql/data D:/Mysql/BAK/BAK-%date:~0,10%  /S/E/I 
7. net start mysql 

然后使用Windows的“計劃任務”定時執行該批處理腳本即可,例如,每天凌晨3點執行back_db.bat,保存一周的數據,用%date:~0,3%,保存每天的數據,用%date:~4,10%,代碼如下:

1. xcopy c:/. d:/aaa /s/e 
2.                    
3. XCOPY source [destination] [/A | /M] [/D[:date]] [/P] 
4.                            [/C] [/I] [/Q] [/F] [/L] [/ 
5.                            [/K] [/N] [/O] [/X] [/Y] [/ 
6.                            [/EXCLUDE:file1[+file2][+fi 

七,linux增量備份,代碼如下:

1. #!/bin/sh 
2. #+++++++++++++mysqlback++++++++++++ 
3. #a mysql incremental backup script. 
4. #by flute 
5. #++++++++++++++++++++++++++++++++++ 
6. BAK_DIR="." 
7. while getopts :u:p:d:c OPTION 
8. do 
9.   case ${OPTION} in 
10.       u) 
11.           DB_USER=${OPTARG} 
12.           ;; 
13.       p) 
14.           PASSWD=${OPTARG} 
15.           ;; 
16.       d) 
17.           DB_NAME=${OPTARG} 
18.           ;; 
19.       b) 
20.           BAK_DIR=${OPTARG} 
21.           ;; 
22.       c) 
23.           INIT="true" 
24.           ;; 
25.       /?) 
26.       echo "Usage: mysqlback.sh [OPTIONS]" 
27.       echo "-u        user for login" 
28.       echo "-p        Password to use when connecting to server" 
29.       echo "-d        which database to backup" 
30.       echo "-b        backup database files to directory,default is current directory" 
31.       echo "-c        must be usage for first backup,is complete backup" 
32.       exit 1 
33.       ;; 
34.   esac 
35. done 
36. MYSQL_BIN=/usr/local/mysql/bin 
37. DATADIR=`${MYSQL_BIN}/mysql -u ${DB_USER} -p${PASSWD} -e "show variables like 'datadir'" | sed -n '2p' | awk '{print $NF}'` 
38. upfile ()    #上傳文件 
39. { 
40.     _FNAME=$1 
41.     _TNAME=`echo ${_FNAME}|sed 's//.sql//g'` 
42.     _TNAME="${_TNAME}.tgz" 
43.     tar -czf ${_TNAME} ${_FNAME} 
44.     if [ $? = 0 ] 
45.         then 
46.         scp ${_TNAME} 192.168.1.10:/usr/local/mysql/bak 
47.         if [ $? = 0 ] 
48.             then 
49.             echo "upfile success." 
50.         else 
51.             logger -f ~/mysqlbak.log -t MYSQLBAK 'upload file ${_TNAME} failed' 
52.             ssh 192.168.1.10 "logger -f ~/mysqlbak.log -t MYSQLBAK 'upload file ${_TNAME} failed'" 
53.         fi 
54.     else 
55.         echo "tar file failed." 
56.     fi 
57. } 
58. bak_file ()    #獲取備份文件名 
59. { 
60.     _DB_NAME=$1 
61.     _BAK_DIR=$2 
62.     _BDATE=`date "+%Y-%m-%d"` 
63.     _BAKFILE="${_BAK_DIR}/_${_DB_NAME}_${_BDATE}.sql" 
64.     if [ ! -f ${_BAKFILE} ] 
65.     then 
66.         echo ${_BAKFILE} 
67.         return 0 
68.     else 
69.     ID=1 
70.     while [ 1 ] 
71.           do 
72.           _BAKFILE="${_BAK_DIR}/_${_DB_NAME}_${_BDATE}_${ID}.sql" 
73.           if [ ! -f ${_BAKFILE} ] 
74.               then 
75.               echo ${_BAKFILE} 
76.               return 0 
77.           fi 
78.           ID=`expr $ID + 1` 
79.     done 
80.          
81.     fi 
82. } 

八,windows增量備份,代碼如下:

1. mkdir %BAKPATH%＼data 
2.  
3. %MYSQLPATH%＼bin＼mysqldump -u%USERNAME% -p%PASSWORD% --single-transaction --default- 
4. character-set=utf8 --flush-logs --master-data=2 --delete-master-logs spirit_beast > % 
5. BAKPATH%＼data＼spirit_beast%date:~0,10%.sql 
6.  
7.  
8. %MYSQLPATH%＼bin＼mysqldump -u%USERNAME% -p%PASSWORD% --single-transaction --default- 
9. character-set=utf8 --flush-logs --master-data=2 --delete-master-logs quartz_event > % 
10. BAKPATH%＼data＼quartz_event%date:~0,10%.sql 
11.  
12.  
13. rar a -ag %BAKPATH%＼full＼ %BAKPATH%＼data＼*.sql 
14.  
15. rmdir /s /q %BAKPATH%＼data＼ 
16. //Vevb.com 
17. @echo  %date% %time% full bakup finish >> C:/mysqlbakup.log