MySQL is an open source and widely used database server. This page provides an overview of the main MySQL components.
This is the introductory page to the MySQL administration series:
- MySQL Components
- MySQL Configuration
- InnoDB Storage Engine
- MyISAM Storage Engine
- MySQL Backup and Restore with mysqldump
- MySQL Date Types and Time Zone support
- MySQL User Management and Access Control
- MySQL Table Maintenance
- Administration Commands
These are the main MySQL components:
- MySQL Server. It is the database server. It processes all queries and manipulates databases and tables.
- MySQL Clients. These are programs that communicate with the server.
- Data Directory. This is where MySQL stores the databases.
- Storage Engine. Manages how the information is organised, stored and accessed. MySQL uses a pluggable storage engine architecture. Example storage engines are MyISAM, InnoDB, Memory or Csv.
The picture below graphically summarises these components.
The MySQL Server manages the changes in the data directory, which contains the databases and tables. It also accepts connections from clients and manages their access to the databases.
These are the most important MySQL Server programs:
- mysqld: the database server
- mysqld_safe: a script to start mysqld that adds some safety features. It is especially useful when server starts with errors.
- mysql.server: a Unix script that starts mysqld_safe
- mysqld_multi: Manages multiple mysqld servers in the same machine
These are programs that connect to the MySQL Server to access databases and tables. Below a list of some of the most important MySQL installation clients:
- mysql is an interactive program where you write SQL statements and execute in the server. It then presents the results in a tabular format. It can also run as a batch script.
- mysqladmin is an administrative program that can configure, monitor or shut down the server
- mysqldump is a tool to back up the database
- mysqlcheck and myisamchk are tools to check the integrity of tables and help repair them.
The data directory contains all the information managed by the MySQL server. This includes databases, tables, triggers, store procedures, status files and log files.
The default location of the data directory in Windows is
C:\ProgramData\MySQL\MySQL Server 5.6
At server startup, you can change the data directory with the --datadir option
You can also change the data directory in the configuration file
datadir="C:/ProgramData/MySQL/MySQL Server 5.6/data/"
To show the location of the current data directory, you can run this SQL command:
SHOW VARIABLES LIKE 'datadir';
For an in-depth explanation how to configure the MySQL server, check MySQL Configuration.
A storage engine manages how the table information is organised, stored and accessed.
MySql uses a pluggable storage engine architecture. MySql has multiple storage engines, such as MyISAM, InnoDB, MEMORY or CSV.
MyISAM storage engine
MyISAM is a non-transactional storage engine. It only supports table-level locking, and does not support foreign keys. MyISAM is ideal for read-mostly applications with few writes. For an introduction explanation how to configure a MyISAM engine, check MyISAM Storage Engine.
InnoDB storage engine
InnoDB is the most widely used transactional storage engine. It provides support for row-level locking, foreign key support, clustered indexes and buffer caching. For a detailed explanation how to configure the InnoDB storage engine, check InnoDB Storage Engine.
- MySQL 5.6 Reference Manual. Oracle, 2015
- MySQL: Developer’s Library. Paul DuBois, Pearson Education, 2009
- MySQL Administrator’s Bible. Sheeri Cabral and Keith Murphy, Wiley Publishing, 2009