腳本之家服務器常用軟件

快捷導航

MySQL邏輯備份的實現步驟

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

本文主要介紹了MySQL邏輯備份的實現步驟,包括使用mysqldump和mysqlpump工具進行備份和恢復,文中通過示例代碼介紹的非常詳細,需要的朋友們下面隨著小編來一起學習學習吧

一.mysqldump

mysqldump是最常用的邏輯備份工具，能夠生成SQL腳本文件，這些文件可以用來重建數據庫結構和數據。

基本命令：

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

參數選項：

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

示例

備份整個數據庫

mysqldump -u root -p mydatabase > mydatabase_backup.sql

備份多個數據庫

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

備份所有數據庫

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

僅備份數據庫結構

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

恢復數據

恢復數據庫

語法

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

示例

mysql -u root -p mydatabase < mydatabase_backup.sql

恢復庫中的表

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

使用source恢復

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

source 文件路徑;

例：

source /path/to/employees_backup.sql;

注意事項

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

二. mysqlpump

mysqlpump是MySQL 5.7引入的備份工具，支持多線程，能夠更快地進行備份。它是 mysqldump 的增強版本。

mysqlpump 特點

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

基本命令：

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

常用選項：

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

示例

備份單個數據庫

mysqlpump -u root -p mydatabase > mydatabase_backup.sql

備份多個數據庫

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

備份所有數據庫

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 參數可以設定并發(fā)線程數，加快備份速度。

過濾表

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

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

僅備份表結構

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

壓縮備份文件

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

備份數據目錄

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

使用 --users 備份用戶賬戶，使用 --routines 備份存儲函數和存儲過程。

恢復數據庫

解壓縮備份文件

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

恢復備份

mysql -u [用戶名] -p [數據庫名] < [備份文件.sql]

示例

mysql -u root -p mydatabase < mydatabase_backup.sql

注意事項

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

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

優(yōu)點：

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

缺點：

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

只備份表數據擴展

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

mysqldump -n

介紹

mysqldump 是一個用于備份 MySQL 數據庫的工具。-n 參數讓你只備份表中數據，不包括表結構。備份得到的文件是純 SQL 語句，可以用來恢復數據。

舉例

場景: 有一個名為 employees 的數據庫，其中包含 staff 表。備份 staff 表中的數據，但不包括表結構。

步驟:

進入命令行。

執(zhí)行以下命令：

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

3.結果: 生成的 staff_data_only.sql 文件內容：

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);

恢復:

在新環(huán)境中，確保已存在相同結構的 staff 表。
進入 MySQL 客戶端，執(zhí)行：

source /path/to/staff_data_only.sql;

SELECT INTO OUTFILE

介紹

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

舉例

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

2.步驟:

進入 MySQL 客戶端。

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

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

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

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

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

-- 每個字段的值都用雙引號包圍
ENCLOSED BY '"'

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

3.結果: 生成的 staff_data.csv 文件內容：

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

恢復:

在新環(huán)境中，確保已存在相同結構的 staff 表。
復制 staff_data.csv 文件到服務器。
使用以下 SQL 語句導入數據：

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