在備份mysql資料庫時,對資料庫進行備份

本文目錄一覽：

1、如何使用mysqldump命令備份mysql資料庫
2、MySQL的備份與還原，非常規備份，全量備份，增量備份
3、如何備份整個mysql資料庫
4、怎麼備份和還原mysql資料庫
5、如何備份MYSQL資料庫？

如何使用mysqldump命令備份mysql資料庫

通用規律只有使用 –all-databases (-A) 會 ERROR 1356，那就看看他到底備份了什麼東西。於是喊上同事一起 less 看了下，上下掃了兩眼。突然發現：1. 備份 SQL 文件里 DROP 掉了 mysql.proc；2. 後CREATE了一個新的 mysql.proc；3. LOCK TABLES 和 UNLOCK TABLES 中間居然沒有備份 CREATE ROUTINE 任何數據？這不就是相當於每次導入全備都給我一個沒有任何 sys schema routines 的全新 mysql.proc 表？那這不就異常的尷尬？

—- Table structure for table `proc`–

—- Dumping data for table `proc`-

真相大白在官方文檔【sys-schema-usage】官方文檔明確的告訴我們不會備份 sys 庫。但在使用 mysqldump 在執行 –all-databases 會清空 mysql.proc 導致 sys 無法正常使用；這是一個 BUG，並且只存在於 MySQL 5.7.x ！

1、mysql_upgrade install or upgrade sys schema

這個方案適用於 sys 庫已經因為 mysqldump 導入而損壞的情況下使用。

注意：mysql_upgrade 在修理 sys 庫的同時，還修理 mysql 庫和用戶庫表（期間加鎖且速度一般），有極小可能會誤傷；使用 mysql_upgrade 的時候要加上 –upgrade-system-tables，不然會掃描用戶庫表。

2、全備時同時備份 sys 庫

這個方案適用於需要還原的資料庫，sys 庫也不太正常的情況下使用；在全備後額外再備份一份 sys 庫用於修復。

注意：不適用於做主從時使用它。

3、使用 databases 全備

這個方案適用於所有場景的全備需求，100% 安全。

4、使用 mysql-sys 開源代碼

如果你的資料庫 sys 全部中招了，又是生產庫。那你只能用這個方法；

mysql-sys：

中記錄了 sys 庫的創建語句將文件下載到本地，然後根據資料庫版本，執行以下命令即可。

MySQL的備份與還原，非常規備份，全量備份，增量備份

1：官方百萬級別的測試資料庫：

官方測試資料庫github網址：

下載到目錄，解壓即可，運行命令：

2：自己創建簡單測試資料庫：

快速隨機生成測試語言的網站：

選擇sql和想生成的欄位，點擊生成Generate!生成即可。

在MySQL輸入生成的語句即可。

3：測試備份還原時用到的命令

刪庫跑路測試（先備份好）

還原後查詢庫的表數據是否完整。

採用複製整個數據存放目錄

1：查看資料庫數據存放位置

有兩種方法：

1)：在資料庫中用命令 show variables like ‘datadir’; 查看

2)：在配置文件中查看，配置了 datadir 目錄的可查看。沒有配置的默認為 /var/lib/mysql/ 位置

Linux中查看配置文件

3：還原時直接複製文件夾到資料庫目錄即可

mysqldump又可叫做全量備份。

參數 –databases 同 -B ，單獨一個庫，也可省略。

1、備份命令mysqldump格式

格式：mysqldump -h主機名 -P埠 -u用戶名 -p密碼 database 資料庫名文件名.sql

備份testDatabase資料庫

2、備份MySQL資料庫為帶刪除表的格式

備份MySQL資料庫為帶刪除表的格式，能夠讓該備份覆蓋已有資料庫而不需要手動刪除原有資料庫。

3、直接將MySQL資料庫壓縮備份

備份並壓縮

4、備份MySQL資料庫某個(些)表

備份testDatabase中的myTable表，不需要用參數 –databases 或者 -B

5、同時備份多個MySQL資料庫

同時備份testDatabase和 employees兩個庫

6、備份伺服器上所有資料庫

參數 –all-databases 同 -A

7、還原MySQL資料庫的命令

1) 不指定數據名還原，默認生成原資料庫名稱，還原所有資料庫。

2) 指定數據名還原，還原指定單個資料庫，需在資料庫種預先創建一個testDatabase名稱。

3) 還原壓縮的MySQL資料庫

4) 進入資料庫用source導入

增量備份是針對於資料庫的bin-log日誌進行備份的，增量備份是在全量的基礎上進行操作的。增量備份主要是靠mysql記錄的bin-log日誌。

1：查看是否開啟bin-log日誌

進入mysql輸入命令可查看。

顯示如下為開啟狀態，日誌文件在/var/lib/mysql/以binlog.00001的格式保存。

如未開啟，需要在配置文件種配置

2：查看目前使用的bin-log日誌文件

進入mysql查看命令。

顯示如下，目前使用的是binlog.000022文件，所有操作都記錄在此文件。

查看當前testDatabase的表myTable數據如下，

3：刷新日誌，使用新的日誌文件（備份）

在命令端執行命令

日誌文件從 binlog.000022 變為 binlog.000023

這時相當與已經備份成功，備份文件即為上次的binlog.000022日誌文件。

4：刪除數量，從日誌還原數據

1) 刪除ABC行

查詢以及沒有ABC行列。

2) 恢複數據ABC行

退出mysql，在命令端用mysqlbinlog命令恢復到binlog.000022日誌狀態。

進入資料庫再次查看數據，ABC已經恢復。

增量備份完成。

如何備份整個mysql資料庫

1、首先打開mysql資料庫軟體進入軟體主界面。

2、然後再左側樹里打開自己的的資料庫。

3、然後需要點擊需要備份的資料庫名。

4、如圖所示為打開資料庫後界面。

5、然後需要點擊轉儲sql文件選項。

6、然後需要打開選擇存儲文件路徑並選擇保存。

7、點擊保存即可在路徑備份好格式為sql的資料庫文件。

怎麼備份和還原mysql資料庫

備份資料庫

使用mysqldump命令備份資料庫

還原資料庫

1、使用mysql命令還原資料庫

將game_backup.sql 還原至 game 資料庫：

2、使用source命令還原資料庫

如果資料庫過大，建議可以使用source命令

如何備份MYSQL資料庫？

定期的備份可使我們資料庫崩潰造成的損失大大降低。在MySQL中進行數據備份的方法有兩種，一種是使用mysqldump程序，一種是使用mysqlhotcopy、cp、tar或cpio等打包程序直接拷貝資料庫文件。mysqldump程序備份資料庫較慢，但它生成的文本文件便於移植。使用mysqlhotcopy等程序備份速度快，因為它直接對系統文件進行操作，需人為協調資料庫數據的備份前後一致性。

使用mysqldump備份資料庫其實就是把資料庫轉儲成一系列CREATE TABLE和INSERT語句，通過這些語句我們就可重新生成資料庫。使用mysqldump的方法如下：

% mysqldump –opt testdb | gzip /data/backup/testdb.bak

#–opt選項會對轉儲過程進行優化，生成的備份文件會小一點，後的管道操作會進行數據壓縮

% mysqldump –opt testdb mytable1,mytable2 | gzip /data/backup/testdb_mytable.bak

#可在資料庫後接數據表名，只導出指定的數據表，多個數據表可用逗號分隔

–opt選項還可激活–add-drop-table選項，它將會在備份文件的每條CREATE TABLE前加上一條DROP TABLE IF EXISTS語句。這可方便進行數據表的更新，而不會發生「數據表已存在」的錯誤。

用mysqldump命令還可直接把資料庫轉移到另外一台伺服器上，不用生成備份文件。重複執行可定期更新遠程資料庫。

% mysqladmin -h remote_host create testdb

% mysqldump –opt testdb | mysql -h remote_host testdb

另外還可通過ssh遠程調用伺服器上的程序，如：

% ssh remote_host mysqladmin create testdb

% mysqldump –opt testdb | ssh remote_host mysql testdb

通過直接拷貝系統文件的方式備份資料庫，在備份時，要確保沒有人對資料庫進行修改操作。要做到這點，最好關閉伺服器。如果不能關閉的，要以只讀方試鎖定有關數據表。下面是一些示例：

% cp -r db /backup/db #備份db資料庫到/backup/db目錄

% cp table_name.* /backup/db #只備份table_name數據表

% scp -r db remotehot:/usr/local/mysql/data #用scp把資料庫直接拷貝到遠程伺服器

在把資料庫直接拷貝到遠程主機時，應注意兩台機器必須有同樣的硬體結構，或者將拷貝的數據表全部是可移植數據表類型。

使用mysqlhotcopy工具，它是一個Perl DBI腳本，可在不關閉伺服器的情況下備份資料庫，它主要的優點是：

它直接拷貝文件，所以它比mysqldump快。

可自動完成數據鎖定工作，備份時不用關閉伺服器。

能刷新日誌，使備份文件和日誌文件的檢查點能保持同步。

下面是該工具的使用示例：

% mysqlhotcopy db /bakcup/ #把db資料庫備份到backup/db目錄里，會自動創建一個db目錄

使用BACKUP TABLE語句進行備份，該語句最早出現在MySQL 3.23.25版本中，僅適用於MyISAM數據表。用法如下：

mysql BACKUP TABLE mytable TO ‘/backup/db’; #把mytable數據表備份到/backup/db目錄下

為了執行該語句，你必須擁有那些表的FILE許可權和SELECT許可權，備份目錄還必須是伺服器可寫的。該語句執行時，會先把內存中的數據寫入磁碟，再把各個數據表的.frm(表結構定義文件)、.MYD(數據)文件從數據目錄拷貝到備份目錄。它不拷貝.MYI(索引)文件，因為它能用另外兩個文件重建。BACKUP TABLE語句備份時，依次鎖定數據表，當同時備份多個數據表時，數據表可能會被修改，所以備份0完成時，備份文件中的數據和現時數據表中的數據可能會有差異，為了消除該差異，我們可用只讀方式鎖定數據表，在備份完成後再解鎖。如：

mysql LOCK TABLES tb1 READ,tb2 READ;

mysql BACKUP TABLE tb1,tb2 TO ‘backup/db’;

mysql UNLOCK TABLE

原創文章，作者：小藍，如若轉載，請註明出處：https://www.506064.com/zh-tw/n/194212.html