Catalog

1. Introduction and installation

2. Basic management

2.1 User management

2.2 Rights management

2.3 Connection management

2.4 Configuration Management

3.MySQL Architecture of

4.SQL

5. Index and execution plan

1. Introduction and installation

1.1 Database classification

RDBMS( Relational database ):Relational Database Management System

On behalf of the product :Oracle  MySQL  MSSQL PG

NoSQL( Non relational database ):Not Only SQL

Representative works :MongoDB  Redis  ES

NewSQL( For all kinds of new expandable / Short for high performance database )

Representative works :spanner  PolarDB(X)  TDSQL  TiDB  gaussian

1.2 MySQL Branch

1.Oracle

2.Percona

3.MariaDB

4. Cloud manufacturers

1.3 MySQL obtain

www.mysql.com

1.4 MySQL install

# 1. decompression 
[root@localhost opt]# tar xf mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz # 2. Soft connection
ln -s /opt/mysql-8.0.20-linux-glibc2.12-x86_64 /usr/local/mysql # 3. Modify environment variables
vim /etc/profile
Add the following
export PATH=/usr/local/mysql/bin:$PATH source /etc/profile # 4. Create user Catalog The configuration file
[root@localhost mysql]# useradd mysql
[root@localhost mysql]# mkdir -p /data/3306/data
[root@localhost mysql]# chown -R mysql.mysql /data
[root@localhost mysql]# vim /etc/my.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
[mysql]
socket=/tmp/mysql.sock # 5. Initialization data
If there is no profile :
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data If there is a configuration file :
[root@localhost data]# mysqld --initialize-insecure # 6. Ready to start script
[root@localhost ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@localhost ~]# /etc/init.d/mysqld stop
Shutting down MySQL... SUCCESS!
[root@localhost ~]# /etc/init.d/mysqld start
Starting MySQL.. SUCCESS!
[root@localhost ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.. SUCCESS!
[root@localhost ~]# /etc/init.d/mysqld status
SUCCESS! MySQL running (12134)

2. Basic management

2.1 User management

2.1.1 effect

1. Used to log in MySQL

2. Used to manage MySQL object [ps: What is? MySQL object ??]

2.1.2 Definition

grammar : user name @' White list '

What is a white list ??  answer :IP Address range

Common form :

root@'%'
root@'localhost'
root@'10.0.0.%'
root@'10.0.0.2'

2.1.3 Manage users

1. Query the user

-- Query the user 
mysql> select user,host ,authentication_string ,plugin from mysql.user;
+------------------+-----------+------------------------------------------------------------------------+-----------------------+
| user | host | authentication_string | plugin |
+------------------+-----------+------------------------------------------------------------------------+-----------------------+
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| mysql.session | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| mysql.sys | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| root | localhost | | caching_sha2_password |
+------------------+-----------+------------------------------------------------------------------------+-----------------------+

Be careful : plugin, Encryption plug-ins , stay 8.0 And then we did an upgrade ,caching_sha2_password, The safety has increased .

Many old client programs will not be able to connect to MySQL. The earlier version was mysql_native_password.

2. Create user / Modify the user / Delete user

-- Create user 
mysql> create user root@'10.0.0.%' identified by '123';
mysql> create user user1@'10.0.0.%' identified with mysql_native_password by '123'; -- Modify the user
mysql> alter user root@'10.0.0.%' identified with mysql_native_password by '123';
mysql> alter user user1@'10.0.0.%' account lock;
mysql> alter user user1@'10.0.0.%' account unlock; -- Delete user
mysql> drop user user1@'10.0.0.%';

Be careful : 8.0 after , Only users can be created before authorization .

2.2 Rights management

2.2.1 Permission list

mysql> show privileges;

The most common permissions :

1.ALL ? It doesn't contain grant option

2.select ,insert ,update ,delete

2.2.2 Authorization and recall rights

-- to grant authorization 
mysql> grant all on *.* to root@'10.0.0.%'; -- Query authority
mysql> show grants for root@'10.0.0.%'; -- Recycling permissions
mysql> revoke drop on *.* from root@'10.0.0.%' ;

About the scope of authority :

*.* : All tables under all libraries

luffy.*: All tables in a single library

luffy.user: Single table

2.3 Connection management

2.3.1 socket The file

-- Premise : It needs to be created in advance localhost White list users 
[root@localhost ~]# mysql -uroot -p -S /tmp/mysql.sock

2.3.2 TCP/IP The way

-- Premise You need to log in to the client IP Join the white list 
[root@localhost ~]# mysql -uroot -p123 -h 10.0.0.111 -P3306

2.4 Configuration Management

2.4.1 Offline configuration

-- Configuration file application order 
[root@localhost ~]# mysqld --help --verbose |grep my.cnf
/etc/my.cnf ---> /etc/mysql/my.cnf ---> /usr/local/mysql/etc/my.cnf --> ~/.my.cnf
-- Configuration file structure 
[root@localhost ~]# cat /etc/my.cnf

[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock [mysql]
socket=/tmp/mysql.sock

Be careful : Modify the configuration file , Restart the database .

2.4.2 Online configuration

-- Modify through special configuration command .
mysql> set global innodb_buffer_pool_size=16777216;
-- View all parameters that can be configured online 
show variables
show variables like '%date%';

3.MySQL Architecture of

Not much to say , Directly above .

1. adjoining course

There are three functions of connecting layer :

1. Provide connection protocol : Socket file ,TCP/IP

2. Verify user identity / Authorization Form .

3. The connection layer provides an interface with sql Layer interaction threads

2.SQL layer

SQL There are six layers :

1. Receive from the connection layer sql sentence

2. verification sql Sentence syntax

3. verification sql The semantics of the statement (DDL,DQL,DML,DCL)

4. Parser : analysis sql sentence , Generate execution plan

5. Optimizer : Choose the best one from the execution plan

Optimize ???? Optimize ( Logic optimization , Physical optimization ( Indexes )

6. actuator : Execute the selected SQL plan

1. Threads that interact with the storage engine layer

2. What will be done sql Statement to the storage engine layer

3.Engine layer

effect : Responsible for interacting with disk

4.SQL

4.1 What is? SQL?

Relational databases are universal Language . Structured query language .[ps: What is a structured query language ?]

4.2 SQL_MODE

Commonly used SQL_MODE Parameters :[ps:SQL_MODE Detailed explanation ]

mysql> select @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+

a key :  ONLY_FULL_GROUP_BY ,5.7 Version new features .

4.3 data type

Particular attention :

char(10) Fixed length

varchar(10) Lengthening

Other detailed data types :[ps:MySQL Data types in ]

4.4 Character set

utf8 Save up to three bytes of characters

utf8mb4 Save up to four bytes of characters [emoji expression ]

4.5 SQL species

DDL : Data definition language

DML : Data operation language

What is? DDL( Data definition language ?), What is? DML( Data operation language ?):[ps:DDL and DML The definition of ]

4.6 DDL Application specification of

4.6.1 To Library (Database) The operation of

library :
create database

drop database

alter database

standard :
a. The production system is disabled drop operation

b. Do not use system reserved characters for library names , Don't use capital letters , Don't start with a number .

c. To build a library is to explicitly set the character set .

4.6.2 Counter table (Table) The operation of

create table

standard :

Do not use system reserved characters for table names , Don't use capital letters , Don't start with a number .ob_user; Not more than 18 character .

data type :

Appropriate Brief Adequate

Be careful :

Each table should have a primary key .

Each column should be as non empty as possible , Or set the default value

Each column should be annotated .

Storage engine use InnoDB Character set utf8mb4

alter table

Main uses :

Add columns

Delete column

Suo Yin

Delete index

This type of

About alter table One more thing to know :

8.0 before :Online DDL It needs to be done during the business period . Or use PT-OSC.

8.0 after : Adding columns can be done directly .

drop table It's not necessary to use .

4.7 About DDL/DML and MySQL Problems in the implementation phase

prepare  MDL X Block all DML write in DDL

exec      S Demote shared lock Don't block DML , Blocking DDL

commit  MDL X Block all DML write in DDL

5. Index and execution plan

5.1 What is index ?

It's like a catalog in a book . Optimized query (select update  delete)

5.2 Index type

BTREE *****

RTREE( Spatial data index )

HASH( Hash index )

FTEXT( Full-text index )

5.3 BTREE Structural understanding

b-tree
b+tree(b*tree) --> Enhanced Edition

5.4 MySQL How to apply BTREE?

5.4.1 Cluster index

1. What is a clustered index ?

InnoDB in , Table data file itself is by pressing B+Tree An index structure of an organization , Clustering index is based on The primary key of each table constructs one B+ Trees , At the same time, the leaf node stores the whole Table row records data , The leaf node of the clustered index is also called Data pages . This feature determines the index organization. The data in the table is also part of the index ;

2. The construction condition of clustering index ?

Primary Key / Unique+not null / RowID

3. The construction details of clustering index ?

Leaf node (Leaf): When you enter data , It will follow the logical order of clustering index , Stored in multiple data pages that are physically continuous . So we have leaf nodes . And store two-way pointers of adjacent leaf nodes .
Branch nodes (No-Leaf): Select the leaf node ID The scope of the + The pointer .
The root node (ROOT) : No-leaf Node ID Range + The pointer

4. The optimization effect of clustering index ?

adopt ID When columns are used as query criteria , It will optimize .

5.4.2 Secondary index

...

day128:MySQL Advanced : More articles about

  1. mysql Advanced ( Twenty-nine ) Common functions

    mysql Advanced ( Twenty-nine ) Common functions One . Mathematical functions ABS(x) return x The absolute value of BIN(x) return x Binary system (OCT Returns octal ,HEX Return hexadecimal ) CEILING(x) Return is greater than the x The minimum integer value of EXP ...

  2. mysql Advanced ( Twenty-eight )MySQL GRANT REVOKE usage

    mysql Advanced ( Twenty-eight )MySQL GRANT REVOKE usage MySQL There are two concepts surrounding the authority system of : authentication -> Determine whether the user is allowed to connect to the database server : to grant authorization -> Determine if the user has sufficient permissions to execute ...

  3. mysql Advanced ( twenty-seven ) Database indexing principle

    mysql Advanced ( twenty-seven ) Database indexing principle Preface This article is mainly about MySQL Indexing mechanism , It mainly describes the storage engine Innodb. The first part mainly discusses the data structure and algorithm theory MySQL Mathematical basis of database index . ...

  4. mysql Advanced ( hexacosa- )MySQL Index type ( Beginners must see )

    mysql Advanced ( hexacosa- )MySQL Index type ( Beginners must see ) Indexes are the key to quick searches .MySQL The establishment of index for MySQL It is very important to run efficiently . Here are a few common ones MySQL Index type . In number ...

  5. mysql Advanced ( sixteen ) FAQ summary

    mysql Advanced ( sixteen ) FAQ summary MySQL View learning : http://www.itokit.com/2011/0908/67848.html When performing a delete operation , The following error appears : The reason for the above problems is ...

  6. 【 turn 】MySQL— Advanced

    [ turn ]MySQL— Advanced Catalog One . View Two . trigger 3、 ... and . function Four . stored procedure 5、 ... and . Business One . View The view is a virtual table ( It's not real ), Its essence is [ according to SQL Statement to get a dynamic data set , And name it ], Users only need to ...

  7. MySQL Advanced ( View )---py The whole stack

    Catalog mysql Advanced ( View )---py The whole stack One . What is a view ? Two . Features of the view 3、 ... and . Benefits of views Four . Use occasion 5、 ... and . View basic operations 6、 ... and . Case study mysql Advanced ( View )---py The whole stack One . What is a view ? The view is from ...

  8. mysql Advanced practice

    One .  MySQL Advanced practice /*========================== Create class table =============================*/ CREATE TABLE class ( ...

  9. MySQL Advanced 19-- Function creation ( give an example )/ Set up mysql Permission to create a function for / see (show)/ Delete (drop) / for 4 A chestnut

    /*MySQL Advanced 19 function Stored procedures and functions : Is similar to java The method in ; Common benefits of stored procedures and functions : 1. Improve code reuse 2. Simplified operation benefits : Reduce the number of operations , Reduce the number of compilations , Reduces the number of connections to the server , ...

  10. MySQL Advanced 13-- Six common constraints : Non empty / Default / Primary key / Unique constraint / Check constraint / Foreign key constraints -- Table level constraints / Column level constraints

    /* MySQL Advanced 13 Six common constraints : 1.not null Non empty 2.default : The default value is , Used to guarantee the default value of this field ; Such as age :1900-10-10 3.primary key : Primary key , use ...

Random recommendation

  1. Collect some useful docker Mirror image

    https://hub.docker.com/explore/  yes star The top image =================================================== ...

  2. framebuff Display subsystem

     #include <unistd.h> #include <stdio.h> #include <fcntl.h> #include <linux/fb.h ...

  3. c# aggregate ArrayList; Special set Stack、Queue

    One )  ArrayList 1.foreach Traversing the elements of an array , Execute internal statement 2.  3. 4.  myarry.Clear();// Empty the collection bool b = myarry.Contains(3 ...

  4. Study pthreads, Passing multiple parameters to a thread

    In the last post .boss The thread only passes one parameter to other threads , So if it's multiple parameters ? How to deliver ? Maybe you have this question , With this question , We enter the world of this article , Multiple parameters are passed here , Using structures , Why? ? Because the structure can ...

  5. Mac A very fashionable high-definition dynamic wallpaper on the computer Living Wallpaper HD

    A lot of friends Mac I like to use dynamic wallpaper on my computer ,Living Wallpaper HD I try a very good high-definition dynamic wallpaper . There's a clock . The weather and so on , It's very fashionable and beautiful . Living Wallpaper HD Download to ...

  6. pythonのdjango Connect MYSQL

    stay py3.* In the use of django Use mysql when , There will be some problems . because django The default is  MySQLdb, but MySQLdb Currently not supported py3.* So we're going to use pymysql, First install pymysql, life ...

  7. 【css】 Wrap the text correctly

    We usually wrap text with word-break attribute , There are two values to choose from word-break: break-all; word-break: break-world; So how to distinguish these two values , Let's look at the picture and talk ...

  8. linux/centos6.5 Download and install python2.7 and python3.5.2

    centos6.5 Installation in environment python2.7 decompression python Source package tar -xf Python-2.7.6.tar.xz Go to the package directory cd Python-2.7.6 Add configuration item ./conf ...

  9. 【Android automation 】 In the use of uiautomator When the framework is automated , Sometimes when you run a script, you find xxx Instance properties are not allowed

    for example : # -*- coding:utf-8 -*- from uiautomator import device as d d(classname="android.widget.List ...

  10. linux Next maven project clean Failure

    In today's linux Create a script for project automatic publishing under , In carrying out the  mvn clean package -Dmaven.test.skip=true When , project clean Failure Check Xiadu Niang ,windows Down lead ...