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