今天具體做了一下MySQLdump的實驗
---------------------------MySQLdump備份------------------------------------
database: test;
tables: sms, email
--------------------------------------------------------------------------
備份整個庫:# mydqldump -uroot -p test>/mnt/zbtest.sql
-----------------查看MySQLdump備份文件------------------------------------
可以看到基本上分為兩部分，第一部分是建表語句，第二部分是插入語句；
# vi /mnt/zbtest.sql
-- MySQL dump 10.11
--
-- Host: localhost Database: test
-- ------------------------------------------------------
-- Server version 5.0.85-community-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 */;--
-- Table structure for table `email`
--DROP TABLE IF EXISTS `email`;
/*!40101 SET @saved_cs_client = @@character_set_clIEnt */;
/*!40101 SET character_set_clIEnt = utf8 */;
CREATE TABLE `email` (
`cn` varchar(12) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_clIEnt */;--
-- Dumping data for table `email`
--LOCK TABLES `email` WRITE;
/*!40000 ALTER TABLE `email` DISABLE KEYS */;
@
@
INSERT INTO `email` VALUES ('xxxxxxxxxxx),('xxxxxxxxxxxx'),(),.........................省略.......................-------------------------------------mysqlump文件恢復------------------------------------# MySQL -u root -pchangyou.com zbdump</mnt/zbtest.sql
ERROR 1049 (42000): Unknown database 'zbdump'
########出錯原因，沒有此數據庫，應該先新建一個名為zbdump的DBMySQL> create database zbdump;
Query OK, 1 row affected (0.00 sec)
########恢復
# MySQL -u root -pchangyou.com zbdump</mnt/zbtest.sql#######查看恢復後的數據庫：
MySQL> use zbdump
Database changed
MySQL> show tables;
+------------------+
| Tables_in_zbdump |
+------------------+
| email |
| sms |
+------------------+
2 rows in set (0.00 sec)MySQL> show create table email;
+-------+-------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------+
| email | CREATE TABLE `email` (
`cn` varchar(12) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)MySQL> select count(*) from email
-> ;
+----------+
| count(*) |
+----------+
| 1600000 |
+----------+
1 row in set (1.02 sec)MySQL> select count(*) from sms;
+----------+
| count(*) |
+----------+
| 250000 |
+----------+
1 row in set (0.16 sec)
---------------------------------------其他實驗------------------------------------------1. 備份單個數據庫的單個表的結構和數據:
# MySQLdump -u root -p --tables zbdump email>/mnt/zbdmp_email.sql2. 導出單個表的表結構與數據:
# MySQLdump -u root -p -T/tmp zbdump sms
sms.sql裡面存儲表定義; sms.txt裡面存儲數據;
# ls -ltr
total 3192
-rw-r--r-- 1 root root 1267 Nov 3 19:47 sms.sql
-rw-rw-rw- 1 mysql MySQL 3250000 Nov 3 19:47 sms.txt
# vi sms.sql-- MySQL dump 10.11
--
-- Host: localhost Database: zbdump
-- ------------------------------------------------------
-- Server version 5.0.85-community-log
-- MySQL dump 10.11
--
-- Host: localhost Database: zbdump
-- ------------------------------------------------------
-- Server version 5.0.85-community-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' */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;--
-- Table structure for table `sms`
--DROP TABLE IF EXISTS `sms`;
/*!40101 SET @saved_cs_client = @@character_set_clIEnt */;
/*!40101 SET character_set_clIEnt = utf8 */;
CREATE TABLE `sms` (
`cn` varchar(12) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_clIEnt */;/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;3.單獨備份單個表的表結構:
# MySQLdump -uroot -p -d zbdump email>email_struct.sql
4.備份一個庫的多個表結構:
MySQLdump -u root -p -d zbdump sms email>/tmp/zbdump_struct.sql
恢復:MySQL -u root -p test</tmp/zbdump_struct.sql
