Connecting and disconnecting
Verify authority, user name and password
Number of connections: show processlist; kill show global variables like ‘wait _ timeout’;
Default sleep time: 8 hours
After the connection, the user information is cached, and the permissions of the database are changed. The connection takes effect only after re login
Create user: create user ‘XTC’ @ ‘localhost’ identified by ‘123456’;
Change user permissions: grant all privileges on ** to ‘xtc’@’localhost’;
Refresh permissions: flush privileges;
Change Password: alter user ‘XTC’ @ ‘%’ identified by ‘654321’;
Change access permissions: update user set host = ‘%’ where user =’xtc ‘;
Query table structure: describe user;
View cache status: show global variables like “query_cache_type”;
Enable cache: / etc / MySQL / my CNF # add query_ cache_ Type = demand (can be hit only if sql_cache is added to the query) off off on by default # on # restart after change
View the number of hits and the amount of storage. Show status like “% qcache%”
- Qcache_ free_ Blocks: indicates how many blocks remain in the query cache. If the value is large, it indicates that there are too many memory fragments in the query cache, which may be sorted out in a certain time.
- Qcache_ free_ Memory: the memory size of the query cache. Through this parameter, you can clearly know whether the query memory of the current system is enough, whether it is too much or not. The DBA can adjust it according to the actual situation.
- Qcache_ Hits: indicates the number of hits to the cache. We can mainly use this value to verify the effect of our query cache. The larger the number, the better the cache effect.
- Qcache_ Inserts: indicates the number of misses and then inserts, which means that the new SQL request is not found in the cache and has to execute query processing. After executing query processing, insert the result into the query cache. The more times this happens, the less the query cache is applied, and the effect is not ideal. Of course, after the system is started, the query cache is empty, which is normal.
- Qcache_ lowmem_ Prunes: this parameter records how many queries are removed from the query cache due to insufficient memory. With this value, the user can adjust the cache size appropriately.
- Qcache_ not_ Cached: indicates because of query_ cache_ The number of queries that are not cached due to the setting of type.
- Qcache_ queries_ in_ Cache: the number of queries cached in the current cache.
- Qcache_ total_ Blocks: number of blocks currently cached.
Lexical analyzer: parse SQL statements into a syntax tree,
Optimizer: selecting indexes
Executor: call engine interface
Server layer engine common
Unlimited size, additional write, and the previous file will not be overwritten
Check whether it is enabled: show global variables like “% log_bin%”;
Open: / etc / MySQL / my CNF # add:
Log bin = / user / local / MySQL / data / binlog / MySQL bin. You need to give permission to this directory
Server id = 1. It needs to be added after 5.7. It is a unique ID, which is defined by itself
Binlog format = row #binlog format. There are three statements that only record the execution, but the efficiency is low. There may be inconsistency between the master and the slave. Row records the results of SQL execution, which is inefficient and safe. Mixed is a combination of the two
Sync binlog = 1 # means to synchronize with the hard disk every time you write, which will affect the performance. When it is 0, it means that MySQL will not brush the disk when the transaction is committed, which is determined by the system
The MySQL bin 00000 1 file will be generated in the specified directory
View binlog file:
/usr/bin/mysqlbinlog –no-defaults /usr/local/mysql/data/binlog/mysql-bin.000001