maatkit - 繼 SQLyog ，另一個管理 MySQL 的好工具！ Data Synchronization

繼 SQLyog ，另一個管理 MySQL 的好工具！ Data Synchronization

MySQL Table Sync maatkit

A toolkit that provides advanced functionality for MySQL

http://www.maatkit.org/

http://code.google.com/p/maatkit/

http://www.xaprb.com/blog/2007/04/05/mysql-table-sync-vs-sqlyog-job-agent/

What Can Maatkit Do?mk-table-checksumThis tool efficiently checks whether tables have the same data. It’s the only way to check your slaves for consistency with their master. Its three checksum algorithms can be used to run low-impact, chunked checksum queries on the master, which propagate through replication (guaranteeing consistency without locking) to the slaves, where you can check the slave’s consistency with a simple SQL query. You can also do fast, non-blocking, network-efficient, parallel table comparisons across a virtually unlimited number of servers simultaneously.

People who use this tool report finding problems in replication they never knew existed. If you’re using a replication slave for backups, you should strongly consider checking to make sure the slave really has the same data as the master! If you need to re-sync a table that is corrupt on a slave, see mk-table-sync.

indispensable… a very efficient way to calculate table checksums.
— Kristian Köhntopp, Principal Consultant, MySQL AB

mk-table-syncThis tool offers unique synchronization algorithms that can efficiently find and resolve differences between two MySQL tables, which need not be on the same server. A smart DBA can repair an out-of-sync slave server without reloading all the data in a fraction of the time otherwise required. It is the only such tool that can optionally operate over replication, guaranteeing consistency and avoiding race conditions.

mk-visual-explainIf you’ve ever wished you could see MySQL’s EXPLAIN output formatted as a tree, now you can. This utility transforms EXPLAIN output into a hierarchical view of the query plan, which is significantly easier to read and understand than EXPLAIN.

tree-like explain is something I always wanted to have
— Timour from the MySQL AB Optimizer team

mk-heartbeatMonitors replication delay via a two-part process: a heartbeat on the master, and monitoring on the slave. It tells how far the slave is behind the master at the top of a replication hierarchy, no matter how deep in the hierarchy the slave is and no matter whether the slave threads are running or not (it does not use SHOW SLAVE STATUS). It produces moving averages over arbitrary time windows, so you can see on average how much your slave has lagged during the last 1, 5 and 15 minutes (by default — you can customize this). It can run as a daemon too.

mk-parallel-dumpIf you’ve been wishing for multi-threaded mysqldump, wish no more. This tool dumps MySQL tables in parallel. It is a much smarter mysqldump that can either act as a wrapper for mysqldump (with sensible default behavior) or as a wrapper around SELECT INTO OUTFILE. It is designed for high-performance applications on very large data sizes, where speed matters a lot. It takes advantage of multiple CPUs and disks to dump your data much faster.

It can dump tables in multiple files, each file with a given number of rows or size of data. It is also geared towards recovery: by default it puts each table into its own file, it’s built with tab-delimited dumps in mind (no more artificial database-at-a-time restrictions), and it’s smart about replication and compression. It’s designed to be a lightweight backup tool that’s actually usable without writing a custom script; it understands backup sets (sets of related tables) and you can tell it not to dump tables that haven’t changed, or not to dump tables you’ve dumped recently.

This is a much more efficient and flexible way to dump your data than you’ve probably been doing.

It is also usable as a generic multi-threaded wrapper script to fork off any command you desire; you can use it to run your own script or just to do jobs like mysqlcheck --optimize in parallel.

mk-parallel-restoreThis is the companion program to mk-parallel-dump. It loads files in parallel either by shelling out to mysql or with LOAD DATA INFILE. It has smart defaults, too. If you dumped with mk-parallel-dump --tab, you can reload with mk-parallel-restore --tab /path/to/files.

mk-query-profilerThis unique tool can profile individual queries, batches of queries, external applications and commands. It measures dozens of server statistics and presents them in a way that makes sense. It goes far beyond mere execution time, and calculates such statistics as the number of disk accesses and index lookups. No other tool gives you such deep insight into how much work your queries really cause the server to do.

mk-archiverThis tool archives or purges rows from a table to another table and/or a file. It is designed to efficiently “nibble” data in very small chunks without interfering with critical online transaction processing (OLTP) queries. It accomplishes this with a non-backtracking query plan that keeps its place in the table from query to query, so each subsequent query does very little work to find more archivable rows.

mk-archiver has lots of advanced functionality, but it’s designed to automatically create highly efficient jobs with the default options. For most situations, you should be able to just run it and not worry about it. See the SYNOPSIS in the documentation for a quick start.

mk-archiver is extensible via plugins, offering hooks into the archiving process where you can insert custom code easily. Possibilities include building summary tables in a data warehouse during archiving, handling dependencies such as foreign keys before archiving each row, or applying advanced logic to determine which rows to archive.

mk-deadlock-loggerThis tool gathers information on the last detected InnoDB deadlock and can store it to a file or to a database table. It is easy to configure so it watches your server continually for deadlocks and records them for later analysis and troubleshooting.

mk-duplicate-key-checkerMost database schemas have duplicate or redundant foreign keys and indexes, which use disk space, consume CPU time, increase cost of updates, and reduce your server’s concurrency and throughput. This tool performs advanced analysis on your schema and reports when it finds indexes or foreign keys that partially or wholly duplicate each other.

mk-findThis tool is the MySQL counterpart to the UNIX ‘find’ command. It accepts tests (such as “find all tables larger than 1GB”) and performs actions, such as executing SQL (”DROP TABLE %s”). With this tool at your disposal you can automate many tedious tasks, such as measuring the size of your tables and indexes and saving the data for historical trending, dropping old scratch tables, and much more. It is especially useful in periodic scheduled tasks such as cron jobs.

mk-show-grantsThis tool can help you extract, compare, change, synchronize, and version control your users’ privileges. MySQL’s SHOW GRANTS command produces output that makes these tasks difficult. Maatkit Show Grants extracts and reformats this data so it is much easier to script.

mk-slave-restartThis tool intelligently monitors a replication slave for errors and tries to restart it. It uses an exponentially varying polling interval to avoid busy-waiting on the server and has lots of options to help you target which errors to ignore, such as only errors involving a specific table. This is a more robust way to do what MySQL administrators may find themselves doing when replication is having trouble. The alternatives are generally a small shell script or configuring the slave to ignore errors, neither of which is always a good solution.

mk-slave-delayThis tool implements delayed replication on the slave by sampling binlog positions, then starting and stopping the slave as needed to make the slave lag its master by a specified amount. It does not read binlogs (directly or indirectly), so it has very little overhead.

mk-slave-prefetchThis tool implements Paul Tuckfield’s famous “oracle” algorithm to read ahead of the slave SQL thread in the relay logs, rewriting queries as SELECT and executing them to warm the slave’s caches. This can help an I/O-bound slave SQL thread run faster under some conditions, because it doesn’t have to wait for as much I/O to complete.