Performing Checksums
Most of the Maatkit tools are targeted towards monitoring replication process between master and slave MySQL server instances. So, when a database replication is in progress between master and slave servers, you need to know about the data consistency in the tables in master and slave database. The 'mk-table-checksum' command of Maatkit lets you check the slave's database consistency with its master. 'mk-table-checksum' generates table checksums for MySQL tables, typically useful in verifying that your slaves are in sync with the master. The checksums are generated by a query on the server, and does not generate much network traffic as a result.

There are three different checksum algorithms that mk-table-ckecksum tool can use. You can choose BIT_XOR, CHECKSUM or ACCUM algorithms by using --algorithm argument along with the command. You can provide different databases as arguments along with their respective host details. The following example lists the checksum details for the tables of the demo database, Sakila. Where databases argument is to specify databases, h is to host, P is to the port address, and u and p being arguments for user name and password:

perl mk-table-checksum --databases sakila h=127.0.0.1,P=4406,u=root,p=password

The mk-table-checksum tool gives the checksums for each table of the database which can later be matched with the replicated database for table's consistency.

In case, while using checksum utility for replication process of databases, you find inconsistency between table data of the database, then by using mk-table-sync command, you can re-sync the table that has become corrupt on the slave server. There may be tables with no rows. For this, this tool returns the checksum result as 0 or NULL. Also, this tool facilitates parallel table comparison across different servers simultaneously.

Profiling Queries
'mk-query-profiler' is another command-line tool that reads a file containing one or more SQL queries , executes them and analyzes the output of SHOW STATUS. This utility presents you with statistics of how the query has performed. For instance, it tells the number of table scans, page reads, temporary tables, rows sorted, etc. It not only reports about the execution details of the query, but also reports execution time statistics as well, through which the load on the server can be determined. For this example, we take a query that will join two more tables to the film_actor table of the Sakila database. Write the query on a notepad and save it as query.txt file. The query is as follows:

select * from sakila.film_actor join (sakila.film, sakila.actor) using(film_id, actor_id) order by first_name DESC;

Now we can pass this file as a parameter to query profiler. The following example shows how the mk-query-profiler command will look like:

perl mk-query-profiler --host localhost -P 4406 --user root --ask-pass query.txt

If there are multiple queries that you want to profile, you can save them all in a single file separated by blank lines. In absence of blank lines serving as delimiter, mk-query-profiler won't be able to differentiate between different queries, and when it executes those on MySQL, syntax errors would be reported.

The mk-query-profiler shows the statistics for the query that is executed. It also shows the Optimizer Cost which comes from the Last_query_cost variable.

Visual EXPLAIN
MySQL's EXPLAIN statement can be used to obtain information about how MySQL executes a given query. The output of the statement is in a tabular format, which is not easy to comprehend given the fact that the table columns can run beyond the command console width. If the same information can be represented in a tree-like format, it would be easier to understand. This is now achievable through Maatkit's mk-visual-explain tool. This tool transforms the EXPLAIN statements's output into a hierarchical view of the query plan by doing reverse engineering on the MySQL's EXPLAIN output and making it more understandable.mk-visual-explain also reads the query from a file. For this demonstration also, we use the same query as we did earlier .

perl mk-visual-explain --connect --host localhost --user root query.txt
Thus it can be said, Maatkit is an essential toolkit that can help you maintain consistency between different MySQL instances and manage them better.