快捷導(dǎo)航

MySQL邏輯備份的實(shí)現(xiàn)步驟

更新時(shí)間：2025年03月05日 11:36:22 作者：ZZDICT

本文主要介紹了MySQL邏輯備份的實(shí)現(xiàn)步驟,包括使用mysqldump和mysqlpump工具進(jìn)行備份和恢復(fù),文中通過(guò)示例代碼介紹的非常詳細(xì),需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧

一.mysqldump

mysqldump是最常用的邏輯備份工具，能夠生成SQL腳本文件，這些文件可以用來(lái)重建數(shù)據(jù)庫(kù)結(jié)構(gòu)和數(shù)據(jù)。

基本命令：

mysqldump -u [用戶(hù)名] -p[密碼] [數(shù)據(jù)庫(kù)名] > [備份文件.sql]

參數(shù)選項(xiàng)：

-u, --user=[name]：指定 MySQL 用戶(hù)名。
-p, --password[=password]：指定 MySQL 密碼。如果不提供密碼，mysqldump 會(huì)提示輸入。
-h, --host=[hostname]：指定 MySQL 服務(wù)器主機(jī)名。
-P, --port=[port_num]：指定 MySQL 服務(wù)器端口號(hào)。
--protocol=[{TCP|SOCKET|PIPE|MEMORY}]：指定連接協(xié)議。
-r, --result-file=[file]：將輸出直接寫(xiě)入文件，而不是通過(guò)標(biāo)準(zhǔn)輸出。
-t, --no-create-info：不寫(xiě)入創(chuàng)建表的語(yǔ)句，只導(dǎo)出數(shù)據(jù)。
-d, --no-data：不寫(xiě)入數(shù)據(jù)，只導(dǎo)出數(shù)據(jù)庫(kù)結(jié)構(gòu)。
--add-drop-table：在每個(gè)創(chuàng)建表語(yǔ)句前增加 DROP TABLE IF EXISTS 語(yǔ)句。
--add-locks：在插入數(shù)據(jù)前后使用 LOCK TABLES 和 UNLOCK TABLES 語(yǔ)句。
--disable-keys：在插入數(shù)據(jù)前使用 ALTER TABLE ... DISABLE KEYS，插入數(shù)據(jù)后使用 ALTER TABLE ... ENABLE KEYS。
-B, --databases：指定要備份的數(shù)據(jù)庫(kù)。如果使用該選項(xiàng)，所有跟在其后的數(shù)據(jù)庫(kù)名都將被備份。
-A, --all-databases：備份所有數(shù)據(jù)庫(kù)。
--tables：指定要備份的表，必須跟在數(shù)據(jù)庫(kù)名之后。
-n, --no-create-db：不寫(xiě)入創(chuàng)建數(shù)據(jù)庫(kù)的語(yǔ)句。
-w, --where='condition'：僅導(dǎo)出符合指定條件的行。
--single-transaction：在一個(gè)事務(wù)中導(dǎo)出所有數(shù)據(jù)，適用于支持事務(wù)的表（如 InnoDB）。
--lock-tables：在導(dǎo)出前鎖定所有表，適用于不支持事務(wù)的表（如 MyISAM）。
--master-data[=#]：在導(dǎo)出中加入主服務(wù)器的二進(jìn)制日志位置和文件名，用于設(shè)置復(fù)制。
--flush-logs：在導(dǎo)出前刷新 MySQL 服務(wù)器的日志。
--routines：導(dǎo)出存儲(chǔ)過(guò)程和函數(shù)。
--triggers：導(dǎo)出觸發(fā)器。
--events：導(dǎo)出事件。
--hex-blob：以十六進(jìn)制格式導(dǎo)出二進(jìn)制列。
--set-gtid-purged=[OFF|ON|AUTO]：控制是否在備份中加入 GTID 信息，適用于 GTID 復(fù)制。

示例

備份整個(gè)數(shù)據(jù)庫(kù)

mysqldump -u root -p mydatabase > mydatabase_backup.sql

備份多個(gè)數(shù)據(jù)庫(kù)

mysqldump -u root -p --databases db1 db2 > multi_database_backup.sql

備份所有數(shù)據(jù)庫(kù)

mysqldump -u root -p --all-databases > all_databases_backup.sql

僅備份數(shù)據(jù)庫(kù)結(jié)構(gòu)

mysqldump -u root -p --no-data mydatabase > mydatabase_structure_backup.sql

僅備份特定表

mysqldump -u root -p mydatabase table1 table2 > mydatabase_tables_backup.sql

添加選項(xiàng)以有效處理鎖表問(wèn)題

mysqldump -u root -p --single-transaction --quick --lock-tables=false mydatabase > mydatabase_backup.sql

恢復(fù)數(shù)據(jù)

恢復(fù)數(shù)據(jù)庫(kù)

語(yǔ)法

mysql -u [用戶(hù)名] -p[密碼] [數(shù)據(jù)庫(kù)名] < [備份文件.sql]

示例

mysql -u root -p mydatabase < mydatabase_backup.sql

恢復(fù)庫(kù)中的表

mysqldump -u root -p[密碼] [庫(kù)名] [表名] > staff_backup.sql

使用source恢復(fù)

首先登錄到mysql中，在mysql中使用下述語(yǔ)法恢復(fù)

source 文件路徑;

例：

source /path/to/employees_backup.sql;

注意事項(xiàng)

權(quán)限管理：確保執(zhí)行 mysqldump 和恢復(fù)命令的用戶(hù)具有相應(yīng)的數(shù)據(jù)庫(kù)讀寫(xiě)權(quán)限。
定期備份：邏輯備份通常對(duì)數(shù)據(jù)一致性和完整性要求較高，建議定期進(jìn)行備份并驗(yàn)證備份文件的有效性。
存儲(chǔ)位置：將備份文件存儲(chǔ)在安全的位置，避免丟失或被不當(dāng)修改。

二. mysqlpump

mysqlpump是MySQL 5.7引入的備份工具，支持多線程，能夠更快地進(jìn)行備份。它是 mysqldump 的增強(qiáng)版本。

mysqlpump 特點(diǎn)

并行處理：mysqlpump 支持并發(fā)地備份多個(gè)表和多數(shù)據(jù)庫(kù)，極大地提高了備份速度。
更豐富的功能選項(xiàng)：支持過(guò)濾特定數(shù)據(jù)庫(kù)、表和數(shù)據(jù)以及導(dǎo)出的同時(shí)進(jìn)行壓縮等。
增強(qiáng)的靈活性：提供更多參數(shù)以定制化備份操作。

基本命令：

mysqlpump -u [用戶(hù)名] -p[密碼] [數(shù)據(jù)庫(kù)名] > [備份文件.sql]

常用選項(xiàng)：

--exclude-databases：排除特定數(shù)據(jù)庫(kù)
--include-databases：包含特定數(shù)據(jù)庫(kù)
--default-parallelism：設(shè)置并行度
--skip-definer：忽略DEFINER子句
--set-gtid-purged：用于GTID的備份

示例

備份單個(gè)數(shù)據(jù)庫(kù)

mysqlpump -u root -p mydatabase > mydatabase_backup.sql

備份多個(gè)數(shù)據(jù)庫(kù)

mysqlpump -u root -p --databases db1 db2 > multi_database_backup.sql

備份所有數(shù)據(jù)庫(kù)

mysqlpump -u root -p --all-databases > all_databases_backup.sql

并發(fā)處理

mysqlpump -u root -p --default-parallelism=4 --databases db1 db2 > multi_database_backup.sql

使用 --default-parallelism 參數(shù)可以設(shè)定并發(fā)線程數(shù)，加快備份速度。

過(guò)濾表

mysqlpump -u root -p mydatabase --exclude-tables=table1,table2 > mydatabase_backup.sql

使用 --exclude-tables 可以排除特定的表

僅備份表結(jié)構(gòu)

mysqlpump -u root -p mydatabase --skip-dump-data > mydatabase_structure_backup.sql

壓縮備份文件

mysqlpump -u root -p mydatabase | gzip > mydatabase_backup.sql.gz

備份數(shù)據(jù)目錄

mysqlpump --users --routines --databases db1 db2 > backup_with_users_routines.sql

使用 --users 備份用戶(hù)賬戶(hù)，使用 --routines 備份存儲(chǔ)函數(shù)和存儲(chǔ)過(guò)程。

恢復(fù)數(shù)據(jù)庫(kù)

解壓縮備份文件

gunzip < mydatabase_backup.sql.gz | mysql -u root -p mydatabase

恢復(fù)備份

mysql -u [用戶(hù)名] -p [數(shù)據(jù)庫(kù)名] < [備份文件.sql]

示例

mysql -u root -p mydatabase < mydatabase_backup.sql

注意事項(xiàng)

權(quán)限管理：確保執(zhí)行 mysqlpump 和恢復(fù)命令的用戶(hù)具有必要的數(shù)據(jù)庫(kù)讀寫(xiě)權(quán)限。
并發(fā)性：合理設(shè)定并發(fā)線程數(shù)，避免服務(wù)器負(fù)載過(guò)高。
定期備份：建議定期備份并驗(yàn)證備份文件的完整性。
存儲(chǔ)位置：將備份文件存儲(chǔ)在安全和可靠的位置，避免丟失或不當(dāng)修改。

邏輯備份的優(yōu)缺點(diǎn)

優(yōu)點(diǎn)：

可讀性強(qiáng)：備份文件是文本格式的SQL腳本，易于查看和編輯。
跨平臺(tái)：備份文件可以在不同操作系統(tǒng)和不同版本的MySQL之間移植。
備份靈活：可以選擇備份整個(gè)數(shù)據(jù)庫(kù)、特定的表或結(jié)構(gòu)。

缺點(diǎn)：

性能開(kāi)銷(xiāo)大：備份和恢復(fù)過(guò)程中會(huì)產(chǎn)生較大的CPU和I/O開(kāi)銷(xiāo)，特別是對(duì)于大數(shù)據(jù)量的數(shù)據(jù)庫(kù)。
恢復(fù)速度慢：相對(duì)于物理備份，邏輯備份的恢復(fù)速度較慢。
一致性問(wèn)題：如果沒(méi)有正確使用事務(wù)一致性選項(xiàng)，可能會(huì)導(dǎo)致數(shù)據(jù)不一致。

只備份表數(shù)據(jù)擴(kuò)展

/var/lib/mysql-files 是 MySQL 默認(rèn)用于存儲(chǔ)導(dǎo)出的文件的目錄。這個(gè)目錄通常用于 SELECT INTO OUTFILE 等操作所生成的文件。

mysqldump -n

介紹

mysqldump 是一個(gè)用于備份 MySQL 數(shù)據(jù)庫(kù)的工具。-n 參數(shù)讓你只備份表中數(shù)據(jù)，不包括表結(jié)構(gòu)。備份得到的文件是純 SQL 語(yǔ)句，可以用來(lái)恢復(fù)數(shù)據(jù)。

舉例

場(chǎng)景: 有一個(gè)名為 employees 的數(shù)據(jù)庫(kù)，其中包含 staff 表。備份 staff 表中的數(shù)據(jù)，但不包括表結(jié)構(gòu)。

步驟:

進(jìn)入命令行。

執(zhí)行以下命令：

mysqldump -u root -p[密碼] --no-create-info employees staff > staff_data_only.sql

3.結(jié)果: 生成的 staff_data_only.sql 文件內(nèi)容：

INSERT INTO `staff` (`id`, `name`, `position`, `salary`) VALUES (1, 'Alice', 'Manager', 50000);
INSERT INTO `staff` (`id`, `name`, `position`, `salary`) VALUES (2, 'Bob', 'Engineer', 40000);
INSERT INTO `staff` (`id`, `name`, `position`, `salary`) VALUES (3, 'Charlie', 'Technician', 30000);

恢復(fù):

在新環(huán)境中，確保已存在相同結(jié)構(gòu)的 staff 表。
進(jìn)入 MySQL 客戶(hù)端，執(zhí)行：

source /path/to/staff_data_only.sql;

SELECT INTO OUTFILE

介紹

SELECT INTO OUTFILE 是一條SQL語(yǔ)句，用于將查詢(xún)結(jié)果導(dǎo)出到文件中。文件格式可以根據(jù)需要來(lái)定義（如CSV格式，也可以是xlsx結(jié)尾的格式），通常用于數(shù)據(jù)分析和傳輸。

舉例

1.場(chǎng)景: 有一個(gè)名為 employees 的數(shù)據(jù)庫(kù)，其中包含 staff 表。導(dǎo)出 staff 表中的數(shù)據(jù)作為CSV文件來(lái)分析。

2.步驟:

進(jìn)入 MySQL 客戶(hù)端。

執(zhí)行以下 SQL 語(yǔ)句：

SELECT * FROM staff INTO OUTFILE '/var/lib/mysql-files/staff_data.csv'
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

命令解讀（也可以使用默認(rèn)，后面就不用那么多指定要求語(yǔ)句了）：
-- 從 staff 表中選擇所有列和行
SELECT * FROM staff 

-- 將查詢(xún)結(jié)果寫(xiě)入指定路徑的文件中
INTO OUTFILE '/var/lib/mysql-files/staff_data.csv'

-- 每個(gè)字段（列）的數(shù)據(jù)使用逗號(hào)分隔
FIELDS TERMINATED BY ',' 

-- 每個(gè)字段的值都用雙引號(hào)包圍
ENCLOSED BY '"'

-- 每行記錄之間以換行符分隔
LINES TERMINATED BY '\n';

3.結(jié)果: 生成的 staff_data.csv 文件內(nèi)容：

    "1","Alice","Manager","50000"
    "2","Bob","Engineer","40000"
    "3","Charlie","Technician","30000"

恢復(fù):

在新環(huán)境中，確保已存在相同結(jié)構(gòu)的 staff 表。
復(fù)制 staff_data.csv 文件到服務(wù)器。
使用以下 SQL 語(yǔ)句導(dǎo)入數(shù)據(jù)：

      LOAD DATA INFILE '/var/lib/mysql-files/staff_data.csv'
      INTO TABLE staff
      FIELDS TERMINATED BY ',' 
      ENCLOSED BY '"'
      LINES TERMINATED BY '\n';

總結(jié)

用途不同:

mysqldump -n 主要用于備份和恢復(fù) MySQL 數(shù)據(jù)庫(kù)數(shù)據(jù)。生成 SQL 文件，便于在需要時(shí)恢復(fù)數(shù)據(jù)。
SELECT INTO OUTFILE 主要用于數(shù)據(jù)導(dǎo)出用于分析或傳輸。生成的文件格式如CSV，可用Excel等工具打開(kāi)。

恢復(fù)指定表: