May
09
2022
--

MyDumper 0.12.3-1 is Now Available

MyDumper 0.12.3-1

MyDumper 0.12.3-1The new MyDumper 0.12.3-1 version, which includes many new features and bug fixes, is now available.  You can download the code from here. MyDumper is Open Source and maintained by the community, it is not a Percona, MariaDB, or MySQL product.

In this new version we focused on:

  • Refactoring tasks: Splitting mydumper.c and myloader.c into multiple files will allow us to find bugs and implement features easily.
  • Adding compression capabilities on stream: One of the more interesting features added to MyDumper was being able to stream to another server and now, compression and transmission will be available and much faster.
  • Support for the Percona Monitoring and Management (PMM) tool: Being able to monitor scheduled processes is always desired, even more, if you know what the process is doing internally and that is why we used the textfile extended metrics to show how many jobs have been done and queue status.

Enhancement:

  • Adding PMM support for MyDumper #660 #587
  • Adding compression capabilities on stream #605
  • Dockerfile: use multi-stage builds #603
  • restore defaults-file override behavior #601

Fix:

  • Fixing when DEFINER is absent and stream/intermediate sync when finish #662 #659
  • Adding better message when schema-create files are not provided #655 #617
  • re adding –csv option #653
  • Fixing trigger absent when –no-schema is used #647 #541
  • Allow compilation on Clang #641 #563
  • Fixing version number #639 #637
  • Adding critical message when DDL fails #636 #634
  • Fix print time #633 #632
  • print sigint confirmation to stdout instead of to a log file #616 #610
  • Dockerfile: add missing libglib2.0 package #608
  • [BUG] mydumper zstd doesn’t run on CentOS due to missing libzstd package dependency #602

Refactoring:

Help Wanted:

  • [BUG] myloader always uses socket to connect to db #650
  • [BUG] real_db_name isn’t found #613

Documentation:

  • Update README.md #614
  • Use 0.11.5-2 in installation examples #606
  • Question: What exactly is inconsistent about inconsistent backups? #644

Download MyDumper 0.12.3 Today!

MyDumper has its own merch now, too. By shopping for the merch you contribute to the project. Check it out! 

Dec
03
2021
--

MyDumper Github Repository Is Now an Organization

MyDumper Github Repository organization

For a long time, MyDumper has been in Max Bubenick’s personal GitHub repository. Now, we decided to move to a new MyDumper’s Organization as requested earlier this year by a user from the community.

There were also two other reasons why we decided to move it. The first one is related to how the project is evolving, and the second is that it will allow us to implement integrations to other projects.

We can see the evolution of the project, noting the increase in commits of the last year:

mydumper

We tried to keep the release cycle every two months, focusing on closing as many bugs as possible and implementing new features requested. It was not an easy task, as lots of changes had to be implemented in mydumper and myloader engine to allow the new features to be developed. 

Seeing the progress that has been done, we can encourage ourselves and expect to release version 1.0.1 next year. This will be a huge step for the project, as it will mean that MyDumper is mature enough to endeavor easily any export/import task.

On the other hand, moving MyDumper to an Organization will allow us to create an official Docker image and it will also allow us to create a pipeline with CircleCI. Both are on the Community wish list but it will be also useful for current and future development members, as one of the most important and difficult tasks is related to testing because of the number of use cases that we have. We expect to see higher quality on releases, not just because of the quality of code, but also because the tests cover most of the uses of mydumper and myloader.

Now MyDumper has its own merch, too. By shopping for the merch you contribute to the project. Learn more here.
Nov
29
2021
--

MyDumper 0.11.3 is Now Available

MyDumper 0.11.3 MySQL

MyDumper 0.11.3 MySQLThe new MyDumper 0.11.3 version, which includes many new features and bug fixes, is now available.  You can download the code from here.

We are very proud to announce that we were able to achieve the two main objectives for the milestone ZSTD and Stream support!  We added four packages with ZSTD support because not all the distributions have support for v1.4 or higher. Package libzstd is required to use ZSTD compression. ZSTD Bullseye package is only available with libraries for Percona Server for MySQL 8.0. There are two main use cases for the Stream functionality:

  • Importing while you are exporting
  • Remote backups

The drawback is that it relies on the network throughput as we are using a single thread to send the files that have been closed. We are going to explain how this functionality works in another blog post!

Enhancement:

Bug/Fixes:

  • Escape double and float values because of -0 #326 #30
  • Fixing const issues after merge zstd #444 #134
  • WITH_ZSTD needs to be set before being used #442
  • Adding better error handling #441 #440
  • Revert #426 #433
  • Database schema creation control added #432 #431
  • Adding anonymized function [Phase I] #427 #428
  • Fixing comment error log in restore_data_in_gstring_from_file #426
  • Adding LC_ALL to allow multi-byte parameters #415 #199
  • Needs to notify main thread to go ahead when “–tables-list” is used without “-B”! #396 #428

Refactoring:

  • Fixing headers #425
  • sync_before_add_index is not needed anymore #416
  • Using generic functions to build filenames #414

Documentation:

  • Modify the log of error #430
  • Fixing readme #420
  • Warning for inconsistencies using multisource #417 #144
  • docs: add brew build dependencies instruction #412
  • [Document] add example #408 #407

Question Addressed:

  • [BUG] Can’t connect to MySQL server using host and port. #434
  • Could not execute query: Unknown error #335

Download MyDumper 0.11.3 Today!

Sep
21
2021
--

MyDumper 0.11.1 is Now Available

MyDumper 0.11.1

MyDumper 0.11.1The new MyDumper 0.11.1 version, which includes many new features and bug fixes, is now available.  You can download the code from here.

For this release, there are three main changes: 1) we added config file functionality which allows users to set session-level variables (one of the most requested features!), 2) we developed a better and robust import mechanism, and 3) we fixed all the filename related issues.  Those changes and mostly the last one forced us to change the version number from 0.10.9 to 0.11.1 as a backup taken in 0.10.x will not work in 0.11.x and vice versa.

New Features:

  • Adding order by part functionality #388
  • improve estimated_step #376 #373
  • Adding config file functionality #370
    • Use only a config file to load the parameters #318
    • We hope to add parameters and support custom SQL mode and character configuration #274
    • [myloader] Add option to disable Galera Cluster (Wsrep) replication before importing #159
    • mydumper can’t recreate mysql.proc + mysql.events due to 5.7 sql_mode #142
    • trouble with global sql_big_selects=0 #50
    • Enabling MAX_EXECUTION_TIME option #368
    • mydumper dump big table failed, because of dumping time longer than max_execution_time #257
    • trouble with global sql_big_selects=0 #50
    • We hope to add parameters and support custom SQL mode and character configuration #274
  • Adding sync mechanism and constraint split #352
    • [fast index] Deadlock found when trying to get lock #342
    • usingFastIndex: indexes may be created while restore is in progress #292
    • [enhancement] optimized data loading order #285
    • Enhancement request: myloader option to prioritize size #78
  • Table and database names changed to fix several issues #399
    • bug for overwrite-tables option #272
    • Problems related to log output when backing up table name with #179 #210
    • parallel schema restore? #169 #311
    • bug with table name #41 #56
    • Export with table folders #212
    • bug for overwrite-tables option #272
    • [BUG] table name make mydumper fail #391
    • [BUG] table name starts with checksum breaks myloader #382
    • Fix issue #182 for tables with quotes #258
  • Dump procedures and functions using case sensitive database name #69

Bug Closed:

  • [BUG] Error occurs when using –innodb-optimize-keys and there is a column having a foreign key and part of a generated column #395
  • Tables without indexes can cause segfault #386

Fixes:

  • [ Fix ] on table name segfault and add free functions #401
  • Adding the set session execution #398
  • [ Fix ] When no index, alter_table_statement must be NULL #397
  • Avoid logging SQL data on error #392
  • [ fix ] The count was incorrect #390
  • Fix on Debian version #387
  • Fix package URLs #384
  • Change quotes to backticks in SQL statements #350
  • bug for overwrite-tables option #272
  • mydumper: fix an ‘SQL injection’ issue when table name contains a ‘ or #168

Refactoring

  • Reading file list only one time #394
  • Export with table folders #212

Question Addressed:

  • [QUESTION] Increase CPU usage Tuning restore #380
  • [Question] how to use mydumper to backup 2 tables with different where conditions? #377
  • column charset is ignored once it’s different from table charset. #156
  • mydumper/myloader feature requests #84
  • load Generated Columns error #175

Download MyDumper 0.11.1 Today!

Jul
06
2021
--

MyDumper 0.10.7 is Now Available

MyDumper 0.10.7 is Now Available

MyDumper 0.10.7 is Now AvailableThe new MyDumper 0.10.7 version, which includes many new features and bug fixes, is now available.  You can download the code from here.

For this release, we have added several features like WHERE support that is required for partial backups. We also added CHECKSUM for tables which help to speed up the restore of large tables to take advantage of fast index creation, and more.

New Features:

  • Adding metadata file per table that contains the number of rows #353
  • Adding –where support #347 #223
  • Option to automatically disable/enable REDO_LOG #305 #334
  • Adding wsrep_sync_wait support #327
  • Adding fast index creation functionality #286
  • Adding ORDER BY Primary Key functionality #227
  • Added support for dumping checksums #141 #194
  • Dump strings using single quote instead of double quotes #191
  • Specify the number of snapshots #118

Bug Fixes:

  • Fixed the create database section when creating with –source-db enabled #213
  • Escaping quotes on detect_generated_fields as it caused segfault #349
  • [usingfastindex] Indexes on AUTO_INCREMENT column should not be selected for fast index creation #322
  • Fixed checksum compression #355
  • Fixed as constraint was ignored #351
  • Fixed int declaration to comply with C99 #346

Documentation:

  • Added s to install #360
  • Added libatomic1 dependency reference on ubuntu #358 #359
  • Release signatures #28

Refactoring:

  • Moving detected_generated_fields as parameter in table_job #331 #333
  • Abstract function for io #332

Won’t-fix:

  • Cannot dump Clustrix tables #87
  • Extending support of WITH_BINLOG to MySQL 8.0 #341
May
07
2021
--

MyDumper 0.10.5 is Now Available

MyDumper 0.10.5 available

MyDumper 0.10.5 availableThe new MyDumper 0.10.5 version, which includes many new features and bug fixes, is now available.  You can download the code from here.

For this release, we focused on fixing some old issues and testing old pull requests to get higher quality code. On releases 0.10.1, 0.10.3, and 0.10.5, we released the packages compiled against MySQL 5.7 libraries, but from now on, we are also compiling against MySQL 8 libraries for testing purposes, not releasing, as we think that more people in the community will start compiling against the latest version, and we should be prepared.

New Features:

  • Password obfuscation #312
  • Using dynamic parameter for SET NAMES #154
  • Refactor logging and enable –logfile in myloader #233
  • Adding purge-mode option (NONE/TRUNCATE/DROP/DELETE) to decide what is the preferable mode #91 #25
  • Avoid sending COMMIT when commit_count equals 1 #234
  • Check if directory exists #294

Bug Fixes:

  • Adding homebrew build support #313
  • Removing MyISAM dependency in temporary tables for VIEWS #261
  • Fix warnings in sphinx document generation #287
  • Fix endless loop when processlist couldn’t be checked #295
  • Fix issues when daemon is used on glist #317

Documentation:

  • Correcting ubuntu/debian packages dependencies #310
  • Provide better CentOS 7 build instructions #232
  • –defaults-file usage for section mydumper and client #306
  • INSERT IGNORE documentation added #195

Refactoring:

  • Adding function new_table_job #315
  • Adding IF NOT EXISTS to SHOW CREATE DATABASE #314
  • Update FindMySQL.cmake #149

 

Apr
21
2021
--

Back From a Long Sleep, MyDumper Lives!

MyDumper MySQL

MyDumper MySQLMySQL databases keep getting larger and larger. And the larger the databases get, the harder it is to backup and restore them.  MyDumper has changed the way that we perform logical backups to enable you to restore tables or objects from large databases. Over the years it has evolved into a tool that we use at Percona to back up petabytes of data every day. It has several features, but the most important one, from my point of view, is how it speeds up the entire process of export and import.

Until the beginning of this year, the latest release was from 2018; yes, more than two years without any release. However, we started 2021 with release v0.10.1 in January, with all the merges up to that point and we committed ourselves to release every two months… and we delivered! Release v0.10.3 was released in March with some old pull requests that have been sleeping for a long time. The next release is planned to be in May, with some of the newest features.

Just to clarify, mydumper/myloader are not officially-supported Percona products. They are open source, community-managed tools for handling logical backups and restores with all flavors of MySQL.

What Has Changed?

The principal maintainer remains Max Bubenick, and I’ve been helping out with reviewing issues and pull requests to give better support to the community.

Better planning means that it is not just released on time; we also need to decide what is the new feature that we are going to be packaging in the next release, and the level of quality.

Register for Percona Live ONLINE
A Virtual Event about Open Source Databases

The releases were in 2021, but this effort started in 2020 and I had been working on the release repository as it wasn’t being maintained.

What’s Next?

Exciting times! There are three features that I would like to mention, not because the others are not important, but rather, because they will speed up the import stage.

  • Fast index creation is finally arriving! This was one of the requested features that even mysqldump implemented.
  • Not long ago I realized that two requests can be merged into one, and the community was asking about CSV export and LOAD DATA support.
  • Finally, this request had been waiting for a long time – Is it possible for MyDumper to stream backups? We found a way and we are going to be working on v0.10.9.

I was able to measure the speed-up of the first two and we could get up to 40% on large tables with multiple secondary indexes. The latest one is not implemented yet, but taking into account that the import will be started alongside the export, we can expect a huge reduction in the timing.

Conclusion

We still have a lot of opportunities to make MyDumper a major league player. Feel free to download it, play with it, and if you want to contribute, we need your help writing code, testing, or asking for new features.

Aug
26
2020
--

Creating an External Replica of AWS Aurora MySQL with Mydumper

Oftentimes, we need to replicate between Amazon Aurora and an external MySQL server. The idea is to start by taking a point-in-time copy of the dataset. Next, we can configure MySQL replication to roll it forward and keep the data up-to-date.

This process is documented by Amazon, however, it relies on the mysqldump method to create the initial copy of the data. If the dataset is in the high GB/TB range, this single-threaded method could take a very long time. Similarly, there are ways to improve the import phase (which can easily take 2x the time of the export).

Let’s explore some tricks to significantly improve the speed of this process.

Preparation Steps

The first step is to enable binary logs in Aurora. Go to the Cluster-level parameter group and make sure binlog_format is set to ROW. There is no log_bin option in Aurora (in case you are wondering), simply setting binlog_format is enough. The change requires a restart of the writer instance, so it, unfortunately, means a few minutes of downtime.

We can check if a server is generating binary logs as follows:

mysql> SHOW MASTER LOGS;

+----------------------------+-----------+
| Log_name                   | File_size |
+----------------------------+-----------+
| mysql-bin-changelog.034148 | 134219307 |
| mysql-bin-changelog.034149 | 134218251 |
...

Otherwise, you will get an error:

ERROR 1381 (HY000): You are not using binary logging

We also need to ensure a proper binary log retention period. For example, if we expect the initial data export/import to take one day, we can set the retention period to something like three days to be on the safe side. This will help ensure we can roll forward the restored data.

mysql> call mysql.rds_set_configuration('binlog retention hours', 72);
Query OK, 0 rows affected (0.27 sec)

mysql> CALL mysql.rds_show_configuration;
+------------------------+-------+------------------------------------------------------------------------------------------------------+
| name                   | value | description                                                                                          |
+------------------------+-------+------------------------------------------------------------------------------------------------------+
| binlog retention hours | 72    | binlog retention hours specifies the duration in hours before binary logs are automatically deleted. |
+------------------------+-------+------------------------------------------------------------------------------------------------------+
1 row in set (0.25 sec)

The next step is creating a temporary cluster to take the export. We need to do this for a number of reasons: first to avoid overloading the actual production cluster by our export process, also because mydumper relies on FLUSH TABLES WITH READ LOCK to get a consistent backup, which in Aurora is not possible (due to the lack of SUPER privilege).

Go to the RDS console and restore a snapshot that was created AFTER the date/time where you enabled the binary logs. The restored cluster should also have binlog_format set, so select the correct Cluster parameter group.

Next, capture the binary log position for replication. This is done by inspecting the Recent events section in the console. After highlighting your new temporary writer instance in the console, you should see something like this:

Binlog position from crash recovery is mysql-bin-changelog.034259 32068147

So now we have the information to prepare the CHANGE MASTER command to use at the end of the process.

Exporting the Data

To get the data out of the temporary instance, follow these steps:

  1. Backup the schema
  2. Save the user privileges
  3. Backup the data

This gives us added flexibility; we can do some schema changes, add indexes, or extract only a subset of the data.

Let’s create a configuration file with the login details, for example:

tee /backup/aurora.cnf <<EOF
[client]
user=percona
password=percona
host=percona-tmp.cgutr97lnli6.us-west-1.rds.amazonaws.com
EOF

For the schema backup, use mydumper to do a no-rows export:

mydumper --no-data \
--triggers \
--routines \
--events \
-v 3 \
--no-locks \
--outputdir /backup/schema \
--logfile /backup/mydumper.log \
--regex '^(?!(mysql|test|performance_schema|information_schema|sys))' \
--defaults-file /backup/aurora.cnf

To get the user privileges I normally like to use pt-show-grants. Aurora is, however, hiding the password hashes when you run SHOW GRANTS statement, so pt-show-grants will print incomplete statements e.g.:

mysql> SHOW GRANTS FOR 'user'@'%';
+---------------------------------------------------------+
| Grants for user@%                                       |
+---------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user'@'%' IDENTIFIED BY PASSWORD |
| GRANT SELECT ON `db`.* TO 'user'@'%'                    |
+---------------------------------------------------------+

We can still gather the hashes and replace them manually in the pt-show-grants output if there is a small-ish number of users.

pt-show-grants --user=percona -ppercona -hpercona-tmp.cgutr97lnli6.us-west-1.rds.amazonaws.com  > grants.sql

mysql> select user, password from mysql.user;

Finally, run mydumper to export the data:

mydumper -t 8 \
--compress \
--triggers \
--routines \
--events \
—-rows=10000000 \
-v 3 \
--long-query-guard 999999 \
--no-locks \
--outputdir /backup/export \
--logfile /backup/mydumper.log \
--regex '^(?!(mysql|test|performance_schema|information_schema|sys))' \
-O skip.txt \
--defaults-file /backup/aurora.cnf

The number of threads should match the number of CPUs of the instance running mydumper. In the skip.txt file, you can include any tables that you don’t want to copy. The –rows argument will give you the ability to split tables in chunks of X number of rows. Each chunk can run in parallel, so it is a huge speed bump for big tables.

Importing the Data

We need to stand up a MySQL instance to do the data import. In order to speed up the process as much as possible, I suggest doing a number of optimizations to my.cnf as follows:

[mysqld]
pid-file=/var/run/mysqld/mysqld.pid
log-error=/var/log/mysqld.log
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log_slave_updates
innodb_buffer_pool_size=16G
binlog_format=ROW
innodb_log_file_size=1G
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=0
server-id=1000
log-bin=/log/mysql-bin
sync_binlog=0
master_info_repository=TABLE
relay_log_info_repository=TABLE
query_cache_type=0
query_cache_size=0
innodb_flush_neighbors=0
innodb_io_capacity_max=10000
innodb_stats_on_metadata=off
max_allowed_packet=1G
net_read_timeout=60
performance_schema=off
innodb_adaptive_hash_index=off
expire_logs_days=3
sql_mode=NO_ENGINE_SUBSTITUTION
innodb_doublewrite=off

Note that mydumper is smart enough to turn off the binary log for the importer threads.

After the import is complete, it is important to revert these settings to “safer” values: innodb_doublewriteinnodb_flush_log_at_trx_commit, sync_binlog, and also enable performance_schema again.

The next step is to create an empty schema by running myloader:

myloader \
-d /backup/schema \
-v 3 \
-h localhost \
-u root \
-p percona

At this point, we can easily introduce modifications like adding indexes, since the tables are empty. We can also restore the users at this time:

(echo "SET SQL_LOG_BIN=0;" ; cat grants.sql ) | mysql -uroot -ppercona -f

Now we are ready to restore the actual data using myloader. It is recommended to run this inside a screen session:

myloader -t 4 \
-d /backup/export \
-q 100 \
-v 3 \
-h localhost \
-u root \
-p percona

The rule of thumb here is to use half the number of vCPU threads. I also normally like to reduce mydumper default transaction size (1000) to avoid long transactions, but your mileage may vary.

After the import process is done, we can leverage faster methods (like snapshots or Percona Xtrabackup) to seed any remaining external replicas.

Setting Up Replication

The final step is setting up replication from the actual production cluster (not the temporary one!) to your external instance.

It is a good idea to create a dedicated user for this process in the source instance, as follows:

CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

Now we can start replication, using the binary log coordinates that we captured before:

CHANGE MASTER TO MASTER_HOST='aurora-cluster-gh5s6lnli6.us-west-1.rds.amazonaws.com', MASTER_USER='repl', MASTER_PASSWORD='percona', MASTER_LOG_FILE='mysql-bin-changelog.034259', MASTER_LOG_POS=32068147;
START SLAVE;

Final Words

Unfortunately, there is no quick and easy method to get a large dataset out of an Aurora cluster. We have seen how mydumper and myloader can save a lot of time when creating external replicas, by introducing parallel operations. We also reviewed some good practices and configuration tricks for speeding up the data loading phase as much as possible.


Optimize your database performance with Percona Monitoring and Management, a free, open source database monitoring tool. Designed to work with Amazon RDS MySQL and Amazon Aurora MySQL with a specific dashboard for monitoring Amazon Aurora MySQL using Cloudwatch and direct sampling of MySQL metrics.

Visit the Demo

Nov
12
2015
--

Logical MySQL backup tool Mydumper 0.9.1 now available

Databases backupThe new Mydumper 0.9.1 version, which includes many new features and bug fixes, is now available.  You can download the code from here.

A significant change included in this version now enables Mydumper to handle all schema objects!!  So there is no longer a dependency on using mysqldump to ensure complex schemas are backed up alongside the data.

Let’s review some of the new features:

Full schema support for Mydumper/Myloader

Mydumper now takes care of backing up the schema, including Views and Merged tables. As a result, we now have these new associated options:

-d, --no-data Do not dump table data
-G, --triggers Dump triggers
-E, --events Dump events
-R, --routines Dump stored procedures and functions

These options are not enabled by default to keep backward compatibility with actual mixed solutions using Mysqldump for DDLs.

Locking reduce options

--trx-consistency-only      Transactional consistency only

You can think on this as --single-transaction for mysqldump, but still with binlog position. Obviously this position only applies to transactional tables (TokuDB included).  One of the advantages of using this option is that the global read lock is only held for the threads coordination, so it’s released as soon as the transactions are started.

GTIDs and Multisource Slave 

GTIDs are now recorded on the metadata file.  Also Mydumper is now able to detect a multisource slave (MariaDB 10.1.x) and will record all the slaves coordinates.

Myloader single database restore

Until now the only option was to copy the database files to a different directory and restore from it. However, we now have a new option available:

-s, --source-db                   Database to restore

It can be used also in combination with -B, --database to restore to a different database name.

Full list of Bug Fixes:

#1431410 innodb stats tables
#1440403 *-post and *-triggers compressed files corrupt
#1470891 functions may be needed by SP and views
#1390437 segmentation fault against Percona MySQL 5.6.15-63.0
#1446280 Segmentation fault on Debian Wheezy
#1399715 Typo in –tables-list option in manpage
#1428608 missing -K option in mydumper manpage
#1440437 myloader: wrong database name in message when -B used
#1457091 tokudb detection doesn’t work
#1481747 Unable to compile r179 WITH_BINLOG=ON (undeclared ‘bj’)
#1507574 Assertion when broken mrg tables
#841651 dump view definitions
#1485688 make compile error myloader.c:209

 

The post Logical MySQL backup tool Mydumper 0.9.1 now available appeared first on MySQL Performance Blog.

Sep
21
2015
--

Containing your logical backups: mydumper in docker

Even with software like Percona Xtrabackup, logical backups remain an important component of a thorough backup strategy. To gather a logical backup in a timely fashion we rely on a tool called mydumper. In the Percona Managed Services department we’re lucky enough to have one of the project’s most active contributors and many of the latest features and bug fixes have been released to satisfy some of our own use cases. Compiling mydumper remains one of my personal bug bears and undoubtedly the highest barrier to entry for many potential adopters. There are a few conditions that make compiling it difficult, tiring or even prohibitive. The open source logical backup tool does not supply any official packages, however our friends over at TwinDB are currently shipping a package for CentOS/RHEL. So what if you’re running something else, like Debian, Ubuntu, Arch? Well recently I had a flash of inspiration.

Since I’ve been turning some ideas into docker containers, it dawned on me that it would be a trivial image to create and would add instant portability to the binaries. With a docker environment you can take an image and run a logical backup through to a mapped volume. It’s almost as easy as that.

mydumper_docker

So let me show you what I mean. I have built a docker image with the mydumper, myloader and mysql client libraries installed and it’s available for you on docker hub. This means that we can call a new container to make our backup without technically installing mydumper anywhere. This can get you from zero to mydumper very fast if there are hurdles in your way to deploying the open source backup tool into production.

With the grand assumption that you’ve got Docker installed somewhere, lets pull the image from the docker hub

docker pull mysqlboy/mydumper

Once all the layers download (it’s < 200MB) you’re all set to launch a backup using the mydumper binary. You can roll your own command but it could look similar to;

docker run
--name mydumper
--rm
-v {backup_parent_dir}:/backups
mysqlboy/mydumper
mydumper
--host={host_ipaddr}
--user={mysql_username}
--password={mysql_password}
--outputdir=/backups
--less-locking
--compress
--use-savepoints

If you’re unfamiliar with Docker itself; a very high level summary for you; Docker is a product intended to facilitate process isolation or ‘micro services’ known as containerization. It intends to be lighter and more efficient than Virtual Machines as we traditionally know them. There’s much more to this work flow than I intend to explain here but please see the further learning section in the footer.

Let me explain a little of the above call. We want to launch a mydumper run isolated to a container. We are giving the docker daemon the instruction to remove the container after it finishes it’s run (–rm), we are calling the container to be an ‘instance’ of the mysqlboy/mydumper image and we are passing a traditional mydumper command as the container’s instruction. We have mapped a location on the local filesystem into the container to ensure that the backup persists after the container is stopped and removed. The mydumper command itself will make a full backup of the instance you point it to (mydumper can make remote backups, pulling the data locally) and will use the less locking, savepoints and compression features.

What’s more, the beauty of containerizing the mydumper/myloader binaries mean that you can use this image in conjunction with docker machine to source logical backups from Mac and Windows where this process is typically difficult to assume.

I’m going to be filling in for my colleague Max Bubenick this week at Percona Live in Amsterdam talking about Logical Backups using Mydumper and if you’re planning to attend, the mydumper docker image will provide you with a quick path to trial. Thanks for reading and if you’re in Amsterdam this week don’t forget to say hello!

Further learning about docker:

https://www.docker.com/whatisdocker

https://www.youtube.com/user/dockerrun

http://europe-2015.dockercon.com/

The post Containing your logical backups: mydumper in docker appeared first on MySQL Performance Blog.

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com