MySQL?數(shù)據(jù)備份和數(shù)據(jù)恢復(fù)的實(shí)現(xiàn)
更新時(shí)間：2023年08月24日 16:04:04 作者：埋頭苦干的小馮
數(shù)據(jù)恢復(fù)的過程包括將備份文件導(dǎo)入到數(shù)據(jù)庫中、重建索引、應(yīng)用日志等,本文主要介紹了MySQL數(shù)據(jù)備份和數(shù)據(jù)恢復(fù)的實(shí)現(xiàn),感興趣的可以了解一下
一、數(shù)據(jù)備份
1、概述
數(shù)據(jù)備份是數(shù)據(jù)庫管理員非常重要的工作之一。系統(tǒng)意外崩潰或者硬件的損壞都可能導(dǎo)致數(shù)據(jù)庫的丟失，因此MySQL數(shù)據(jù)管理員需要定期進(jìn)行數(shù)據(jù)庫備份，使得意外發(fā)生盡可能的減少損失。
2、MySQLdump命令備份
該備份方式是系統(tǒng)自己提供的一種備份方式，可以更具需求選擇選項(xiàng)。
基本語法
mysqldump -u 用戶名 -h 主機(jī)名 -p 密碼 數(shù)據(jù)庫名[ 表名] > 備份文件目錄/文件名.sql
mysqldump 常用選項(xiàng)：
- --defaults-file= 備份到默認(rèn)配置文件
- -A, --all-databases 備份所有庫
- -B, --databases 備份結(jié)果多了創(chuàng)建庫和切換庫命令---這個(gè)便于數(shù)據(jù)恢復(fù)。
- -d, --no-data 只備份結(jié)構(gòu)，不備份數(shù)據(jù)
- -R 可以備份存儲(chǔ)過程和函數(shù)
可以用 mysqldump --help命令來查看其他選項(xiàng)
1）備份單個(gè)數(shù)據(jù)庫中的所有表
mysqldump -u用戶名 -p密碼 數(shù)據(jù)庫名 > /備份目錄/文件名.sql mysqldump -u用戶名 -p密碼 -B 數(shù)據(jù)庫名 > /備份目錄/文件名.sql --會(huì)備份庫的創(chuàng)建和切換到這個(gè)庫的命令
2) 備份數(shù)據(jù)中某個(gè)或多個(gè)表
多個(gè)表空格間隔
mysqldump -u用戶名 -p密碼 庫名 表名1 [表名2……] > /備份目錄/[表名1|表名2|……].sql
3) 備份所有數(shù)據(jù)庫
mysqldump -u用戶名 -p密碼 -A > /備份目錄/文件名.sql
4）備份多個(gè)庫
mysqldump -u用戶名 -p密碼 --databases 數(shù)據(jù)庫1 [數(shù)據(jù)庫2 ……] > /備份目錄/文件.sql
5) 只備份一個(gè)表或多個(gè)表結(jié)構(gòu)
mysqldump -u用戶名 -p密碼 -d 庫名 表名1 [表名2……] > /備份目錄/[表名1|表名2|……].sql
二、數(shù)據(jù)恢復(fù)
1）使用mysql命令恢復(fù)
mysql -u用戶名 -p'密碼' 數(shù)據(jù)庫 < /選擇備份數(shù)據(jù)的路徑.sql文件
2）進(jìn)入數(shù)據(jù)庫，使用 source 加載備份文件恢復(fù)
需要?jiǎng)?chuàng)建數(shù)據(jù)，然后切換到該數(shù)據(jù)庫。
mysql -u用戶名 -p'密碼' -e 'source /恢復(fù)文件的路徑.sql文件' #方法2 #進(jìn)入數(shù)據(jù)庫，創(chuàng)建一個(gè)數(shù)據(jù)庫，然后切換到創(chuàng)建的數(shù)據(jù)庫，再執(zhí)行下面命令 source 文件路徑
三、數(shù)據(jù)備份與恢復(fù)應(yīng)用
素材
CREATE DATABASE booksDB; use booksDB; CREATE TABLE books ( bk_id INT NOT NULL PRIMARY KEY, bk_title VARCHAR(50) NOT NULL, copyright YEAR NOT NULL ); INSERT INTO books VALUES (11078, 'Learning MySQL', 2010), (11033, 'Study Html', 2011), (11035, 'How to use php', 2003), (11072, 'Teach youself javascript', 2005), (11028, 'Learing C++', 2005), (11069, 'MySQL professional', 2009), (11026, 'Guide to MySQL 5.5', 2008), (11041, 'Inside VC++', 2011); CREATE TABLE authors ( auth_id INT NOT NULL PRIMARY KEY, auth_name VARCHAR(20), auth_gender CHAR(1) ); INSERT INTO authors VALUES (1001, 'WriterX' ,'f'), (1002, 'WriterA' ,'f'), (1003, 'WriterB' ,'m'), (1004, 'WriterC' ,'f'), (1011, 'WriterD' ,'f'), (1012, 'WriterE' ,'m'), (1013, 'WriterF' ,'m'), (1014, 'WriterG' ,'f'), (1015, 'WriterH' ,'f'); CREATE TABLE authorbook ( auth_id INT NOT NULL, bk_id INT NOT NULL, PRIMARY KEY (auth_id, bk_id), FOREIGN KEY (auth_id) REFERENCES authors (auth_id), FOREIGN KEY (bk_id) REFERENCES books (bk_id) ); INSERT INTO authorbook VALUES (1001, 11033), (1002, 11035), (1003, 11072), (1004, 11028), (1011, 11078), (1012, 11026), (1012, 11041), (1014, 11069);
1、使用mysqldump命令備份數(shù)據(jù)庫中的所有表
先在根目錄下創(chuàng)建一個(gè)備份文件的目錄
[root@master ~]# mkdir /backup
利用mysqldump備份
[root@master ~]# mysqldump -uroot -pRedHat@123 -B booksDB > /backup/booksDB.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. [root@master ~]# cat /backup/booksDB.sql -- MySQL dump 10.13 Distrib 5.7.18, for Linux (x86_64) -- -- Host: localhost Database: booksDB -- ------------------------------------------------------ -- Server version 5.7.18 /*!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 */; /*!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: `booksDB` -- CREATE DATABASE /*!32312 IF NOT EXISTS*/ `booksDB` /*!40100 DEFAULT CHARACTER SET latin1 */; USE `booksDB`; -- -- Table structure for table `authorbook` -- DROP TABLE IF EXISTS `authorbook`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `authorbook` ( `auth_id` int(11) NOT NULL, `bk_id` int(11) NOT NULL, PRIMARY KEY (`auth_id`,`bk_id`), KEY `bk_id` (`bk_id`), CONSTRAINT `authorbook_ibfk_1` FOREIGN KEY (`auth_id`) REFERENCES `authors` (`auth_id`), CONSTRAINT `authorbook_ibfk_2` FOREIGN KEY (`bk_id`) REFERENCES `books` (`bk_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `authorbook` -- LOCK TABLES `authorbook` WRITE; /*!40000 ALTER TABLE `authorbook` DISABLE KEYS */; INSERT INTO `authorbook` VALUES (1012,11026),(1004,11028),(1001,11033),(1002,11035),(1012,11041),(1014,11069),(1003,11072),(1011,11078); /*!40000 ALTER TABLE `authorbook` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `authors` -- DROP TABLE IF EXISTS `authors`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `authors` ( `auth_id` int(11) NOT NULL, `auth_name` varchar(20) DEFAULT NULL, `auth_gender` char(1) DEFAULT NULL, PRIMARY KEY (`auth_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `authors` -- LOCK TABLES `authors` WRITE; /*!40000 ALTER TABLE `authors` DISABLE KEYS */; INSERT INTO `authors` VALUES (1001,'WriterX','f'),(1002,'WriterA','f'),(1003,'WriterB','m'),(1004,'WriterC','f'),(1011,'WriterD','f'),(1012,'WriterE','m'),(1013,'WriterF','m'),(1014,'WriterG','f'),(1015,'WriterH','f'); /*!40000 ALTER TABLE `authors` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `books` -- DROP TABLE IF EXISTS `books`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `books` ( `bk_id` int(11) NOT NULL, `bk_title` varchar(50) NOT NULL, `copyright` year(4) NOT NULL, PRIMARY KEY (`bk_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `books` -- LOCK TABLES `books` WRITE; /*!40000 ALTER TABLE `books` DISABLE KEYS */; INSERT INTO `books` VALUES (11026,'Guide to MySQL 5.5',2008),(11028,'Learing C++',2005),(11033,'Study Html',2011),(11035,'How to use php',2003),(11041,'Inside VC++',2011),(11069,'MySQL professional',2009),(11072,'Teach youself javascript',2005),(11078,'Learning MySQL',2010); /*!40000 ALTER TABLE `books` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!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 2023-08-21 18:00:50
2、備份booksDB數(shù)據(jù)庫中的books表
[root@master ~]# mysqldump -uroot -pRedHat@123 booksDB books > /backup/booksDB_books.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. [root@master backup]# ls booksDB_books.sql booksDB.sql [root@master backup]# cat booksDB_books.sql -- MySQL dump 10.13 Distrib 5.7.18, for Linux (x86_64) -- -- Host: localhost Database: booksDB -- ------------------------------------------------------ -- Server version 5.7.18 /*!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 */; /*!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 `books` -- DROP TABLE IF EXISTS `books`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `books` ( `bk_id` int(11) NOT NULL, `bk_title` varchar(50) NOT NULL, `copyright` year(4) NOT NULL, PRIMARY KEY (`bk_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `books` -- LOCK TABLES `books` WRITE; /*!40000 ALTER TABLE `books` DISABLE KEYS */; INSERT INTO `books` VALUES (11026,'Guide to MySQL 5.5',2008),(11028,'Learing C++',2005),(11033,'Study Html',2011),(11035,'How to use php',2003),(11041,'Inside VC++',2011),(11069,'MySQL professional',2009),(11072,'Teach youself javascript',2005),(11078,'Learning MySQL',2010); /*!40000 ALTER TABLE `books` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!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 2023-08-21 18:28:06
3、使用mysqldump備份booksDB和test數(shù)據(jù)庫
[root@master ~]# mysqldump -uroot -pRedHat@123 --databases booksDB test > /backup/DB_booksDB_test.sql mysqldump: [Warning] Using a password on the command line interface can be insecure.
可以查看備份
4、使用mysqldump備份服務(wù)器中的所有數(shù)據(jù)庫
[root@master ~]# mysqldump -uroot -pRedHat@123 -A > /backup/DB_all.sql mysqldump: [Warning] Using a password on the command line interface can be insecure.
查看備份的數(shù)據(jù)，內(nèi)容較多
5、使用mysql命令還原第二題導(dǎo)出的books表
把傳在一個(gè)全新的主機(jī)上
[root@master ~]# scp 192.168.78.143:/backup/booksDB_books.sql /backup
在主機(jī)2上去查看
因?yàn)槲覀冎皞浞莸臅r(shí)候沒有選擇備份數(shù)據(jù)庫，創(chuàng)建一個(gè)數(shù)據(jù)庫
mysql> create database DB1; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | DB1 | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec)
先查看該數(shù)據(jù)庫里面是沒有表
再進(jìn)行備份
[root@master2 ~]# mysql -uroot -p'Root@123;MySQL' DB1 < /backup/booksDB_books.sql mysql: [Warning] Using a password on the command line interface can be insecure.
再查看你備份后的數(shù)據(jù)庫
6、進(jìn)入數(shù)據(jù)庫使用source命令還原第二題導(dǎo)出的book表。
同樣要先創(chuàng)建一個(gè)數(shù)據(jù)庫
mysql> create database DB2; Query OK, 1 row affected (0.00 sec) mysql> use DB2; Database changed mysql> show tables; Empty set (0.00 sec)
再進(jìn)行恢復(fù)數(shù)據(jù)
#先切換到要恢復(fù)的數(shù)據(jù)庫中,再用下面的source恢復(fù) mysql> source /backup/booksDB_books.sql; Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 8 rows affected (0.00 sec) Records: 8 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec)
再查看
mysql> select * from books; +-------+--------------------------+-----------+ | bk_id | bk_title | copyright | +-------+--------------------------+-----------+ | 11026 | Guide to MySQL 5.5 | 2008 | | 11028 | Learing C++ | 2005 | | 11033 | Study Html | 2011 | | 11035 | How to use php | 2003 | | 11041 | Inside VC++ | 2011 | | 11069 | MySQL professional | 2009 | | 11072 | Teach youself javascript | 2005 | | 11078 | Learning MySQL | 2010 | +-------+--------------------------+-----------+ 8 rows in set (0.00 sec)
到此這篇關(guān)于MySQL 數(shù)據(jù)備份和數(shù)據(jù)恢復(fù)的實(shí)現(xiàn)的文章就介紹到這了,更多相關(guān)MySQL 數(shù)據(jù)備份和數(shù)據(jù)恢復(fù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
MySql 8.0.16版本安裝提示已經(jīng)不使用“UTF8B3”而是使用“UTF8B4”問題
這篇文章主要介紹了MySql 8.0.16版本安裝提示已經(jīng)不使用“UTF8B3”而是使用“UTF8B4”問題 ,需要的朋友可以參考下2019-07-07
MySQL數(shù)據(jù)庫壓縮版本安裝與配置詳細(xì)教程
今天教各位小伙伴怎么安裝及配置Mysql數(shù)據(jù)庫,文中有非常詳細(xì)的圖文解說及代碼示例,對(duì)剛?cè)腴Tmysql的小伙伴們很有幫助,需要的朋友可以參考下2021-05-05
my.cnf參數(shù)配置實(shí)現(xiàn)InnoDB引擎性能優(yōu)化
目前來說：InnoDB是為Mysql處理巨大數(shù)據(jù)量時(shí)的最大性能設(shè)計(jì)。它的CPU效率可能是任何其它基于磁盤的關(guān)系數(shù)據(jù)庫引擎所不能匹敵的。在數(shù)據(jù)量大的網(wǎng)站或是應(yīng)用中Innodb是倍受青睞的。另一方面，在數(shù)據(jù)庫的復(fù)制操作中Innodb也是能保證master和slave數(shù)據(jù)一致有一定的作用。2017-05-05
Mysql數(shù)據(jù)庫支持的存儲(chǔ)引擎對(duì)比
這篇文章主要介紹了Mysql數(shù)據(jù)庫支持的各個(gè)存儲(chǔ)引擎之間的對(duì)比分析，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪2021-09-09
java實(shí)現(xiàn)mysql自動(dòng)更新創(chuàng)建時(shí)間與更新時(shí)間的兩種方式
在實(shí)際開發(fā)中,每條數(shù)據(jù)的創(chuàng)建時(shí)間和修改時(shí)間,盡量不需要應(yīng)用程序去記錄,而由數(shù)據(jù)庫獲取當(dāng)前時(shí)間自動(dòng)記錄創(chuàng)建時(shí)間,本文主要介紹了java實(shí)現(xiàn)mysql自動(dòng)更新創(chuàng)建時(shí)間與更新時(shí)間的兩種方式,感興趣的可以了解一下2024-01-01
這篇文章主要介紹了MySQL分區(qū)表的相關(guān)資料，幫助大家更好的理解和學(xué)習(xí)mysql，感興趣的朋友可以了解下2020-08-08
mysql修改sql_mode報(bào)錯(cuò)的解決
今天在Navicat中運(yùn)行sql語句創(chuàng)建數(shù)據(jù)表出現(xiàn)了錯(cuò)誤Err 1067。本文主要介紹了mysql修改sql_mode報(bào)錯(cuò)的解決，感興趣的可以了解一下2021-09-09
如何解決mysql的count()函數(shù)條件表達(dá)式不生效問題
該文章總結(jié)了SQL查詢中`count`函數(shù)統(tǒng)計(jì)錯(cuò)誤的原因,以及三種解決方法：使用`ornull`方法、`IF()`函數(shù)和`casewhen`表達(dá)式,當(dāng)不滿足條件時(shí),表達(dá)式的值為NULL2024-11-11
MySQL實(shí)現(xiàn)replace函數(shù)的幾種實(shí)用場景
這篇文章主要介紹了MySQL實(shí)現(xiàn)replace函數(shù)的幾種實(shí)用場景，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2021-02-02
SQL?Server攜程核心系統(tǒng)無感遷移到MySQL實(shí)戰(zhàn)
這篇文章主要介紹了SQL?Server攜程核心系統(tǒng)無感遷移到MySQL實(shí)戰(zhàn)，文章通過基于數(shù)據(jù)庫部署架構(gòu)鏡像構(gòu)建了訂單緩存統(tǒng)一管理熱點(diǎn)數(shù)據(jù)，解決各端差異，具體詳情需要的小伙伴可以參考下面文章詳細(xì)內(nèi)容2022-05-05