Introduction to MySQL user management and engine

database Metabase introduction

  What is metabase
         Record mysql Database of own data
     What meta databases are there
        information_schema
             Information database , preservation mysql Other database information maintained , for example ： Database name , Table of database , Table column data type and access right Limit
        mysql
             Core database , The user who is mainly responsible for storing the database 、 permissions 、 Keywords, etc mysql Control and management information that you need to use
        performance_schema
             be used for mysql Storage of monitoring data
Switch database  
    use   Database name
     Example ： use  mysql

MySql User management  

Create an account
     Command format

 create user  user name  identified by ‘ password ’

    explain
        identified by The plain text password is encrypted and stored as a hash value
     Example ：

 CREATE USER test IDENTIFIED BY '123456';

View account
     Command format ：

  select host,user,password from user;

    host Column description ：
        % 
               Match all hosts
        localhost      
            localhost It won't be resolved into IP Address , Directly through UNIXsocket Connect
                 Communication with the same host , No network protocol stack , Don't pack or unpack , Calculate the check sum 、 Maintain serial number response, etc . Just copy application layer data from one process to another
        127.0.0.1
             Will pass TCP/IP Protocol connection , And can only be accessed locally
        ::1
            ::1 It's compatibility support ipv6 Of , It means the same as ipv4 Of 127.0.0.1

Delete account

     Command format
        drop user user name
     Example ：
        DROP USER test;
     Generally do not use , Be careful when using

Change Password
     Command format
        set password for user name =password(' New password ')
     Example ：

 SET PASSWORD FOR test=PASSWORD('123456');

Refresh configuration
     Command format
        flush privileges

You have to use flush privileges In two cases

1、 Change password .

2、 Authorized superuser .

flush privileges The essence of the command is to put the current user and privilige User information in the table / Permissions are set from mysql library (MySQL The built-in Library of the database ) In memory .

MySQL User data and permissions have been modified , Hope that in " No restart MySQL service " Directly in the case of , Then you need to execute this command .

It's usually modifying ROOT After setting the account number , I'm afraid I can't log in again after restart , So directly flush After that, you can see whether the permission settings take effect . Without taking too much risk .

Set the permissions
     Command format
  

     grant privileges on databasename.tablename to [email protected]'host'

    privileges
              Appoint select,update Such as permissions , Use all permissions all
    databasename
                 Specify database , Please use... For all databases *
     tablename
                 Specify data sheets , Please use *
     username
                 User name that needs authorization ,@ Yes Host, Indicates that the weighting operation is for those links , View details 【host Column description 】
     Examples of use

   GRANT SELECT, UPDATE ON bookshop.`t_book` TO [email protected]'%';

  take bookshop In the database t_book Tabular select,update Confer authority on test user , And it's not ip Address Limit

  grant all on *.* to [email protected]'localhost'

It is generally used to give the administrator the highest permission , Use caution

 grant create view on testdb.* to [email protected]'192.168.0.%'; 

  With 192.168.0 The initial account name deveoper The user is targeting testdb The database grants permission to create views 、

  grant show   view on testdb.* to [email protected]'192.168.0.%'

        With 192.168.0 The initial account name deveoper The user is targeting testdb The database grants the right to view

 grant index on testdb.* to [email protected]'192.168.0.%';

          With 192.168.0 The initial account name deveoper The user is targeting testdb The database grants permission to create indexes
      

  grant create routine on testdb.* to [email protected]'192.168.0.%'
  grant alter  routine on testdb.* to [email protected]'192.168.0.%';

             With 192.168.0 The initial account name deveoper The user is targeting testdb The database gives permission to operate stored procedures and functions
             With 192.168.0 The initial account name deveoper The user is targeting testdb The database gives the ability to create and delete stored procedures and functions jurisdiction
     Authorized users can give their rights to other users
         Command format
            grant privileges on databasename.tablename to [email protected]'host' with grant option
         Example
            grant select on testdb.* to [email protected] with grant option
         explain ：
             Generally do not use , It is recommended to contact the database administrator （DBA） Unified management
View permissions
    show grants
         View the current user （ own ） jurisdiction
    show grants for [email protected];
         See more MySQL User permissions
Revoke authority
     Command format
        revoke privileges on databasename.tablename from [email protected]'host'
     Example
        REVOKE UPDATE ON bookshop.t_book FROM [email protected]'%';
             Take back test User for bookshop In the library t_book Tabular update jurisdiction （ip There is no limit ）

MySQL Engine character set basic commands

（1）MySQL Query database character set     

 show VARIABLES like '%character%';

（2）Mysql The configuration file ：

windows System ：my.nin;
Linux System ：my.cnf;

（3） Query the database storage engine ：

show engines;
show variables like '%storage_engine%'

  Two 、MySQL Engine introduction
MySQL The data in is stored in files using various techniques ( Or memory ) in . Each of these technologies uses a different storage mechanism 、 Indexing techniques 、 Lock levels and ultimately provide a wide range of different functions and capabilities . By choosing different technologies , You can get extra speed or functionality , To improve the overall functionality of your app .

for example , If you're looking at a lot of temporary data , You may need to use memory MySQL Storage engine . Memory storage engine can store all table data in memory . Or, , You may need a database that supports transaction processing ( To ensure the ability of data fallback in case of unsuccessful transaction ).

These different technologies and related functions are in MySQL It's called the storage engine ( Also called table type ). MySQL Many different storage engines are configured by default , It can be preset or in MySQL Enable... In the server . You can choose to apply to the server 、 Database and table storage engine , In order to choose how to store your information 、 How to retrieve this information and what features and functions you need from your data give you maximum flexibility .

（1）MyISAM Storage engine

Unsupported transaction 、 Foreign keys are also not supported , The advantage is fast access , There is no... For transaction integrity Ask for or with select,insert The main application can basically use this engine to create tables

Support 3 Different storage formats , Namely ： Static table ; Dynamic table ; Compression meter

Static table ： The fields in the table are all non variable length fields , So every record is a fixed length , Advantages storage is very fast , Easy to cache , It's easy to recover in case of failure ; The disadvantage is that it usually takes up more space than a dynamic table （ Because the space will be filled according to the width of the column definition when storing ）ps： When I get the data , By default, the space after the field is removed , If you don't pay attention, you will ignore the spaces in the data itself .

Dynamic table ： Records are not fixed length , The advantage of this storage is that it takes up less space ; shortcoming ： Frequent updates 、 Deleting data is prone to fragmentation , It needs to be carried out on a regular basis OPTIMIZE TABLE perhaps myisamchk-r Command to improve performance

Compression meter ： Because each record is compressed individually , So there's only a very small cost of access

（2）InnoDB Storage engine

The storage engine provides 、 Transaction security of rollback and crash recovery capability . But contrast MyISAM engine , The efficiency of writing will be lower , And it will take up more disk space to keep data and index .
InnoDB Features of the storage engine ： Support auto grow Columns , Support for foreign key constraints

（3）MEMORY Storage engine

Memory The storage engine uses what is in memory to create tables . Every memory Table only corresponds to one disk file , The format is .frm.memory Table access of type is very fast , Because its data is in memory , And by default HASH Indexes , But once the service is shut down , The data in the table will be lost .
MEMORY The table of the storage engine can choose to use BTREE Index or HASH Indexes , Two different types of indexes have different scopes of use

Hash Indexing benefits ：
Hash The particularity of index structure , Its retrieval efficiency is very high , The index can be retrieved at one time , Unlike B-Tree The index needs to be from the root node to the branch node , Finally, you can access the page node for many times IO visit , therefore Hash The query efficiency of index is much higher than B-Tree Indexes .
Hash Indexing disadvantages ： What about imprecise search , Is also evident , because hash The algorithm is based on equivalent calculation , So for “like” Equal range search hash Invalid index , I won't support it ;

Memory Type of storage engine is mainly used for code tables whose content changes infrequently , Or as an intermediate result table for statistical operations , It is convenient to analyze the intermediate results efficiently and get the final statistical results ,. For the storage engine memory Be careful when updating tables , Because the data is not actually written to disk , So we must consider how to get the modified data after the next service restart .

（4）MERGE Storage engine

Merge The storage engine is a set of MyISAM Combination of tables , these MyISAM Tables must be exactly the same structure ,merge The table itself has no data , Yes merge Types of tables can be queried , to update , Delete operation , These operations are actually internal MyISAM Table .

（5）MyISAM And InnoDB Engine comparison

Contrast item     MyISAM    InnODB
Main foreign key      I won't support it      Support
Business      I won't support it      Support
Row table lock      Table locks , Even operating on one record locks the entire table , Not suitable for highly concurrent operations      Row lock , When operating, only lock the line of operation , It doesn't affect anything else , Suitable for high concurrency operations .
cache      Cache index only , Other real data will not be cached .     Not only cache indexes, but also cache real data , High memory requirements , And memory size has a decisive impact on performance .
Table space      Small      Big
concerns      performance      Business

（6）ARCHIVE Storage engine

    Only inserts and queries are allowed , Modification and deletion are not allowed , Compressed storage , Save space , High concurrency insertion can be achieved , Support self increment id Index on
     Use scenarios ： It can be used in logging and data collection
     characteristic
        Archive Table ratio MyISAM The watch should be about 75%, It's better than those that support transaction processing InnoDB The watch is about 83%
         Index not supported （ Self increasing id Except for columns ）
         Support insert,select operation , But does not support delete,update operation

（7） How to choose the right storage engine ？

（1） The selection criteria can be divided into ：
（2） Need to support transactions ;
（3） Whether hot standby is needed ;
（4） Crash recovery ： Can you accept collapse ;
（5） Need foreign key support ;

（6） Do you need more love Like logs ;

  
And then according to the standard , Select the corresponding storage engine .