MySQL Study案例之--快速了解MySQL服务器

向右看齐 2022-08-07 10:41 215阅读 0赞

**MySQL Study案例之--快速了解MySQL服务器**

**1、查看数据库版本**

**2、查看服务器状态**

mysql> status
    --------------
    ./mysql  Ver 14.14 Distrib 5.6.4-m7, for Linux (x86_64) using  EditLine wrapper
    Connection id:          3
    Current database:
    Current user:           root@localhost
    SSL:                    Not in use
    Current pager:          stdout
    Using outfile:          ''
    Using delimiter:        ;
    Server version:         5.6.4-m7 Source distribution
    Protocol version:       10
    Connection:             Localhost via UNIX socket
    Server characterset:    latin1
    Db     characterset:    latin1
    Client characterset:    utf8
    Conn.  characterset:    utf8
    UNIX socket:            /tmp/mysql.sock
    Uptime:                 26 min 33 sec
    Threads: 1  Questions: 6  Slow queries: 0  Opens: 66  Flush tables: 1  Open tables: 59  Queries per second avg: 0.003
       该列表中主要包括MySQL的版本（为version 5.6.4）、运行平台（linux（x86_64））、数据库连接的ID、当前连接用户、以及连接的方式（本次连接采用UNIX socket，而未使用SSL）、服务端的字符集、MySQL运行时间和线程、查询、表的一些参数。

**3、查看数据库存储引擎**

mysql> show engines;
    +--------------------+---------+------------------------------------------------------------+--------------+------+------------+
    | Engine             | Support | Comment                                                    | Transactions | XA   | Savepoints |
    +--------------------+---------+------------------------------------------------------------+--------------+------+------------+
    | MyISAM             | YES     | MyISAM storage engine                                      | NO           | NO   | NO         |
    | InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
    | MRG_MYISAM         | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |
    | PERFORMANCE_SCHEMA | YES     | Performance Schema                                         | NO           | NO   | NO         |
    | CSV                | YES     | CSV storage engine                                         | NO           | NO   | NO         |
    | MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |
    +--------------------+---------+------------------------------------------------------------+--------------+------+------------+
    6 rows in set (0.00 sec)
       MySQL 5.6默认的引擎为innodb。

**4、查看引擎插件**

**5、查看引擎状态**

mysql> Show engine innodb status;
    | Type   | Name | Status                                                                                                                                                                                                                                                 | InnoDB |      | 
    =====================================
    150206 13:21:41 INNODB MONITOR OUTPUT
    =====================================
    Per second averages calculated from the last 32 seconds
    -----------------
    BACKGROUND THREAD
    -----------------
    srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 3076 srv_idle
    srv_master_thread log flush and writes: 3076
    ----------
    SEMAPHORES
    ----------
    OS WAIT ARRAY INFO: reservation count 2
    OS WAIT ARRAY INFO: signal count 2
    Mutex spin waits 0, rounds 0, OS waits 0
    RW-shared spins 2, rounds 60, OS waits 2
    RW-excl spins 0, rounds 0, OS waits 0
    Spin rounds per wait: 0.00 mutex, 30.00 RW-shared, 0.00 RW-excl
    ------------
    TRANSACTIONS
    ------------
    Trx id counter 2817
    Purge done for trx's n:o < 0 undo n:o < 0
    History list length 0
    LIST OF TRANSACTIONS FOR EACH SESSION:
    ---TRANSACTION 0, not started
    MySQL thread id 3, OS thread handle 0x7f56600f3700, query id 15 localhost root init
    Show engine innodb status
    --------
    FILE I/O
    --------
    I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
    I/O thread 1 state: waiting for completed aio requests (log thread)
    I/O thread 2 state: waiting for completed aio requests (read thread)
    I/O thread 3 state: waiting for completed aio requests (read thread)
    I/O thread 4 state: waiting for completed aio requests (read thread)
    I/O thread 5 state: waiting for completed aio requests (read thread)
    I/O thread 6 state: waiting for completed aio requests (write thread)
    I/O thread 7 state: waiting for completed aio requests (write thread)
    I/O thread 8 state: waiting for completed aio requests (write thread)
    I/O thread 9 state: waiting for completed aio requests (write thread)
    Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
     ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
    Pending flushes (fsync) log: 0; buffer pool: 0
    156 OS file reads, 5 OS file writes, 5 OS fsyncs
    0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
    -------------------------------------
    INSERT BUFFER AND ADAPTIVE HASH INDEX
    -------------------------------------
    Ibuf: size 1, free list len 0, seg size 2, 0 merges
    merged operations:
     insert 0, delete mark 0, delete 0
    discarded operations:
     insert 0, delete mark 0, delete 0
    Hash table size 276707, node heap has 0 buffer(s)
    0.00 hash searches/s, 0.00 non-hash searches/s
    ---
    LOG
    ---
    Log sequence number 1602881
    Log flushed up to   1602881
    Pages flushed up to 1602881
    Last checkpoint at  1602881
    0 pending log writes, 0 pending chkp writes
    8 log i/o's done, 0.00 log i/o's/second
    ----------------------
    BUFFER POOL AND MEMORY
    ----------------------
    Total memory allocated 137363456; in additional pool allocated 0
    Dictionary memory allocated 34906
    Buffer pool size   8192
    Free buffers       8047
    Database pages     145
    Old database pages 0
    Modified db pages  0
    Pending reads 0
    Pending writes: LRU 0, flush list 0 single page 0
    Pages made young 0, not young 0
    0.00 youngs/s, 0.00 non-youngs/s
    Pages read 145, created 0, written 1
    0.00 reads/s, 0.00 creates/s, 0.00 writes/s
    Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
    Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
    LRU len: 145, unzip_LRU len: 0
    I/O sum[0]:cur[0], unzip sum[0]:cur[0]
    --------------
    ROW OPERATIONS
    --------------
    0 queries inside InnoDB, 0 queries in queue
    0 read views open inside InnoDB
    Main thread process no. 4728, id 140008597153536, state: sleeping
    Number of rows inserted 0, updated 0, deleted 0, read 0
    0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
    ----------------------------
    END OF INNODB MONITOR OUTPUT
    ============================
     1 row in set (0.01 sec)

**6、查看数据库状态统计**

mysql> show status;
    +-------------------------------------------+-------------+
    | Variable_name                             | Value       |
    +-------------------------------------------+-------------+
    | Aborted_clients                           | 0           |
    | Aborted_connects                          | 1           |
    | Binlog_cache_disk_use                     | 0           |
    | Binlog_cache_use                          | 0           |
    | Binlog_stmt_cache_disk_use                | 0           |
    | Binlog_stmt_cache_use                     | 0           |
    | Bytes_received                            | 1333        |
    | Bytes_sent                                | 53031       |
    | Com_admin_commands                        | 0           |
    | Com_assign_to_keycache                    | 0           |
    | Com_alter_db                              | 0           |
    | Com_alter_db_upgrade                      | 0           |
    | Com_alter_event                           | 0           |
    | Com_alter_function                        | 0           |
    | Com_alter_procedure                       | 0           |
    | Com_alter_server                          | 0           |
    | Com_alter_table                           | 0           |
    | Com_alter_tablespace                      | 0           |
    | Com_analyze                               | 0           |
    | Com_begin                                 | 0           |
    | Com_binlog                                | 0           |
    | Com_call_procedure                        | 0           |
    | Com_change_db                             | 1           |
    | Com_change_master                         | 0           |
    | Com_check                                 | 0           |
    | Com_checksum                              | 0           |
    | Com_commit                                | 0           |
    | Com_create_db                             | 0           |
    | Com_create_event                          | 0           |
    | Com_create_function                       | 0           |
    | Com_create_index                          | 0           |
    | Com_create_procedure                      | 0           |
    | Com_create_server                         | 0           |
    | Com_create_table                          | 0           |
    | Com_create_trigger                        | 0           |
    | Com_create_udf                            | 0           |
    | Com_create_user                           | 0           |
    | Com_create_view                           | 0           |
    | Com_dealloc_sql                           | 0           |
    | Com_delete                                | 0           |
    | Com_delete_multi                          | 0           |
    | Com_do                                    | 0           |
    | Com_drop_db                               | 0           |
    | Com_drop_event                            | 0           |
    | Com_drop_function                         | 0           |
    | Com_drop_index                            | 0           |
    | Com_drop_procedure                        | 0           |
    | Com_drop_server                           | 0           |
    | Com_drop_table                            | 0           |
    | Com_drop_trigger                          | 0           |
    | Com_drop_user                             | 0           |
    | Com_drop_view                             | 0           |
    | Com_empty_query                           | 0           |
    | Com_execute_sql                           | 0           |
    | Com_flush                                 | 0           |
    | Com_get_diagnostics                       | 0           |
    | Com_grant                                 | 0           |
    | Com_ha_close                              | 0           |
    | Com_ha_open                               | 0           |
    | Com_ha_read                               | 0           |
    | Com_help                                  | 0           |
    | Com_insert                                | 0           |
    | Com_insert_select                         | 0           |
    | Com_install_plugin                        | 0           |
    | Com_kill                                  | 0           |
    | Com_load                                  | 0           |
    | Com_lock_tables                           | 0           |
    | Com_optimize                              | 0           |
    | Com_preload_keys                          | 0           |
    | Com_prepare_sql                           | 0           |
    | Com_purge                                 | 0           |
    | Com_purge_before_date                     | 0           |
    | Com_release_savepoint                     | 0           |
    | Com_rename_table                          | 0           |
    | Com_rename_user                           | 0           |
    | Com_repair                                | 0           |
    | Com_replace                               | 0           |
    | Com_replace_select                        | 0           |
    | Com_reset                                 | 0           |
    | Com_resignal                              | 0           |
    | Com_revoke                                | 0           |
    | Com_revoke_all                            | 0           |
    | Com_rollback                              | 0           |
    | Com_rollback_to_savepoint                 | 0           |
    | Com_savepoint                             | 0           |
    | Com_select                                | 4           |
    | Com_set_option                            | 0           |
    | Com_signal                                | 0           |
    | Com_show_authors                          | 0           |
    | Com_show_binlog_events                    | 0           |
    | Com_show_binlogs                          | 0           |
    | Com_show_charsets                         | 0           |
    | Com_show_collations                       | 0           |
    | Com_show_contributors                     | 0           |
    | Com_show_create_db                        | 0           |
    | Com_show_create_event                     | 0           |
    | Com_show_create_func                      | 0           |
    | Com_show_create_proc                      | 0           |
    | Com_show_create_table                     | 0           |
    | Com_show_create_trigger                   | 0           |
    | Com_show_databases                        | 1           |
    | Com_show_engine_logs                      | 0           |
    | Com_show_engine_mutex                     | 0           |
    | Com_show_engine_status                    | 1           |
    | Com_show_events                           | 0           |
    | Com_show_errors                           | 0           |
    | Com_show_fields                           | 29          |
    | Com_show_function_code                    | 0           |
    | Com_show_function_status                  | 0           |
    | Com_show_grants                           | 0           |
    | Com_show_keys                             | 0           |
    | Com_show_master_status                    | 2           |
    | Com_show_open_tables                      | 0           |
    | Com_show_plugins                          | 1           |
    | Com_show_privileges                       | 0           |
    | Com_show_procedure_code                   | 0           |
    | Com_show_procedure_status                 | 1           |
    | Com_show_processlist                      | 0           |
    | Com_show_profile                          | 0           |
    | Com_show_profiles                         | 0           |
    | Com_show_relaylog_events                  | 0           |
    | Com_show_slave_hosts                      | 0           |
    | Com_show_slave_status                     | 1           |
    | Com_show_status                           | 2           |
    | Com_show_storage_engines                  | 3           |
    | Com_show_table_status                     | 1           |
    | Com_show_tables                           | 1           |
    | Com_show_triggers                         | 0           |
    | Com_show_variables                        | 3           |
    | Com_show_warnings                         | 0           |
    | Com_slave_start                           | 0           |
    | Com_slave_stop                            | 0           |
    | Com_stmt_close                            | 0           |
    | Com_stmt_execute                          | 0           |
    | Com_stmt_fetch                            | 0           |
    | Com_stmt_prepare                          | 0           |
    | Com_stmt_reprepare                        | 0           |
    | Com_stmt_reset                            | 0           |
    | Com_stmt_send_long_data                   | 0           |
    | Com_truncate                              | 0           |
    | Com_uninstall_plugin                      | 0           |
    | Com_unlock_tables                         | 0           |
    | Com_update                                | 0           |
    | Com_update_multi                          | 0           |
    | Com_xa_commit                             | 0           |
    | Com_xa_end                                | 0           |
    | Com_xa_prepare                            | 0           |
    | Com_xa_recover                            | 0           |
    | Com_xa_rollback                           | 0           |
    | Com_xa_start                              | 0           |
    | Compression                               | OFF         |
    | Connections                               | 4           |
    | Created_tmp_disk_tables                   | 2           |
    | Created_tmp_files                         | 5           |
    | Created_tmp_tables                        | 11          |
    | Delayed_errors                            | 0           |
    | Delayed_insert_threads                    | 0           |
    | Delayed_writes                            | 0           |
    | Flush_commands                            | 1           |
    | Handler_commit                            | 0           |
    | Handler_delete                            | 0           |
    | Handler_discover                          | 0           |
    | Handler_external_lock                     | 2           |
    | Handler_mrr_init                          | 0           |
    | Handler_prepare                           | 0           |
    | Handler_read_first                        | 1           |
    | Handler_read_key                          | 2           |
    | Handler_read_last                         | 0           |
    | Handler_read_next                         | 0           |
    | Handler_read_prev                         | 0           |
    | Handler_read_rnd                          | 0           |
    | Handler_read_rnd_next                     | 515         |
    | Handler_rollback                          | 0           |
    | Handler_savepoint                         | 0           |
    | Handler_savepoint_rollback                | 0           |
    | Handler_update                            | 0           |
    | Handler_write                             | 504         |
    | Innodb_buffer_pool_dump_status            | not started |
    | Innodb_buffer_pool_load_status            | not started |
    | Innodb_buffer_pool_pages_data             | 150         |
    | Innodb_buffer_pool_pages_dirty            | 0           |
    | Innodb_buffer_pool_pages_flushed          | 2           |
    | Innodb_buffer_pool_pages_free             | 8042        |
    | Innodb_buffer_pool_pages_misc             | 0           |
    | Innodb_buffer_pool_pages_total            | 8192        |
    | Innodb_buffer_pool_read_ahead_rnd         | 0           |
    | Innodb_buffer_pool_read_ahead             | 0           |
    | Innodb_buffer_pool_read_ahead_evicted     | 0           |
    | Innodb_buffer_pool_read_requests          | 3750        |
    | Innodb_buffer_pool_reads                  | 151         |
    | Innodb_buffer_pool_wait_free              | 0           |
    | Innodb_buffer_pool_write_requests         | 1           |
    | Innodb_data_fsyncs                        | 5           |
    | Innodb_data_pending_fsyncs                | 0           |
    | Innodb_data_pending_reads                 | 0           |
    | Innodb_data_pending_writes                | 0           |
    | Innodb_data_read                          | 4657152     |
    | Innodb_data_reads                         | 161         |
    | Innodb_data_writes                        | 5           |
    | Innodb_data_written                       | 34304       |
    | Innodb_dblwr_pages_written                | 1           |
    | Innodb_dblwr_writes                       | 1           |
    | Innodb_have_atomic_builtins               | ON          |
    | Innodb_log_waits                          | 0           |
    | Innodb_log_write_requests                 | 0           |
    | Innodb_log_writes                         | 1           |
    | Innodb_os_log_fsyncs                      | 3           |
    | Innodb_os_log_pending_fsyncs              | 0           |
    | Innodb_os_log_pending_writes              | 0           |
    | Innodb_os_log_written                     | 512         |
    | Innodb_page_size                          | 16384       |
    | Innodb_pages_created                      | 0           |
    | Innodb_pages_read                         | 150         |
    | Innodb_pages_written                      | 1           |
    | Innodb_row_lock_current_waits             | 0           |
    | Innodb_row_lock_time                      | 0           |
    | Innodb_row_lock_time_avg                  | 0           |
    | Innodb_row_lock_time_max                  | 0           |
    | Innodb_row_lock_waits                     | 0           |
    | Innodb_rows_deleted                       | 0           |
    | Innodb_rows_inserted                      | 0           |
    | Innodb_rows_read                          | 0           |
    | Innodb_rows_updated                       | 0           |
    | Innodb_num_open_files                     | 3           |
    | Innodb_truncated_status_writes            | 0           |
    | Key_blocks_not_flushed                    | 0           |
    | Key_blocks_unused                         | 6698        |
    | Key_blocks_used                           | 0           |
    | Key_read_requests                         | 0           |
    | Key_reads                                 | 0           |
    | Key_write_requests                        | 0           |
    | Key_writes                                | 0           |
    | Last_query_cost                           | 10.499000   |
    | Max_used_connections                      | 1           |
    | Not_flushed_delayed_rows                  | 0           |
    | Open_files                                | 54          |
    | Open_streams                              | 0           |
    | Open_table_definitions                    | 77          |
    | Open_tables                               | 77          |
    | Opened_files                              | 195         |
    | Opened_table_definitions                  | 11          |
    | Opened_tables                             | 18          |
    | Performance_schema_accounts_lost          | 0           |
    | Performance_schema_cond_classes_lost      | 0           |
    | Performance_schema_cond_instances_lost    | 0           |
    | Performance_schema_file_classes_lost      | 0           |
    | Performance_schema_file_handles_lost      | 0           |
    | Performance_schema_file_instances_lost    | 0           |
    | Performance_schema_hosts_lost             | 0           |
    | Performance_schema_locker_lost            | 0           |
    | Performance_schema_mutex_classes_lost     | 0           |
    | Performance_schema_mutex_instances_lost   | 0           |
    | Performance_schema_rwlock_classes_lost    | 0           |
    | Performance_schema_rwlock_instances_lost  | 0           |
    | Performance_schema_socket_classes_lost    | 0           |
    | Performance_schema_socket_instances_lost  | 0           |
    | Performance_schema_stage_classes_lost     | 0           |
    | Performance_schema_statement_classes_lost | 0           |
    | Performance_schema_table_handles_lost     | 0           |
    | Performance_schema_table_instances_lost   | 0           |
    | Performance_schema_thread_classes_lost    | 0           |
    | Performance_schema_thread_instances_lost  | 0           |
    | Performance_schema_users_lost             | 0           |
    | Prepared_stmt_count                       | 0           |
    | Qcache_free_blocks                        | 0           |
    | Qcache_free_memory                        | 0           |
    | Qcache_hits                               | 0           |
    | Qcache_inserts                            | 0           |
    | Qcache_lowmem_prunes                      | 0           |
    | Qcache_not_cached                         | 0           |
    | Qcache_queries_in_cache                   | 0           |
    | Qcache_total_blocks                       | 0           |
    | Queries                                   | 56          |
    | Questions                                 | 54          |
    | Select_full_join                          | 0           |
    | Select_full_range_join                    | 0           |
    | Select_range                              | 0           |
    | Select_range_check                        | 0           |
    | Select_scan                               | 11          |
    | Slave_heartbeat_period                    | 0.000       |
    | Slave_last_heartbeat                      |             |
    | Slave_open_temp_tables                    | 0           |
    | Slave_received_heartbeats                 | 0           |
    | Slave_retried_transactions                | 0           |
    | Slave_running                             | OFF         |
    | Slow_launch_threads                       | 0           |
    | Slow_queries                              | 0           |
    | Sort_merge_passes                         | 0           |
    | Sort_range                                | 0           |
    | Sort_rows                                 | 0           |
    | Sort_scan                                 | 0           |
    | Table_locks_immediate                     | 70          |
    | Table_locks_waited                        | 0           |
    | Tc_log_max_pages_used                     | 0           |
    | Tc_log_page_size                          | 0           |
    | Tc_log_page_waits                         | 0           |
    | Threads_cached                            | 0           |
    | Threads_connected                         | 1           |
    | Threads_created                           | 3           |
    | Threads_running                           | 1           |
    | Uptime                                    | 3221        |
    | Uptime_since_flush_status                 | 3221        |
    +-------------------------------------------+-------------+
    302 rows in set (0.01 sec)

**7、查看数据库参数**

本文出自 “[天涯客的blog][blog]” 博客，请务必保留此出处[http://tiany.blog.51cto.com/513694/1612448][http_tiany.blog.51cto.com_513694_1612448]

[blog]: http://tiany.blog.51cto.com
[http_tiany.blog.51cto.com_513694_1612448]: http://tiany.blog.51cto.com/513694/1612448