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.
- 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
- 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.
- /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