Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers

up vote 4 down vote accepted

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.
share|improve this answer
    
Thanks for the links and the suggestions. This gives me a good starting point. –  Safado Jul 24 '13 at 15:47
add comment

MySQL Database Maintainance

  • You should always keep a check on certain MySQL variables like
    • MySQL availability
    • insecure users and databases
    • Hacking attempts
    • Error log, InnoDB deadlock
    • max_used_connections
    • Percentage of full table scans
    • Reference: Top 10 things to Monitor

Your environment has replication setup as well so you should always check replication status by executing SHOW SLAVE STATUS\G which can be helpful in finding replication status like

  • Slave_IO_Running
  • Slave_SQL_Running
  • Seconds_Behind_Master
  • etc..

There are certain tools which can be helpful in monitoring your MySQL server with ease, like MySQL Enterprise Monitor, MONYog and Percona Toolkit.

Reference: How to Monitor MySQL replication using different approach

  • MySQL backup and restore you can go for mysqldump and mysqlimport you can schedule it using CRONJOB, check the backup and restore regularly may be once in a week.

Keep a track of the following blogs

share|improve this answer
add comment

What about backup for both data and programs? Review the hardware and storage that MySQL is sitting on Review all logs on a daily or timely basis Disk space even with automatic extends - it should be watched Make sure one person is doing the DBA work (organization) Security - review the policy for data types and who is accessing it Keep your database updated - within reason Prepare for different disasters and recovering from those disasters

/Mark J. Richards

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.