MySQL Components

Introduction

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

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.

MySQL components

 

MySQL Server

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

MySQL Clients

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.

Data Directory

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

At server startup, you can change the data directory with the --datadir option

You can also change the data directory in the configuration file

To show the location of the current data directory, you can run this SQL command:

For an in-depth explanation how to configure the MySQL server, check MySQL Configuration.

Storage Engines

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.

Bibliography

The following two tabs change content below.

Eduard Manas

Eduard is a senior IT consultant with over 15 years in the financial sector. He is an experienced developer in Java, C#, Python, Wordpress, Tibco EMS/RV, Oracle, Sybase and MySQL.Outside of work, he likes spending time with family, friends, and watching football.

Latest posts by Eduard Manas (see all)

Leave a Reply