MySQL system principle

Client and server-side models

MySQL is a single process, multithreaded program MySQL comes with the program MySQL mysqladminmysqldumpmysqld binary program, a daemon process single process multithreaded service structure

Client Connection Service

tcp/10.0. 0.52  -uroot-p123456-s/tmp/-uroot-p123456-s/tmp/-uroot-p123456 using the socket method login

Example

MySQL during the startup process - start the background daemon and generate worker threads - pre-allocated memory structure for MySQL processing data usage instance what is the MySQL background process + Thread + pre-allocated memory structure

MySQL server composition

Connection Layer

Communication Protocol TCP / IP Socket allocation thread, providing service to verify connection legitimacy

SQL Layer

Parser Authorization Optimizer query execution query cache query caching log record

Storage Engine layer

Plug-in disk InnoDB MyISAM memory network The NDB storage engine is a server component that acts as a handler for different table types used by the storage engine to store data to retrieve data through an index to find data double-layered processing upper layer includes SQL parser and optimizer downlevel contains a set of storage engines SQL layer does not depend on storage engine engine does not affect SQL processing with some exceptions

function

Storage media Transaction features lock backup and restore optimization Special Features: Full-text Search referential integrity spatial data processing

MySQL Logical Structure

--    show databasses; --      show tables; --     Select* from database.  Table; -- Field    desc user;

How MySQL uses disk

The database corresponds to the directory table that corresponds to three files[[email protected] MySQL]# LLUser.*-rw-rw----1 mysql mysql 10684 Dec 23:23 user.frm-rw-rw----1 MySQL mysql 488 Dec 20:47 user. MYD-rw-rw----1 MySQL mysql 2048 Dec 20:47 user. MYI MYISAM . frm files are field structures. MyD is the record data. Myi is the index record innodb db.opt t1.frm file is the field structure T1.IBD is the record data and index share table Space : ibdata1 ibdata2 (System Meta data--base table---? information_schema---show)stand-alone table space: T1.ibd t1.frm5. 6 after the default storage engine creates a database and a table to see the difference between separate table spaces

MySQL storage underlying structure ( segments, extents, blocks )

MySQL Connection management

See the following file links

mysql--start-up and connection management
mysql--Configuration file Management

MySQL system principle