MySQL 的邏輯備份指的是使用 SQL 語句備份數(shù)據(jù)庫的結(jié)構(gòu)和數(shù)據(jù)，而不是直接備份數(shù)據(jù)庫文件。通常使用 mysqldump 工具進(jìn)行邏輯備份。

一.mysqldump

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

基本命令：

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

參數(shù)選項：

• -u, --user=[name]：指定 MySQL 用戶名。
• -p, --password[=password]：指定 MySQL 密碼。如果不提供密碼，mysqldump 會提示輸入。
• -h, --host=[hostname]：指定 MySQL 服務(wù)器主機(jī)名。
• -P, --port=[port_num]：指定 MySQL 服務(wù)器端口號。
• --protocol=[{TCP|SOCKET|PIPE|MEMORY}]：指定連接協(xié)議。
• -r, --result-file=[file]：將輸出直接寫入文件，而不是通過標(biāo)準(zhǔn)輸出。
• -t, --no-create-info：不寫入創(chuàng)建表的語句，只導(dǎo)出數(shù)據(jù)。
• -d, --no-data：不寫入數(shù)據(jù)，只導(dǎo)出數(shù)據(jù)庫結(jié)構(gòu)。
• --add-drop-table：在每個創(chuàng)建表語句前增加 DROP TABLE IF EXISTS 語句。
• --add-locks：在插入數(shù)據(jù)前后使用 LOCK TABLES 和 UNLOCK TABLES 語句。
• --disable-keys：在插入數(shù)據(jù)前使用 ALTER TABLE ... DISABLE KEYS，插入數(shù)據(jù)后使用 ALTER TABLE ... ENABLE KEYS。
• -B, --databases：指定要備份的數(shù)據(jù)庫。如果使用該選項，所有跟在其后的數(shù)據(jù)庫名都將被備份。
• -A, --all-databases：備份所有數(shù)據(jù)庫。
• --tables：指定要備份的表，必須跟在數(shù)據(jù)庫名之后。
• -n, --no-create-db：不寫入創(chuàng)建數(shù)據(jù)庫的語句。
• -w, --where='condition'：僅導(dǎo)出符合指定條件的行。
• --single-transaction：在一個事務(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)出存儲過程和函數(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ù)制。

示例

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

mysqldump -u root -p mydatabase > mydatabase_backup.sql

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

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

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

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

僅備份數(shù)據(jù)庫結(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

添加選項以有效處理鎖表問題

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

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

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

語法

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

示例

mysql -u root -p mydatabase < mydatabase_backup.sql

恢復(fù)庫中的表 

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

使用source恢復(fù) 

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

source 文件路徑;

例： 

source /path/to/employees_backup.sql;

注意事項

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

二. mysqlpump

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

mysqlpump 特點

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

基本命令：

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

常用選項：

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

示例

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

mysqlpump -u root -p mydatabase > mydatabase_backup.sql

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

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

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

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ù)，加快備份速度。 

過濾表

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 備份用戶賬戶，使用 --routines 備份存儲函數(shù)和存儲過程。

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

解壓縮備份文件

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

恢復(fù)備份

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

示例

mysql -u root -p mydatabase < mydatabase_backup.sql

注意事項

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

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

優(yōu)點：

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

缺點：

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

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

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

mysqldump -n

介紹

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

舉例

場景: 有一個名為 employees 的數(shù)據(jù)庫，其中包含 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 客戶端，執(zhí)行：

source /path/to/staff_data_only.sql;

SELECT INTO OUTFILE

介紹

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

舉例

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

2.步驟:

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

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

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

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

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

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

-- 每個字段的值都用雙引號包圍
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 語句導(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ù)庫數(shù)據(jù)。生成 SQL 文件，便于在需要時恢復(fù)數(shù)據(jù)。
• SELECT INTO OUTFILE 主要用于數(shù)據(jù)導(dǎo)出用于分析或傳輸。生成的文件格式如CSV，可用Excel等工具打開。

恢復(fù)指定表:

• mysqldump -n 恢復(fù)數(shù)據(jù)更簡單，直接源文件加載到MySQL中即可。
• SELECT INTO OUTFILE 導(dǎo)出的文件格式靈活，但需要額外步驟導(dǎo)入數(shù)據(jù)（如使用 LOAD DATA INFILE）。

便捷性和靈活性:

• mysqldump -n 適用于跨平臺環(huán)境，恢復(fù)數(shù)據(jù)庫較為便捷。
• SELECT INTO OUTFILE 適用于導(dǎo)出數(shù)據(jù)進(jìn)行外部分析，格式靈活但操作稍復(fù)雜。

到此這篇關(guān)于MySQL邏輯備份的實現(xiàn)步驟的文章就介紹到這了,更多相關(guān)MySQL邏輯備份內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！ 

最新評論