MySQL database introduction, installation, and configuration files

MySQL database introduction, installation and configuration file MySQL database introduction

    mysql是开源关系型数据库，遵循GPL协议。    mysql的特点是性能卓越且服务稳定，开源，无版本限制，成本低，单进程多线程，多用户，基于C/S（客户端/服务端）架构，安全可靠，插入式存储引擎。    mysql的另个版本为MariaDB，MariaDB是单进程，多线程的，提供了诸多扩展和新特性，提供了较多测试组件并且同样开源。

MySQL system architecture

    I. Logical module composition MySQL can be seen as a two-tier architecture. The first layer we are often called SQL layer, all the work before the MySQL database system processing the underlying data is done at this level, including authority judgment, SQL parsing, Execution Plan optimization, Querycache processing and so on the second layer is the storage engine layer, we are often called Storag        The e-engine layer, which is the underlying data access operation implementation Part, consists of a variety of storage engines.        Multiple sub-modules are included in the SQL Layer.        Initialization 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.            The core API Core API module is designed to provide some optimization implementations that require very efficient underlying operations, including implementations of various underlying data structures, implementation of special algorithms, string processing, digital processing, small file I/O, formatted output, and most important memory management parts.        All source code for the core API module is concentrated under the Mysys and Strings folders.        Network interaction module The underlying Network interaction module abstracts the interface API used by the underlying network interaction, realizes the receiving and sending of the underlying network data to facilitate the invocation of other modules, as well as the maintenance of this part, all the source code is under the Vio folder. client& Server Interactive 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.            The user module 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. The access control module requires the Access control module to monitor the user's actions in real time, giving differentThe user has different permissions.            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.            The connection management, connection thread, and thread management 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 the maintenance of these connection threads, including thread creation, thread cache, and so on. Query parsing and forwarding module in MySQL we used to send all client-side commands to the Server as query, and in MySQL Server, the connection thread receives a query from the client and passes the query directly to            Specifically responsible for the classification of various query and then forwarded to the 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. Querycache Module 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, with the Query of a            A hash value to make 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, the Query Cache is significantly more performance-intensive, of course, and the memory consumption is very large. The query optimizer module query optimizer is the optimizationThe client requests the query, according to the client request query statement, and the database of some statistics, on the basis of a series of algorithms to analyze, to obtain an optimal strategy, tell the following program how to get the results of this query statement.                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.                Table Maintenance Module Table status checks, bug fixes, and optimization and analysis of the work is the table maintenance module to do things.                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 showstatus commands commonly used by DBAs, showvariables commands, etc., and the result is returned by this module.            Table Manager This module appears to be easily confused with the table changes and table maintenance modules in the name, but its functionality is completely different from the change and maintenance modules.            Each 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, and it maintains the lock management at the table level.                Logging module Logging module mainly responsible for the entire system level of the logical layer of the log records, including error log,binary log,slow query log.            Replication module replication module can be divided into Master module and slave module two parts, Master module is mainly responsible for reading the binary log on the master side of the replication environment, as well as the slave side of the I/O thread interaction and other work.            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.            The storage Engine interface Module Storage Engine Interface module can be said to be the most distinctive point in 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.        Second, each module work with the understanding of the various MySQL modules, we look at how the MySQL module between each other to work together.        When we execute the start 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, the various storage engines are also started and their initialization work is done, and the connection management module takes over when the whole system is initialized.        The connection Management module initiates a listener that handles client connection requests, including TCP/IP network snooping and a UNIX socket, at which point MySQL Server is ready to accept client requests.        When the connection management module hears the client's connection request (with the help of the Network interaction module), the connection Management module forwards the connection request to the thread management module and requests a connection thread after the two parties have "exchanged greetings" with the protocol defined by the Client & Server Interaction Protocol module.        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. Of course, the connection thread module does not immediately take out a connection request after receiving the connection line thread attached and the client connection, but first by invoking the user module for authorization checks, only after the client request passed the authorization check, he will the client request and the negativeThe connection line thread attached the request.            In MySQL, Uery needs to call parser, which is the query parsing and forwarding module parsing to be able to execute the request.        A command that does not need to call parser to execute a request directly. If full querylogging is turned on in our initialization configuration, then the query parsing and forwarding module invokes the logging module to count the requests into the log, either a query-type request or a command-type request, which is logged into the log.        Therefore, for performance reasons, it is generally very rare to open the full querylogging feature.        When the client request and the connection thread "exchange the password (interworking protocol)", the connection thread begins processing various commands (or query) sent by the client request, accepting the request.        It forwards the received query statement to the query parsing and forwarding module, and the query parser makes basic semantic and syntactic parsing of the query, and then depending on the type of command, some will be processed directly and some will be distributed to other modules for processing.        If it is a query type request, will give control to the query parser, the query parser first analysis to see if it is a select type of query, if so, call the query cache module, let it check whether the query in the query cache already exists.        If so, the data in the cache is returned directly to the connection thread module, which then transmits data to the client through the thread connected to the client.        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. If the parser resolves the result to be a SELECT statement that is not the cache, the control is given to optimizer, the query optimizer module, and if it is a DML or DDL statement, it 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. In fact, the table change management module, depending on the corresponding processing request, is separated by the Insert processor, delete placeprocessors, the update processor, the Create processor, and the ALTER processor 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.        The table Management module first checks to see if the table already exists in table cache, if it is already open, locks-related processing, if not in the cache, you need to open the table file to get the lock, and then hand the open table to the table change management module.        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. However, for the table Change Management module, only visible is the Storage Engine interface module provides a series of "standard" interface, the underlying storage engine implementation module implementation, for the table change management module is transparent, he only need to call the corresponding interface, and indicate the table type,        The interface module invokes the correct storage engine based on the table type to handle the appropriate processing.        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 arithmetic processing functions of each module are highly dependent on the core API modules of MySQL, such as memory management, file I/O, digital and string processing, etc.

MySQL installation mode

    1 、源代码：编译安装    2 、二进制格式的程序包：        展开至特定路径，并经过简单配置后即可使用    3 、程序包管理器管理的程序包        CentOS 安装光盘        项目官方：        https://downloads.mariadb.org/mariadb/repositories/

Common binary format installation process

    Install MARIADB 1) Prepare user groupadd-r MySQL useradd-r-g-u 306-m-d/app/data-s/sbin/nologin            MySQL 2) Prepare the data directory with/app/data as an example, we recommend that you use logical volumes Chown Mysql:mysql/app/data 3) to prepare a binary program Tar XF mariadb-version-linux-x86_64.tar.gz-c/usr/local cd/usr/local ln-sv mariadb-version mysq            L CHOWN-R ROOT:MYSQL/USR/LOCAL/MYSQL/4) prepare configuration file Configuration format: Class INI format, each program is provided with a single configuration file [Prog_name] Configuration file Lookup Order: Overwrite the previous configuration file/etc/my.cnf---/etc/mysql/my.cnf-----default-extra-file=/path/to/conf_file--            > ~/.my.cnf mkdir/etc/mysql/cp/usr/local/mysql/support-files/my-large.cnf/etc/mysql/my.cnf Three options added in [mysqld]: DataDir =/app/data innodb_file_per_table = on ski P_name_resolve = on disallow hostname Resolution 5) Create database file cd/usr/local/mysql/./scripts/mysql_install_db--data Dir=/app/data--user=mysQL to start mysqld at boot time with to copy Support-files/mysql.ser Ver to your system REMEMBER to SET A PASSWORD for the Ma                    RIADB Root USER! To does, start the server, then issue the following commands: './bin/mysqladmin '-                                        U root password ' new-password './bin/mysqladmin '-u root-h centos7 password ' new-password '                                        Alternatively you can run: './bin/mysql_secure_installation ' You can start the MariaDB daemon with:cd '. ';./bin/mysqld_safe--datadir= '                    /app/data ' can test the MariaDB daemon with mysql-test-run.pl CD './mysql-test '; Perl mysql-test-run.pl 6) Preparing the log file Mkdir/var/log/maRiadb/touch/var/log/mariadb/mariadb.log Setfacl-r-M u:mysql:rwx/var/log/mariadb            7) Prepare the service script and start the service CP./SUPPORT-FILES/MYSQL.SERVER/ETC/INIT.D/MYSQLD chkconfig--add mysqld Service Mysqld start 8) Add path path vim/etc/profile.d/mysql.sh export Path=/usr/local/mysql /bin: $PATH 9) safe initialization of/user/local/mysql/bin/mysql_secure_installation Mysql_                Secure_installation Setting the database administrator root password prevents root telnet from deleting anonymous user account Delete Test Database

Yum Installation

        需要安装的包有：            mariadb            mariadb-libs            mariadb-server            mariadb-test（可选）            mariadb-bench        安装完成后也要安全初始化

Configuration file

    MySQL reads multiple configuration files in multiple places and will proceed in the specified order; # my_print_defaults default options is read from the following fi Les in the given order:/ETC/MYSQL/MY.CNF/ETC/MY.CNF ~/.my.cnf When the same parameter appears in different configuration files and has a different value, the post-read The default read configuration file (Mysqld_safe command):--defaults-file=file_name The default configuration file read to add Download a file (Mysqld_safe command):--defaults-extra-file=path configuration file format: INI-style configuration file that can be provided for various MySQL applications                    Configuration information: [MYSQLD] [Mysqld_safe] [mysqld_multi] [Server]             [MySQL]             [Mysqldump] [client] ... parameter = value Parameter:innodb_fil         E_per_table innodb-file-per-table program file: Server-side program: Mysqld_safe, Mysqld_multi Client program: MySQL, mysqldump, mysqladmin tool program: Myisampack, ... mysql--> MySQL protocol--mysqld MySQL: interactive CLI tool; MySQL [options] db_name mysq LD server program: definition of working characteristics display profile Parameters Server parameters/variables: set MySQL operating characteristics; status (statistics) parameters/variables: Save statistics or status in MySQL run Mysql>show [Global | session] variables [like_or_where]; MariaDB [(None)]>               Show global variables like '%ssl% '; How to display a single variable setpoint: mysql> Select @@[global.| Session.]                    System_var_name%: Matches any character of any length; _: matches any single character; variable/parameter level: Global: Set default for all sessions; session: Related to a single session; session creation is inherited from the global; run-time adjustment of server variables: Global: New only after modification                   Session: Valid for the current session only, and immediately, starting with the configuration file modification, after the restart takes effect when the runtime modifies the variable value operation method: mysql> help Set set [Global | session] System_var_name = expr Set [@ @global. | @ @session. | @@]system_vaR_name = Expr Note: Modifications to the global value require the user to have administrative privileges; security initialization after installation: Mysql_secure_installation Parameters that are modified before running: Innodb_file_per_table=on skip_name_resolve=on max_connections = 2000             0 MARIADB Profile and configuration:/etc/my.cnf/etc/my.cnf.d/*.cnf listening 3306/tcp port can be bound with one or all interface IP VIM/ETC/MY.CNF [mysqld] plus one line: Skip-networking=1 only listens on the local client, and all interactions with the server are through        A socket implementation, the socket configuration is stored in the/var/lib/mysql/mysql.sock, can be modified in/etc/my.cnf firewall-cmd--permanent--add-service=mysql Firewall-cmd--reload MariaDB Program composition: Client:mysql:CLI Interactive client program mysqldump, Mys        Qladmin ... Server:mysqld_safe mysqld Mysqld_multi: Two socket addresses for multi-instance server monitoring: IP Socket: Supervisor Listen on TCP 3306 port, support remote Communication UNIX sock: Listen on sock file (/tmp/mysql.sock,/var/lib/mysql/mysql.sock), support native communication only SERVER:LOCALH OST, 127.0.0.1 automatic use of the UNIX sock Client tool command line interactive client program: MySQL mysql option:-uusername: username; default is root -hhost: Server host; Default is localhost-ppassword: User's password; It is recommended to use-p, the default is a null password MySQL user account consists of two parts: ' USERNAME ' @ ' host ' host to restrict which remote hosts this user can connect to MySQL service support Use wildcards:% matches any character of any length 172.16.0.0/16 or 172.16.%.% _ matches any single character

MySQL Database introduction, installation, and configuration files