ä¸ã        ç®ä»
       Â
        mysqldumpæ¯å®¢æ·ç«¯ç¨æ¥å¤ä»½æ°æ®åºæè å¨ä¸éæ°æ®åºä¹é´è¿è¡æ°æ®è¿ç§»çå·¥å ·ï¼å¤ä»½å 容å å«å建表æè è£ è½½è¡¨çSQLè¯å¥
Â
äºã       å½ä»¤æ ¼å¼
Â
å¤ä»½å个æ°æ®åºæè æ°æ®åºä¸é¨åæ°æ®è¡¨
mysqldump [åæ°] æ°æ®åºå [表å]
å¤ä»½æå®çä¸ä¸ªæè å¤ä¸ªæ°æ®åº
mysqldump [åæ°] --database db1 [db2db3]
å¤ä»½æææ°æ®åº
mysqldump [åæ°] --all-database
Â
ä¸ã       常ç¨åæ°
Â
        -u--user=name        ç¨æ·å
   -p --password           å¯ç
   -h --host=hostname æå®æå¡å¨IPæè å¯è§£æå°çåå
        -P--port                     端å£
        --add-drop-database æ¯ä¸ªæ°æ®åºå建è¯å¥åå ä¸drop databaseï¼ç¨äºå建æ°åºåå°æ§åºå æ
        --add-drop-table      å¨æ¯ä¸ªå建表åå ä¸drop tableè¯å¥ï¼ç¨äºå建æ°è¡¨åå é¤æ§è¡¨
         -n--no-create-db    ä¸å å«æ°æ®åºå建è¯å¥
        -t --no-create-info   ä¸å å«æ°æ®è¡¨å建è¯å¥
        -d --no-data              ä¸å å«æ°æ®
        --compact                å°è¾åºçç»æç®æ´ï¼ä¸å æ¬é»è®¤é项ä¸çåç§æ³¨é
        -c--complete-insert å¨insertè¯å¥ä¸å ä¸å段åï¼é»è®¤ä¸å ï¼Â                     Â
        -F     --flush-logs      å¤ä»½åå·æ°æ¥å¿ï¼ä½¿å¾æ¹ä¾¿æ¢å¤ã
        -l      --lock-tables    ç»ææ表添å 读éä¿è¯æ°æ®ä¸è´æ§ä¸è¬ä¸-Fé å使ç¨
        -T     æ¤åæ°ä¸ä¸é¢åæ°é å使ç¨ï¼æ¤åæ°æ¯å°æ°æ®å¤ä»½ä¸ºæ·¡åºçæ°æ®ææ¬ææ¡£å建表çSQL两个æ件
                  --filelds-terminated-by=name(ååé符)
                  --filelds-enclosed-by=nameï¼åå¼ç¨ç¬¦ï¼
                  --fields-optionally-enclosed-by=name(åå¯éå¼ç¨ç¬¦ï¼
                  --fields-escaped-by=name(转ä¹å符ï¼
        --default-characte-set=å符类å  æ¤é项å¯ä»¥è®¾ç½®å¯¼åºç客æ·ç«¯å符éï¼ç³»ç»é»è®¤å®¢æ·ç«¯å符éå¯ä»¥éè¿ mysql --verbose --help |grep 'default-character-set'|grep-v nameæ¥æ¥çæ以ä¸è¬å¯¼åºçæ¶åä¸å®è¦å°å符é设置对ã
Â
åã       常ç¨ä¾å
1)    å¤ä»½T4表å°test4.txt
Â
[[email protected] data]# mysqldump -h10.22.19.44 -uroot-p test2 t4 >test4.txt
Â
2)    å¤ä»½æå¡è¡¨
Â
[[email protected]]# mysqldump -h10.22.19.44 -uroot -p jiaowu >jiaowu.txt
Â
3)    åªå¯¼åºè¡¨çå建è¯å¥ï¼ä¸å å«å ¶ä»ä¿¡æ¯
Â
[[email protected]]# mysqldump -h10.22.19.44 -uroot -p -d test2 t4 >nodata.txt
Enter password:
[[email protected] data]# cat nodata.txt
-- MySQL dump 10.13Â Distrib 5.6.12, for linux-glibc2.5 (x86_64)
--
-- Host: 10.22.19.44Â Â Â Database: test2
--------------------------------------------------------
-- Server version      5.6.12-log
Â
;
;
;
;
;
;
;
;
;
;
Â
--
-- Table structure for table `t4`
--
Â
DROP TABLE IF EXISTS `t4`;
;
;
CREATE TABLE `t4` (
 `id` int(10) NOT NULL DEFAULT '0',
 `str_number` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
;
;
Â
;
;
;
;
;
;
;
Â
-- Dump completed on 2015-07-17 14:53:49
Â
4)    åªå¯¼åºè¡¨çå建è¯å¥ï¼å¹¶ä¸æ·»å å é¤æ§è¡¨çè¯å¥
Â
[[email protected]]# mysqldump -h10.22.19.44 -uroot -p -d --add-drop-table jiaowu tutors>nooldtable.txt
Enter password:
[[email protected]]# cat nooldtable.txt
-- MySQL dump 10.13Â Distrib 5.6.12, for linux-glibc2.5 (x86_64)
--
-- Host: 10.22.19.44Â Â Â Database: jiaowu
-- ------------------------------------------------------
-- Server version      5.6.12-log
Â
;
;
;
;
;
;
;
;
;
;
Â
--
-- Table structure for table `tutors`
--
Â
DROP TABLE IF EXISTS `tutors`;
;
;
CREATE TABLE `tutors` (
 `TID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
 `Tname` varchar(50) NOT NULL,
 `Gender` enum('F','M') DEFAULT 'M',
 `Age` tinyint(3) unsigned DEFAULT NULL,
 UNIQUE KEY `TID` (`TID`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULTCHARSET=latin1;
;
;
Â
;
;
;
;
;
;
;
Â
-- Dump completed on 2015-07-17 14:57:03
Â
5)        å°ä¸é¢çç»æéè¿ç®æ´çæ¹å¼è¾åº
Â
[[email protected]]# mysqldump -h10.22.19.44 -uroot -p -d --compact --add-drop-table jiaowututors >compactnooldtable.txt
Enter password:
[[email protected]]# cat compactnooldtable.txt
DROP TABLE IF EXISTS `tutors`;
;
;
CREATE TABLE `tutors` (
 `TID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
 `Tname` varchar(50) NOT NULL,
 `Gender` enum('F','M') DEFAULT 'M',
 `Age` tinyint(3) unsigned DEFAULT NULL,
 UNIQUE KEY `TID` (`TID`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULTCHARSET=latin1;
;
Â
6)        éè¿ç®æ´æ¹å¼å°tutors导åºï¼å¹¶ä¸å¨insert intoè¯å¥ä¸æ·»å å段å
Â
[[email protected]]# mysqldump -h10.22.19.44 -uroot -p -c jiaowu tutors>completetutors.txt
Enterpassword:
[[email protected]]# cat completetutors.txt
-- MySQL dump 10.13Â Distrib 5.6.12, for linux-glibc2.5 (x86_64)
--
-- Host: 10.22.19.44Â Â Â Database: jiaowu
--------------------------------------------------------
-- Server version      5.6.12-log
Â
;
;
;
;
;
;
;
;
;
;
Â
--
-- Table structure for table `tutors`
--
Â
DROP TABLE IF EXISTS `tutors`;
;
;
CREATE TABLE `tutors` (
 `TID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
 `Tname` varchar(50) NOT NULL,
 `Gender` enum('F','M') DEFAULT 'M',
 `Age`tinyint(3) unsigned DEFAULT NULL,
 UNIQUE KEY `TID` (`TID`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULTCHARSET=latin1;
;
Â
--
-- Dumping data for table `tutors`
--
Â
LOCK TABLES `tutors` WRITE;
;
INSERT INTO `tutors` (`TID`, `Tname`,`Gender`, `Age`) VALUES(1,'HongQigong','M',93),(2,'HuangYaoshi','M',63),(3,'Miejueshitai','F',72),(4,'OuYangfeng','M',76),(5,'YiDeng','M',90),(6,'YuCanghai','M',56),(7,'Jinlunfawang','M',67),(8,'HuYidao','M',42),(9,'NingZhongze','F',49),(10,'TOM','F',30),(11,'DingDian','M',25),(12,'HuFei','M',31),(13,'Xuzhu','M',26),(18,'DingDian','M',25),(19,'HuFei','M',31),(20,'Xuzhu','M',26),(23,'stu0010','M',NULL),(24,'stu0012','M',NULL),(25,'str000023','M',NULL),(26,'str000024','M',NULL),(27,'str9999','M',NULL),(28,'str9991','M',NULL),(29,'str9992','M',NULL),(30,'str9993','M',NULL);
;
UNLOCK TABLES;
;
Â
;
;
;
;
;
;
;
Â
-- Dump completed on 2015-07-17 15:03:30
[[email protected] data]# mysqldump-h10.22.19.44 -uroot -p --compact -c jiaowu tutors >completetutors.txt
Enter password:
[[email protected] data]# catcompletetutors.txt
;
;
CREATE TABLE `tutors` (
 `TID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
 `Tname` varchar(50) NOT NULL,
 `Gender` enum('F','M') DEFAULT 'M',
 `Age` tinyint(3) unsigned DEFAULT NULL,
 UNIQUE KEY `TID` (`TID`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULTCHARSET=latin1;
;
INSERT INTO `tutors` (`TID`, `Tname`,`Gender`, `Age`) VALUES(1,'HongQigong','M',93),(2,'HuangYaoshi','M',63),(3,'Miejueshitai','F',72),(4,'OuYangfeng','M',76),(5,'YiDeng','M',90),(6,'YuCanghai','M',56),(7,'Jinlunfawang','M',67),(8,'HuYidao','M',42),(9,'NingZhongze','F',49),(10,'TOM','F',30),(11,'DingDian','M',25),(12,'HuFei','M',31),(13,'Xuzhu','M',26),(18,'DingDian','M',25),(19,'HuFei','M',31),(20,'Xuzhu','M',26),(23,'stu0010','M',NULL),(24,'stu0012','M',NULL),(25,'str000023','M',NULL),(26,'str000024','M',NULL),(27,'str9999','M',NULL),(28,'str9991','M',NULL),(29,'str9992','M',NULL),(30,'str9993','M',NULL);
Â
7)        å°tutors导åºä¸ºå纯çææ¬æ件å建表SQL
Â
[[email protected]]# mkdir bak
[[email protected]]# chown mysql:mysql ./bak/
[[email protected]]# mysqldump -uroot -p123456 jiaowu tutors -T ./bak/
Warning: Using a password on the commandline interface can be insecure.
[[email protected] data]# cat./bak/tutors.sql
-- MySQL dump 10.13Â Distrib 5.6.12, for linux-glibc2.5 (x86_64)
--
-- Host: localhost   Database: jiaowu
--------------------------------------------------------
-- Server version      5.6.12-log
Â
;
;
;
;
;
;
;
;
Â
--
-- Table structure for table `tutors`
--
Â
DROP TABLE IF EXISTS `tutors`;
;
;
CREATE TABLE `tutors` (
 `TID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
 `Tname` varchar(50) NOT NULL,
 `Gender` enum('F','M') DEFAULT 'M',
 `Age` tinyint(3) unsigned DEFAULT NULL,
 UNIQUE KEY `TID` (`TID`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULTCHARSET=latin1;
;
Â
;
Â
;
;
;
;
;
Â
-- Dump completed on 2015-07-17 15:14:21
Â
[[email protected]]# cat ./bak/tutors.txt
1      HongQigong     M      93
2      HuangYaoshi    M      63
3      Miejueshitai   F      72
4      OuYangfeng     M      76
5      YiDeng M      90
6      YuCanghai      M      56
7      Jinlunfawang   M      67
8Â Â Â Â Â Â HuYidao MÂ Â Â Â Â Â 42
9      NingZhongze    F      49
10Â Â Â Â Â TOMÂ Â Â Â FÂ Â Â Â Â Â 30
11     DingDian       M      25
12     HuFei  M      31
13     Xuzhu  M      26
18     DingDian       M      25
19     HuFei  M      31
20     Xuzhu  M      26
23Â Â Â Â Â stu0010 MÂ Â Â Â Â Â \N
24Â Â Â Â Â stu0012 MÂ Â Â Â Â Â \N
25Â Â Â Â Â str000023Â Â Â Â Â Â MÂ Â Â Â Â Â \N
26Â Â Â Â Â str000024Â Â Â Â Â Â MÂ Â Â Â Â Â \N
27Â Â Â Â Â str9999 MÂ Â Â Â Â Â \N
28Â Â Â Â Â str9991 MÂ Â Â Â Â Â \N
29Â Â Â Â Â str9992 MÂ Â Â Â Â Â \N
30Â Â Â Â Â str9993 MÂ Â Â Â Â Â \N
Â
å¨åç®å½ä¸å¯¼åºæ件æ¶ï¼ææ¶åä¼æ示ä¸é¢é误
[ro[email protected] data]# mysqldump -uroot-p123456 test2 t4 -T  ./bak/
Warning: Using a password onthe command line interface can be insecure.
mysqldump: Got error: 1: Can'tcreate/write to file '/usr/local/mysql/data/bak/t4.txt' (Errcode: 13 -Permission denied) when executing 'SELECT INTO OUTFILE'
æ¥åºæ¤éæ¯ç±äºç®å½æ²¡æ足å¤æé导è´ï¼åªéç»ç®å½è¶³å¤æéå³å¯
[[email protected] data]# chownmysql:mysql ./bak/
[[email protected] data]# mysqldump -uroot -p123456 jiaowu tutors -T ./bak/
Warning: Using a password on the command lineinterface can be insecure.
Â
Â
8)        导åºå«æä¸æçæ°æ®ï¼ä¸å åæ°ä¼å¯¼è´æ°æ®ä¸æä¹±ç
Â
[[email protected]]# mysqldump -uroot -p123456 --compact jiaowu tutors >test5
Warning: Using a password on the commandline interface can be insecure.
[[email protected]]# cat test5
;
;
CREATE TABLE `tutors` (
 `TID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
 `Tname` varchar(200) DEFAULT NULL,
 `Gender`enum('F','M') DEFAULT 'M',
 `Age` tinyint(3) unsigned DEFAULT NULL,
 UNIQUE KEY `TID` (`TID`)
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULTCHARSET=latin1;
;
INSERT INTO `tutors` VALUES (1,'HongQigong','M',93),(2,'HuangYaoshi','M',63),(3,'Miejueshitai','F',72),(4,'OuYangfeng','M',76),(5,'YiDeng','M',90),(6,'YuCanghai','M',56),(7,'Jinlunfawang','M',67),(8,'HuYidao','M',42),(9,'NingZhongze','F',49),(10,'TOM','F',30),(11,'DingDian','M',25),(12,'HuFei','M',31),(13,'Xuzhu','M',26),(18,'DingDian','M',25),(19,'HuFei','M',31),(20,'Xuzhu','M',26),(23,'stu0010','M',NULL),(24,'stu0012','M',NULL),(25,'str000023','M',NULL),(26,'str000024','M',NULL),(27,'str9999','M',NULL),(28,'str9991','M',NULL),(29,'str9992','M',NULL),(30,'str9993','M',NULL),(32,'ä¸ÂÃ¥âºÂ½','M',21),(33,'çâ¯åÂÂ','M',21),(34,'Ã¥âȌÂÂ','M',21);
Â
å¯ä»¥çå°å¯¼åºçæ°æ®æ¯ä¹±ç
Â
[[email protected]]# mysqldump -uroot -p123456 --compact --default-character-set=latin1jiaowu tutors >test6
Warning: Using a password on the commandline interface can be insecure.
[[email protected]]# cat test6
;
;
CREATE TABLE `tutors` (
 `TID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
 `Tname` varchar(200) DEFAULT NULL,
 `Gender` enum('F','M') DEFAULT 'M',
 `Age` tinyint(3) unsigned DEFAULT NULL,
 UNIQUE KEY `TID` (`TID`)
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULTCHARSET=latin1;
;
INSERT INTO `tutors` VALUES(1,'HongQigong','M',93),(2,'HuangYaoshi','M',63),(3,'Miejueshitai','F',72),(4,'OuYangfeng','M',76),(5,'YiDeng','M',90),(6,'YuCanghai','M',56),(7,'Jinlunfawang','M',67),(8,'HuYidao','M',42),(9,'NingZhongze','F',49),(10,'TOM','F',30),(11,'DingDian','M',25),(12,'HuFei','M',31),(13,'Xuzhu','M',26),(18,'DingDian','M',25),(19,'HuFei','M',31),(20,'Xuzhu','M',26),(23,'stu0010','M',NULL),(24,'stu0012','M',NULL),(25,'str000023','M',NULL),(26,'str000024','M',NULL),(27,'str9999','M',NULL),(28,'str9991','M',NULL),(29,'str9992','M',NULL),(30,'str9993','M',NULL),(32,'ä¸å½','M',21),(33,'ç¯å','M',21),(34,'å»å','M',21);
Â
å¯ä»¥çå°è®¾ç½®ç¼ç åï¼å¯¼åºçæ°æ®ä¸ºæ£ç¡®çä¸æå符
Â
9)        å¤ä»½åéå®è¡¨å¹¶ä¸å·æ°å¤ä»½æ¥å¿
Â
å¤ä»½åæ¥çäºè¿å¶æ¥å¿å·ï¼
mysql>show master status;
+--------------+----------+--------------+------------------+-------------------+
| File        | Position | Binlog_Do_DB |Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------+----------+--------------+------------------+-------------------+
| mysql.000009 |Â Â Â Â Â 120 |Â Â Â Â Â Â Â Â Â Â Â Â Â |Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â |Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â |
+--------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
[[email protected]]# mysqldump -uroot -p123456 --compact -F -l jiaowu >jiaowu.sql
Warning: Using a password on the commandline interface can be insecure.
å¤ä»½åæ¥çäºè¿å¶æ¥å¿å·
mysql>show master status;
+--------------+----------+--------------+------------------+-------------------+
| File        | Position | Binlog_Do_DB |Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------+----------+--------------+------------------+-------------------+
| mysql.000011 |Â Â Â Â Â 120 |Â Â Â Â Â Â Â Â Â Â Â Â Â |Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â |Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â |
+--------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
æé è¿éå± ç¶æ»å¨äºä¿©æ¥å¿ï¼æä¸ç´æ²¡ææç½ä¸ºä»ä¹æ»å¨ä¿©æ¥å¿ï¼ä¸å°12导åºè¯·å¤§å®¶è§£å³å°å¼é®é¢
;Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â
 ;                          Â
 ;        Â
 DELIMITER ;                                                                Â
 #at 4                                                                          Â
 #150717 16:06:16 server id 1 end_log_pos 120 CRC32 0x8f464488 Start: binlog v 4, server v 5.6.12-log created 150717 16:06:16
 BINLOG '                                                                        Â
 eLeoVQ8BAAAAdAAAAHgAAAAAAAQANS42LjEyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
 AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAYhE
 Ro8=
 ';                                                                         Â
 #at 120                                                                        Â
 #150717 16:06:16 server id 1 end_log_pos 163 CRC32 0xf497faf6 Rotate to mysql.000013 pos: 4
 DELIMITER ;                                                                     Â
 #End of log file                                                               Â
 ROLLBACK ;                                            Â
 ;                            Â
 ;                                   Â
 ~