Data query language (DQL): its statements, also known as "data retrieval statements", are used to obtain data from tables and determine how data is given in applications. The reserved word SELECT is the verb most frequently used by DQL (and all SQL). Other reserved words commonly used by DQL include WHERE, ORDER BY, GROUP BY and HAVING. These DQL reserved words are often used with other types of SQL statements.
show status
Using the SHOW STATUS statement, you can obtain some status information of the MySQL server, which is mainly the performance parameters of the MySQL database. The syntax format of the SHOW STATUS statement is as follows:
SHOW [SESSION | GLOBAL] STATUS LIKE 'status_name';
Where, SESSION means to obtain the performance parameters at the current SESSION level, GLOBAL means to obtain the performance parameters at the GLOBAL level, and SESSION and GLOBAL can be omitted. If omitted, the default is SESSION. status_name indicates the parameter value of the query. Mastering the use of these parameters can better understand the execution frequency of SQL statements. Parameter values supported by the SHOW STATUS statement
Not_flushed_key_blocks A key block that has changed in the key cache but has not been emptied to disk. Not_flushed_delayed_rows mysql> show status; +-----------------------------------------------+--------------------------------------------------+ | Variable_name | Value | +-----------------------------------------------+--------------------------------------------------+ | Aborted_clients | 1 The number of connections that have been abandoned because the connection has died because the customer has not closed it correctly| | Aborted_connects | 0 The attempt has failed MySQL Number of connections to the server | | Binlog_cache_disk_use | 0 This status indicates how many transactions use temporary files | | Binlog_cache_use | 1 How many things are used ache_size To cache uncommitted transaction logs | | Binlog_stmt_cache_disk_use | 0 | | Binlog_stmt_cache_use | 0 | | Bytes_received | 248 The number of bytes that have been received from the client | | Bytes_sent | 185 Number of bytes sent to all customers | | Com_statement | 0 The number of times corresponding to each variable statement | | Connections | 41472 Trying to connect MySQL Number of servers | | Created_tmp_disk_tables | 0 The number of temporary tables automatically created on the hard disk when the server executes statements | | Created_tmp_files | 5 mysqld Number of temporary files created | | Created_tmp_tables | 0 The number of implicit temporary tables that have been created when the statement is executed | | Delayed_errors | 0 use INSERT DELAYED Some errors occurred while writing to(Possible duplicate key values)Number of rows | | Delayed_insert_threads | 0 The number of deferred insert processor threads in use | | Delayed_writes | 0 use INSERT DELAYED Number of lines written | | Flush_commands | 17 implement FLUSH Number of commands | | Handler_commit | 0 inside COMMIT Number of commands | | Handler_delete | 0 Number of requests to delete rows from a table | | Handler_discover | 0 MySQL The server can ask: NDB CLUSTER Does the storage engine know a table with a certain name | | Handler_external_lock | 0 | | Handler_mrr_init | 0 | | Handler_prepare | 0 Counters in the preparation stage of the two-stage commit operation | | Handler_read_first | 0 The number of times the first row in the table is requested to be read | | Handler_read_key | 0 Request number (key based read line) | | Handler_read_last | 0 | | Handler_read_next | 0 The number of times a row based on a key is requested to be read | | Handler_read_prev | 0 The number of requests to read the data in the previous row in the order of index | | Handler_read_rnd | 0 The number of times a row based on a fixed position is requested to be read | | Handler_read_rnd_next | 0 The number of requests to read the next line of data in the data file | | Handler_rollback | 0 inside ROLLBACK Number of commands | | Handler_savepoint | 0 The number of requests to place a savepoint in a storage engine | | Handler_savepoint_rollback | 0 The number of savepoints that a storage engine requires to be rolled back to | | Handler_update | 0 The number of times a row in the table is requested to be updated | | Handler_write | 0 The number of times a row is requested to be inserted into the table | | Innodb_buffer_pool_dump_status | Dumping of buffer pool not started | | Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 220224 21:04:35 | | Innodb_buffer_pool_resize_status | | | Innodb_buffer_pool_pages_data | 32422 | | Innodb_buffer_pool_bytes_data | 531202048 | | Innodb_buffer_pool_pages_dirty | 0 | | Innodb_buffer_pool_bytes_dirty | 0 | | Innodb_buffer_pool_pages_flushed | 235 | | Innodb_buffer_pool_pages_free | 97929 | | Innodb_buffer_pool_pages_misc | 705 | | Innodb_buffer_pool_pages_total | 131056 | | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 23377 | | Innodb_buffer_pool_read_ahead_evicted | 0 | | Innodb_buffer_pool_read_requests | 330362716 InnoDB Number of logical read requests completed | | Innodb_buffer_pool_reads | 9004 | | Innodb_buffer_pool_wait_free | 0 | | Innodb_buffer_pool_write_requests | 3783 | | Innodb_data_fsyncs | 47 | | Innodb_data_pending_fsyncs | 0 | | Innodb_data_pending_reads | 0 | | Innodb_data_pending_writes | 0 | | Innodb_data_read | 530813440 | | Innodb_data_reads | 33379 | | Innodb_data_writes | 279 | | Innodb_data_written | 4193792 Amount of data written so far (bytes) | | Innodb_dblwr_pages_written | 20 | | Innodb_dblwr_writes | 10 | | Innodb_log_waits | 0 The time we have to wait | | Innodb_log_write_requests | 9 Number of log write requests | | Innodb_log_writes | 15 Number of physical writes to the log file | | Innodb_os_log_fsyncs | 22 Completed to log file fsync()Write quantity | | Innodb_os_log_pending_fsyncs | 0 Pending log files fsync()Number of operations | | Innodb_os_log_pending_writes | 0 Pending log file write operations | | Innodb_os_log_written | 12288 Number of bytes written to the log file | | Innodb_page_size | 16384 Compiled InnoDB Page size:(Default 16 KB) | | Innodb_pages_created | 42 Number of pages created | | Innodb_pages_read | 32380 Number of pages read | | Innodb_pages_written | 235 Number of pages written | | Innodb_row_lock_current_waits | 0 Number of currently waiting rows to be locked | | Innodb_row_lock_time | 0 Total time spent locking rows, in milliseconds | | Innodb_row_lock_time_avg | 0 Average time of row locking, in milliseconds | | Innodb_row_lock_time_max | 0 Maximum time of row locking, in milliseconds | | Innodb_row_lock_waits | 0 The number of times a row lock must wait | | Innodb_rows_deleted | 6 from InnoDB Number of rows deleted from the table | | Innodb_rows_inserted | 2193 Insert into InnoDB Number of rows in the table | | Innodb_rows_read | 351814064 from InnoDB Number of rows read by the table | | Innodb_rows_updated | 0 InnoDB Number of rows updated in the table | | Innodb_num_open_files | 136 | | Innodb_truncated_status_writes | 0 | | Innodb_available_undo_logs | 128 | | Key_blocks_not_flushed | 0 The number of key blocks in the key cache that have been changed but have not been emptied to the hard disk| | Key_blocks_unused | 53585 Number of unused blocks in the key cache | | Key_blocks_used | 3 Number of blocks used for keyword caching | | Key_read_requests | 6 The number of times a key value is requested to be read from the cache | | Key_reads | 3 The number of times a key value is physically read from the disk | | Key_write_requests | 0 Number of requests to write a keyword block to the cache | | Key_writes | 0 The number of times a key block is physically written to disk | | Last_query_cost | 0.000000 | | Last_query_partial_plans | 0 | | Locked_connects | 0 | | Max_execution_time_exceeded | 0 | | Max_execution_time_set | 0 | | Max_execution_time_set_failed | 0 | | Max_used_connections | 330 Maximum number of simultaneous connections | | Max_used_connections_time | 2022-02-24 22:07:44 | | Not_flushed_delayed_rows | 0 stay INSERT DELAY The number of rows in the queue waiting to be written | | Ongoing_anonymous_transaction_count | 0 | | Open_files | 5 Number of open files | | Open_streams | 0 Number of open streams(Mainly used for logging) | | Open_table_definitions | 11 Cached.frm Number of files | | Open_tables | 78 Number of open tables | | Opened_files | 7925 Total number of files opened by the system | | Opened_table_definitions | 0 Cached.frm Number of files | | Opened_tables | 0 Number of tables that have been opened | | Qcache_free_blocks | 1 Query the number of free memory blocks in the cache | | Qcache_free_memory | 1031832 Amount of free memory used for query cache | | Qcache_hits | 0 The number of times the query cache was accessed | | Qcache_inserts | 0 Number of queries added to the cache | | Qcache_lowmem_prunes | 0 Number of queries deleted from cache due to low memory | | Qcache_not_cached | 236889 Number of non cached queries | | Qcache_queries_in_cache | 0 Number of queries registered in the cache | | Qcache_total_blocks | 1 Total number of blocks in query cache | | Queries | 561204 Number of statements executed by the server | | Questions | 2 Number of queries sent to the server | | Select_full_join | 0 | | Select_full_range_join | 0 | | Select_range | 0 | | Select_range_check | 0 | | Select_scan | 0 | | Slave_open_temp_tables | 0 | | Slow_launch_threads | 0 | | Slow_queries | 0 To spend more than long_query_time Query quantity of time | | Sort_merge_passes | 0 | | Sort_range | 0 | | Sort_rows | 0 | | Sort_scan | 0 | | Ssl_*** | 0 be used for SSL Connected variables | | Table_locks_immediate | 123 The number of times the table's locks were obtained immediately | | Table_locks_waited | 0 The number of times a table's lock cannot be obtained immediately | | Tc_log_max_pages_used | 0 Maximum number of pages used by the log | | Tc_log_page_size | 0 be used for XA The page size of the memory mapping implementation of the recovery log | | Tc_log_page_waits | 0 Memory mapping implementation for recovery log | | Threads_cached | 2 Cache value of thread | | Threads_connected | 328 Number of currently open connections | | Threads_created | 330 Number of threads created to process connections | | Threads_running | 2 Number of threads not sleeping | | Uptime | 42686 How many seconds did the server work | | Uptime_since_flush_status | 42686 Last use FLUSH STATUS Time of (in seconds)| # Status information query statement --View attempts to connect to MySQL(Whether the connection is successful or not)Number of connections show status like 'connections'; --View the number of threads in the thread cache. show status like 'threads_cached'; --View the number of currently open connections. show status like 'threads_connected'; --Viewing query time exceeds long_query_time The number of queries per second. show status like 'slow_queries'; --View the number of threads show status like 'Threads%'; Threads_connected : This value refers to the number of open connections. Threads_running : This value refers to the number of active connections, which is generally much lower than connected numerical value. Threads_connected Follow show processlist The results are the same, indicating the current number of connections. To be precise, Threads_running Yes represents the current concurrent number Threads_created Indicates the number of threads that have been created Threads_created You can view MySQL The process status of the server. ( MySQL The number of threads on the server needs to be within a reasonable range to ensure MySQL The server runs healthily and smoothly) If we are MySQL The server configuration file has thread_cache_size，After the client is disconnected, the thread of the server processing this client will be cached to respond to the next client instead of destroying it(The precondition is that the number of caches has not reached the upper limit). Threads_created Indicates the number of threads created. If it is found that Threads_created If the value is too large, it indicates that MySQL The server has been creating threads, which also consumes resources. You can appropriately increase the number of threads in the configuration file thread_cache_size Value, query server thread_cache_size Value of: show variables like 'thread_cache_size'; # Usually, the maximum number of mysql connections is 100 by default, and the maximum can reach 16384. show variables like '%max_connection%'; View maximum connections set global max_connections=200 SET GLOBAL innodb_lru_scan_depth=256; Influence: right InnoDB Algorithm and heuristic parameters for buffer pool refresh operation. Performance expert tuning I/ o I'm interested in intensive workloads. It specifies that each buffer pool instance LRU The distance that the page cleaning thread scans to find the dirty page to refresh is listed below the page. This is a background operation performed every second.`Settings that are less than the default are generally applicable to most workloads`. If the value is much higher than the necessary value, performance may be affected. Idle only under typical workload I/O The value is only considered to be increased when the capacity is increased. Conversely, if a write intensive workload I/O If the capacity is saturated, reduce this value, especially when using large buffer pools. tuning innodb_lru_scan_depth Start with a lower value and configure it upward. The goal is to rarely see zero idle pages. In addition, when changing the number of buffer pool instances, you can consider adjusting innodb_lru_scan_depth，because innodb_lru_scan_depth * innodb_buffer_pool_instances Defines the workload of the page cleanup thread per second.
show processlist;
show full processlist; Or show processlist (only the top 100 are listed by default)// Show which threads are running
id #ID identification, which is very useful when you want to kill a statement user #Current connected user host #It shows which port of which ip this connection originates from db #Database name command #Connection status, generally sleep, query and connect time #Connection duration in seconds state #Displays the status of the current sql statement info #Display this sql statement
show slave status \G;
The MySQL synchronization function is implemented by three threads (one on the master and two on the slave). After executing the start slave statement, slave creates an I/O thread. The I/O thread connects to the master and requests the master to send statements in the binary log. The master creates a thread to send the contents of the log to the slave. This thread is the binlog dumpthread in the result of executing the show processlist statement on the master. The I/O thread on the slave reads the statements sent by the binlog dumpthread of the master, and copies them to the relay logs in its data directory. The third is the SQL thread, which the slave uses to read the relay logs and then executes them to update the data.
Used to provide information about key parameters of slave server threads
Slave_IO_State: ID The state of the thread, if master All changes to have been received. This status will be displayed as: Waiting for master to send event Master_Log_File: IO Thread is reading master binlog file name Read_Master_Log_Pos: IO The location where the thread has finished reading Relay_Master_Log_File: SQL Thread is reading master binlog file name Exec_Master_Log_Pos: SQL The location where the thread has read Slave_IO_State: SHOW PROCESSLIST Output State Copy of field. SHOW PROCESSLIST For dependencies I/O Thread. If the thread is trying to connect to the main server, waiting for the time from the main server, or connecting to the main server, etc., this statement will notify you
journal
| Log type: | Information written to the log |
|---|---|
| Error log | Record problems encountered when starting, running or stopping mysqld |
| General query log | Record established client connections and executed statements |
| Binary log | Record statements that change data |
| relay logs | Data changes received from replication master server |
| Slow query log | Record all execution times over long_query_time (seconds) or queries without index |
| DDL log (metadata log) | Metadata operations are performed by DDL statements |
# mysqlbinlog [options] logfile1 logfile2 ... mysqlbinlog Options for -d, --database=name Only the dump contents of the specified database are displayed. -o, --offset=# Skip the log entries of the first N rows. -r, --result-file=name Dumps the input text format file to the specified file. -R, --read-from-remote-server instructions mysqlbinlog Command reads log file from remote server -s, --short-form Use simple format(Show only SQL sentence). --server-id appoint mysql Server, make sure it is provided by the given server id of mysql Logs generated by the server. --set-charset=name Add at the beginning of the dump file'SET NAMES character_set'sentence. --start-datetime=name Start time of dump log. --stop-datetime=name Deadline of dump log. -j, --start-position=# The starting location of the dump log. --stop-position=# The cutoff location of the dump log. -v, --verbose Reconstructing pseudo with line events sql sentence. -vv display sql Statement plus field type.
view log
mysqlbinlog --no-defaults --database=qq --start-datetime='2022-02-11 18:00:00' --stop-datetime='2022-02-11 20:00:00' --base64-output=decode-rows -v mysql-bin.000052 >18-20.log
Added -- Base64 output = decode rows – v option for parsing (purpose: to become an SQL statement that can be read by humans)
Using the base64 output option, you can control when the output statement is a BINLOG statement encoded in Base64. The following are possible values for Base64 output settings:
never //It will display the base64 encoded BINLOG statement in the output always //Whenever possible, it will only display BINLOG items decode-rows //The row based event will be decoded into an SQL statement auto(Default) //Print BINLOG items only for certain event types, such as line based events and format description events
People are not afraid to walk in the night, not afraid of sunshine heart.