MySQL備份與恢復之熱備(3)。本站提示廣大學習愛好者:(MySQL備份與恢復之熱備(3))文章只能為提供參考,不一定能成為您想要的結果。以下是MySQL備份與恢復之熱備(3)正文
在上兩篇文章(MySQL備份與恢復之冷備,MySQL備份與恢復之真實情況應用冷備)中,我們提到了冷備和真實情況中應用冷備。那從這篇文章開端我們看下熱備。明顯熱備和冷備是兩個絕對的概念,冷備是把數據庫辦事,好比MySQL,Oracle停上去,然後應用拷貝、打包或許緊縮敕令對數據目次停止備份;那末我們很輕易想到熱備就是在MySQL或許其他數據庫辦事在運轉的情形下停止備份。然則,這裡存在一個成績,由於臨盆庫在運轉的情形下,有對該庫的讀寫,讀寫頻率有能夠高,也能夠低,不論頻率高下,總會就會形成備份出來的數據和臨盆庫中的數據紛歧致的情形。熱備這段時光,其別人弗成以操作是不實際的,由於你總弗成能終止用戶拜訪Web法式。要處理這個成績,可以采取指定備份戰略,好比哪一個時光段停止備份,備份哪些數據等等,總之,包管數據的完全性和分歧性,切記,備份重於一切!!!
熱備可以對多個庫停止備份,可以對單張表或許某幾張表停止備份。然則沒法同時備份多個庫多個表,只要離開備份。上面我們看下熱備的表示圖,並停止熱備模仿。
表示圖
熱備模仿
1、對單個庫停止備份
第一步,移除LVM快照。(假如沒有創立,疏忽此步)
[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
第二步,設置MySQL的暗碼
mysql> set password=password("123456"); Query OK, 0 rows affected (0.00 sec)
第三步,檢查MySQL能否啟動。由於是熱備,所以請求MySQL辦事啟動
[root@serv01 data]# /etc/init.d/mysqld status SUCCESS! MySQL running (2664)
第四步,導出單個數據庫
[root@serv01 data]# cd /databackup/ #實質是導出為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 #將輸入成果保留到文件中 [root@serv01 databackup]# mysqldump -uroot -p123456 --database larrydb > larrydb.sql
第五步,模仿數據喪失,進入MySQL,刪除數據庫
[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
第六步,導入數據
[root@serv01 databackup]# mysql -uroot -p123456 <larrydb.sql
第七步,登錄MySQL,檢查數據能否正常
[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)
對多個庫停止備份
第一步,檢查有哪些數據庫
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 | 魏國 | 洛陽 | +-----+---------+----------+ 6 rows in set (0.00 sec)
第二步,備份多個庫
[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
第三步,模仿數據喪失。
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)
第四步,恢單數據
[root@serv01 databackup]# mysql -uroot -p123456 < larrydb_game.sql
第五步,檢查數據能否正常
[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 | 魏國 | 洛陽 | +-----+---------+----------+ 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)
備份一切的庫
[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
第二步,模仿數據喪失
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)
第三步,檢查數據能否正常
[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 | 魏國 | 洛陽 | +-----+---------+----------+
經由過程這兩天關於MySQL熱備和冷備的進修,年夜家是否是對MySQL備份與恢復的懂得加倍深刻了,不論是冷備照樣熱備其目標都是分歧的包管數據的完全性和分歧性,切記,備份重於一切!!!
信任明天所學的常識在以後的進修任務中對年夜家有所贊助。