Install Mydumper and Myloader Software for Backup of MySQL / MariaDB Databases and Usage of commands

Mydumper and Myloader Installation 

----------------------------------

Comparison of MySQL backup tools:

1. mysqldump: belongs to logical backup, there will be lock tables, but considering that the amount of data is relatively large, the time to lock the tables will be longer, business is not allowed...

2. xtrabackup: It is a physical backup, and there is no lock table, but considering that the two DBs use shared table spaces, and when the database of business B is restored, one is that the time is relatively long, and the other is that the data is definitely incorrect....

3. mydumper: belongs to logical backup. It is a multi-threaded, high-performance data logical backup and recovery tool, and the lock table time is very short (40G data, within 10 minutes), and it will record binlog file and position at the same time....

Mydumper & Myloader installation in RHEL for MariaDB / MySQL Backups utilities - Third party Tools

--------------------------------------------------------------------

Mydumper and Myloader are the 3rd party tool for taking a backup of MYSQL / MariaDB Databases.

Export:

--------

MySQLDump which is single threaded and depending on the data size it can take hours to export the data from Source.

MyDumper is Percona toolkit which has multi-threaded functionality which is recommended for large databases. 

Import:

--------

MySQL to import the data which is again a single threaded and depending on the data size it can take hours to impor the data to target.

MyLoader is Percona toolkit which has muti-threaded functionality which s recommended for large databases.

With MyDumper the table data is exported to its own file automatically, so even when database has 30K tables we end up with 30K files.Each file can be loaded using MyLoader.

1st Method :- Copying the s/w from local machine to Linux Server .

--------------------------------------------------------------------

This blog is belongs to install / Configure Mydumper and Myloader Package .

We can get required version of mydumper software from below link..

https://github.com/maxbube/mydumper/releases

The above link for RPM package download and install...

[root@hostname ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.2 (Santiago)

[root@hostname ~]# cd /home/vasu/

[root@hostname vasu]# pwd
/home/vasu

[root@hostname vasu]# ls -ltr
-rw-r--r--.  1 root root     44463 Nov  6  2015 mydumper-0.9.1.tar.gz
drwxrwxr-x. 11 1000 1000      4096 Jan 14 07:58 percona-toolkit-3.3.0
-rw-r--r--.  1 root root 128465829 Jan 14 07:59 percona-toolkit-3.3.0.tar.gz
-rw-rw-r--.  1 vasu vasu   1659540 Apr 20 06:49 mydumper-0.9.5-2.el6.x86_64.rpm
drwxr-xr-x.  5 root root      4096 Apr 20 07:03 mydumper-0.9.1

[root@hostname vasu]# rpm -ivh mydumper-0.9.5-2.el6.x86_64.rpm

Preparing...

              ########################################### [100%]

   1:mydumper ########################################### [100%]

[root@hostname vasu]# cd

[root@hostname ~]# mydumper --version
mydumper 0.9.5, built against MySQL 5.7.21-21

[root@hostname ~]# myloader --version
myloader 0.9.5, built against MySQL 5.7.21-21

Or else 

You can use directely download in Linux Server by using "wget" Command.

wget https://github.com/maxbube/mydumper/releases/download/v0.11.1-3/mydumper-0.11.1-3.el7.x86_64.rpm

2nd Method :- By using TAR file install.

------------------------------------------

But the below procedure for tar file download and install the mydumper and myloader 

There is some dependency packages to install mydumper and myloader 

[root@hostname ~]# cd /home/vasu/

[root@hostname vasu]# pwd
/home/vasu

[root@hostname vasu]# wget https://launchpad.net/mydumper/0.9/0.9.1/+download/mydumper-0.9.1.tar.gz

[root@hostname vasu]# tar -xvf mydumper-0.9.1.tar.gz

[root@hostname vasu]# ls -ltr
-rw-r--r--.  1 root root     44463 Nov  6  2015 mydumper-0.9.1.tar.gz
drwxrwxr-x. 11 1000 1000      4096 Jan 14 07:58 percona-toolkit-3.3.0
-rw-r--r--.  1 root root 128465829 Jan 14 07:59 percona-toolkit-3.3.0.tar.gz
drwxr-xr-x.  4 root root      4096 Apr 20 06:32 mydumper-0.9.1

[root@hostname vasu]# cd mydumper-0.9.1

[root@hostname mydumper-0.9.1]# ls -ltr
-rw-r--r--. 1 root root  1116 Nov  6  2015 server_detect.h
-rw-r--r--. 1 root root  2021 Nov  6  2015 server_detect.c
-rw-r--r--. 1 root root  2244 Nov  6  2015 README
-rw-r--r--. 1 root root  1284 Nov  6  2015 myloader.h
-rw-r--r--. 1 root root 16630 Nov  6  2015 myloader.c
-rw-r--r--. 1 root root  2169 Nov  6  2015 mydumper.h
-rw-r--r--. 1 root root 92941 Nov  6  2015 mydumper.c
-rw-r--r--. 1 root root   339 Nov  6  2015 g_unix_signal.h
-rw-r--r--. 1 root root  3614 Nov  6  2015 g_unix_signal.c
-rw-r--r--. 1 root root   101 Nov  6  2015 config.h.in
-rw-r--r--. 1 root root  2081 Nov  6  2015 common.h
-rw-r--r--. 1 root root  2339 Nov  6  2015 CMakeLists.txt
-rw-r--r--. 1 root root  1244 Nov  6  2015 binlog.h
-rw-r--r--. 1 root root  8171 Nov  6  2015 binlog.c
drwxr-xr-x. 3 root root  4096 Apr 20 06:32 cmake
drwxr-xr-x. 4 root root  4096 Apr 20 06:32 docs

[root@hostname mydumper-0.9.1]# cd cmake/

[root@hostname cmake]# ls -ltr
drwxr-xr-x. 2 root root 4096 Nov  6  2015 modules

[root@hostname cmake]# cd

[root@hostname ~]# cmake .
-bash: cmake: command not found

[root@hostname ~]# yum install cmake

[root@hostname ~]# cd /home/vasu/mydumper-0.9.1

[root@hostname mydumper-0.9.1]# pwd
/home/vasu/mydumper-0.9.1

[root@hostname mydumper-0.9.1]# ls -ltr
-rw-r--r--. 1 root root  1116 Nov  6  2015 server_detect.h
-rw-r--r--. 1 root root  2021 Nov  6  2015 server_detect.c
-rw-r--r--. 1 root root  2244 Nov  6  2015 README
-rw-r--r--. 1 root root  1284 Nov  6  2015 myloader.h
-rw-r--r--. 1 root root 16630 Nov  6  2015 myloader.c
-rw-r--r--. 1 root root  2169 Nov  6  2015 mydumper.h
-rw-r--r--. 1 root root 92941 Nov  6  2015 mydumper.c
-rw-r--r--. 1 root root   339 Nov  6  2015 g_unix_signal.h
-rw-r--r--. 1 root root  3614 Nov  6  2015 g_unix_signal.c
-rw-r--r--. 1 root root   101 Nov  6  2015 config.h.in
-rw-r--r--. 1 root root  2081 Nov  6  2015 common.h
-rw-r--r--. 1 root root  2339 Nov  6  2015 CMakeLists.txt
-rw-r--r--. 1 root root  1244 Nov  6  2015 binlog.h
-rw-r--r--. 1 root root  8171 Nov  6  2015 binlog.c
drwxr-xr-x. 3 root root  4096 Apr 20 06:32 cmake
drwxr-xr-x. 4 root root  4096 Apr 20 06:32 docs

[root@hostname mydumper-0.9.1]# cmake .
-- The C compiler identification is GNU 4.4.7
-- The CXX compiler identification is GNU 4.4.7
-- 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: /usr/local/mysql/bin/mysql_config
-- Found MySQL: /usr/local/mariadb-10.3.26-linux-x86_64/include/mysql, /usr/local/mariadb-10.3.26-linux-x86_64/lib/libmariadb.so
-- Found ZLIB: /usr/lib64/libz.so (found version "1.2.3")
-- Found PkgConfig: /usr/bin/pkg-config (found version "0.23")
-- checking for one of the modules 'glib-2.0'
-- checking for one of the modules 'gthread-2.0'
-- checking for module 'libpcre'
--   package 'libpcre' not found
CMake Error at /usr/share/cmake/Modules/FindPkgConfig.cmake:279 (message):
  A required package was not found
Call Stack (most recent call first):
  /usr/share/cmake/Modules/FindPkgConfig.cmake:333 (_pkg_check_modules_internal)
  cmake/modules/FindPCRE.cmake:25 (pkg_check_modules)
  CMakeLists.txt:11 (find_package)

-- Could NOT find PCRE (missing:  PCRE_INCLUDE_DIR PCRE_PCRE_LIBRARY PCRE_PCREPOSIX_LIBRARY)
CMake Warning at docs/CMakeLists.txt:9 (message):
  Unable to find Sphinx documentation generator

-- ------------------------------------------------
-- MYSQL_CONFIG = /usr/local/mysql/bin/mysql_config
-- CMAKE_INSTALL_PREFIX = /usr/local
-- BUILD_DOCS = ON
-- WITH_BINLOG = OFF
-- RUN_CPPCHECK = OFF
-- Change a values with: cmake -D<Variable>=<Value>
-- ------------------------------------------------
--
CMake Error: The following variables are used in this project, but they are set to NOTFOUND.
Please set them or make sure they are set and tested correctly in the CMake files:
PCRE_INCLUDE_DIR (ADVANCED)
   used as include directory in directory /home/vasu/mydumper-0.9.1
   used as include directory in directory /home/vasu/mydumper-0.9.1
   used as include directory in directory /home/vasu/mydumper-0.9.1
PCRE_PCRE_LIBRARY (ADVANCED)
    linked by target "mydumper" in directory /home/vasu/mydumper-0.9.1
    linked by target "myloader" in directory /home/vasu/mydumper-0.9.1
-- Configuring incomplete, errors occurred!
See also "/home/vasu/mydumper-0.9.1/CMakeFiles/CMakeOutput.log".
Error .............
We should install the below pacakges 

[root@hostname mydumper-0.9.1]# yum search libpcre
==================================================================== Matched: libpcre =====================================================================
pcre.x86_64 : Perl-compatible regular expression library
pcre.i686 : Perl-compatible regular expression library
[root@hostname mydumper-0.9.1]# yum install pcre.x86_64
yum install gcc-c++
[root@hostname mydumper-0.9.1]# yum install pcre.i686
[root@hostname mydumper-0.9.1]# rpm -qa | grep -i glib2-devel
glib2-devel-2.22.5-6.el6.x86_64
[root@hostname mydumper-0.9.1]# rpm -qa | grep -i mysql-devel
[root@hostname mydumper-0.9.1]# rpm -qa | grep -i zlib-devel
zlib-devel-1.2.3-29.el6.x86_64
[root@hostname mydumper-0.9.1]# rpm -qa | grep -i pcre-devel
[root@hostname mydumper-0.9.1]# yum search pcre-devel
============================================================= N/S Matched: pcre-devel ==============================================================
pcre-devel.i686 : Development files for pcre
pcre-devel.x86_64 : Development files for pcre
  Name and summary matches only, use "search all" for everything.

[root@hostname mydumper-0.9.1]# yum install pcre-devel.x86_64

[root@hostname mydumper-0.9.1]# pwd
/home/vasu/mydumper-0.9.1

[root@hostname mydumper-0.9.1]# ls -ltr
-rw-r--r--. 1 root root  1116 Nov  6  2015 server_detect.h
-rw-r--r--. 1 root root  2021 Nov  6  2015 server_detect.c
-rw-r--r--. 1 root root  2244 Nov  6  2015 README
-rw-r--r--. 1 root root  1284 Nov  6  2015 myloader.h
-rw-r--r--. 1 root root 16630 Nov  6  2015 myloader.c
-rw-r--r--. 1 root root  2169 Nov  6  2015 mydumper.h
-rw-r--r--. 1 root root 92941 Nov  6  2015 mydumper.c
-rw-r--r--. 1 root root   339 Nov  6  2015 g_unix_signal.h
-rw-r--r--. 1 root root  3614 Nov  6  2015 g_unix_signal.c
-rw-r--r--. 1 root root   101 Nov  6  2015 config.h.in
-rw-r--r--. 1 root root  2081 Nov  6  2015 common.h
-rw-r--r--. 1 root root  2339 Nov  6  2015 CMakeLists.txt
-rw-r--r--. 1 root root  1244 Nov  6  2015 binlog.h
-rw-r--r--. 1 root root  8171 Nov  6  2015 binlog.c
drwxr-xr-x. 3 root root  4096 Apr 20 06:32 cmake
drwxr-xr-x. 5 root root  4096 Apr 20 06:51 docs
-rw-r--r--. 1 root root    92 Apr 20 06:51 config.h
-rw-r--r--. 1 root root 18693 Apr 20 06:51 CMakeCache.txt
drwxr-xr-x. 4 root root  4096 Apr 20 06:54 CMakeFiles

[root@hostname mydumper-0.9.1]# cmake .
-- Using mysql-config: /usr/local/mysql/bin/mysql_config
-- Found MySQL: /usr/local/mariadb-10.3.26-linux-x86_64/include/mysql, /usr/local/mariadb-10.3.26-linux-x86_64/lib/libmariadb.so
-- checking for module 'libpcre'
--   found libpcre, version 7.8
-- Found PCRE: /usr/include
CMake Warning at docs/CMakeLists.txt:9 (message):
  Unable to find Sphinx documentation generator

-- ------------------------------------------------
-- MYSQL_CONFIG = /usr/local/mysql/bin/mysql_config
-- CMAKE_INSTALL_PREFIX = /usr/local
-- 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: /home/vasu/mydumper-0.9.1

[root@hostname mydumper-0.9.1]# make
Scanning dependencies of target mydumper
[ 25%] Building C object CMakeFiles/mydumper.dir/mydumper.c.o
[ 50%] Building C object CMakeFiles/mydumper.dir/server_detect.c.o
[ 75%] Building C object CMakeFiles/mydumper.dir/g_unix_signal.c.o
Linking C executable mydumper
[ 75%] Built target mydumper
Scanning dependencies of target myloader
[100%] Building C object CMakeFiles/myloader.dir/myloader.c.o
Linking C executable myloader
[100%] Built target myloader

[root@hostname mydumper-0.9.1]# ./mydumper --version

mydumper 0.9.1, built against MySQL 10.3.26-MariaDB

[root@hostname mydumper-0.9.1]# ./myloader --version
myloader 0.9.1, built against MySQL 10.3.26-MariaDB

Commands :-

-----------

Mydumper Full DB [ All Schema's ] backup and myloader for restore :-

mydumper --host=localhost --user=root --password='R00t@123' --outputdir=/home/vagrant/mydumper_backup --compress --threads=4 --compress-protocol --no-backup-locks --statement-size=100000000 -G -E -R

myloader --host=localhost --user=root --password='R00t@123' --threads=8 -d /home/vagrant/mydumper_backup -o -v 3

Single Table Backup by using mydumper :-

mydumper --host=localhost --user=root --ask-password -B <DB_NAME> -T <Table_Name> --outputdir=/mysql/mydumper_backup --no- views --compress --threads=4 --compress-protocol --no-backup-locks --less-locking --statement-size=100000000 --logfile /mysql/mydumper_table_bkp.log -G -E -R

time myloader --threads=4 -d /mysql/mydumper_backup/ -B <db_name> -q 100 -o -v 3 

Backup Single Schema :-

mydumper --host=source --user=admin_user --host=hostname --password=Secret --database=database_name --outputdir=

/home/database_name/backup --no-views --compress --build-empty-files --threads=4 --compress-protocol --no-backup-locks --less-locking --statement-size=100000000 -G -E -R -k

time myloader -h target -u admin_user -p Secret --database=dbname -t 5 -d /home/database_name/backup

Note :- Here we can create any folder for Backup path.

                Thank you for visiting my blog.....