Solved

Database Administration Best Practices

Posted on 2014-01-03
4
445 Views
Last Modified: 2014-11-12
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!
0
Comment
Question by:alon_sabi
  • 2
4 Comments
 
LVL 62

Accepted Solution

by:
gheist earned 250 total points
ID: 39755373
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...
0
 
LVL 36

Assisted Solution

by:Loganathan Natarajan
Loganathan Natarajan earned 250 total points
ID: 39755576
Also use MySQL Tuner to make adjustments to increase performance and stability etc.,  http://majorhayden.com/
0
 

Author Comment

by:alon_sabi
ID: 39760038
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.
0
 
LVL 62

Expert Comment

by:gheist
ID: 39761396
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.
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

It’s 2016. Password authentication should be dead — or at least close to dying. But, unfortunately, it has not traversed Quagga stage yet. Using password authentication is like laundering hotel guest linens with a washboard — it’s Passé.
Exchange server is not supported in any cloud-hosted platform (other than Azure with Azure Premium Storage).
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question