目錄

一、數(shù)據(jù)備份

1、概述

2、MySQLdump命令備份

1）備份單個數(shù)據(jù)庫中的所有表

2) 備份數(shù)據(jù)中某個或多個表

3) 備份所有數(shù)據(jù)庫

4）備份多個庫

5)?只備份一個表或多個表結(jié)構(gòu)

二、數(shù)據(jù)恢復

三、數(shù)據(jù)備份與恢復應(yīng)用

---

一、數(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 ??????????????備份結(jié)果多了創(chuàng)建庫和切換庫命令---這個便于數(shù)據(jù)恢復。
?? ??? ??? ?-d, --no-data? ? ? ? ? ? ? ? ? ? 只備份結(jié)構(gòu)，不備份數(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)?只備份一個表或多個表結(jié)構(gòu)

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ù)備份與恢復應(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)建一個備份文件的目錄

[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命令還原第二題導出的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)