• +86 400-9955-698
  • support@vinchin.com
logo
关于我们

技术分享

技术分享 使用 mysqldump 进行备份

使用 mysqldump 进行备份

2020-04-10

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

注: 只有前半 部分,备份文 件不完整

  • 标签:
  • 容灾备份

您可能感兴趣的新闻 换一批

现在下载,可享30天免费试用

立即下载