Sep
26
2014
--

Logical MySQL backup tool mydumper 0.6.2 now available

We are pleased to announce the third release in the 0.6 series of mydumper, a tool for performing logical MySQL backups. In this release, we focused on simplifying compiling the code and added new features for making logical backups. These new features include enhancements to AWS RDS support and extending TokuDB support.

Due to recent changes (or not so much) on mysql libs, it became impossible to compile mydumper without the complete mysql source code. To simplify this, we had to disable the binlog functionality by default, as it was the one affected by the mysql libs changes. Now you should be able to compile against any mysql version without issues. If you still want the binlog feature, it is still there and you can enable it with:

cmake . -DWITH_BINLOG=ON

The one caveat is that you will need to compile against a mysql version greater than 5.5.34. It’s also possible to compile with binlog enabled against the latest 5.6 versions. In this case, you will need the source code to make some changes. You can find related information about this issue at these locations:

Download mydumper-0.6.2 source code here.

Bugs Fixed:

  • #1347392 Last row of table not dumped if it brings statement over statement_size
  • #1157113 Compilation of latest branch fails on CentOS 6.3 64bit
  • #1326368 Can’t make against Percona-Server-devel-55 headers
  • #1282862 Unknown type name ‘HASH’
  • #1336860 k is used twice
  • #913307 Can’t compile – missing libs crypto and ssl
  • #1364393 Rows chunks doesn’t increase non innodb jobs

New MySQL Backup Features:

  • --lock-all-tables

Use LOCK TABLE for all instead of FLUSH TABLES WITH READ LOCK. With this option you will be able to backup RDS instances and also get the binlog coordinates (5.6).

  • TokuDB support

Now TokuDB tables are dumped within the consistent snapshot instead of being locked like MyISAM.

  • Support to dump tables from different schemas

mydumper has two arguments,

    --database (-B)

and

    --tables-list (-T)

so until now you were able to do;

-B db1

or

-B db1 -T t1,t2,tn

To dump a whole database or a list of tables from one database respectively. In 0.6.2 you can list tables in different databases in this way;

-T db1.t1,db1.t2,db2.t1,db2.t1

NOTE: You should not use -B here because mydumper will take the -T list as table names.

The post Logical MySQL backup tool mydumper 0.6.2 now available appeared first on Percona Performance Blog.

Written by in: AWS RDS,Logical MySQL backup tool,Max Bubenick,mydumper 0.6.2,MySQL,Percona Toolkit,Secondary,TokuDB,Zend Developer |
Jun
13
2014
--

mydumper [less] locking

In this post I would like to review how my dumper for MySQL works from the point of view of locks. Since 0.6 serie we have different options, so I will try to explain how they work

As you may know mydumper is multithreaded and this adds a lot of complexity compared with other logical backup tools as it also needs to coordinate all threads with the same snapshot to be consistent. So let review how mydumper does this with the default settings.

By default mydumper uses 4 threads to dump data and 1 main thread

Main Thread

  • FLUSH TABLES WITH READ LOCK
Dump Thread X

  • START TRANSACTION WITH CONSISTENT SNAPSHOT;
  • dump non-InnoDB tables
Main Thread

  • UNLOCK TABLES
Dump Thread X

  • dump InnoDB tables
As you can see in this case we need FTWRL for two things, coordinate transaction’s snapshots and dump non-InnoDB tables in a consistent way. So we have have global read lock until we dumped all non-InnoDB tables.
What less locking does is this:
Main Thread

  • FLUSH TABLES WITH READ LOCK
Dump Thread X

  • START TRANSACTION WITH CONSISTENT SNAPSHOT;
 LL Dump Thread X

  • LOCK TABLES non-InnoDB
Main Thread

  • UNLOCK TABLES
 LL Dump Thread X

  • dump non-InnoDB tables
  • UNLOCK non-InnoDB
Dump Thread X

  • dump InnoDB tables

So now the global read lock its in place until less-locking threads lock non-InnoDB tables, and this is really fast. The only downsize is that it uses double the amount of threads, so for the default (4 threads) we will end up having 9 connections, but always 4 will be running at the same time.

Less-locking really helps when you have MyISAM or ARCHIVE that are not heavily updated by production workload, also you should know that LOCK TABLE … READ LOCAL allows non conflicting INSERTS on MyISAM so if you use that tables to keep logs (append only) you will not notice that lock at all.

For the next release we will implement backup locks that will avoid us to run FTWRL.

The post mydumper [less] locking appeared first on MySQL Performance Blog.

Written by in: backup tools,Insight for DBAs,Max Bubenick,mydumper,MySQL,Zend Developer |

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