Pythian Group

Applying binary logs to a MySQL instance is not particularly difficult, using the mysqlbinlog command line utility:

$> mysqlbinlog mysql-bin.000003 > 03.sql
$> mysql < 03.sql

Turning off binary logging for a session is not difficult, from the MySQL commandline, if you authenticate as a user with the SUPER privilege:

mysql> SET SESSION sql_log_bin=0;

However, sometimes you want to apply binary logs to a MySQL instance, without having those changes applied to the binary logs themselves. One option is to restart the server binary logging disabled, and after the load is finished, restart the server with binary logging re-enabled. This is not always possible nor desirable, so there’s a better way, that works in at least versions 4.1 and up:
Read the rest of this entry . . .

There are a lot of discussions going on in the Internet regarding whether we can trust third parties to look after our data. I am not going to add fuel to the fire. I am going to show you how simple it is to backup an Oracle database to the Storage Cloud using the Oracle Secure Backup (OSB) Cloud Module.

First, you need to sign up for an Amazon S3 account and get an Access Key ID and a Secret Access Key. Check AWS pricing first.

Next, download the OSB Cloud Module and unzip it. Read the rest of this entry . . .

Have you ever been asked to restore a database and wondered which backup files were available? And if many were available, which ones you should use, and in which order?

Getting familiar with the msdb schema, especially the backupset and backupmediafamily tables, helps to answer that question since all the backup history is stored in that database. But you would still have to check if the backups are still available on disk (tape?) and figure out what are the commands to restore, and the order in which to restore each file.

Okay, this is not a difficult thing to do, but when you do it over and over again, it becomes tedious, and the automation bug in you starts to look for a better and quicker way to handle it. The bug in me found the following answer.

Read the rest of this entry . . .

Let’s say for you want to move a database from one environment (possibly production) to another (possibly development) on a semi-regular basis. After about the 10^th time doing it manually, you’re ready to automate the process. But how?

That’s what I intend to help with in this post. For this example, I’m running two Virtual servers, both running Windows Server 2003 and SQL Server 2005 SP2. We’ll use the AdventureWorks OLTP database, available for download from http://www.codeplex.com/SqlServerSamples. I’m running SQL Server and SQL Agents with local users that have permissions on both machines. This should work for SQL Server 2005 or 2008, and the Windows version level isn’t important.

There are a couple ways you could go about this task. One way would be to write stored procedures and call them with a batch file using osql.exe and SQL Agent job or your enterprise scheduler. There’s nothing wrong with that approach, but I think that for this task, SSIS packages give more options, flexibility, and simplicity for ongoing management.

At a high level, the SSIS package we are about to create will do the following:
Read the rest of this entry . . .

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:

Read the rest of this entry . . .

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?

Read the rest of this entry . . .