Infrastructure at your Service

William Sescu

MariaDB – Speed up your logical MariaDB backups with mydumper

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.

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

Another cool feature is compression.

./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

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

Unzip and compile mydumper

$ 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/

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.

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.

-- 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.

-- 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.

 

 

 

Leave a Reply


eight × = 64

William Sescu
William Sescu

Consultant