mysqldump與mysqlpump

                   5.7新出的資料庫匯出方法，官方的書法是用mysqlpump代替mysqldump，當然mysqldump匯出非常慢是單執行緒的，mysqlpump是多執行緒的，在社群版本中mydumper是多執行緒的。
mysqlpump會有問題，要在5.7.11之後使用，

mysqldump下有一個預設的佇列，佇列下可以開啟多個執行緒，並且可以針對不同的資料庫開啟相應的多執行緒。
mysqlpump  --single-transaction --databases scott > scott.sql

對應執行緒數，預設是2

可以指定備份資料庫的佇列，如下：

當然我這個是5.7.9，你可以新增上--single-transaction在以後的版本中。
並且備份檔案中，是建立表後插入資料，最後建立索引，比mysqldump優化了些(因為mysqldump是建立表的時候同時建立索引，再插入資料，這樣的效率不如前者)。
還有一個好處是可以壓縮，之前mysqldump是備份出來tar個包壓縮，現在使用mysqlpump可以指定引數壓縮，

zlib慢
mysql < scott.sql匯入
zlib_decompress scott.sql xx

----------------------------------分割線--------------------------------------

----------------------------------分割線--------------------------------------

----------------------------------分割線--------------------------------------

                   有人說他們公司135是mysqldump，246是xtrabackup，額...醉了...
ibbackup：官方收費。
xtrabackup 是ibbackup社群版備份工具，都是物理備份工具，類似oracle的rman。
mysqldump 是邏輯備份工具。
老版本的xtrabackup在備份5.6的時候，有可能丟失資料，丟失最後一組的資料，資料庫繁忙的時候會出現，2.2.3解決了這個問題，這時候必須提高xtrabackup的版本。

xtrabackup沒有指定壓縮的話，備份出來的大小和原來大小類似。但是用mysqldump備份出來的也就是原來的1/10、1/5的大小。但是xtrabackup並不像想象的那麼可靠，因為總有一些場景丟失資料。
mysqldump要將資料讀到緩衝池中，熱資料不會擠出緩衝池等。其實也還好，因此我們備份在備庫做。
最新的xtrabackup是2.4.1的支援了5.7
mysqldump

當然也可以如下看更詳細的引數

引數 --single-transaction 指定一致性的備份，只innodb引擎，一致性備份說的是備份的資料是在開始的那個點的資料，即便資料更改。。



[root@MyO ~]# mysqldump --databases scott > scott.sql
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 
[root@MyO ~]# 
[root@MyO ~]# cat scott.sql 
-- MySQL dump 10.13  Distrib 5.7.9, for linux-glibc2.5 (x86_64)
--
-- Host: localhost    Database: scott
-- ------------------------------------------------------
-- Server version 5.7.9-log


/*!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 */;
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;


--
-- GTID state at the beginning of the backup 
--


SET @@GLOBAL.GTID_PURGED='cc03f088-9fd5-11e5-bc43-080027520b8c:1-48';


--
-- Current Database: `scott`
--


CREATE DATABASE /*!32312 IF NOT EXISTS*/ `scott` /*!40100 DEFAULT CHARACTER SET utf8 */;


USE `scott`;


--
-- Table structure for table `dept`
--


DROP TABLE IF EXISTS `dept`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `dept` (
  `department_id` int(4) DEFAULT NULL,
  `department_name` varchar(30) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `manager_id` int(6) DEFAULT NULL,
  `location_id` int(4) DEFAULT NULL,
  UNIQUE KEY `dept_id_pk` (`department_id`),
  KEY `dept_location_ix` (`location_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;


--
-- Dumping data for table `dept`
--


LOCK TABLES `dept` WRITE;
/*!40000 ALTER TABLE `dept` DISABLE KEYS */;
INSERT INTO `dept` VALUES (10,'Administration',200,1700),(20,'Marketing',201,1800),(30,'Purchasing',114,1700),(40,'Human Resources',203,2400),(50,'Shipping',121,1500),(60,'IT',103,1400),(70,'Public Relations',204,2700),(80,'Sales',145,2500),(90,'Executive',100,1700),(100,'Finance',108,1700),(110,'Accounting',205,1700),(120,'Treasury',NULL,1700),(130,'Corporate Tax',NULL,1700),(140,'Control And Credit',NULL,1700),(150,'Shareholder Services',NULL,1700),(160,'Benefits',NULL,1700),(170,'Manufacturing',NULL,1700),(180,'Construction',NULL,1700),(190,'Contracting',NULL,1700),(200,'Operations',NULL,1700),(210,'IT Support',NULL,1700),(220,'NOC',NULL,1700),(230,'IT Helpdesk',NULL,1700),(240,'Government Sales',NULL,1700),(250,'Retail Sales',NULL,1700),(260,'Recruiting',NULL,1700),(270,'Payroll',NULL,1700);
/*!40000 ALTER TABLE `dept` ENABLE KEYS */;
UNLOCK TABLES;


--
-- Table structure for table `emp`
--


DROP TABLE IF EXISTS `emp`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `emp` (
  `employee_id` int(6) DEFAULT NULL,
  `first_name` varchar(20) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `last_name` varchar(25) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `email` varchar(25) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `phone_number` varchar(20) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `hire_date` datetime NOT NULL,
  `job_id` varchar(10) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `salary` decimal(8,2) DEFAULT NULL,
  `commission_pct` decimal(2,2) DEFAULT NULL,
  `manager_id` int(6) DEFAULT NULL,
  `department_id` int(4) DEFAULT NULL,
  UNIQUE KEY `emp_email_uk` (`email`),
  UNIQUE KEY `emp_emp_id_pk` (`employee_id`),
  KEY `emp_department_ix` (`department_id`),
  KEY `emp_job_ix` (`job_id`),
  KEY `emp_manager_ix` (`manager_id`),
  KEY `emp_name_ix` (`last_name`,`first_name`),
  KEY `emp_salary_ix` (`salary`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;


--
-- Dumping data for table `emp`
--


LOCK TABLES `emp` WRITE;
/*!40000 ALTER TABLE `emp` DISABLE KEYS */;
INSERT INTO `emp` VALUES (167,'Amit','Banda','ABANDA','011.44.1346.729268','2008-04-21 00:00:00','SA_REP',6200.00,0.10,147,80),(185,'Alexis','Bull','ABULL','650.509.2876','2005-02-20 00:00:00','SH_CLERK',4100.00,NULL,121,50),(187,'Anthony','Cabrio','ACABRIO','650.509.4876','2007-02-07 00:00:00','SH_CLERK',3000.00,NULL,121,50),(147,'Alberto','Errazuriz','AERRAZUR','011.44.1344.429278','2005-03-10 00:00:00','SA_MAN',12000.00,0.30,100,80),(121,'Adam','Fripp','AFRIPP','650.123.2234','2005-04-10 00:00:00','ST_MAN',8200.00,NULL,100,50),(103,'Alexander','Hunold','AHUNOLD','590.423.4567','2006-01-03 00:00:00','IT_PROG',9000.00,NULL,102,60),(175,'Alyssa','Hutton','AHUTTON','011.44.1644.429266','2005-03-19 00:00:00','SA_REP',8800.00,0.25,149,80),(115,'Alexander','Khoo','AKHOO','515.127.4562','2003-05-18 00:00:00','PU_CLERK',3100.00,NULL,114,30),(158,'Allan','McEwen','AMCEWEN','011.44.1345.829268','2004-08-01 00:00:00','SA_REP',9000.00,0.35,146,80),(196,'Alana','Walsh','AWALSH','650.507.9811','2006-04-24 00:00:00','SH_CLERK',3100.00,NULL,124,50),(104,'Bruce','Ernst','BERNST','590.423.4568','2007-05-21 00:00:00','IT_PROG',6000.00,NULL,103,60),(193,'Britney','Everett','BEVERETT','650.501.2876','2005-03-03 00:00:00','SH_CLERK',3900.00,NULL,123,50),(142,'Curtis','Davies','CDAVIES','650.121.2994','2005-01-29 00:00:00','ST_CLERK',3100.00,NULL,124,50),(179,'Charles','Johnson','CJOHNSON','011.44.1644.429262','2008-01-04 00:00:00','SA_REP',6200.00,0.10,149,80),(153,'Christopher','Olsen','COLSEN','011.44.1344.498718','2006-03-30 00:00:00','SA_REP',8000.00,0.20,145,80),(162,'Clara','Vishney','CVISHNEY','011.44.1346.129268','2005-11-11 00:00:00','SA_REP',10500.00,0.25,147,80),(105,'David','Austin','DAUSTIN','590.423.4569','2005-06-25 00:00:00','IT_PROG',4800.00,NULL,103,60),(151,'David','Bernstein','DBERNSTE','011.44.1344.345268','2005-03-24 00:00:00','SA_REP',9500.00,0.25,145,80),(109,'Daniel','Faviet','DFAVIET','515.124.4169','2002-08-16 00:00:00','FI_ACCOUNT',9000.00,NULL,108,100),(199,'Douglas','Grant','DGRANT','650.507.9844','2008-01-13 00:00:00','SH_CLERK',2600.00,NULL,124,50),(163,'Danielle','Greene','DGREENE','011.44.1346.229268','2007-03-19 00:00:00','SA_REP',9500.00,0.15,147,80),(165,'David','Lee','DLEE','011.44.1346.529268','2008-02-23 00:00:00','SA_REP',6800.00,0.10,147,80),(107,'Diana','Lorentz','DLORENTZ','590.423.5567','2007-02-07 00:00:00','IT_PROG',4200.00,NULL,103,60),(198,'Donald','OConnell','DOCONNEL','650.507.9833','2007-06-21 00:00:00','SH_CLERK',2600.00,NULL,124,50),(114,'Den','Raphaely','DRAPHEAL','515.127.4561','2002-12-07 00:00:00','PU_MAN',11000.00,NULL,100,30),(174,'Ellen','Abel','EABEL','011.44.1644.429267','2004-05-11 00:00:00','SA_REP',11000.00,0.30,149,80),(172,'Elizabeth','Bates','EBATES','011.44.1343.529268','2007-03-24 00:00:00','SA_REP',7300.00,0.15,148,80),(149,'Eleni','Zlotkey','EZLOTKEY','011.44.1344.429018','2008-01-29 00:00:00','SA_MAN',10500.00,0.20,100,80),(148,'Gerald','Cambrault','GCAMBRAU','011.44.1344.619268','2007-10-15 00:00:00','SA_MAN',11000.00,0.30,100,80),(183,'Girard','Geoni','GGEONI','650.507.9879','2008-02-03 00:00:00','SH_CLERK',2800.00,NULL,120,50),(118,'Guy','Himuro','GHIMURO','515.127.4565','2006-11-15 00:00:00','PU_CLERK',2600.00,NULL,114,30),(204,'Hermann','Baer','HBAER','515.123.8888','2002-06-07 00:00:00','PR_REP',10000.00,NULL,101,70),(169,'Harrison','Bloom','HBLOOM','011.44.1343.829268','2006-03-23 00:00:00','SA_REP',10000.00,0.20,148,80),(136,'Hazel','Philtanker','HPHILTAN','650.127.1634','2008-02-06 00:00:00','ST_CLERK',2200.00,NULL,122,50),(126,'Irene','Mikkilineni','IMIKKILI','650.124.1224','2006-09-28 00:00:00','ST_CLERK',2700.00,NULL,120,50),(111,'Ismael','Sciarra','ISCIARRA','515.124.4369','2005-09-30 00:00:00','FI_ACCOUNT',7700.00,NULL,108,100),(131,'James','Marlow','JAMRLOW','650.124.7234','2005-02-16 00:00:00','ST_CLERK',2500.00,NULL,121,50),(110,'John','Chen','JCHEN','515.124.4269','2005-09-28 00:00:00','FI_ACCOUNT',8200.00,NULL,108,100),(186,'Julia','Dellinger','JDELLING','650.509.3876','2006-06-24 00:00:00','SH_CLERK',3400.00,NULL,121,50),(189,'Jennifer','Dilly','JDILLY','650.505.2876','2005-08-13 00:00:00','SH_CLERK',3600.00,NULL,122,50),(181,'Jean','Fleaur','JFLEAUR','650.507.9877','2006-02-23 00:00:00','SH_CLERK',3100.00,NULL,120,50),(156,'Janette','King','JKING','011.44.1345.429268','2004-01-30 00:00:00','SA_REP',10000.00,0.35,146,80),(127,'James','Landry','JLANDRY','650.124.1334','2007-01-14 00:00:00','ST_CLERK',2400.00,NULL,120,50),(177,'Jack','Livingston','JLIVINGS','011.44.1644.429264','2006-04-23 00:00:00','SA_REP',8400.00,0.20,149,80),(133,'Jason','Mallin','JMALLIN','650.127.1934','2004-06-14 00:00:00','ST_CLERK',3300.00,NULL,122,50),(112,'Jose Manuel','Urman','JMURMAN','515.124.4469','2006-03-07 00:00:00','FI_ACCOUNT',7800.00,NULL,108,100),(125,'Julia','Nayer','JNAYER','650.124.1214','2005-07-16 00:00:00','ST_CLERK',3200.00,NULL,120,50),(140,'Joshua','Patel','JPATEL','650.121.1834','2006-04-06 00:00:00','ST_CLERK',2500.00,NULL,123,50),(145,'John','Russell','JRUSSEL','011.44.1344.429268','2004-10-01 00:00:00','SA_MAN',14000.00,0.40,100,80),(139,'John','Seo','JSEO','650.121.2019','2006-02-12 00:00:00','ST_CLERK',2700.00,NULL,123,50),(176,'Jonathon','Taylor','JTAYLOR','011.44.1644.429265','2006-03-24 00:00:00','SA_REP',8600.00,0.20,149,80),(200,'Jennifer','Whalen','JWHALEN','515.123.4444','2003-09-17 00:00:00','AD_ASST',4400.00,NULL,101,10),(188,'Kelly','Chung','KCHUNG','650.505.1876','2005-06-14 00:00:00','SH_CLERK',3800.00,NULL,122,50),(119,'Karen','Colmenares','KCOLMENA','515.127.4566','2007-08-10 00:00:00','PU_CLERK',2500.00,NULL,114,30),(197,'Kevin','Feeney','KFEENEY','650.507.9822','2006-05-23 00:00:00','SH_CLERK',3000.00,NULL,124,50),(135,'Ki','Gee','KGEE','650.127.1734','2007-12-12 00:00:00','ST_CLERK',2400.00,NULL,122,50),(178,'Kimberely','Grant','KGRANT','011.44.1644.429263','2007-05-24 00:00:00','SA_REP',7000.00,0.15,149,NULL),(124,'Kevin','Mourgos','KMOURGOS','650.123.5234','2007-11-16 00:00:00','ST_MAN',5800.00,NULL,100,50),(146,'Karen','Partners','KPARTNER','011.44.1344.467268','2005-01-05 00:00:00','SA_MAN',13500.00,0.30,100,80),(129,'Laura','Bissot','LBISSOT','650.124.5234','2005-08-20 00:00:00','ST_CLERK',3300.00,NULL,121,50),(102,'Lex','De Haan','LDEHAAN','515.123.4569','2001-01-13 00:00:00','AD_VP',17000.00,NULL,100,90),(160,'Louise','Doran','LDORAN','011.44.1345.629268','2005-12-15 00:00:00','SA_REP',7500.00,0.30,146,80),(168,'Lisa','Ozer','LOZER','011.44.1343.929268','2005-03-11 00:00:00','SA_REP',11500.00,0.25,148,80),(113,'Luis','Popp','LPOPP','515.124.4567','2007-12-07 00:00:00','FI_ACCOUNT',6900.00,NULL,108,100),(159,'Lindsey','Smith','LSMITH','011.44.1345.729268','2005-03-10 00:00:00','SA_REP',8000.00,0.30,146,80),(130,'Mozhe','Atkinson','MATKINSO','650.124.6234','2005-10-30 00:00:00','ST_CLERK',2800.00,NULL,121,50),(201,'Michael','Hartstein','MHARTSTE','515.123.5555','2004-02-17 00:00:00','MK_MAN',13000.00,NULL,100,20),(164,'Mattea','Marvins','MMARVINS','011.44.1346.329268','2008-01-24 00:00:00','SA_REP',7200.00,0.10,147,80),(134,'Michael','Rogers','MROGERS','650.127.1834','2006-08-26 00:00:00','ST_CLERK',2900.00,NULL,122,50),(182,'Martha','Sullivan','MSULLIVA','650.507.9878','2007-06-21 00:00:00','SH_CLERK',2500.00,NULL,120,50),(120,'Matthew','Weiss','MWEISS','650.123.1234','2004-07-18 00:00:00','ST_MAN',8000.00,NULL,100,50),(154,'Nanette','Cambrault','NCAMBRAU','011.44.1344.987668','2006-12-09 00:00:00','SA_REP',7500.00,0.20,145,80),(108,'Nancy','Greenberg','NGREENBE','515.124.4569','2002-08-17 00:00:00','FI_MGR',12008.00,NULL,101,100),(101,'Neena','Kochhar','NKOCHHAR','515.123.4568','2005-09-21 00:00:00','AD_VP',17000.00,NULL,100,90),(184,'Nandita','Sarchand','NSARCHAN','650.509.1876','2004-01-27 00:00:00','SH_CLERK',4200.00,NULL,121,50),(155,'Oliver','Tuvault','OTUVAULT','011.44.1344.486508','2007-11-23 00:00:00','SA_REP',7000.00,0.15,145,80),(202,'Pat','Fay','PFAY','603.123.6666','2005-08-17 00:00:00','MK_REP',6000.00,NULL,201,20),(152,'Peter','Hall','PHALL','011.44.1344.478968','2005-08-20 00:00:00','SA_REP',9000.00,0.25,145,80),(122,'Payam','Kaufling','PKAUFLIN','650.123.3234','2003-05-01 00:00:00','ST_MAN',7900.00,NULL,100,50),(157,'Patrick','Sully','PSULLY','011.44.1345.929268','2004-03-04 00:00:00','SA_REP',9500.00,0.35,146,80),(150,'Peter','Tucker','PTUCKER','011.44.1344.129268','2005-01-30 00:00:00','SA_REP',10000.00,0.30,145,80),(144,'Peter','Vargas','PVARGAS','650.121.2004','2006-07-09 00:00:00','ST_CLERK',2500.00,NULL,124,50),(137,'Renske','Ladwig','RLADWIG','650.121.1234','2003-07-14 00:00:00','ST_CLERK',3600.00,NULL,123,50),(143,'Randall','Matos','RMATOS','650.121.2874','2006-03-15 00:00:00','ST_CLERK',2600.00,NULL,124,50),(191,'Randall','Perkins','RPERKINS','650.505.4876','2007-12-19 00:00:00','SH_CLERK',2500.00,NULL,122,50),(166,'Sundar','Ande','SANDE','011.44.1346.629268','2008-03-24 00:00:00','SA_REP',6400.00,0.10,147,80),(116,'Shelli','Baida','SBAIDA','515.127.4563','2005-12-24 00:00:00','PU_CLERK',2900.00,NULL,114,30),(192,'Sarah','Bell','SBELL','650.501.1876','2004-02-04 00:00:00','SH_CLERK',4000.00,NULL,123,50),(205,'Shelley','Higgins','SHIGGINS','515.123.8080','2002-06-07 00:00:00','AC_MGR',12008.00,NULL,101,110),(100,'Steven','King','SKING','515.123.4567','2003-06-17 00:00:00','AD_PRES',24000.00,NULL,NULL,90),(173,'Sundita','Kumar','SKUMAR','011.44.1343.329268','2008-04-21 00:00:00','SA_REP',6100.00,0.10,148,80),(128,'Steven','Markle','SMARKLE','650.124.1434','2008-03-08 00:00:00','ST_CLERK',2200.00,NULL,120,50),(203,'Susan','Mavris','SMAVRIS','515.123.7777','2002-06-07 00:00:00','HR_REP',6500.00,NULL,101,40),(194,'Samuel','McCain','SMCCAIN','650.501.3876','2006-07-01 00:00:00','SH_CLERK',3200.00,NULL,123,50),(161,'Sarath','Sewall','SSEWALL','011.44.1345.529268','2006-11-03 00:00:00','SA_REP',7000.00,0.25,146,80),(138,'Stephen','Stiles','SSTILES','650.121.2034','2005-10-26 00:00:00','ST_CLERK',3200.00,NULL,123,50),(117,'Sigal','Tobias','STOBIAS','515.127.4564','2005-07-24 00:00:00','PU_CLERK',2800.00,NULL,114,30),(123,'Shanta','Vollman','SVOLLMAN','650.123.4234','2005-10-10 00:00:00','ST_MAN',6500.00,NULL,100,50),(170,'Tayler','Fox','TFOX','011.44.1343.729268','2006-01-24 00:00:00','SA_REP',9600.00,0.20,148,80),(190,'Timothy','Gates','TGATES','650.505.3876','2006-07-11 00:00:00','SH_CLERK',2900.00,NULL,122,50),(132,'TJ','Olson','TJOLSON','650.124.8234','2007-04-10 00:00:00','ST_CLERK',2100.00,NULL,121,50),(141,'Trenna','Rajs','TRAJS','650.121.8009','2003-10-17 00:00:00','ST_CLERK',3500.00,NULL,124,50),(195,'Vance','Jones','VJONES','650.501.4876','2007-03-17 00:00:00','SH_CLERK',2800.00,NULL,123,50),(106,'Valli','Pataballa','VPATABAL','590.423.4560','2006-02-05 00:00:00','IT_PROG',4800.00,NULL,103,60),(206,'William','Gietz','WGIETZ','515.123.8181','2002-06-07 00:00:00','AC_ACCOUNT',8300.00,NULL,205,110),(171,'William','Smith','WSMITH','011.44.1343.629268','2007-02-23 00:00:00','SA_REP',7400.00,0.15,148,80),(180,'Winston','Taylor','WTAYLOR','650.507.9876','2006-01-24 00:00:00','SH_CLERK',3200.00,NULL,120,50);
/*!40000 ALTER TABLE `emp` ENABLE KEYS */;
UNLOCK TABLES;
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;
/*!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 */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;


-- Dump completed on 2016-01-22 21:32:47

我們看mysqldump具體做了哪些操作！
set global general_log  = 1;
set global log_output ='table';

然後查詢

可以看出，mysqldump是會話執行緒級別的，並且開啟的是rr模式的會話，並且是通過查詢表做邏輯備份，save point sp和rollback to savepoint sp。
master-data=1
mysqldump --single-transaction --master-data=1 --databases scott > scott.sql

備份是change master點時的一致性備份，如下就是我的備份位置，並且在general_log做了，flush tables with read lock(在mysql上層實現鎖住表);複製的環境中一般要使用master-data這個引數。

雖然會鎖住表，影響業務，但是就很短的時間，之後unlock tables。就是為了知道對應的二進位制的位置是多少。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29990276/viewspace-1994831/，如需轉載，請註明出處，否則將追究法律責任。