MySQL Architecture Composition--logic module composed for mysql6.7.13

Tag: blank target style title

http://hongge.blog.51cto.com/

One, MySQL Server system architecture

650) this.width=650; "title=" clip_image002 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image002 "src=" http://s3.51cto.com/wyfs02/M02/86/C7/ Wkiol1fkxfqh7dy4aacjwbtddkw778.jpg "" 359 "height=" 224 "/>

In the previous section we learned the composition of MySQL's physical files, and then we will learn the logic modules of MySQL.

The logic module consists of:

MySQL logical structure can be seen as a two-tier architecture, the first layer we are often called SQL layer, in the MySQL database system before the processing of the underlying data is done in this layer, including the right to judge, SQL resolution, Execution plan optimization, query cache processing and so on The second layer is the storage engine layer, which we often call the Storageengine layer, which is the underlying data access operation implementation part, consisting of a variety of storage engines. Therefore, the basic architecture of MySQL can be represented by one of the simplest architectures, as follows:

650) this.width=650; "title=" clip_image004 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image004 "src=" http://s3.51cto.com/wyfs02/M00/86/C7/ Wkiol1fkxfuzkejfaaavawin3ro318.jpg "" 359 "height=" 108 "/>

From the look of MySQL logical architecture is very simple, but in fact each layer contains a lot of their own small modules, especially the first layer of SQL layers, the structure is quite complex. The following is a simple analysis of the SQL layer and storage Engine layer, respectively.

The SQL Layer contains multiple sub-modules, and I'll take a brief look at the following:

1. Initialize the module

Initialization module is when the MySQL Server starts, the whole system to do a variety of initialization operations, such as the various Buffer,cache structure initialization and memory space applications, the initialization of various system variables, various storage engine initialization settings, and so on.

2. Core API

The core API modules are designed to provide some optimization implementations that require efficient underlying operations, including the implementation of various underlying data structures, as well as the most important memory management, file I/O, numeric and string processing, and so on.

。

3. Network Interaction Module

The underlying network interaction module abstracts the interface APIs used by the underlying network interaction, enabling the receiving and sending of the underlying network data to facilitate the invocation of other modules, as well as the maintenance of this part.

4. Client & Server Interaction Protocol Module

Any software system of C/s structure will certainly have its own unique information interaction protocol, MySQL is no exception. MySQL's Client & Server Interaction Protocol Module Section implements all of the protocols that clients have with MySQL during the interaction process. Of course, these protocols are built on existing OS and network protocols, such as TCP/IP and UNIX sockets.

5. User Module

The user module realizes the function, mainly includes the user's login connection permission control and the user's authorization management. He decided to "open the door" to the visitor, just like the door guard at MySQL.

6. Access Control Module

What do you want to do when you visit the guests? For security reasons, it must not be so casual. The access control module is required to monitor the guest's actions in real time and give different privileges to different guests. The function of the access control module is to control the user's access to the data according to the authorization information of each user in the user module and various constraints peculiar to the database itself. Both the user module and the Access control module combine to form the function of security management of the entire MySQL database system.

7. Connection management, connection threading, and thread management

The connection Management module is responsible for listening to various requests to MySQL Server, receiving connection requests, and forwarding all connection requests to the thread management module. Each client request to a MySQL Server on a connection is assigned (or created) by a connection thread for its individual service. The main task of connection thread is to be responsible for the communication between MySQL server and client, accept the command request from client, pass the result information of server side and so on. The thread management module is responsible for managing and maintaining these connection threads. This includes the creation of threads, the cache of threads, and so on.

8. Query Parsing and forwarding module

In MySQL we are accustomed to the command that all client side sends to server side is called query, inside MySQLServer, the connection thread receives a query from the client, it will pass the query directly to the specialized responsible for the various query Classify and then forward to each corresponding processing module, this module is the query parsing and forwarding module. The main task is to make the query statement semantic and grammatical analysis, and then according to different types of operation classification, and then make targeted forwarding.

9. Query Cache Module

The query cache module is a very important module in MySQL, his main function is to submit the client to the MySQL Select Class query request return result set Cache into memory, and a hash value of the query to do a corresponding. After any data changes occur to the base table of the data taken by the query, MySQL automatically invalidates the query's cache. In applications with very high read-write ratios, Query Cache is a significant improvement in performance. Of course, its memory consumption is also very large.

10. Query Optimizer Module

Query optimizer, as the name implies, is to optimize the client request query, according to the client request query statement, and the database of some statistical information, based on a series of algorithms to analyze, to obtain an optimal strategy, tell the following program how to get the results of this query statement.

11. Table Change Management module

Table Change Management module is mainly responsible for the completion of some DML and DDL query, such as: update,delte,insert,create table,alter table and other statements processing.

12. Table Maintenance Module

Table status checks, bug fixes, and optimizations and analyses are all things that the table maintenance module needs to do.

13. System State Management Module

The System State Management module is responsible for returning various state data to the user when the client requests the state of the system, such as the various show status commands commonly used by DBAs, the show variables command, and so on, and the resulting results are returned by this module.

14. Table Manager

This module is easily confused with the table change and table maintenance module, but its function is completely different from the change and maintenance module. As you know, every MySQL table has a table definition file, which is the *.frm file. The main task of the Table manager is to maintain these files, as well as a cache, where the main content of the cache is the structure information of the individual tables. In addition, it maintains table-level lock management.

15. Log Recording Module

The logging module is responsible for logging the entire system-level logic layer, including error log,binarylog,slow query log.

16. Copy Module

Replication module can be divided into Master module and slave module two parts, Master module is mainly responsible for reading the binary log of the master side in the replication environment, and the interaction with the I/O thread of the slave side.

The Slave module has a little more to do than the Master module, which is mainly embodied on two threads in the system. One is responsible for requesting and accepting binary logs from Master and writing the I/O thread in the local relay log. The other one is responsible for reading the related log events from relay log and parsing them into a command that can be executed correctly on the slave side and get exactly the same results as the master, and then handed to the SQL thread executed by slave.

17. Storage Engine Interface Module

The storage Engine interface module can be said to be one of the most distinctive features of the MySQL database. Currently, only MySQL can implement the plug-in management of its underlying data storage engine in a variety of database products. This module is actually an abstract class, but it is precisely because it successfully abstracts the various data processing, it is the feature of today's MySQL pluggable storage engine.

Working with each module

We can draw each of the above modules into a simple diagram to describe the working relationships of each module:

650) this.width=650; "title=" clip_image006 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image006 "src=" http://s3.51cto.com/wyfs02/M01/86/C7/wKiom1fKXfzhu_ G0aafl52ft3ro151.png "" 394 "height=" 378 "/>

How does MySQL work together with each other's modules?

Next, we analyze the entire process by starting MySQL, client connection, request query, get return results, and finally exit.

First, when we execute the MySQL command, the MySQL initialization module reads the system parameters and command line parameters from the system configuration file and initializes the entire system with parameters, such as requesting and allocating buffer, initializing global variables, and various structures. At the same time, each storage engine is also started, and the respective initialization work is done. When the entire system is initialized, it is taken over by the connection management module. The connection Management module initiates a listener that handles client connection requests, including TCP/IP network snooping, and a UNIX socket. At this point, MySQL Server completes the basic boot and is ready to accept the client request.

When the connection management module hears the client's connection request, the connection Management module forwards the connection request to the thread management module to request a connection thread.

The thread management module will now hand over the control to the connection thread module, telling the connection thread module: Now I have a connection request coming over, need to establish a connection, you quickly deal with. Connection thread module after receiving the connection request, first checks whether there is an idle connection thread in the current connection thread pool, and if so, takes out one and the client request connection, and if there is no idle connection thread, establishes a new connection thread to connect with the client request.

Note: The connection thread module does not immediately take out a connection request after receiving the connection thread attached and the client connection, but rather first by invoking the user module for authorization checks, only after the client request passed the authorization check, he will be the client request and responsible for the requested connection line thread attached.

Knowledge Supplement: In MySQL, there are two types of client requests: A query that calls parser, which is the query parsing and parsing of the forwarding module to execute, and a command that does not need to call parser to execute the request directly.

When the client request and the connection thread "exchange the password (interworking protocol)", the connection thread begins to process the various commands (or queries) sent by the client and forwards the received query to the query parsing and forwarding module, and the query parser first queries the query For basic semantic and syntactic parsing, and then depending on the type of command, some will be processed directly and some will be distributed to other modules for processing.

For example, for a SELECT statement, the server checks the cache (query cache) before parsing the query, and if it can find the corresponding query, the server does not have to perform the entire process of query parsing, optimization, and execution, but instead directly returns the result set in the query cache. If it is not a query type that can be used by the cache, or the data in the cache does not have the query, then query will be passed back to the query parser, with the query parser handled accordingly, and distributed to the relevant processing module by the query dispatcher. For example, if the parser resolves a select statement that is not the cache, the control is given to the query optimizer, which is the query optimizer module, which, if it is a DML or DDL statement, is given to the table change management module, if it is some update statistics, detection, The repair and collation of the query will be given to the table maintenance module to process, copy the relevant query to the replication module to do the corresponding processing, the request status of the query is forwarded to the State Collection report module.

Note: In fact, the table change management module is based on the corresponding processing requests, respectively, by the Insert processor, delete processor, update processor, create processor, and alter processor these small modules are responsible for different DML and DDL.

After each module receives the query resolution and the distribution module to distribute the request, first through the Access control module to check whether the connection user has access to the target table and the Target field permissions, if any, will call the Table Management module request the corresponding table, and obtain the corresponding lock.

When the table change management module "gets" The open table, the table's storage engine type and other related information are judged based on the relevant meta information for that table. According to the storage engine type of the table, submit the request to the Storage Engine interface module, call the corresponding storage engine implementation module, and handle accordingly.

When a query or command processing completes (success or failure), control is returned to the connection thread module. If the processing succeeds, the processing result (either a result set or a successful or failed identity) is fed back to the client through the connection thread. If an error occurs during processing, the appropriate error message is also sent to the client, and then the thread module cleans up and continues to wait for the subsequent request, repeating the process mentioned above, or completing a client disconnect request.

If, in the process above, the relevant module changes the data in the database and MySQL opens the Binlog function, the corresponding processing module also invokes the log processing module to record the corresponding change statement as an update event in the binary log file specified by the relevant parameter.

During the processing of each of the above modules, the core processing functions of the cores will depend on the core API modules of MySQL, such as memory management, file I/O, digital and string processing, etc.

Ii. Introduction to the use of MySQL's own tools

The MySQL database not only provides the server-side application of the database, but also provides a large number of client tools, such as mysql,mysqladmin,mysqldump, etc.

1. mysql command

MySQL command is one of the most used command tools, providing a command line interface for users to operate the MySQL server.

Syntax format:

Usage:mysql [OPTIONS] [Database]

Example: # mysql-e "Select user,host from user" MySQL

All you need to do is run MySQL--help and you will get the following basic usage help information:

650) this.width=650; "title=" clip_image008 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image008 "src=" http://s3.51cto.com/wyfs02/M02/86/C7/ Wkiom1fkxf3i58mpaaahyo5mo3g125.jpg "383" height= "Notoginseng"/>

Here are some of the relevant options that you will use in the operations process:

First look at the "-E,--execute=name" parameter, which tells MySQL that I want to execute a command after "-e" instead of logging on to MySQL Server over a MySQL connection. This parameter is useful when we write some basic MySQL check and monitor scripts, which are often used in scripting when you are working with MySQL.

#mysql-H HOSTNAME-PPORT-UUSERNAME-PPASSWORD-E related MySQL SQL statements

Example 1:

Binlog_cache_use and Binlog_cache_disk_use to analyze whether the set of binlog_cache_size is sufficient

650) this.width=650; "title=" clip_image010 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image010 "src=" http://s3.51cto.com/wyfs02/M02/86/C7/ Wkiol1fkxf2xyuxyaaamwouae1a297.jpg "" 395 "height="/>

Example 2: Creating a database, a table, and adding, changing, deleting, and checking the table through a script.

The script reads as follows:

650) this.width=650; "title=" clip_image012 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image012 "src=" http://s3.51cto.com/wyfs02/M00/86/C7/ Wkiom1fkxf7amkmcaabfktbpiea540.jpg "" 397 "height=" 221 "/>

650) this.width=650; "title=" clip_image014 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image014 "src=" http://s3.51cto.com/wyfs02/M02/86/C7/ Wkiol1fkxf-yk0gzaaby13w-u-k243.jpg "" "height=" 166 "/>

Create grant test user can log on at the specified source

650) this.width=650; "title=" clip_image016 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image016 "src=" http://s3.51cto.com/wyfs02/M00/86/C7/wKioL1fKXf_ Qg6vsaaayiqdhooc404.jpg "" Height= "/>"

Testing the test user to connect to the MySQL server

650) this.width=650; "title=" clip_image018 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image018 "src=" http://s3.51cto.com/wyfs02/M02/86/C7/ Wkiom1fkxgdwojxlaaarkbedw-a225.jpg "" 407 "height="/>

Grant Script Execution permissions

#chmod +x/root/mysql1.sh

Execute script:

650) this.width=650; "title=" clip_image020 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image020 "src=" http://s3.51cto.com/wyfs02/M01/86/C7/ Wkiom1fkxgcczqmoaabbahcq_aq618.jpg "" 407 "height="/>

If you use the "-E,--vertical" parameter when connecting, all query results after logging in will be displayed in columns, and the effect is the same as when we end up with "\g" after a query.

#mysql-e-U root-p

650) this.width=650; "title=" clip_image022 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image022 "src=" http://s3.51cto.com/wyfs02/M00/86/C7/ Wkiom1fkxggdide0aabgld5_a_e156.jpg "" 417 "height=" 131 "/>

"-H,--html" and "-X,--xml", after enabling these two parameters, all the results of select will be output in the format "HTML" and "XML", in some cases, such as the need for XML or HTML file format to export some report files, is very convenient.

#mysql-X-u root-p

650) this.width=650; "title=" clip_image024 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image024 "src=" http://s3.51cto.com/wyfs02/M02/86/C7/ Wkiom1fkxggcm47xaabdpcyodgw711.jpg "" 423 "height=" 138 "/>

The "--prompt=name" parameter is a very important parameter option for those who do operations, and its main function is to customize the display of their own MySQL prompt. By default, after we log in to the database via MySQL, the MySQL prompt is just a simple "mysql>" with no additional information. Fortunately, MySQL provides us with custom prompt information through the "--prompt=name" parameter, which can be configured to display the login host address, login username, current time, some of the current database Schema,mysql Server

And so on. I personally strongly recommend the login host name, login user name and the schema of the three items to add the prompt content,

Because when we manage more and more MySQL, more and more frequent operations, it is very easy because the operation of the time do not care too much about their current environment and caused in the wrong environment to execute the wrong command and cause serious consequences. If we add these items to the ToolTip, at least it's more convenient to remind ourselves of the current environment in order to minimize the probability of making mistakes.

The individual strongly recommends the prompt definition: "\\[email protected]\\h: \\d \\r:\\m:\\s>", showing the effect:

650) this.width=650; "title=" clip_image026 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image026 "src=" http://s3.51cto.com/wyfs02/M00/86/C7/ Wkiol1fkxgkaaqsqaaboed0y_gm176.jpg "" 401 "height=" 141 "/>

To switch databases:

650) this.width=650; "title=" clip_image028 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image028 "src=" http://s3.51cto.com/wyfs02/M00/86/C7/ Wkiom1fkxgoqjubaaaautlej5bk103.jpg "" 403 "height="/>

Prompt Explanation:

\u represents the user name, \h represents the hostname, \d represents the current database, \ r hours (12-hour system), \m, \s seconds, \ r The present time, in 24-hour military time (0–23)

The "--tee=name" parameter is also a very useful parameter option for OPS, which tells MySQL to log all input and output content into a file. In some of our larger maintenance changes, it is best to keep all the input and output of the entire operation process in order to be easily checked.

If the MySQL command line state, to do a lot of interaction, in fact, these operations can be recorded in log audit, very simple mysql-u root-p--tee=/path/xxxx.log

650) this.width=650; "title=" clip_image030 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image030 "src=" http://s3.51cto.com/wyfs02/M02/86/C7/ Wkiom1fkxgojgq3haaadn07acky742.jpg "411" height= "Notoginseng"/>

You can also join the [client] in/ETC/MY.CNF on the server.

Tee =/tmp/client_mysql.log.

Note: If you do not have [client], you can add

Or, at the mysql> prompt, execute the following command

650) this.width=650; "title=" clip_image031 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image031 "src=" http://s3.51cto.com/wyfs02/M01/86/C7/ Wkiol1fkxgtdag0naaawwnxhjms437.png "" 418 "height="/>

MySQL's other parameter options can be found through the MySQL official reference manual, or by performing a "MySQL--help" or man MySQL to get help information through self-experimentation to further understand.

2, Mysqladmin

usage:mysqladmin [OPTIONS] Command command ...

Mysqadmin, as the name implies, provides functions that are related to MySQL management. such as MySQL server status check, flush of various statistics, create/delete database, close MySQL server and so on. Mysqladmin can do things, although most of them can be logged on to MySQL Server through MySQL connection, but most of the work done by mysqladmin is easier and more convenient. Here are a few common features you'll often use:

The ping command can easily detect if MySQL Server is still serving properly

MySQL on-machine test:

650) this.width=650; "title=" clip_image033 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image033 "src=" http://s3.51cto.com/wyfs02/M02/86/C7/ Wkiol1fkxgtxwzaoaaablnmlfmy997.jpg "" 418 "height="/>

Test MySQL server for normal service on other hosts

650) this.width=650; "title=" clip_image035 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image035 "src=" http://s3.51cto.com/wyfs02/M00/86/C7/ Wkiom1fkxgxdw0o1aaabvplmxb0508.jpg "" 421 "height="/>

Note 1: Address 192.168.56.11 is the IP of MySQL server

Note 2:mysql server's firewall to allow 3306/TCP communication

Note 3: Create an authorized user on MySQL server

650) this.width=650; "title=" clip_image037 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image037 "src=" http://s3.51cto.com/wyfs02/M02/86/C7/ Wkiol1fkxgwyvrdkaaassvkykim387.jpg "" 416 "height="/>

The status command can get several basic status values for the current MySQL Server:

650) this.width=650; "title=" clip_image039 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image039 "src=" http://s3.51cto.com/wyfs02/M01/86/C7/ Wkiom1fkxgxzbzq7aaays3quiuu260.jpg "" 404 "height="/>

The mysqladmin Status command results in the following columns

Uptime: Is the number of seconds that the MySQL server is running.

Threads: The number of active threads is the number of sessions opened.

Questions: Number of Customer problems (queries) since server startup (as long as you interact with MySQL, regardless of the query table or query server status).

Slow Queries: Is the number of slow queries.

Opens: MySQL has opened the number of database tables

Flush Tables: The number of flush Tables,refresh and reload commands that MySQL has executed.

NOTE: Flush tables//Refresh table (Clear Cache)

Reload Overload Authorization Form

Refresh Erase all tables and close and open log files

Open: Opens the number of tables for the database, starting with server startup.

Queries per second AVG: SELECT statement Average query time

Memory in use (only available when MySQL is compiled with--with-debug)

Max memory used allocated maximum RAM (only available when MySQL is compiled with--with-debug)

Processlist Gets the connection thread information for the current database:

Monitor the running status of the MySQL process:

650) this.width=650; "title=" clip_image041 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image041 "src=" http://s3.51cto.com/wyfs02/M02/86/C7/ Wkiom1fkxgaqu0rgaaa9hh5pcqw799.jpg "" 422 "height="/>

The above three features are often used in some simple monitoring scripts.

Mysqladmin additional parameter options can be obtained by performing "Mysqladmin--help" or man mysqladmin.

Write a simple MySQL monitoring script that reads as follows:

650) this.width=650; "title=" clip_image043 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image043 "src=" http://s3.51cto.com/wyfs02/M00/86/C7/wKiom1fKXgeju_ Reaabrqq1mukw951.jpg "" 418 "height=" 203 "/>

Additional Knowledge point 1:

MySQL's system database:

1) information_schema data dictionary: This database stores information (metadata) for all other databases. Metadata is data about the data, such as database name or table name, the data type of the column, or access permissions.

650) this.width=650; "title=" clip_image044 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image044 "src=" http://s3.51cto.com/wyfs02/M02/86/C7/ Wkiol1fkxges24dgaaamoekkjyo156.png "" 420 "height=" "/>

Main system tables for the INFORMATION_SCHEMA library

TABLES Table : Provides information about the tables and views in the database. (The Table_schema field represents the database name to which the data table belongs.)

SELECT * from INFORMATION_SCHEMA. TABLES WHERE table_schema= ' database name ';

COLUMNS Table : Provides the column information in the table. Describes in detail all the columns of a table and the information for each column.

SELECT * from INFORMATION_SCHEMA. COLUMNS WHERE table_schema= ' database name ' and table_name= ' table name '

table_constraints table : Store primary KEY constraints, FOREIGN KEY constraints, UNIQUE constraints, check constraints. Description information for each field

Elect * from INFORMATION_SCHEMA. table_constraints WHERE table_schema= ' database name ' and table_name= ' table name '

STATISTICS Table : Provides information about the index of a table.

SELECT * from INFORMATION_SCHEMA. STATISTICS WHERE table_schema= ' database name ' and table_name= ' table name '

2) Performance_schema Performance dictionary, this database provides important reference information for database performance optimization

3) MySQL database: This database is also a core database, storing user's permission information and help information.

4) MySQL5.7 provides the SYS system database. SYS database contains a series of stored procedures, custom functions, and views to help us quickly understand the system's meta-data information. The SYS system database combines INFORMATION_SCHEMA and performance_schema data to make it easier to retrieve metadata.

Additional Knowledge point 2:

MySQL About show usage

SHOW databases lists the databases on the MySQL server.

SHOW TABLES [from db_name] lists the tables in the database.

SHOW table STATUS [from db_name] lists the tables information for the database, in more detail.

Show COLUMNS from Tbl_name [from db_name] Lists column information for the table, with show fields from Tbl_name [from Db_name],

DESCRIBE tbl_name [Col_name].

Show full COLUMNS from Tbl_name [from db_name] lists the column information for the table, in more detail, with show all fields from Tbl_name [from Db_name].

SHOW index from Tbl_name [from db_name] lists the indexes information for the table.

SHOW Status Lists the state information for the Server.

SHOW variables lists MySQL system parameter values

Show Processlist view current MySQL query process

SHOW GRANTS for user lists authorization commands for users

3, Mysqldump:

The function of this tool is to dump the data in MySQL server into a text file from the database in the form of an SQL statement. Mysqldump is a logical backup tool for MySQL.

4, Mysqlbinlog

The main function of the Mysqlbinlog program is to analyze the binary logs (i.e. Binlog) generated by MySQL Server.

With Mysqlbinlog, we can parse the contents of the specified time period in Binlog or specify the start and end of the log to parse into an SQL statement.

http://hongge.blog.51cto.com/

MySQL Architecture Composition--logic module composed for mysql6.7.13