• 首页
  • vue
  • TypeScript
  • JavaScript
  • scss
  • css3
  • html5
  • php
  • MySQL
  • redis
  • jQuery
    •
    位置： MySQL 8 中文手册 -> MySQL NDB群集8.0 -> NDB群集的管理

    快速参考：NDB群集SQL语句

    本节讨论了一些SQL语句，这些语句在管理和监视连接到NDB群集的MySQL服务器方面可能很有用，并在某些情况下提供了有关群集本身的信息。

    • SHOW ENGINE NDB STATUS，SHOW ENGINE NDBCLUSTER STATUS

      该语句的输出包含有关服务器与群集的连接，NDB Cluster对象的创建和使用以及NDB Cluster复制的二进制日志记录的信息。

      有关用法示例和更多详细信息，请参见“ SHOW ENGINE语句”。

    • SHOW ENGINES

      该语句可用于确定MySQL服务器中是否启用了集群支持，如果已启用，则是否处于活动状态。

      有关更多详细信息，请参见“ SHOW ENGINES语句”。

      注意

      该语句不支持LIKE子句。但是，您可以使用LIKE过滤针对该INFORMATION_SCHEMA.ENGINES表的查询，如下一项所述。

    • SELECT * FROM INFORMATION_SCHEMA.ENGINES[WHERE ENGINE LIKE 'NDB%']

      这等效于SHOW ENGINES，但使用数据库ENGINES表INFORMATION_SCHEMA。与该SHOW ENGINES语句不同，可以使用LIKE子句过滤结果，并选择特定的列以获得可能在脚本中使用的信息。例如，以下查询显示服务器是否由NDB支持构建，如果支持，则是否启用：

      mysql> SELECT SUPPORT FROM INFORMATION_SCHEMA.ENGINES
->   WHERE ENGINE LIKE 'NDB%';
+---------	+
| support	|
+---------	+
| ENABLED	|
+---------	+

      有关更多信息，请参见“ INFORMATION_SCHEMA引擎表”。

    • SHOW VARIABLES LIKE 'NDB%'

      该语句提供了与NDB存储引擎相关的大多数服务器系统变量及其值的列表，如下所示：

      mysql> SHOW VARIABLES LIKE 'NDB%';
+--------------------------------------	+---------------------------------------	+
| Variable_name	| Value	|
+--------------------------------------	+---------------------------------------	+
| ndb_allow_copying_alter_table	| ON	|
| ndb_autoincrement_prefetch_sz	| 512	|
| ndb_batch_size	| 32768	|
| ndb_blob_read_batch_bytes	| 65536	|
| ndb_blob_write_batch_bytes	| 65536	|
| ndb_clear_apply_status	| ON	|
| ndb_cluster_connection_pool	| 1	|
| ndb_cluster_connection_pool_nodeids	|	|
| ndb_connectstring	| 127.0.0.1	|
| ndb_data_node_neighbour	| 0	|
| ndb_default_column_format	| FIXED	|
| ndb_deferred_constraints	| 0	|
| ndb_distribution	| KEYHASH	|
| ndb_eventbuffer_free_percent	| 20	|
| ndb_eventbuffer_max_alloc	| 0	|
| ndb_extra_logging	| 1	|
| ndb_force_send	| ON	|
| ndb_fully_replicated	| OFF	|
| ndb_index_stat_enable	| ON	|
| ndb_index_stat_option                | loop_enable=1000ms,loop_idle=1000ms,
loop_busy=100ms,update_batch=1,read_batch=4,idle_batch=32,check_batch=8,
check_delay=10m,delete_batch=8,clean_delay=1m,error_batch=4,error_delay=1m,
evict_batch=8,evict_delay=1m,cache_limit=32M,cache_lowpct=90,zero_total=0      |
| ndb_join_pushdown	| ON	|
| ndb_log_apply_status	| OFF	|
| ndb_log_bin	| OFF	|
| ndb_log_binlog_index	| ON	|
| ndb_log_empty_epochs	| OFF	|
| ndb_log_empty_update	| OFF	|
| ndb_log_exclusive_reads	| OFF	|
| ndb_log_orig	| OFF	|
| ndb_log_transaction_id	| OFF	|
| ndb_log_update_as_write	| ON	|
| ndb_log_update_minimal	| OFF	|
| ndb_log_updated_only	| ON	|
| ndb_metadata_check	| ON	|
| ndb_metadata_check_interval	| 60	|
| ndb_metadata_sync	| OFF	|
| ndb_mgmd_host	| 127.0.0.1	|
| ndb_nodeid	| 0	|
| ndb_optimization_delay	| 10	|
| ndb_optimized_node_selection	| 3	|
| ndb_read_backup	| ON	|
| ndb_recv_thread_activation_threshold	| 8	|
| ndb_recv_thread_cpu_mask	|	|
| ndb_report_thresh_binlog_epoch_slip	| 10	|
| ndb_report_thresh_binlog_mem_usage	| 10	|
| ndb_row_checksum	| 1	|
| ndb_schema_dist_lock_wait_timeout	| 30	|
| ndb_schema_dist_timeout	| 120	|
| ndb_schema_dist_upgrade_allowed	| ON	|
| ndb_show_foreign_key_mock_tables	| OFF	|
| ndb_slave_conflict_role	| NONE	|
| ndb_table_no_logging	| OFF	|
| ndb_table_temporary	| OFF	|
| ndb_use_copying_alter_table	| OFF	|
| ndb_use_exact_count	| OFF	|
| ndb_use_transactions	| ON	|
| ndb_version	| 524308	|
| ndb_version_string	| ndb-8.0.20	|
| ndb_wait_connected	| 30	|
| ndb_wait_setup	| 30	|
| ndbinfo_database	| ndbinfo	|
| ndbinfo_max_bytes	| 0	|
| ndbinfo_max_rows	| 10	|
| ndbinfo_offline	| OFF	|
| ndbinfo_show_hidden	| OFF	|
| ndbinfo_table_prefix	| ndb$	|
| ndbinfo_version	| 524308	|
+--------------------------------------	+---------------------------------------	+

      有关更多信息，请参见“服务器系统变量”。

    • SELECT * FROM performance_schema.global_variables WHERE VARIABLE_NAME LIKE 'NDB%'

      该语句与上SHOW VARIABLES一项中描述的语句等效，并且提供几乎相同的输出，如下所示：

      mysql> SELECT * FROM performance_schema.global_variables
->   WHERE VARIABLE_NAME LIKE 'NDB%';
+--------------------------------------	+---------------------------------------	+
| VARIABLE_NAME	| VARIABLE_VALUE	|
+--------------------------------------	+---------------------------------------	+
| ndb_allow_copying_alter_table	| ON	|
| ndb_autoincrement_prefetch_sz	| 512	|
| ndb_batch_size	| 32768	|
| ndb_blob_read_batch_bytes	| 65536	|
| ndb_blob_write_batch_bytes	| 65536	|
| ndb_clear_apply_status	| ON	|
| ndb_cluster_connection_pool	| 1	|
| ndb_cluster_connection_pool_nodeids	|	|
| ndb_connectstring	| 127.0.0.1	|
| ndb_data_node_neighbour	| 0	|
| ndb_default_column_format	| FIXED	|
| ndb_deferred_constraints	| 0	|
| ndb_distribution	| KEYHASH	|
| ndb_eventbuffer_free_percent	| 20	|
| ndb_eventbuffer_max_alloc	| 0	|
| ndb_extra_logging	| 1	|
| ndb_force_send	| ON	|
| ndb_fully_replicated	| OFF	|
| ndb_index_stat_enable	| ON	|
| ndb_index_stat_option                | loop_enable=1000ms,loop_idle=1000ms,
loop_busy=100ms,update_batch=1,read_batch=4,idle_batch=32,check_batch=8,
check_delay=10m,delete_batch=8,clean_delay=1m,error_batch=4,error_delay=1m,
evict_batch=8,evict_delay=1m,cache_limit=32M,cache_lowpct=90,zero_total=0      |
| ndb_join_pushdown	| ON	|
| ndb_log_apply_status	| OFF	|
| ndb_log_bin	| OFF	|
| ndb_log_binlog_index	| ON	|
| ndb_log_empty_epochs	| OFF	|
| ndb_log_empty_update	| OFF	|
| ndb_log_exclusive_reads	| OFF	|
| ndb_log_orig	| OFF	|
| ndb_log_transaction_id	| OFF	|
| ndb_log_update_as_write	| ON	|
| ndb_log_update_minimal	| OFF	|
| ndb_log_updated_only	| ON	|
| ndb_metadata_check	| ON	|
| ndb_metadata_check_interval	| 60	|
| ndb_metadata_sync	| OFF	|
| ndb_mgmd_host	| 127.0.0.1	|
| ndb_nodeid	| 0	|
| ndb_optimization_delay	| 10	|
| ndb_optimized_node_selection	| 3	|
| ndb_read_backup	| ON	|
| ndb_recv_thread_activation_threshold	| 8	|
| ndb_recv_thread_cpu_mask	|	|
| ndb_report_thresh_binlog_epoch_slip	| 10	|
| ndb_report_thresh_binlog_mem_usage	| 10	|
| ndb_row_checksum	| 1	|
| ndb_schema_dist_lock_wait_timeout	| 30	|
| ndb_schema_dist_timeout	| 120	|
| ndb_schema_dist_upgrade_allowed	| ON	|
| ndb_show_foreign_key_mock_tables	| OFF	|
| ndb_slave_conflict_role	| NONE	|
| ndb_table_no_logging	| OFF	|
| ndb_table_temporary	| OFF	|
| ndb_use_copying_alter_table	| OFF	|
| ndb_use_exact_count	| OFF	|
| ndb_use_transactions	| ON	|
| ndb_version	| 524308	|
| ndb_version_string	| ndb-8.0.20	|
| ndb_wait_connected	| 30	|
| ndb_wait_setup	| 30	|
| ndbinfo_database	| ndbinfo	|
| ndbinfo_max_bytes	| 0	|
| ndbinfo_max_rows	| 10	|
| ndbinfo_offline	| OFF	|
| ndbinfo_show_hidden	| OFF	|
| ndbinfo_table_prefix	| ndb$	|
| ndbinfo_version	| 524308	|
+--------------------------------------	+---------------------------------------	+

      与该SHOW VARIABLES语句不同，可以选择单个列。例如：

      mysql> SELECT VARIABLE_VALUE 
->   FROM performance_schema.global_variables
->   WHERE VARIABLE_NAME = 'ndb_force_send';
+----------------	+
| VARIABLE_VALUE	|
+----------------	+
| ON	|
+----------------	+

      这里显示了一个更有用的查询：

      mysql> SELECT VARIABLE_NAME AS Name, VARIABLE_VALUE AS Value
>   FROM performance_schema.global_variables
>   WHERE VARIABLE_NAME
>     IN ('version', 'ndb_version',
>       'ndb_version_string', 'ndbinfo_version');

+--------------------	+----------------	+
| Name	| Value	|
+--------------------	+----------------	+
| ndb_version	| 524308	|
| ndb_version_string	| ndb-8.0.20	|
| ndbinfo_version	| 524308	|
| version	| 8.0.20-cluster	|
+--------------------	+----------------	+
4 rows in set (0.00 sec)

      有关更多信息，请参见“性能模式状态变量表”和“服务器系统变量”。

    • SHOW STATUS LIKE 'NDB%'

      此语句一目了然，显示MySQL服务器是否充当群集SQL节点，如果是，它将提供MySQL服务器的群集节点ID，与其连接的群集管理服务器的主机名和端口，以及集群中数据节点的数量，如下所示：

      mysql> SHOW STATUS LIKE 'NDB%';
+----------------------------------------------	+-------------------------------	+
| Variable_name	| Value	|
+----------------------------------------------	+-------------------------------	+
| Ndb_metadata_detected_count	| 0	|
| Ndb_cluster_node_id	| 100	|
| Ndb_config_from_host	| 127.0.0.1	|
| Ndb_config_from_port	| 1186	|
| Ndb_number_of_data_nodes	| 2	|
| Ndb_number_of_ready_data_nodes	| 2	|
| Ndb_connect_count	| 0	|
| Ndb_execute_count	| 0	|
| Ndb_scan_count	| 0	|
| Ndb_pruned_scan_count	| 0	|
| Ndb_schema_locks_count	| 0	|
| Ndb_api_wait_exec_complete_count_session	| 0	|
| Ndb_api_wait_scan_result_count_session	| 0	|
| Ndb_api_wait_meta_request_count_session	| 1	|
| Ndb_api_wait_nanos_count_session	| 163446	|
| Ndb_api_bytes_sent_count_session	| 60	|
| Ndb_api_bytes_received_count_session	| 28	|
| Ndb_api_trans_start_count_session	| 0	|
| Ndb_api_trans_commit_count_session	| 0	|
| Ndb_api_trans_abort_count_session	| 0	|
| Ndb_api_trans_close_count_session	| 0	|
| Ndb_api_pk_op_count_session	| 0	|
| Ndb_api_uk_op_count_session	| 0	|
| Ndb_api_table_scan_count_session	| 0	|
| Ndb_api_range_scan_count_session	| 0	|
| Ndb_api_pruned_scan_count_session	| 0	|
| Ndb_api_scan_batch_count_session	| 0	|
| Ndb_api_read_row_count_session	| 0	|
| Ndb_api_trans_local_read_row_count_session	| 0	|
| Ndb_api_adaptive_send_forced_count_session	| 0	|
| Ndb_api_adaptive_send_unforced_count_session	| 0	|
| Ndb_api_adaptive_send_deferred_count_session	| 0	|
| Ndb_trans_hint_count_session	| 0	|
| Ndb_sorted_scan_count	| 0	|
| Ndb_pushed_queries_defined	| 0	|
| Ndb_pushed_queries_dropped	| 0	|
| Ndb_pushed_queries_executed	| 0	|
| Ndb_pushed_reads	| 0	|
| Ndb_last_commit_epoch_server	| 37632503447571	|
| Ndb_last_commit_epoch_session	| 0	|
| Ndb_system_name	| MC_20191126162038	|
| Ndb_api_event_data_count_injector	| 0	|
| Ndb_api_event_nondata_count_injector	| 0	|
| Ndb_api_event_bytes_count_injector	| 0	|
| Ndb_api_wait_exec_complete_count_slave	| 0	|
| Ndb_api_wait_scan_result_count_slave	| 0	|
| Ndb_api_wait_meta_request_count_slave	| 0	|
| Ndb_api_wait_nanos_count_slave	| 0	|
| Ndb_api_bytes_sent_count_slave	| 0	|
| Ndb_api_bytes_received_count_slave	| 0	|
| Ndb_api_trans_start_count_slave	| 0	|
| Ndb_api_trans_commit_count_slave	| 0	|
| Ndb_api_trans_abort_count_slave	| 0	|
| Ndb_api_trans_close_count_slave	| 0	|
| Ndb_api_pk_op_count_slave	| 0	|
| Ndb_api_uk_op_count_slave	| 0	|
| Ndb_api_table_scan_count_slave	| 0	|
| Ndb_api_range_scan_count_slave	| 0	|
| Ndb_api_pruned_scan_count_slave	| 0	|
| Ndb_api_scan_batch_count_slave	| 0	|
| Ndb_api_read_row_count_slave	| 0	|
| Ndb_api_trans_local_read_row_count_slave	| 0	|
| Ndb_api_adaptive_send_forced_count_slave	| 0	|
| Ndb_api_adaptive_send_unforced_count_slave	| 0	|
| Ndb_api_adaptive_send_deferred_count_slave	| 0	|
| Ndb_slave_max_replicated_epoch	| 0	|
| Ndb_api_wait_exec_complete_count	| 4	|
| Ndb_api_wait_scan_result_count	| 7	|
| Ndb_api_wait_meta_request_count	| 172	|
| Ndb_api_wait_nanos_count	| 1083548094028	|
| Ndb_api_bytes_sent_count	| 4640	|
| Ndb_api_bytes_received_count	| 109356	|
| Ndb_api_trans_start_count	| 4	|
| Ndb_api_trans_commit_count	| 1	|
| Ndb_api_trans_abort_count	| 1	|
| Ndb_api_trans_close_count	| 4	|
| Ndb_api_pk_op_count	| 2	|
| Ndb_api_uk_op_count	| 0	|
| Ndb_api_table_scan_count	| 1	|
| Ndb_api_range_scan_count	| 1	|
| Ndb_api_pruned_scan_count	| 0	|
| Ndb_api_scan_batch_count	| 1	|
| Ndb_api_read_row_count	| 3	|
| Ndb_api_trans_local_read_row_count	| 2	|
| Ndb_api_adaptive_send_forced_count	| 1	|
| Ndb_api_adaptive_send_unforced_count	| 5	|
| Ndb_api_adaptive_send_deferred_count	| 0	|
| Ndb_api_event_data_count	| 0	|
| Ndb_api_event_nondata_count	| 0	|
| Ndb_api_event_bytes_count	| 0	|
| Ndb_metadata_blacklist_size	| 0	|
| Ndb_metadata_synced_count	| 0	|
| Ndb_conflict_fn_max	| 0	|
| Ndb_conflict_fn_old	| 0	|
| Ndb_conflict_fn_max_del_win	| 0	|
| Ndb_conflict_fn_epoch	| 0	|
| Ndb_conflict_fn_epoch_trans	| 0	|
| Ndb_conflict_fn_epoch2	| 0	|
| Ndb_conflict_fn_epoch2_trans	| 0	|
| Ndb_conflict_trans_row_conflict_count	| 0	|
| Ndb_conflict_trans_row_reject_count	| 0	|
| Ndb_conflict_trans_reject_count	| 0	|
| Ndb_conflict_trans_detect_iter_count	| 0	|
| Ndb_conflict_trans_conflict_commit_count	| 0	|
| Ndb_conflict_epoch_delete_delete_count	| 0	|
| Ndb_conflict_reflected_op_prepare_count	| 0	|
| Ndb_conflict_reflected_op_discard_count	| 0	|
| Ndb_conflict_refresh_op_count	| 0	|
| Ndb_conflict_last_conflict_epoch	| 0	|
| Ndb_conflict_last_stable_epoch	| 0	|
| Ndb_index_stat_status                        | allow:1,enable:1,busy:0,
loop:1000,list:(new:0,update:0,read:0,idle:0,check:0,delete:0,error:0,total:0),
analyze:(queue:0,wait:0),stats:(nostats:0,wait:0),total:(analyze:(all:0,error:0),
query:(all:0,nostats:0,error:0),event:(act:0,skip:0,miss:0),cache:(refresh:0,
clean:0,pinned:0,drop:0,evict:0)),cache:(query:0,clean:0,drop:0,evict:0,
usedpct:0.00,highpct:0.00)                                                     |
| Ndb_index_stat_cache_query	| 0	|
| Ndb_index_stat_cache_clean	| 0	|
+----------------------------------------------	+-------------------------------	+

      如果MySQL服务器是使用群集支持构建的，但未连接到群集，则此语句输出中的每一行的Value列均包含零或空字符串。

      另请参见“ SHOW STATUS语句”。

    • SELECT * FROM performance_schema.global_status WHERE VARIABLE_NAME LIKE 'NDB%'

      该语句提供的输出与上SHOW STATUS一项中讨论的语句类似。与的情况不同SHOW STATUS，可以使用SELECT语句在SQL中提取值以用于监视和自动化目的的脚本中。

      有关更多信息，请参见“性能模式状态变量表”。

    • SELECT * FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'NDB%'

      该语句从INFORMATION_SCHEMA.PLUGINS表中显示有关与NDB Cluster关联的插件的信息，例如版本，作者和许可证，如下所示：

      mysql> SELECT * FROM INFORMATION_SCHEMA.PLUGINS
>     WHERE PLUGIN_NAME LIKE 'NDB%'\G
*************************** 1. row***************************
           PLUGIN_NAME: ndbcluster
        PLUGIN_VERSION: 1.0
         PLUGIN_STATUS: ACTIVE
           PLUGIN_TYPE: STORAGE ENGINE
   PLUGIN_TYPE_VERSION: 80020.0
        PLUGIN_LIBRARY: NULL
PLUGIN_LIBRARY_VERSION: NULL
         PLUGIN_AUTHOR: Oracle Corporation
    PLUGIN_DESCRIPTION: Clustered, fault-tolerant tables
        PLUGIN_LICENSE: GPL
           LOAD_OPTION: ON
*************************** 2. row***************************
           PLUGIN_NAME: ndbinfo
        PLUGIN_VERSION: 0.1
         PLUGIN_STATUS: ACTIVE
           PLUGIN_TYPE: STORAGE ENGINE
   PLUGIN_TYPE_VERSION: 80020.0
        PLUGIN_LIBRARY: NULL
PLUGIN_LIBRARY_VERSION: NULL
         PLUGIN_AUTHOR: Oracle Corporation
    PLUGIN_DESCRIPTION: MySQL Cluster system information storage engine
        PLUGIN_LICENSE: GPL
           LOAD_OPTION: ON
*************************** 3. row***************************
           PLUGIN_NAME: ndb_transid_mysql_connection_map
        PLUGIN_VERSION: 0.1
         PLUGIN_STATUS: ACTIVE
           PLUGIN_TYPE: INFORMATION SCHEMA
   PLUGIN_TYPE_VERSION: 80020.0
        PLUGIN_LIBRARY: NULL
PLUGIN_LIBRARY_VERSION: NULL
         PLUGIN_AUTHOR: Oracle Corporation
    PLUGIN_DESCRIPTION: Map between MySQL connection ID and NDB transaction ID
        PLUGIN_LICENSE: GPL
           LOAD_OPTION: ON

      您还可以使用该SHOW PLUGINS语句显示此信息，但是该语句的输出不能轻松地进行过滤。另请参见“ MySQL插件API”，它描述了在何处以及如何获取PLUGINS表中的信息。

    您还可以查询ndbinfo信息数据库中的表，以获取有关许多NDB Cluster操作的实时数据。请参见“ ndbinfo：NDB群集信息数据库”。

    上篇：NDB群集单用户模式

    下篇：ndbinfo：NDB群集信息数据库