1. A little configuration after installation (centos7+mysql5.6)
Modify the character set (long by default, where latin character set text is garbled and needs to be replaced):
show variables like 'collation_%'; //This is a view statement show variables like 'character\_set\_%'; //Is also a view statement +--------------------------+--------+ | Variable_name | Value | +--------------------------+--------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | +--------------------------+--------+ +----------------------+-------------------+ | Variable_name | Value | +----------------------+-------------------+ | collation_connection | utf8_general_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +----------------------+-------------------+
Find my.cnf under etc, if you can't find it, go to / usr/share/mysql and copy a file like my-default.cnf into the etc directory, enter my.cnf file, add default-character-set=utf8 and character-set-server=utf8 configurations under [client] and [mysqld], respectively, or add one yourself if you don't have [client].
2. Location of log and configuration files and data files in Mysql
show variables like 'log_%'; +----------------------------------------+---------------------+ | Variable_name | Value | +----------------------------------------+---------------------+ | log_bin | OFF | | log_bin_basename | | | log_bin_index | | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | log_error | /var/log/mysqld.log | | log_output | FILE | | log_queries_not_using_indexes | OFF | | log_slave_updates | OFF | | log_slow_admin_statements | OFF | | log_slow_slave_statements | OFF | | log_throttle_queries_not_using_indexes | 0 | | log_warnings | 1 | +----------------------------------------+---------------------+
You can see if logging is enabled
log_bin # Binary logs for master-slave replication log_bin_basename # Binary log file name, log_bin_basename not specified, bin log uses host name to name bin log log log log_bin_index # Binary Index File Name log_bin_trust_function_creators # log_bin_use_v1_row_events # log_error # Error Log log_output # FILE means log to file TABLE means log to database log_queries_not_using_indexes | OFF | log_slave_updates log_slow_admin_statements log_slow_slave_statements log_throttle_queries_not_using_indexes log_warnings
2.1,binlog
A binlog is a binary log that records all database table structure changes (such as CREATE, ALTER TABLE...) and table data modifications (INSERT, UPDATE, DELETE...). If an update operation does not cause data changes, it is also recorded in a binlog
Bilog does not record operations such as SELECT and SHOW because they do not modify the data itself, but you can query the generic log to see all the statements MySQL has executed.
This binary log contains two types of files:
- index files (file name suffix.index) are used to record which log files are being used
- The log file (file name suffix is.00000*) records all DDL and DML statement events (except for data query statements) in the database.
For instance:
stay my.cnf Of [mysqld]Add the following configuration below server-id = 11 log-bin = /var/mysqllog/123log #This is the location of the log file and requires mysql users to have access, which is not normally available chown mysql -R /var/mysqllog/123log #Change the owner of the folder so that mysql has access
In this place, I often can't start the mysqld service when I try, check the log, usually you can see errors like (Errcode: 13 - Permission denied) under /var/log/mysqld.log, so you need to change the permissions of the folder you want to use, because mysqld starts using the mysql user of the system, so you need to give mysql user permissions.
You can see that two files have been generated in this location, with the extension index being the record file for the binary log file currently in use.
To view its contents, it is the name of the binlog log file (with the extension number).
+----------------------------------------+----------------------------+ | Variable_name | Value | +----------------------------------------+----------------------------+ | log_bin | ON | | log_bin_basename | /var/mysqllog/123log | | log_bin_index | /var/mysqllog/123log.index |
By querying the log information again, you can see that the first three variables have changed.
Of course, you can also add the following configurations to my.cnf
log-bin-index = /var/mysqllog/456log #Customize the location and name of that index file #log-bin-basename does not have this variable name
In innodb, there are actually two parts, one in the cache and the other on the disk. There is an industry term for brush disk, which means to brush logs from the cache onto the disk. There are two parameters related to brush disk: sync_binlog and binlog_cache_size. These two parameters work as follows:binlog_cache_size:Size of the binary log cache section, default 32k sync_binlog=[N]: Indicates how many write buffers to flush the disk once.The default value is 0, and binlog_cache_size is set too large, which causes memory waste. A binlog_cache_size setting is too small, which frequently writes buffer logs to temporary files. sync_binlog=0: indicates that the point of time to refresh the binlog is determined by the operating system itself, which refreshes the cached data to disk at regular intervals, which is the best performance. sync_binlog=1, which represents every eventWhen a transaction commits, the binlog is refreshed to disk, and the other N indicates that the transaction commits to disk once.
There is a consistency problem here, sync_binlog=N. When the operating system crashes, the data may not be synchronized to disk. Restarting the database again may cause data loss.
When sync_binlog=1, when the transaction is committed, the data is written to the binlog, but not to the transaction log (redo log and undo log, about which we will talk later)At this time, the downtime happened, the database was restarted, and the data was rolled back. However, the binlog has been recorded, and there is inconsistency. This transaction log and binlog consistency problem allows you to query mysql's internal XA protocol, which solves this consistency problem.
Bilog is not unique to InnoDb. You should know that the most significant difference between Innodb and mysiam is that they support transactions. One does not support transactions. You can say that binlog logs binary logs based on transactions, such as sync_binlog=1.Bilog is written every time a transaction is committed. You cannot say that binlog is a transaction log. Bilog not only records Innodb logs, but also exists in Myisam.
First, let's briefly mention master-slave replication: the master library (Master) has a log dump thread that passes binlog to the slave library. There are two threads from the library, an I/O thread, an SQL thread, and an I/O thread that reads the binlog content passed from the master library and writes it to the relay log. The SQL thread reads the content from the relay log and writes it to the database from the relay log.
With reset master, this command deletes all logs and restarts the log file from 000001.
Common formats for binlog:
Currently, the row mode is recommended for high accuracy. Although the file size is large, there are SSD s and gigabit optical networks available which are acceptable for both disk IO and network IO
Common parameters:
Reference text: What is binlog?_Log
2.2, Mysql Query Log--general_log
show variables like '%general_log%'; +------------------+------------------------------+ | Variable_name | Value | +------------------+------------------------------+ | general_log | OFF | | general_log_file | /var/lib/mysql/localhost.log | +------------------+------------------------------+
Using the query sql above, you can find the variable information related to the query log, and you can see that general_log is off by default.
MySQL's query log supports writing to a file or to a data table, which is controlled by the parameter log_output, which is mentioned above and is written to a file by default. If log_output=table is set, the log results are recorded in a table named gengera_log, which has CSV as the default engine. MySQL's query log records all MySQL database requests.This means that even if I query the SQL of a table that does not exist, the query log will still be recorded.
Reference resources: MySQL Query Log Summary - Ray(Mr.huang) - Blog Park
1.3, Mysql data files
On a windows system, you can view it when you find the data in the installation directory.
On Linux, the default is in the /var/lib/mysql directory
The following file types occur when the storage engine is Myisam
| frm type file | Storage table structure |
| myd file | Store table data |
| myi type file | Store table index |
The following file types occur when the storage engine is InnoDB
| frm type | Storage table structure |
| ibd | Store table data and indexes |
For mysql indexing, let's talk later
2.3, Mysql configuration file, my.ini file under win, and/etc/my.cnf file under Linux
3. Logical Architecture of Mysql
As you can see from the diagram, mysql It consists mainly of the following components: 1. Connection Pool Component 2. Manage service and tool components 3. SQL Interface Component 4. Query Parser Component 5. Optimize Components 6. Buffer Component 7. Plug-in Storage Engine 8. Physical Files
Connectors
Refers to the interaction modules with SQL in different languages.
Connection Pool
(ii) Manage caching requirements for buffered user connections, thread processing, etc. Responsible for listening for various requests to MySQL Server, receiving connection requests, forwarding all connection requests to the thread management module. Client requests for MySQL Server on each connection are assigned (or created)A connection thread serves it independently. The main work of the connection thread is responsible for the communication between MySQL Server and the client, accepting command requests from the client, passing result information from the server, etc. The thread management module is responsible for the management and maintenance of these connection threads. This includes the creation of threads, the cache of threads, etc.
Management Serveices & Utilities (Backup, Security, Replication, Clustering)
System management and control tools.
SQL Interface (stored procedures, views, triggers)
Accept the user's SQL commands and return the results the user needs to query.
Parser
SQL commands are validated and parsed by the parser when they are passed to the parser. The parser is implemented by Lex and YACC and is a very long script. In MySQL, we are accustomed to referring to all the commands sent from Client to Server as query. In MySQL Server, the connection thread receives a Query from the client and directly queries it.Pass it to Parser, which categorizes the various Queries and forwards them to the corresponding processing modules.
(a) Conduct semantic and grammatical analysis of the SQL statements, break them into data structures, then classify them according to different operation types, and then make specific forwards to the next steps, which will be the basis for the subsequent delivery and processing of the SQL statements;
b. If an error is encountered in the decomposition, then this sql statement is unreasonable.
Optimizer
Query Optimizer: The SQL statement uses the Query Optimizer to optimize the query before querying. It optimizes the query requested by the client, analyzes the query statement requested by the client, and some statistical information in the database on the basis of a series of algorithms, and obtains an optimal strategy to tell subsequent programs how to obtain the results of this query statement.
Query using the Select-Projection-Join strategy
Cache and Buffer
Query Cache: The main function is to put in memory the return result set cache of a Select query request submitted by the client to MySQL, which corresponds to a hash value of the query. After any data changes in the base table of the query's data, MySQL automatically causes the ache of the queryFailure. In very high read-write ratio applications, Query Cache can significantly improve performance. Of course, it also consumes a lot of memory.
If the query cache has hit query results, the query statement can go directly to the query cache to fetch data. This caching mechanism consists of a series of small caches, such as table caches, record caches, key caches, permission caches, etc.
Storage Engine Interface
The most important feature MySQL distinguishes from other databases is its plug-in table storage engine. The MySQL plug-in storage engine architecture provides a series of standard management and service support that are independent of the storage engine itself and may be required by each database system itself, such as the SQL analyzer and optimizer, while the storage engine is the underlying physicalThe implementation of the structure allows each storage engine developer to develop as he or she wishes. The storage engine is table based, not database based.
3.1, a complete execution process
1. Connect (Establish TCP Connection) ---> 2. Authorization Authentication: (You also need to invoke the user module for authorization checking to verify whether the user has permission before the real operation)------> 3. Receive the SQL statement------> 4. Send the statement to the SQL statement parsing module for syntax and semantics analysis------> 5.0 If it is a query statement, you can first see if there are any results in the query cache----> 6. If there are no results in the query cache, you need to really query the database engine layer and send it to the SQL optimizer------>
5.1 If table changes are made, they are handled by insert, update, delete, create, alter processing modules respectively -->...
7. The storage engine opens the table and acquires the appropriate lock if needed-->.8. Query the cache page for the corresponding data first, if there is any, it can be returned directly, if not read from disk. --->9. When the corresponding data is found on disk, it will be loaded into the cache, which makes subsequent queries more efficient. Due to limited memory, more flexible LRU s are used.Tables manage cached pages, ensuring that all cached data is frequently accessed. ----> 10. Finally, get the data back to the client, close the connection, and release the connection thread
An execution process for SQL:
4. Storage Engine for Mysql
Data in MySQL is stored in files (or memory) using a variety of different technologiesEach of these technologies uses different storage mechanisms, indexing techniques, locking levels, and ultimately offers a wide range of different functions and capabilities. By choosing a different technology, you can gain additional speed or functionality to improve the overall functionality of your application. These different technologies, along with their associated functions, areMySQL is called a storage engine, which has its own features, such as fast data processing by the memory storage engine and transaction support by the Innodb storage engine.
mysql> show engines; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
You can view all the storage engines using show engines, and support is currently supported by mysql. You can see that the default storage engine in MySQL 5.6 is Innodb.
A Preliminary Study of mysql Default Engine innodb (1)_Take One Step More-CSDN Blog
have access to
show variables like '%storage_engine%'
View the storage engine in use
Comparison of Mainstream Storage Engines Myisam and InnoDB
| Differential Items | Myisam | InnoDB |
| Primary Foreign Key | I won't support it | Support |
| affair | I won't support it | Support |
| Row table lock | Table lock, operation of a record also locks the entire table, not suitable for highly concurrent operations | Row lock, when operating, locks only one row, does not affect other rows, suitable for high concurrent operations |
| cache | Cache index only, not data | Not only does it cache the index, but it also caches the real data, which requires more memory, and memory size has a decisive impact on Performance |
| Tablespace | Small | Small |
| Focus | performance | affair |
| Default Installation | Yes | Yes |
| file structure | frm,myi,myd | frm,ibd |