Diamond Notes

In preparation for disaster recovery and for training purposes I asked our Junior DBA to document the innodb_force_recovery options in our internal Wiki.  I am modestly familiar with the command having used it a few times because of data corruption.  However, somehow, I completely missed something that I discovered when discussing his findings with the other DBA.  I am passing it along since some of you might not know of it.

If you are doing a large operation that is modifying a transactional table and something goes wrong and you end up aborting the operation mysql will normally roll back the operation.  Because this is a disk-bound operation it will be many times slower than the original transaction.

Solution is this: if you abort a large operation and it begins to roll back you can kill the entire server process, add the  innodb_force_recovery=3 line to your my.cnf file (the mysqld section) and restart the mysql server.  You can then do any cleanup necessary because the server doesn’t begin transaction rollback as it normally would.  If you were doing a large import that you cancled you could drop the table for example.   Then just remove the innodb_force_recovery line from my.cnf and restart the mysql server.  You should tail the error log as it there will be lots of complaining by the server because of what you did, but in ten or fifteen minutes you should be back online.  I would restart the server again just to make sure everything begins as normal but at that point you can consider yourself in business.  If you can restart the server (which might not always be possible) this is much faster than waiting for a long transaction to roll back.

You should test this in a controlled test environment before having to use it “in the field”.

Resource:   http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html

You can copy the three files  (table_name.frm, table_name.MYI, and table_name.MYD) that form a MyISAM table from one server to another and just add them to the appropriate database directory and they will “appear” in the new database.  However, with Innodb this is not possible.  To copy Innodb databases you either need to dump the database/tables involved and re-import or copy the entire dataset and tablespace files from one server to another.

One caveat about the MyISAM table copy, you should lock the table while copying it to keep the copy from being corrupt due to changes to the file during the copy operation.  Also, check and make sure your owner/permissions are the same after the copy.

Here is somethingthat you might be interested in.  If you are using Linux on your servers and you ssh to the server and then run the mysql client to access the server you can use a .my.cnf file to speed up access.  This is put in your home directory and is automatically parsed by the mysql client when it is invoked.  For example here is a really simple one:

[client]
user =username

password =x&r%@ldc

host = localhost

Now you can just type ‘mysql’ and it connnects you up automatically.  There are many other options you can put in here.  Not sure how this works on Windows — I don’t think it does.  Of course if you are using Windows as a desktop OS you can ssh to the server and then go from there.  The reference page for this is: http://dev.mysql.com/doc/refman/5.0/en/option-files.html

There are several ways to count rows in a table.

SELECT count(*) from table_name;

or

SHOW TABLE STATUS  LIKE ‘table_name%’;

or

SELECT table_name, table_rows FROM information_schema.tables where table_name=’table_name’ and table_schema=’database_name’;

However, the last two methods of getting a row count are only approximate row counts for Innodb tables.  Keep that in mind.

I came up with an orginal *wink* idea of doing a small tip each day.  The post I just did about pagination gave me the idea.  I will do one tip a day as long as I can come up with something original.  Something short.  And I am soliciting input from the readership.  If YOU have an idea for a Tip of the Day shoot me an email at bmurphy AT paragon-cs.com.  If you have a previous post I can “reprint” with appropriate kudos and links I would love that too.

For those who don’t know I started a dial-up  Internet Service Provider in Pensacola, Florida in January of 1998.  I began using RedHat Linux (5.0 or 5.1…can’t remember) a couple of months later.  In addition to RedHat I began using a little database server called MySQL.  It was version 3.21.  Not that I really remember, but I looked up the release date one time :).  Before this, for a period of two and a half years, I was a SQL programmer and an Oracle DBA.

Why the history lesson?  Not because you wanted to know all that.  And I am not bragging.  I wish I could say that I used MySQL on a daily basis ever since.  Not the case.  I worked as a systems admin for a long time after that.  While there where times I used MySQL as part of my job, it wasn’t the focus of my work.

However, longevity tends to give perspective.  And I have worked with and watched MySQL for quite some time.  Back when it was version 3.x most people honestly thought of it as a toy.  There were so many missing features.  And it wasn’t bullet-proof.  Some might have said it was bullet ridden :)  Version 4.x brought Innodb and some much needed stability.  It wasn’t until version 5.X that there were views, triggers, stored procedures and such.  Another important addition was the clustering functionality brought by the new NDB storage engine.  By then, “old school” corporations were beginning to use it.  That really brings us up to now.

Where do we go from here?  Version six is set to bring us a new round of improvements.  I understand online backups are in store (no pun intended).  Falcon as a new storage engine is definitely in order.  Optimizer updates will continue.  There is no real roadmaps at this point beyond version 6.0.

I want to rub the crystal  ball a bit.  See what the next ten years might bring.  The last ten years since brought major versions four, five and (alpha) of six.  Maybe the next ten years will bring six, seven and eight.  I hope to be around discussing these changes and where they bring us.  Maybe large scale clustering more along the lines of Oracle RAC? Parallel query processing using multiple processors as I discussed in a recent post (http://www.paragon-cs.com/wordpress/?p=83)

I could not imagine ten years ago where MySQL is now.  Today I would be foolish if I thought that I have more than a semi-educated guess at what is coming.  Time will tell.  I do know that MySQL will contine to grow, reach new markets and win over converts.

Many times your results are longer than your screen.  You end up scrolling up the terminal screen.  It can be a pain.  One way to solve this problem was shown to me some months ago in a session and it kind of slid right by me (maybe I should have scrolled up the screen :))

I just came across it again and thought I would document it for both me and anyone who hasn’t heard of this. It is quite useful.  All you have to do is this:

mysql> \P less
PAGER set to ‘less’

Then, when your results hit the bottom of the screen, you will get a colon prompt and it will wait for you to hit any key.  Once you hit the end the prompt changes to (END).  Hitting the ‘q’ key will return you to the normal mysql> prompt.  It can be very useful.  Enjoy!!

Some of you are tracking the beta program of MySQL Workbench (http://dev.mysql.com/downloads/workbench/5.0.html).  I just received an email that the latest version (5.0.14) was available for download.  I downloaded it and loaded up some old schemas and played around with it a little.

Let me say it is MUCH better than previous versions.  They changed the underlying graphics display method and it is SO much smoother.  If you haven’t already, take it for a spin.  It’s turning into a nice program.

I hope you monitor your servers.  I really do.  If not, if something isn’t on fire right now you need to set up server monitoring.  There are various systems around for doing this with one of the more popular ones being Ganglia.  If you are already using Ganglia here a webpage from Vladimir Vuksan that I just came across that provides some great scripts for monitoring your i/o usage and some MySQL functionality (queries/sec, etc).  Both are available here.

Thanks Vladimir!!!!

 Start planning now.  The next meeting of the Triangle MUG is March the 5th at 7:00 pm (we begin gathering at 6:30 for pizza).  This month is going to be great .. David Holoboff from Columbia, SC is going to be presenting on:

1) Migration from Oracle/SQL Server/Sybase to MySQL - plans & process, including “gearing up” MySQL for big time (at about 12 - 20 million record inserts daily)

2) Data Warehousing and Business Intelligence (inspired by Ralph Kimball) - and our experience with Pentaho

Don’t miss out.  Meeting is at 2635 Meridian Parkway, Durham NC.