MySQL architecture, locations of MySQL installed files and MySQL Shell commands

In this post we will first take a look at the mysql architecture and then explore the locations of the installed mysql files.

First layer is the client-layer:

• It is an interface between the mysql user and the database server.
• It handles client connections and requests.
• authentication and authorisation to ensure secure access.
  

Second layer is the Daemon process:

• mysqld(daemon process) runs core server process that runs in the background to manage the database operations.
• It oversees query execution, caching, session management.

Third layer is the MySQL brain:

• This is the core execution engine responsible for managing and processing data.
• It includes the storage engine layer, with innoDB being the most commonly used storage engine

Fourth layer is the Physical storage layer:

• This layer deals with the actual data and database files on the underlying file system.
• Handles how MySQL stores and receives data physically on disk.

This is the basic overview of the MySQL architecture.

Now lets explore the locations of the installed mysql files.

1. Data Directory

• Also known as datadir
• Default location: /var/lib/mysql
• All new databases that we create reside in this folder

2. Log Files

• Default location: /var/log/mysqld.log
• Single source of information for the errors and the warnings.
• Also contains the initial root password

3. Global configuration files

• Default location: /etc/my.cnf
• Contains all the configuration setting that will be loaded when server starts.
• Only root user can access this file.

4. MySQL socket file

• Default location: /var/lib/mysql
• It allows clients to connect with the server using local connection
• As it is a local file, it provides faster communication compared to TCP/IP.
• This file gets created when mysql server starts.

To look for MySQL Executable programs:

    ls /usr/bin/mysql*

MySQL Shell commands

1. help

\h or \!

Displays help for any of the shell commands

2. quit

\q

Exits from mysql shell

3. status

\s

For how long MySQL has been running, connection id, version of MySQL

4. use

\u database_name

Takes database name as argument 

5. source

\. /path/to/your/file.sql

used to execute sql files.

To transfer a file from your local machine to VM you can execute the following command:

    scp -i /path/to/your/file.pem /path/to/your/file.sql ec2-user@<public-ip-address>

here scp is secure copy paste.

Then you can use the source command or \. to execute the sql file.

    \. newfile.sql

There are many more shell commands that you can use such as edit, system, prompt etc.

Great! Now you have a basic understanding of mysql architecture, the locations of installed MySQL files, and some common MySQL shell commands 🎉