在備份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-hant/n/194212.html