MySQL Back up & Restore: Faster InnoDB database backup / restore using Mydumper and Myloader multi-threaded tools

+--------+--------+----------+------------+-----------+------------+---------+
| engine | tables | rows     | data       | idx       | total_size | idxfrac |
+--------+--------+----------+------------+-----------+------------+---------+
| InnoDB |     49 | 96004641 | 20118.45MB | 1824.86MB | 21943.31MB |    0.09 |
| MyISAM |     21 | 1992     | 0.52MB     | 0.09MB    | 0.61MB     |    0.17 |
| Aria   |      8 | NULL     | 0.06MB     | 0.06MB    | 0.13MB     |    1.00 |
| MEMORY |     44 | NULL     | 0.00MB     | 0.00MB    | 0.00MB     |    NULL |
| CSV    |      2 | 4        | 0.00MB     | 0.00MB    | 0.00MB     |    NULL |
+--------+--------+----------+------------+-----------+------------+---------+

+--------+--------+----------+------------+-----------+------------+---------+ | engine | tables | rows | data | idx | total_size | idxfrac | +--------+--------+----------+------------+-----------+------------+---------+ | InnoDB | 49 | 96004641 | 20118.45MB | 1824.86MB | 21943.31MB | 0.09 | | MyISAM | 21 | 1992 | 0.52MB | 0.09MB | 0.61MB | 0.17 | | Aria | 8 | NULL | 0.06MB | 0.06MB | 0.13MB | 1.00 | | MEMORY | 44 | NULL | 0.00MB | 0.00MB | 0.00MB | NULL | | CSV | 2 | 4 | 0.00MB | 0.00MB | 0.00MB | NULL | +--------+--------+----------+------------+-----------+------------+---------+

[root@sbe tools]# ./mydumperbench.sh mydumper
----------------------------------------------------------------------------------------
mydumperbench.sh 0.0.6 by George Liu (eva2000)
http://vbtechsupport.com
Mon Jan  9 21:33:27 EST 2012
----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
[sbtest total size: 22435 MB]
 [mydumper threads: 12 ] mydumper -t 12 -B sbtest
 [backup stats:] real: 165.61s user: 86.75s sys: 31.82s cpu: 71% maxmem: 123168 KB cswaits: 1391501
 [backup speed:] 135.46 MB/s 8127.60 MB/min 476.22 GB/hr
Successfully backed database using mydumper using 12 threads
----------------------------------------------------------------------------------------
CSV format:
threads,real time,user time,system time,cpu %,max mem,context switch waits,speed (MB/s),speed (MB/min),speed (GB/hr)
12,165.61,86.75,31.82,71,123168,1391501,135.46,8127.60,476.22

[root@sbe tools]# ./mydumperbench.sh mydumper ---------------------------------------------------------------------------------------- mydumperbench.sh 0.0.6 by George Liu (eva2000) http://vbtechsupport.com Mon Jan 9 21:33:27 EST 2012 ---------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------- [sbtest total size: 22435 MB] [mydumper threads: 12 ] mydumper -t 12 -B sbtest [backup stats:] real: 165.61s user: 86.75s sys: 31.82s cpu: 71% maxmem: 123168 KB cswaits: 1391501 [backup speed:] 135.46 MB/s 8127.60 MB/min 476.22 GB/hr Successfully backed database using mydumper using 12 threads ---------------------------------------------------------------------------------------- CSV format: threads,real time,user time,system time,cpu %,max mem,context switch waits,speed (MB/s),speed (MB/min),speed (GB/hr) 12,165.61,86.75,31.82,71,123168,1391501,135.46,8127.60,476.22

[root@sbe tools]# ./mydumperbench.sh mysqlrestore
----------------------------------------------------------------------------------------
mydumperbench.sh 0.0.6 by George Liu (eva2000)
http://vbtechsupport.com
Mon Jan  9 21:39:31 EST 2012
----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
[/backup/sbtest.sql size: 18397 MB]
 [mysql threads: 1 ] mysql sbtest < SQLFILE
 [restore stats:] real: 1261.77s user: 156.27s sys: 10.75s cpu: 13% maxmem: 16032 KB cswaits: 104630
 [restore speed:] 14.58 MB/s 874.80 MB/min 51.25 GB/hr
Successfully restored mysqldump backup
----------------------------------------------------------------------------------------
CSV format:
threads,real time,user time,system time,cpu %,max mem,context switch waits,speed (MB/s),speed (MB/min),speed (GB/hr)
1,1261.77,156.27,10.75,13,16032,104630,14.58,874.80,51.25

[root@sbe tools]# ./mydumperbench.sh mysqlrestore ---------------------------------------------------------------------------------------- mydumperbench.sh 0.0.6 by George Liu (eva2000) http://vbtechsupport.com Mon Jan 9 21:39:31 EST 2012 ---------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------- [/backup/sbtest.sql size: 18397 MB] [mysql threads: 1 ] mysql sbtest < SQLFILE [restore stats:] real: 1261.77s user: 156.27s sys: 10.75s cpu: 13% maxmem: 16032 KB cswaits: 104630 [restore speed:] 14.58 MB/s 874.80 MB/min 51.25 GB/hr Successfully restored mysqldump backup ---------------------------------------------------------------------------------------- CSV format: threads,real time,user time,system time,cpu %,max mem,context switch waits,speed (MB/s),speed (MB/min),speed (GB/hr) 1,1261.77,156.27,10.75,13,16032,104630,14.58,874.80,51.25

[root@sbe tools]# ./mydumperbench.sh myloader
----------------------------------------------------------------------------------------
mydumperbench.sh 0.0.6 by George Liu (eva2000)
http://vbtechsupport.com
Mon Jan  9 22:03:03 EST 2012
----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
[export-20120109-213431 total size: 18528 MB]
 [myloader threads: 12 ] myloader -t 12 -d export-20120109-213431 -B sbtest -q 10000
 [restore stats:] real: 343.62s user: 21.48s sys: 11.43s cpu: 9% maxmem: 124816 KB cswaits: 73064
 [restore speed:] 53.92 MB/s 3235.20 MB/min 189.56 GB/hr
Successfully restored mydumper backup using 12 threads
----------------------------------------------------------------------------------------
CSV format:
threads,real time,user time,system time,cpu %,max mem,context switch waits,speed (MB/s),speed (MB/min),speed (GB/hr)
12,343.62,21.48,11.43,9,124816,73064,53.92,3235.20,189.56

[root@sbe tools]# ./mydumperbench.sh myloader ---------------------------------------------------------------------------------------- mydumperbench.sh 0.0.6 by George Liu (eva2000) http://vbtechsupport.com Mon Jan 9 22:03:03 EST 2012 ---------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------- [export-20120109-213431 total size: 18528 MB] [myloader threads: 12 ] myloader -t 12 -d export-20120109-213431 -B sbtest -q 10000 [restore stats:] real: 343.62s user: 21.48s sys: 11.43s cpu: 9% maxmem: 124816 KB cswaits: 73064 [restore speed:] 53.92 MB/s 3235.20 MB/min 189.56 GB/hr Successfully restored mydumper backup using 12 threads ---------------------------------------------------------------------------------------- CSV format: threads,real time,user time,system time,cpu %,max mem,context switch waits,speed (MB/s),speed (MB/min),speed (GB/hr) 12,343.62,21.48,11.43,9,124816,73064,53.92,3235.20,189.56