萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 資料庫備份完整指南:從基礎命令到自動化腳本實戰

MySQL 資料庫備份核心命令詳解

MySQL 資料庫備份其實只需一條 mysqldump 指令即可完成,但對於初學者而言,掌握這條指令的各種參數組合至關重要。本文將系統整理從基礎導出到生產環境自動化備份的完整方案。

1. 導出整個資料庫

預設導出檔案會存放在 mysql\bin 目錄下:

mysqldump -u 用戶名 -p 資料庫名 > 導出檔案名.sql
# 範例
mysqldump -u user_name -p123456 database_name > outfile_name.sql

2. 導出單一資料表

mysqldump -u 用戶名 -p 資料庫名 表名 > 導出檔案名.sql
# 範例
mysqldump -u user_name -p database_name table_name > outfile_name.sql

3. 僅導出資料庫結構(無數據)

mysqldump -u user_name -p -d --add-drop-table database_name > outfile_name.sql
# -d:不導出數據
# --add-drop-table:在每個 CREATE 語句前加上 DROP TABLE

4. 指定字元集導出

mysqldump -uroot -p --default-character-set=latin1 --set-charset=gbk --skip-opt database_name > outfile_name.sql

5. 實戰範例:備份 aaa 資料庫

[root@test1 root]# cd /home/data/mysql
[root@test1 mysql]# mysqldump -u root -p --opt aaa > back_aaa

Windows 環境下的自動化備份方案

批次檔備份腳本

假設環境:MySQL 安裝於 C:\MySQL,資料庫名稱為 bbs,備份目標 C:\db_bak\

建立 db_bak.bat,內容如下:

*******************************Code Start*****************************
net stop mysql
xcopy c:\mysql\data\bbs\*.* c:\db_bak\bbs\%date:~0,10%\ /S /I
net start mysql
*******************************Code End *****************************

透過 Windows「工作排程器」設定定時執行(如每天凌晨 3 點)。

優缺點分析

  • 優點:操作簡單、完整性高、備份週期控制靈活(利用 %date:~0,10% 實現按日期建立資料夾)。
  • 缺點:佔用空間大、備份期間 MySQL 會短暫斷開(30MB 資料庫約 5 秒)。適合擁有獨立主機但缺乏 MySQL 管理經驗的用戶。

Linux 環境:自動備份並上傳至遠端 FTP 伺服器

實施步驟

  1. 建立備份目錄:
    cd /home
    mkdir mysql_data
  2. 建立備份腳本 /home/mysql_data/mysql_databak.sh:
    #!/bin/sh
    DUMP=/usr/bin/mysqldump
    OUT_DIR=/home/mysql_data
    LINUX_USER=root
    DB_NAME=data
    DB_USER=root
    DB_PASS=123456
    DAYS=7  # 保留 7 天備份
    
    cd $OUT_DIR
    DATE=`date +%Y_%m_%d`
    OUT_SQL="$DATE.sql"
    TAR_SQL="mysqldata_bak_$DATE.tar.gz"
    
    $DUMP -u$DB_USER -p$DB_PASS $DB_NAME --default-character-set=utf8 --opt -Q -R --skip-lock-tables > $OUT_SQL
    tar -czf $TAR_SQL ./$OUT_SQL
    rm $OUT_SQL
    chown $LINUX_USER:$LINUX_USER $OUT_DIR/$TAR_SQL
    find $OUT_DIR -name "mysqldata_bak_*" -type f -mtime +$DAYS -exec rm {} \;
    
    # FTP 上傳區塊
    deldate=`date -d -7day +%Y_%m_%d`
    ftp -n<<!
    open 192.168.1.1 21
    user admin 123456
    binary
    cd mysqlbak
    lcd /home/mysql_data
    prompt
    put mysqldata_bak_$DATE.tar.gz mysqldata_bak_$DATE.tar.gz
    delete mysqldata_bak_$deldate.tar.gz mysqldata_bak_$deldate.tar.gz
    close
    bye
    !
  3. 賦予執行權限:
    chmod +x /home/mysql_data/mysql_databak.sh
  4. 設定 crontab 定時任務:
    vi /etc/crontab
    # 每天凌晨 1:30 執行
    30 1 * * * root /home/mysql_data/mysql_databak.sh
  5. 重啟 crond 服務:
    /etc/rc.d/init.d/crond restart
    chkconfig crond on
    service crond start

恢復操作

每天在 /home/mysql_data 目錄下會生成類似 mysqldata_bak_2012_12_19.tar.gz 的壓縮檔。恢復時只需解壓:

tar -zxvf mysqldata_bak_2012_12_19.tar.gz
copyright © 萬盛學電腦網 all rights reserved