MySQL備份與恢復(fù)之熱備（3）
更新時(shí)間：2015年08月12日 10:41:02 作者：Wentasy
熱備使用mysqldump命令進(jìn)行備份，此工具是MySQL內(nèi)置的備份和恢復(fù)工具，功能強(qiáng)大，它可以對(duì)整個(gè)庫(kù)進(jìn)行備份，可以對(duì)多個(gè)庫(kù)進(jìn)行備份，可以對(duì)單張表或者某幾張表進(jìn)行備份,需要了解的朋友可以參考下
在上兩篇文章（MySQL備份與恢復(fù)之冷備，MySQL備份與恢復(fù)之真實(shí)環(huán)境使用冷備）中，我們提到了冷備和真實(shí)環(huán)境中使用冷備。那從這篇文章開(kāi)始我們看下熱備。顯然熱備和冷備是兩個(gè)相對(duì)的概念，冷備是把數(shù)據(jù)庫(kù)服務(wù)，比如MySQL，Oracle停下來(lái)，然后使用拷貝、打包或者壓縮命令對(duì)數(shù)據(jù)目錄進(jìn)行備份；那么我們很容易想到熱備就是在MySQL或者其他數(shù)據(jù)庫(kù)服務(wù)在運(yùn)行的情況下進(jìn)行備份。但是，這里存在一個(gè)問(wèn)題，因?yàn)樯a(chǎn)庫(kù)在運(yùn)行的情況下，有對(duì)該庫(kù)的讀寫(xiě)，讀寫(xiě)頻率有可能高，也可能低，不管頻率高低，總會(huì)就會(huì)造成備份出來(lái)的數(shù)據(jù)和生產(chǎn)庫(kù)中的數(shù)據(jù)不一致的情況。熱備這段時(shí)間，其他人不可以操作是不現(xiàn)實(shí)的，因?yàn)槟憧偛豢赡芙K止用戶訪問(wèn)Web程序。要解決這個(gè)問(wèn)題，可以采用指定備份策略，比如哪個(gè)時(shí)間段進(jìn)行備份，備份哪些數(shù)據(jù)等等，總之，保證數(shù)據(jù)的完整性和一致性，切記，備份重于一切?。?！
熱備可以對(duì)多個(gè)庫(kù)進(jìn)行備份，可以對(duì)單張表或者某幾張表進(jìn)行備份。但是無(wú)法同時(shí)備份多個(gè)庫(kù)多個(gè)表，只有分開(kāi)備份。下面我們看下熱備的示意圖，并進(jìn)行熱備模擬。
示意圖
熱備模擬
1、對(duì)單個(gè)庫(kù)進(jìn)行備份
第一步，移除LVM快照。（如果沒(méi)有創(chuàng)建，忽略此步）
[root@serv01 data]# lvremove /dev/data/smydata Do you really want to remove active logical volume smydata? [y/n]: y Logical volume "smydata" successfully removed
第二步，設(shè)置MySQL的密碼
mysql> set password=password("123456");
Query OK, 0 rows affected (0.00 sec)
第三步，查看MySQL是否啟動(dòng)。因?yàn)槭菬醾?，所以要求MySQL服務(wù)啟動(dòng)
[root@serv01 data]# /etc/init.d/mysqld status SUCCESS! MySQL running (2664)
第四步，導(dǎo)出單個(gè)數(shù)據(jù)庫(kù)
[root@serv01 data]# cd /databackup/ #本質(zhì)是導(dǎo)出為SQL [root@serv01 databackup]# mysqldump -uroot -p123456 --database larrydb -- MySQL dump 10.13 Distrib 5.5.29, for Linux (x86_64) -- -- Host: localhost Database: larrydb -- ------------------------------------------------------ -- Server version 5.5.29-log /*!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: `larrydb` -- CREATE DATABASE /*!32312 IF NOT EXISTS*/ `larrydb` /*!40100 DEFAULT CHARACTER SET latin1 */; USE `larrydb`; -- -- Table structure for table `class` -- DROP TABLE IF EXISTS `class`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `class` ( `cid` int(11) DEFAULT NULL, `cname` varchar(30) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `class` -- LOCK TABLES `class` WRITE; /*!40000 ALTER TABLE `class` DISABLE KEYS */; INSERT INTO `class` VALUES (1,'linux'),(2,'oracle'); /*!40000 ALTER TABLE `class` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `stu` -- DROP TABLE IF EXISTS `stu`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `stu` ( `sid` int(11) DEFAULT NULL, `sname` varchar(30) DEFAULT NULL, `cid` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `stu` -- LOCK TABLES `stu` WRITE; /*!40000 ALTER TABLE `stu` DISABLE KEYS */; INSERT INTO `stu` VALUES (1,'larry01',1),(2,'larry02',2); /*!40000 ALTER TABLE `stu` 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 2013-09-10 18:56:06 #將輸出結(jié)果保存到文件中 [root@serv01 databackup]# mysqldump -uroot -p123456 --database larrydb > larrydb.sql
第五步，模擬數(shù)據(jù)丟失，進(jìn)入MySQL，刪除數(shù)據(jù)庫(kù)
[root@serv01 data]# mysql -uroot -p123456 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.5.29-log Source distribution Copyright (c) 2000, 2012, 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 | | crm | | game | | hello | | larrydb | | mnt | | mysql | | performance_schema | | test | +--------------------+ 9 rows in set (0.00 sec) mysql> drop database larrydb; Query OK, 2 rows affected (0.01 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | crm | | game | | hello | | mnt | | mysql | | performance_schema | | test | +--------------------+ 8 rows in set (0.00 sec) mysql> exit Bye
第六步，導(dǎo)入數(shù)據(jù)
[root@serv01 databackup]# mysql -uroot -p123456 <larrydb.sql
第七步，登錄MySQL，查看數(shù)據(jù)是否正常
[root@serv01 data]# mysql -uroot -p123456 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.5.29-log Source distribution Copyright (c) 2000, 2012, 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 database; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database' at line 1 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | crm | | game | | hello | | larrydb | | mnt | | mysql | | performance_schema | | test | +--------------------+ 9 rows in set (0.00 sec) mysql> use larrydb; Database changed mysql> select * from class; +------+--------+ | cid | cname | +------+--------+ | 1 | linux | | 2 | oracle | +------+--------+ 2 rows in set (0.00 sec) mysql> select * from stu; +------+---------+------+ | sid | sname | cid | +------+---------+------+ | 1 | larry01 | 1 | | 2 | larry02 | 2 | +------+---------+------+ 2 rows in set (0.00 sec)
對(duì)多個(gè)庫(kù)進(jìn)行備份
第一步，查看有哪些數(shù)據(jù)庫(kù)
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | crm | | game | | hello | | larrydb | | mnt | | mysql | | performance_schema | | test | +--------------------+ 9 rows in set (0.00 sec) mysql> use game; Database changed mysql> show tables; +----------------+ | Tables_in_game | +----------------+ | country | | fight | | hero | +----------------+ 3 rows in set (0.00 sec) mysql> select * from country; +-----+---------+----------+ | cno | cname | location | +-----+---------+----------+ | 10 | caowei | luoyang | | 20 | shuhan | chengdou | | 30 | sunwu | nanjing | | 40 | houhan | luoyang | | 50 | beisong | kaifeng | | 60 | 魏國(guó) | 洛陽(yáng) | +-----+---------+----------+ 6 rows in set (0.00 sec)
第二步，備份多個(gè)庫(kù)
[root@serv01 databackup]# mysqldump -uroot -p123456 --databases larrydb game > larrydb_game.sql [root@serv01 databackup]# ll larrydb_game.sql -rw-r--r--. 1 root root 6159 Sep 10 19:05 larrydb_game.sql
第三步，模擬數(shù)據(jù)丟失。
mysql> drop database game; Query OK, 3 rows affected (0.01 sec) mysql> drop database larrydb; Query OK, 2 rows affected (0.00 sec) mysql> use crm; Database changed mysql> show tables; +---------------+ | Tables_in_crm | +---------------+ | test | +---------------+ 1 row in set (0.00 sec) mysql> select * from test; Empty set (0.00 sec) mysql> drop database crm; Query OK, 1 row affected (0.00 sec)
第四步，恢復(fù)數(shù)據(jù)
[root@serv01 databackup]# mysql -uroot -p123456 < larrydb_game.sql
第五步，查看數(shù)據(jù)是否正常
[root@serv01 data]# mysql -uroot -p123456 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 5.5.29-log Source distribution Copyright (c) 2000, 2012, 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 | | game | | hello | | larrydb | | mnt | | mysql | | performance_schema | | test | +--------------------+ 8 rows in set (0.00 sec) mysql> use game; Database changed mysql> select * from country; +-----+---------+----------+ | cno | cname | location | +-----+---------+----------+ | 10 | caowei | luoyang | | 20 | shuhan | chengdou | | 30 | sunwu | nanjing | | 40 | houhan | luoyang | | 50 | beisong | kaifeng | | 60 | 魏國(guó) | 洛陽(yáng) | +-----+---------+----------+ 6 rows in set (0.00 sec) mysql> use larrydb; Database changed mysql> select * from class; +------+--------+ | cid | cname | +------+--------+ | 1 | linux | | 2 | oracle | +------+--------+ 2 rows in set (0.00 sec)
備份所有的庫(kù)
[root@serv01 databackup]# mysqldump --help | grep all-database OR mysqldump [OPTIONS] --all-databases [OPTIONS] -A, --all-databases Dump all the databases. This will be same as --databases --databases= or --all-databases), the logs will be --all-databases or --databases is given. all-databases FALSE [root@serv01 databackup]# mysqldump -uroot -p123456 --all-databases > all_databases.sql [root@serv01 databackup]# ll all_databases.sql -h -rw-r--r--. 1 root root 506K Sep 10 19:16 all_databases.sql
備份某張表或者某幾張表
第一步，備份某張表和某幾張表
[root@serv01 databackup]# mysqldump game hero country -uroot -p123456 > game_hero_country.sql [root@serv01 databackup]# ll game_hero_country.sql -rw-r--r—. 1 root root 3955 Sep 10 19:11 game_hero_country.sql
第二步，模擬數(shù)據(jù)丟失
mysql> use game; Database changed mysql> show tables; +----------------+ | Tables_in_game | +----------------+ | country | | fight | | hero | +----------------+ 3 rows in set (0.00 sec) mysql> drop table hero; Query OK, 0 rows affected (0.00 sec) mysql> drop table country; Query OK, 0 rows affected (0.00 sec)
第三步，查看數(shù)據(jù)是否正常
[root@serv01 databackup]# mysql -uroot -p123456 < game_hero_country.sql ERROR 1046 (3D000) at line 22: No database selected [root@serv01 databackup]# mysql -uroot -p123456 --database game < game_hero_country.sql [root@serv01 databackup]# mysql -uroot -p123456 -e "select * from game.country" +-----+---------+----------+ | cno | cname | location | +-----+---------+----------+ | 10 | caowei | luoyang | | 20 | shuhan | chengdou | | 30 | sunwu | nanjing | | 40 | houhan | luoyang | | 50 | beisong | kaifeng | | 60 | 魏國(guó) | 洛陽(yáng) | +-----+---------+----------+
通過(guò)這兩天關(guān)于MySQL熱備和冷備的學(xué)習(xí)，大家是不是對(duì)MySQL備份與恢復(fù)的了解更加深入了，不管是冷備還是熱備其目的都是一致的保證數(shù)據(jù)的完整性和一致性，切記，備份重于一切！??！
相信今天所學(xué)的知識(shí)在之后的學(xué)習(xí)工作中對(duì)大家有所幫助。
相關(guān)文章
MySQL查看和修改字符編碼的實(shí)現(xiàn)方法
下面小編就為大家?guī)?lái)一篇MySQL查看和修改字符編碼的實(shí)現(xiàn)方法。小編覺(jué)得挺不錯(cuò)的，現(xiàn)在就分享給大家，也給大家做個(gè)參考。一起跟隨小編過(guò)來(lái)看看吧2016-11-11
MySQL不使用order by實(shí)現(xiàn)排名的三種思路總結(jié)
ORDER BY語(yǔ)句用于根據(jù)指定的列對(duì)結(jié)果集進(jìn)行排序,在日常開(kāi)發(fā)中也經(jīng)常會(huì)用到,但下面這篇文章主要給大家介紹了關(guān)于MySQL不使用order by實(shí)現(xiàn)排名的三種思路,需要的朋友可以參考下2021-06-06
這篇文章主要介紹了mysql索引(覆蓋索引,聯(lián)合索引,索引下推)，文章圍繞主題展開(kāi)詳細(xì)的內(nèi)容介紹，具有一定的參考價(jià)值，需要的小伙伴可以參考一下2022-08-08
MYSQL讀寫(xiě)性能測(cè)試的簡(jiǎn)單記錄
本文主要介紹了MYSQL讀寫(xiě)性能測(cè)試的簡(jiǎn)單記錄，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2022-08-08
下面小編就為大家?guī)?lái)一篇innodb引擎redo文件維護(hù)方法。小編覺(jué)得挺不錯(cuò)的，現(xiàn)在就分享給大家，也給大家做個(gè)參考。一起跟隨小編過(guò)來(lái)看看吧2017-03-03
這篇文章主要為大家詳細(xì)介紹了Linux下mysql 8.0安裝教程，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2018-09-09
詳解MySQL插入和查詢數(shù)據(jù)的相關(guān)命令及語(yǔ)句使用
這篇文章主要介紹了MySQL插入和查詢數(shù)據(jù)的相關(guān)命令及語(yǔ)句使用,包括相關(guān)的PHP腳本操作方法講解也很詳細(xì),需要的朋友可以參考下2015-11-11
在 MySQL 數(shù)據(jù)庫(kù)中,有時(shí)我們需要截取字段或字符串的一部分進(jìn)行查詢、展示或處理,本文將介紹 MySQL 中常用的字段截取和字符串截取方法,幫助你靈活處理數(shù)據(jù),需要的朋友可以參考下2024-01-01
Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids2011-05-05
這篇文章主要介紹了mysql視圖之創(chuàng)建可更新視圖的方法,結(jié)合實(shí)例形式分析了mysql可更新視圖的具體創(chuàng)建、使用方法及相關(guān)操作注意事項(xiàng),需要的朋友可以參考下2019-12-12