企业网站策划书制作,珠海cp网站建设,广饶网站建设,内网代理ip建设网站目录
一、数据备份
1、概述
2、MySQLdump命令备份
1#xff09;备份单个数据库中的所有表
2) 备份数据中某个或多个表
3) 备份所有数据库
4#xff09;备份多个库
5) 只备份一个表或多个表结构
二、数据恢复
三、数据备份与恢复应用 一、数据备份
1、概述
数据备…目录
一、数据备份
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密码 数据库名 /备份目录/文件名.sql
mysqldump -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 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命令备份数据库中的所有表
先在根目录下创建一个备份文件的目录
[rootmaster ~]# mkdir /backup利用mysqldump备份
[rootmaster ~]# mysqldump -uroot -pRedHat123 -B booksDB /backup/booksDB.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[rootmaster ~]# 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_CLIENTCHARACTER_SET_CLIENT */;
/*!40101 SET OLD_CHARACTER_SET_RESULTSCHARACTER_SET_RESULTS */;
/*!40101 SET OLD_COLLATION_CONNECTIONCOLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET OLD_TIME_ZONETIME_ZONE */;
/*!40103 SET TIME_ZONE00:00 */;
/*!40014 SET OLD_UNIQUE_CHECKSUNIQUE_CHECKS, UNIQUE_CHECKS0 */;
/*!40014 SET OLD_FOREIGN_KEY_CHECKSFOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS0 */;
/*!40101 SET OLD_SQL_MODESQL_MODE, SQL_MODENO_AUTO_VALUE_ON_ZERO */;
/*!40111 SET OLD_SQL_NOTESSQL_NOTES, SQL_NOTES0 */;--
-- 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)
) ENGINEInnoDB DEFAULT CHARSETlatin1;
/*!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)
) ENGINEInnoDB DEFAULT CHARSETlatin1;
/*!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)
) ENGINEInnoDB DEFAULT CHARSETlatin1;
/*!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_ZONEOLD_TIME_ZONE */;/*!40101 SET SQL_MODEOLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKSOLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKSOLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENTOLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTSOLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTIONOLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTESOLD_SQL_NOTES */;-- Dump completed on 2023-08-21 18:00:502、备份booksDB数据库中的books表
[rootmaster ~]# mysqldump -uroot -pRedHat123 booksDB books /backup/booksDB_books.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[rootmaster backup]# ls
booksDB_books.sql booksDB.sql
[rootmaster 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_CLIENTCHARACTER_SET_CLIENT */;
/*!40101 SET OLD_CHARACTER_SET_RESULTSCHARACTER_SET_RESULTS */;
/*!40101 SET OLD_COLLATION_CONNECTIONCOLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET OLD_TIME_ZONETIME_ZONE */;
/*!40103 SET TIME_ZONE00:00 */;
/*!40014 SET OLD_UNIQUE_CHECKSUNIQUE_CHECKS, UNIQUE_CHECKS0 */;
/*!40014 SET OLD_FOREIGN_KEY_CHECKSFOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS0 */;
/*!40101 SET OLD_SQL_MODESQL_MODE, SQL_MODENO_AUTO_VALUE_ON_ZERO */;
/*!40111 SET OLD_SQL_NOTESSQL_NOTES, SQL_NOTES0 */;--
-- 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)
) ENGINEInnoDB DEFAULT CHARSETlatin1;
/*!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_ZONEOLD_TIME_ZONE */;/*!40101 SET SQL_MODEOLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKSOLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKSOLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENTOLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTSOLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTIONOLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTESOLD_SQL_NOTES */;-- Dump completed on 2023-08-21 18:28:063、使用mysqldump备份booksDB和test数据库
[rootmaster ~]# mysqldump -uroot -pRedHat123 --databases booksDB test /backup/DB_booksDB_test.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.可以查看备份 4、使用mysqldump备份服务器中的所有数据库
[rootmaster ~]# mysqldump -uroot -pRedHat123 -A /backup/DB_all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.查看备份的数据内容较多 5、使用mysql命令还原第二题导出的books表
把传在一个全新的主机上
[rootmaster ~]# 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)先查看该数据库里面是没有表 再进行备份
[rootmaster2 ~]# mysql -uroot -pRoot123;MySQL DB1 /backup/booksDB_books.sql
mysql: [Warning] Using a password on the command line interface can be insecure.再查看你备份后的数据库 6、进入数据库使用source命令还原第二题导出的book表。
同样要先创建一个数据库
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)再进行恢复数据
#先切换到要恢复的数据库中,再用下面的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)
