The Maatkit toolkit is a real blessing for the MySQL DBA. And while its documentation is pretty good, in some cases it’s necessary to read carefully a second and third time to make sure you are not missing an important piece of information. In this article I will comment on mk-table-chksum and mk-table-sync. My comments are mostly aimed at those DBAs who are considering using these utilities with medium or larger-sized databases.

–replicate

This option allows you to store the checksum results on the master, in a table that will get replicated to the slaves. Although it might seem like overhead for a simple check, it really simplifies your life, especially when used in combination with mk-table-sync. I always use it, for the following reasons:

• You only need to run mk-table-chksum on the master.
• A simple query will tell you the slave status.
• When used with --chunksize, it divides the checking and synching into manageable portions.
• It’s the best way to get consistent checks between master and slaves.

I always define the table as an InnoDB table to improve concurrency and avoid deadlocks (see the tool documentation for details). This table can be created on any database. Just make sure that it is going to be replicated. Here’s an example of the table definition (straight from the documentation) with the InnoDB specification at the end:

(more…)

I am often asked what ways are there to minimize downtime when upgrading from SQL Server 2000 to SQL Server 2005, or when moving databases to a new (probably bigger and stronger) environment.

Well, if you can afford having both–old environment and new environment in parallel–this task can be very easy and straightforward . . .

Options

First, let’s see what are the options to copy a database from one server to the other.

1. Detaching and re-attaching the database.
2. Using backup and restore.
3. Using the database copy wizard.
4. Manually (creating schemas + transferring data), probably by using DTS/SSIS packages.
5. And here is another cute method: use the snapshot creation and delivery part of the replication.

I am not fond of the third method — the wizard never works very well for me. The fourth method? — nah, I am too lazy. Attaching and detaching database files? — the original database goes offline until the files are moved to the new location; it’s not what we want.

If the database is small to medium size, the replication snapshot can be used. If the database is larger, backing and restoring the databases might be the best solution. In these methods, the original database can stay on line while the secondary database is being built.

If you have large databases

What are the tasks that usually take the most time when moving large databases using backup and restore?

(more…)