I think it is very important that DBAs (especially new ones) do sufficient planning, physical design, risk analysis and backup/recovery planning before installing MySQL. MySQL is an easy database to install and configure on a wide range of platforms.
However if sufficient planning is not performed up front, a DBA is going to have problems as their databases grows, issues come up and more users start accessing the system. A top 10 list of things to do before installing a MySQL database include:
- Scalability: Understand the purpose and potential growth of your database.
- Physical Design: Determine the appropriate physical layout of your database. The defaults are not always the best long term layout.
- Storage Engines: Select the appropriate storage engines and how they impact performance and your backup/recovery strategies.
- Backup and Recovery: Layout your backup and recovery strategy. Pick the appropriate backup/recovery tools for your environment. Then test your backup and recovery strategies sufficiently. Test recovery as well as backups. Understand your recovery times.
- Database Environment: Understand the number of databases that will be created and the maintenance ramifications of the database layout.
- Disaster Recovery: What are the business costs and ramifications of this environment (database server, host server, site, network, ...) being down? Perform appropriate risk analysis.
- Security: Understand how security is being managed and maintained for your users and your databases. Make sure you secure your userids and passwords.
- SQL_MODE and Isolation Level: Set the appropiate SQL_MODE and Isolation Level and make sure you understand the ramifications of the these selections. If running an OLTP environment I highly recommend setting the SQL_MODE to a minimum of traditional.
- Storage: Understand your physical storage layout. Make sure you know the ramifications, limitations, scalability and performance of using separate disks, storage arrays, etc.
- Best Practices: Define a set of best practices that determine your physical layouts, naming conventions, management tools, infrastructure management and document everything.
A few areas that did not make my top ten but I would give honorable mention.
- Make sure and secure the hardware platforms, networks and operating systems where your databases will be running. Along with that take out operating system features you should not be using on a database server. This includes features such as Telnet, web services, print services, etc. These unnecessary OS features have security issues to watch out for and add additional weight (impact performance) to the operating system.
- Read the README files and research the release you are moving to.
- Determine what software you are going to use to manage your MySQL database environment. If you have multiple MySQL databases you should consider the MySQL Network Monitoring and Advisory Service Advisors.
- Administration Advisor
- Schema Advisor
- Performance Advisor
- Security Advisor
- Memory Usage Advisor
- Replication Advisor
- Custom Advisor
The five P's apply to database configurations. Proper Planning Prevents Pitiful Performance. I had a higher pain threshold when I was younger versus today. I have found that proper planning can help you avoid a lot of pain, anguish and late evenings as a DBA.