This is the 27th day of my participation in the More Text Challenge. For more details, see more text Challenge

Mysql is the database used by many companies. No matter in the job or in the interview, WILL be exposed to MySQL related issues.

Advantage:

  • free
  • Small size, fast speed
  • Easy to use, low maintenance cost
  • Good compatibility, support a variety of operating systems
  • Support for multiple development languages
  • Easy to scale, cluster, and high availability
  • Supports transactions, MVCC, and four isolation levels

Of course, when we use mysql, we encounter various problems: deadlocks, delays, character set errors, slow queries, and so on. Therefore, it is necessary to deeply learn its principles to quickly locate problems and solve problems.

architecture

Architecture diagram of MySQL

The MySQL architecture consists of four parts

The Client layer Connectors

Function: Processes client connection requests and creates connections with the client.

Almost all server-side languages are supported

MySQL Server layer

  • Connection Pool
    • Responsible for handling and storing database and client connections (one thread manages one connection)
    • It contains user authentication module (user authentication and authentication and security management)
  • System administration and the toolset Services & Utilities
    • Provides backup and restoration, security management, and cluster management services and tools
  • SQL Interface SQL Interface
    • Responsible for receiving various SQL statements sent by the client, such as DML, DDL, stored procedure, etc
  • The Parser Parser
    • Parse SQL statements to generate parse trees
  • The query optimizer Opyimizer
    • The execution plan is generated from the parse tree and the appropriate index is selected, and the EXECUTION plan SQL statements are then executed and interacted with the various storage engines
  • Cache Caches
    • The MySQL table cache, record cache, also contains cache parts for each storage engine, and also stores some permissions and session level caches

StorageEngines Storage engine layer

Plug-in storage engine: Any engine can access MySQL as long as it defines an interface to interact with MySQL Server

Storage engines include MyISAM, InnoDB, Archive, Memory Memory, etc

Key storage engines: InnoDB, MyISAM

System file layer

Physical storage of files, including generic query logs, binary logs, data files, error logs, slow query logs, full logs, redo/undo logs, etc

  • Error log:

    • Error message generated during the running of MySQL
    • To view the error log:show variabes like '%log_error%'

  • General query logs:

    • General query information generated during the running of MySQL
    • To view general query logs:show variabes like '%general%'

  • Binary logs:

    • Record the amount of MySQL data. Insert, modify, and delete operations are performed, including the execution time and duration of SQL statements

    • Restore database data and implement MySQL master/slave replication through binary logs

      show variables like '%log_bin%'// Check whether binary log is enabledshow variables like '%binlog%'// View binary log parametersshow  binary logs// View the log fileCopy the code

  • Slow Log Query

    • The execution time of the SQL statement exceeds the specified time (default time is 10s).

      show variables like '%slow_query%'// Check whether the slow log query function is enabledshow variables like '%long_query_time%'// Query Slow query timeCopy the code

  • The data file

    • The db.opt file, FRM file, MYD file, MYI file, ibD file, ibdata file, ibData1 file, ib_logfile0, and ib_logfile1 file are included

  • The configuration file

    • All configuration information about MySQL
    • On Unix/Linux, the file is my, CNF, and on Windows, the file is my.ini.