MySQL?數(shù)據(jù)備份和數(shù)據(jù)恢復的實現(xiàn)
更新時間：2023年08月24日 16:04:04 作者：埋頭苦干的小馮
數(shù)據(jù)恢復的過程包括將備份文件導入到數(shù)據(jù)庫中、重建索引、應用日志等,本文主要介紹了MySQL數(shù)據(jù)備份和數(shù)據(jù)恢復的實現(xiàn),感興趣的可以了解一下
一、數(shù)據(jù)備份
1、概述
數(shù)據(jù)備份是數(shù)據(jù)庫管理員非常重要的工作之一。系統(tǒng)意外崩潰或者硬件的損壞都可能導致數(shù)據(jù)庫的丟失，因此MySQL數(shù)據(jù)管理員需要定期進行數(shù)據(jù)庫備份，使得意外發(fā)生盡可能的減少損失。
2、MySQLdump命令備份
該備份方式是系統(tǒng)自己提供的一種備份方式，可以更具需求選擇選項。
基本語法
mysqldump -u 用戶名 -h 主機名 -p 密碼 數(shù)據(jù)庫名[ 表名] > 備份文件目錄/文件名.sql
mysqldump 常用選項：
- --defaults-file= 備份到默認配置文件
- -A, --all-databases 備份所有庫
- -B, --databases 備份結果多了創(chuàng)建庫和切換庫命令---這個便于數(shù)據(jù)恢復。
- -d, --no-data 只備份結構，不備份數(shù)據(jù)
- -R 可以備份存儲過程和函數(shù)
可以用 mysqldump --help命令來查看其他選項
1）備份單個數(shù)據(jù)庫中的所有表
mysqldump -u用戶名 -p密碼 數(shù)據(jù)庫名 > /備份目錄/文件名.sql mysqldump -u用戶名 -p密碼 -B 數(shù)據(jù)庫名 > /備份目錄/文件名.sql --會備份庫的創(chuàng)建和切換到這個庫的命令
2) 備份數(shù)據(jù)中某個或多個表
多個表空格間隔
mysqldump -u用戶名 -p密碼 庫名 表名1 [表名2……] > /備份目錄/[表名1|表名2|……].sql
3) 備份所有數(shù)據(jù)庫
mysqldump -u用戶名 -p密碼 -A > /備份目錄/文件名.sql
4）備份多個庫
mysqldump -u用戶名 -p密碼 --databases 數(shù)據(jù)庫1 [數(shù)據(jù)庫2 ……] > /備份目錄/文件.sql
5) 只備份一個表或多個表結構
mysqldump -u用戶名 -p密碼 -d 庫名 表名1 [表名2……] > /備份目錄/[表名1|表名2|……].sql
二、數(shù)據(jù)恢復
1）使用mysql命令恢復
mysql -u用戶名 -p'密碼' 數(shù)據(jù)庫 < /選擇備份數(shù)據(jù)的路徑.sql文件
2）進入數(shù)據(jù)庫，使用 source 加載備份文件恢復
需要創(chuàng)建數(shù)據(jù)，然后切換到該數(shù)據(jù)庫。
mysql -u用戶名 -p'密碼' -e 'source /恢復文件的路徑.sql文件' #方法2 #進入數(shù)據(jù)庫，創(chuàng)建一個數(shù)據(jù)庫，然后切換到創(chuàng)建的數(shù)據(jù)庫，再執(zhí)行下面命令 source 文件路徑
三、數(shù)據(jù)備份與恢復應用
素材
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)建一個備份文件的目錄
[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備份服務器中的所有數(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命令還原第二題導出的books表
把傳在一個全新的主機上
[root@master ~]# scp 192.168.78.143:/backup/booksDB_books.sql /backup
在主機2上去查看
因為我們之前備份的時候沒有選擇備份數(shù)據(jù)庫，創(chuàng)建一個數(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ù)庫里面是沒有表
再進行備份
[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、進入數(shù)據(jù)庫使用source命令還原第二題導出的book表。
同樣要先創(chuàng)建一個數(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)
再進行恢復數(shù)據(jù)
#先切換到要恢復的數(shù)據(jù)庫中,再用下面的source恢復 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)
到此這篇關于MySQL 數(shù)據(jù)備份和數(shù)據(jù)恢復的實現(xiàn)的文章就介紹到這了,更多相關MySQL 數(shù)據(jù)備份和數(shù)據(jù)恢復內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
數(shù)據(jù)庫查詢哪個對像里面包含什么字段方法語句
在本篇文章里小編給大家整理的關于數(shù)據(jù)庫查詢哪個對像里面包含什么字段方法語句有需要的朋友們可以學習下。2019-08-08
從MySQL的Innodb特性中我們知道，Inndob的表空間有共享和獨享的特點，如果是共享的。則默認會把表空間存放在一個文件中（ibdata1），當開啟獨享表空間參數(shù)Innodb_file_per_table時，會為每個Innodb表創(chuàng)建一個.ibd的文件。文章討論在獨享表空間卸載、裝載、遷移Innodb表的情況2013-11-11
通過mysql show processlist 命令檢查mysql鎖的方法
show processlist 命令非常實用，有時候mysql經(jīng)常跑到50%以上或更多，就需要用這個命令看哪個sql語句占用資源比較多，就知道哪個網(wǎng)站的程序問題了。2010-03-03
mysql sql語句性能調(diào)優(yōu)簡單實例
這篇文章主要介紹了 mysql sql語句性能調(diào)優(yōu)簡單實例的相關資料,需要的朋友可以參考下2017-06-06
這篇文章主要介紹了怎樣安全地關閉MySQL實例，文中講解非常細致，幫助大家更好的理解和學習MySQL，感興趣的朋友可以了解下2020-07-07
這篇文章主要介紹了Mysql使用sql實現(xiàn)對行數(shù)據(jù)求和問題，具有很好的參考價值，希望對大家有所幫助。2023-05-05
SQL判斷數(shù)據(jù)存不存在的正確做法(99%的人還在寫錯!)
業(yè)務代碼中,需要根據(jù)一個或多個條件,查詢是否存在記錄,不關心有多少條記錄,這篇文章主要介紹了SQL判斷數(shù)據(jù)存不存在的正確做法,文中通過代碼介紹的非常詳細,需要的朋友可以參考下2026-04-04
這篇文章主要給大家介紹了關于MySQL服務器 IO 100%的相關資料，文中通過示例代碼介紹的介紹非常詳細，對大家學習或者使用mysql具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2018-10-10
這篇文章主要介紹了mysql多表聯(lián)合查詢操作,結合實例形式分析了mysql多表聯(lián)合查詢的語法、功能、相關操作技巧與注意事項,需要的朋友可以參考下2019-04-04
這篇文章主要介紹了Linux下安裝mysql8.0.20的教程,本文通過圖文并茂的形式給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2020-05-05