mysqldump 备份全集

最新推荐文章于 2024-10-17 18:58:27 发布

cuifasang1563

最新推荐文章于 2024-10-17 18:58:27 发布

阅读量63

点赞数

文章标签：数据库

使用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

______________________________________________________________________________________________________________________________________________________________

-- 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 '',

`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 */;

注：只有前半部分，备份文件不完整

______________________________________________________________________________________________________________________________________________________________

使用 mysqldump -u username -p --database database1 database2 > backup.sql

使用此语句备份多个数据库，并且将备份内容写到同一个文件当中。

备份文件中包含：create database if not exists database1 ，use database1,drop table ,create table ,lock tables,

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

______________________________________________________________________________________________________________________________________________________________

-- Current Database: `test`

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `test`;

-- 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 '',

`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',160398),(13,'Apeldoorn','NLD','Gelderland',153491),(14,'Nijmegen','NLD','Gelderland',152463),(15,'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

______________________________________________________________________________________________________________________________________________________________

-- 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`

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 */;

/*!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;

/*!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','MICROSECOND','YEAR_MONTH','DAY_HOUR','DAY_MINUTE','DAY_SECOND','HOUR_MINUTE','HOUR_SECOND','MINUTE_SECOND','DAY_MICROSECOND','HOUR_MICROSECOND','MINUTE_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_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_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',

`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` (

`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 flow functions',37,''),(8,'Transactions',34,''),(9,'Help Metadata',34,''),(10,'Account Management',34,''),(11,'Point properties',33,''),(12,'Encryption Functions',37,''),(13,'LineString properties',33,''),(14,'Logical operators',37,''),(15,'Miscellaneous Functions',37,''),(16,'Functions and Modifiers for Use with GROUP BY',34,''),(17,'Information Functions',37,''),(18,'Comparison operators',37,''),(19,'Bit Functions',37,''),(20,'Table Maintenance',34,''),(21,'User-Defined Functions',34,''),(22,'Data Types',34,''),(23,'Compound 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 properties',33,''),(36,'String 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`;

/*!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`)

) 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,'SCHEDULE'),(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_RESULT'),(22,'DROP'),(23,'GEOMETRYCOLLECTIONFROMWKB'),(24,'EVENTS'),(25,'MONTH'),(26,'INFO'),(27,'PROFILES'),(28,'DUPLICATE'),(29,'REPLICATION'),(30,'UNLOCK'),(31,'INNODB'),(32,'YEAR_MONTH'),(33,'SUBJECT'),(34,'PREPARE'),(35,'LOCK'),(36,'CHECK'),(37,'FULL'),(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_HEARTBEAT_PERIOD'),(50,'DELETE'),(51,'ON'),(52,'COLUMN_NAME'),(53,'CONNECTION'),(54,'CLOSE'),(55,'X509'),(56,'USE'),(57,'SUBCLASS_ORIGIN'),(58,'WHERE'

注：只有前半部分，备份文件不完整

______________________________________________________________________________________________________________________________________________________________

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24003562/viewspace-707396/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24003562/viewspace-707396/