MySQL中數(shù)據(jù)庫備份恢復(fù)的常用方法詳解
更新時間：2026年04月28日 09:21:46 作者：長戀離亭
本文深入探討了數(shù)據(jù)庫備份的重要性和分類,詳細講解了MySQL完全備份與增量備份的實現(xiàn)方法,包括冷備份、熱備份、溫備份和基于二進制日志的增量恢復(fù)技術(shù),希望對大家有所幫助
一、數(shù)據(jù)庫備份的分類
1.1 數(shù)據(jù)備份的重要性
- 在生產(chǎn)環(huán)境中，數(shù)據(jù)的安全性至關(guān)重要
- 任何數(shù)據(jù)的丟失都可能產(chǎn)生嚴重的后果
- 造成數(shù)據(jù)丟失的原因
- 程序錯誤
- 人為操作錯誤
- 運算錯誤
- 磁盤故障
- 災(zāi)難(如火災(zāi)、地震)和盜竊
1.2 數(shù)據(jù)庫備份的分類
從物理與邏輯的角度，備份可分為
- 物理備份:對數(shù)據(jù)庫操作系統(tǒng)的物理文件(如數(shù)據(jù)文件、日志文件等)的備份
- 邏輯備份:對數(shù)據(jù)庫邏輯組件(如:表等數(shù)據(jù)庫對象)的備份
物理備份方法
- 冷備份(脫機備份) :是在關(guān)閉數(shù)據(jù)庫的時候進行的
- 熱備份(聯(lián)機備份) :數(shù)據(jù)庫處于運行狀態(tài),依賴于數(shù)據(jù)庫的日志文件
- 溫備份:數(shù)據(jù)庫鎖定表格(不可寫入但可讀)的狀態(tài)下進行備份操作
從數(shù)據(jù)庫的備份策略角度，備份可分為
- 完全備份:每次對數(shù)據(jù)庫進行完整的備份
- 差異備份:備份自從上次完全備份之后被修改過的文件
- 增量備份:只有在上次完全備份或者增量備份后被修改的文件才會被備份
1.3 常見的備份方法
物理冷備
- 備份時數(shù)據(jù)庫處于關(guān)閉狀態(tài)，直接打包數(shù)據(jù)庫文件
- 備份速度快，恢復(fù)時也是最簡單的
專用備份工具mydump或mysqlhotcopy
- mysqldump常用的邏輯備份工具
- mysqlhotcopy僅擁有備份MyISAM和ARCHIVE表
啟用二進制日志進行增量備份：進行增量備份，需要刷新二進制日志
第三方工具備份：免費的MySQL熱備份軟件Percona XtraBackup
二、MySQL完全備份
- 是對整個數(shù)據(jù)庫、數(shù)據(jù)庫結(jié)構(gòu)和文件結(jié)構(gòu)的備份
- 保存的是備份完成時刻的數(shù)據(jù)庫
- 是差異備份與增量備份的基礎(chǔ)
優(yōu)點
- 備份與恢復(fù)操作簡單方便
缺點
- 數(shù)據(jù)存在大量的重復(fù)
- 占用大量的備份空間
- 備份與恢復(fù)時間長
2.1 數(shù)據(jù)庫完全備份分類
物理冷備份與恢復(fù)
- 關(guān)閉MySQL數(shù)據(jù)庫
- 使用tar命令直接打包數(shù)據(jù)庫文件夾
- 直接替換現(xiàn)有MySQL目錄即可
mysqldump備份與恢復(fù)
- MySQL自帶的備份工具，可方便實現(xiàn)對MySQL的備份
- 可以將指定的庫、表導(dǎo)出為SQL腳本
- 使用命令mysq|導(dǎo)入備份的數(shù)據(jù)
2.2 MySQL物理冷備份及恢復(fù)
物理冷備份
[root@localhost mysql]# systemctl stop mysqld.service [root@localhost ~]# cd /usr/local/mysql/ [root@localhost mysql]# tar zcvf /opt/mysql_all-$(date +%F).tar.gz data/ [root@localhost mysql]# cd /opt [root@localhost opt]# ls mysql_all-2020-08-18.tar.gz
恢復(fù)數(shù)據(jù)庫
[root@localhost opt]# cd /usr/local/mysql/ [root@localhost mysql]# rm -rf data/ '刪除數(shù)據(jù)庫文件' [root@localhost mysql]# mysql -uroot -p mysql> show databases; '此時數(shù)據(jù)庫文件消失' +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.00 sec) [root@localhost mysql]# cd /opt [root@localhost opt]# tar zxvf mysql_all-2020-08-18.tar.gz -C /usr/local/mysql/ '將備份的文件恢復(fù)' [root@localhost mysql]# systemctl start mysqld.service [root@localhost mysql]# mysql -uroot -p mysql> show databases; '數(shù)據(jù)恢復(fù)' +--------------------+ | Database | +--------------------+ | information_schema | | mydatabase | | mysql | | performance_schema | | sys | +--------------------+ 6 rows in set (0.00 sec)
2.3 mysqkdump備份數(shù)據(jù)庫
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | apple | | mydatabase | | mysql | | performance_schema | | sys | +--------------------+ 6 rows in set (0.00 sec) mysql> use mydatabase; Database changed mysql> show tables; +----------------------+ | Tables_in_mydatabase | +----------------------+ | mytable | +----------------------+ 1 row in set (0.00 sec) mysql> select * from mytable; +----+----------+-------+---------+ | id | name | score | address | +----+----------+-------+---------+ | 1 | zhangsan | 80.00 | sh | | 2 | lisi | 77.00 | nj | +----+----------+-------+---------+ 2 rows in set (0.00 sec)
mysqldump命令對單個庫進行完全備份
mysqldump -u用戶名-p [密碼] [選項] [數(shù)據(jù)庫名] > /備份路徑/備份文件名
[root@localhost ~]# mysqldump -uroot -p123456 mydatabase > /opt/mydatabase.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. [root@localhost opt]# ls mydatabase.sql
mysqldump命令對多個庫進行完全備份
mysqldump -u 用戶名 -p [密碼] [選項] --databases 庫名1 [庫名2] ... > /備份路徑/備份文件名
[root@localhost ~]# mysqldump -uroot -p123456 --databases mydatabase apple > /opt/mydatabase-apple.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. [root@localhost opt]# ls mydatabase-apple.sql mydatabase.sql
對所有庫進行完全備份
mysqldump -u 用戶名 -p [密碼] [選項] --all-databases > /備份路徑/備份文件名
[root@localhost ~]# mysqldump -uroot -p123456 --all-databases > /opt/all.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. [root@localhost opt]# ls all.sql mydatabase-apple.sql mydatabase.sql
mysqldump可針對庫內(nèi)特定的表進行備份
mysqldump -u 用戶名 -p [密碼] [選項] 數(shù)據(jù)庫名 表名 > /備份路徑/備份文件名
[root@localhost opt]# mysqldump -uroot -p123456 mydatabase mytable > /opt/mydatabase.mytable.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. [root@localhost opt]# ls all.sql mydatabase.sql mydatabase-apple.sql mydatabase.mytable.sql
2.4 恢復(fù)數(shù)據(jù)庫
使用mysqldump導(dǎo)出的腳本，可使用導(dǎo)入的方法
- source命令，用在mysq|中, 只可以使用絕對路徑
- mysq|命令，用在linux下面
使用source恢復(fù)數(shù)據(jù)庫的步驟
- 登錄到MySQL數(shù)據(jù)庫
- 執(zhí)行source備份sq|腳本的路徑
source恢復(fù)的示例
mysql > source /backup/all-data.sql
mysql> drop table mytable; Query OK, 0 rows affected (0.00 sec) mysql> show tables; Empty set (0.00 sec) mysql> source /opt/mydatabase.sql mysql> show tables; +----------------------+ | Tables_in_mydatabase | +----------------------+ | mytable | +----------------------+ 1 row in set (0.00 sec)
mysql> drop database apple; Query OK, 1 row affected (0.00 sec) mysql> drop database mydatabase; Query OK, 1 row affected (0.01 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) mysql> source /opt/mydatabase-apple.sql mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | apple | | mydatabase | | mysql | | performance_schema | | sys | +--------------------+ 6 rows in set (0.00 sec)
使用mysql命令恢復(fù)數(shù)據(jù)庫
mysql -u 用戶名 -p 密碼 < 庫備份腳本的路徑
例：
[root@localhost opt]# mysql -uroot -p123456 < /opt/mydatabase-apple.sql
恢復(fù)表的操作
- 恢復(fù)表時同樣可以使用source或者mysql命令
- source恢復(fù)表的操作與恢復(fù)庫的操作相同
- 當備份文件中只包含表的備份，而不包括創(chuàng)建庫的語句時，必須指定庫名，且目標庫必須存在
mysql -u 用戶名 -p 密碼 < 表備份腳本的路徑
[root@localhost opt]# mysql -uroot -p123456 < /opt/mydatabase.info.sql
在生產(chǎn)環(huán)境中，可以使用shell腳本自動實現(xiàn)定時備份
三、MySQL增量備份
使用mysqldump進行完全備份存在的問題
- 備份數(shù)據(jù)中有重復(fù)數(shù)據(jù)
- 備份時間與恢復(fù)時間過長
是自上一次備份后增加/變化的文件或者內(nèi)容
特點
- 沒有重復(fù)數(shù)據(jù)，備份量不大，時間短
- 恢復(fù)需要_上次完全備份及完全備份之后所有的增量備份才能恢復(fù)，而且要對所有增量備份進行逐個反推恢復(fù)
MySQL沒有提供直接的增量備份方法
可通過MySQL提供的二進制日志間接實現(xiàn)增量備份
MySQL二進制日志對備份的意義
- 二進制日志保存了所有更新或者可能更新數(shù)據(jù)庫的操作
- 二進制日志在啟動MySQL服務(wù)器后開始記錄，并在文件達到max_binlog_size所設(shè)置的大小或者接收到flush logs命令后重新創(chuàng)建新的日志文件
- 只需定時執(zhí)行flush logs方法重新創(chuàng)建新的日志，生成二進制文件序列，并及時把這些日志保存到安全的地方就完成了一個時間段的增量備份
[root@localhost opt]# vim /etc/my.cnf ....... [mysqld] user = mysql basedir = /usr/local/mysql datadir=/usr/local/mysql/data port = 3306 character_set_server=utf8 pid-file = /usr/local/mysql/mysqld.pid socket = /usr/local/mysql/mysql.sock log-bin=mysql-bin '添加以mysql-bin為開頭的二進制文件' server-id = 1 [root@localhost data]# ls apple ibdata1 ibtmp1 mysql-bin.000001 '二進制日志文件' auto.cnf ib_logfile0 mydatabase mysql-bin.index ib_buffer_pool ib_logfile1 mysql
3.1 MySQL數(shù)據(jù)庫增量恢復(fù)
一般恢復(fù)：將所有備份的二進制日志內(nèi)容全部恢復(fù)
mysqlbinlog [--no-defaults] 增量備份文件 | mysql -u 用戶名 -p
基于位置恢復(fù)
- 數(shù)據(jù)庫在某一時間點可能既有錯誤的操作也有正確的操作
- 可以基于精準的位置跳過錯誤的操作
恢復(fù)數(shù)據(jù)到指定位置 mysqlbinlog --stop-position='操作id' 二進制日志 |mysql -u 用戶名 -p 密碼 從指定的位置開始恢復(fù)數(shù)據(jù) mysqlbinlog --start-position='操作id' 二進制日志 |mysql -u 用戶名 -p 密碼
基于時間點恢復(fù)
跳過某個發(fā)生錯誤的時間點實現(xiàn)數(shù)據(jù)恢復(fù)
從日志開頭截止到某個時間點的恢復(fù) mysqlbinlog [--no-defaults] --stop-datetime='年-月-日 小時:分鐘:秒' 二進制日志 |mysql -u 用戶名 -p 密碼 從某個時間點到日志結(jié)尾的恢復(fù) mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小時:分鐘:秒' 二進制日志 |mysql -u 用戶名 -p 密碼 從某個時間點到某個時間點的恢復(fù) mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小時:分鐘:秒' --stop-datetime='年-月-日 小時:分鐘:秒' 二進制日志 |mysql -u 用戶名 -p 密碼
3.2 增量備份及恢復(fù)的具體操作
操作前先進行完整備份
[root@localhost ~]# mysqldump -uroot -p123456 mydatabase mytable > /opt/mytable.sql
開啟日志文件
[root@localhost ~]# vim /etc/my.cnf [mysqld] user = mysql basedir = /usr/local/mysql datadir=/usr/local/mysql/data port = 3306 character_set_server=utf8 pid-file = /usr/local/mysql/mysqld.pid socket = /usr/local/mysql/mysql.sock log-bin=mysql-bin '開啟二進制日志文件' [root@localhost data]# systemctl restart mysqld.service [root@localhost data]# ls apple ibdata1 ibtmp1 mysql-bin.000001 '二進制日志文件' auto.cnf ib_logfile0 mydatabase mysql-bin.index ib_buffer_pool ib_logfile1 mysql
進行模擬誤操作
mysql> select * from mytable; +----+----------+-----+ | id | name | age | +----+----------+-----+ | 1 | zhangsan | 22 | | 2 | lisi | 26 | | 3 | wangwu | 30 | +----+----------+-----+ 3 rows in set (0.00 sec) mysql> insert into mytable values (4,'zhaoliu','20'); '執(zhí)行正確操作' Query OK, 1 row affected (0.02 sec) mysql> delete from mytable where id=1; '進行誤操作' Query OK, 1 row affected (0.00 sec) mysql> insert into mytable values (5,'qiqi','21'); '執(zhí)行正確操作' Query OK, 1 row affected (0.01 sec) mysql> select * from mytable; +----+---------+-----+ | id | name | age | +----+---------+-----+ | 2 | lisi | 26 | | 3 | wangwu | 30 | | 4 | zhaoliu | 20 | | 5 | qiqi | 21 | +----+---------+-----+ 4 rows in set (0.00 sec)
進行增量備份
[root@localhost data]# mysqladmin -uroot -p123456 flush-logs mysqladmin: [Warning] Using a password on the command line interface can be insecure.
解碼二進制日志文件
[root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000001 > /opt/bk01.txt
[root@localhost data]# vim /opt/bk01.txt # at 584 '正常操作結(jié)束' #200822 15:06:12 server id 1 end_log_pos 646 CRC32 0x591ad99d Table_map: `mydatabase`.`mytable` mapped to number 108 # at 646 '執(zhí)行的誤操作' #200822 15:06:12 server id 1 end_log_pos 698 CRC32 0x4ddb56be Delete_rows: table id 108 flags: STMT_END_F ### DELETE FROM `mydatabase`.`mytable` ### WHERE ### @1=1 ### @2='zhangsan' ### @3='22' # at 698 '正常操作開始' #200822 15:06:12 server id 1 end_log_pos 729 CRC32 0x30b1bed7 Xid = 7 COMMIT/*!*/; #200822 15:06:30 server id 1 end_log_pos 794 CRC32 0x576f94b9 Anonymous_GTID last_committed=2 sequence_number=3 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
先刪除錯誤的數(shù)據(jù)表，進行恢復(fù)
mysql> drop table mytable; Query OK, 0 rows affected (0.00 sec) mysql> source /opt/mytable.sql; mysql> select * from mytable; +----+----------+-----+ | id | name | age | +----+----------+-----+ | 1 | zhangsan | 22 | | 2 | lisi | 26 | | 3 | wangwu | 30 | +----+----------+-----+ 3 rows in set (0.00 sec)
使用增量備份的斷點恢復(fù)
[root@localhost data]# mysqlbinlog --no-defaults --stop-position='584' /usr/local/mysql/data/mysql-bin.000001 | mysql -uroot -p123456 mysql: [Warning] Using a password on the command line interface can be insecure. mysql> select * from mytable; +----+----------+-----+ | id | name | age | +----+----------+-----+ | 1 | zhangsan | 22 | | 2 | lisi | 26 | | 3 | wangwu | 30 | | 4 | zhaoliu | 20 | +----+----------+-----+ 4 rows in set (0.00 sec) [root@localhost data]# mysqlbinlog --no-defaults --start-position='698' /usr/local/mysql/data/mysql-bin.000001 | mysql -uroot -p123456 mysql: [Warning] Using a password on the command line interface can be insecure. mysql> select * from mytable; +----+----------+-----+ | id | name | age | +----+----------+-----+ | 1 | zhangsan | 22 | | 2 | lisi | 26 | | 3 | wangwu | 30 | | 4 | zhaoliu | 20 | | 5 | qiqi | 21 | +----+----------+-----+ 5 rows in set (0.00 sec)
按照增量備份中的時間點恢復(fù)
mysql> drop table mytable; Query OK, 0 rows affected (0.00 sec) mysql> source /opt/mytable.sql; mysql> select * from mytable; +----+----------+-----+ | id | name | age | +----+----------+-----+ | 1 | zhangsan | 22 | | 2 | lisi | 26 | | 3 | wangwu | 30 | +----+----------+-----+ 3 rows in set (0.00 sec)
[root@localhost data]# mysqlbinlog --no-defaults --stop-datetime='2020-8-22 15:06:12' /usr/local/mysql/data/mysql-bin.000001 | mysql -uroot -p123456 mysql: [Warning] Using a password on the command line interface can be insecure. mysql> select * from mytable; +----+----------+-----+ | id | name | age | +----+----------+-----+ | 1 | zhangsan | 22 | | 2 | lisi | 26 | | 3 | wangwu | 30 | | 4 | zhaoliu | 20 | +----+----------+-----+ 4 rows in set (0.00 sec) [root@localhost data]# mysqlbinlog --no-defaults --start-datetime='2020-8-22 15:06:30' /usr/local/mysql/data/mysql-bin.000001 | mysql -uroot -p123456 mysql: [Warning] Using a password on the command line interface can be insecure. mysql> select * from mytable; +----+----------+-----+ | id | name | age | +----+----------+-----+ | 1 | zhangsan | 22 | | 2 | lisi | 26 | | 3 | wangwu | 30 | | 4 | zhaoliu | 20 | | 5 | qiqi | 21 | +----+----------+-----+ 5 rows in set (0.00 sec)
以上就是MySQL中數(shù)據(jù)庫備份恢復(fù)的常用方法詳解的詳細內(nèi)容，更多關(guān)于MySQL數(shù)據(jù)庫備份恢復(fù)的資料請關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
安裝MySQL后時間太長了會忘記密碼，在這里總結(jié)一下忘記密碼時如何重置本地MySQL Root密碼。感興趣的朋友跟隨腳本之家一起學(xué)習(xí)吧2018-03-03
Mysql?8?默認啟用SSL安全連接,也可以強制必須啟用,這篇文章主要來和大家介紹一下如何在Mysql8中配置SSL安全連接,有需要的小伙伴可以了解下2025-06-06
ADODB 入門...2006-12-12
Mysql根據(jù)某層部門ID查詢所有下級多層子部門的示例
這篇文章主要介紹了Mysql根據(jù)某層部門ID查詢所有下級多層子部門的示例，文中通過示例代碼介紹的非常詳細，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-12-12
眾所周知，MySql 提供了很多存儲引擎，這里來比較一下常見引擎的優(yōu)劣。幫助大家選擇合適的存儲引擎2021-06-06
MySQL中的條件判斷和空值處理是開發(fā)中的常見需求,本文詳細解析了IF和IFNULL兩個核心函數(shù),文章通過多個實戰(zhàn)示例展示了它們的用法,感興趣的朋友跟隨小編一起看看吧2026-03-03
MySQL系列教程小白數(shù)據(jù)庫基礎(chǔ)
這篇文章主要為大家介紹了MySQL系列中的數(shù)據(jù)庫基礎(chǔ)，非常適合數(shù)據(jù)庫小白的入門基礎(chǔ)篇，詳細的講解了數(shù)據(jù)庫的基本概念以及基礎(chǔ)命令及操作示例，有需要的朋友可以借鑒參考下2021-10-10
MySQL 外鍵約束和表關(guān)系相關(guān)總結(jié)
一個項目中如果將所有的數(shù)據(jù)都存放在一張表中是不合理的，比如一個員工信息，公司只有2個部門，但是員工有1億人，就意味著員工信息這張表中的部門字段的值需要重復(fù)存儲，極大的浪費資源，因此可以定義一個部門表和員工信息表進行關(guān)聯(lián)，而關(guān)聯(lián)的方式就是外鍵。2021-06-06
MySQL和Oracle Database都是一個關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，本文主要介紹了MySQL和Oracle Database的區(qū)別及優(yōu)缺點是什么，感興趣的小伙伴歡迎閱讀2023-04-04
linux系統(tǒng)中使用openssl實現(xiàn)mysql主從復(fù)制
在MySQL的主從復(fù)制中，其傳輸過程是明文傳輸，并不能保證數(shù)據(jù)的安全性，今天我們就來討論下linux系統(tǒng)中使用openssl實現(xiàn)mysql主從復(fù)制，有需要的小伙伴可以參考下2016-11-11