詳解MySQL資料備份之mysqldump使用方法
mysqldump常用於MySQL資料庫邏輯備份。
1、各種用法說明
A. 最簡單的用法:
?
12 |
mysqldump -uroot -pPassword [ database name ] > [dump file]
|
上述命令將指定資料庫備份到某dump檔案(轉儲檔案)中,比如:
?
1 | mysqldump -uroot -p123 test > test.dump |
生成的test.dump檔案中包含建表語句(生成資料庫結構哦)和插入資料的insert語句。
B. --opt
如果加上--opt引數則生成的dump檔案中稍有不同:
建表語句包含drop table if exists tableName
insert之前包含一個鎖表語句lock tables tableName write,insert之後包含unlock tables
C. 跨主機備份
使用下面的命令可以將host1上的sourceDb複製到host2的targetDb,前提是host2主機上已經建立targetDb資料庫:
?
1 |
mysqldump --host=host1 --opt sourceDb| mysql --host=host2 -C targetDb
|
-C指示主機間的資料傳輸使用資料壓縮
D. 只備份表結構
?
1 |
mysqldump --no-data --databases mydatabase1 mydatabase2 mydatabase3 > test.dump
|
將只備份表結構。--databases指示主機上要備份的資料庫。如果要備份某個MySQL主機上的所有資料庫可以使用--all-databases選項,如下:
?
12 |
mysqldump --all-databases > test.dump
|
E. 從備份檔案恢復資料庫
?
1 |
mysql [ database name ]
|
2、結合Linux的cron命令實現定時備份
比如需要在每天凌晨1:30備份某個主機上的所有資料庫並壓縮dump檔案為gz格式,那麼可在/etc/crontab配置檔案中加入下面程式碼行:
?
1 |
30 1 * * * root mysqldump -u root -pPASSWORD --all-databases | gzip > /mnt/disk2/database_ ` date '+%m-%d-%Y' `.sql.gz
|
前面5個引數分別表示分鐘、小時、日、月、年,星號表示任意。date '+%m-%d-%Y'得到當前日期的MM-DD-YYYY格式。
3、一個完整的Shell指令碼備份MySQL資料庫示例
?
#vi /backup/backup.sh #!bin/bash cd /backup echo "You are in backup dir" mv backup* /oldbackup echo "Old dbs are moved to oldbackup folder" File = backup-$Now.sql mysqldump -u user -p password database-name > $File echo "Your database backup successfully completed"
|
上面指令碼檔案儲存為backup.sh,並且系統中已經建立兩個目錄/olcbackup和/backup。每次執行backup.sh時都會先將/backup目錄下所有名稱為backup開頭的檔案移到/oldbackup目錄。
為上述指令碼制定執行計劃如下:
?
12 |
#crontab -e 30 1 * * * /backup .sh
|
4、mysqldump全量備份+mysqlbinlog二進位制日誌增量備份
從mysqldump備份檔案恢復資料會丟失掉從備份點開始的更新資料,所以還需要結合mysqlbinlog二進位制日誌增量備份。確保my.ini或者my.cnf中包含下面的配置以啟用二進位制日誌,或者mysqld ---log-bin:
?
12 |
[mysqld] log-bin=mysql-bin
|
mysqldump命令必須帶上--flush-logs選項以生成新的二進位制日誌檔案:
?
1 | mysqldump --single-transaction --flush-logs --master-data=2 > backup.sql |
這樣生成的增量二進位制日誌檔案比如為mysql-bin.000003,那麼恢復資料時如下:
?
12 | shell> mysql -uroot -pPwd |
此外mysqlbinlog還可以指定--start-date、--stop-date、--start-position和--stop-position引數,用於精確恢復資料到某個時刻之前或者跳過中間某個出問題時間段恢復資料,直接摘錄MySQL文件說明中相關內容如下:
?
5.9.3.1. 指定恢復時間 對於MySQL 4.1.4,可以在mysqlbinlog語句中透過--start-date和--stop-date選項指定DATETIME格式的起止時間。舉例說明,假設在今天上午10:00(今天是2005年4月20日),執行SQL語句來刪除一個大表。要想恢復表和資料,你可以恢復前晚上的備份,並輸入: mysqlbinlog --stop-date="2005-04-20 9:59:59" /var/log/mysql/bin.123456 | mysql -u root -pmypwd 該命令將恢復截止到在--stop-date選項中以DATETIME格式給出的日期和時間的所有資料。如果你沒有檢測到幾個小時後輸入的錯誤的SQL語句,可能你想要恢復後面發生的活動。根據這些,你可以用起使日期和時間再次執行mysqlbinlog: mysqlbinlog --start-date="2005-04-20 10:01:00" /var/log/mysql/bin.123456 | mysql -u root -pmypwd 在該行中,從上午10:01登入的SQL語句將執行。組合執行前夜的轉儲檔案和mysqlbinlog的兩行可以將所有資料恢復到上午10:00前一秒鐘。你應檢查日誌以確保時間確切。下一節介紹如何實現。 5.9.3.2. 指定恢復位置 也可以不指定日期和時間,而使用mysqlbinlog的選項--start-position和--stop-position來指定日誌位置。它們的作用與起止日選項相同,不同的是給出了從日誌起的位置號。使用日誌位置是更準確的恢復方法,特別是當由於破壞性SQL語句同時發生許多事務的時候。要想確定位置號,可以執行mysqlbinlog尋找執行了不期望的事務的時間範圍,但應將結果重新指向文字檔案以便進行檢查。操作方法為: mysqlbinlog --start-date="2005-04-20 9:55:00" --stop-date="2005-04-20 10:05:00" /var/log/mysql/bin.123456 > /tmp/mysql_restore.sql 該命令將在/tmp目錄建立小的文字檔案,將顯示執行了錯誤的SQL語句時的SQL語句。你可以用文字編輯器開啟該檔案,尋找你不要想重複的語句。如果二進位制日誌中的位置號用於停止和繼續恢復操作,應進行註釋。用log_pos加一個數字來標記位置。使用位置號恢復了以前的備份檔案後,你應從命令列輸入下面內容: mysqlbinlog --stop-position="368312" /var/log/mysql/bin.123456 | mysql -u root -pmypwd mysqlbinlog --start-position="368315" /var/log/mysql/bin.123456 | mysql -u root -pmypwd 上面的第1行將恢復到停止位置為止的所有事務。下一行將恢復從給定的起始位置直到二進位制日誌結束的所有事務。因為mysqlbinlog的輸出包括每個SQL語句記錄之前的SET TIMESTAMP語句,恢復的資料和相關MySQL日誌將反應事務執行的原時間。
|
以上就是本文的全部內容,希望對大家的學習有所幫助,也希望大家多多支援指令碼之家。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/4686/viewspace-2805642/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- mysqldump使用方法(MySQL資料庫的備份與恢復)MySql資料庫
- Mysql備份和還原資料庫-mysqldumpMySql資料庫
- 使用Mysqldump備份和恢復MySQL資料庫MySql資料庫
- (7) MySQL資料庫備份詳解MySql資料庫
- mysqldump 資料庫備份程式MySql資料庫
- mysql5.6 mysqldump備份報錯MySql
- MySQL主從配置及mysqldump備份MySql
- Linux基礎命令---mysqldump資料庫備份LinuxMySql資料庫
- MySQL資料備份MySql
- 【mysqldump】mysqldump及備份恢復示例MySql
- 【資料分析師_02_SQL+MySQL】030_MySQL的資料備份和效能管理(MYSQLDUMP,MYSQLHOTCOPY,INDEX,EXPLAIN)MySqlIndexAI
- Mysqldump的備份流程MySql
- MySQLDump的備份方法MySql
- mysqldump備份技巧分享MySql
- 初探MySQL資料備份及備份原理MySql
- Mysqldump備份說明及資料庫備份指令碼分享-運維筆記MySql資料庫指令碼運維筆記
- mysql 資料庫 備份MySql資料庫
- Mysql資料安全備份MySql
- 雲伺服器mysql備份與還原資料庫命令詳解伺服器MySql資料庫
- windows mysqldump備份指令碼WindowsMySql指令碼
- Oracle資料庫備份還原詳解XKUSOracle資料庫
- innobackupex 備份MySQL資料庫MySql資料庫
- mysqlbinlog命令詳解 Part 9 MySQL備份策略MySql
- mysqldump備份時如何保持資料的一致性MySql
- oracle資料庫備份之exp增量備份Oracle資料庫
- 簡單的mysqldump備份(windows)MySqlWindows
- mysql資料庫備份命令大全MySql資料庫
- Mysql資料備份和還原MySql
- 備份mysql資料庫報告MySql資料庫
- 1. 備份MySQL資料庫MySql資料庫
- MySQL資料庫的基本備份MySql資料庫
- Mysql資料庫自動備份MySql資料庫
- Mysql資料備份與恢復MySql
- 定時備份MySQL資料庫MySql資料庫
- mysqldump 備份匯出資料排除某張表或多張表MySql
- 詳解MYSQL的備份還原(PHP實現)MySqlPHP
- windows下使用mysqldump備份資料庫並上傳到阿里雲OSSWindowsMySql資料庫阿里
- Mysql定時備份資料指令碼MySql指令碼