Common operation
show databases; Show all databases use dbname; Open database: show tables; show database mysql All tables in; describe user; Display table mysql In database user Column information of table);
Connector
mysql ‐ h host [database address] - u root [user] - p root [password] - P 3306
Mysql client connection server
- Verify host, pwd
- Allocate session space (load permission and other information) (at this time, the modification permission of the server has no effect)
Modify user password
CREATE USER 'username'@'host' IDENTIFIED BY 'password'; //Create a new user
grant all privileges on *.* to 'username'@'%'; //Grant permission,% means all (host)
flush privileges //Refresh database
update user set password=password("new-pwd′′) where user='root';(Set user name and password)
show grants for root@"%"; View the permissions of the current user
View the client connection status show processlist;
You can manually kill the connection kill < ID >
If the client does not send a command to the Server for a long time, the connector will automatically disconnect it. This time is determined by the parameter wait_timeout control. The default value is 8 hours
Check how long the connection will be disconnected without operation
show global VARIABLES like 'wait_timeout';
set global wait_timeout=28800; Sets the number of seconds the global server waits for activity before closing non interactive connections
Query cache (query cache function has been removed in mysql8.0)
my.cnf parameterquery_ cache_ Type configuration cache
Generally, if you don't use it, use it on demand (query_cache_type=2)
#query_cache_type has three values # 0 = query cache OFF # 1 means ON # 2 (DEMAND) means that there is sql in the sql statement_ Cache keyword query_cache_type=2 select SQL_CACHE * from test where ID=5
Check whether the cache mechanism is enabled for the current mysql instance
show global variables like "%query_cache_type%";
Monitor the hit rate of query cache
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 block s currently cached.
use
select SQL_CACHE * from books where ID = 2
analyzer
Lexical analyzer According to the result of lexical analysis, the parser will judge the word you entered according to the grammar rules SQL Whether the statement is satisfied MySQL grammar If your statement is wrong, you will receive“ You have an error in your SQL syntax"Error alert for
The lexical analyzer is divided into six main steps to complete the analysis of sql statements
1. Lexical analysis
2. Grammar analysis
3. Semantic analysis
4. Construct execution tree
5. Generate execution plan
6. Implementation of the plan
For further research on syntax tree, please refer to this wiki article: https://en.wikipedia.org/wiki/LR_parser
select id, name, age, from test where id = 1;
optimizer
After the analyzer, MySQL will know what you want to do. Before execution, it needs to be processed by the optimizer.
The optimizer decides which index to use when there are multiple indexes in the table; Or when a statement has multiple table joins, determine the connection order of each table.
After the optimizer phase is completed, the execution scheme of this statement is determined, and then enter the executor phase
Actuator
When you start executing, you should first check if you have the right to execute the query on the table T. If not, you will return the error without permission. As shown below, if you hit the query cache on the project implementation, you will do the right limit validation when the query cache returns results. The query will also call precheck validation authority before the optimizer.
select * from test where id=10;
If you have permission, open the table to continue execution. When the table is opened, the executor will use the interface provided by the engine according to the definition of the table engine.
For example, in the test table in our example, the ID field has no index, so the execution process of the actuator is as follows:
- Call the InnoDB engine interface to take the first row of the table and judge whether the ID value is 10. If not, skip. If yes, save this row in the result set;
- Call the engine interface to take the "next row" and repeat the same judgment logic until the last row of the table is taken.
- The executor returns the record set composed of all qualified rows in the above traversal process to the client as the result set.
Bin log archive
Binlog is the binary log implemented by the Server layer. It will record our cud operations. Binlog has the following features:
- Binlog is implemented in the Server layer of MySQL (engine sharing)
- Binlog is a logic log that records the original logic of a statement
- Binlog is unlimited in size and can be written additionally. It will not overwrite the previous logs
If we delete the database by mistake, we can use binlog for archiving! To use binlog archiving, we must first record binlog, so we need to turn on the binlog function of MySQL first.
Configure my cnf
Configuration on binlog log‐bin=/usr/local/mysql/data/binlog/mysql‐bin Note 5.7 And later versions need to configure this item:server‐id=123454(custom,Guarantee uniqueness); #binlog format: there are three kinds of statement, row and mixed binlog‐format=ROW #It means that every write is synchronized with the hard disk, which will affect the performance. When it is 0, it means that mysql does not brush the disk when the transaction is committed, which is determined by the system. 7 sync ‐ binlog=1
binlog command
show variables like '%log_bin%'; see bin‐log Open or not flush logs; There will be one more up-to-date bin‐log journal show master status; View last bin‐log Log related information reset master; Empty all bin‐log journal
View binlog content
/usr/local/mysql/bin/mysqlbinlog ‐‐no‐defaults /usr/local/mysql/data/binlog/mysql ‐bin. 000001 see binlog content
The content in binlog is not readable, so we need to judge the recovered logical point by ourselves. How to observe it?
Look at key information, such as keyword information such as begin and commit. As long as you see it in binlog, you can understand that the information between begin commit is a complete transaction logic, and then judge the recovery according to the location position.
Recover data from bin ‐ log
Recover all data /usr/local/mysql/bin/mysqlbinlog ‐‐no‐defaults /usr/local/mysql/data/binlog/mysql‐bin.000001 |mysql ‐uroot ‐p tuling(Database name) Recover data at the specified location /usr/local/mysql/bin/mysqlbinlog ‐‐no‐defaults ‐‐start‐position="408" ‐‐stop‐position="731" /usr/local/mysql/data/binlog/mysql‐bin.000001 |mysql ‐uroot ‐p tuling(database) Recover data in specified time period /usr/local/mysql/bin/mysqlbinlog ‐‐no‐defaults /usr/local/mysql/data/binlog/mysql‐bin.000001 ‐‐stop‐date= "2018‐03‐02 12:00:00" ‐‐start‐date= "2019‐03‐02 11:55:00"|mysql ‐uroot ‐p test(database)
Define a stored procedure to write data
drop procedure if exists tproc;
delimiter $$
create procedure tproc(i int)
begin
declare s int default 1;
declare c char(50) default repeat('a',50);
while s<=i do
start transaction;
insert into test values(null,c);
commit;
set s=s+1;
end while;
end$$
delimiter ;
Delete data truncate test;
Archive with binlog / usr/local/mysql/bin/mysqlbinlog ‐ no ‐ defaults /usr/local/mysql/data/binlog/mysql ‐ bin 00000 1 | MySQL ‐ uroot ‐ P Turing (database name)