Home | 简体中文 | 繁体中文 | 杂文 | Github | 知乎专栏 | 51CTO学院 | CSDN程序员研修院 | OSChina 博客 | 腾讯云社区 | 阿里云栖社区 | Facebook | Linkedin | Youtube | 打赏(Donations) | About
知乎专栏多维度架构

2.3. mysqladmin - client for administering a MySQL server

2.3.1. reload

mysqladmin --user=root --password reload
			

2.3.2. 更改密码

sudo mysqladmin -u root -p password '你的新密码'
			

2.3.3. status

每个10秒输出一次mysql的状态信息

mysqladmin -i 10 extended status
			
mysqladmin -h 172.16.0.1 -u monitor -ppasswd status
Uptime: 195824  Threads: 21  Questions: 57744081  Slow queries: 516230  Opens: 13202607  Flush tables: 1  Open tables: 160  Queries per second avg: 294.877
			
 mysqladmin -h 172.16.0.1 -u monitor -ppasswd  extended-status
+-----------------------------------+-------------+
| Variable_name                     | Value       |
+-----------------------------------+-------------+
| Aborted_clients                   | 60336       |
| Aborted_connects                  | 4599        |
| Binlog_cache_disk_use             | 36          |
| Binlog_cache_use                  | 100721      |
| Bytes_received                    | 17510873261 |
| Bytes_sent                        | 92890743568 |
| Com_admin_commands                | 10026660    |
| 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                   | 418         |
| Com_alter_tablespace              | 0           |
| Com_analyze                       | 0           |
| Com_backup_table                  | 0           |
| Com_begin                         | 0           |
| Com_binlog                        | 0           |
| Com_call_procedure                | 0           |
| Com_change_db                     | 4440400     |
| Com_change_master                 | 1           |
| Com_check                         | 0           |
| Com_checksum                      | 0           |
| Com_commit                        | 30089       |
| Com_create_db                     | 1           |
| Com_create_event                  | 0           |
| Com_create_function               | 0           |
| Com_create_index                  | 1           |
| Com_create_procedure              | 0           |
| Com_create_server                 | 0           |
| Com_create_table                  | 211         |
| Com_create_trigger                | 4           |
| Com_create_udf                    | 0           |
| Com_create_user                   | 0           |
| Com_create_view                   | 2           |
| Com_dealloc_sql                   | 0           |
| Com_delete                        | 36664       |
| 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                    | 213         |
| Com_drop_trigger                  | 0           |
| Com_drop_user                     | 0           |
| Com_drop_view                     | 4           |
| Com_empty_query                   | 0           |
| Com_execute_sql                   | 0           |
| Com_flush                         | 9           |
| Com_grant                         | 6           |
| Com_ha_close                      | 0           |
| Com_ha_open                       | 0           |
| Com_ha_read                       | 0           |
| Com_help                          | 0           |
| Com_insert                        | 472260      |
| Com_insert_select                 | 0           |
| Com_install_plugin                | 0           |
| Com_kill                          | 12          |
| Com_load                          | 0           |
| Com_load_master_data              | 0           |
| Com_load_master_table             | 0           |
| Com_lock_tables                   | 209         |
| 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                       | 4612        |
| Com_replace_select                | 0           |
| Com_reset                         | 0           |
| Com_restore_table                 | 0           |
| Com_revoke                        | 0           |
| Com_revoke_all                    | 0           |
| Com_rollback                      | 0           |
| Com_rollback_to_savepoint         | 0           |
| Com_savepoint                     | 0           |
| Com_select                        | 20310686    |
| Com_set_option                    | 9089818     |
| Com_show_authors                  | 0           |
| Com_show_binlog_events            | 0           |
| Com_show_binlogs                  | 0           |
| Com_show_charsets                 | 24          |
| Com_show_collations               | 18214       |
| Com_show_column_types             | 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                | 24          |
| Com_show_engine_logs              | 0           |
| Com_show_engine_mutex             | 0           |
| Com_show_engine_status            | 0           |
| Com_show_events                   | 0           |
| Com_show_errors                   | 0           |
| Com_show_fields                   | 147160      |
| Com_show_function_status          | 3           |
| Com_show_grants                   | 0           |
| Com_show_keys                     | 2           |
| Com_show_master_status            | 1           |
| Com_show_new_master               | 0           |
| Com_show_open_tables              | 0           |
| Com_show_plugins                  | 0           |
| Com_show_privileges               | 0           |
| Com_show_procedure_status         | 3           |
| Com_show_processlist              | 12483       |
| Com_show_profile                  | 0           |
| Com_show_profiles                 | 0           |
| Com_show_slave_hosts              | 0           |
| Com_show_slave_status             | 0           |
| Com_show_status                   | 1158        |
| Com_show_storage_engines          | 0           |
| Com_show_table_status             | 2           |
| Com_show_tables                   | 29915       |
| Com_show_triggers                 | 0           |
| Com_show_variables                | 26295       |
| 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                 | 209         |
| Com_update                        | 501411      |
| Com_update_multi                  | 23112       |
| 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                       | 4555052     |
| Created_tmp_disk_tables           | 421231      |
| Created_tmp_files                 | 1172        |
| Created_tmp_tables                | 2769149     |
| Delayed_errors                    | 0           |
| Delayed_insert_threads            | 0           |
| Delayed_writes                    | 0           |
| Flush_commands                    | 1           |
| Handler_commit                    | 100721      |
| Handler_delete                    | 133583      |
| Handler_discover                  | 0           |
| Handler_prepare                   | 0           |
| Handler_read_first                | 404032      |
| Handler_read_key                  | 18292439681 |
| Handler_read_next                 | 33393351305 |
| Handler_read_prev                 | 77792315    |
| Handler_read_rnd                  | 2969739139  |
| Handler_read_rnd_next             | 41965058450 |
| Handler_rollback                  | 0           |
| Handler_savepoint                 | 0           |
| Handler_savepoint_rollback        | 0           |
| Handler_update                    | 4595750766  |
| Handler_write                     | 6069006380  |
| Innodb_buffer_pool_pages_data     | 19          |
| Innodb_buffer_pool_pages_dirty    | 0           |
| Innodb_buffer_pool_pages_flushed  | 0           |
| Innodb_buffer_pool_pages_free     | 493         |
| Innodb_buffer_pool_pages_misc     | 0           |
| Innodb_buffer_pool_pages_total    | 512         |
| Innodb_buffer_pool_read_ahead_rnd | 1           |
| Innodb_buffer_pool_read_ahead_seq | 0           |
| Innodb_buffer_pool_read_requests  | 77          |
| Innodb_buffer_pool_reads          | 12          |
| Innodb_buffer_pool_wait_free      | 0           |
| Innodb_buffer_pool_write_requests | 0           |
| Innodb_data_fsyncs                | 3           |
| Innodb_data_pending_fsyncs        | 0           |
| Innodb_data_pending_reads         | 0           |
| Innodb_data_pending_writes        | 0           |
| Innodb_data_read                  | 2494464     |
| Innodb_data_reads                 | 25          |
| Innodb_data_writes                | 3           |
| Innodb_data_written               | 1536        |
| Innodb_dblwr_pages_written        | 0           |
| Innodb_dblwr_writes               | 0           |
| 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                 | 19          |
| Innodb_pages_written              | 0           |
| 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           |
| Key_blocks_not_flushed            | 0           |
| Key_blocks_unused                 | 6917        |
| Key_blocks_used                   | 53585       |
| Key_read_requests                 | 35870213140 |
| Key_reads                         | 13788784    |
| Key_write_requests                | 35265303    |
| Key_writes                        | 2467239     |
| Last_query_cost                   | 0.000000    |
| Max_used_connections              | 3001        |
| Not_flushed_delayed_rows          | 0           |
| Open_files                        | 238         |
| Open_streams                      | 0           |
| Open_table_definitions            | 228         |
| Open_tables                       | 160         |
| Opened_files                      | 20864567    |
| Opened_table_definitions          | 653         |
| Opened_tables                     | 13202613    |
| Prepared_stmt_count               | 0           |
| Qcache_free_blocks                | 10480       |
| Qcache_free_memory                | 38697120    |
| Qcache_hits                       | 17943956    |
| Qcache_inserts                    | 8251298     |
| Qcache_lowmem_prunes              | 560647      |
| Qcache_not_cached                 | 11879434    |
| Qcache_queries_in_cache           | 54611       |
| Qcache_total_blocks               | 125193      |
| Queries                           | 57755205    |
| Questions                         | 57582352    |
| Rpl_status                        | NULL        |
| Select_full_join                  | 602236      |
| Select_full_range_join            | 6851        |
| Select_range                      | 1633467     |
| Select_range_check                | 0           |
| Select_scan                       | 10981650    |
| Slave_open_temp_tables            | 0           |
| Slave_retried_transactions        | 0           |
| Slave_running                     | OFF         |
| Slow_launch_threads               | 206         |
| Slow_queries                      | 516237      |
| Sort_merge_passes                 | 548         |
| Sort_range                        | 293328      |
| Sort_rows                         | 2831414035  |
| Sort_scan                         | 2726547     |
| Ssl_accept_renegotiates           | 0           |
| Ssl_accepts                       | 0           |
| Ssl_callback_cache_hits           | 0           |
| Ssl_cipher                        |             |
| Ssl_cipher_list                   |             |
| Ssl_client_connects               | 0           |
| Ssl_connect_renegotiates          | 0           |
| Ssl_ctx_verify_depth              | 0           |
| Ssl_ctx_verify_mode               | 0           |
| Ssl_default_timeout               | 0           |
| Ssl_finished_accepts              | 0           |
| Ssl_finished_connects             | 0           |
| Ssl_session_cache_hits            | 0           |
| Ssl_session_cache_misses          | 0           |
| Ssl_session_cache_mode            | NONE        |
| Ssl_session_cache_overflows       | 0           |
| Ssl_session_cache_size            | 0           |
| Ssl_session_cache_timeouts        | 0           |
| Ssl_sessions_reused               | 0           |
| Ssl_used_session_cache_entries    | 0           |
| Ssl_verify_depth                  | 0           |
| Ssl_verify_mode                   | 0           |
| Ssl_version                       |             |
| Table_locks_immediate             | 46406490    |
| Table_locks_waited                | 1428430     |
| Tc_log_max_pages_used             | 0           |
| Tc_log_page_size                  | 0           |
| Tc_log_page_waits                 | 0           |
| Threads_cached                    | 33          |
| Threads_connected                 | 33          |
| Threads_created                   | 77809       |
| Threads_running                   | 7           |
| Uptime                            | 195854      |
| Uptime_since_flush_status         | 195854      |
+-----------------------------------+-------------+

		

2.3.4. process list

[root@development ~]# mysqladmin -u root -p -h 127.0.0.1 processlist
Enter password:
+-------+--------+----------------------+--------+---------+------+-------+------------------+
| Id    | User   | Host                 | db     | Command | Time | State | Info             |
+-------+--------+----------------------+--------+---------+------+-------+------------------+
| 23648 | dbuser | 192.168.3.237:1220   | testdb | Sleep   | 2733 |       |                  |
| 23878 | dbuser | www.testdb.com:53639 | testdb | Sleep   | 7    |       |                  |
| 23881 | root   | localhost:57243      |        | Query   | 0    |       | show processlist |
+-------+--------+----------------------+--------+---------+------+-------+------------------+
			
mysql -u root -pneo -S /tmp/mysql.sock -e "show full processlist;"|grep -v Sleep