更新时间:2022-09-27 23:14:06
081022 17:39:33 7 Connect root@localhost on 7 Query /*!40100 SET @@SQL_MODE='' */ 7 Init DB yejr 7 Query SHOW TABLES LIKE 'yejr' 7 Query LOCK TABLES `yejr` READ /*!32311 LOCAL */ 7 Query SET OPTION SQL_QUOTE_SHOW_CREATE=1 7 Query show create table `yejr` 7 Query show fields from `yejr` 7 Query show table status like 'yejr' 7 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `yejr` 7 Query UNLOCK TABLES 7 Quit
081022 17:36:21 5 Connect root@localhost on 5 Query /*!40100 SET @@SQL_MODE='' */ 5 Init DB yejr 5 Query SHOW TABLES LIKE 'yejr' 5 Query LOCK TABLES `yejr` READ /*!32311 LOCAL */ 5 Query SET OPTION SQL_QUOTE_SHOW_CREATE=1 5 Query show create table `yejr` 5 Query show fields from `yejr` 5 Query show table status like 'yejr' 5 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `yejr` 5 Query UNLOCK TABLES 5 Quit
081022 17:36:55 6 Connect root@localhost on 6 Query /*!40100 SET @@SQL_MODE='' */ 6 Query FLUSH TABLES 6 Query FLUSH TABLES WITH READ LOCK 6 Init DB yejr 6 Query SHOW TABLES LIKE 'yejr' 6 Query SET OPTION SQL_QUOTE_SHOW_CREATE=1 6 Query show create table `yejr` 6 Query show fields from `yejr` 6 Query show table status like 'yejr' 6 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `yejr` 6 Quit
081022 17:59:02 1 Connect root@localhost on 1 Query /*!40100 SET @@SQL_MODE='' */ 1 Query FLUSH TABLES 1 Query FLUSH TABLES WITH READ LOCK 1 Query SHOW MASTER STATUS 1 Init DB yejr 1 Query SHOW TABLES LIKE 'yejr' 1 Query SET OPTION SQL_QUOTE_SHOW_CREATE=1 1 Query show create table `yejr` 1 Query show fields from `yejr` 1 Query show table status like 'yejr' 1 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `yejr` 1 Quit
081022 17:23:35 1 Connect root@localhost on 1 Query /*!40100 SET @@SQL_MODE='' */ 1 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 1 Query BEGIN 1 Query UNLOCK TABLES 1 Init DB yejr 1 Query SHOW TABLES LIKE 'yejr' 1 Query SET OPTION SQL_QUOTE_SHOW_CREATE=1 1 Query show create table `yejr` 1 Query show fields from `yejr` 1 Query show table status like 'yejr' 1 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `yejr` 1 Quit
081022 17:27:07 2 Connect root@localhost on 2 Query /*!40100 SET @@SQL_MODE='' */ 2 Query FLUSH TABLES 2 Query FLUSH TABLES WITH READ LOCK 2 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 2 Query START TRANSACTION WITH CONSISTENT SNAPSHOT 2 Query SHOW MASTER STATUS 2 Query UNLOCK TABLES 2 Init DB yejr 2 Query SHOW TABLES LIKE 'yejr' 2 Query SET OPTION SQL_QUOTE_SHOW_CREATE=1 2 Query show create table `yejr` 2 Query show fields from `yejr` 2 Query show table status like 'yejr' 2 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `yejr` 2 Quit
The WITH CONSISTENT SNAPSHOT clause starts a consistent read for storage engines that are capable of it. This applies only to InnoDB. The effect is the same as issuing a START TRANSACTION followed by a SELECT from any InnoDB table. See Section 13.2.10.4, “Consistent Non-Locking Read”. The WITH CONSISTENT SNAPSHOT clause does not change the current transaction isolation level, so it provides a consistent snapshot only if the current isolation level is one that allows consistent read (REPEATABLE READ or SERIALIZABLE).
#导出耗时 time mysqldump -f --single-transaction -B yejr --tables yejr | gzip > /home/databak/yejr.sql.gz real 10m15.319s user 6m47.946s sys 0m38.496s #文件大小 608M /home/databak/yejr.sql.gz #导出期间系统负载 05:00:01 PM all 0.71 0.00 0.61 7.33 91.36 05:10:02 PM all 13.93 0.00 2.21 4.64 79.22
#导出耗时 time mysqldump -f --single-transaction -B yejr --tables yejr | gzip --fast > /home/databak/yejr_fast.sql.gz real 9m6.248s user 4m21.467s sys 0m37.604s #文件大小 815M Oct 21 17:33 /home/databak/yejr_fast.sql.gz #导出期间系统负载 05:20:01 PM all 11.94 0.00 2.43 5.69 79.94 05:30:01 PM all 6.46 0.00 1.57 3.95 88.02
time ./mk-parallel-dump --database yejr --tables yejr --basedir /home/databak/ default: 25 tables, 25 chunks, 25 successes, 0 failures, 404.93 wall-clock time, 613.25 dump time real 6m48.763s user 4m20.724s sys 0m38.125s #文件大小 819M /home/databak/default/yejr/ #导出期间系统负载 05:10:02 PM all 13.93 0.00 2.21 4.64 79.22 05:20:01 PM all 11.94 0.00 2.43 5.69 79.94