The Casual MySQL DBA – Operational Basics

So your not a MySQL DBA, but you have to perform like one. If you have a production environment that’s running now, what are the first things you do when it’s not running or reported as not running?

  1. Are the MySQL processes running? (i.e. mysqld and mysqld_safe)
  2. Can you connect locally via cli?
  3. What’s in the MySQL error log?
  4. What are current MySQL threads doing? Locked? long running? how many? idle sources?
  5. Can you connect remotely via cli?
  6. Verify free diskspace?
  7. Verify system physical resources?
  8. If this is a slave, is MySQL replication running? Is it up to date?
  9. What is the current MySQL load, e.g. reads/writes/throughput/network/disk etc?
  10. What is the current InnoDB state and load? (based on if your using InnoDB)

After you do this manually more then once you should be scripting these commands to be productive for future analysis and proactive monitoring?

Is a problem obvious? Does the output look different to what a normal environment looks like? (HINT: This list is not just for when there is a problem)

So moving forward?

  1. Is disk/memory/cpu/network bottleneck an issue you can resolve?
  2. Can you improving locking statements (if applicable)?
  3. Can you identify, analyse and tune long running statements?
  4. Do you know how to restart MySQL?
  5. Do you know who to call when you have a non working environment?
  6. When did your backup last run?
  7. Does your last backup work?

In order to support any level of production MySQL environment you need to know the answers to these questions? If you don’t, then this is your homework checklist for MySQL DBA operations 101. There a number of resources where you can find the answers, and this help can be available online, however never assume the timeliness of responses, especially if your expecting if for FREE! Open source software can be free, open source support rarely is.

The Blue Pill or the Red Pill

At the recent FOSDEM 2010 event, I presented in my keynote Dolphins, now and beyond a option which I termed the “Blue Pill” or the “Red Pill”. The following slide produced noticed interest in a packed room, and subsequent conversation.

While the ownership of MySQL has changed, the option between MySQL and Oracle as a product for use still remains. While MySQL is the most popular for modern online applications, Oracle continues to have the widely used enterprise database product and has a large number of Oracle DBAs in the IT marketplace.

Over the past 5 years I have presented a number of topics on MySQL for Oracle DBA’s. At the upcoming MySQL Users Conference 2010 I will be presenting the first two talks in my new series “MySQLCamp for the Oracle DBA”.

  • IGNITION – is the preparation necessary for a successful launch of a MySQL ecosystem for an Oracle DBA. This volume covers the preparation needed to be ready for ongoing production administration of MySQL.
  • LIFTOFF – Only after a successful preparation covered in IGNITION can you be ready for the implementation and management of a MySQL ecosystem and a successful launch of your product.