MySQL Architecture using MOCA

Proper installation of a MySQL database environment will make a tremendous difference in the management of the MySQL database server. This image shows key areas that need to be considered in defining the MySQL configuration. MOCA is used to demonstrate important points to consider.

I've received a lot of emails asking about defining a MySQL environment using MOCA. So I defined this image to try to summarize the core configuration on one slide. On the right it shows the MySQL instance and the storage engines.

• The MySQL instance is the global shared memory and session threads for an individual MySQL database server.
• The storage engines control how I/O is performed on tables and indexes. The individual characteristics of each storage engine will determine where and how the tables and indexes for each storage engine will reside.

Software Installation

The MySQL database software should be installed in its own file system. All dynamic files should be moved out of the software location. The software location should be a static environment.

• The MySQL_BASE directory (/opt/mysql) is the directory point for installing different versions of MySQL database software. Each MySQL database version should be installed in a subdirectory below this base directory location. Naming each software subdirectory with the database version makes it easier to manage.
• The MYSQL_HOME directory (ex. 5.1.22 for the MySQL 5.1.22 software release) is the location of a database software install. This directory should be located below the MySQL_BASE location.

MySQL Global Variables

MySQL global variables define the location of important directories and files for an individual database server instance.

• basedir - is the directory location of the database software installation for a specific release. This is equal to the MYSQL_HOME directory location for a database server.
  
• datadir - is the default location for the database files are stored. Each individual database schema will have a subdirectory below this location that will contain database .frm files and MyISAM files. The default location for the InnoDB tablespace and log files will be stored in the directory defined by the datadir parameter.
• log-bin - defines the location of the binary files required to recover a database to a point in time. This location should be different than the datadir location.
• pid-file - defines the location of the PID file containing the unique process id of the mysqld daemon that runs the database server.
• log-error - location of the primary error and event log file.

Primary operating system directories

These primary directory locations need to be well thought out for a database server. Refer to older blogs that describe MOCA in more detail. Below are sample directories that distribute database software, database files, binary logs for recovery, administration files and backup files into different physical locations. The default installation puts all these in one directory structure. Best practices strongly recommend separating the physical location of different MySQL files. It is a good practice to name directory paths with /dbxx so everyone can understand these directories contain database files. The are template directory names. The mysql2 name is the unique name of a MySQL database server.

• /opt/mysql - base location of MySQL software
• /db01/mysql/mysql2/data - base location of database files
• /db02/mysql/mysql2/binlog - base location of binary logs
• /db03/mysql/mysql2/dbadmin/ - base location of administration directories

• /db04/mysql/mysql2/backups/ - base location of backups

More MOCA please ...

There are certain facts about how database servers work. A database configuration should follow best practices to address these facts. Below is a list of certain truths about database servers that are self-evident:

• Default installs by database vendors are designed to be simple to install and under configured to minimize the initial use of resources. Default installs should not be used to run production servers.
• Relational database servers run faster when memory is configured properly.
  
• Balancing disk I/O is important.
  
• Treat your information and the data you need to do recovery as matter and anti-matter. Never put them together.
  
• Separate your database software from your data.
• Separate your information logs and administration files.
• Database software should never be installed as root or as the operating system administrator.
• Database software, data, logs, administration files, recovery files should be kept separate from other types of files.

Default installs do not address the above. The default software for MySQL is designed to be simple to install and minimize the use of initial resources. However new users perform the default install and then use the default install to run an important database. This default install creates a "lot" of problems if you want to use MySQL as a database server. This is why I created MOCA. MOCA is defined further down in this blog.

MOCA stands for the MySQL Optimal Configuration Architecture. This is a summary of best practices that apply to any database server. Every experienced production DBA from Oracle, DB2, SQL Server, Informix and MySQL uses something similar that contains a list of guidelines and best practices that they have learned from years of experience that should be followed. For years Oracle DBAs have followed OFA (the Optimial Flexible Architecture). MOCA is a summary of best practices modeled from OFA and that I have picked up from 20 years of DBA experience. It is not a complete listing but a set of common sense guidelines that should be considered for a database installation.

I strongly recommend that if you are new to database administration you review MOCA. It pains me to see customers using the default install of MySQL that they installed as an operating system administrator userid and then they wonder why they are having so many problems. The default install will create pain and anguish in your life if you use it as a production configuration.

Create aliases to make MySQL administration easier

When working in a Unix/Linux/Mac OS environment, aliases can make things a lot easier if you are running the same commands over and over again. Here are a few aliases you can define to simplify running MySQL commands. Then just type the alias name. Put these in one of your configuration files dependent on the Unix shell you are running so these aliases will be automatically set in your environment.

Alias examples ($ represents the command line prompt):

$ alias myalias='alias | grep mysql'

$ alias myenv='env | grep mysql '

$ alias mystat='mysqladmin -u root -p status'

$ alias myestat='mysqladmin -u root -p extended-status'

$ alias myping='mysqladmin -u root -p ping'

$ alias myrun='ps -aux | grep mysql | grep -v grep'

$ alias mysql='mysql -u root -p'

$ alias mystop='mysqladmin -u root -p shutdown'

$ alias mysafe='nohup /opt/mysql/5.1.22/bin/mysqld_safe -- defaults-file=/dbadmin/mysql/startup/my.cnf & '

To view your aliases type the following:

$ alias

There are a lot of additional aliases you can define to make your daily tasks easier. Normally aliases are put in a startup file (in the $HOME directory) that is executed each time a Unix shell is started.

• In the Korn shell (ksh) put the aliases in the .kshrc file.
  
• In the Bash shell (bash) put the aliases in the .bashrc file.
  

Favorite 3rd party software with MySQL

Here are the results of a small poll I conducted on favorite 3rd party software with MySQL. The poll results are:

• SQLYog 48%
• Nagios 15%
• Other 14%
• Toad 10%
• Navicat 7%
• DBArtisan 3%
• Innodb Hot Backup 1%
• BMC 1%
• Cfengine 1%
• Zmanda 0%

Since this was an informal poll (take with a grain of salt), so I wanted to add a few insights I have found in customers that I have been working with.

• SQLYog and Nagios are very popular in MySQL environments.
• Navicat has its own fan base in the MySQL world.
• Toad althought popular in the Oracle world is very quirky when used with MySQL.
• Zmanda althought it did not get any votes, I do see it being used quite a bit by students in my classes.
• I was really surprised that I do not see a lot of people using InnoDB Hot Backup.