I have seen many developers who specialize in PHP, Perl, Python or Java, but end up responsible for a database server also. Many start using 50% of their time as “DBA” but lack the depth of the DB technology.
If you find yourself in this position, what is your plan of attack? Here are some questions to ask yourself to help you through the process. These similar patterns can also be applied to any other database technologies, be it Oracle or Postgres:
- Is MySql service running? Maybe a simple “service mysqld status” will tell you if its up or not.
- Can you log into MySQL from within the same server? Log in as root # mysql -h 127.0.0.1 -u root -p
- Do you see anything strange on the database error log? Display the latest error messages # tail -f -n 50 /var/log/mysql/mysql.log
- What are the internal MySQL processes doing? Locked rows? Long running queries? Many connections open? Idle sources? Within MySQL execute “SHOW PROCESSLIST;”
- Can you connect remotely to MySQL? Log in remotely as # mysql -h <SERVER_IP> -u <USER> –p <PASSWORD>;
- Do you have enough disk space? Try # df -h
- Verify system resources with top? If it’s a slave, is MySQL replication running? Is it up to date with the Master? Within MySQL execute “SHOW SLAVE STATUS;”
- What is the current MySQL counters of reads, writes, throughput, network, IO, disk, etc? Within MySQL execute “SHOW ENGINE STATUS;”
- What is the current InnoDB state and load, dirty pages, buffer pool? Use and learn the INNOTOP tool here.
Now that we were able to detect a particular issue that caused MySQL to stop responding, what can we do to resolve the problem and to prevent future downtime? Here is a list of questions to consider:
- Is disk, memory, cpu, network traffic a bottleneck issue you can resolve?
- Can you prevent queries that generate locks (if applicable)?
- Can you identify, analyze and tune long running queries?
- Do you have a more knowledgeable person to call when you have a non-working environment?
- Do you have proper backups and are they up to date?
In order to maintain any production database, you need to know the answers to these questions. If you don’t, then this is your checklist of things you must learn to pass a MySQL DBA operations 101 class. There are many resources online where you can find the answers, however never assume the timeliness of responses when issues arise, especially if you’re expecting if for free. Open source software can be free, but open source support isn’t.
If you have any questions about DBA please don’t hesitate to leave a comment below. Subscribe to our blog and stay tuned for my exciting next post, where our VP of Global Talent Tess Manderson and I will discuss how to become a DevOps Engineer.
Gorilla Logic is always on the lookout for the best and brightest. If you think you have what it takes to join our Band of Gorillas, click below to check out our job postings: