Mysqladmin是MySQL/MariaBD的数据库命令行管理工具,通过该工具,数据库管理员可方便的执行一些数据库基本管理任务,例如设置”root”帐户密码、修改”root”帐户密码、监控数据库进程、重载权限以及检查服务器状态等。
本文将演示”mysqladmin”工具非常有用的功能,这些功能对于每个数据库管理人员几乎都会使用。当然,想要使用这个工具,首先要保证系统已经安装了MySQL/MariaBD数据库,并且数据库系统最好保持最新,以确保所有命令都可以正常执行。
1] 设置root用户密码
MySQL/MariaBD安装后,root用户是没有设置密码的,可以使用如下命令为root用户设置密码:
# mysqladmin -u root password YOURNEWPASSWORD
2] 修改root用户密码
如果想修改或更新root用户密码,你可以使用如下命令将root用户密码”123456″修改为”xyz123″:
# mysqladmin -u root -p123456 password 'xyz123'
3] 查看MySQL数据库服务器运行状态
如果想查看MySQL/MariaDB数据库的运行状态,可以使用如下命令:
# mysqladmin -uroot -p ping Enter password: mysqld is alive
4] 如何查看系统运行的MySQL/MariaDB运行版本
以下的命令可以显示当前系统信息和MySQL/MariaDB数据库版本信息:
# mysqladmin -uroot -p version Enter password: mysqladmin Ver 9.1 Distrib 10.1.31-MariaDB, for Linux on x86_64 Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Server version 10.1.31-MariaDB Protocol version 10 Connection Localhost via UNIX socket UNIX socket /run/mysqld/mysqld.sock Uptime: 2 min 45 sec Threads: 1 Questions: 2 Slow queries: 0 Opens: 17 Flush tables: 1 Open tables: 11 Queries per second avg: 0.012
5] 查看当前MySQL/MariaDB服务器当前状态
查看当前数据库服务器的状态,则可以使用如下命令,这条命令会显示服务器运行时间、进行信息以及查询量等:
# mysqladmin -u root -p status Enter password: Uptime: 481 Threads: 1 Questions: 3 Slow queries: 0 Opens: 17 Flush tables: 1 Open tables: 11 Queries per second avg: 0.006
6] 如何查看MySQL/MariaDB服务器的全部状态
使用以下命令可以查看MySQL/MariaDB服务器更多的状态信息:
# mysqladmin -u root -p extended-status Enter password: +--------------------------------------------------------------+----------------------------------------+ | Variable_name | Value | +--------------------------------------------------------------+----------------------------------------+ | Aborted_clients | 0 | | Aborted_connects | 0 | | Access_denied_errors | 0 | | Acl_column_grants | 0 | | Acl_database_grants | 4 | | Acl_function_grants | 0 | | Acl_procedure_grants | 0 | | Acl_proxy_users | 2 | | Acl_role_grants | 0 | | Acl_roles | 0 | | Acl_table_grants | 0 | | Acl_users | 7 | | Aria_pagecache_blocks_not_flushed | 0 | | Aria_pagecache_blocks_unused | 15706 | | Aria_pagecache_blocks_used | 0 | | Aria_pagecache_read_requests | 0 | | Aria_pagecache_reads | 0 | | Aria_pagecache_write_requests | 0 | | Aria_pagecache_writes | 0 | | Aria_transaction_log_syncs | 0 | | Binlog_commits | 0 | | Binlog_group_commits | 0 | | Binlog_group_commit_trigger_count | 0 | | Binlog_group_commit_trigger_lock_wait | 0 | | Binlog_group_commit_trigger_timeout | 0 | | Binlog_snapshot_file | mysql-bin.000006 | | Binlog_snapshot_position | 327 | | Binlog_bytes_written | 0 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 0 | | Binlog_stmt_cache_disk_use | 0 | | Binlog_stmt_cache_use | 0 | | Busy_time | 0.000000 | | Bytes_received | 829 | | Bytes_sent | 695 | | Com_admin_commands | 1 | | 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_assign_to_keycache | 0 | | Com_begin | 0 | | Com_binlog | 0 | | Com_call_procedure | 0 | | Com_change_db | 0 | | Com_change_master | 0 | | Com_check | 0 | | Com_checksum | 0 | | Com_commit | 0 | | Com_compound_sql | 0 | | Com_create_db | 0 | | Com_create_event | 0 | | Com_create_function | 0 | | Com_create_index | 0 | | Com_create_procedure | 0 | | Com_create_role | 0 | | Com_create_server | 0 | | Com_create_table | 0 | | Com_create_temporary_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_role | 0 | | Com_drop_server | 0 | | Com_drop_table | 0 | | Com_drop_temporary_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_grant_role | 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_revoke_role | 0 | | Com_rollback | 0 | | Com_rollback_to_savepoint | 0 | | Com_savepoint | 0 | | Com_select | 0 | | Com_set_option | 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 | 0 | | Com_show_engine_logs | 0 | | Com_show_engine_mutex | 0 | | Com_show_engine_status | 0 | | Com_show_errors | 0 | | Com_show_events | 0 | | Com_show_explain | 0 | | Com_show_fields | 0 | | Com_show_function_status | 0 | | Com_show_generic | 0 | | Com_show_grants | 0 | | Com_show_keys | 0 | | Com_show_master_status | 0 | | Com_show_open_tables | 0 | | Com_show_plugins | 0 | | Com_show_privileges | 0 | | Com_show_procedure_status | 0 | | 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 | 0 | | Com_show_status | 3 | | Com_show_storage_engines | 0 | | Com_show_table_status | 0 | | Com_show_tables | 0 | | Com_show_triggers | 0 | | Com_show_variables | 0 | | Com_show_warnings | 0 | | Com_shutdown | 0 | | Com_signal | 0 | | Com_start_all_slaves | 0 | | Com_start_slave | 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_stop_all_slaves | 0 | | Com_stop_slave | 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 | | Connection_errors_accept | 0 | | Connection_errors_internal | 0 | | Connection_errors_max_connections | 0 | | Connection_errors_peer_address | 0 | | Connection_errors_select | 0 | | Connection_errors_tcpwrap | 0 | | Connections | 7 | | Cpu_time | 0.000000 | | Created_tmp_disk_tables | 0 | | Created_tmp_files | 6 | | Created_tmp_tables | 1 | | Delayed_errors | 0 | | Delayed_insert_threads | 0 | | Delayed_writes | 0 | | Delete_scan | 0 | | Empty_queries | 0 | | Executed_events | 0 | | Executed_triggers | 0 | | Feature_delay_key_write | 0 | | Feature_dynamic_columns | 0 | | Feature_fulltext | 0 | | Feature_gis | 0 | | Feature_locale | 0 | | Feature_subquery | 0 | | Feature_timezone | 0 | | Feature_trigger | 0 | | Feature_xml | 0 | | Flush_commands | 1 | | Handler_commit | 0 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_external_lock | 0 | | Handler_icp_attempts | 0 | | Handler_icp_match | 0 | | Handler_mrr_init | 0 | | Handler_mrr_key_refills | 0 | | Handler_mrr_rowid_refills | 0 | | Handler_prepare | 0 | | Handler_read_first | 3 | | Handler_read_key | 0 | | Handler_read_last | 0 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_retry | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_deleted | 0 | | Handler_read_rnd_next | 22 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_tmp_update | 0 | | Handler_tmp_write | 0 | | Handler_update | 0 | | Handler_write | 0 | | Innodb_available_undo_logs | 128 | | Innodb_background_log_sync | 687 | | Innodb_buffer_pool_bytes_data | 3670016 | | Innodb_buffer_pool_bytes_dirty | 0 | | Innodb_buffer_pool_dump_status | Dumping buffer pool(s) not yet started | | Innodb_buffer_pool_load_status | Loading buffer pool(s) not yet started | | Innodb_buffer_pool_pages_data | 224 | | Innodb_buffer_pool_pages_dirty | 0 | | Innodb_buffer_pool_pages_flushed | 1 | | Innodb_buffer_pool_pages_free | 7967 | | Innodb_buffer_pool_pages_lru_flushed | 0 | | Innodb_buffer_pool_pages_made_not_young | 0 | | Innodb_buffer_pool_pages_made_young | 0 | | Innodb_buffer_pool_pages_misc | 0 | | Innodb_buffer_pool_pages_old | 0 | | Innodb_buffer_pool_pages_total | 8191 | | Innodb_buffer_pool_read_ahead | 0 | | Innodb_buffer_pool_read_ahead_evicted | 0 | | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_requests | 1212 | | Innodb_buffer_pool_reads | 225 | | Innodb_buffer_pool_wait_free | 0 | | Innodb_buffer_pool_write_requests | 1 | | Innodb_checkpoint_age | 0 | | Innodb_checkpoint_max_age | 80826164 | | Innodb_data_fsyncs | 5 | | Innodb_data_pending_fsyncs | 0 | | Innodb_data_pending_reads | 0 | | Innodb_data_pending_writes | 0 | | Innodb_data_read | 3756544 | | Innodb_data_reads | 239 | | Innodb_data_writes | 5 | | Innodb_data_written | 34304 | | Innodb_dblwr_pages_written | 1 | | Innodb_dblwr_writes | 1 | | Innodb_deadlocks | 0 | | Innodb_have_atomic_builtins | ON | | Innodb_history_list_length | 35 | | Innodb_ibuf_discarded_delete_marks | 0 | | Innodb_ibuf_discarded_deletes | 0 | | Innodb_ibuf_discarded_inserts | 0 | | Innodb_ibuf_free_list | 0 | | Innodb_ibuf_merged_delete_marks | 0 | | Innodb_ibuf_merged_deletes | 0 | | Innodb_ibuf_merged_inserts | 0 | | Innodb_ibuf_merges | 0 | | Innodb_ibuf_segment_size | 2 | | Innodb_ibuf_size | 1 | | Innodb_log_waits | 0 | | Innodb_log_write_requests | 0 | | Innodb_log_writes | 1 | | Innodb_lsn_current | 1744765 | | Innodb_lsn_flushed | 1744765 | | Innodb_lsn_last_checkpoint | 1744765 | | Innodb_master_thread_active_loops | 1 | | Innodb_master_thread_idle_loops | 686 | | Innodb_max_trx_id | 3332 | | Innodb_mem_adaptive_hash | 2217568 | | Innodb_mem_dictionary | 629911 | | Innodb_mem_total | 140574720 | | Innodb_mutex_os_waits | 2 | | Innodb_mutex_spin_rounds | 60 | | Innodb_mutex_spin_waits | 2 | | Innodb_oldest_view_low_limit_trx_id | 0 | | 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 | 224 | | Innodb_pages0_read | 4 | | Innodb_pages_written | 1 | | Innodb_purge_trx_id | 2931 | | Innodb_purge_undo_no | 0 | | Innodb_read_views_memory | 88 | | 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_system_rows_deleted | 0 | | Innodb_system_rows_inserted | 0 | | Innodb_system_rows_read | 0 | | Innodb_system_rows_updated | 0 | | Innodb_s_lock_os_waits | 2 | | Innodb_s_lock_spin_rounds | 60 | | Innodb_s_lock_spin_waits | 2 | | Innodb_truncated_status_writes | 0 | | Innodb_x_lock_os_waits | 0 | | Innodb_x_lock_spin_rounds | 0 | | Innodb_x_lock_spin_waits | 0 | | Innodb_page_compression_saved | 0 | | Innodb_page_compression_trim_sect512 | 0 | | Innodb_page_compression_trim_sect1024 | 0 | | Innodb_page_compression_trim_sect2048 | 0 | | Innodb_page_compression_trim_sect4096 | 0 | | Innodb_page_compression_trim_sect8192 | 0 | | Innodb_page_compression_trim_sect16384 | 0 | | Innodb_page_compression_trim_sect32768 | 0 | | Innodb_num_index_pages_written | 0 | | Innodb_num_non_index_pages_written | 5 | | Innodb_num_pages_page_compressed | 0 | | Innodb_num_page_compressed_trim_op | 0 | | Innodb_num_page_compressed_trim_op_saved | 0 | | Innodb_num_pages_page_decompressed | 0 | | Innodb_num_pages_page_compression_error | 0 | | Innodb_num_pages_encrypted | 0 | | Innodb_num_pages_decrypted | 0 | | Innodb_have_lz4 | ON | | Innodb_have_lzo | ON | | Innodb_have_lzma | ON | | Innodb_have_bzip2 | ON | | Innodb_have_snappy | OFF | | Innodb_defragment_compression_failures | 0 | | Innodb_defragment_failures | 0 | | Innodb_defragment_count | 0 | | Innodb_onlineddl_rowlog_rows | 0 | | Innodb_onlineddl_rowlog_pct_used | 0 | | Innodb_onlineddl_pct_progress | 0 | | Innodb_secondary_index_triggered_cluster_reads | 0 | | Innodb_secondary_index_triggered_cluster_reads_avoided | 0 | | Innodb_buffered_aio_submitted | 0 | | Innodb_encryption_rotation_pages_read_from_cache | 0 | | Innodb_encryption_rotation_pages_read_from_disk | 0 | | Innodb_encryption_rotation_pages_modified | 0 | | Innodb_encryption_rotation_pages_flushed | 0 | | Innodb_encryption_rotation_estimated_iops | 0 | | Innodb_encryption_key_rotation_list_length | 0 | | Innodb_encryption_n_merge_blocks_encrypted | 0 | | Innodb_encryption_n_merge_blocks_decrypted | 0 | | Innodb_encryption_n_rowlog_blocks_encrypted | 0 | | Innodb_encryption_n_rowlog_blocks_decrypted | 0 | | Innodb_scrub_background_page_reorganizations | 0 | | Innodb_scrub_background_page_splits | 0 | | Innodb_scrub_background_page_split_failures_underflow | 0 | | Innodb_scrub_background_page_split_failures_out_of_filespace | 0 | | Innodb_scrub_background_page_split_failures_missing_index | 0 | | Innodb_scrub_background_page_split_failures_unknown | 0 | | Innodb_encryption_num_key_requests | 0 | | Key_blocks_not_flushed | 0 | | Key_blocks_unused | 13389 | | Key_blocks_used | 0 | | Key_blocks_warm | 0 | | Key_read_requests | 0 | | Key_reads | 0 | | Key_write_requests | 0 | | Key_writes | 0 | | Last_query_cost | 0.000000 | | Master_gtid_wait_count | 0 | | Master_gtid_wait_time | 0 | | Master_gtid_wait_timeouts | 0 | | Max_statement_time_exceeded | 0 | | Max_used_connections | 1 | | Memory_used | 156405160 | | Not_flushed_delayed_rows | 0 | | Open_files | 26 | | Open_streams | 0 | | Open_table_definitions | 18 | | Open_tables | 11 | | Opened_files | 80 | | Opened_plugin_libraries | 0 | | Opened_table_definitions | 17 | | Opened_tables | 17 | | Opened_views | 0 | | Performance_schema_accounts_lost | 0 | | Performance_schema_cond_classes_lost | 0 | | Performance_schema_cond_instances_lost | 0 | | Performance_schema_digest_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_session_connect_attrs_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 | 1 | | Qcache_free_memory | 1031336 | | Qcache_hits | 0 | | Qcache_inserts | 0 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 0 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 1 | | Queries | 4 | | Questions | 4 | | Rows_read | 13 | | Rows_sent | 0 | | Rows_tmp_read | 0 | | Rpl_status | AUTH_MASTER | | Select_full_join | 0 | | Select_full_range_join | 0 | | Select_range | 0 | | Select_range_check | 0 | | Select_scan | 1 | | Slave_connections | 0 | | Slave_heartbeat_period | 0.000 | | Slave_open_temp_tables | 0 | | Slave_received_heartbeats | 0 | | Slave_retried_transactions | 0 | | Slave_running | OFF | | Slave_skipped_errors | 0 | | Slaves_connected | 0 | | Slaves_running | 0 | | Slow_launch_threads | 0 | | Slow_queries | 0 | | Sort_merge_passes | 0 | | Sort_priority_queue_sorts | 0 | | Sort_range | 0 | | Sort_rows | 0 | | Sort_scan | 0 | | 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_server_not_after | | | Ssl_server_not_before | | | 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 | | | Subquery_cache_hit | 0 | | Subquery_cache_miss | 0 | | Syncs | 6 | | Table_locks_immediate | 21 | | Table_locks_waited | 0 | | Tc_log_max_pages_used | 0 | | Tc_log_page_size | 0 | | Tc_log_page_waits | 0 | | Threadpool_idle_threads | 0 | | Threadpool_threads | 0 | | Threads_cached | 0 | | Threads_connected | 1 | | Threads_created | 4 | | Threads_running | 1 | | Update_scan | 0 | | Uptime | 689 | | Uptime_since_flush_status | 689 | | wsrep_cluster_conf_id | 18446744073709551615 | | wsrep_cluster_size | 0 | | wsrep_cluster_state_uuid | | | wsrep_cluster_status | Disconnected | | wsrep_connected | OFF | | wsrep_local_bf_aborts | 0 | | wsrep_local_index | 18446744073709551615 | | wsrep_provider_name | | | wsrep_provider_vendor | | | wsrep_provider_version | | | wsrep_ready | OFF | | wsrep_thread_count | 0 | +--------------------------------------------------------------+----------------------------------------+
7] 查看当前MySQL/MariaDB服务器变量和数值
输入以下命令可 以查看当前MySQL/MariaDB服务器变量和数值:
# mysqladmin -u root -p variables
8] 查看当前MySQL/MariaDB服务器所有运行进程
以下命令显示MySQL所有的运行进程和查询:
# mysqladmin -uroot -p processlist Enter password: +----+------+-----------+----+---------+------+-------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+------+-----------+----+---------+------+-------+------------------+----------+ | 12 | root | localhost | | Query | 0 | init | show processlist | 0.000 | +----+------+-----------+----+---------+------+-------+------------------+----------+
9] 使用mysqladmin创建数据库
MySQL/MariaDB可以使用mysqladmin命令快速创建数据库,例如创建”NewDatabase”示例如下:
# mysqladmin -u root -p create NewDatabase Enter password: # mysql -uroot -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 14 Server version: 10.1.31-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | NewDatabase | | RultrDB | | information_schema | | mysql | | performance_schema | | test | +--------------------+ 6 rows in set (0.06 sec) MariaDB [(none)]>
10] 使用mysqladmin删除一个数据库
可以快速创建,当然也可以快速删除一个数据库,使用如下命令就可以将”NewDatabase”数据库删除,注意在删除时需要按”y”键确认:
# mysqladmin -u root -p drop NewDatabase Enter password: Dropping the database is potentially a very bad thing to do. Any data stored in the database will be destroyed. Do you really want to drop the 'NewDatabase' database [y/N] y Database "NewDatabase" dropped
11] 如何重新加载/刷新MySQL权限
使用”reload”命令会让MySQL重新加载权限表,而”refresh”命令会刷新所有系统表,并重新打开日志文件:
# mysqladmin -u root -p reload # mysqladmin -u root -p refresh
12] 安全的关闭MySQL/MariaDB数据库服务器
想要安全的关闭MySQL/MariaDB数据库服务器,可以使用如下命令:
# mysqladmin -u root -p shutdown
当然,使用Linux系统的命令也同样有效:
# systemctl stop mariadb # systemctl start mariadb
13] 一些重要的MySQL刷新命令
以下是一些重要的”mysqladmin”刷新重新和相应的解释:
- flush-hosts::刷新主机缓存中的所有主机信息
- flush-tables :刷新所有数据表
- flush-threads:刷新进程缓存
- flush-logs:刷新所有信息日志
- flush-privileges: 刷新系统权限表 (功能同reload类似).
- flush-status:清空所有状态变量
使用命令如下所示:
# mysqladmin -u root -p flush-hosts # mysqladmin -u root -p flush-tables # mysqladmin -u root -p flush-threads # mysqladmin -u root -p flush-logs # mysqladmin -u root -p flush-privileges # mysqladmin -u root -p flush-status
14] 删除MySQL客户端的睡眠进程
首先使用”processlist”命令查看所有进程,如果存在睡眠进程,则使用”kill 进程ID”就可结束该进程:
# mysqladmin -uroot -p processlist Enter password: +----+------+-----------+----+---------+------+-------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+------+-----------+----+---------+------+-------+------------------+----------+ | 8 | root | localhost | | Sleep | 63 | | | 0.000 | | 9 | root | localhost | | Query | 0 | init | show processlist | 0.000 | +----+------+-----------+----+---------+------+-------+------------------+----------+ [root@arch-host ~]# mysqladmin -uroot -p kill 8 Enter password: [root@arch-host ~]# mysqladmin -uroot -p processlist Enter password: +----+------+-----------+----+---------+------+-------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+------+-----------+----+---------+------+-------+------------------+----------+ | 11 | root | localhost | | Query | 0 | init | show processlist | 0.000 | +----+------+-----------+----+---------+------+-------+------------------+----------+
如果需要删除多个进程,则使用”,”号将各个进程ID分隔。
15] mysqladmin如何使用多条命令
如果需要同时使用mysqladmin的多条命令,可以按以下方式将不同命令空格分隔即可:
# mysqladmin -u root -p processlist status version Enter password: +----+------+-----------+----+---------+------+-------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+------+-----------+----+---------+------+-------+------------------+----------+ | 12 | root | localhost | | Query | 0 | init | show processlist | 0.000 | +----+------+-----------+----+---------+------+-------+------------------+----------+ Uptime: 1400 Threads: 1 Questions: 16 Slow queries: 0 Opens: 17 Flush tables: 1 Open tables: 11 Queries per second avg: 0.011 mysqladmin Ver 9.1 Distrib 10.1.31-MariaDB, for Linux on x86_64 Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Server version 10.1.31-MariaDB Protocol version 10 Connection Localhost via UNIX socket UNIX socket /run/mysqld/mysqld.sock Uptime: 23 min 20 sec Threads: 1 Questions: 16 Slow queries: 0 Opens: 17 Flush tables: 1 Open tables: 11 Queries per second avg: 0.011
16] 连接远程MySQL数据库
如果想要连接远程数据库,则mysqladmin可以使用-h参数加远程服务器IP地址方式指定远程数据库:
# mysqladmin -h 172.16.25.126 -u root -p
17] mysqladmin连接远程数据库并执行命令
要让远程数据库执行命令,方法同上一条类似,只不过在命令中加入相应的命令即可:
# mysqladmin -h 172.16.25.126 -u root -p status
18] 如何在从服务器上启动/停止MySQL副本
使用如下命令可以在从服务器上启动和停止MySQL副本:
# mysqladmin -u root -p start-slave # mysqladmin -u root -p stop-slave
19] 如果将MySQL调试信息存入日志
使用如下命令,会将服务器日志设置为调试级,就可以将锁、内存信息以及其它一些查询情况记录到MySQL日志文件中:
# mysqladmin -u root -p debug
20] 查看mysqladmin的帮助信息
如果想了解更多的mysqladmin的参数信息,可以使用–help命令来查看具体信息,使用如下命令后,就会显示出mysqladmin的全部参数信息:
# mysqladmin --help mysqladmin Ver 9.1 Distrib 10.1.31-MariaDB, for Linux on x86_64 Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Administration program for the mysqld daemon. Usage: mysqladmin [OPTIONS] command command.... Default options are read from the following files in the given order: /etc/mysql/my.cnf ~/.my.cnf The following groups are read: mysqladmin client client-server client-mariadb The following options may be given as the first argument: --print-defaults Print the program argument list and exit. --no-defaults Don't read default options from any option file. --defaults-file=# Only read default options from the given file #. --defaults-extra-file=# Read this file after the global files are read. -c, --count=# Number of iterations to make. This works with -i (--sleep) only. --debug-check Check memory and open file usage at exit. --debug-info Print some debug info at exit. -f, --force Don't ask for confirmation on drop database; with multiple commands, continue even if an error occurs. -C, --compress Use compression in server/client protocol. --character-sets-dir=name Directory for character set files. --default-character-set=name Set the default character set. -?, --help Display this help and exit. -h, --host=name Connect to host. -l, --local Local command, don't write to binlog. -b, --no-beep Turn off beep on error. -p, --password[=name] Password to use when connecting to server. If password is not given it's asked from the tty. -P, --port=# Port number to use for connection or 0 for default to, in order of preference, my.cnf, $MYSQL_TCP_PORT, /etc/services, built-in default (3306). --protocol=name The protocol to use for connection (tcp, socket, pipe, memory). -r, --relative Show difference between current and previous values when used with -i. Currently only works with extended-status. -s, --silent Silently exit if one can't connect to server. -S, --socket=name The socket file to use for connection. -i, --sleep=# Execute commands repeatedly with a sleep between. --ssl Enable SSL for connection (automatically enabled with other flags). --ssl-ca=name CA file in PEM format (check OpenSSL docs, implies --ssl). --ssl-capath=name CA directory (check OpenSSL docs, implies --ssl). --ssl-cert=name X509 cert in PEM format (implies --ssl). --ssl-cipher=name SSL cipher to use (implies --ssl). --ssl-key=name X509 key in PEM format (implies --ssl). --ssl-crl=name Certificate revocation list (implies --ssl). --ssl-crlpath=name Certificate revocation list path (implies --ssl). --ssl-verify-server-cert Verify server's "Common Name" in its cert against hostname used when connecting. This option is disabled by default. -u, --user=name User for login if not current user. -v, --verbose Write more information. -V, --version Output version information and exit. -E, --vertical Print output vertically. Is similar to --relative, but prints output vertically. -w, --wait[=#] Wait and retry if connection is down. --connect-timeout=# --shutdown-timeout=# --plugin-dir=name Directory for client-side plugins. --default-auth=name Default authentication client-side plugin to use. Variables (--variable-name=value) and boolean options {FALSE|TRUE} Value (after reading options) --------------------------------- ---------------------------------------- count 0 debug-check FALSE debug-info FALSE force FALSE compress FALSE character-sets-dir (No default value) default-character-set auto host (No default value) local FALSE no-beep FALSE port 3306 relative FALSE socket /run/mysqld/mysqld.sock sleep 0 ssl FALSE ssl-ca (No default value) ssl-capath (No default value) ssl-cert (No default value) ssl-cipher (No default value) ssl-key (No default value) ssl-crl (No default value) ssl-crlpath (No default value) ssl-verify-server-cert FALSE user (No default value) verbose FALSE vertical FALSE connect-timeout 43200 shutdown-timeout 3600 plugin-dir (No default value) default-auth (No default value) Where command is a one or more of: (Commands may be shortened) create databasename Create a new database debug Instruct server to write debug information to log drop databasename Delete a database and all its tables extended-status Gives an extended status message from the server flush-all-statistics Flush all statistics tables flush-all-status Flush status and statistics flush-client-statistics Flush client statistics flush-hosts Flush all cached hosts flush-index-statistics Flush index statistics flush-logs Flush all logs flush-privileges Reload grant tables (same as reload) flush-binary-log Flush binary log flush-engine-log Flush engine log(s) flush-error-log Flush error log flush-general-log Flush general log flush-relay-log Flush relay log flush-slow-log Flush slow query log flush-status Clear status variables flush-table-statistics Clear table statistics flush-tables Flush all tables flush-threads Flush the thread cache flush-user-statistics Flush user statistics flush-user-resources Flush user resources kill id,id,... Kill mysql threads password [new-password] Change old password to new-password in current format old-password [new-password] Change old password to new-password in old format ping Check if mysqld is alive processlist Show list of active threads in server reload Reload grant tables refresh Flush all tables and close and open logfiles shutdown Take server down status Gives a short status message from the server start-slave Start slave stop-slave Stop slave variables Prints variables available version Get version info from server
以上就是”mysqladmin”的常用操作,如果想了解更多内容,那么可以参照–help的内容进行更多的尝试,这里就不再一一举例。