“This is the 26th day of my participation in the Gwen Challenge in November. Check out the details: The Last Gwen Challenge in 2021.”

Introduction to advanced MySQL

What is the DBA?

  • Database Administrator, abbreviated as DBA.
  • Database Administrator (DBA) is a branch of operation and maintenance engineers who manage and maintain the Database management system (DBMS). It is mainly responsible for the whole life cycle management of service Database from design, test to deployment and delivery. The core goal of DBA is to ensure the stability, security, integrity and high performance of database management system. In foreign countries, DBA is also called Database Engineer by some companies. Their work content is basically the same, which is to ensure the stable and efficient operation of Database service 7*24 hours. However, we need to distinguish BETWEEN DBA and Database Developer: 1) The main responsibility of database development engineer is to design and develop database management system and database application software system, focusing on software research and development; 2) THE main responsibility of DBA is to operate and manage database management system, focusing on operation and maintenance management

The responsibilities of a senior DBA

  • Responsible for capacity planning, architecture design, installation and deployment of MySQL
  • Responsible for daily management, monitoring and maintenance of MySQL, and continuous performance optimization of MySQL
  • Responsible for MySQL development support, participate in data architecture planning and design, as well as related business data modeling, design review, SQL code review and optimization

Level of database knowledge mastered by intermediate Java development engineer

  • Familiar with mainstream database, able to complete daily database operations through code (framework)
  • Familiar with SQL, SQL optimization, stored procedure view creation and use
  • Understand the overall architecture of MySQL and understand the features of the MySQL transaction storage engine
  • Understand MySQL index optimization, understand MySQL lock mechanism

As Java development engineers, our focus should be on database knowledge related to development. With this advanced knowledge, the goal is to write more efficient applications.

Professional database maintenance, server optimization, performance tuning and other database-related operation and maintenance work should be left to the DBA

MySQL Logical Architecture

Learning MySQL is like building a house. If you want to build a house very high, the foundation must be stable and solid. Before learning MySQL database, we must first understand its architecture, which is the premise of learning MySQL database well.

This section describes the MySQL architecture

MySQL consists of connection pool, SQL interface, parser, optimizer, cache, storage engine, etc. It can be divided into four layers, namely connection layer, service layer, engine layer and file system layer.

At the top of the connection layer are some clients and connection services, which are not specific to MySQL. All NETWORK BASED C/S network applications should include connection handling, authentication, security management, etc.

Service layer The middle layer is the core of MySQL, including query parsing, analysis, optimization, and caching. It also provides cross-storage engine functionality, including stored procedures, triggers, and views.

The engine layer stores the engine layer, which is responsible for accessing data. The server can interact with various storage engines through apis. Different storage engines have different functions. You can select a storage engine based on actual requirements

Storage layer The data storage layer stores data on file systems running on raw devices and interacts with storage engines

SQL Query Process

  • We use the execution trajectory of an SQL SELECT statement to illustrate the interaction between the client and MySQL, as shown in the figure below.

  1. Connect to MySQL through client/server communication protocol
  2. If Query Cache is enabled and the same SQL statement is found in the Query Cache, the Query result is directly returned to the client. If Query Cache is not enabled or the same SQL statement is not queried, the parser parses the syntax and semantics and generates a parsing tree.
  3. The preprocessor generates a new parse tree.
  4. The query optimizer generates the execution plan.
  5. The query execution engine executes the SQL statement. The query execution engine obtains the query result based on the storage engine type of the table in the SQL statement and the interaction between the corresponding API interface and the cache or physical file of the underlying storage engine. The MySQL Server filters the query result and returns it to the client. If QueryCache is enabled, SQL statements and results are stored completely in QueryCache. If the same SQL statement is executed later, results are directly returned.

MySQL physical files

  • Physical files include log files, data files, and configuration files

The log file

  • Log files include
    • Error log /var/log/mysqld.log
    • Bin log Binary log backup Incremental backup DDL DML DCL
    • Relay log Relay log replication receiving Replication Master
    • Slow log Slow Query log query time exceeds the specified value
-- Check the error log file path
show variables like 'log_error';

+---------------+---------------------+
| Variable_name | Value |
+---------------+---------------------+
| log_error | /var/log/mysqld.log |
+---------------+---------------------+

-- Slowly query the log file path
show variables like 'slow_query_log_file';

+---------------------+-----------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------+
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+

-- bin log log files need to be configured in my.cnf
log-bin=/var/log/mysql-bin/bin.log
server-id=2

-- View relay log parameters
show variables like '%relay%';
Copy the code

Configuration files & data files

  • Configuration file my.cnf

In the my.cnf file you can set some parameters to tune the database.

[client] # The client connection parameters by default port = 3307 # the default connection socket = / data/mysqldata / 3307 / mysql. The sock # for local connection socket socket default - character - set = Utf8mb4 # code [mysqld] # basic set port = 3307 MySQL server listening on port socket = / data/mysqldata / 3307 / MySQL. The sock # for MySQL local communication between client and server to specify a socket file - the file = / data/mysqldata pid / 3307 / MySQL. The pid # pid file directory basedir = / usr/local/MySQL - 5.7.11 # to use this directory as the root directory (installation directory) datadir = / data/mysqldata / # 3307 / data directory data files stored tmpdir = # MySQL/data/mysqldata / 3307 / TMP directory where temporary files Character_set_server = UTF8MB4 # Server Default encoding (database level)Copy the code
  • The data file
-- Check the location of the data file
show variables like '%dir%';

+-----------------------------------------+----------------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------------+
| datadir | /var/lib/mysql/ |
+-----------------------------------------+----------------------------+
Copy the code
  1. The FRM file

Regardless of the storage engine, each table has a. FRM file named after the table name. This file stores the meta information related to the table, including the definition information of the table structure.

  1. The MYD file

Myisam is dedicated to storage engines and stores data of myISAM tables. Each myISAM table will have one. The MYD file echoes this and is also stored in the directory of the owning database

  1. The MYI file

Also myISAM storage engine dedicated, store myISAM table index related information. There is one for each myISAM table. MYI file, its location and.frM and. As MYD

  1. The ibd file

Store innoDB data files (including indexes).

  1. Db. Opt file

This file is available in every self-built library, and records the default character set and validation criteria used by that library.