簡(jiǎn)介
點(diǎn)進(jìn)來(lái)本文的讀者，說(shuō)明你對(duì) MySQL 數(shù)據(jù)庫(kù)有一定的基礎(chǔ)蓉媳，起碼肯定會(huì) CRUD譬挚，本文主要有以下 6 個(gè)主要內(nèi)容：
- 使用 MySQL 自帶的 mysqldump 工具，做單表酪呻、多表减宣、單庫(kù)、多庫(kù)号杠、全庫(kù)的邏輯備份蚪腋，以及數(shù)據(jù)恢復(fù)。
- MySQL 在線(xiàn)熱備工具 percona-xtrabackup 的安裝介紹姨蟋。
- 使用 percona-xtrabackup 對(duì) MySQL 數(shù)據(jù)庫(kù)做在線(xiàn)熱備屉凯，以及全庫(kù)恢復(fù)。
- 介紹 mysqldump眼溶、innobackupex 工具備份原理悠砚。
- binlog2sql 閃回工具幫你無(wú)損恢復(fù)業(yè)務(wù)記錄。
- 當(dāng)你誤刪除業(yè)務(wù)表的 .ibd 文件時(shí)堂飞，如何恢復(fù)被刪除的 .ibd 文件灌旧。
mysqldump 工具邏輯備份
在 MySQL 數(shù)據(jù)庫(kù)日常運(yùn)維過(guò)程中，肯定有需要導(dǎo)出單表數(shù)據(jù)绰筛、多表數(shù)據(jù)枢泰、單庫(kù)、多庫(kù)铝噩，甚至券庫(kù)導(dǎo)出的需求衡蚂，如果你對(duì) mysqldump 工具不是很了解，很容易就造成生產(chǎn)故障。
舉個(gè)例子毛甲，為什么有人使用 mysqldump 工具在線(xiàn)導(dǎo)出表記錄年叮，不鎖表，不影響業(yè)務(wù)玻募，而你使用 mysqldump 工具導(dǎo)出表記錄卻鎖表只损、影響到業(yè)務(wù)呢。
下面七咧，我就一步一步帶你解決上面的疑問(wèn)跃惫。
mysqldump 工具重要參數(shù)介紹
要想把 mysqldump 工具玩的溜，必須掌握 2 個(gè)重要的參數(shù)坑雅。
--single-transaction 參數(shù)作用
當(dāng)你使用 mysqldump 工具導(dǎo)數(shù)時(shí)辈挂，一定要帶上，如果不帶這個(gè)參數(shù)裹粤，會(huì)鎖表终蒂，并禁止任何數(shù)據(jù)的寫(xiě)入，從而影響業(yè)務(wù)遥诉。
如果設(shè)置此參數(shù)拇泣，當(dāng)使用 mysqldump 的時(shí)候，會(huì)自動(dòng)設(shè)置會(huì)話(huà)的隔離級(jí)別為 RR矮锈，然后再開(kāi)啟一個(gè)事務(wù)霉翔，這樣到導(dǎo)出數(shù)據(jù)整個(gè)過(guò)程，能保證數(shù)據(jù)的一致性苞笨，當(dāng)然前提是你的表使用的存儲(chǔ)引擎是 InnoDB债朵。
但是 mysqldump 開(kāi)啟的事務(wù)，會(huì)被一些 DDL 語(yǔ)句破壞掉瀑凝，例如 alter table序芦，所以呢，在執(zhí)行 mysqldump 過(guò)程中粤咪，不要使用 DDL 語(yǔ)句谚中。尤其是當(dāng)你想用 mysqldump 導(dǎo)出的數(shù)據(jù)，搭建主從復(fù)制的情況寥枝，不然會(huì)導(dǎo)致你的主從數(shù)據(jù)不一致的宪塔。
--skip-tz-utc 參數(shù)作用
這個(gè)參數(shù)也非常重要，大家應(yīng)該知道時(shí)區(qū)囊拜，mysqldump 默認(rèn)會(huì)啟用 tz-utc 選項(xiàng)的某筐，即會(huì)設(shè)置導(dǎo)出數(shù)據(jù)時(shí)區(qū)為格林威治時(shí)間，即 0 時(shí)區(qū)冠跷，如果你的數(shù)據(jù)庫(kù)在中國(guó)来吩，當(dāng)你把導(dǎo)出的數(shù)據(jù)敢辩，導(dǎo)入到目標(biāo)庫(kù)之后，就會(huì)很奇怪弟疆，為什么 timestamp 字段顯示的時(shí)間，會(huì)比源庫(kù)差 8 個(gè)小時(shí)盗冷，原因就在此怠苔。
所以當(dāng)你導(dǎo)出數(shù)據(jù)的時(shí)候，一定要加上 --skip-tz-utc 參數(shù)仪糖，告訴 MySQL 數(shù)據(jù)庫(kù)柑司，我不使用格林威治時(shí)間，使用當(dāng)前 MySQL 數(shù)據(jù)庫(kù)的時(shí)區(qū)進(jìn)行導(dǎo)出锅劝。
mysqldump 單表攒驰、多表、單庫(kù)故爵、多庫(kù)玻粪、全庫(kù)邏輯導(dǎo)出
本次模擬創(chuàng)建了 2 個(gè)數(shù)據(jù)庫(kù)，分別為 testdb 和 testdb2诬垂，在 testdb 中有 2 張表（t_test_1劲室，t_test_2），在 testdb2 中有一張表（t_test_3）结窘。
[root@localhost] 14:45:10 [testdb2]>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
| testdb2 |
+--------------------+
6 rows in set (0.00 sec)
[root@localhost] 14:45:14 [testdb2]>use testdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
[root@localhost] 14:45:22 [testdb]>show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| t_test_1 |
| t_test_2 |
+------------------+
2 rows in set (0.00 sec)
[root@localhost] 14:45:24 [testdb]>use testdb2;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
[root@localhost] 14:45:31 [testdb2]>show tables;
+-------------------+
| Tables_in_testdb2 |
+-------------------+
| t_test_3 |
+-------------------+
1 row in set (0.00 sec)
1. mysqldump 單表導(dǎo)出 testdb 庫(kù)的 t_test_1 表很洋，不導(dǎo)出建表語(yǔ)句，帶上 -t 參數(shù)即可隧枫。
mysqldump --databases testdb --skip-tz-utc -t --single-transaction -u root -proot --socket=/data/mysql/run/3307/mysql.sock --tables t_test_1 > db_script.sql
查看一下導(dǎo)出結(jié)果：
[mysql@mysql backup]$ more db_script.sql
-- MySQL dump 10.13 Distrib 5.7.26, for el7 (x86_64)
--
-- Host: localhost Database: testdb
-- ------------------------------------------------------
-- Server version 5.7.26-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;
--
-- GTID state at the beginning of the backup
--
SET @@GLOBAL.GTID_PURGED='892f6da4-f0d2-11ea-9e95-000c29cc2388:1-10';
--
-- Dumping data for table `t_test_1`
--
LOCK TABLES `t_test_1` WRITE;
/*!40000 ALTER TABLE `t_test_1` DISABLE KEYS */;
INSERT INTO `t_test_1` VALUES (1,'trest'),(2,'e99e'),(3,'test'),(4,'fresd'),(5,'fsfa');
/*!40000 ALTER TABLE `t_test_1` ENABLE KEYS */;
UNLOCK TABLES;
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2020-12-10 14:56:01
2. mysqldump 單表導(dǎo)出 testdb 庫(kù)的 t_test_1 表喉磁，帶建表語(yǔ)句：
mysqldump --databases testdb --skip-tz-utc --single-transaction -u root -proot --socket=/data/mysql/run/3307/mysql.sock --tables t_test_1 > db_script.sql
查看一下導(dǎo)出結(jié)果：
[mysql@mysql backup]$ more db_script.sql
-- MySQL dump 10.13 Distrib 5.7.26, for el7 (x86_64)
--
-- Host: localhost Database: testdb
-- ------------------------------------------------------
-- Server version 5.7.26-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;
--
-- GTID state at the beginning of the backup
--
SET @@GLOBAL.GTID_PURGED='892f6da4-f0d2-11ea-9e95-000c29cc2388:1-10';
--
-- Table structure for table `t_test_1`
--
DROP TABLE IF EXISTS `t_test_1`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t_test_1` (
`id` int(11) NOT NULL,
`name` char(10) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `t_test_1`
--
LOCK TABLES `t_test_1` WRITE;
/*!40000 ALTER TABLE `t_test_1` DISABLE KEYS */;
INSERT INTO `t_test_1` VALUES (1,'trest'),(2,'e99e'),(3,'test'),(4,'fresd'),(5,'fsfa');
/*!40000 ALTER TABLE `t_test_1` ENABLE KEYS */;
UNLOCK TABLES;
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
由于導(dǎo)出的內(nèi)容比較多，后續(xù)的案例導(dǎo)出內(nèi)容官脓，就不一一展示了协怒。
3. mysqldump 多表導(dǎo)出 testdb 庫(kù)的 t_test_1、t_test_2：
mysqldump --databases testdb --skip-tz-utc -t --single-transaction -u root -proot --socket=/data/mysql/run/3307/mysql.sock --tables t_test_1 t_test_2> db_script.sql
4. mysqldump 導(dǎo)出單庫(kù) testdb：
mysqldump --databases testdb --skip-tz-utc -t --single-transaction -u root -proot --socket=/data/mysql/run/3307/mysql.sock> db_script.sql
5. mysqldump 導(dǎo)出多庫(kù) testdb确买、testdb2：
mysqldump --databases testdb testdb2 --skip-tz-utc -t --single-transaction -u root -proot --socket=/data/mysql/run/3307/mysql.sock> db_script.sql
6. mysqldump 導(dǎo)出全庫(kù)：
mysqldump --all-databases --skip-tz-utc --single-transaction -u root -proot --socket=/data/mysql/run/3307/mysql.sock> db_script.sql
注意事項(xiàng)：如果導(dǎo)出語(yǔ)句中不帶建表語(yǔ)句斤讥，需要加參數(shù) -t，否則不帶參數(shù)湾趾。默認(rèn)情況下芭商，mysqldump 工具是不導(dǎo)出存儲(chǔ)過(guò)程和函數(shù)，事件的搀缠，如果要導(dǎo)出铛楣，需要加 -ER 參數(shù)。
percona-xtrabackup 在線(xiàn)熱備工具安裝
percona-xtrabackup 工具是 Percona 公司免費(fèi)的一款針對(duì) MySQL 數(shù)據(jù)庫(kù)在線(xiàn)熱備的工具艺普，安裝和使用都非常簡(jiǎn)單簸州，登錄官網(wǎng)鉴竭，找到對(duì)應(yīng)的軟件版本，下載即可岸浑，如下圖所示：
