使用 mysqldump 进行备份
1 ． Mysqldump
-u username -p database table1 table2 ...> backup.sql
2 ． Mysqldump -u username -p --databases database1 database2 ... > backup.sql 3 ． Mysqldump -u username -p --all-databases > backup.sql
使用 Mysqldump
-u username -p database table1 table2 ...> backup.sql
备份文件 中含有 ： drop table
create table ， lock tables
， insert into values ， unlock tables
但不含有 create database ，在使用 mysql 进行还原 数据时，需要 指定数据库名 即： mysql -u username -p database < backup.sql
-- Server version
5.5.15
/*!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 `city` --
DROP TABLE IF EXISTS `city`;
/*!40101 SET @saved_cs_client
= @@character_set_client */;
/*!40101 SET character_set_client = utf8 */; CREATE TABLE `city` (
`ID` int(11) NOT NULL AUTO_INCREMENT, `Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '', `District` char(20) NOT NULL DEFAULT '',
-----------------------------------------------------Page 1-----------------------------------------------------
`Population` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `city` --
LOCK TABLES `city` WRITE;
/*!40000 ALTER TABLE `city` DISABLE KEYS */;
INSERT
INTO
`city`
VALUES
(1,'Kabul','AFG','Kabol',1780000),(2,'Qandahar','AFG','Qandahar',237500),(3,'Herat','AFG','Herat', 186800),(4,'Mazar-e-Sharif','AFG','Balkh',127800),(5,'Amsterdam','NLD','Noord-Holland',731200) ,(6,'Rotterdam','NLD','Zuid-Holland',593321),(7,'Haag','NLD','Zuid-Holland',440900),(8,'Utrecht',' NLD','Utrecht',234323),(9,'Eindhoven','NLD','Noord-Brabant',201843)
注： 只有前半 部分，备份文 件不完整
_______________________________________________________________________________ _______________________________________________________________________________
使用 mysqldump -u username -p --database database1 database2 > backup.sql 使用此语 句备份多个数 据库，并且将 备份内容写到 同一个文件当 中。
备 份 文 件 中 包 含 ： create database if not exists database1 ， use
table ,lock tables,
database1,drop table ,create
Insert into values ,unlock tables
和
create database if not exists database2 ,use
database2 ,drop table,create table ,lock
tables,insert into values ,unlock tables 和
create
database3 、、、、、、、
在使用 mysql 进行还原 的时候，不需 要制定数据库 名。 即： mysql -u username -p < backup.sql
_______________________________________________________________________________ _______________________________________________________________________________ --
-- Current Database: `test` --
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `test`;
--
-- Table structure for table `city`
-----------------------------------------------------Page 2-----------------------------------------------------
--
DROP TABLE IF EXISTS `city`;
/*!40101 SET @saved_cs_client
= @@character_set_client */;
/*!40101 SET character_set_client = utf8 */; CREATE TABLE `city` (
`ID` int(11) NOT NULL AUTO_INCREMENT, `Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '', `District` char(20) NOT NULL DEFAULT '', `Population` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `city` --
LOCK TABLES `city` WRITE;
/*!40000 ALTER TABLE `city` DISABLE KEYS */;
INSERT
INTO
`city`
VALUES
(1,'Kabul','AFG','Kabol',1780000),(2,'Qandahar','AFG','Qandahar',237500),(3,'Herat','AFG','Herat', 186800),(4,'Mazar-e-Sharif','AFG','Balkh',127800),(5,'Amsterdam','NLD','Noord-Holland',731200) ,(6,'Rotterdam','NLD','Zuid-Holland',593321),(7,'Haag','NLD','Zuid-Holland',440900),(8,'Utrecht',' NLD','Utrecht',234323),(9,'Eindhoven','NLD','Noord-Brabant',201843),(10,'Tilburg','NLD','Noord- Brabant',193238),(11,'Groningen','NLD','Groningen',172701),(12,'Breda','NLD','Noord-Brabant',1 60398),(13,'Apeldoorn','NLD','Gelderland',153491),(14,'Nijmegen','NLD','Gelderland',152463),(1 5,'Enschede','NLD','Overijssel',149544),(16,'Haarlem','NLD','Noord-Holland',148772),(17,'Almere ','NLD','Flevoland',142465),(18,'Arnhem','NLD','Gelderland',138020),(19,'Zaanstad','NLD',
注： 只有前半 部分，备份文 件不完整
_______________________________________________________________________________ _______________________________________________________________________________
使用 mysqldump -u username -p --all-databases > backup.sql 所有的备 份数据备份到 同一个文本文 件中。 文本文件 中包含的语句 有：
Create database if
not exists database1 , drop table if exist table,create table ,lock tables ,insert
into values ,unlock tables. 、、、、、、、
使用 mysql 进行恢复 的时候，不需 要制定数据库 名。 即： mysql -u username -p < backup.sql
-----------------------------------------------------Page 3-----------------------------------------------------
_______________________________________________________________________________ _______________________________________________________________________________ --
-- Host: localhost
Database:
-- ------------------------------------------------------
-- Server version
5.5.15
/*!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: `mydb` --
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mydb` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `mydb`;
--
-- Table structure for table `db1` --
DROP TABLE IF EXISTS `db1`;
/*!40101 SET @saved_cs_client
= @@character_set_client */;
/*!40101 SET character_set_client = utf8 */; CREATE TABLE `db1` (
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `db1` --
-----------------------------------------------------Page 4-----------------------------------------------------
LOCK TABLES `db1` WRITE;
/*!40000 ALTER TABLE `db1` DISABLE KEYS */; INSERT INTO `db1` VALUES (1),(2);
/*!40000 ALTER TABLE `db1` ENABLE KEYS */; UNLOCK TABLES;
--
-- Current Database: `mysql` --
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `mysql`;
--
-- Table structure for table `columns_priv` --
DROP TABLE IF EXISTS `columns_priv`;
/*!40101 SET @saved_cs_client
= @@character_set_client */;
/*!40101 SET character_set_client = utf8 */; CREATE TABLE `columns_priv` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '', `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '', `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
`Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '', `Column_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,
`Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL
DEFAULT '',
PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`,`Column_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Column privileges';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `columns_priv` --
LOCK TABLES `columns_priv` WRITE;
/*!40000 ALTER TABLE `columns_priv` DISABLE KEYS */;
-----------------------------------------------------Page 5-----------------------------------------------------
/*!40000 ALTER TABLE `columns_priv` ENABLE KEYS */; UNLOCK TABLES;
--
-- Table structure for table `db` --
DROP TABLE IF EXISTS `db`;
/*!40101 SET @saved_cs_client
= @@character_set_client */;
/*!40101 SET character_set_client = utf8 */; CREATE TABLE `db` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '', `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '', `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
`Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', PRIMARY KEY (`Host`,`Db`,`User`), KEY `User` (`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Database privileges';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `db` --
LOCK TABLES `db` WRITE;
-----------------------------------------------------Page 6-----------------------------------------------------
/*!40000 ALTER TABLE `db` DISABLE KEYS */; /*!40000 ALTER TABLE `db` ENABLE KEYS */; UNLOCK TABLES;
--
-- Table structure for table `event` --
DROP TABLE IF EXISTS `event`;
/*!40101 SET @saved_cs_client
= @@character_set_client */;
/*!40101 SET character_set_client = utf8 */; CREATE TABLE `event` (
`db` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '', `name` char(64) NOT NULL DEFAULT '', `body` longblob NOT NULL,
`definer` char(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '', `execute_at` datetime DEFAULT NULL, `interval_value` int(11) DEFAULT NULL, `interval_field`
enum('YEAR','QUARTER','MONTH','DAY','HOUR','MINUTE','WEEK','SECOND','MICROSEC OND','YEAR_MONTH','DAY_HOUR','DAY_MINUTE','DAY_SECOND','HOUR_MINUTE','H OUR_SECOND','MINUTE_SECOND','DAY_MICROSECOND','HOUR_MICROSECOND','MI NUTE_MICROSECOND','SECOND_MICROSECOND') DEFAULT NULL,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `last_executed` datetime DEFAULT NULL, `starts` datetime DEFAULT NULL, `ends` datetime DEFAULT NULL,
`status` enum('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL DEFAULT
'ENABLED',
`on_completion` enum('DROP','PRESERVE') NOT NULL DEFAULT 'DROP', `sql_mode`
set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USE D','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','P OSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTI ONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZER O','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZER O','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGIN E_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') NOT NULL DEFAULT '', `comment` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '', `originator` int(10) unsigned NOT NULL,
`time_zone` char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM',
-----------------------------------------------------Page 7-----------------------------------------------------
`character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT
NULL,
`collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT
NULL,
`db_collation` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `body_utf8` longblob,
PRIMARY KEY (`db`,`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Events'; /*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `func` --
DROP TABLE IF EXISTS `func`;
/*!40101 SET @saved_cs_client
= @@character_set_client */;
/*!40101 SET character_set_client = utf8 */; CREATE TABLE `func` (
`name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '', `ret` tinyint(1) NOT NULL DEFAULT '0',
`dl` char(128) COLLATE utf8_bin NOT NULL DEFAULT '',
`type` enum('function','aggregate') CHARACTER SET utf8 NOT NULL, PRIMARY KEY (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='User defined functions';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `func` --
LOCK TABLES `func` WRITE;
/*!40000 ALTER TABLE `func` DISABLE KEYS */; /*!40000 ALTER TABLE `func` ENABLE KEYS */; UNLOCK TABLES;
--
-- Table structure for table `help_category` --
DROP TABLE IF EXISTS `help_category`;
/*!40101 SET @saved_cs_client
= @@character_set_client */;
/*!40101 SET character_set_client = utf8 */; CREATE TABLE `help_category` (
-----------------------------------------------------Page 8-----------------------------------------------------
`help_category_id` smallint(5) unsigned NOT NULL, `name` char(64) NOT NULL,
`parent_category_id` smallint(5) unsigned DEFAULT NULL, `url` char(128) NOT NULL,
PRIMARY KEY (`help_category_id`), UNIQUE KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='help categories'; /*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `help_category` --
LOCK TABLES `help_category` WRITE;
/*!40000 ALTER TABLE `help_category` DISABLE KEYS */;
INSERT
INTO
`help_category`
VALUES
(1,'Geographic',0,''),(2,'Polygon
properties',33,''),(3,'WKT',33,''),(4,'Numeric
Functions',37,''),(5,'Plugins',34,''),(6,'MBR',33,''),(7,'Control functions',37,''),(8,'Transactions',34,''),(9,'Help
flow Metadata',34,''),(10,'Account
Management',34,''),(11,'Point properties',33,''),(14,'Logical
properties',33,''),(12,'Encryption operators',37,''),(15,'Miscellaneous
Functions',37,''),(13,'LineString Functions',37,''),(16,'Functions
and Modifiers for Use with GROUP BY',34,''),(17,'Information Functions',37,''),(18,'Comparison
operators',37,''),(19,'Bit Functions',34,''),(22,'Data
Functions',37,''),(20,'Table Types',34,''),(23,'Compound
Maintenance',34,''),(21,'User-Defined
Statements',34,''),(24,'Geometry
constructors',33,''),(25,'GeometryCollection
properties',1,''),(26,'Administration',34,''),(27,'Data
Manipulation',34,''),(28,'Utility',34,''),(29,'Language
Structure',34,''),(30,'Geometry
relations',33,''),(31,'Date
and
Time
Functions',37,''),(32,'WKB',33,''),(33,'Geographic
Features',34,''),(34,'Contents',0,''),(35,'Geometry
Functions',37,''),(37,'Functions',34,''),(38,'Data Definition',34,''); /*!40000 ALTER TABLE `help_category` ENABLE KEYS */; UNLOCK TABLES;
--
-- Table structure for table `help_keyword` --
DROP TABLE IF EXISTS `help_keyword`;
properties',33,''),(36,'String
/*!40101 SET @saved_cs_client
= @@character_set_client */;
/*!40101 SET character_set_client = utf8 */; CREATE TABLE `help_keyword` (
`help_keyword_id` int(10) unsigned NOT NULL, `name` char(64) NOT NULL,
PRIMARY KEY (`help_keyword_id`), UNIQUE KEY `name` (`name`)
-----------------------------------------------------Page 9-----------------------------------------------------
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='help keywords'; /*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `help_keyword` --
LOCK TABLES `help_keyword` WRITE;
/*!40000 ALTER TABLE `help_keyword` DISABLE KEYS */;
INSERT
INTO
`help_keyword`
VALUES
(0,'JOIN'),(1,'HOST'),(2,'REPEAT'),(3,'SERIALIZABLE'),(4,'REPLACE'),(5,'AT'),(6,'SCHEDUL E'),(7,'RETURNS'),(8,'STARTS'),(9,'MASTER_SSL_CA'),(10,'NCHAR'),(11,'COLUMNS'),(12,' COMPLETION'),(13,'WORK'),(14,'DATETIME'),(15,'MODE'),(16,'OPEN'),(17,'INTEGER'),(18,' ESCAPE'),(19,'VALUE'),(20,'MASTER_SSL_VERIFY_SERVER_CERT'),(21,'SQL_BIG_RESU LT'),(22,'DROP'),(23,'GEOMETRYCOLLECTIONFROMWKB'),(24,'EVENTS'),(25,'MONTH'),( 26,'INFO'),(27,'PROFILES'),(28,'DUPLICATE'),(29,'REPLICATION'),(30,'UNLOCK'),(31,'INN ODB'),(32,'YEAR_MONTH'),(33,'SUBJECT'),(34,'PREPARE'),(35,'LOCK'),(36,'CHECK'),(37,'F ULL'),(38,'INT4'),(39,'BY'),(40,'NO'),(41,'MINUTE'),(42,'PARTITION'),(43,'DATA'),(44,'DAY'),( 45,'SHARE'),(46,'REAL'),(47,'SEPARATOR'),(48,'MESSAGE_TEXT'),(49,'MASTER_HEARTB EAT_PERIOD'),(50,'DELETE'),(51,'ON'),(52,'COLUMN_NAME'),(53,'CONNECTION'),(54,'CL OSE'),(55,'X509'),(56,'USE'),(57,'SUBCLASS_ORIGIN'),(58,'WHERE'
注： 只有前半 部分，备份文 件不完整
转载于:https://blog.51cto.com/lwgarmstrong/674947
扫一扫
4021
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
