2
Architecture
Copyright © 2011, Oracle and/or its affiliates. All rights reserved.
Objectives
After completing this lesson, you should be able to:
• Describe the MySQL client/server model
• Understand communication protocols
• Understand how the server supports storage engines
• Explain the basics of how MySQL uses memory and disk
space
2-2 Copyright © 2011, Oracle and/or its affiliates. All rights reserved.
MySQL Architecture
MySQL client/server model
Database
PHP
mysqld TCP/IP
MySQL Server Process
TCP/IP Socket
Linux
(localhost) TCP/IP
mysql PHP ODBC
Client Client
Process Process
Windows
2-3 Copyright © 2011, Oracle and/or its affiliates. All rights reserved.
Client Programs
• Connect to the MySQL server to retrieve, modify, add, or
remove data.
• Use these client programs to perform the following:
– mysql: Issue queries and view results.
– mysqladmin: Administer the server.
– mysqlcheck: Check the integrity of database tables.
– mysqldump: Create logical backups.
– mysqlimport: Import text data files.
– mysqlshow: Show database, table, and column information.
– mysqlslap: Emulate client load.
• Use MySQL Workbench for database management.
2-4 Copyright © 2011, Oracle and/or its affiliates. All rights reserved.
Administrative and Utility Programs
• Access data files directly without using a client to connect
to the server.
• Use non-client programs to:
– myisamchk: Check and repair MyISAM tables.
– myisampack: Create compressed read-only MyISAM
tables.
– innochecksum: Check InnoDB tablespace file offline.
– mysqlaccess: Check access privileges.
– mysqldumpslow: Summarize the slow query log files.
• Some of the programs have requirements that must be met
prior to running:
– Shut down the server.
– Back up your current tables.
• Review program requirements prior to implementation.
2-5 Copyright © 2011, Oracle and/or its affiliates. All rights reserved.
MySQL Server
• Is the database server program called mysqld
• Is not the same as a “host”
• Is a single process and is multi-threaded
• Manages access to databases on disk and in memory
• Supports simultaneous client connections
• Supports multiple storage engines
• Supports both transactional and nontransactional tables
• Uses memory in the form of:
– Caching
– Buffering
2-6 Copyright © 2011, Oracle and/or its affiliates. All rights reserved.
Server Process
Application Application Application
Connection layer
SQL layer
mysql
d
Storage layer
Disk Memory Network
2-7 Copyright © 2011, Oracle and/or its affiliates. All rights reserved.
Connection Layer
Application Application Application
Connection layer
• Communication protocols
• Threads
• Authentication
mysql
d
SQL layer
Storage layer
2-8 Copyright © 2011, Oracle and/or its affiliates. All rights reserved.
Communication Protocols
Protocol Types of Connections Supported Operating
Systems
TCP/IP Local, remote All
UNIX socket file Local only UNIX only
Shared memory Local only Windows only
Named pipes Local only Windows only
• Protocols are implemented in the client libraries and
drivers.
• The speed of a connection protocol varies with the local
settings.
2-9 Copyright © 2011, Oracle and/or its affiliates. All rights reserved.
SQL Layer
Application Application Application
Connection layer
SQL layer
mysql
• Parser • Query execution
• Authorization
d
• Query cache
• Optimizer • Query logging
Storage layer
2 - 10 Copyright © 2011, Oracle and/or its affiliates. All rights reserved.
SQL Statement Processing
SQL layer Start
Check cache Yes No
SELECT? Parse query.
query.
Optimize
No query.
FOUND?
Execute
Yes
query.
Update cache
query.
Finish
2 - 11 Copyright © 2011, Oracle and/or its affiliates. All rights reserved.
Storage Layer
Application Application Application
Connection layer
SQL layer
mysql
d
Storage layer
Disk Memory Network
• InnoDB • MEMORY • NDB
• MyISAM
2 - 12 Copyright © 2011, Oracle and/or its affiliates. All rights reserved.
Storage Engine: Overview
A storage engine is a server component that offers an interface
between the client and the server (via SQL statements).
• Storage engines are used to:
– Store data
– Retrieve data
– Find data through an index
• Two-tier processing
– Upper tier includes SQL parser and optimizer.
– Lower tier comprises a set of storage engines.
• SQL tier is not dependent on the storage engine:
– The engine does not affect SQL processing.
– There are some exceptions.
2 - 13 Copyright © 2011, Oracle and/or its affiliates. All rights reserved.
Features Dependent on Storage Engine
• Storage medium
• Transactional capabilities
• Locking
• Backup and recovery
• Optimization
• Special features
– Full-text search
– Referential integrity
– Spatial data handling
2 - 14 Copyright © 2011, Oracle and/or its affiliates. All rights reserved.
How MySQL Uses Disk Space
Disk Space
Data Directory
Server Log Files Program
Executable
Status Files Files
InnoDB Log Files
Database Directory
(per Database)
Data and Index Files
Program
Format Files (.frm) Log Files
Triggers
2 - 15 Copyright © 2011, Oracle and/or its affiliates. All rights reserved.
How MySQL Uses Memory
• Global
– Allocated once
– Shared by the server process and its threads
• Session
– Allocated for each thread
– Dynamically allocated and deallocated
– Used for handling query results
– Buffer sizes per session
2 - 16 Copyright © 2011, Oracle and/or its affiliates. All rights reserved.
Memory Structures
Server allocates memory in three different categories:
Server/Shared
Storage Engine/
Shared
Connection/
Session
2 - 17 Copyright © 2011, Oracle and/or its affiliates. All rights reserved.
MySQL Plugin Interface
• Daemon plugin (intended to be successor to UDF
interface)
• Plugin API allows loading and unloading of server
components.
– MySQL 5.1 and later
– At runtime without restarting server
– Supports full-text parser
– Supports storage engines installed as plugins
– Supports INFORMATION_SCHEMA PLUGINS
• Requires PLUGINS table in mysql database
2 - 19 Copyright © 2011, Oracle and/or its affiliates. All rights reserved.
Summary
In this lesson, you should have learned how to:
• Describe the MySQL client/server model
• Understand communication protocols
• Understand how the server supports storage engines
• Explain the basics of how MySQL uses memory and disk
space
2 - 20 Copyright © 2011, Oracle and/or its affiliates. All rights reserved.
Practice 2-1 Overview:
Quiz
In this quiz, you answer questions about the MySQL
architecture.
2 - 21 Copyright © 2011, Oracle and/or its affiliates. All rights reserved.