MySQL Architecture

MySQL Architecture is divided into layers , its quit simple but for now we will focus on outer layer ‘End-User/Client Layer’ and ‘physical storage’

End-User/Client Layer: This is where applications or tools that need to work with MySQL, like scripts, come in. They use something called a MySQL connector—a type of software that lets them talk to the MySQL server. This way, they can log in, send their queries, and manage the database. It works with many programming languages, including C, C++, and PHP.
Physical Storage: Whenever data in the database is altered, whether through a programming language or a tool, these changes are permanently stored in the database’s data files.
Daemon Process: The heart of MySQL’s operations is the main daemon process, known as “mysqld.” It interfaces with the client layer, directing tasks to various specialized processes based on whether they involve stored procedures, functions, data definitions, DML, and so on. These specialized processes are referred to as worker processes.

hey analyze queries for syntax errors, check if the queries are already present in a memory segment called the cache, and determine the most efficient method to fetch the results swiftly. Once these steps are completed, the query is handed off to the storage engine for execution.
–MySQL brain (storage engine ): this have deferent storage engines

The storage engine handles how data is stored, including details like where to put certain information, which indexes to use, and where files are kept. It also keeps track of changes for recovering data if needed. This engine works closely with the physical storage of the database.
InnoDB is the default storage engine used.

MySQL installed File Locations

DATA DIRECTORY

This is also referred to as the datadir
by default the location is /var/lib/mysql
The directory is owned by the mysql OS user, serving as its home directory, this user us automatically created when we install MySQL. if you attempt to delete the user using userdell -rf the whole data directory will be delete as well
Whenever new databases are created, they are stored in separate directories within this data directory.
ca

LOGS FILES

default location is /var/log/mysql.log
This important file records all errors, warnings, and info, etc.
also contain initial root password for newly installed MySQL

GLOVBAL CONFIGURATION

-default location is /etc/my.cnf
contains all the configuration settings that will be loaded when MySQL server start .
it acutely discrip how MySQl server should behave , how MySQL server should run.
you can see from the below image , the file is owned by root , only root or user with sudo can edit this file .

MYSQL Executable programmes

these programmes are located under /usr/bin/mysql*

MySQL services under systemd

MySQL services are controlled by systemd
systemd provide utility called systmctl that we can use to start ,stop , enable , disable MySQL services

MySQL Shell Commands

there are some basic shell commands that are available to use that can be very useful for our day to day DBA activity’s
the command run under MySQL shell , so we need to login to MySQL before we run these commands

help command

-syntax is \h or \?

prints help about MySQL Shell and all available shell command
Display help for any of the shell commands

quit

-quits or exits from MySQL Shell syntax \q

status

shortcut is \s
display for how long MySQL server has been up , what is my connection id , version of MySQL
display if the current user logged in locally or from remote location.

system

syntax is \!
allow you to run OS command from inside MySQL shell

MySQL Socket File

special file that Manges connection to the MySQL server, if user is on the database host and want to connect to MySQL locally , then this file is needed without this file users cannot connect
its owned by MySQL OS user and default location is /var/lib/mysql
keep in in mind the following terms for reference UNIX socket=local connection , TCP/IP= Remote connection
this file is empty by default , but MySQL server creates another file mysql.sock.lock and add pid for MySQLd services

if we check pid of the mysqld services using pidof mysqld and then check the content of the file mysql.sock.lock we will find them matching the same pid

what happen if we delete both file

let see if we attempt to delete both mysql.sock and mysql.sock.lock , will we be able to connect to mysql

you can use rm -rf [filepath] to delete both

we are unable to connect because of the absents of the two files,.

in order to have the files back we need to to restart the mysqld so MySQL can recreate these files
systemctl restart mysqld

MYSQL GLOBAL Variables ⭐️

MySQL server maintains many system variables that are used to configure how MySQL should operate
these system variables has two scops 1.GLOBAL scope & 2.SESSION scope
global variable effect the overall operation of mysql server
each value in global variable can be changed in option file or on command line .
each global variable has default value
all these global variable are identified by using @@ sing

how to see the values of the global variables

there two ways we can see the values of the global variables

using show global [variable_name ]
or using select @@[variable_name]

example of system variable :

max_connection
server_id
sql_mode

retrieve and set these global values

to show all the values in global variable we can use the below command , which will retwrive all the values
show global variables;

these are all by default system variables that come along with MySQL server that are bundled in mysql8 version
deferent version might have deferent list
to find specific global variables , we can filter the list using like and then putting the variable we are looking for

show global variables like 'max_connections';

if you don’t know the exact name of the variable you can type the name follow by %

show global variables like 'max_conne%';

set the value for global variable

to set the value for global variable we will use the set global [variable_name]=[new_value];

for this insist we will change the value for max_connections to 300

set global max_connections=300;

note: that the changed value will return to default if we restart the MySQL , to make the global variable value permeate then we need to make the changes in the file itself .

session variables what are they and how retrieve and edit session variables

session variables affect only in the current session that you login in
default value for session variables can only be changed on command line
once you disconnect the session , new values for session variables will be rollback to the default values
session variables value can be retrieve by using @@variabales
or using show session variables like 'variable_name ';
or using select @@[variable_name];
example of session variables is sql_mode

How retrieve and edit session variables

to view all session variables we can use the below command

show variables ;
or you can use the below
show session variables;

to retrieve certain variable we can use like follow by the variable we are looking for

show session variables like 'sql_mode';

or using the select @@[variable_name];
select @@sql_mode;

edit session variables

the syntax is similar to what we did in global variables , we will use the syntax set sesssion [variable_name]=[new value ];

for this example we will edit the value for sql_mode
first retrieve the value for sql_mode copy the one of output then we will set it up by new value

if we disconnect from session and login again the value will rollback

GETTING SYSTEM VARIABLE HELP

for us is very difficult to remember all the MySQL global variables , what are they , what type of values we can apply.
for that is best if we relay on MySQL pages to get to know these variables
link for MySQL global variables page.

MySQL show commands

MySQL show command is special query to view the information schema of any record that are stored in their database.
this is read only SQL statement , so you can run as many command as you want without effect or changing anything on database .
they have many uses such as

display all connected session
the connections to the database
list all the database
list all the tables in certain database
list all the database jobs that are scheduled to run
it can show how certain table , a user , a database , a trigger event was created .

example of it are :

show databases;
show tables like '%view%';
show binary logs;
show binlog events;
show engines;
show create table | user | database;
show errors;
show warninges;
show events;
show triggers;
show processlist;

the command show databases; will list all the databases in MySQL server

the command show processlist; will view all connections connected to mysql server .

the output showed two rows which means two connection are they one of them is the user that i connecting to , the other one is event scheduler.

the command show events; will display any jobs that are scheduled.

the command show tables ; will display all the tables in the database;
to use it you must change to a database you want to view
use [database_name];

alternately you can view all tables in databases without the need to t change to the databases using use
syntax
show tables from [database name];

MySQL System Databases

MySQL server comes with default system databases

information_schema: A built-in database in every MySQL instance, also known as the system catalogue or data dictionary. It provides access to metadata, which is data about other data. The “tables” in this database are actually read-only views, meaning you cannot perform insert, update, or delete operations on them.
mysql: This database contains tables essential for the MySQL server’s operation. It includes information about user accounts, the event scheduler’s registry, installed plugins, replication system tables, and time zone data.
performance_schema: Focuses on the internal execution of the server, offering insights into how the server is performing. It is designed to collect performance data, including event waits, database locks, and memory allocation details.
sys: A more user-friendly alternative to the performance_schema, providing a collection of views, functions, and stored procedures. These tools assist MySQL administrators in gaining insights into database usage, including the number of connections established, memory usage by users, and the frequency of table scans.
Test: This is a default database that can be safely removed as part of securing a MySQL installation, typically through the mysql_secure_installation script.

when you login in MySQL you can see these databases by using command show databases;

let first check the performance_schema ad list all there tables.

these are acutely not tables , these are acutely views , so you cannot make in modification on them , you are free to explore these views .

before we dig deep its best if we get some information about the table using describe command , whcih will show details about the table and values that are stored and column names .
for instate we will check table called engines
describe engines;

reset can be done on all system databases;

MySQL Local vs Remote Connections

each user that connect to MySQL server ae connected as

localhost-connections
specific-host-connections
any-host-connection

localhost-connections

localhost : means this user can only log in from the database server itself .
root@localhost means root can only login from database server itself and not from anywhere else .

specific-host-connection:

in some cases you want to create a specific application user that can only connect to MySQL server from a specific web server where the application resides .
it could be hostname , or it could be ip address
-host or Ip Address such as webserver01 or 192.168.10.10 it could be hostname , or it could be ip address both are allowed

app_user@webserver01 clearly states that user app_user can only connect from hostname called webserver01

any-host-connection

this will remove all restriction and allow the user to connect from anywhere
–% means any source are allowed
–dba@% means the user dba can connect to MySQL server from anywhere .

retrieve the created user and there connection setting

each user that created to connect to MySQL server is stored in user table indie mysql system database

we can use describe user; to help us understand the structure of the table

let’s view all user that are created in database
select user, host from user;

you can see that user root is set to only allowed to connect from localhost.

MySQL Shell

its separate program that you install on either Linux or windows
the syntax of MySQL shell to start MySQL shell is mysqlsh

advanced command-line client and code editor for MySQL
offers scripting capabilities for Python and JavaScript
mysql shell connect to MySQL server through the X protocol (mysqlx.sock mysqlx.sock.lock).

syntax

mysqlsh> \connect-mysqlx|--mysql user@server:port

example

mysqlsh>\connect-mysql bob@centos7:3306

Visited 4 times, 1 visit(s) today

Category: mysql