Database Administration Best Practices

Hello,

I work as a software engineer/ sysadmin and am currently moving into DBA land. I'm tasked to come up with at least 5-10 best practices for our MySQL database environment running on centos 6.5 in AWS. We currently run two MySQL database servers for our web-based SAAS application in master-master replication, with a disaster recovery database as a slave of one of the masters. We also have this exact setup in our stack for a different piece of the application, so in total 6 database servers.

I'm wondering if anyone has any suggestions for what we should be doing or what we can adopt as best practices for managing and maintaining database environments. If there are any DBAs out there that could share their expertise that would be very much appreciated!

Thank you!
alon_sabiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gheistCommented:
https://dev.mysql.com/tech-resources/articles/mysql-administrator-best-practices.html ?
First is done by scripts (couple of them on the web)
Second - just read through.
Third - ask your boss to restore the DBbackup to his desktop... try again until it works...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Loganathan NatarajanLAMP DeveloperCommented:
Also use MySQL Tuner to make adjustments to increase performance and stability etc.,  http://majorhayden.com/
alon_sabiAuthor Commented:
These are great answers. Thank you.

I'm looking for more suggestions about cleanup and optimization tasks that I can run on the databases safely and automcatically through a cron job. As a side project, I run a forum software called Invision Power Board that has regular cleanup tasks that run every so many hours such as clearing sleeping connections, running optimize-table, etc. This is not related to the SAAS application and runs on a different stack.

Just wondering if these are things that can normally be run safely or if they are ok to run only because of the way IPB setup their application.

Also would like to know about any more automatic cleanup tasks I can run.
gheistCommented:
Usually protocol takes care of idle connections (if iptables kill them in 12h or Linux kernel in 2 days)
Optimize table delays access to table while running.
They know better that their app is broken and needs to close idle connections. Or that is so non-realtime that it can wait to optimize biggest table.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.