Tag Archive for data-recovery at Xaprb

Tag Archive for 'data-recovery'

MySQL Toolkit version 1254 released

Published at November 12, 2007 in Perl, SQL and Tools. 0 Comments

This release fixes several bugs introduced in the last release as I replaced untested code with tested code — how ironic! Actually, I knew that was virtually guaranteed to happen. Anyway, all the bugs you’ve helped me find are now fixed. I also fixed a long-standing bug in MySQL Table Sync, which I am otherwise trying to touch as little as possible for the time being. (Remember to contribute to the bounty, and get your employer to contribute as well, so I can do some real work on it in the next month or so!)

The other big news is that the parallel dump and restore tools are now 1.0.0 because I consider them feature-complete. I have put the most work into tab-separated dumps. These two tools can do something MySQL AB’s tools can’t currently do: restore data before creating triggers (when doing tab-delimited dumps). That’s an obvious requirement for loading data when tables have triggers. If you create the triggers before loading the data, you’re practically guaranteed to end up with different data than was dumped. The tools now dump and reload both triggers and views. As long as you’re dumping the mysql database, I think they should be able to completely duplicate a server (my initial goal was just data, not routines/triggers/views/etc).

Honestly, I hope MySQL’s tools make this pair of tools obsolete in the future, but until then, they’re a good way to dump and reload data at higher speeds. Keith Murphy did some measurements on parallel dump and restore speeds.

Here’s the full changelog:

Changelog for mysql-archiver:

2007-11-12: version 1.0.3

   * The --no-ascend option caused too many bind variables to be used.

Changelog for mysql-parallel-dump:

2007-11-12: version 1.0.0

   * Dump views when --tab is given.
   * Use a module to find databases and tables.
   * Do not shell out to mysqldump for --tab.
   * Removed the --opt option.
   * Check for valid options to mysqldump.
   * Dump table definition and triggers separately for --tab.

Changelog for mysql-parallel-restore:

2007-11-12: version 1.0.0

   * Removed the --sql option, as sort order is implied when --tab is given.
   * Added code to load .trg files (triggers) and load 00_views files.
   * Print out files that are not loaded.

Changelog for mysql-table-checksum:

2007-11-12: version 1.1.18

   * DSN Parsing was broken.

Changelog for mysql-table-sync:

2007-11-12: version 0.9.9

   * DSN parsing was broken when --synctomaster was given with one DSN.
   * Changed --replicate to --synctomaster option.
   * Errors were being hidden in an EVAL when --execute was specified (bug #1819744).

Technorati Tags:backups, data recovery, mysql, parallel dump, sql

Introducing MySQL Parallel Restore

Published at October 15, 2007 in Sys-Admin, Perl, SQL and Tools. 0 Comments

Download MySQL Toolkit

The new release of MySQL Toolkit (version 1051) updates MySQL Parallel Dump in minor ways, but more importantly, it adds MySQL Parallel Restore.

MySQL Parallel Restore is the reverse of MySQL Parallel Dump. You give it one or more files and/or directories, and it discovers all the files contained within them and loads them in parallel. It understands how to load SQL and/or TXT/CSV files. If you give it some of both, it loads the SQL first and then loads the TXT/CSV as delimited files with LOAD DATA INFILE.

It does not parallelize a single table. That is, it doesn’t try to load two files into a table at the same time. But if you’re loading multiple tables, it will do them in parallel.

It has what I consider to be smart defaults. For example, by default it commits between each delimited file it loads. And since the dump tool makes it easy to dump a table in chunks, this makes it much easier on the server to restore a very large table.

I’ve been following the “release early, release often” philosophy with these two tools. You should test carefully before you trust them with your data. If you can’t restore your data accurately, it’s probably a bug. I’ve been testing with the following procedure:

mysql-table-checksum -a ACCUM localhost > checksum-before
mysqldump --all-databases | gzip -c - > sanity.gz
mysql-parallel-dump ... options ... 
mysql-parallel-restore ... options ... default/
mysql-table-checksum -a ACCUM localhost > checksum-after
mysql-checksum-filter checksum-before checksum-after

If the checksums don’t match after restoring, you can restore the original data from the sanity dump. I encourage you to report any bugs you find with this procedure. Incidentally, this exercise taught me that LOAD DATA INFILE is pretty hard to get just right. It has all sorts of weird dependencies on character sets that aren’t documented. That’s why I’m a little cautious and I’m asking you to tell me if you can’t restore correctly.

Technorati Tags:backups, data recovery, mysql, parallel dump, sql

MySQL Toolkit version 1030 released

Published at October 10, 2007 in Perl, SQL and Tools. 0 Comments

Download MySQL Toolkit

This release of MySQL Toolkit updates MySQL Parallel Dump. Together you and I found a few bugs in it (table locking, argument quoting, exit status code). The restore utility is in progress.

Technorati Tags:backups, data recovery, mysql, parallel dump, sql

MySQL Toolkit version 1011 released

Published at October 5, 2007 in Perl, SQL and Tools. 0 Comments

Download MySQL Toolkit

MySQL Toolkit version numbers are based on Subversion revision number. This release is the first past the 1,000-commit milestone. It also marks several days of being in Sourceforge’s top 100 most active projects. It has been in the top 300 for a couple of months, and the top 1000 for, um, a long time. While I would hasten to say I’m not a popularity-contest-focused person, it’s rewarding to see that people think this project is important and useful.

This release of MySQL Toolkit updates MySQL Parallel Dump. I had been using it on a relatively small server; yesterday I took a deep breath and started using it to generate backups from a large server with lots of data and lots of queries. Of course I found a couple bugs and decided I needed more functionality and error handling. The major new functionality is for efficiency; it defers locking as late as possible and releases locks as soon as possible, and with the --setperdb option it treats each database as a set to be locked and dumped together. I also added some information that will be helpful when restoring a table dumped in chunks: the range of values in each chunk. And finally, I made it able to deal with some race conditions like a table being dropped between the time it’s discovered and the time it’s locked (this is very relevant for me because I avoid temporary tables so replication is restartable).

I don’t have a timeline for when I’ll write the corresponding restore utility, but the answer is probably “soon.” This is very much a need-driven project. To begin with, I’m replacing a dump system that didn’t allow point-in-time recovery. Now I’ve got the data I need for point-in-time recovery, but if I have to do that it’ll be a manual job until I write the restore utility.

I am very focused on recovery, not backup, as you’ll see if you buy the second edition of High Performance MySQL :-) I’m just solving my needs in the order of urgency: one must have a backup to do a restoration. I generally don’t like the “urgent, fix now” approach! (For various reasons I won’t get into, I am not able to use ZRM, but I would ordinarily recommend it over rolling your own solution).

Technorati Tags:backups, data recovery, mysql, parallel dump, sql

Xaprb