The other day while trying to move a schema from one MySQL server to another, I encountered a very odd issue. The schema to be moved contained both MyISAM and InnoDB tables, so the only option I had was to dump the schema using mysqldump on the source server and import it on the destination server. The dump on the source server went fine with absolutely no issues but it failed to import into the second server, and the error message was:

Can't create/write to file ‘/disk1/activity.MYI’ (Errcode: 2)

This was an extremely odd message as the data directory on the destination server was properly setup in terms of ownership and permission. The source and destination MySQL servers have been running without issues for months. Prior to the error, four tables in the dump file were imported into the destination server without any issues whatsoever. Furthermore the source and destination server have the exact same operating system …

Backups are an integral and very important part of any system. Backups allow you to recover your data and be up and running again, in the advent of problems such as system crashes, hardware failures or users deleting data by mistake. I had been evaluating backup solution for a while but to be honest I really wasn't satisfied with the solutions available until I came across XtraBackup and I am loving it since. In this post I intend on showing how to do backups and restores using XtraBackup.

MySQL keeps many different files, some contain real data, some contain meta data. Witch ones are important? Witch can your throw away?

This is my attempt to create a quick reference of all the files used by MySQL, whats in them, what can you do if they are missing, what can you do with them.

When I was working for Dell doing Linux support my first words to a customer where “DO YOU HAVE COMPLETE AND VERIFIED BACKUP?” Make one now before you think about doing anything I suggest here.

You should always try to manage your data through a MySQL client.  If things have gone very bad this may not be possible. MySQL may not start. If your file system get corrupt you may have missing files. Sometimes people create other files in the MySQL directory (BAD).  This should help you understand what is safe to remove.

Before you try to work with one of these files make sure you have the file permissions set …

The MySQL performance team in Oracle has recently completed a series of benchmarks comparing Read / Write and Read-Only performance of MySQL 5.5 with the InnoDB and MyISAM storage engines.

Compared to MyISAM, InnoDB delivered 35x higher throughput on the Read / Write test and 5x higher throughput on the Read-Only test, with 90% scalability across 36 CPU cores.

A full analysis of results and MySQL configuration parameters are documented in a new whitepaper


In addition to the benchmark, the new whitepaper, also includes:
- A discussion of the use-cases for each storage engine
- Best practices for users considering the migration of existing applications from MyISAM to …

The MySQL performance team in Oracle has recently completed a series of benchmarks comparing Read / Write and Read-Only performance of MySQL 5.5 with the InnoDB and MyISAM storage engines.

Compared to MyISAM, InnoDB delivered 35x higher throughput on the Read / Write test and 5x higher throughput on the Read-Only test, with 90% scalability across 36 CPU cores.

A full analysis of results and MySQL configuration parameters are documented in a new whitepaper


In addition to the benchmark, the new whitepaper, also includes:
- A discussion of the use-cases for each storage engine
- Best practices for users considering the migration of existing applications from MyISAM to …

I do not know if it is because we're hosting a free webinar on migrating MyISAM to Innodb or some other reason but recently I see a lot of questions about migration from MyISAM to Innodb.

Webinar will cover the process in a lot more details though I would like to go over basics in advance. You can also check my old post on this topic about Moving MyISAM to Innodb as well as searching the blog - We've blogged so much on this topic.

So what are the basics ?

Regression Benchmarks - Make sure to run regression benchmarks in particular in terms of concurrent behavior. You may have hidden …

First of all: mysqldump is not converting tables. It is something else. Here is the story:

One of my clients had a case when they were migrating to a new mysql server: they used mysqldump to export data from the old server (all InnoDB) and imported it to the new server. When finished, all the tables became MyISAM on the new server. So they asked me this question:
“Why mysqldump is converting my tables from InnoDB to MyISAM?”

First of all we made sure that the tables are InnoDB on the old server. It was true.
Second we run “show engines” on the new server:

+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| …

This week I was one of the presenters at the first Surge Scalability Conference in Baltimore. An event that focused not just on one technology but on what essential tools, technologies and practices system architects need to know about for successfully scaling web applications.

While MySQL is an important product in many environments, it is only one component for a successful technology stack and for many organizations is one of several products that manage your data.

My presentation was on the common MySQL scalability mistakes and how to avoid them. This is a problem/solution approach and is a companion talk with Successful MySQL Scalability which describes design for successfully scalability …

Why should you convert myisam tables to innodb ?

For the perfectly simple reason that innodb tables do not get locked by concurrent selects & inserts. So if you find that your myisam table is suffering for too many locks - due to concurrent selects and inserts, it is time for you to covert the table to innodb.

The simple query which does the trick is

Alter table myisam_table_name engine =

You have a huge mysql table - maybe 100 GB. And you need to run alter on it - to either add an index, drop an index, add a column or drop a column. If you run the simple mysql "alter table" command, you will end up spending ages to bring the table back into production.

Here is a simple hack to get the thing done. The benefit of the hack is that the alter runs quite fast. But since this is a hack