Notes on Learning MySQL (as an Oracle DBA)

Jun 14, 2010 / By Gwen Shapira

Tags: , ,

I spent some time last month getting up to speed on MySQL. One of the nice perks of working at Pythian is the ability to study during the workday. They could have easily said “You are an Oracle DBA, you don’t need to know MySQL. We have enough REAL MySQL experts”, but they didn’t, and I appreciate.

So how does an Oracle DBA go about learning MySQL?
Obviously you start by reading the docs. Specifically, I looked for the MySQL equivalent of the famous Oracle “Concepts Guide”.
Unfortunately, it doesn’t exist. I couldn’t find any similar overview of the architecture and the ideas behind the database. The first chapter of “High Performance MySQL” had a high level architecture review, which was useful but being just one chapter in a book, it lacked many of the details I wanted to learn. Peter Zaitsev’s “InnoDB Architecture” presentation had the kind of information I needed – but covered just InnoDB.

Thats really too bad because I definitely feel the lack – which I can easily tell you what Oracle does when you connect to a database, run a select, an update, commit or rollback – I can’t say the same about MySQL. So far I managed without this knowledge, but I have a constant worry that this will come back and bite me later.

Lacking a concepts guide, I read the documentation I had access to: Sheeri has nice presentations available for Pythian employees (and probably customers too. I’m not sure if she ever released them to the whole world). The official documentation is not bad either – it covers syntax without obvious errors and serves as a decent “how do I do X?” guide.

But reading docs is only half the battle. The easier half too. So I installed MySQL 5.1 on my Ubuntu from ready packages. Then I installed MySQL 5.5 from the tarball – which was not nearly as much fun, but by the time this worked I know much more about where everything is located and the various ways one can mis-configure MySQL.

Once the installation was successfull, I played a bit with users, schemas and databases. MySQL is weird – Schemas are called databases, users have many-to-many relation with databases. If a user logs in from a differnet IP, it is almost like a different user. If you delete all the data files and restart MySQL – it will create new empty data files instead. You can easily start a new MySQL server on the same physical box by modifying one file and creating few directories.

MySQL docs make a very big deal about storage engines. There are only 2 things that are important to rememeber though: MyISAM is non-transactional and is used for mysql schema (the data dictionary), it doesn’t have foreign keys or row level locks. InnoDB is transactional, has row level locks and is used everywhere else.

There are a confusing bunch of tools for backing up MySQL. MySQLDump is the MySQL equivalent of Export. Except that it creates a file full of the SQL commands required to recreate the database. These files can grow huge very fast, but it is very easy to restore from them, restore any parts of the schema or even modifying the data or schema before restoring.
XTRABackup is a tool for consistent backups of InnoDB schema (remember that in MyISAM there are no transactions so consistent backups is rather meaningless). It is easy to use – one command to backup, two commands to restore. You can do PITR of sorts with it, and you can restore specific data files. It doesn’t try to manage the backup policies for you the way RMAN does – so cleaning old backups is your responsibility.

Replication is considered a basic skill, not an advanced skill like in the Oracle world. Indeed once you know how to restore from a backup, setting up replication is trivial. It took me about 2 hours to configure my first replication in MySQL. I think in Oracle Streams it took me few days, and that was on top of years of other Oracle experience.

Having access to experienced colleagues who are happy to spend time teaching a newbie is priceless. I already mentioned Sheeri‘s docs. Chris Schneider volunteered around 2 hours of his time to introduce me to various important configuration parameters, innoDB secrets and replication tips and tricks. Raj Thukral helped me by providing step by step installation and replication guidance and helping debug my work. I’m so happy to work with such awesome folks.

To my shock and horror, at that point I felt like I was done. I learned almost everything important there was to know about MySQL. It took a month. As an Oracle DBA, after two years I still felt like a complete newbie, and even today there are many areas I wish I had better expertise. I’m sure it is partially because I don’t know how much I don’t know, but MySQL really is a rather simple DB – there is less to tweak, less to configure, fewer components, less tools to learn.

Jonathan Lewis once said that he was lucky to learn Oracle with version 6, because back then it was still relatively simple to learn, but the concepts didn’t change much since so what he learned back then is still relevant today. Maybe in 10 years I’ll be saying the same about MySQL.

7 Responses to “Notes on Learning MySQL (as an Oracle DBA)”

  • Chen,
    Thanks for this eye opening report. We in the MySQL community are so used to our daily routine that we sometimes forget how the beginners may cope when coming from a different background.
    Specifically, I would like to know what did you expect from a “concepts guide”? Could you be more specific about it? It would be important to know what kind of doc you were looking for, how detailed, and covering which topics. I will pass that information to the documentation team, which is always eager to improve their text.

    Thanks

    Giuseppe

  • Gwen,

    Have you looked at the MySQL Administrator’s Bible? There are chapters on transactions (chapter 9), server tuning (chapter 10) and storage engines (chapter 11) that should give you a sense of what you’re looking for in a concepts guide.

    There are copies @Pythian HQ, but since you’re not there, I suggest buying a copy off Amazon.com — 40% off retail price (so it’s about $30 instead of $50) and free shipping (because it’s over $25).

  • Gerry says:

    Hi Gwen,

    Sheeri’s book should be in your bookshelf as well, as she pointed out. Subscribe to Planet MySQL (http://planet.mysql.com) for ongoing learning. Although you covered 80% of what needs to be known to be a MySQL DBA, understanding the remaining 20% is what will make you an expert.

    Good luck,
    G

  • Francis says:

    You forgot to mention how some queries, which Oracle would throw out as illegal, seem to run fine but return nonsense. These are usually group by queries. Oracle’s parser is much stricter about correctness of things like group by, and correctness in general. You can’t assume the parser will protect you the way you can with Oracle.

    There’s also the interesting feature that ” is an empty string in MySQL and NULL in Oracle – this can trip you up until you get used to it.

    I *do* like not having the DUAL table though.

  • Gary says:

    The bit I don’t get is where is the line drawn between the responsibilities of MySQL and those of the Storage Engine. A lot of the Oracle ‘concepts’ seem to be at that lower layer (read consistency, constraints, locking, physical structures…).

  • Gwen Shapira says:

    Gary,

    First of all, most of the time you don’t really need to worry about what component is responsible for what. As long as you remember that InnoDB acts like a real database (with transactions, constrains, row locks) and MyISAM is basically a text file per table.

    To make things even more confusing – the optimizer is part of MySQL except in the case of InnoDB which has its own optimizer (or maybe just parts of optimizer).

    The documentation places tons of emphasis on the storage engine thing, which serves more to confuse than to clarify, IMO.

    Sheeri’s book doesn’t fall into this trap, btw :)

  • Gwen Shapira says:

    Francis,

    On one hand a parser that will not throw errors is indeed annoying. On the other hand, I’ve been programming perl for years – I can handle this.

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>