0. summary
1. 常用参数
2. 备份示例
. 2.1 备份指定数据库
. 2.2 根据条件备份指定表
3. 恢复示例
4. mysqldump的原理
1. 常用参数
[root@test-1 mdata]# mysqldump
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
database [tables] ---- 备份指定库的指定表
-B或者--databases ---- 备份指定库
-A或者--all-databases ---- 备份全库
--single-transaction ---- 备份出来的数据是一致的
对于sharing, 通常不做一致性备份，每个sharing应该是没有依赖关系的，不需要一致性备份。要对集群做恢复的话，需要在各个节点去下发flush tables with read lock. 再去发起备份，备份过程中把flush tables的给干掉。
-w, --where=name ---- 备份指定条件
--skip-extended-insert ---- 不会把insert语句合并，即相当于每条commit
2. 备份示例
2.1 备份指定数据库
[root@test-1 mdata]# mysqldump --single-transaction -B mytest > test_backup.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.
备份的时候针对表数据还是可以读写的。备份的时候有元数据锁，但是很短。
2.2 根据条件备份指定表
(root@localhost) [mytest]> select * from n;
+-------------+----------------+-------------+------------------------------------------------------------------------------------------------------------------+--------+
| n_nationkey | n_name | n_regionkey | n_comment | n_test |
+-------------+----------------+-------------+------------------------------------------------------------------------------------------------------------------+--------+
| 0 | ALGERIA | 0 | final accounts wake quickly. special reques | aaa |
| 1 | ARGENTINA | 1 | idly final instructions cajole stealthily. regular instructions wake carefully blithely express accounts. fluffi | aaa |
| 2 | BRAZIL | 1 | always pending pinto beans sleep sil | aaa |
| 3 | CANADA | 1 | foxes among the bold requests | aaa |
| 4 | EGYPT | 4 | pending accounts haggle furiously. furiously bold accounts detect. platelets at the packages haggle caref | aaa |
| 5 | ETHIOPIA | 0 | fluffily ruthless requests integrate fluffily. pending ideas wake blithely acco | aaa |
| 6 | FRANCE | 3 | even requests detect near the pendin | aaa |
| 7 | GERMANY | 3 | blithely ironic foxes grow. quickly pending accounts are b | aaa |
| 8 | INDIA | 2 | ironic packages should have to are slyly around the special, ironic accounts. iron | aaa |
| 9 | INDONESIA | 2 | unusual excuses are quickly requests. slyly ironic accounts haggle carefully above the pendin | aaa |
| 10 | IRAN | 4 | blithely even accounts about the furiously regular foxes nag slyly final accounts. quickly final fo | aaa |
| 11 | IRAQ | 4 | express, pending deposits boost quick | aaa |
| 12 | JAPAN | 2 | blithely final packages cajole quickly even dependencies? blithely regular deposits haggle express, ironic re | aaa |
| 13 | JORDAN | 4 | blithe, express deposits boost carefully busy accounts. furiously pending depos | aaa |
| 14 | KENYA | 0 | ironic requests boost. quickly pending pinto beans cajole slyly slyly even deposits. ironic packages | aaa |
| 15 | MOROCCO | 0 | ideas according to the fluffily final pinto beans sleep furiously | aaa |
| 16 | MOZAMBIQUE | 0 | ironic courts wake fluffily even, bold deposi | aaa |
| 17 | PERU | 1 | final, final accounts sleep slyly across the requests. | aaa |
| 18 | CHINA | 2 | bold accounts are. slyly ironic escapades haggle acc | aaa |
| 19 | ROMANIA | 3 | deposits boost against the brave id | aaa |
| 20 | SAUDI ARABIA | 4 | fluffily final accounts wake slyly-- fi | aaa |
| 21 | VIETNAM | 2 | doggedly ironic requests haggle furiously ironic, ironic packages. furiously final courts wake fur | aaa |
| 22 | RUSSIA | 3 | slowly pending patterns x-ray quickly. ironic, even accounts haggle furiously. even, final deposits mold bl | aaa |
| 23 | UNITED KINGDOM | 3 | fluffily regular pinto beans breach according to the ironic dolph | aaa |
| 24 | UNITED STATES | 1 | blithely regular deposits serve furiously blithely regular warthogs! slyly fi | aaa |
+-------------+----------------+-------------+------------------------------------------------------------------------------------------------------------------+--------+
25 rows in set (0.00 sec)
(root@localhost) [mytest]> exit
Bye
[root@lab11g mdata]# mysqldump mytest n -w n_regionkey=1
-- MySQL dump 10.13 Distrib 5.7.13, for linux-glibc2.5 (x86_64)
--
-- Host: localhost Database: mytest
-- ------------------------------------------------------
-- Server version 5.7.13-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 */;
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.
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='06729104-4808-11e6-9234-005056218eb2:1-175637';
--
-- Table structure for table `n`
--
DROP TABLE IF EXISTS `n`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `n` (
`n_nationkey` int(11) NOT NULL,
`n_name` char(25) DEFAULT NULL,
`n_regionkey` int(11) DEFAULT NULL,
`n_comment` varchar(255) DEFAULT NULL,
`n_test` varchar(255) DEFAULT NULL,
PRIMARY KEY (`n_nationkey`),
KEY `i_n_regionkey` (`n_regionkey`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `n`
--
-- WHERE: n_regionkey=1
LOCK TABLES `n` WRITE;
/*!40000 ALTER TABLE `n` DISABLE KEYS */;
INSERT INTO `n` VALUES (1,'ARGENTINA',1,'idly final instructions cajole stealthily. regular instructions wake carefully blithely express accounts. fluffi','aaa'),(2,'BRAZIL',1,'always pending pinto beans sleep sil','aaa'),(3,'CANADA',1,'foxes among the bold requests','aaa'),(17,'PERU',1,'final, final accounts sleep slyly across the requests. ','aaa'),(24,'UNITED STATES',1,'blithely regular deposits serve furiously blithely regular warthogs! slyly fi','aaa');
/*!40000 ALTER TABLE `n` 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-08-30 20:37:54
3. 恢复示例
mysql < test_backup.sql
加-f是强制跳过错误。
不要用source恢复，用source去做的时候会输出很多结果，远程连过去需要返回，而实际上有可能已经恢复完成了。
4. mysqldump的原理
[root@test-1 mdata]# mysqldump --single-transaction --master-data=1 -B mytest > mytest.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.
--master-data=1 ---- 带上binlog信息
查看dump出来的文件
-- MySQL dump 10.13 Distrib 5.7.14, for linux-glibc2.5 (x86_64)
--
-- Host: localhost Database: mytest
-- ------------------------------------------------------
-- Server version 5.7.14-log
......
--
-- GTID state at the beginning of the backup
--
SET @@GLOBAL.GTID_PURGED='713a7f7f-6f53-11e6-b7a9-000c29de5d8b:1-284349';
--
-- Position to start replication or point-in-time recovery from
--
CHANGE MASTER TO MASTER_LOG_FILE='bin.000087', MASTER_LOG_POS=643; ---- 获取2进制文件名和对应的position
--
-- Current Database: `mytest`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mytest` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
USE `mytest`;
--
-- Table structure for table `UserInfo`
--
DROP TABLE IF EXISTS `UserInfo`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `UserInfo` (
`userid` bigint(20) NOT NULL AUTO_INCREMENT,
`username` varchar(256) DEFAULT NULL,
`cellphone` varchar(16) DEFAULT NULL,
`regDate` datetime NOT NULL,
PRIMARY KEY (`userid`),
UNIQUE KEY `idx_uk_username` (`username`),
UNIQUE KEY `idx_uk_cellphone` (`cellphone`),
KEY `idx_regDate` (`regDate`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `UserInfo`
--
LOCK TABLES `UserInfo` WRITE;
/*!40000 ALTER TABLE `UserInfo` DISABLE KEYS */;
INSERT INTO `UserInfo` VALUES (2,NULL,NULL,'0000-00-00 00:00:00'),(3,'panda',NULL,'2016-10-07 18:06:33'),(4,'jack','128','2016-10-07 18:06:43'),(5,'top','138','2016-10-07 18:06:49'),(6,'back','139','2016-10-07 21:53:23'),(9,'tail','130','2016-10-07 21:53:39');
/*!40000 ALTER TABLE `UserInfo` ENABLE KEYS */;
UNLOCK TABLES;
......
mysqldump是一个二进制文件，并不是一个perl脚本之类的文件。
[root@test-1 mdata]# file /usr/local/mysql/bin/mysqldump
/usr/local/mysql/bin/mysqldump: ELF 64-bit LSB executable, x86-64, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.9, not stripped
可以通过通用日志跟踪的方法去查看mysqldump的原理。
(root@localhost) [mytest]> set global log_output = 'table';
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [mytest]> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
(root@localhost) [mysql]> truncate table general_log;
Query OK, 0 rows affected (0.01 sec)
(root@localhost) [mysql]> set global general_log = 1;
Query OK, 0 rows affected (0.00 sec)
另一个session执行导出
[root@test-1 mdata]# mysqldump --single-transaction --master-data=1 -B mytest > mytest_backup.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@localhost) [mysql]> set global general_log = 0;
Query OK, 0 rows affected (0.00 sec)
查看通用日志
(root@localhost) [mysql]> select left(argument,64) from general_log order by event_time limit 32;
+------------------------------------------------------------------+
| left(argument,64) |
+------------------------------------------------------------------+
| FLUSH /*!40101 LOCAL */ TABLES | ---- 刷新表
| FLUSH TABLES WITH READ LOCK | ---- 实例级别的读锁，因为加了--master-data=1的参数，用来在获取备份的时候获取二进制日志的file和postition, 有GTID还要获取GTID.
| SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ | ---- 会话级别设置RR事务隔离级别
| START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ | ---- 创建快照读，!40100是版本号，大于这个版本就会执行后面的
| SHOW VARIABLES LIKE 'gtid\_mode' | ---- 获得是否是GITD
| SELECT @@GLOBAL.GTID_EXECUTED | ---- 获得当前执行到的GTID的位置
| SHOW MASTER STATUS | ---- 获得filename和postition
| UNLOCK TABLES | ---- 释放实例级别的读锁，从加读锁到释放这个过程非常短
| SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZ |
| SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, |
| SHOW VARIABLES LIKE 'ndbinfo\_version' |
| mytest |
| SHOW CREATE DATABASE IF NOT EXISTS `mytest` |
| SAVEPOINT sp |
| show tables |
| show table status like 'UserInfo' | ---- 表结构
| SET SQL_QUOTE_SHOW_CREATE=1 |
| SET SESSION character_set_results = 'binary' |
| show create table `UserInfo` |
| SET SESSION character_set_results = 'utf8' |
| show fields from `UserInfo` | ---- 表的列
| show fields from `UserInfo` |
| SELECT /*!40001 SQL_NO_CACHE */ * FROM `UserInfo` | ---- 数据
| SET SESSION character_set_results = 'binary' |
| use `mytest` |
| select @@collation_database |
| SHOW TRIGGERS LIKE 'UserInfo' |
| SET SESSION character_set_results = 'utf8' |
| ROLLBACK TO SAVEPOINT sp |
| show table status like 'a' |
| SET SQL_QUOTE_SHOW_CREATE=1 |
| SET SESSION character_set_results = 'binary' |
+------------------------------------------------------------------+
32 rows in set (0.00 sec)
从上面可以看出，mysqldump的一致性读是通过innodb的快照读来实现的
