During the development of the myloader –innodb-optimize-keys option, which was released in version 0.10.7, we found several issues and opportunities to improve the process. We had to change the approach, reimplement some of the core functionality and add a couple of data structures. That allowed us to implement, at a really low cost, a feature that executes the files that contain INSERT statements, sorted by Primary Key. This is desirable to reduce page splits, which cause on-disk tablespace fragmentation.

In this blog post, I will present the differences in data fragmentation for each version.

Test Details

These are local vm tests as there is no intention to show performance gain.

The table that I used is: …

Mydumper is known as the faster (much faster) mysqldump alternative. So, if you take a logical backup you will choose Mydumper instead of mysqldump. But what about the restore? Well, who needs to restore a logical backup? It takes ages! Even with Myloader. But this could change just a bit if we are able to take advantage of Fast Index Creation.

As you probably know, Mydumper and mysqldump export the struct of a table, with all the indexes and the constraints, and of course, the data. Then, Myloader and MySQL import the struct of the table and import the data. The most important difference is that you can configure Myloader to import the data using a certain amount of threads. The import steps are:

1. Create the complete struct of the table
2. Import the data

When you execute Myloader, internally it first creates the tables executing the “-schema.sql” files and then takes all the filenames …

Mydumper 0.2.2 has been released today with a number of fixes and new features.  The one that most people have been asking for is consistent snapshots for non-InnoDB tables (such as MyISAM).  We have been able to achieve this without locking the database for the entire backup using the following method:

1. 
   
2. Flush tables with read lock (and start transaction with consistent snapshot on all threads)
   
3. Dump non-InnoDB
   
4. Start InnoDB dump
   
5. When non-InnoDB dump has finished (whilst InnoDB is dumping) unlock tables
   
6. ...
   
7. Profit

I have also started work on Drizzle support this week.  The …

Mydumper 0.2.2 has been released today with a number of fixes and new features.  The one that most people have been asking for is consistent snapshots for non-InnoDB tables (such as MyISAM).  We have been able to achieve this without locking the database for the entire backup using the following method:

1. Flush tables with read lock (and start transaction with consistent snapshot on all threads)
2. Dump non-InnoDB
3. Start InnoDB dump
4. When non-InnoDB dump has finished (whilst InnoDB is dumping) unlock tables
5. …
6. Profit

I have also started work on Drizzle support this week.  The Drizzle support is not entirely complete mostly down to Drizzle’s handling of SHOW TABLE STATUS and …

It has only been a few days since the 0.2.0 release of mydumper but there have been some big changes since then.  I will try and go over them all here.
Mydumper 0.2.1
Mydumper 0.2.1 has been released today, many thanks for all those who have been testing the trunk source, the feedback has gone a long way to making fixes and improvements to mydumper.  You can download the source for it here.
Myloader
A few days ago Mark Callaghan asked about restoring mydumper backups.  There is of course the great ' …

It has only been a few days since the 0.2.0 release of mydumper but there have been some big changes since then.  I will try and go over them all here.

Mydumper 0.2.1

Mydumper 0.2.1 has been released today, many thanks for all those who have been testing the trunk source, the feedback has gone a long way to making fixes and improvements to mydumper.  You can download the source for it here.

Myloader

A few days ago Mark Callaghan asked about restoring mydumper backups.  There is of course the great ‘ …