mysqldump用法尚方宝剑

(2012-03-23 19:04) 一键转载

http://blog.chinaunix.net/uid-20682026-id-3145889.html

mysqldump备份尚方宝剑
mysqldump --database test > test.sql
如果里面有表painting,poll_vote和person，那么备份一个表的格式如下：
--
-- Table structure for table `painting`
--
DROP TABLE IF EXISTS `painting`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `painting` (
  `a_id` int(10) unsigned NOT NULL,
  `p_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `state` varchar(2) NOT NULL,
  `price` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`p_id`),
  KEY `a_id` (`a_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;

--
-- Dumping data for table `painting`
--

LOCK TABLES `painting` WRITE;
/*!40000 ALTER TABLE `painting` DISABLE KEYS */;
INSERT INTO `painting` VALUES (1,1,'The Last Supper','IN',34),(1,2,'The Mona Lisa','MI',87),(3,3,'Starry Night','KY',48),(3,4,'The P
otato Eaters','KY',67),(3,5,'The Rocks','IA',33),(5,6,'Les Deux Soeurs','NE',64);
/*!40000 ALTER TABLE `painting` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `person`
--

DROP TABLE IF EXISTS `person`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `person` (
  `last_name` char(20) NOT NULL,
  `first_name` char(20) NOT NULL,
  `address` char(40) DEFAULT NULL,
  PRIMARY KEY (`last_name`,`first_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;

--
-- Dumping data for table `person`
--

LOCK TABLES `person` WRITE;
/*!40000 ALTER TABLE `person` DISABLE KEYS */;
INSERT INTO `person` VALUES ('x1','y1',NULL),('x2','y2',NULL),('x3','y3','深圳南山');
/*!40000 ALTER TABLE `person` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `poll_vote`
--

DROP TABLE IF EXISTS `poll_vote`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `poll_vote` (
  `poll_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `candidate_id` int(10) unsigned NOT NULL DEFAULT '0',
  `vote_count` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`poll_id`,`candidate_id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;

--
-- Dumping data for table `poll_vote`
--

LOCK TABLES `poll_vote` WRITE;
/*!40000 ALTER TABLE `poll_vote` DISABLE KEYS */;
INSERT INTO `poll_vote` VALUES (14,2,13);
/*!40000 ALTER TABLE `poll_vote` ENABLE KEYS */;
UNLOCK TABLES;

--
--
--

1,现在不要备份test库的表poll_vote和person,如果有想忽略多个表，就写多个--ignore-table=(数据库名).(表名)
[root@mysql ~]# mysqldump -uroot -pcluster123 --database test --ignore-table=test.poll_vote --ignore-table=test.person > test.sql

2,如果你是NDB环境，你备份出来的数据会有create logfile group和create tablespace，需要使用参数--no-tablespaces/-y
默认情况下如果不添加参数--no-tablespaces，那么在备份文件中会出现如下：
--
-- Logfile group: lg_1
--

CREATE LOGFILE GROUP lg_1
  ADD UNDOFILE 'undo_1.log'
  UNDO_BUFFER_SIZE 2097152
  INITIAL_SIZE 16777216
  ENGINE=ndbcluster;
 
--
-- Tablespace: ts_1
--

CREATE TABLESPACE ts_1
  ADD DATAFILE 'data_1.data'
  USE LOGFILE GROUP lg_1
  EXTENT_SIZE 1048576
  INITIAL_SIZE 209715200
  ENGINE=ndbcluster;
 
 
3,参数-R, --routines，默认情况下不备份数据库的存储过程和函数，除非添加该参数。
4,参数--triggers，默认情况下不备份数据的触发器，除非添加该参数。
5,参数-n, --no-create-db，默认情况下 'CREATE DATABASE /*!32312 IF NOT EXISTS*/ db_name /*!40100 DEFAULT CHARACTER SET utf8 */;'将会出现在数据库备份中，添加该参数就不会出现备份中。当有选项--databases 或者 --all-databases时，才会有上述创建数据库语句。
6,参数-t, --no-create-info，默认情况下会有创建表结构的语句，添加该参数后将不会有表创建信息。
7,参数-E, --events，默认情况下部分事件，需要指定该参数。
8,参数--master-data 设置为1，将会打印CHANGE MASTER命令，如果设置为2将会把CHANGE MASTER命令注释后打印出来。
9,--complete-insert, -c
INSERT语句包含表的列名
10,--ignore-table=[db_name1].[tbl_name1],[db_name12].[tbl_name1]
指定的表数据不备份，必须指定数据库和表名；想忽略多个表就按照[数据库名].[表名]写;，也可以指定不需备份的视图。


-- master-data 设置为1
--
-- Position to start replication or point-in-time recovery from
--

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000019', MASTER_LOG_POS=23474;

-- master-data 设置为2
--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000019', MASTER_LOG_POS=23474;