Mysql Logical Backup using mydumper – Export
In this article, we are going to demonstrate Mysql Logical Backup using mydumper – Export.
There are several ways to take the backup of MySQL data.
Those are: –
1) Physical Backup
2) Logical Backup
Physical Backup :
It copies the directories and its files that have database contents. Physical backup is also called Raw Backup.
Logical Backup:
It converts all the available rows into single or multiple inserts and also contains create statements to create databases, tables, triggers, and so on. Logical backup also called Text Backup.
List of utilities available for logical backup: –
A) Mysqldump Click here to read more
B) Mysqlpump Click here to read more
C) Mydumper
D) Mysqlshell Click here to read more
mydumper :
Mydumper/Myloader is not available by default. We need to download it from Github and install it. Generally, it will be useful for very big tables not for the smaller ones.
The utilities like mysqldump/mysqlpump use a single thread at the time of dumpfile import. But mydumper has an additional toolset called myloader and it comes along with mydumper package. Myloader uses multiple threads to speed up the data restoration/recovery process.
Click here to download mydumper from Github, Since I am using OEL 7.10 I will download version 7. Please refer to the below image. Once rpm is downloaded transfer to the mysql server and install using rpm command.
[root@test-machine01 tmp]# rpm -ivh mydumper-0.9.5-2.el7.x86_64.rpm
Preparing... ################################# [100%]
Updating / installing...
1:mydumper-0.9.5-2 ################################# [100%]
[root@test-machine01 tmp]#
After mydumper is installed we have to create a new user account to access mydumper utility as if we try to access it with the root account will receive the below error. I will use an admin account
[root@test-machine01 tmp]# mydumper
** (mydumper:83956): CRITICAL **: 12:52:47.274: Error connecting to database: Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory
mysql> create user 'admin'@'localhost' identified by 'Root@1234';
Query OK, 0 rows affected (1.05 sec)
mysql> grant all privileges on *.* to 'admin'@'localhost' ;
Query OK, 0 rows affected (0.25 sec)
mysql>
mysql> alter user 'admin'@'localhost' identified with mysql_native_password BY 'Root@1234';
Query OK, 0 rows affected (0.11 sec)
Full DB backup : Use below command to take full backup. Please note you need to specify directory for each backup. You can get full options list from –help command.
[root@test-machine01 ~]# mydumper --help
[root@test-machine01 ~]# myloader --help
[root@test-machine01 fulldb]# mydumper -h localhost -u admin -p Root@1234 -t 2 --rows=50000 -v 3 --outputdir /u01/backup3/fulldb --logfile /u01/backup3/fulldb/fulldb.log
[root@test-machine01 fulldb]# ls -ltr
total 170504
-rw-r--r--. 1 root root 133 Feb 13 15:59 employees-schema-create.sql
-rw-r--r--. 1 root root 129 Feb 13 15:59 mysql-schema-create.sql
-rw-r--r--. 1 root root 133 Feb 13 15:59 repl_test-schema-create.sql
-rw-r--r--. 1 root root 127 Feb 13 15:59 sys-schema-create.sql
-rw-r--r--. 1 root root 351 Feb 13 15:59 employees.departments.sql
Options Used :
-t, --threads Number of threads to use, default 4
-v, --verbose Verbosity of output, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2
-r, --rows Try to split tables into chunks of this many rows. This option turns off --chunk-filesize
-F, --chunk-filesize Split tables into chunks of this output file size. This value is in MB
Single DB Backup : Use below command to take single db backup. You can specify either –rows or –chunk-filesize to control dumpfiles.
[root@test-machine01 singledb]# mydumper -h localhost -u admin -p Root@1234 -t 2 --rows=50000 --database employees -v 3 --outputdir /u01/backup3/singledb --logfile /u01/backup3/singledb/singledb.log
[root@test-machine01 singledb]# ls -ltr
total 168424
-rw-r--r--. 1 root root 133 Feb 14 12:02 employees-schema-create.sql
-rw-r--r--. 1 root root 351 Feb 14 12:02 employees.departments.sql
-rw-r--r--. 1 root root 862968 Feb 14 12:02 employees.dept_emp.00001.sql
-rw-r--r--. 1 root root 2613818 Feb 14 12:02 employees.dept_emp.00002.sql
-rw-r--r--. 1 root root 3791352 Feb 14 12:02 employees.dept_emp.00000.sql
[root@test-machine01 test]# mydumper -h localhost -u admin -p Root@1234 -t 3 --chunk-filesize 10 --database employees -v 3 --outputdir /u01/backup3/test --logfile /u01/backup3/test/singledb.log
[root@test-machine01 test]# ls -ltr
total 168340
-rw-r--r--. 1 root root 133 Feb 14 12:00 employees-schema-create.sql
-rw-r--r--. 1 root root 351 Feb 14 12:00 employees.departments.sql
-rw-r--r--. 1 root root 1168 Feb 14 12:00 employees.dept_manager.sql
-rw-r--r--. 1 root root 10999800 Feb 14 12:00 employees.employees.00001.sql
-rw-r--r--. 1 root root 10999908 Feb 14 12:00 employees.dept_emp.00001.sql
Compressed DB backup: It will create a single file for each table as no –rows or –chunk-filesize option specified. You can see the difference after using compression option in dumpfile size.
[root@test-machine01 compdb]# mydumper -h localhost -u admin -p Root@1234 -t 3 --compress --database employees -v 3 --outputdir /u01/backup3/compdb --logfile /u01/backup3/compdb/compdb.log
[root@test-machine01 compdb]# ls -ltr
total 35128
-rw-r--r--. 1 root root 136 Feb 14 12:09 employees-schema-create.sql.gz
-rw-r--r--. 1 root root 403 Feb 14 12:09 employees.dept_manager.sql.gz
-rw-r--r--. 1 root root 259 Feb 14 12:09 employees.departments.sql.gz
-rw-r--r--. 1 root root 2464150 Feb 14 12:09 employees.dept_emp.sql.gz
[root@test-machine01 backup3]# du -sh compdb
35M compdb
[root@test-machine01 backup3]# du -sh singledb
165M singledb
[root@test-machine01 backup3]#
Multiple DB Backup : Multile DB backup is not possible in mydumper need to take single db backup in each command.
[root@test-machine01 multidb]# mydumper -h localhost -u admin -p Root@1234 -t 3 --chunk-filesize 20 --database employees -v 3 --outputdir /u01/backup3/multidb --logfile /u01/backup3/multidb/multidb.log
[root@test-machine01 multidb]# mydumper -h localhost -u admin -p Root@1234 -t 3 --chunk-filesize 20 --database repl_test -v 3 --outputdir /u01/backup3/multidb --logfile /u01/backup3/multidb/multidb.log
Multiple Table Backup : Use below command to take table level backup
[root@test-machine01 tablebkp]# mydumper -h localhost -u admin -p Root@1234 -t 4 --database employees --tables-list employees,departments,salaries -v 3 --outputdir /u01/backup3/tablebkp --logfile /u01/backup3/tablebkp/tablebkp.log
[root@test-machine01 tablebkp]# ls -ltr
total 133248
-rw-r--r--. 1 root root 133 Feb 14 12:37 employees-schema-create.sql
-rw-r--r--. 1 root root 332 Feb 14 12:37 employees.departments-schema.sql
-rw-r--r--. 1 root root 415 Feb 14 12:37 employees.employees-schema.sql
-rw-r--r--. 1 root root 351 Feb 14 12:37 employees.departments.sql
-rw-r--r--. 1 root root 447 Feb 14 12:37 employees.salaries-schema.sql
-rw-r--r--. 1 root root 17722965 Feb 14 12:37 employees.employees.sql
-rw-r--r--. 1 root root 118693182 Feb 14 12:37 employees.salaries.sql
-rw-r--r--. 1 root root 1317 Feb 14 12:37 tablebkp.log
-rw-r--r--. 1 root root 133 Feb 14 12:37 metadata
[root@test-machine01 tablebkp]#
A regular expression for DB Backup: You can use a regular expression to include or exclude DB from backup. In the below example all system schemas are excluded from backup only user schema will be backed up.
Also, note we are using option –ask-password if you don’t want to mention password in the backup command.
[root@test-machine01 regtablebkp]# mydumper -h localhost -u admin --ask-password --regex='^(?!(mysql|information_schema|performance_schema|sys))' --events --triggers --routines --outputdir=/u01/backup3/regtablebkp --logfile /u01/backup3/regtablebkp/regtablebkp.log
Enter MySQL Password:
[root@test-machine01 regtablebkp]# ls -ltr
total 168348
-rw-r--r--. 1 root root 0 Feb 14 13:03 regtablebkp.log
-rw-r--r--. 1 root root 133 Feb 14 13:03 employees-schema-create.sql
-rw-r--r--. 1 root root 133 Feb 14 13:03 repl_test-schema-create.sql
-rw-r--r--. 1 root root 351 Feb 14 13:03 employees.departments.sql
-rw-r--r--. 1 root root 1168 Feb 14 13:03 employees.dept_manager.sql
-rw-r--r--. 1 root root 14160012 Feb 14 13:03 employees.dept_emp.sql
Only Schema no data : If you want to take only metadata backup.
[root@test-machine01 schemaonlydb]# mydumper -h localhost -u admin --ask-password -t 3 --database employees --no-data -v 3 --outputdir /u01/backup3/schemaonlydb --logfile /u01/backup3/schemaonlydb/schemaonlydb.log
Enter MySQL Password:
[root@test-machine01 schemaonlydb]#
[root@test-machine01 schemaonlydb]# ls -ltr
total 52
-rw-r--r--. 1 root root 133 Feb 14 13:10 employees-schema-create.sql
-rw-r--r--. 1 root root 332 Feb 14 13:10 employees.departments-schema.sql
-rw-r--r--. 1 root root 602 Feb 14 13:10 employees.dept_manager-schema.sql
Only data no schema: If you want to take only data backup without metadata.
[root@test-machine01 dataonly]# mydumper -h localhost -u admin --ask-password -t 3 --database employees --no-schemas -v 3 --outputdir /u01/backup3/dataonly --logfile /u01/backup3/dataonly/dataonly.log
Enter MySQL Password:
[root@test-machine01 dataonly]# ls -ltr
total 168272
-rw-r--r--. 1 root root 351 Feb 14 13:13 employees.departments.sql
-rw-r--r--. 1 root root 1168 Feb 14 13:13 employees.dept_manager.sql
-rw-r--r--. 1 root root 14160012 Feb 14 13:13 employees.dept_emp.sql
-rw-r--r--. 1 root root 17722965 Feb 14 13:13 employees.employees.sql
Part II : Mysql Logical Backup using myloader – Import
This document is only for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.
Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the latest update. Click here to know more about our pursuit.
