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;
阅读(6013) | 评论(0) | 转发(1) |