Mysql – Proper MySQL database maintenance

maintenanceMySQL

I hope this isn't too broad of a question. I'm certain it'll be able to help me and any future dba noobs that stumble upon it.

I'm a systems admin that got put into the roll of DBA (because I helped the CEO with his outlook, so I can obviously manage our databases!). It's not that big or busy of a database server… a mysqldump is about 6GB in size and it's taken us 5 years to get it that big. Munin reports that we're averaging 40-60 queries a second at our peak hours.

My boss paid for me to take the Oracle University systems admin course, but having gone through it, it simply explains the different parts of the mysql server, what things do and how they do it. But it's not that in-depth and you definitely don't come out of that course a DBA.

So as the current DBA, what should I do to make sure everything is running smooth? Are there daily maintenance tasks I can perform? Are there certain metrics I should be checking? Or to put it another way, as DBAs, what do YOU do on a daily basis to keep everything in good shape?

If it'll help tailor the answer a little bit, here are some specifics. We have 171 tables, all but 3 are innodb, the others are myisam. We have Master/Slave replication set up between our primary datacenter and our disaster recovery site, using RBR. Version is 5.5.28.

What can I do?

Best Answer

First things first. Make sure you have developed and documented a disaster recovery (DR) strategy. Spend some time thinking up ways that things can go wrong, how to recover from them, and testing them to get an idea of how long it will take, especially when restoring from a backup. Some general ideas:

  • mysql crashed, but server is fine: fix error and start mysql.
  • MySQL has to be restored from a backup: restore from a backup and start mysql <-- test this before it becomes something you have to do in an emergency.
  • Server died and has to be replaced: get a replacement server in place and restore from a backup.

Once you have a DR strategy and a method developed to test your backups in place, then you can move in to more periodic tasks:

  • test your restore process regularly. This ensures familiarity in case when it's necessary.
  • ensure proper indexes. If you're using percona server, you can get statistics on which indexes are unused after a given time (month or so)
  • slow query review. Enable the slow query log with long query time of 1second or so and use pt-query-digest to review them on a weekly/monthly basis.
  • read http://www.mysqlperformanceblog.com/ and blogs from http://planet.mysql.com/ .. on such a tiny database, you'll rarely have many of the performance issues of interest. So you'll have plenty of time to read up on interesting issues and how to resolve them.
Related Question