By William Sescu

Per default, MariaDB is shipped with a utility called mysqldump for logical backups. For more information, please take a look at the following link.

https://mariadb.com/kb/en/mariadb/mysqldump/

The mysqldump has advantages, e.g. it is easy to use and it is shipped with the standard MariaDB installation.  So, no additional installation is needed. However, it has also some disadvantages. E.g. it is single threaded and it is  writing to one big file, even with the latest version which is MariaDB 10.2.7 at the moment.

In case you want to dump out your data very quickly this can be your bottleneck. This is where the mydumper comes into play. The main feature of mydumper is that you can parallelize it. The mydumper utility uses 4 parallel threads per default if not otherwise specified.

1
2
./mydumper --help | grep threads
  -t, --threads               Number of threads to use, default 4

Another cool feature is compression.

1
2
./mydumper --help | grep compress
  -c, --compress              Compress output files

The biggest disadvantage is that mydumper is not delivered out of the box. You have to compile it yourself. To do so, simply follow the following steps:

Install the packages, which are needed for the mydumper compilation

1
2
# yum install gcc gcc-c++ glib2-devel mysql-devel zlib-devel \
  pcre-devel openssl-devel cmake

Unzip and compile mydumper

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
$ unzip mydumper-master.zip
mysql@mysql01:/u00/app/mysql/product/tools/ [mysqld1] unzip mydumper-master.zip
Archive:  mydumper-master.zip
e643528321f51e21a463156fbf232448054b955d
   creating: mydumper-master/
  inflating: mydumper-master/.bzrignore
  inflating: mydumper-master/CMakeLists.txt
  inflating: mydumper-master/README
  inflating: mydumper-master/binlog.c
  inflating: mydumper-master/binlog.h
   creating: mydumper-master/cmake/
   creating: mydumper-master/cmake/modules/
  inflating: mydumper-master/cmake/modules/CppcheckTargets.cmake
  inflating: mydumper-master/cmake/modules/FindGLIB2.cmake
  inflating: mydumper-master/cmake/modules/FindMySQL.cmake
  inflating: mydumper-master/cmake/modules/FindPCRE.cmake
  inflating: mydumper-master/cmake/modules/FindSphinx.cmake
  inflating: mydumper-master/cmake/modules/Findcppcheck.cmake
  inflating: mydumper-master/cmake/modules/Findcppcheck.cpp
  inflating: mydumper-master/common.h
  inflating: mydumper-master/config.h.in
   creating: mydumper-master/docs/
  inflating: mydumper-master/docs/CMakeLists.txt
   creating: mydumper-master/docs/_build/
  inflating: mydumper-master/docs/_build/conf.py.in
  inflating: mydumper-master/docs/_build/sources.cmake.in
  inflating: mydumper-master/docs/authors.rst
  inflating: mydumper-master/docs/compiling.rst
  inflating: mydumper-master/docs/examples.rst
  inflating: mydumper-master/docs/files.rst
  inflating: mydumper-master/docs/index.rst
  inflating: mydumper-master/docs/mydumper_usage.rst
  inflating: mydumper-master/docs/myloader_usage.rst
  inflating: mydumper-master/g_unix_signal.c
  inflating: mydumper-master/g_unix_signal.h
  inflating: mydumper-master/mydumper.c
  inflating: mydumper-master/mydumper.h
  inflating: mydumper-master/myloader.c
  inflating: mydumper-master/myloader.h
  inflating: mydumper-master/server_detect.c
  inflating: mydumper-master/server_detect.h
 
mysql@mysql01:/u00/app/mysql/product/tools/ [mysqld1] mv mydumper-master mydumper-0.9.2
mysql@mysql01:/u00/app/mysql/product/tools/ [mysqld1] cd mydumper-0.9.2
mysql@mysql01:/u00/app/mysql/product/tools/mydumper-0.9.2/ [mysqld1] cmake . -DCMAKE_INSTALL_PREFIX=/u00/app/mysql/product/tools/mydumper-0.9.2
-- The C compiler identification is GNU 4.8.5
-- The CXX compiler identification is GNU 4.8.5
-- Check for working C compiler: /usr/bin/cc
-- Check for working C compiler: /usr/bin/cc -- works
-- Detecting C compiler ABI info
-- Detecting C compiler ABI info - done
-- Check for working CXX compiler: /usr/bin/c++
-- Check for working CXX compiler: /usr/bin/c++ -- works
-- Detecting CXX compiler ABI info
-- Detecting CXX compiler ABI info - done
-- Using mysql-config: /u00/app/mysql/product/mysql-5.6.37/bin/mysql_config
-- Found MySQL: /u00/app/mysql/product/mysql-5.6.37/include, /u00/app/mysql/product/mysql-5.6.37/lib/libmysqlclient.so;/usr/lib64/libpthread.so;/usr/lib64/libm.so;/usr/lib64/librt.so;/usr/lib64/libdl.so
-- Found ZLIB: /usr/lib64/libz.so (found version "1.2.7")
-- Found PkgConfig: /usr/bin/pkg-config (found version "0.27.1")
-- checking for one of the modules 'glib-2.0'
-- checking for one of the modules 'gthread-2.0'
-- checking for module 'libpcre'
--   found libpcre, version 8.32
-- Found PCRE: /usr/include
1
-- ------------------------------------------------
-- MYSQL_CONFIG = /u00/app/mysql/product/mysql-5.6.37/bin/mysql_config
-- CMAKE_INSTALL_PREFIX = /u00/app/mysql/product/tools/mydumper-0.9.2
-- BUILD_DOCS = ON
-- WITH_BINLOG = OFF
-- RUN_CPPCHECK = OFF
-- Change a values with: cmake -D<Variable>=<Value>
-- ------------------------------------------------
--
-- Configuring done
-- Generating done
-- Build files have been written to: /u00/app/mysql/product/tools/mydumper-0.9.2

HINT: In case you don’t have Sphinx installed, you can use the -DBUILD_DOCS=OFF option. Sphinx is a documentation generator. For more information see http://sphinx-doc.org/

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
mysql@mysql01:/u00/app/mysql/product/tools/mydumper-0.9.2/ [mysqld1] make
Scanning dependencies of target mydumper
[ 16%] Building C object CMakeFiles/mydumper.dir/mydumper.c.o
[ 33%] Building C object CMakeFiles/mydumper.dir/server_detect.c.o
[ 50%] Building C object CMakeFiles/mydumper.dir/g_unix_signal.c.o
Linking C executable mydumper
[ 50%] Built target mydumper
Scanning dependencies of target myloader
[ 66%] Building C object CMakeFiles/myloader.dir/myloader.c.o
Linking C executable myloader
[ 66%] Built target myloader
Scanning dependencies of target doc_sources
[ 66%] Built target doc_sources
Scanning dependencies of target doc_html
[ 83%] Building HTML documentation with Sphinx
/u00/app/mysql/product/tools/mydumper-0.9.2/docs/_sources/files.rst:39: WARNING: unknown option: mydumper --schemas
WARNING: html_static_path entry '/u00/app/mysql/product/tools/mydumper-0.9.2/docs/_static' does not exist
[ 83%] Built target doc_html
Scanning dependencies of target doc_man
[100%] Building manual page with Sphinx
[100%] Built target doc_man
 
mysql@mysql01:/u00/app/mysql/product/tools/mydumper-0.9.2/ [mysqld1] make install
[ 50%] Built target mydumper
[ 66%] Built target myloader
[ 66%] Built target doc_sources
[ 83%] Building HTML documentation with Sphinx
[ 83%] Built target doc_html
[100%] Building manual page with Sphinx
[100%] Built target doc_man
Install the project...
-- Install configuration: ""
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/bin/mydumper
-- Removed runtime path from "/u00/app/mysql/product/tools/mydumper-0.9.2/bin/mydumper"
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/bin/myloader
-- Removed runtime path from "/u00/app/mysql/product/tools/mydumper-0.9.2/bin/myloader"
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/authors.rst
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/compiling.rst
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/examples.rst
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/files.rst
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/index.rst
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/mydumper_usage.rst
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/myloader_usage.rst
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/authors.html
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_sources
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_sources/authors.txt
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_sources/compiling.txt
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_sources/examples.txt
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_sources/files.txt
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_sources/index.txt
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_sources/mydumper_usage.txt
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_sources/myloader_usage.txt
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/compiling.html
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/examples.html
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/files.html
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/index.html
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/mydumper_usage.html
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/myloader_usage.html
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/genindex.html
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/search.html
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_static
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_static/pygments.css
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_static/ajax-loader.gif
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_static/basic.css
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_static/comment-bright.png
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_static/comment-close.png
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_static/comment.png
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_static/doctools.js
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_static/down-pressed.png
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_static/down.png
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_static/file.png
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_static/jquery-1.11.1.js
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_static/jquery.js
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_static/minus.png
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_static/plus.png
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_static/searchtools.js
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_static/underscore-1.3.1.js
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_static/underscore.js
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_static/up-pressed.png
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_static/up.png
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_static/websupport.js
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_static/classic.css
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_static/sidebar.js
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_static/default.css
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/.buildinfo
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/searchindex.js
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/objects.inv
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/man/man1/mydumper.1
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/man/man1/myloader.1
mysql@mysql01:/u00/app/mysql/product/tools/mydumper-0.9.2/ [mysqld1]
 
mysql@mysql01:/u00/app/mysql/product/tools/ [mysqld1] ln -s mydumper-0.9.2 mydumper
mysql@mysql01:/u00/app/mysql/product/tools/ [mysqld1]

If compiled correctly, you will see two new binaries created. The mydumper and the myloader.

1
2
3
4
mysql@mysql01:/u00/app/mysql/product/tools/mydumper/bin/ [mysqld1] ls -l
total 280
-rwxr-xr-x 1 mysql mysql 218808 Aug  7 07:25 mydumper
-rwxr-xr-x 1 mysql mysql  63448 Aug  7 07:25 myloader

And besides that, you will have the documentation compiled as html in the ../mydumper-0.9.2/share/doc/mydumper/html folder.

MyDumper HTML

Ok. Let’s see now mysqldump vs. mydumper in action. My sample database is about 10G in size. Of course, the bigger the database is, the bigger the performance impact of mydumper will be.

First, we dump out all databases with mysqldump (without and with compression) and record the time.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- no compression
 
mysql@mysql01:/u00/app/mysql/ [mysqld1] mysqldump --version
mysqldump  Ver 10.16 Distrib 10.2.7-MariaDB, for Linux (x86_64)
 
mysql@mysql01:/u00/app/mysql/ [mysqld1] time mysqldump --defaults-file=/u00/app/mysql/admin/mysqld1/.my.cnf --single-transaction --all-databases > mysqldump.sql
 
real    3m38.94s
user    1m29.11s
sys     0m11.85s
 
mysql@mysql01:/u00/app/mysql/ [mysqld1] ls -lh mysqldump.sql    
-rw-r--r-- 1 mysql mysql 10G Aug  7 11:33 mysqldump.sql
 
-- compression
 
mysql@mysql01:/u00/app/mysql/ [mysqld1] time mysqldump --defaults-file=/u00/app/mysql/admin/mysqld1/.my.cnf --single-transaction --all-databases | gzip > mysqldump.sql.gz
 
real    4m43.75s
user    4m55.25s
sys     0m10.65s
 
mysql@mysql01:/u00/app/mysql/ [mysqld1] ls -lh mysqldump.sql.gz
-rw-r--r-- 1 mysql mysql 3.1G Aug  7 11:55 mysqldump.sql.gz

The uncompressed dump took about 3.39 Minute (10G) and the compressed one about 4.44 Minute (3.1G).

Now we repeat it with mydumper.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- no compression
 
mysql@mysql01:/u00/app/mysql/ [mysqld1] time /u00/app/mysql/product/tools/mydumper/bin/mydumper --defaults-file=/u00/app/mysql/admin/mysqld1/.my.cnf --trx-consistency-only --threads=6 --outputdir=/mydump/mysqld1/mydumper_mysqld1
 
real    1m22.44s
user    0m41.17s
sys     0m7.31s
 
mysql@mysql01:/u00/app/mysql/ [mysqld1] du -hs /mydump/mysqld1/mydumper_mysqld1/
10G     mydumper_mysqld1/
 
-- compression
 
mysql@mysql01:/u00/app/mysql/ [mysqld1] time /u00/app/mysql/product/tools/mydumper/bin/mydumper --defaults-file=/u00/app/mysql/admin/mysqld1/.my.cnf --trx-consistency-only --threads=6 --compress --outputdir=/mydump/mysqld1/mydumper_mysqld1
 
real    3m4.99s
user    3m54.94s
sys     0m5.11s
 
mysql@mysql01:/u00/app/mysql/ [mysqld1] du -hs /mydump/mysqld1/mydumper_mysqld1/
3.1G    mydumper_mysqld1/

With mydumper, the uncompressed dump took about 1.23 Minute (10G) and the compressed one about 3.04 Minute (3.1G).

As you can see in the results, the uncompressed dump was about 3 times faster with mydumper. The compressed mydumper export only about 30% faster. The reason for the compressed export being only 30% faster might be due to the fact that I have only 2 virtual cpu’s assigned to my VM.

Conclusion

MyDumper is a great tool that can speed up your database exports quite dramatically. Take a look at it. It might be worth it.

 

 

Post Views: 1,712
Thumbnail [60x60]
by
Oracle Team