Shulou(Shulou.com)05/31 Report--

What this article shares with you is about how to analyze the implementation process of MySQL. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article. Let's take a look at it.

1. Analysis of the execution process of MySQL

1.1. MySQL 5.7installation steps

1. Download the rpm package wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar# if prompted to require an account password Download # wget-- http-user=youremail@email.com-- http-passwd=yourpassword https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar2 in this way, check whether the system comes with mariadbrpm-qa | grep mariadb3, uninstall the found mariadb, rpm-e-- nodeps mariadb-libs-5.5.64-1.el7.x86_644, Extract the mysql tar you just downloaded from tar-xvf mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar5, Install the following four mysql core packages rpm-ivh mysql-community-common-5.7.28-1.el7.x86_64.rpmrpm-ivh mysql-community-libs-5.7.28-1.el7.x86_64.rpmrpm-ivh mysql-community-client-5.7.28-1.el7.x86_64.rpmrpm-ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm6 in the decompressed directory. Missing dependency error error when installing to server: Failed dependencies: libaio.so.1 () (64bit) is needed by mysql-community-server-5.7.28-1.el7.x86_64 libaio.so.1 (LIBAIO_0.1) (64bit) is needed by mysql-community-server-5.7.28-1.el7.x86_64 libaio.so.1 (LIBAIO_0.4) (64bit) is needed by mysql-community-server-5 .7.28-1.el7.x86_647, Install the missing dependency yum-y install libaio8, install serverrpm-ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm9 again, start the mysql service service mysqld start10, check the v5.7 default login password grep password / var/log/mysqld.log11, log in to the mysql command line Modify the default password ALTER USER 'root'@'localhost' IDENTIFIED BY' 123456' 12. I will tell you that the password does not conform to the specification. You can modify the check level and length and execute the above statement set global validate_password_policy=LOW;set global validate_password_length=6; 13 again. All ip authorized by the account use mysql;select host,user from user;# can access the database grant all privileges on *. * to gavin@'%' identified by '123456' # only the private network segment ip can be accessed, and the authorized account can authorize others # grant all privileges on *. * to gavin@'192.168.%' identified by '123456' with grant option;flush privileges

1.2. Basic data import for learning

Create database icoding_admin;DROP TABLE IF EXISTS `ad_ role`; CREATE TABLE `ad_ role` (`id` int (11) unsigned NOT NULL AUTO_INCREMENT, `role_ name` varchar (50) NOT NULL DEFAULT', PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `ad_ role` (`id`, `role_ name`) VALUES CREATE TABLE `ad_ user` (`id`int (11) unsigned NOT NULL AUTO_INCREMENT, `username` varchar (50) NOT NULL DEFAULT'', `password` varchar (50) NOT NULL DEFAULT'', PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `ad_ user` (`id`, `username`, `password`) VALUES (123456'), (2) gavinomery 1234567'), (3codingtitle 123456'); DROP TABLE IF EXISTS `ad_user_ role` CREATE TABLE `ad_user_ role` (`id` int (11) unsigned NOT NULL AUTO_INCREMENT, `user_ id` int (11) NOT NULL, `role_ id` int (11) NOT NULL, PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8 INSERT INTO `ad_user_ role` (`id`, `role_ id`, `role_ id`) VALUES (1meme 1meme 1), (2meme 1meme 2), (3meme 1meme 3), (4meme 2meme 2), (5pence3min3), (7mem2meme 3)

Review of basic knowledge

Where condition parsing order

MySQL: from left to right

Oralce: from right to left

SQL execution order

FROM

ON

JOIN

WHERE

GROUP BY

HAVING

SELECT

DISTINCT

ORDER BY

LIMIT

Full-text index

It is only available in MyISAM engines, and can only be used in CHAR, VARCHAR, and TEXT fields.

The process executed by SQL in MySQL-MySQL 5.7

Connector

Query cache

Parser (lexical, grammatical)

Optimizer

Actuator

Declare to use query caching, but it is not recommended to use select SQL_CACHE * from ad_user; Note: MySQL 8.0 removes the query caching module.

MySQL data engine

The data engine mysql > show engines; storage engine supported by MySQL describes the MyISAM high-speed query engine. It does not support transaction InnoDBv5.5, which will be the default engine of MySQL, Archive data compression storage engine. It is convenient for data archiving Memory memory storage engine to compare MyISAM and InnoDB.

Compared with the form of InnoDB-- storage file, .frm table definition file, .ibd locks tables, pages, and rows that store data and indexes support CRUD to read and write # query table engine ````sqlshow table status like'% ad_user%'\ G

Location of data storage in MySQL database

The cd / var/lib/mysql directory stores the data files of each database corresponding to the database.

Default path for MySQL profile

Vi / etc/my.cnf

2. The function and analysis of log types inside MySQL there are several logs commonly used in MySQL

Error log

Show variables like'% log_error%';log_error=/var/log/mysqld.loglog_warnings=2log_warnings= 0 | 1 | 20 close 1 open-default > 1 failed connection, access denied error will also be recorded

Query log

Query log will record all database operations (general log general log)

Consume Icano, which is not enabled by default

Show variables like'% general_log%';log_output=FILEFILE, TABLE, FILE,TABLE, NONE

Slow query log

Show variables like'% slow%'; [mysqld] slow_query_log=ONslow_launch_time=3slow_query_log_file=/usr/local/slow.logchown-R mysql:mysql / usr/local/select sleep (3), user from user

View the slow query log directly

Time Id Command Argument# Time: 2020-06-17T13:05:20.509651Z# User [@ Host] (https://my.oschina.net/u/116016): root [root] @ localhost [] Id: Query_time: 12.000509 Lock_time: 0.000111 Rows_sent: 3 Rows_examined: 3use icoding_admin;SET timestamp=1592399120;select sleep (4), username from ad_user # Time: 2020-06-17T13:09:14.528655Z# User [@ Host] (https://my.oschina.net/u/116016): root [root] @ localhost [] Id: Query_time: 12.000488 Lock_time: 0.000097 Rows_sent: 3 Rows_examined: 3SET timestamp=1592399354;select sleep (4), id from ad_user

You can use the slow query command provided by mysql to view the

# descending according to time mysqldumpslow-s-t / var/lib/mysql/DB213-slow.log# according to the number of records descending mysqldumpslow-s-r / var/lib/mysql/DB213-slow.log# according to the number of executions descending mysqldumpslow-s-C / var/lib/mysql/DB213-slow.log# help manual mysqldumpslow-help

Content

Reading mysql slow query log from / var/lib/mysql/DB213-slow.logCount: 1 Time=12.00s (12s) Lock=0.00s (0s) Rows=3.0 (3), root [root] [@ localhost] (https://my.oschina.net/u/570656) select sleep (N), id from ad_userCount: 1 Time=12.00s (12s) Lock=0.00s (0s) Rows=3.0 (3), root [root] [@ localhost] (https://my.oschina.net/u/570656) select sleep (N)) Username from ad_user

Binary log: binlog

This is the most important log in the database. It records all DML, not select.

Transaction log

Relay log: reply log

3. MySQL data backup, recovery and optimization

3.1. Binlog is a statement with three modes.

Pure statement to record DML

-update ad_user set username='gavin.huang' where id=1;- delete from ad_user where id=1;# statement# MySQL will automatically generate a mysql-bin-00001.log# chown-R mysql:mysql / usr/local/binlog/log_bin=/usr/local/binlog/mysql-binbinlog_format=statement# binlog log slice size max_binlog_size=500m# binlog Expiration cleanup time expire_logs_days= 3 [mysqld] server-id=213mysql > show binlog events in 'mysql-bin.000001'

Row (v5.7 version defaults to row mode)

The past historical value and the present new value

Log query mysqlbinlog of row mode-- base64-output=decode-rows-vv mysql-bin.000002

Results of the query

# DELETE FROM `icoding_ admin`.`ad _ user` # WHERE### @ 1q3 / * INT meta=0 nullable=0 is_null=0 * / # @ 2chocolate admin`s / * VARSTRING meta=150 nullable=0 is_null=0 * / # @ 3remote 123456' / * VARSTRING meta=150 nullable=0 is_null=0 * / # UPDATE `icoding_ admin`.ad _ user` # WHERE### @ 1room2 / * INT meta=0 nullable=0 is_null=0 * / # @ 2 'gavin' / * VARSTRING / meta=150 nullable=0 is_null=0 * / # # @ 3 / VARSTRING / meta=150 nullable=0 is_null=0 * / # SET### @ 1 / 2 / * INT meta=0 nullable=0 is_null=0 * / # @ 2 / VARSTRING / meta=150 nullable=0 is_null=0 * / # @ 3 / VARSTRING / meta=150 nullable=0 is_null=0 * /

How to quickly find misoperated statements

Positioning of row mode

Mysqlbinlog-- base64-output=decode-rows-- start-datetime='2020-06-17 22 start-datetime='2020 10'--stop-datetime='2020-06-17 22 start-datetime='2020 25'- vv mysql-bin.000002

Mysqlbinlog can also query the data of statement schema to get the time interval.

Assignment: set up the binlog record of statement and row mode mixed mode by yourself, and check the relevant content by yourself.

If a large number of data operations are carried out, the database is secure at this time and MySQL records are not allowed.

Mysql > set sql_log_bin=0; # temporarily close binlog

Mixed

Mixed mode statement:95%

3.2. Scenario and Analysis of data backup

Full backup

Differential backup

Incremental backup

Point-in-time backup

Backup Typ

Hot backup: hot backup cannot be done through a simple copy command

Warm standby: only read operations can be performed

Cold standby

Physical backup: copy fil

Logical backup

Common backup tools

Mysqldump

Xtrabackup provided by Percona

Mysqldump-- help#-- master-data 0 (do not record position) 1 (record position location) 2 (record position location and comment this) # routines stored procedure # triggers trigger # events event # single-transaction#-- ignore-table=icoding_admin.ad_user_role-- ignore-table=icoding_admin.ad_user# based on innodb engine mysqldump-uroot-p123456-h227.0.0.1-- master-data=2-- routines-- triggers-- events-- Single-transaction-- databases icoding_admin-- ignore-table=icoding_admin.ad_user_role > mydb.sql

Why use-- single-transaction

Scenario: Xiaoming 200 points, 12 backups, the integral table has 200w data, the database has 300 tables

The above is how to analyze the implementation process of MySQL. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.