MySQL 数据备份和数据恢复

目录

一、数据备份

1、概述

2、MySQLdump命令备份

1）备份单个数据库中的所有表

2) 备份数据中某个或多个表

3) 备份所有数据库

4）备份多个库

5) 只备份一个表或多个表结构

二、数据恢复

三、数据备份与恢复应用

---

一、数据备份

1、概述

数据备份是数据库管理员非常重要的工作之一。系统意外崩溃或者硬件的损坏都可能导致数据库的丢失，因此MySQL数据管理员需要定期进行数据库备份，使得意外发生尽可能的减少损失。

2、MySQLdump命令备份

该备份方式是系统自己提供的一种备份方式，可以更具需求选择选项。

基本语法

mysqldump -u 用户名 -h 主机名 -p 密码 数据库名[ 表名] > 备份文件目录/文件名.sql

mysqldump 常用选项：
            --defaults-file=                 备份到默认配置文件
            -A, --all-databases          备份所有库
            -B, --databases               备份结果多了创建库和切换库命令---这个便于数据恢复。
            -d, --no-data                    只备份结构，不备份数据
            -R                                    可以备份存储过程和函数

可以用 mysqldump --help命令来查看其他选项

1）备份单个数据库中的所有表

mysqldump -u用户名 -p密码 数据库名 > /备份目录/文件名.sqlmysqldump -u用户名 -p密码 -B 数据库名 > /备份目录/文件名.sql --会备份库的创建和切换到这个库的命令

2) 备份数据中某个或多个表

        多个表空格间隔

mysqldump -u用户名 -p密码 库名 表名1 [表名2……] > /备份目录/[表名1|表名2|……].sql

3) 备份所有数据库

mysqldump -u用户名 -p密码 -A > /备份目录/文件名.sql

4）备份多个库

mysqldump -u用户名 -p密码 --databases 数据库1 [数据库2 ……] > /备份目录/文件.sql

5) 只备份一个表或多个表结构

mysqldump -u用户名 -p密码 -d 库名 表名1 [表名2……] > /备份目录/[表名1|表名2|……].sql

二、数据恢复

1）使用mysql命令恢复

mysql -u用户名 -p'密码' 数据库 < /选择备份数据的路径.sql文件

2）进入数据库，使用 source 加载备份文件恢复

需要创建数据，然后切换到该数据库。

mysql -u用户名 -p'密码' -e 'source /恢复文件的路径.sql文件'#方法2 #进入数据库，创建一个数据库，然后切换到创建的数据库，再执行下面命令source 文件路径

三、数据备份与恢复应用

素材

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 booksVALUES (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 authorbookVALUES (1001, 11033), (1002, 11035), (1003, 11072), (1004, 11028),(1011, 11078), (1012, 11026), (1012, 11041), (1014, 11069);

使用mysqldump命令备份数据库中的所有表

先在根目录下创建一个备份文件的目录

[root@master ~]# mkdir /backup

 利用mysqldump备份

[root@master ~]# mysqldump -uroot -pRedHat@123 -B booksDB > /backup/booksDB.sqlmysqldump: [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 version5.7.18;;;;;;;;;;---- Current Database: `booksDB`--CREATE DATABASE  `booksDB` ;USE `booksDB`;---- Table structure for table `authorbook`--DROP TABLE IF EXISTS `authorbook`;;;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;;---- Dumping data for table `authorbook`--LOCK TABLES `authorbook` WRITE;;INSERT INTO `authorbook` VALUES (1012,11026),(1004,11028),(1001,11033),(1002,11035),(1012,11041),(1014,11069),(1003,11072),(1011,11078);;UNLOCK TABLES;---- Table structure for table `authors`--DROP TABLE IF EXISTS `authors`;;;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;;---- Dumping data for table `authors`--LOCK TABLES `authors` WRITE;;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');;UNLOCK TABLES;---- Table structure for table `books`--DROP TABLE IF EXISTS `books`;;;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;;---- Dumping data for table `books`--LOCK TABLES `books` WRITE;;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);;UNLOCK TABLES;;;;;;;;;-- Dump completed on 2023-08-21 18:00:50

备份booksDB数据库中的books表

[root@master ~]# mysqldump -uroot -pRedHat@123 booksDB books > /backup/booksDB_books.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.[root@master backup]# lsbooksDB_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 version5.7.18;;;;;;;;;;---- Table structure for table `books`--DROP TABLE IF EXISTS `books`;;;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;;---- Dumping data for table `books`--LOCK TABLES `books` WRITE;;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);;UNLOCK TABLES;;;;;;;;;-- Dump completed on 2023-08-21 18:28:06

使用mysqldump备份booksDB和test数据库

[root@master ~]# mysqldump -uroot -pRedHat@123 --databases booksDB test > /backup/DB_booksDB_test.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.

可以查看备份

使用mysqldump备份服务器中的所有数据库

[root@master ~]# mysqldump -uroot -pRedHat@123 -A > /backup/DB_all.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.

查看备份的数据，内容较多 

使用mysql命令还原第二题导出的books表

把传在一个全新的主机上

[root@master ~]# scp 192.168.78.143:/backup/booksDB_books.sql /backup

在主机2上去查看

因为我们之前备份的时候没有选择备份数据库，创建一个数据库

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)

先查看该数据库里面是没有表

再进行备份

[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.

再查看你备份后的数据库

进入数据库使用source命令还原第二题导出的book表。

同样要先创建一个数据库

mysql> create database DB2;Query OK, 1 row affected (0.00 sec)mysql> use DB2;Database changedmysql> show tables;Empty set (0.00 sec)

再进行恢复数据

#先切换到要恢复的数据库中,再用下面的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: 0Query 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)

来源地址：https://blog.csdn.net/weixin_65685029/article/details/132413482