Choosing a MySQL Configuration Strategy

MySQL configurations cover the range from developers installing a simple MySQL database for a LAMP application to the largest Internet companies using MySQL to deliver web content.

There are two different profiles that cover this range for installing, configuring and managing MySQL database servers:

1. Profile 1: Individuals or small organizations that wants to use MySQL to create a simple database for web applications and the LAMP stack.
   
2. Profile 2: Individuals or organizations that are looking at creating larger MySQL databases or may be creating a large number of MySQL databases.

Profile 1:

For this profile, the person can be a non-DBA. This profile can use the default install using the Windows Installation Manager or the Unix or Linux RPMs. The GUI interfaces will take you through a very simple install. MySQL database servers do not take a lot of resources to run. The template files (my.small, my.medium, my.large) can show startup files that contain larger configurations. This is a simple environment to install and manage. I have seen non-DBAs and developers use this environment for years and it works great for them.

This type of DBA is typically using some simple GUI interfaces, doing backups with mysqldump and taking a simple approach for managing MySQL databases. MySQL's basic configuration can make it very popular for the small and medium sized organization.

Profile2:

This profile involves more complex and larger MySQL databases. This environment often benefits from installing and configuring using best practices. This environment is more successful with someone that has DBA experience or training. Implementing a MySQL environment with the MySQL Optimal Configuration Architecture (MOCA) where a DBA organizes a database for performance, high availability, ease of management becomes important for this environment being successful.

Dependent on the size and complexity of the MySQL environment, the DBA for this environment may be more concerned with one or more of these areas:

• How the MySQL servers will be monitored and managed. Using the MySQL Enterprise Monitor, open source monitors or 3rd party monitors becomes important.
  
• What backup strategy to use: Snapshots, InnoDB Hot Backup, Replication, etc.
  
• High Availability (MySQL cluster, other clustering strategies).
• How to balance I/O by spreading data across multiple disks, storage arrays, etc.
  
• Performance Tuning.
• The establishment of database standards, guidelines and best practices (MOCA).
• Change control.
• Having to much coffee.
• Managing test, development and production databases. Developing rollout strategies.
• Security, auditing, Sarbanes-Oxley, COBIT, ITIL.
• Information Lifecycle Management

These two profiles show two ends of the spectrum for MySQL database administration.