您好,登錄后才能下訂單哦!
本文主要內(nèi)容
? 隨著辦公自動(dòng)化和電子商務(wù)的飛速發(fā)展,企業(yè)對(duì)信息系統(tǒng)的依賴(lài)性越來(lái)越高,數(shù)據(jù)庫(kù)作為信息系統(tǒng)的核心擔(dān)當(dāng)者主要的角色。數(shù)據(jù)庫(kù)備份,是在數(shù)據(jù)庫(kù)丟失的情況下,能及時(shí)恢復(fù)重要數(shù)據(jù),防止數(shù)據(jù)丟失的一種重要手段。一個(gè)合理的數(shù)據(jù)庫(kù)備份方案,應(yīng)該能夠在數(shù)據(jù)丟失時(shí),有效地恢復(fù)數(shù)據(jù),同時(shí)需要考慮技術(shù)實(shí)現(xiàn)難度和有效地利用資源。
? 在生產(chǎn)環(huán)境中,數(shù)據(jù)庫(kù)中數(shù)據(jù)的安全性是至關(guān)重要的,任何數(shù)據(jù)的丟失都可能產(chǎn)生嚴(yán)重的后果;造成數(shù)據(jù)丟失的原因如下:
? 因此,我們需要盡可能地將數(shù)據(jù)庫(kù)中的數(shù)據(jù)進(jìn)行各種備份,從而避免因?yàn)閿?shù)據(jù)丟失造成業(yè)務(wù)事故,給公司造成負(fù)面影響。
? 數(shù)據(jù)庫(kù)的備份可以從不同角度進(jìn)行分類(lèi),下面我們介紹一下數(shù)據(jù)庫(kù)的具體的分類(lèi)及其概念。
物理備份:對(duì)數(shù)據(jù)庫(kù)操作系統(tǒng)的物理文件(數(shù)據(jù)文件、日志文件等)的備份
物理備份又可以分為脫機(jī)備份(冷備份)和聯(lián)機(jī)備份(熱備份)
? 冷備份:是在關(guān)閉數(shù)據(jù)庫(kù)的時(shí)候進(jìn)行的;
? 熱備份:數(shù)據(jù)庫(kù)處于運(yùn)行狀態(tài),這種備份方法依賴(lài)于數(shù)據(jù)庫(kù)的日志文件
邏輯備份:對(duì)數(shù)據(jù)庫(kù)邏輯組件(如表等數(shù)據(jù)庫(kù)對(duì)象)的備份
我們使用一個(gè)比較形象的例子來(lái)說(shuō)明這三者的區(qū)別:
備份方式 | 完全備份 | 差異備份 | 增量備份 |
---|---|---|---|
完全備份時(shí)的狀態(tài) | table1,table2 | table1,table2 | table1,table2 |
第一次添加內(nèi)容 | 創(chuàng)建table3 | 創(chuàng)建table3 | 創(chuàng)建table3 |
備份內(nèi)容 | 備份table1,2,3 | 備份table3 | 備份table3 |
第二次添加內(nèi)容 | 創(chuàng)建table4 | 創(chuàng)建table4 | 創(chuàng)建table4 |
備份內(nèi)容 | 備份table1,2,3,4 | 備份table3,4 | 備份table4 |
? 可以這樣歸納:完全備份每次備份的是所有數(shù)據(jù)備份一次,差異備份的參考對(duì)象為第一次完全備份,而增量備份的參考對(duì)象為最近(相對(duì)之前的備份是最后一次)的一次備份。
? MySQL的備份方式主要有完全備份與增量備份。而完全備份是增量備份的基礎(chǔ)。所以,生產(chǎn)環(huán)境中,這兩種方式都會(huì)使用,需要制定合理高效的方案達(dá)到備份數(shù)據(jù)的目的。
? MySQL數(shù)據(jù)庫(kù)的備份可以采用兩種方式,因?yàn)閿?shù)據(jù)庫(kù)實(shí)際上就是文件,可以直接打包數(shù)據(jù)庫(kù)文件夾,或者使用專(zhuān)門(mén)的備份工具mysqldump進(jìn)行備份操作。
(1)安裝xz壓縮格式工具,該格式的壓縮率較大。
[root@localhost ~]# yum install -y xz
已加載插件:fastestmirror, langpacks
base | 3.6 kB 00:00
extras | 2.9 kB 00:00
updates | 2.9 kB 00:00
Loading mirror speeds from cached hostfile
* base: mirrors.aliyun.com
* extras: mirrors.aliyun.com
* updates: mirrors.aliyun.com
軟件包 xz-5.2.2-1.el7.x86_64 已安裝并且是最新版本
無(wú)須任何處理
(2)對(duì)數(shù)據(jù)庫(kù)中的數(shù)據(jù)庫(kù)文件打包操作
[root@localhost opt]# tar Pjcf /opt/data-$(date +%F).tar.xz /usr/local/mysql/data/fruit /usr/local/mysql/data/student/
[root@localhost opt]# ls
data-2020-01-07.tar.xz mysql-5.7.17 rh
(3)對(duì)比占用的空間資源
[root@localhost opt]# ls -lh /usr/local/mysql/data/student/
總用量 112K
-rw-r-----. 1 mysql mysql 61 1月 7 14:11 db.opt
-rw-r-----. 1 mysql mysql 8.5K 1月 7 14:14 stu_info.frm
-rw-r-----. 1 mysql mysql 96K 1月 7 14:14 stu_info.ibd
[root@localhost opt]# ls -lh /usr/local/mysql/data/fruit/
總用量 148K
-rw-r-----. 1 mysql mysql 61 1月 6 16:37 db.opt
-rw-r-----. 1 mysql mysql 8.5K 1月 6 18:30 fruit_info.frm
-rw-r-----. 1 mysql mysql 129K 1月 7 12:04 fruit_info.ibd
[root@localhost opt]# ls -lh data-2020-01-07.tar.xz
-rw-r--r--. 1 root root 1.4K 1月 7 14:32 data-2020-01-07.tar.xz
(4)如果原目錄中數(shù)據(jù)丟失損壞,可以解壓到數(shù)據(jù)目錄下回復(fù)數(shù)據(jù)
[root@localhost opt]# cd /usr/local/mysql/data/
[root@localhost data]# ls
auto.cnf ibdata1 ibtmp1 student
fruit ib_logfile0 mysql sys
ib_buffer_pool ib_logfile1 performance_schema
[root@localhost data]# rm fruit/ -rf
[root@localhost data]# rm student/ -rf
[root@localhost data]# ls
auto.cnf ib_logfile0 mysql usr
ib_buffer_pool ib_logfile1 performance_schema
ibdata1 ibtmp1 sys
[root@localhost data]# tar Pjxf /opt/data-2020-01-07.tar.xz -C .
[root@localhost data]# ls
auto.cnf ibdata1 ibtmp1 student
fruit ib_logfile0 mysql sys
ib_buffer_pool ib_logfile1 performance_schema usr
? 我們知道使用打壓縮包的方法其實(shí)在實(shí)際情況中并不是一個(gè)非常好的選擇,因?yàn)檫@是備份數(shù)據(jù)庫(kù)中所有的內(nèi)容,而mysqldump工具可以更加靈活地控制備份的內(nèi)容,比如將特定數(shù)據(jù)庫(kù),特定表進(jìn)行備份。
? 首先我們有一個(gè)如下的數(shù)據(jù)庫(kù)系統(tǒng),其中有兩個(gè)自己創(chuàng)建的數(shù)據(jù)庫(kù):fruit student
其中fruit中有fruit_info表,student中有stu_info表
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| fruit |
| mysql |
| performance_schema |
| student |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql> use student ;
Database changed
mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| stu_info |
+-------------------+
1 row in set (0.00 sec)
mysql> use fruit ;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-----------------+
| Tables_in_fruit |
+-----------------+
| fruit_info |
+-----------------+
1 row in set (0.00 sec)
(1)使用mysqldump命令對(duì)某表進(jìn)行完全備份,命令格式如下:
mysqldump -uroot -p [選項(xiàng)] [數(shù)據(jù)庫(kù)名] [數(shù)據(jù)表名] > /備份路徑/備份文件名(要以.sql后綴名結(jié)尾)
實(shí)例:
[root@localhost ~]# mysqldump -uroot -p fruit fruit_info > /opt/fruit_bak_$(date +%F).sql
Enter password:
[root@localhost ~]# ls /opt/
data-2020-01-07.tar.xz fruit_bak_2020-01-07.sql mysql-5.7.17 rh
(2)使用mysqldump命令對(duì)單個(gè)數(shù)據(jù)庫(kù)進(jìn)行完全備份,命令格式如下:
mysqldump -uroot -p [選項(xiàng)] [數(shù)據(jù)庫(kù)名] > /備份路徑/備份文件名(要以.sql后綴名結(jié)尾)
實(shí)例:
[root@localhost ~]# mysqldump -uroot -p fruit > /opt/fruit_db_backup-$(date +%F).sql
Enter password:
[root@localhost ~]# ls /opt/
data-2020-01-07.tar.xz fruit_db_backup-2020-01-07.sql rh
fruit_bak_2020-01-07.sql mysql-5.7.17
(3)使用mysqldump命令對(duì)多個(gè)數(shù)據(jù)庫(kù)進(jìn)行完全備份,命令格式如下:
mysqldump -uroot -p [選項(xiàng)] --databases [數(shù)據(jù)庫(kù)名列表] > /備份路徑/備份文件名(要以.sql后綴名結(jié)尾)
實(shí)例:
[root@localhost ~]# mysqldump -uroot -p --databases fruit student > /opt/fruit_and_student_db_backup-$(date +%F).sql
Enter password:
[root@localhost ~]# ls /opt/
data-2020-01-07.tar.xz
fruit_and_student_db_backup-2020-01-07.sql
fruit_bak_2020-01-07.sql
fruit_db_backup-2020-01-07.sql
mysql-5.7.17
rh
(4)使用mysqldump命令對(duì)所有數(shù)據(jù)庫(kù)進(jìn)行完全備份,命令格式如下:
mysqldump -uroot -p [選項(xiàng)] --all-databases > /備份路徑/備份文件名(要以.sql后綴名結(jié)尾)
實(shí)例:
[root@localhost ~]# mysqldump -uroot -p --all-databases > /opt/all_db_backup-$(date +%F).sql
Enter password:
[root@localhost ~]# ls /opt/
all_db_backup-2020-01-07.sql
data-2020-01-07.tar.xz
fruit_and_student_db_backup-2020-01-07.sql
fruit_bak_2020-01-07.sql
fruit_db_backup-2020-01-07.sql
mysql-5.7.17
rh
(5)使用mysqldump命令直接備份表結(jié)構(gòu)或者整個(gè)數(shù)據(jù)表,命令格式如下:
mysqldump -uroot -p [-d] [數(shù)據(jù)庫(kù)名] [數(shù)據(jù)表名] > /備份路徑/備份文件名(要以.sql后綴名結(jié)尾)
實(shí)例:
[root@localhost ~]# mysqldump -uroot -p -d fruit fruit_info > /opt/table_structure.sql
Enter password:
[root@localhost ~]# ls /opt/
all_db_backup-2020-01-07.sql
data-2020-01-07.tar.xz
fruit_and_student_db_backup-2020-01-07.sql
fruit_bak_2020-01-07.sql
fruit_db_backup-2020-01-07.sql
mysql-5.7.17
rh
table_structure.sql
[root@localhost ~]# mysqldump -uroot -p fruit fruit_info > /opt/table_structure-$(date +%F).sql
Enter password:
[root@localhost ~]# ls /opt/
all_db_backup-2020-01-07.sql
data-2020-01-07.tar.xz
fruit_and_student_db_backup-2020-01-07.sql
fruit_bak_2020-01-07.sql
fruit_db_backup-2020-01-07.sql
mysql-5.7.17
rh
table_structure-2020-01-07.sql
table_structure.sql
我們來(lái)對(duì)比一下加了選項(xiàng)-d和不加的備份文件的內(nèi)容
有-d選項(xiàng):
-- Host: localhost Database: fruit
-- Server version 5.7.17
/!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 /;
fruit_info
DROP TABLE IF EXISTS fruit_info
;
/!40101 SET @saved_cs_client = @@character_set_client /;
/!40101 SET character_set_client = utf8 /;
CREATE TABLE fruit_info
(id
int(4) NOT NULL,price
decimal(3,2) NOT NULL,newtype
varchar(6) DEFAULT NULL,
UNIQUE KEY id_index_new
(id
),
UNIQUE KEY type_index
(newtype
),
KEY id_index
(id
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/!40101 SET character_set_client = @saved_cs_client /;
/!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 2020-01-07 14:59:25
沒(méi)有-d選項(xiàng):
-- Host: localhost Database: fruit
-- Server version 5.7.17
/!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 /;
fruit_info
DROP TABLE IF EXISTS fruit_info
;
/!40101 SET @saved_cs_client = @@character_set_client /;
/!40101 SET character_set_client = utf8 /;
CREATE TABLE fruit_info
(id
int(4) NOT NULL,price
decimal(3,2) NOT NULL,newtype
varchar(6) DEFAULT NULL,
UNIQUE KEY id_index_new
(id
),
UNIQUE KEY type_index
(newtype
),
KEY id_index
(id
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/!40101 SET character_set_client = @saved_cs_client /;
fruit_info
LOCK TABLES fruit_info
WRITE;
/!40000 ALTER TABLE fruit_info
DISABLE KEYS /;
INSERT INTO fruit_info
VALUES (1,2.50,'banana'),(2,5.50,'apple'),(3,6.00,'peach');
/!40000 ALTER TABLE fruit_info
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 2020-01-07 15:01:04
? 可以看出加了-d選項(xiàng)的只是將表的結(jié)構(gòu)進(jìn)行備份,而不加-d參數(shù)的就會(huì)將整個(gè)數(shù)據(jù)表進(jìn)行備份
? 上文介紹了數(shù)據(jù)庫(kù)完全備份的具體操作,那么當(dāng)數(shù)據(jù)出現(xiàn)錯(cuò)誤時(shí),可以使用以下幾種方式對(duì)其進(jìn)行恢復(fù)操作
當(dāng)需要恢復(fù)整庫(kù)的時(shí)候,可以使用source命令和mysql命令
命令格式:source 備份腳本路徑(絕對(duì)路徑)
實(shí)例:
利用上面?zhèn)浞輋ruit數(shù)據(jù)庫(kù)的例子,模擬刪除該庫(kù)并進(jìn)行恢復(fù)操作
方法:生成備份數(shù)據(jù)文件——》登錄數(shù)據(jù)庫(kù)——》刪除數(shù)據(jù)庫(kù)——》使用source命令恢復(fù)
具體操作:
[root@localhost data]# mysqldump -uroot -p student > /opt/student.sql
Enter password:
[root@localhost data]# ls /opt/
data-2020-01-07.tar.xz mysql-5.7.17 rh student.sql
mysql> drop database student;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| fruit |
| mysql |
| performance_schema |
| sys |
| usr |
+--------------------+
6 rows in set (0.00 sec)
mysql> create database student
-> ;
Query OK, 1 row affected (0.00 sec)
mysql> use student;
Database changed
mysql> source /opt/student.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
...#省略部分內(nèi)容
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> mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| stu_info |
+-------------------+
1 row in set (0.00 sec)
不需要登錄mysql數(shù)據(jù)庫(kù)系統(tǒng),可以使用mysql命令直接恢復(fù)整庫(kù)。
實(shí)例如下:
[root@localhost data]# mysqldump -uroot -p student > /opt/student.sql
Enter password:
[root@localhost data]# ls /opt/
data-2020-01-07.tar.xz rh student.sql
mysql-5.7.17 student1.sql
模擬數(shù)據(jù)庫(kù)丟失:
[root@localhost data]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 41
Server version: 5.7.17 Source distribution
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| fruit |
| mysql |
| performance_schema |
| student |
| sys |
| usr |
+--------------------+
7 rows in set (0.00 sec)
mysql> drop database student;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| fruit |
| mysql |
| performance_schema |
| sys |
| usr |
+--------------------+
6 rows in set (0.00 sec)
整庫(kù)恢復(fù):
[root@localhost data]# mysql -uroot -p < /opt/student.sql
Enter password:
ERROR 1046 (3D000) at line 22: No database selected
[root@localhost data]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 43
Server version: 5.7.17 Source distribution
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database student;
Query OK, 1 row affected (0.01 sec)
mysql> exit
Bye
[root@localhost data]# mysql -uroot -p student < /opt/student.sql
Enter password:
[root@localhost data]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 47
Server version: 5.7.17 Source distribution
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| fruit |
| mysql |
| performance_schema |
| student |
| sys |
| usr |
+--------------------+
7 rows in set (0.00 sec)
mysql> use student;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from stu_info;
+----+-------+---------+
| id | name | address |
+----+-------+---------+
| 1 | zhsan | bj |
| 2 | wawu | nj |
+----+-------+---------+
2 rows in set (0.00 sec)
? 在進(jìn)行整庫(kù)恢復(fù)時(shí),MySQL5.7版本中都需要先創(chuàng)建庫(kù)才可以使用source或者mysql命令進(jìn)行整庫(kù)恢復(fù)。
(1)創(chuàng)建恢復(fù)文件目錄存放備份表文件:
[root@localhost opt]# mkdir abc
[root@localhost opt]# cd -
/usr/local/mysql/data
[root@localhost data]# ls
auto.cnf ibdata1 ibtmp1 student
fruit ib_logfile0 mysql sys
ib_buffer_pool ib_logfile1 performance_schema usr
[root@localhost data]# mysqldump -uroot -p student stu_info > /opt/abc/table.sql
Enter password:
[root@localhost data]# ls /opt/abc/
table.sql
(2)查看數(shù)據(jù)庫(kù)數(shù)據(jù)表中的內(nèi)容
[root@localhost data]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 52
Server version: 5.7.17 Source distribution
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use student;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| stu_info |
+-------------------+
1 row in set (0.00 sec)
mysql> select * from stu_info;
+----+-------+---------+
| id | name | address |
+----+-------+---------+
| 1 | zhsan | bj |
| 2 | wawu | nj |
+----+-------+---------+
2 rows in set (0.00 sec)
(3)模擬表文件數(shù)據(jù)丟失
mysql> drop table stu_info;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
Empty set (0.00 sec)
(4)source命令恢復(fù)數(shù)據(jù)表
mysql> source /opt/abc/table.sql;
Query OK, 0 rows affected (0.00 sec)
...#省略部分內(nèi)容
Query OK, 0 rows affected (0.00 sec
mysql> select * from stu_info;
+----+-------+---------+
| id | name | address |
+----+-------+---------+
| 1 | zhsan | bj |
| 2 | wawu | nj |
+----+-------+---------+
2 rows in set (0.00 sec)
? 使用mysql命令恢復(fù)表的操作的時(shí)候,當(dāng)備份文件中只包含表的備份,而不包括創(chuàng)建庫(kù)的語(yǔ)句時(shí),必須指定庫(kù)名,其目標(biāo)庫(kù)存在。
? 實(shí)例如下:前面的步驟與source命令的演示類(lèi)似,就不過(guò)多說(shuō)明了
[root@localhost data]# mysqldump -uroot -p student stu_info > /opt/abc/table1.sql
Enter password:
[root@localhost data]# ls /opt/abc/
table1.sql table.sql
[root@localhost data]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 57
Server version: 5.7.17 Source distribution
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use student;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from stu_info;
+----+-------+---------+
| id | name | address |
+----+-------+---------+
| 1 | zhsan | bj |
| 2 | wawu | nj |
+----+-------+---------+
2 rows in set (0.00 sec)
mysql> drop table stu_info;
Query OK, 0 rows affected (0.01 sec)
mysql> exit
Bye
恢復(fù)表操作:
[root@localhost data]# mysql -uroot -p student < /opt/abc/table1.sql
Enter password:
[root@localhost data]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 59
Server version: 5.7.17 Source distribution
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use student;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from stu_info;
+----+-------+---------+
| id | name | address |
+----+-------+---------+
| 1 | zhsan | bj |
| 2 | wawu | nj |
+----+-------+---------+
2 rows in set (0.00 sec)
? 在恢復(fù)表時(shí)命令的操作基本一致,只不過(guò)使用環(huán)境不同而已,而恢復(fù)表和恢復(fù)庫(kù)的區(qū)別就在于恢復(fù)庫(kù)需要先創(chuàng)建使用庫(kù),而恢復(fù)數(shù)據(jù)表則不需要,這是因?yàn)閭浞菸募膬?nèi)容決定的。
? MySQL需要定期實(shí)施備份,制定合適的備份計(jì)劃或者策略,并且嚴(yán)格遵守。除了進(jìn)行完全備份,開(kāi)啟MySQL服務(wù)器的日志功能也是重中之重,完全備份配合日志,可以對(duì)MySQL進(jìn)行最大化還原。
? 備份文件的名字需要使用統(tǒng)一和易理解的名字,推薦使用庫(kù)名配合時(shí)間的命名方法,這樣方便他人和自己使用。
? 本文主要講述數(shù)據(jù)庫(kù)備份的分類(lèi)及其制作備份和恢復(fù)數(shù)據(jù)的實(shí)操。數(shù)據(jù)庫(kù)備份,從物理與邏輯的角度,備份分為物理(冷熱備份)和邏輯備份;從數(shù)據(jù)庫(kù)的備份策略角度,備份可以分為完全備份、差異備份與增量備份。
? MySQL中有專(zhuān)門(mén)的mysqldump工具備份,生成的是SQL的腳本文件。而數(shù)據(jù)庫(kù)的恢復(fù)操作使用mysql或source命令。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。