mysql dba体系进修（16）mysql的mysqldump备份

 这就是dump的结果

[root@test4 Desktop]# cat /tmp/test.sql
-- MySQL dump 10.13  Distrib 5.1.70, for unknown-linux-gnu (x86_64)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version   5.1.70-log
/*!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 */;
--
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE='mysqlbin.000166', MASTER_LOG_POS=798;
--
-- Current Database: `test`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `test`;
--
-- Table structure for table `tt`
--
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tt` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(12) DEFAULT NULL
);
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `tt`
--
INSERT INTO `tt` VALUES (1,'zz');
INSERT INTO `tt` VALUES (2,'yy');
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2013-09-05 20:15:38

由于我们dump的时候用了master-data参数，这个时候记录了日志位置和日志文件名

-- CHANGE MASTER TO MASTER_LOG_FILE='mysqlbin.000166', MASTER_LOG_POS=798;

实践之事务性一直备份（备份期间数据库可写）

mysql> use  test

mysql> create table tt(id int,name varchar(12)) engine=innodb ;

Query OK, 0 rows affected (0.11 sec)

mysql> insert into tt values(1,'zz');

Query OK, 1 row affected (0.01 sec)

mysql> insert into tt values(2,'yy');

Query OK, 1 row affected (0.00 sec)

[root@test4 Desktop]# mysqldump  --databases test  --skip-opt --quick --extended-insert=false  --single-transaction   --master-data=2  -u root -p123456 >  /tmp/test.sql

出处：http://wolfword.blog.51cto.com/4892126/1289596

（编辑：开发网_开封站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!