“This is the second day of my participation in the August More Text Challenge.

1. MySQL client and server models

MySQL is a typical C/S structure, C/S structure is client/server model.

MySQL provides clients such as MySQL, mysqladmin, and mysqldump

2. Two connection modes of MySQL

Connect over the network string: TCP/IP#Mysql -uroot -p -h 127.0.0.1 -p3306Through a socket file, socket# mysql -uroot -p -S /tmp/mysql.sock
Copy the code

Example 3.

3.1 Popular Explanation

First, let’s talk about what databases do.

Database is used to store data for a long time, and we all know that memory can only be temporary storage, disk etc can really store data. So where does the database go? It must be on disk, so the database is just a file on disk.

The simple understanding is: database = files on disk.

Since databases can be thought of as files on disk, how do we use them?

If we can use the database directly, it is equivalent to using the files on disk directly. We also know that we can only use files from disk if we read them into memory. This is the correct database flow.

So how does the database read data into memory?

This is where instance comes in, which can be understood as a memory structure and a set of background processes.

Instances are used to read data from disk into memory and use data.

3.2 Professional Interpretation

MySQL starts with:

  • Start the background daemon (mysqld);
  • Generate worker threads;
  • Pre-allocated memory structure for MySQL to process data;

So the example is: MySQL background process + threads + pre-allocated memory structure

MySQL is single-process and multi-threaded, which means that MySQL actually represents a server process in the system, i.e. a process (you can create multiple instances in various ways, install MySQL with a different port number, or create a new server instance with a different port number by workbench, etc.).

4. Mysql vs. mysqld

  • Mysqld is a daemon that runs in the background and listens on port 3306. In order to use a client program, the program must run because the client connects to the server to access the database.
  • Mysql is a command line tool for interactively entering SQL statements or executing them from files in batch mode.

5. Import SQL statements

We usually use some professional commands in Linux to manage objects in our operating system, such as touch and mkidr. These commands are some of the features supported by the Bash shell of the Linux operating system.

The same is true for MySQL, which probably doesn’t use ls. MySQL also has built-in commands for managing data in the database, called SQL (Structured Query Language).

To facilitate learning and memorizing, we divide SQL statements into the following common types:

  • DML: Data Manipulation Language (DML). It is provided by a database management system (DBMS) and is used by users or programmers to manipulate Data in a database. Mainly includesselect,insert,update,delete,merge,explain plan,call,lock tableSuch statements.
  • DDL :(Data Definition Language), used for the creation and destruction of SQL schemas, basic tables, views, and indexescreate,alter,drop,truncate,comment,replace(rename)Generally, transaction operations such as COMMIT are not required.
  • DCL :(Data Control Language), used for database authorization, role Control and other management. Mainly containsgrant,revokeSuch statements.
  • TCL :(Transaction Control Language) for Transaction management of databases. Mainly containssavepoint,rollback.commit.set transactionSuch statements.

The familiar statement, for example, cannot be executed on Linux.

# select user,host from mysql.user;-bash: syntax error near unexpected symbol 'from'Copy the code

We have to get into mysql to run it.

mysql> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
3 rows in set (0.00 sec)
Copy the code

The whole process seems simple enough: enter a statement, hit Enter, and the result is displayed.

But have we ever thought about why in our daily business development, for example, when we want to implement a function, the execution of the statements written by some people is fast, and the execution of the statements written by some people is slow, the results are the same, so why one is fast and the other is slow?

We might think that it is possible to execute slow statements that are very long and complex to write, and yes, that could be the reason.

Let’s dig a little deeper and see if it’s possible that something unexpected happened to the statement during its internal execution, or that some of the operations were expensive. We may not be able to see the problem at face value, and it may not be that long SQL is slow, but rather that longer SQL is faster, and simpler statements are slower.

Therefore, we should not only focus on the result, but also not focus on the process, so we can not find the root cause, next, let’s learn what happens in the process from connecting to the database, entering SQL statements, and then hitting Enter, and then displaying the results.

Next, we will use this query statement to learn about the detailed work flow of MySQL.

6. MySQL internal execution process

6.1 mysqld program structure

The mysqld structure is divided into three layers: connection layer, SQL layer and storage engine layer.

6.2 the connection layer

First, a statement may be initiated by a user, such as Navicat, which executes the following statement:

select user,host from mysql.user;
Copy the code

So Navicat has to connect to MySQL first, so how does it connect? We’ve talked about TCP/IP or socket connections. This means that the connection layer must support both protocols and allow connections using both protocols.

Then, if we are using TCP/IP to connect, we need to enter the username, password, IP, port number. If I enter the port number is 3307, then can log in? It is certainly not possible, because we configure the port number is 3306, that means the connection layer is also used to verify the user name, password, IP, port number, etc., verify the validity.

Once connected, we also said that in MySQL all the functions to be implemented need to be provided by the worker thread, for example, to receive the request statement and return the result. Therefore, a connection thread is automatically opened at the connection layer to receive statements and view the results. You can check the connection threads by showing ProcessList.

mysql> show processlist;
+-----+------+-----------+------+---------+------+----------+------------------+
| Id  | User | Host      | db   | Command | Time | State    | Info             |
+-----+------+-----------+------+---------+------+----------+------------------+
| 282 | root | localhost | NULL | Query   |    0 | starting | show processlist |
+-----+------+-----------+------+---------+------+----------+------------------+
1 row in set (0.01 sec)
Copy the code

Then I start MySQL again on another terminal, execute again, and find that the connection thread has become two.

mysql> show processlist;
+-----+------+-----------+------+---------+------+----------+------------------+
| Id  | User | Host      | db   | Command | Time | State    | Info             |
+-----+------+-----------+------+---------+------+----------+------------------+
| 282 | root | localhost | NULL | Sleep   |   20 |          | NULL             |
| 286 | root | localhost | NULL | Query   |    0 | starting | show processlist |
+-----+------+-----------+------+---------+------+----------+------------------+
2 rows in set (0.00 sec)
Copy the code

The default maximum number of connected threads is 151, although this number can be adjusted. A session is opened without any user requests, and if the session is inactive for 8 hours, the connection is disconnected.

At this point, the work of the connection layer is done and the request is passed on to the next layer.

To summarize, the connection layer functions:

  1. Through connection protocol: TCP/IP and socket connection;
  2. Login authentication: verifies the validity of the user name, password, IP address, and port number.
  3. Provide connection threads: receive user SQL statements and return results (useshow processlistCommand to view threads connected by the user.
  4. Pass the request to the next layer, the SQL layer.

6.3 the SQL layer

  1. Receives SQL statements sent from the upper layer.

    The SQL layer must first receive the SQL before performing any subsequent operations.

  2. Syntax check: Verifies statement syntax to determine whether SQL_MODE is met.

    Syntax checking is definitely necessary, and if you haven’t done it, you can’t do it.

    SQL_MODE is used to solve the following problems:

    1. By setting SQL_MODE, you can complete data verification of different rigors and effectively ensure data accuracy.
    2. You can set SQL_MODE to loose mode to ensure that most SQL statements comply with the standard SQL syntax. In this way, service SQL does not need to be greatly modified when the SQL is migrated between different databases.
    3. Before migrating data from different databases, you can easily migrate data from MySQL to the target database by setting SQL_MODE.

  3. Semantic checking. Determine the type of SQL statement.

    On Linux, we log in as a user, such as the work user, and some files cannot be deleted. You have to switch to root. The same is true for MySQL. But before the permission, we can first make a judgment on the type of SQL statement, and then to determine the corresponding permission.

    • DDL: data definition language, such as CREATE/DROP/ALTER /
    • DCL: data control language, such as Grant/REVOKE /commit
    • DML: data manipulation language, such as insert/delete/update/select
    • DQL: data query language, such as SELECT

  4. Permission check to check whether users have corresponding permissions to libraries and tables.

  5. Parsers: preprocess SQL statements, generate parsers (explain DESC), and generate multiple execution schemes.

    Can MySQL execute statements directly? Will it produce bad results after execution? Do we need to evaluate how expensive statements are? Find the fastest and most appropriate way to do it.

  6. Optimizer: According to the multiple execution plans obtained by the parser, the judgment is made to select the best execution plan.

    Cost model: MySQL used to measure SQL statements in terms of time. Now it measures performance in terms of (CPU/IO/MEM) consumption (based on cost).

    The optimizer algorithm varies from version to version.

  7. Executor, which selects the optimal execution plan to execute the SQL statement and produces the result of execution.

    Actually run the SQL.

    Execution result: A result is provided to the storage engine layer indicating where the result of the query is located on disk.

  8. Provides query caching (disabled by default).

    If some SQL is executed all the time, say 10 million times, then we don’t need to execute all the time, we can provide a query cache to put the request results in the cache.

  9. Binlog: Records binary logs. This function is disabled by default.

    Includes audit logs, common logs, and binlogs.

6.4 Storage Engine Layer

In the SQL layer, when the executor executes, it gets a result that it shows us, but where is our data? It’s still on disk.

On Linux, disks are file systems and cannot be read or written to directly. The same is true for MySQL. MySQL calls this file system, which takes care of special data reads and writes, a storage engine. (similar to FS).

What the storage engine layer does:

  1. According to the SQL execution result, go to the disk to find the corresponding data.

  2. Find the hexadecimal data on disk, return it to the SQL layer again, structure it into a two-dimensional table, and then return it to the user by the dedicated thread of the connection layer, and finally display it.

MySQL logical storage structure

MySQL can be understood as a file system, so many concepts and commands can be learned from Linux. Similar concepts in MySQL and Linux are summarized in the subscript.

MySQL concepts in Linux concept
library directory
create database account charset utf8mb4; mkdir /account
show databases; ls /
use account; cd /account
table file
Columns (fields)
Data row (record) The data line
Table properties File attributes
List of attributes

MySQL physical storage structure

8.1 database

Libraries in MySQL are represented by directories on the file system. Our MySQL data is stored under /data/ MySQL /data

First let’s take a look at our database.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
Copy the code

Then I create an account directory under /data/mysql/data.

# mkdir account
Copy the code

Let’s look at the database again.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| account            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
Copy the code

You can see that there is an additional account database.

Table 8.2

Let’s now look at how data is stored in the common MyISAM storage engine and InnoDB storage engine. Take the mysql.user table as an example.

8.2.1 MyISAM

# cd /data/mysql/data/mysql
# ls. -rw-r----- 1 mysql mysql 10816 1月 13 22:25 user. FRM -rw-r----- 1 mysql mysql 396 1月 14 17:21 user.MYD -rw-r----- 1 mysql Mysql 4096 1月 14 17:21 user.myiCopy the code

For MyISAM storage engine, MySQL uses user. FRM, user.MYD, user.MYI tables to store data.

  • user.frm: Stores table structures (columns, attributes)
  • user.MYD: Records of stored data
  • user.MYI: Store index

8.2.2 InnoDB

-rw-r----- 1 mysql mysql 8636 1月 13 22:25 time_zone. FRM -rw-r----- 1 mysql mysql 98304 1月 13 22:25 time_zone.ibdCopy the code

For InnoDB storage engine, MySQL uses two tables, time_zone. FRM and time_zone.ibd, to store data.

  • time_zone.frm: Stores table structures (columns, attributes)
  • time_zone.ibd: Records and indexes of stored data

In fact, for InnoDB storage engine, MySQL also has a file to store metadata, which is the data dictionary. The file is called ibdata1. We are in /data/mysql/data directory.

InnoDB section, area, page

In general (non-partitioned table) :

  • A table is a segment;
  • A segment consists of multiple areas;
  • In the case that a page is 16K in size, a region consists of 64 consecutive pages, a total of 1M in size;

10. Manage users and rights

10.1 User Functions

  1. Log on to the MySQL
  2. Manage MySQL

10.2 User Definition

Username @’ whitelist ‘, for example: root@’localhost’

10.3 User Operations

10.3.1 Creating a User

mysql> create user user2@'localhost';
mysql> exit
Copy the code
# mysql -uuser2
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 307
......
Copy the code

10.3.2 Creating a User with a Password

mysql> create user user3@'localhost' identified by '123';
Query OK, 0 rows affected (0.00 sec)
Copy the code

10.3.3 Querying User Information:

mysql> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+| user1 | 10.0.0. % | | mysql. The session | localhost | | mysql. The sys | localhost | | root | localhost | | user2 | localhost | | user3 | localhost | +---------------+-----------+
6 rows in set (0.00 sec)
Copy the code

10.3.3 Changing a User Password

mysql> alter user user3@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
Copy the code

10.3.4 Deleting a User

mysql> drop user user3@'localhost'; Query OK, 0 rows affected (0.01sec) mysql>select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+| user1 | 10.0.0. % | | mysql. The session | localhost | | mysql. The sys | localhost | | root | localhost | | user2 | localhost | +---------------+-----------+
5 rows in set (0.00 sec)
Copy the code

10.4 Rights Management

10.4.1 Permission List

  • “ALL” : indicates ALL permissions

    Select, INSERT, update, delete……

  • Grant option to other users

10.4.2 Authorization Commands

Grant permission on target to identified password with grant option;

Purpose:

  • *. *: indicates all tables of all databases in MySQL.
  • account.*: Account All tables under the database
  • account.t1: T1 table under the account database

10.4.3 Authorization Requirements

  1. Create an administrator user that can manage the database on 10 network segments
mysql> grant all on*. *to root@'10.0.0. *' identified by '123' with grant option;
Copy the code
  1. To create an application user, you can perform select, INSERT, delete, and update operations on tables in the Account library on 10 network segments
mysql> grant select.insert.delete.update on account.* to user4@'10.0.0. *' identified by '123';
Copy the code

10.4.4 Revoking permission

  • Check the permissions
mysql> show grants for user4@'10.0.0. *';
+---------------------------------------------------------------------------+| | Grants for [email protected]. * +---------------------------------------------------------------------------+
| GRANT USAGE ON*. *TO 'user4'@'10.0.0. *'                                  |
| GRANT SELECT.INSERT.UPDATE.DELETE ON `account`. *TO 'user4'@'10.0.0. *' |
+---------------------------------------------------------------------------+
2 rows in set (0.00 sec)
Copy the code
  • Recycling permissions
mysql> revoke delete on account.* from user4@'10.0.0. *';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for user4@'10.0.0. *';
+-------------------------------------------------------------------+| | Grants for [email protected]. * +-------------------------------------------------------------------+
| GRANT USAGE ON*. *TO 'user4'@'10.0.0. *'                          |
| GRANT SELECT.INSERT.UPDATE ON `account`. *TO 'user4'@'10.0.0. *' |
+-------------------------------------------------------------------+
2 rows in set (0.00 sec)
Copy the code