Artikel ini membawa anda pengetahuan yang berkaitan tentang mysql, yang terutamanya memperkenalkan isu berkaitan sandaran dan pemulihan pangkalan data, termasuk mysqldump untuk melaksanakan sandaran logik, perintah mysql untuk memulihkan data dan sandaran fizikal pemulihan dan kandungan lain, mari kita lihat di bawah ini saya harap ia akan membantu semua orang.

Pembelajaran yang disyorkan: tutorial video mysql

Dalam sebarang persekitaran pangkalan data, akan sentiasa ada 不確定的意外 situasi, seperti pengecualian Gangguan kuasa, pelbagai kegagalan perisian dan perkakasan dalam sistem komputer, sabotaj manusia, salah operasi pentadbir, dsb. Situasi ini boleh membawa kepada akibat yang serius seperti 數(shù)據(jù)的丟失 dan 服務(wù)器癱瘓. Apabila terdapat berbilang pelayan, akan ada 數(shù)據(jù)同步問題 antara pelayan tuan dan hamba.

Untuk mencegah kehilangan data dengan berkesan dan meminimumkan kehilangan, anda harus 定期melakukan 備份 pada pelayan pangkalan data MySQL. Jika data dalam pangkalan data hilang atau ralat berlaku, data yang disandarkan boleh digunakan 進行恢復(fù). Penyegerakan data antara pelayan tuan dan hamba boleh dicapai melalui fungsi replikasi.

1. Sandaran fizikal dan sandaran logik

Sandaran fizikal : Sandarkan fail data dan buang fail fizikal pangkalan data ke direktori tertentu. Kelajuan pemulihan sandaran fizikal agak pantas, tetapi ia mengambil banyak ruang Anda boleh menggunakan alat xtrabackup dalam MySQL untuk melakukan sandaran fizikal.

Sandaran logik : Gunakan alatan untuk mengeksport objek pangkalan data dan meringkaskannya ke dalam fail sandaran. Kelajuan pemulihan sandaran logik adalah perlahan, tetapi ia mengambil sedikit ruang dan lebih fleksibel. Alat sandaran logik yang biasa digunakan dalam MySQL ialah mysqldump. Sandaran logik ialah 備份sql語句 Semasa pemulihan, pernyataan SQL sandaran dilaksanakan untuk menghasilkan semula data pangkalan data.

2. Mysqldump melaksanakan sandaran logik

Mysqldump ialah alat sandaran pangkalan data yang sangat berguna yang disediakan oleh MySQL.

2.1 Sandarkan pangkalan data

Apabila arahan mysqldump dilaksanakan, pangkalan data boleh disandarkan ke dalam 文本文件 Fail ini sebenarnya mengandungi berbilang pernyataan CREATE dan INSERT. Gunakan Pernyataan ini boleh mencipta semula jadual dan memasukkan data.

• Ketahui struktur jadual yang perlu disandarkan, dan jana pernyataan CREATE dalam fail teks
• Tukar semua rekod dalam jadual kepada pernyataan INSERT.

Sintaks asas:

mysqldump?–u?用戶名稱?–h?主機名稱?–p密碼?待備份的數(shù)據(jù)庫名稱[tbname,?[tbname...]]>?備份文件名稱.sql

Contoh: Gunakan pengguna akar untuk menyandarkan pangkalan data atguigu:

mysqldump?-uroot?-p?atguigu>atguigu.sql?#備份文件存儲在當(dāng)前目錄下

mysqldump?-uroot?-p?atguigudb1?>?/var/lib/mysql/atguigu.sql

Analisis fail sandaran:

--?MySQL?dump?10.13?Distrib?8.0.26,?for?Linux?(x86_64)
--
--?Host:?localhost?Database:?atguigu
--?------------------------------------------------------
--?Server?version?8.0.26
/*!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?*/;
/*!50503?SET?NAMES?utf8mb4?*/;
/*!40103?SET?@OLD_TIME_ZONE=@@TIME_ZONE?*/;
/*!40103?SET?TIME_ZONE='+00:00'?*/;
/*!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?*/;
--
--?Current?Database:?`atguigu`
--
CREATE?DATABASE?/*!32312?IF?NOT?EXISTS*/?`atguigu`?/*!40100?DEFAULT?CHARACTER?SET
utf8mb4?COLLATE?utf8mb4_0900_ai_ci?*/?/*!80016?DEFAULT?ENCRYPTION='N'?*/;
USE?`atguigu`;
--
--?Table?structure?for?table?`student`
--
DROP?TABLE?IF?EXISTS?`student`;
/*!40101?SET?@saved_cs_client?=?@@character_set_client?*/;
/*!50503?SET?character_set_client?=?utf8mb4?*/;
CREATE?TABLE?`student`?(
`studentno`?int?NOT?NULL,
`name`?varchar(20)?DEFAULT?NULL,
`class`?varchar(20)?DEFAULT?NULL,
PRIMARY?KEY?(`studentno`)
)?ENGINE=InnoDB?DEFAULT?CHARSET=utf8mb3;
/*!40101?SET?character_set_client?=?@saved_cs_client?*/;
INSERT?INTO?`student`?VALUES?(1,'張三_back','一班'),(3,'李四','一班'),(8,'王五','二班'),
(15,'趙六','二班'),(20,'錢七','>三班'),(22,'zhang3_update','1ban'),(24,'wang5','2ban');
/*!40000?ALTER?TABLE?`student`?ENABLE?KEYS?*/;
UNLOCK?TABLES;
.
.
.
.
/*!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?2022-01-07?9:58:23

• -- bermula dengan ulasan pernyataan SQL; ??> Pernyataan yang bermula dengan
dan berakhir dengan
• adalah ulasan MySQL boleh laku ini boleh dilaksanakan oleh MySQL, tetapi diabaikan sebagai ulasan dalam sistem pengurusan pangkalan data lain, yang boleh meningkatkan kemudahalihan pangkalan data /* !Permulaan fail menunjukkan nombor versi alat MySQLdump yang digunakan untuk menyandarkan fail diikuti dengan nama dan maklumat hos akaun sandaran, serta nama pangkalan data yang disandarkan; nombor versi pelayan MySQL, iaitu 8.0.26 dalam kes ini. */
Bahagian seterusnya bagi fail sandaran ialah beberapa penyata SET, yang memberikan beberapa nilai pembolehubah sistem???kepada pembolehubah yang ditentukan pengguna untuk memastikan pembolehubah sistem pangkalan data yang dipulihkan adalah sama seperti yang terdapat dalam sandaran asal, contohnya:
• 
  Beberapa baris terakhir fail sandaran MySQL menggunakan pernyataan SET untuk memulihkan nilai asal pembolehubah sistem pelayan, contohnya: Penyataan DROP , CIPTA pernyataan dan INSERT pernyataan selepas
semuanya dipulihkan digunakan apabila. Sebagai contoh, penyataan
• digunakan untuk menentukan sama ada terdapat jadual bernama pelajar dalam pangkalan data Jika ia wujud, padamkan penyataan CREATE digunakan untuk membuat jadual INSERT digunakan untuk memulihkan data .
• Sesetengah pernyataan pada permulaan fail sandaran bermula dengan nombor. Nombor ini mewakili nombor versi MySQL, memberitahu kami bahawa pernyataan ini hanya boleh dilaksanakan dalam versi MySQL yang ditentukan atau lebih tinggi daripada itu. Sebagai contoh, 40101 menunjukkan bahawa pernyataan ini hanya boleh dilaksanakan apabila nombor versi MySQL ialah 4.01.01 atau lebih tinggi. Masa sandaran direkodkan pada penghujung fail. DROPTABLE IF EXISTS 'student'
• 2.2 Sandarkan semua pangkalan data

Jika anda ingin menggunakan mysqldump untuk menyandarkan keseluruhan kejadian, anda boleh menggunakan parameter

atau

:

--all-databases-A2.3 Pangkalan data bahagian sandaran

mysqldump?-uroot?-pxxxxxx?--all-databases?>?all_database.sql?
mysqldump?-uroot?-pxxxxxx?-A?>?all_database.sql

menggunakan parameter

atau

Parameter ini diikuti dengan nama pangkalan data berbilang dipisahkan oleh ruang. Jika parameter pangkalan data ditentukan, pernyataan untuk mencipta pangkalan data akan wujud dalam fail sandaran Jika parameter tidak dinyatakan, ia tidak akan wujud. Sintaksnya adalah seperti berikut:

--databases-B Contoh

mysqldump?–u?user?–h?host?–p?--databases?[數(shù)據(jù)庫的名稱1?[數(shù)據(jù)庫的名稱2...]]?>?備份文件名稱.sql

atau

mysqldump?-uroot?-p?-B?atguigu?atguigu12?>?two_database.sql

2.4 備份部分表

比如，在表變更前做個備份。語法如下：

mysqldump?–u?user?–h?host?–p?數(shù)據(jù)庫的名稱?[表名1?[表名2...]]?>?備份文件名稱.sql

舉例：備份atguigu數(shù)據(jù)庫下的book表

mysqldump?-uroot?-p?atguigu?book>?book.sql#備份多張表?mysqldump?-uroot?-p?atguigu?book?account?>?2_tables_bak.sql

book.sql文件內(nèi)容如下

mysqldump?-uroot?-p?atguigu?book>?book.sql^C
[root@node1?~]#?ls
kk?kubekey?kubekey-v1.1.1-linux-amd64.tar.gz?README.md?test1.sql?two_database.sql
[root@node1?~]#?mysqldump?-uroot?-p?atguigu?book>?book.sql
Enter?password:
[root@node1?~]#?ls
book.sql?kk?kubekey?kubekey-v1.1.1-linux-amd64.tar.gz?README.md?test1.sql
two_database.sql
[root@node1?~]#?vi?book.sql
--?MySQL?dump?10.13?Distrib?8.0.26,?for?Linux?(x86_64)
--
--?Host:?localhost?Database:?atguigu
--?------------------------------------------------------
--?Server?version?8.0.26
/*!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?*/;
/*!50503?SET?NAMES?utf8mb4?*/;
/*!40103?SET?@OLD_TIME_ZONE=@@TIME_ZONE?*/;
/*!40103?SET?TIME_ZONE='+00:00'?*/;
/*!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?*/;
--
--?Table?structure?for?table?`book`
--
DROP?TABLE?IF?EXISTS?`book`;
/*!40101?SET?@saved_cs_client?=?@@character_set_client?*/;
/*!50503?SET?character_set_client?=?utf8mb4?*/;
CREATE?TABLE?`book`?(
`bookid`?int?unsigned?NOT?NULL?AUTO_INCREMENT,
`card`?int?unsigned?NOT?NULL,
`test`?varchar(255)?COLLATE?utf8_bin?DEFAULT?NULL,
PRIMARY?KEY?(`bookid`),
KEY?`Y`?(`card`)
)?ENGINE=InnoDB?AUTO_INCREMENT=101?DEFAULT?CHARSET=utf8mb3?COLLATE=utf8_bin;
/*!40101?SET?character_set_client?=?@saved_cs_client?*/;
--
--?Dumping?data?for?table?`book`
--
LOCK?TABLES?`book`?WRITE;
/*!40000?ALTER?TABLE?`book`?DISABLE?KEYS?*/;
INSERT?INTO?`book`?VALUES?(1,9,NULL),(2,10,NULL),(3,4,NULL),(4,8,NULL),(5,7,NULL),
(6,10,NULL),(7,11,NULL),(8,3,NULL),(9,1,NULL),(10,17,NULL),(11,19,NULL),(12,4,NULL),
(13,1,NULL),(14,14,NULL),(15,5,NULL),(16,5,NULL),(17,8,NULL),(18,3,NULL),(19,12,NULL),
(20,11,NULL),(21,9,NULL),(22,20,NULL),(23,13,NULL),(24,3,NULL),(25,18,NULL),
(26,20,NULL),(27,5,NULL),(28,6,NULL),(29,15,NULL),(30,15,NULL),(31,12,NULL),
(32,11,NULL),(33,20,NULL),(34,5,NULL),(35,4,NULL),(36,6,NULL),(37,17,NULL),
(38,5,NULL),(39,16,NULL),(40,6,NULL),(41,18,NULL),(42,12,NULL),(43,6,NULL),
(44,12,NULL),(45,2,NULL),(46,12,NULL),(47,15,NULL),(48,17,NULL),(49,2,NULL),
(50,16,NULL),(51,13,NULL),(52,17,NULL),(53,7,NULL),(54,2,NULL),(55,9,NULL),
(56,1,NULL),(57,14,NULL),(58,7,NULL),(59,15,NULL),(60,12,NULL),(61,13,NULL),
(62,8,NULL),(63,2,NULL),(64,6,NULL),(65,2,NULL),(66,12,NULL),(67,12,NULL),(68,4,NULL),
(69,5,NULL),(70,10,NULL),(71,16,NULL),(72,8,NULL),(73,14,NULL),(74,5,NULL),
(75,4,NULL),(76,3,NULL),(77,2,NULL),(78,2,NULL),(79,2,NULL),(80,3,NULL),(81,8,NULL),
(82,14,NULL),(83,5,NULL),(84,4,NULL),(85,2,NULL),(86,20,NULL),(87,12,NULL),
(88,1,NULL),(89,8,NULL),(90,18,NULL),(91,3,NULL),(92,3,NULL),(93,6,NULL),(94,1,NULL),
(95,4,NULL),(96,17,NULL),(97,15,NULL),(98,1,NULL),(99,20,NULL),(100,15,NULL);
/*!40000?ALTER?TABLE?`book`?ENABLE?KEYS?*/;
UNLOCK?TABLES;
/*!40103?SET?TIME_ZONE=@OLD_TIME_ZONE?*/;

可以看到， book文件和備份的庫文件類似。不同的是，book文件只包含book表的DROP、CREATE和INSERT語句。

備份多張表使用下面的命令，比如備份book和account表：

#備份多張表mysqldump?-uroot?-p?atguigu?book?account?>?2_tables_bak.sql

2.5 備份單表的部分?jǐn)?shù)據(jù)

有些時候一張表的數(shù)據(jù)量很大，我們只需要部分?jǐn)?shù)據(jù)。這時就可以使用--where選項了。where后面附帶需要滿足的條件。

舉例：備份student表中id小于10的數(shù)據(jù)：

mysqldump?-uroot?-p?atguigu?student?--where="id??student_part_id10_low_bak.sql

內(nèi)容如下所示，insert語句只有id小于10的部分

LOCK?TABLES?`student`?WRITE;
/*!40000?ALTER?TABLE?`student`?DISABLE?KEYS?*/;
INSERT?INTO?`student`?VALUES?(1,100002,'JugxTY',157,280),(2,100003,'QyUcCJ',251,277),
(3,100004,'lATUPp',80,404),(4,100005,'BmFsXI',240,171),(5,100006,'mkpSwJ',388,476),
(6,100007,'ujMgwN',259,124),(7,100008,'HBJTqX',429,168),(8,100009,'dvQSQA',61,504),
(9,100010,'HljpVJ',234,185);

2.6 排除某些表的備份

如果我們想備份某個庫，但是某些表數(shù)據(jù)量很大或者與業(yè)務(wù)關(guān)聯(lián)不大，這個時候可以考慮排除掉這些表，同樣的，選項--ignore-table可以完成這個功能。

mysqldump?-uroot?-p?atguigu?--ignore-table=atguigu.student?>?no_stu_bak.sql

通過如下指定判定文件中沒有student表結(jié)構(gòu)：

grep?"student"?no_stu_bak.sql

2.7 只備份結(jié)構(gòu)或只備份數(shù)據(jù)

只備份結(jié)構(gòu)的話可以使用--no-data簡寫為--d選項；只備份數(shù)據(jù)可以使用--no-create-info簡寫為--t選項。

• 只備份結(jié)構(gòu)

mysqldump?-uroot?-p?atguigu?--no-data?>?atguigu_no_data_bak.sql
#使用grep命令，沒有找到insert相關(guān)語句,表示沒有數(shù)據(jù)備份。
[root@node1?~]#?grep?"INSERT"?atguigu_no_data_bak.sql
[root@node1?~]#

• 只備份數(shù)據(jù)

mysqldump?-uroot?-p?atguigu?--no-data?>?atguigu_no_data_bak.sql
#使用grep命令，沒有找到insert相關(guān)語句，表示沒有數(shù)據(jù)備份。
[root@node1?~]#?grep?"INSERT"?atguigu_no_data_bak.sql
[root@node1?~]#

• 只備份數(shù)據(jù)

mysqldump?-uroot?-p?atguigu?--no-create-info?>?atguigu_no_create_info_bak.sql
#使用grep命令，沒有找到create相關(guān)語句，表示沒有數(shù)據(jù)結(jié)構(gòu)。
[root@node1?~]#?grep?"CREATE"?atguigu_no_create_info_bak.sql
[root@node1?~]#

2.8 備份中包含存儲過程、函數(shù)、事件

mysqldump備份默認是不包含存儲過程，自定義函數(shù)及事件的?？梢允褂?code>--routines或-R選項來備份存儲過程及函數(shù)，使用--events或-E參數(shù)來備份事件。

舉例：備份整個atguigu庫，包含存儲過程及事件：

• 使用下面的SQL可以查看當(dāng)前庫有哪些存儲過程或者函數(shù)

mysql>?SELECT?SPECIFIC_NAME,ROUTINE_TYPE?,ROUTINE_SCHEMA?FROM
information_schema.Routines?WHERE?ROUTINE_SCHEMA="atguigu";
+---------------+--------------+----------------+
|?SPECIFIC_NAME?|?ROUTINE_TYPE?|?ROUTINE_SCHEMA?|
+---------------+--------------+----------------+
|?rand_num?|?FUNCTION?|?atguigu?|
|?rand_string?|?FUNCTION?|?atguigu?|
|?BatchInsert?|?PROCEDURE?|?atguigu?|
|?insert_class?|?PROCEDURE?|?atguigu?|
|?insert_order?|?PROCEDURE?|?atguigu?|
|?insert_stu?|?PROCEDURE?|?atguigu?|
|?insert_user?|?PROCEDURE?|?atguigu?|
|?ts_insert?|?PROCEDURE?|?atguigu?|
+---------------+--------------+----------------+
9?rows?in?set?(0.02?sec)

下面?zhèn)浞輆tguigu庫的數(shù)據(jù)，函數(shù)以及存儲過程。

mysqldump?-uroot?-p?-R?-E?--databases?atguigu?>?fun_atguigu_bak.sql

查詢備份文件中是否存在函數(shù)，如下所示，可以看到確實包含了函數(shù)。

grep?-C?5?"rand_num"?fun_atguigu_bak.sql
--
--
--?Dumping?routines?for?database?'atguigu'--
/*!50003?DROP?FUNCTION?IF?EXISTS?`rand_num`?*/;/*!50003?SET?@saved_cs_client?=?@@character_set_client?*/?;/*!50003?SET?@saved_cs_results?=?@@character_set_results?*/?;/*!50003?SET?@saved_col_connection?=?@@collation_connection?*/?;/*!50003?SET?character_set_client?=?utf8mb3?*/?;/*!50003?SET?character_set_results?=?utf8mb3?*/?;/*!50003?SET?collation_connection?=?utf8_general_ci?*/?;/*!50003?SET?@saved_sql_mode?=?@@sql_mode?*/?;/*!50003?SET?sql_mode?='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_pISIO
N_BY_ZERO,NO_ENGINE_SUBSTITUTION'?*/?;DELIMITER?;;CREATE?DEFINER=`root`@`%`?FUNCTION?`rand_num`(from_num?BIGINT?,to_num?BIGINT)?RETURNS
bigint
BEGIN
DECLARE?i?BIGINT?DEFAULT?0;SET?i?=?FLOOR(from_num?+RAND()*(to_num?-?from_num+1))?;RETURN?i;END?;;--
BEGIN
DECLARE?i?INT?DEFAULT?0;SET?autocommit?=?0;REPEAT
SET?i?=?i?+?1;INSERT?INTO?class?(?classname,address,monitor?)?VALUES(rand_string(8),rand_string(10),rand_num());UNTIL?i?=?max_num
END?REPEAT;COMMIT;END?;;DELIMITER?;--
BEGIN
DECLARE?i?INT?DEFAULT?0;SET?autocommit?=?0;?#設(shè)置手動提交事務(wù)REPEAT?#循環(huán)SET?i?=?i?+?1;?#賦值INSERT?INTO?order_test?(order_id,?trans_id?)?VALUES(rand_num(1,7000000),rand_num(100000000000000000,700000000000000000));UNTIL?i?=?max_num
END?REPEAT;COMMIT;?#提交事務(wù)END?;;DELIMITER?;--
BEGIN
DECLARE?i?INT?DEFAULT?0;SET?autocommit?=?0;?#設(shè)置手動提交事務(wù)REPEAT?#循環(huán)SET?i?=?i?+?1;?#賦值INSERT?INTO?student?(stuno,?name?,age?,classId?)?VALUES((START+i),rand_string(6),rand_num(),rand_num());UNTIL?i?=?max_num
END?REPEAT;COMMIT;?#提交事務(wù)END?;;DELIMITER?;--
BEGIN
DECLARE?i?INT?DEFAULT?0;SET?autocommit?=?0;REPEAT
SET?i?=?i?+?1;INSERT?INTO?`user`?(?name,age,sex?)?VALUES?("atguigu",rand_num(1,20),"male");UNTIL?i?=?max_num
END?REPEAT;COMMIT;END?;;DELIMITER?;

三、mysql命令恢復(fù)數(shù)據(jù)

使用mysqldump命令將數(shù)據(jù)庫中的數(shù)據(jù)備份成一個文本文件。需要恢復(fù)時，可以使用mysql命令來恢復(fù)備份的數(shù)據(jù)。

mysql命令可以執(zhí)行備份文件中的CREATE語句和INSERT語句。通過CREATE語句來創(chuàng)建數(shù)據(jù)庫和表。通過INSERT語句來插入備份的數(shù)據(jù)。

基本語法：

mysql?–u?root?–p?[dbname]?<p>其中，dbname參數(shù)表示數(shù)據(jù)庫名稱。該參數(shù)是可選參數(shù)，可以指定數(shù)據(jù)庫名，也可以不指定。指定數(shù)據(jù)庫名時，表示還原該數(shù)據(jù)庫下的表。此時需要確保MySQL服務(wù)器中已經(jīng)創(chuàng)建了該名的數(shù)據(jù)庫。不指定數(shù)據(jù)庫名時，表示還原文件中所有的數(shù)據(jù)庫。此時sql文件中包含有CREATE DATABASE語句，不需要MysQL服務(wù)器中已存在這些數(shù)據(jù)庫。</p><h2>3.1 單庫備份中恢復(fù)單庫</h2><p>使用root用戶，將之前練習(xí)中備份的atguigu.sql文件中的備份導(dǎo)入數(shù)據(jù)庫中，命令如下：</p><p>如果備份文件中包含了創(chuàng)建數(shù)據(jù)庫的語句，則恢復(fù)的時候不需要指定數(shù)據(jù)庫名稱，如下所示</p><pre class="brush:php;toolbar:false">#備份文件中包含了創(chuàng)建數(shù)據(jù)庫的語句mysql?-uroot?-p?<p>否則需要指定數(shù)據(jù)庫名稱，如下所示</p><pre class="brush:php;toolbar:false">#備份文件中不包含了創(chuàng)建數(shù)據(jù)庫的語句mysql?-uroot?-p?atguigu4<h2>3.2 全量備份恢復(fù)</h2><p>如果我們現(xiàn)在有昨天的全量備份，現(xiàn)在想整個恢復(fù)，則可以這樣操作：</p><pre class="brush:php;toolbar:false">mysql?–u?root?–p?<pre class="brush:php;toolbar:false">mysql?-uroot?-pxxxxxx?<p>執(zhí)行完后，MySQL數(shù)據(jù)庫中就已經(jīng)恢復(fù)了all.sql文件中的所有數(shù)據(jù)庫。</p><blockquote><p>補充：<br> 如果使用<code>--all-databases</code>參數(shù)備份了所有的數(shù)據(jù)庫，那么恢復(fù)時不需要指定數(shù)據(jù)庫。對應(yīng)的sql文件包含有CREATE DATABASE語句，可通過該語句創(chuàng)建數(shù)據(jù)庫。創(chuàng)建數(shù)據(jù)庫后，可以執(zhí)行sql文件中的USE語句選擇數(shù)據(jù)庫，再創(chuàng)建表并插入記錄。</p></blockquote><h2>3.3 從全量備份中恢復(fù)單庫</h2><p>可能有這樣的需求，比如說我們只想恢復(fù)某一個庫，但是我們有的是整個實例的備份，這個時候我們可以從全量備份中分離出單個庫的備份。</p><p>舉例：</p><pre class="brush:php;toolbar:false">sed?-n?'/^--?Current?Database:?`atguigu`/,/^--?Current?Database:?`/p'?all_database.sql?>?atguigu.sql?

#分離完成后我們再導(dǎo)入atguigu.sql即可恢復(fù)單個庫

3.4 從單庫備份中恢復(fù)單表

這個需求還是比較常見的。比如說我們知道哪個表誤操作了，那么就可以用單表恢復(fù)的方式來恢復(fù)。

舉例：我們有atguigu整庫的備份，但是由于class表誤操作，需要單獨恢復(fù)出這張表。

cat?atguigu.sql?|?sed?-e?'/./{H;$!d;}'?-e?'x;/CREATE?TABLE?`class`/!d;q'?>?class_structure.sql?
cat?atguigu.sql?|?grep?--ignore-case?'insert?into?`class`'?>?class_data.sql?
#用shell語法分離出創(chuàng)建表的語句及插入數(shù)據(jù)的語句后?再依次導(dǎo)出即可完成恢復(fù)?

use?atguigu;?
mysql>?source?class_structure.sql;?
Query?OK,?0?rows?affected,?1?warning?(0.00?sec)?

mysql>?source?class_data.sql;?
Query?OK,?1?row?affected?(0.01?sec)

四、物理備份：直接復(fù)制整個數(shù)據(jù)庫

直接將MySQL中的數(shù)據(jù)庫文件復(fù)制出來。這種方法最簡單，速度也最快。MySQL的數(shù)據(jù)庫目錄位置不一定相同：

• 在Windows平臺下，MySQL 8.0存放數(shù)據(jù)庫的目錄通常默認為 C:\ProgramData\MySQL\MySQL Server 8.0\Data或者其他用戶自定義目錄；
• 在Linux平臺下，數(shù)據(jù)庫目錄位置通常為/var/lib/mysql/；
• 在MAC OSX平臺下，數(shù)據(jù)庫目錄位置通常為“/usr/local/mysql/data”

但為了保證備份的一致性。需要保證：

• 方式1：備份前，將服務(wù)器停止。
• 方式2：備份前，對相關(guān)表執(zhí)行FLUSH TABLES WITH READ LOCK操作。這樣當(dāng)復(fù)制數(shù)據(jù)庫目錄中的文件時，允許其他客戶繼續(xù)查詢表。同時，F(xiàn)LUSH TABLES語句來確保開始備份前將所有激活的索引頁寫入硬盤。

這種方式方便、快速，但不是最好的備份方法，因為實際情況可能不允許停止MySQL服務(wù)器或者鎖住表，而且這種方法對InnoDB存儲引擎的表不適用。對于MyISAM存儲引擎的表，這樣備份和還原很方便，但是還原時最好是相同版本的MySQL數(shù)據(jù)庫，否則可能會存在文件類型不同的情況。

注意，物理備份完畢后，執(zhí)行UNLOCK TABLES來結(jié)算其他客戶對表的修改行為。

說明： 在MySQL版本號中，第一個數(shù)字表示主版本號，主版本號相同的MySQL數(shù)據(jù)庫文件格式相同。

此外，還可以考慮使用相關(guān)工具實現(xiàn)備份。比如，MySQLhotcopy工具。MySQLhotcopy是一個Perl腳本，它使用LOCK TABLES、FLUSH TABLES和cp或scp來快速備份數(shù)據(jù)庫。它是備份數(shù)據(jù)庫或單個表最快的途徑，但它只能運行在數(shù)據(jù)庫目錄所在的機器上，并且只能備份MyISAM類型的表。多用于mysql5.5之前。

五、物理恢復(fù)：直接復(fù)制到數(shù)據(jù)庫目錄

步驟：

1. 演示刪除備份的數(shù)據(jù)庫中指定表的數(shù)據(jù)
2. 將備份的數(shù)據(jù)庫數(shù)據(jù)拷貝到數(shù)據(jù)目錄下，并重啟MySQL服務(wù)器
3. 查詢相關(guān)表的數(shù)據(jù)是否恢復(fù)。需要使用下面的 chown 操作。

要求：

• 必須確保備份數(shù)據(jù)的數(shù)據(jù)庫和待恢復(fù)的數(shù)據(jù)庫服務(wù)器的主版本號相同。
  • 因為只有MySQL數(shù)據(jù)庫主版本號相同時，才能保證這兩個MySQL數(shù)據(jù)庫文件類型是相同的。
• 這種方式對MyISAM類型的表比較有效，對于InnoDB類型的表則不可用。
  • 因為InnoDB表的表空間不能直接復(fù)制。
• 在Linux操作系統(tǒng)下，復(fù)制到數(shù)據(jù)庫目錄后，一定要將數(shù)據(jù)庫的用戶和組變成mysql，命令如下：

chown?-R?mysql.mysql?/var/lib/mysql/dbname

其中，兩個mysql分別表示組和用戶；“-R”參數(shù)可以改變文件夾下的所有子文件的用戶和組；“dbname”參數(shù)表示數(shù)據(jù)庫目錄。

提示 Linux操作系統(tǒng)下的權(quán)限設(shè)置非常嚴(yán)格。通常情況下，MySQL數(shù)據(jù)庫只有root用戶和mysql用戶組下的mysql用戶才可以訪問，因此將數(shù)據(jù)庫目錄復(fù)制到指定文件夾后，一定要使用chown命令將文件夾的用戶組變?yōu)閙ysql，將用戶變?yōu)閙ysql。

六、表的導(dǎo)出與導(dǎo)入

6.1 表的導(dǎo)出

1. 使用SELECT…INTO OUTFILE導(dǎo)出文本文件

在MySQL中，可以使用SELECT…INTO OUTFILE語句將表的內(nèi)容導(dǎo)出成一個文本文件。

舉例：使用SELECT…INTO OUTFILE將atguigu數(shù)據(jù)庫中account表中的記錄導(dǎo)出到文本文件。

（1）選擇數(shù)據(jù)庫atguigu，并查詢account表，執(zhí)行結(jié)果如下所示。

use?atguigu;
select?*?from?account;
mysql>?select?*?from?account;
+----+--------+---------+
|?id?|?name?|?balance?|
+----+--------+---------+
|?1?|?張三?|?90?|
|?2?|?李四?|?100?|
|?3?|?王五?|?0?|
+----+--------+---------+
3?rows?in?set?(0.01?sec)

（2）mysql默認對導(dǎo)出的目錄有權(quán)限限制，也就是說使用命令行進行導(dǎo)出的時候，需要指定目錄進行操作。

查詢secure_file_priv值：

mysql>?SHOW?GLOBAL?VARIABLES?LIKE?'%secure%';
+--------------------------+-----------------------+
|?Variable_name?|?Value?|
+--------------------------+-----------------------+
|?require_secure_transport?|?OFF?|
|?secure_file_priv?|?/var/lib/mysql-files/?|
+--------------------------+-----------------------+
2?rows?in?set?(0.02?sec)

參數(shù)secure_file_priv的可選值和作用分別是：

• 如果設(shè)置為empty，表示不限制文件生成的位置，這是不安全的設(shè)置；
• 如果設(shè)置為一個表示路徑的字符串，就要求生成的文件只能放在這個指定的目錄，或者它的子目錄；
• 如果設(shè)置為NULL，就表示禁止在這個MySQL實例上執(zhí)行select … into outfile操作。

（3）上面結(jié)果中顯示，secure_file_priv變量的值為/var/lib/mysql-files/，導(dǎo)出目錄設(shè)置為該目錄，SQL語句如下。

SELECT?*?FROM?account?INTO?OUTFILE?"/var/lib/mysql-files/account.txt";

（4）查看 /var/lib/mysql-files/account.txt`文件。

1?張三?902?李四?1003?王五?0

2. 使用mysqldump命令導(dǎo)出文本文件

舉例1：使用mysqldump命令將將atguigu數(shù)據(jù)庫中account表中的記錄導(dǎo)出到文本文件：

mysqldump?-uroot?-p?-T?"/var/lib/mysql-files/"?atguigu?account

mysqldump命令執(zhí)行完畢后，在指定的目錄/var/lib/mysql-files/下生成了account.sql和account.txt文件。

打開account.sql文件，其內(nèi)容包含創(chuàng)建account表的CREATE語句。

[root@node1?mysql-files]#?cat?account.sql
--?MySQL?dump?10.13?Distrib?8.0.26,?for?Linux?(x86_64)
--
--?Host:?localhost?Database:?atguigu
--?------------------------------------------------------
--?Server?version?8.0.26
/*!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?*/;
/*!50503?SET?NAMES?utf8mb4?*/;
/*!40103?SET?@OLD_TIME_ZONE=@@TIME_ZONE?*/;
/*!40103?SET?TIME_ZONE='+00:00'?*/;
/*!40101?SET?@OLD_SQL_MODE=@@SQL_MODE,?SQL_MODE=''?*/;
/*!40111?SET?@OLD_SQL_NOTES=@@SQL_NOTES,?SQL_NOTES=0?*/;
--
--?Table?structure?for?table?`account`
--
DROP?TABLE?IF?EXISTS?`account`;
/*!40101?SET?@saved_cs_client?=?@@character_set_client?*/;
/*!50503?SET?character_set_client?=?utf8mb4?*/;
CREATE?TABLE?`account`?(
`id`?int?NOT?NULL?AUTO_INCREMENT,
`name`?varchar(255)?NOT?NULL,
`balance`?int?NOT?NULL,
PRIMARY?KEY?(`id`)
)?ENGINE=InnoDB?AUTO_INCREMENT=4?DEFAULT?CHARSET=utf8mb3;
/*!40101?SET?character_set_client?=?@saved_cs_client?*/;
/*!40103?SET?TIME_ZONE=@OLD_TIME_ZONE?*/;
/*!40101?SET?SQL_MODE=@OLD_SQL_MODE?*/;
/*!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?2022-01-07?23:19:27

打開account.txt文件，其內(nèi)容只包含account表中的數(shù)據(jù)。

[root@node1?mysql-files]#?cat?account.txt1?張三?902?李四?1003?王五?0

舉例2：使用mysqldump將atguigu數(shù)據(jù)庫中的account表導(dǎo)出到文本文件，使用FIELDS選項，要求字段之間使用逗號“，”間隔，所有字符類型字段值用雙引號括起來：

mysqldump?-uroot?-p?-T?"/var/lib/mysql-files/"?atguigu?account?--fields-terminatedby=','?--fields-optionally-enclosed-by='\"'

語句mysqldump語句執(zhí)行成功之后，指定目錄下會出現(xiàn)兩個文件account.sql和account.txt。

打開account.sql文件，其內(nèi)容包含創(chuàng)建account表的CREATE語句。

[root@node1?mysql-files]#?cat?account.sql
--?MySQL?dump?10.13?Distrib?8.0.26,?for?Linux?(x86_64)
--
--?Host:?localhost?Database:?atguigu
--?------------------------------------------------------
--?Server?version?8.0.26
/*!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?*/;
/*!50503?SET?NAMES?utf8mb4?*/;
/*!40103?SET?@OLD_TIME_ZONE=@@TIME_ZONE?*/;
/*!40103?SET?TIME_ZONE='+00:00'?*/;
/*!40101?SET?@OLD_SQL_MODE=@@SQL_MODE,?SQL_MODE=''?*/;
/*!40111?SET?@OLD_SQL_NOTES=@@SQL_NOTES,?SQL_NOTES=0?*/;
--
--?Table?structure?for?table?`account`
--
DROP?TABLE?IF?EXISTS?`account`;
/*!40101?SET?@saved_cs_client?=?@@character_set_client?*/;
/*!50503?SET?character_set_client?=?utf8mb4?*/;
CREATE?TABLE?`account`?(
`id`?int?NOT?NULL?AUTO_INCREMENT,
`name`?varchar(255)?NOT?NULL,
`balance`?int?NOT?NULL,
PRIMARY?KEY?(`id`)
)?ENGINE=InnoDB?AUTO_INCREMENT=4?DEFAULT?CHARSET=utf8mb3;
/*!40101?SET?character_set_client?=?@saved_cs_client?*/;
/*!40103?SET?TIME_ZONE=@OLD_TIME_ZONE?*/;
/*!40101?SET?SQL_MODE=@OLD_SQL_MODE?*/;
/*!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?2022-01-07?23:36:39

打開account.txt文件，其內(nèi)容包含創(chuàng)建account表的數(shù)據(jù)。從文件中可以看出，字段之間用逗號隔開，字符類型的值被雙引號括起來。

[root@node1?mysql-files]#?cat?account.txt1,"張三",902,"李四",1003,"王五",0

3. 使用mysql命令導(dǎo)出文本文件

舉例1：使用mysql語句導(dǎo)出atguigu數(shù)據(jù)中account表中的記錄到文本文件：

mysql?-uroot?-p?--execute="SELECT?*?FROM?account;"?atguigu>?"/var/lib/mysqlfiles/account.txt"

打開account.txt文件，其內(nèi)容包含創(chuàng)建account表的數(shù)據(jù)。

[root@node1?mysql-files]#?cat?account.txtid?name?balance1?張三?902?李四?1003?王五?0

舉例2：將atguigu數(shù)據(jù)庫account表中的記錄導(dǎo)出到文本文件，使用–veritcal參數(shù)將該條件記錄分為多行顯示：

mysql?-uroot?-p?--vertical?--execute="SELECT?*?FROM?account;"?atguigu?>"/var/lib/mysql-files/account_1.txt"

打開account_1.txt文件，其內(nèi)容包含創(chuàng)建account表的數(shù)據(jù)。

[root@node1?mysql-files]#?cat?account_1.txt***************************?1.?row?***************************
id:?1name:?張三
balance:?90***************************?2.?row?***************************
id:?2name:?李四
balance:?100***************************?3.?row?***************************
id:?3name:?王五
balance:?0

舉例3：將atguigu數(shù)據(jù)庫account表中的記錄導(dǎo)出到xml文件，使用–xml參數(shù)，具體語句如下。

mysql?-uroot?-p?--xml?--execute="SELECT?*?FROM?account;"?atguigu>"/var/lib/mysqlfiles/account_3.xml"

[root@node1?mysql-files]#?cat?account_3.xml
<?xml  version="1.0"?>
<resultset xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<field>1</field>
<field>張三</field>
<field>90</field>
</row>
<row>
<field>2</field>
<field>李四</field>
<field>100</field>
</row>
<row>
<field>3</field>
<field>王五</field>
<field>0</field>
</row>
</resultset>

說明：如果要將表數(shù)據(jù)導(dǎo)出到html文件中，可以使用--html選項。然后可以使用瀏覽器打開。

6.2 表的導(dǎo)入

1. 使用LOAD DATA INFILE方式導(dǎo)入文本文件

舉例1：

使用SELECT…INTO OUTFILE將atguigu數(shù)據(jù)庫中account表的記錄導(dǎo)出到文本文件

SELECT?*?FROM?atguigu.account?INTO?OUTFILE?'/var/lib/mysql-files/account_0.txt';

刪除account表中的數(shù)據(jù)：

DELETE?FROM?atguigu.account;

從文本文件account.txt中恢復(fù)數(shù)據(jù)：

LOAD?DATA?INFILE?'/var/lib/mysql-files/account_0.txt'?INTO?TABLE?atguigu.account;

查詢account表中的數(shù)據(jù)：

mysql>?select?*?from?account;
+----+--------+---------+
|?id?|?name?|?balance?|
+----+--------+---------+
|?1?|?張三?|?90?|
|?2?|?李四?|?100?|
|?3?|?王五?|?0?|
+----+--------+---------+
3?rows?in?set?(0.00?sec)

舉例2： 選擇數(shù)據(jù)庫atguigu，使用SELECT…INTO OUTFILE將atguigu數(shù)據(jù)庫account表中的記錄導(dǎo)出到文本文件，使用FIELDS選項和LINES選項，要求字段之間使用逗號"，"間隔，所有字段值用雙引號括起來：

SELECT?*?FROM?atguigu.account?INTO?OUTFILE?'/var/lib/mysql-files/account_1.txt'?FIELDS
TERMINATED?BY?','?ENCLOSED?BY?'\"';

刪除account表中的數(shù)據(jù)：

DELETE?FROM?atguigu.account;

從/var/lib/mysql-files/account.txt中導(dǎo)入數(shù)據(jù)到account表中：

LOAD?DATA?INFILE?'/var/lib/mysql-files/account_1.txt'?INTO?TABLE?atguigu.account
FIELDS?TERMINATED?BY?','?ENCLOSED?BY?'\"';

查詢account表中的數(shù)據(jù)，具體SQL如下：

select?*?from?account;
mysql>?select?*?from?account;
+----+--------+---------+
|?id?|?name?|?balance?|
+----+--------+---------+
|?1?|?張三?|?90?|
|?2?|?李四?|?100?|
|?3?|?王五?|?0?|
+----+--------+---------+
3?rows?in?set?(0.00?sec)

2. 使用mysqlimport方式導(dǎo)入文本文件

舉例：

導(dǎo)出文件account.txt，字段之間使用逗號"，"間隔，字段值用雙引號括起來：

SELECT * FROM atguigu.account INTO OUTFILE '/var/lib/mysql-files/account.txt' FIELDS
TERMINATED BY ',' ENCLOSED BY '\"';

刪除account表中的數(shù)據(jù)：

DELETE FROM atguigu.account;

使用mysqlimport命令將account.txt文件內(nèi)容導(dǎo)入到數(shù)據(jù)庫atguigu的account表中：

mysqlimport -uroot -p atguigu '/var/lib/mysql-files/account.txt' --fields-terminatedby=',' --fields-optionally-enclosed-by='\"'

查詢account表中的數(shù)據(jù)：

select * from account;
mysql> select * from account;
+----+--------+---------+
| id | name | balance |
+----+--------+---------+
| 1 | 張三 | 90 |
| 2 | 李四 | 100 |
| 3 | 王五 | 0 |
+----+--------+---------+
3 rows in set (0.00 sec)

七、數(shù)據(jù)庫遷移

7.1 概述

數(shù)據(jù)遷移（data migration）是指選擇、準(zhǔn)備、提取和轉(zhuǎn)換數(shù)據(jù)，并 將數(shù)據(jù)從一個計算機存儲系統(tǒng)永久地傳輸?shù)搅硪粋€計算機存儲系統(tǒng)的過程 。此外，驗證遷移數(shù)據(jù)的完整性和退役原來舊的數(shù)據(jù)存儲，也被認為是整個數(shù)據(jù)遷移過程的一部分。

數(shù)據(jù)庫遷移的原因是多樣的，包括服務(wù)器或存儲設(shè)備更換、維護或升級，應(yīng)用程序遷移，網(wǎng)站集成，災(zāi)難恢復(fù)和數(shù)據(jù)中心遷移。

根據(jù)不同的需求可能要采取不同的遷移方案，但總體來講，MySQL 數(shù)據(jù)遷移方案大致可以分為物理遷移和邏輯遷移兩類。通常以盡可能自動化的方式執(zhí)行，從而將人力資源從繁瑣的任務(wù)中解放出來。

7.2 遷移方案

物理遷移

物理遷移適用于大數(shù)據(jù)量下的整體遷移。使用物理遷移方案的優(yōu)點是比較快速，但需要停機遷移并且要求 MySQL 版本及配置必須和原服務(wù)器相同，也可能引起未知問題。

物理遷移包括拷貝數(shù)據(jù)文件和使用 XtraBackup 備份工具兩種。

不同服務(wù)器之間可以采用物理遷移，我們可以在新的服務(wù)器上安裝好同版本的數(shù)據(jù)庫軟件，創(chuàng)建好相同目錄，建議配置文件也要和原數(shù)據(jù)庫相同，然后從原數(shù)據(jù)庫方拷貝來數(shù)據(jù)文件及日志文件，配置好文件組權(quán)限，之后在新服務(wù)器這邊使用 mysqld 命令啟動數(shù)據(jù)庫。

邏輯遷移

邏輯遷移適用范圍更廣，無論是 部分遷移 還是 全量遷移 ，都可以使用邏輯遷移。邏輯遷移中使用最多的就是通過 mysqldump 等備份工具。

7.3 遷移注意點

① 相同版本的數(shù)據(jù)庫之間遷移注意點

指的是在主版本號相同的MySQL數(shù)據(jù)庫之間進行數(shù)據(jù)庫移動。

方式1： 因為遷移前后MySQL數(shù)據(jù)庫的主版本號相同，所以可以通過復(fù)制數(shù)據(jù)庫目錄來實現(xiàn)數(shù)據(jù)庫遷移，但是物理遷移方式只適用于MyISAM引擎的表。對于InnoDB表，不能用直接復(fù)制文件的方式備份數(shù)據(jù)庫。

方式2： 最常見和最安全的方式是使用mysqldump命令導(dǎo)出數(shù)據(jù)，然后在目標(biāo)數(shù)據(jù)庫服務(wù)器中使用MySQL命令導(dǎo)入。

舉例：

#host1的機器中備份所有數(shù)據(jù)庫,并將數(shù)據(jù)庫遷移到名為host2的機器上mysqldump –h host1 –uroot –p –-all-databases|mysql –h host2 –uroot –p

在上述語句中，|符號表示管道，其作用是將mysqldump備份的文件給mysql命令；--all-databases表示要遷移所有的數(shù)據(jù)庫。通過這種方式可以直接實現(xiàn)遷移。

② 不同版本的數(shù)據(jù)庫之間遷移注意點

例如，原來很多服務(wù)器使用5.7版本的MySQL數(shù)據(jù)庫，在8.0版本推出來以后，改進了5.7版本的很多缺陷，因此需要把數(shù)據(jù)庫升級到8.0版本

舊版本與新版本的MySQL可能使用不同的默認字符集，例如有的舊版本中使用latin1作為默認字符集，而最新版本的MySQL默認字符集為utf8mb4。如果數(shù)據(jù)庫中有中文數(shù)據(jù)，那么遷移過程中需要對默認字符集進行修改 ，不然可能無法正常顯示數(shù)據(jù)。

高版本的MySQL數(shù)據(jù)庫通常都會兼容低版本，因此可以從低版本的MySQL數(shù)據(jù)庫遷移到高版本的MySQL數(shù)據(jù)庫。

③ 不同數(shù)據(jù)庫之間遷移注意點

不同數(shù)據(jù)庫之間遷移是指從其他類型的數(shù)據(jù)庫遷移到MySQL數(shù)據(jù)庫，或者從MySQL數(shù)據(jù)庫遷移到其他類型的數(shù)據(jù)庫。這種遷移沒有普適的解決方法。

遷移之前，需要了解不同數(shù)據(jù)庫的架構(gòu)，比較它們之間的差異。不同數(shù)據(jù)庫中定義相同類型的數(shù)據(jù)的關(guān)鍵字可能會不同。例如，MySQL中日期字段分為DATE和TIME兩種，而ORACLE日期字段只有DATE；SQL Server數(shù)據(jù)庫中有ntext、Image等數(shù)據(jù)類型，MySQL數(shù)據(jù)庫沒有這些數(shù)據(jù)類型；MySQL支持的ENUM和SET類型，這些SQL Server數(shù)據(jù)庫不支持。

另外，數(shù)據(jù)庫廠商并沒有完全按照SQL標(biāo)準(zhǔn)來設(shè)計數(shù)據(jù)庫系統(tǒng)，導(dǎo)致不同的數(shù)據(jù)庫系統(tǒng)的SQL語句有差別。例如，微軟的SQL Server軟件使用的是T-SQL語句，T-SQL中包含了非標(biāo)準(zhǔn)的SQL語句，不能和MySQL的SQL語句兼容。

不同類型數(shù)據(jù)庫之間的差異造成了互相遷移的困難，這些差異其實是商業(yè)公司故意造成的技術(shù)壁壘。但是不同類型的數(shù)據(jù)庫之間的遷移并不是完全不可能 。例如，可以使用MyODBC實現(xiàn)MySQL和SQL Server之間的遷移。MySQL官方提供的工具MySQL Migration Toolkit也可以在不同數(shù)據(jù)之間進行數(shù)據(jù)遷移。MySQL遷移到Oracle時，需要使用mysqldump命令導(dǎo)出sql文件，然后，手動更改sql文件中的CREATE語句。

7.4 遷移小結(jié)

八、刪庫了不敢跑，能干點啥？

傳統(tǒng)的高可用架構(gòu)是不能預(yù)防誤刪數(shù)據(jù)的，因為主庫的一個drop table命令，會通過binlog傳給所有從庫和級聯(lián)從庫，進而導(dǎo)致整個集群的實例都會執(zhí)行這個命令。

為了找到解決誤刪數(shù)據(jù)的更高效的方法，我們需要先對和MySQL相關(guān)的誤刪數(shù)據(jù)，做下分類：

1. 使用delete語句誤刪數(shù)據(jù)行；
2. 使用drop table或者truncate table語句誤刪數(shù)據(jù)表；
3. 使用drop database語句誤刪數(shù)據(jù)庫；
4. 使用rm命令誤刪整個MySQL實例。

8.1 delete：誤刪行

處理措施1：數(shù)據(jù)恢復(fù)

使用Flashback工具恢復(fù)數(shù)據(jù)。

原理：修改binlog內(nèi)容，拿回原庫重放。如果誤刪數(shù)據(jù)涉及到了多個事務(wù)的話，需要將事務(wù)的順序調(diào)過來再執(zhí)行。

使用前提：binlog_format=row和binlog_row_image=FULL。

處理措施2：預(yù)防

• 代碼上線前，必須SQL審查、審計。

• 建議可以打開安全模式，把sql_safe_updates參數(shù)設(shè)置為on。強制要求加where條件且where后需要是索引字段，否則必須使用limit。否則就會報錯。

8.2 truncate/drop: Pemadaman pangkalan data/jadual secara tidak sengaja

Latar Belakang:
Memadamkan keseluruhan jadual adalah sangat perlahan . Ia adalah perlu untuk menjana log rollback, menulis buat semula, dan menulis binlog. Oleh itu, dari perspektif prestasi, beri keutamaan untuk menggunakan perintah truncatetable atau drop table.

Anda juga boleh menggunakan Flashback untuk memulihkan data yang dipadam menggunakan arahan padam. Data yang dipadam menggunakan perintah truncate /drop table dan drop pangkalan data tidak boleh dipulihkan melalui Flashback. Kerana, walaupun kita mengkonfigurasi binlog_format=row, apabila melaksanakan ketiga-tiga arahan ini, binlog yang direkodkan masih dalam format pernyataan. Hanya terdapat satu penyataan truncate/drop dalam binlog, dan data tidak boleh dipulihkan daripada maklumat ini.

Penyelesaian :

Dalam kes ini, anda perlu menggunakan gabungan sandaran penuh dan log tambahan untuk memulihkan data.

Premis penyelesaian: sandaran penuh biasa dan sandaran masa nyata binlog.

Contoh: Seseorang telah tersilap memadam pustaka pada pukul 3 petang. Langkah-langkahnya adalah seperti berikut:

1. Dapatkan yang terkini 全量備份. Andaikan bahawa pangkalan data disediakan untuk disandarkan sekali sehari, dan data sandaran terkini ialah hari itu凌晨2點;
2. Pulihkan satu daripada sandaran臨時庫 (Nota: Pilih pangkalan data sementara di sini bukannya mengendalikan pangkalan data utama secara langsung)
3. Keluarkan log binlog selepas 2 pagi;
4. Kecuali pernyataan yang memadamkan data secara tidak sengaja, semua pernyataan lain digunakan pada pustaka sementara. (Saya bercakap tentang pemulihan binlog sebelum ini)
5. Akhirnya pulihkan ke pangkalan data utama

8.3 Halang penggunaan truncate /drop untuk memadam pangkalan data/jadual secara tidak sengaja

Kami menyebut penggunaan di atas Pelan pemulihan untuk memadam perpustakaan/jadual secara tidak sengaja dengan penyataan truncate/drop boleh digunakan dalam persekitaran pengeluaran untuk mengelakkan salah operasi yang serupa sebanyak mungkin melalui penyelesaian yang dicadangkan berikut.

① Pemisahan kebenaran

• Hadkan kebenaran akaun pada umumnya 不能隨便分配寫權(quán)限. anda perlukan 審批. Contohnya, hanya kebenaran DML diberikan kepada pembangun perniagaan, tetapi kebenaran memotong/menggugurkan tidak diberikan. Malah ahli pasukan DBA dikehendaki hanya menggunakan 只讀賬號 setiap hari dan hanya menggunakan akaun dengan kebenaran kemas kini apabila perlu.
• Akaun yang berbeza dan data yang berbeza mesti 權(quán)限分離ed untuk mengelakkan satu akaun daripada memadamkan semua pustaka.

② Bangunkan spesifikasi pengendalian

Contohnya, sebelum memadamkan jadual data, anda mesti menamakan semula jadual terlebih dahulu (seperti menambah _to_be_deleted). Kemudian, amati untuk tempoh masa untuk memastikan tiada kesan ke atas perniagaan sebelum memadamkan jadual.

③ Tetapkan tunggu sedia replikasi tertunda

Ringkasnya, replikasi tertunda adalah untuk menetapkan masa tunda tetap, seperti 1 jam, supaya budak Perpustakaan adalah satu jam di belakang perpustakaan utama. Dalam masa 1 jam selepas operasi pemadaman tidak sengaja berlaku, laksanakan stop slave pada pangkalan data siap sedia ini, dan kemudian gunakan kaedah yang diperkenalkan sebelum ini untuk melangkau perintah operasi tidak sengaja untuk memulihkan data yang diperlukan. Di sini, melalui perintah CHANGE MASTER TO MASTER_DELAY = N, anda boleh menentukan bahawa pangkalan data siap sedia akan terus mengalami kelewatan N saat daripada pangkalan data utama. Sebagai contoh, tetapkan N kepada 3600, yang mewakili 1 jam.

Selain itu, replikasi tertunda juga boleh digunakan untuk menyelesaikan masalah berikut:

1. digunakan untuk melakukan 延遲測試, seperti pengasingan pangkalan data membaca dan menulis, menggunakan pangkalan data hamba sebagai Membaca perpustakaan, jika anda ingin tahu apa yang akan berlaku apabila data ditangguhkan, anda boleh menggunakan ciri ini untuk mensimulasikan kelewatan.

2. digunakan untuk 老數(shù)據(jù)的查詢等需求 Contohnya, jika anda sering perlu menyemak nilai jadual atau medan pada hari tertentu, anda mungkin perlu memulihkan sandaran dan menyemaknya. . Jika terdapat kelewatan dari pangkalan data, contohnya Delay selama seminggu, maka keperluan serupa seperti ini dapat diselesaikan.

8.4 rm: Instance MySQL telah dipadam secara tidak sengaja

Untuk kluster MySQL dengan mekanisme ketersediaan tinggi, tidak perlu risau rm刪除數(shù)據(jù). Kerana jika hanya satu data nod dipadamkan, sistem HA akan memilih pangkalan data utama baharu untuk memastikan operasi normal keseluruhan kluster. Selepas kami memulihkan data pada nod ini, kami kemudian boleh menyambungkannya ke seluruh kluster.

Tetapi jika keseluruhan kluster dipadamkan secara berniat jahat, maka anda perlu mempertimbangkan sandaran bilik merentas mesin dan sandaran merentas bandar.

Pembelajaran yang disyorkan: tutorial video mysql

Atas ialah kandungan terperinci Fahami cara membuat sandaran dan memulihkan pangkalan data MySQL dalam satu artikel. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!