MySQL 5.6参考手册：包含集群NDB 7.3指南

需积分: 9 162 浏览量更新于2024-07-23 收藏 40.4MB PDF 举报

"MySQL 5.6产品文档是MySQL官方提供的一份详尽的参考资料，涵盖了MySQL 5.6版本从5.6.20到5.6.20的全部内容，以及基于NDB CLUSTER 7.3的MySQL Cluster版本至5.6.17-ndb-7.3.6的相关信息。这份文档不仅描述了MySQL 5.6的核心功能，还包含了可能在不同版本中未包含的特定特性。对于具体包含在您所拥有的MySQL 5.6版本中的特性，应参考您的许可证协议或联系Oracle销售代表以获取详细信息。在MySQL 5.6中，用户可以了解到一系列增强的功能，如性能优化、新的SQL语法、更强大的备份和恢复工具，以及对高可用性和可扩展性的改进。例如，InnoDB存储引擎的改进可能包括更高的并发性、更快的全文搜索、以及更好的事务处理能力。此外，MySQL 5.6还可能引入了更高效的复制技术，如半同步复制，以确保数据的一致性。对于管理员来说，MySQL 5.6参考手册提供了服务器管理、安装与升级、安全配置等方面的指导。MySQL Yum Repositories的信息则帮助Linux用户通过Yum包管理器轻松安装和更新MySQL。MySQL Workbench是一款集成开发环境，用于数据库设计、管理和维护，对于开发者和管理员都非常有用。对于开发者和功能实现，文档详细阐述了MySQL的各种函数和操作，包括但不限于数据类型、SQL查询语言、触发器、存储过程、视图等。此外，连接器和APIs章节介绍了如何使用各种编程语言（如Java、Python、PHP等）与MySQL进行交互，这对于开发数据库驱动的应用程序至关重要。在高可用性和可扩展性方面，MySQL 5.6提供了集群解决方案，如MySQL Cluster，它允许数据在多台服务器之间分散，以提高系统的可用性和性能。HA/Scalability Guide则详细介绍了如何设置和管理这些集群，以及如何在需要时扩展数据库系统。 MySQL 5.6产品文档是一份全面的指南，无论你是初次接触MySQL的新手，还是经验丰富的数据库管理员，都能从中找到所需的信息，以充分利用MySQL 5.6的强大功能。同时，它也是开发者和企业用户的重要参考资料，帮助他们构建和优化高效、可靠的数据库解决方案。"

MySQL 5.6 Reference Manual
xvi
4.12 ndb_drop_table — Drop an NDB Table ...................................................  2494
4.13 ndb_error_reporter — NDB Error-Reporting Utility .................................  2495
4.14 ndb_index_stat — NDB Index Statistics Utility .........................................  2496
4.15 ndb_print_backup_file — Print NDB Backup File Contents .................... 2501
4.16 ndb_print_schema_file — Print NDB Schema File Contents ................... 2501
4.17 ndb_print_sys_file — Print NDB System File Contents .........................  2502
4.18 ndbd_redo_log_reader — Check and Print Content of Cluster Redo Log ... 2502
4.19 ndb_restore — Restore a MySQL Cluster Backup ..................................... 2503
4.20 ndb_select_all — Print Rows from an NDB Table ...................................  2516
4.21 ndb_select_count — Print Row Counts for NDB Tables ...........................  2519
4.22 ndb_setup.py — Start browser-based Auto-Installer for MySQL Cluster ......  2519
4.23 ndb_show_tables — Display List of NDB Tables .......................................  2523
4.24 ndb_size.pl — NDBCLUSTER Size Requirement Estimator ......................  2524
4.25 ndb_waiter — Wait for MySQL Cluster to Reach a Given Status ................. 2527
4.26 Options Common to MySQL Cluster Programs — Options Common to MySQL
Cluster Programs ....................................................................................................  2529
5 Management of MySQL Cluster ...............................................................................  2532
5.1 Summary of MySQL Cluster Start Phases ......................................................  2533
5.2 Commands in the MySQL Cluster Management Client ....................................  2535
5.3 Online Backup of MySQL Cluster ..................................................................  2538
5.4 MySQL Server Usage for MySQL Cluster ....................................................... 2543
5.5 Performing a Rolling Restart of a MySQL Cluster ...........................................  2544
5.6 Event Reports Generated in MySQL Cluster ................................................... 2547
5.7 MySQL Cluster Log Messages ......................................................................  2557
5.8 MySQL Cluster Single User Mode .................................................................  2568
5.9 Quick Reference: MySQL Cluster SQL Statements .........................................  2569
5.10 The ndbinfo MySQL Cluster Information Database .....................................  2571
5.11 MySQL Cluster Security Issues .................................................................... 2591
5.12 MySQL Cluster Disk Data Tables ................................................................. 2598
5.13 Adding MySQL Cluster Data Nodes Online ................................................... 2604
5.14 Distributed MySQL Privileges for MySQL Cluster ..........................................  2615
5.15 NDB API Statistics Counters and Variables ..................................................  2618
6 MySQL Cluster Replication ......................................................................................  2629
6.1 MySQL Cluster Replication: Abbreviations and Symbols .................................. 2630
6.2 General Requirements for MySQL Cluster Replication ....................................  2631
6.3 Known Issues in MySQL Cluster Replication ..................................................  2632
6.4 MySQL Cluster Replication Schema and Tables .............................................  2638
6.5 Preparing the MySQL Cluster for Replication .................................................. 2641
6.6 Starting MySQL Cluster Replication (Single Replication Channel) ....................  2642
6.7 Using Two Replication Channels for MySQL Cluster Replication ......................  2644
6.8 Implementing Failover with MySQL Cluster Replication ...................................  2645
6.9 MySQL Cluster Backups With MySQL Cluster Replication ...............................  2646
6.10 MySQL Cluster Replication: Multi-Master and Circular Replication .................. 2652
6.11 MySQL Cluster Replication Conflict Resolution .............................................  2656
7 MySQL Cluster Release Notes ................................................................................. 2665
Partitioning  .........................................................................................................................   2667
1 Overview of Partitioning in MySQL ...........................................................................  2669
2 Partitioning Types ....................................................................................................  2671
2.1 RANGE Partitioning ........................................................................................  2673
2.2 LIST Partitioning ..........................................................................................  2677
2.3 COLUMNS Partitioning ....................................................................................  2680
2.4 HASH Partitioning ..........................................................................................  2687
2.5 KEY Partitioning ............................................................................................  2690
2.6 Subpartitioning ..............................................................................................  2692
2.7 How MySQL Partitioning Handles NULL .........................................................  2695
3 Partition Management ..............................................................................................  2699
3.1 Management of RANGE and LIST Partitions ...................................................  2700
3.2 Management of HASH and KEY Partitions .......................................................  2706

MySQL 5.6 Reference Manual
xvii
3.3 Exchanging Partitions and Subpartitions with Tables ....................................... 2707
3.4 Maintenance of Partitions ..............................................................................  2712
3.5 Obtaining Information About Partitions ...........................................................  2713
4 Partition Pruning ......................................................................................................  2715
5 Partition Selection .................................................................................................... 2718
6 Restrictions and Limitations on Partitioning ...............................................................  2724
6.1 Partitioning Keys, Primary Keys, and Unique Keys .......................................... 2730
6.2 Partitioning Limitations Relating to Storage Engines ........................................ 2733
6.3 Partitioning Limitations Relating to Functions .................................................. 2734
6.4 Partitioning and Locking ................................................................................  2735
Stored Programs and Views ...............................................................................................  2737
1 Defining Stored Programs ........................................................................................  2738
2 Using Stored Routines (Procedures and Functions) ...................................................  2739
2.1 Stored Routine Syntax ..................................................................................  2739
2.2 Stored Routines and MySQL Privileges .......................................................... 2740
2.3 Stored Routine Metadata ............................................................................... 2741
2.4 Stored Procedures, Functions, Triggers, and LAST_INSERT_ID() .................. 2741
3 Using Triggers .........................................................................................................  2741
3.1 Trigger Syntax and Examples ........................................................................ 2742
3.2 Trigger Metadata ..........................................................................................  2745
4 Using the Event Scheduler ....................................................................................... 2745
4.1 Event Scheduler Overview ............................................................................  2746
4.2 Event Scheduler Configuration ....................................................................... 2747
4.3 Event Syntax ................................................................................................  2749
4.4 Event Metadata ............................................................................................  2749
4.5 Event Scheduler Status ................................................................................. 2750
4.6 The Event Scheduler and MySQL Privileges ..................................................  2750
5 Using Views  ............................................................................................................  2753
5.1 View Syntax .................................................................................................  2753
5.2 View Processing Algorithms ..........................................................................  2754
5.3 Updatable and Insertable Views ....................................................................  2755
5.4 View Metadata .............................................................................................. 2757
6 Access Control for Stored Programs and Views ........................................................  2757
7 Binary Logging of Stored Programs ..........................................................................  2759
INFORMATION_SCHEMA Tables ........................................................................................... 2765
1 The INFORMATION_SCHEMA CHARACTER_SETS Table ............................................. 2768
2 The INFORMATION_SCHEMA COLLATIONS Table ....................................................  2768
3 The INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY
Table  ..............................................................................................................................  2769
4 The INFORMATION_SCHEMA COLUMNS Table ..........................................................  2769
5 The INFORMATION_SCHEMA COLUMN_PRIVILEGES Table ....................................... 2770
6 The INFORMATION_SCHEMA ENGINES Table ..........................................................  2771
7 The INFORMATION_SCHEMA  EVENTS Table ............................................................  2771
8 The INFORMATION_SCHEMA  FILES Table ..............................................................  2775
9 The INFORMATION_SCHEMA GLOBAL_STATUS and SESSION_STATUS Tables ..........  2776
10 The INFORMATION_SCHEMA GLOBAL_VARIABLES and SESSION_VARIABLES
Tables  .............................................................................................................................  2776
11 The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table ....................................... 2776
12 The INFORMATION_SCHEMA OPTIMIZER_TRACE Table ......................................... 2777
13 The INFORMATION_SCHEMA PARAMETERS Table ................................................... 2778
14 The INFORMATION_SCHEMA PARTITIONS Table ................................................... 2778
15 The INFORMATION_SCHEMA  PLUGINS Table ......................................................... 2781
16 The INFORMATION_SCHEMA PROCESSLIST Table ................................................. 2782
17 The INFORMATION_SCHEMA PROFILING Table ..................................................... 2783
18 The INFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS Table .........................  2784
19 The INFORMATION_SCHEMA  ROUTINES Table ....................................................... 2784
20 The INFORMATION_SCHEMA  SCHEMATA Table ....................................................... 2786
21 The INFORMATION_SCHEMA SCHEMA_PRIVILEGES Table ..................................... 2786

MySQL 5.6 Reference Manual
xviii
22 The INFORMATION_SCHEMA STATISTICS Table ................................................... 2787
23 The INFORMATION_SCHEMA TABLES Table ..........................................................  2787
24 The INFORMATION_SCHEMA TABLESPACES Table ................................................. 2789
25 The INFORMATION_SCHEMA TABLE_CONSTRAINTS Table ..................................... 2789
26 The INFORMATION_SCHEMA TABLE_PRIVILEGES Table ....................................... 2789
27 The INFORMATION_SCHEMA  TRIGGERS Table ....................................................... 2790
28 The INFORMATION_SCHEMA USER_PRIVILEGES Table ......................................... 2792
29 The INFORMATION_SCHEMA  VIEWS Table ............................................................  2792
30 INFORMATION_SCHEMA Tables for InnoDB ............................................................  2793
30.1 The INFORMATION_SCHEMA INNODB_CMP and INNODB_CMP_RESET
Tables .....................................................................................................................  2794
30.2 The INFORMATION_SCHEMA INNODB_CMP_PER_INDEX and
INNODB_CMP_PER_INDEX_RESET Tables ................................................................ 2794
30.3 The INFORMATION_SCHEMA INNODB_CMPMEM and INNODB_CMPMEM_RESET
Tables .....................................................................................................................  2795
30.4 The INFORMATION_SCHEMA INNODB_TRX Table ........................................  2796
30.5 The INFORMATION_SCHEMA INNODB_LOCKS Table ....................................  2798
30.6 The INFORMATION_SCHEMA INNODB_LOCK_WAITS Table ..........................  2798
30.7 The INFORMATION_SCHEMA INNODB_SYS_TABLES Table ..........................  2799
30.8 The INFORMATION_SCHEMA INNODB_SYS_INDEXES Table ........................  2799
30.9 The INFORMATION_SCHEMA INNODB_SYS_COLUMNS Table ........................  2800
30.10 The INFORMATION_SCHEMA INNODB_SYS_FIELDS Table ........................  2801
30.11 The INFORMATION_SCHEMA INNODB_SYS_FOREIGN Table ......................  2801
30.12 The INFORMATION_SCHEMA INNODB_SYS_FOREIGN_COLS Table ............. 2801
30.13 The INFORMATION_SCHEMA INNODB_SYS_TABLESTATS View .................. 2802
30.14 The INFORMATION_SCHEMA INNODB_SYS_DATAFILES Table ................... 2803
30.15 The INFORMATION_SCHEMA INNODB_SYS_TABLESPACES Table ............... 2803
30.16 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE Table ......................  2803
30.17 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE_LRU Table ............... 2805
30.18 The INFORMATION_SCHEMA INNODB_BUFFER_POOL_STATS Table ........... 2805
30.19 The INFORMATION_SCHEMA INNODB_METRICS Table ..............................  2806
30.20 The INFORMATION_SCHEMA INNODB_FT_CONFIG Table ..........................  2812
30.21 The INFORMATION_SCHEMA INNODB_FT_DEFAULT_STOPWORD Table ....... 2813
30.22 The INFORMATION_SCHEMA INNODB_FT_INDEX_TABLE Table ................. 2813
30.23 The INFORMATION_SCHEMA INNODB_FT_INDEX_CACHE Table ................. 2814
30.24 The INFORMATION_SCHEMA INNODB_FT_DELETED Table ........................  2814
30.25 The INFORMATION_SCHEMA INNODB_FT_BEING_DELETED Table ............. 2815
31 Thread Pool INFORMATION_SCHEMA Tables ........................................................... 2815
31.1 The INFORMATION_SCHEMA TP_THREAD_STATE Table ..............................  2816
31.2 The INFORMATION_SCHEMA TP_THREAD_GROUP_STATE Table ..................  2816
31.3 The INFORMATION_SCHEMA TP_THREAD_GROUP_STATS Table ..................  2818
32 Extensions to SHOW Statements .............................................................................. 2819
MySQL Performance Schema .............................................................................................  2823
1 Performance Schema Quick Start ............................................................................. 2824
2 Performance Schema Configuration .......................................................................... 2830
2.1 Performance Schema Build Configuration ......................................................  2830
2.2 Performance Schema Startup Configuration ...................................................  2831
2.3 Performance Schema Runtime Configuration .................................................  2834
3 Performance Schema Queries .................................................................................. 2850
4 Performance Schema Instrument Naming Conventions .............................................. 2850
5 Performance Schema Status Monitoring ...................................................................  2853
6 Performance Schema Atom and Molecule Events .....................................................  2856
7 Performance Schema Statement Digests ..................................................................  2856
8 Performance Schema General Table Characteristics .................................................  2858
9 Performance Schema Table Descriptions .................................................................. 2858
9.1 Performance Schema Table Index .................................................................  2859
9.2 Performance Schema Setup Tables ............................................................... 2860
9.3 Performance Schema Instance Tables ...........................................................  2864

MySQL 5.6 Reference Manual
xix
9.4 Performance Schema Wait Event Tables .......................................................  2869
9.5 Performance Schema Stage Event Tables .....................................................  2873
9.6 Performance Schema Statement Event Tables ...............................................  2876
9.7 Performance Schema Connection Tables ....................................................... 2882
9.8 Performance Schema Connection Attribute Tables .........................................  2884
9.9 Performance Schema Summary Tables .........................................................  2885
9.10 Performance Schema Miscellaneous Tables ................................................. 2897
10 Performance Schema Option and Variable Reference .............................................. 2904
11 Performance Schema Command Options ................................................................ 2906
12 Performance Schema System Variables .................................................................  2907
13 Performance Schema Status Variables ...................................................................  2919
14 Performance Schema and Plugins .......................................................................... 2921
15 Using the Performance Schema to Diagnose Problems ...........................................  2922
Connectors and APIs .......................................................................................................... 2925
1 MySQL Connector/ODBC ......................................................................................... 2928
2 MySQL Connector/Net .............................................................................................  2929
3 MySQL Connector/J ................................................................................................. 2929
4 MySQL Connector/C++ ............................................................................................  2929
5 MySQL Connector/C ................................................................................................ 2929
6 MySQL Connector/Python ........................................................................................  2929
7 libmysqld, the Embedded MySQL Server Library .......................................................  2929
7.1 Compiling Programs with libmysqld ............................................................ 2930
7.2 Restrictions When Using the Embedded MySQL Server ..................................  2930
7.3 Options with the Embedded Server ................................................................ 2931
7.4 Embedded Server Examples .........................................................................  2931
8 MySQL C API  .........................................................................................................  2934
8.1 MySQL C API Implementations .....................................................................  2935
8.2 Simultaneous MySQL Server and MySQL Connector/C Installations ................  2936
8.3 Example C API Client Programs .................................................................... 2937
8.4 Building and Running C API Client Programs .................................................  2937
8.5 C API Data Structures ..................................................................................  2941
8.6 C API Function Overview ..............................................................................  2946
8.7 C API Function Descriptions .......................................................................... 2950
8.8 C API Prepared Statements ..........................................................................  3002
8.9 C API Prepared Statement Data Structures .................................................... 3002
8.10 C API Prepared Statement Function Overview .............................................. 3008
8.11 C API Prepared Statement Function Descriptions .........................................  3011
8.12 C API Threaded Function Descriptions ......................................................... 3033
8.13 C API Embedded Server Function Descriptions ............................................  3034
8.14 C API Client Plugin Functions ...................................................................... 3035
8.15 Common Questions and Problems When Using the C API ............................. 3038
8.16 Controlling Automatic Reconnection Behavior ...............................................  3040
8.17 C API Support for Multiple Statement Execution ...........................................  3041
8.18 C API Prepared Statement Problems ...........................................................  3043
8.19 C API Prepared Statement Handling of Date and Time Values ....................... 3043
8.20 C API Support for Prepared CALL Statements ..............................................  3045
9 MySQL PHP API .....................................................................................................  3048
10 MySQL Perl API ....................................................................................................  3049
11 MySQL Python API ................................................................................................ 3049
12 MySQL Ruby APIs ................................................................................................. 3050
12.1 The MySQL/Ruby API .................................................................................  3050
12.2 The Ruby/MySQL API .................................................................................  3050
13 MySQL Tcl API ...................................................................................................... 3050
14 MySQL Eiffel Wrapper ...........................................................................................  3050
Extending MySQL ..............................................................................................................  3051
1 MySQL Internals ...................................................................................................... 3051
1.1 MySQL Threads ............................................................................................ 3051
1.2 The MySQL Test Suite .................................................................................. 3052

MySQL 5.6 Reference Manual
xx
24.2 The MySQL Plugin API ............................................................................................ 3053
24.2.1 Plugin API Characteristics .............................................................................  3053
24.2.2 Plugin API Components ................................................................................  3054
24.2.3 Types of Plugins ...........................................................................................  3055
24.2.4 Writing Plugins .............................................................................................. 3058
24.2.5 MySQL Services for Plugins ..........................................................................  3100
24.3 Adding New Functions to MySQL .............................................................................  3102
24.3.1 Features of the User-Defined Function Interface .............................................  3103
24.3.2 Adding a New User-Defined Function ............................................................  3103
24.3.3 Adding a New Native Function ......................................................................  3112
24.4 Debugging and Porting MySQL ................................................................................  3114
24.4.1 Debugging a MySQL Server .......................................................................... 3114
24.4.2 Debugging a MySQL Client ...........................................................................  3121
24.4.3 The DBUG Package .....................................................................................  3121
25 MySQL Enterprise Edition ................................................................................................... 3125
25.1 MySQL Enterprise Monitor .......................................................................................  3125
25.2 MySQL Enterprise Backup .......................................................................................  3126
25.3 MySQL Enterprise Security ......................................................................................  3126
25.4 MySQL Enterprise Audit ........................................................................................... 3127
25.5 MySQL Enterprise Thread Pool ................................................................................ 3127
26 MySQL Workbench ............................................................................................................  3129
27 MySQL for Excel  ................................................................................................................  3131
27.1 Installing and Configuring ......................................................................................... 3131
27.2 Edit MySQL Data in Excel .......................................................................................  3133
27.3 Import MySQL Data into Excel .................................................................................  3135
27.4 Append Excel Data into MySQL ...............................................................................  3138
27.5 Export Excel Data into MySQL .................................................................................  3141
27.6 What Is New In MySQL for Excel .............................................................................  3144
27.7 MySQL for Excel FAQ .............................................................................................  3145
A Licenses for Third-Party Components ...................................................................................  3147
A.1 ANTLR 3 License ...................................................................................................... 3150
A.2 Artistic License (Perl) 1.0 ..........................................................................................  3151
A.3 Boost Library License ................................................................................................ 3153
A.4 dtoa.c License ........................................................................................................ 3153
A.5 Editline Library (libedit) License ............................................................................  3153
A.6 Editline Library (libedit) License ............................................................................  3156
A.7 Expect.pm License .................................................................................................. 3158
A.8 Facebook Fast Checksum Patch License ...................................................................  3165
A.9 Facebook Patches License ........................................................................................  3166
A.10 FindGTest.cmake License .................................................................................... 3166
A.11 Fred Fish's Dbug Library License ............................................................................. 3167
A.12 getarg License ...................................................................................................... 3168
A.13 GLib License (for MySQL Proxy) .............................................................................. 3168
A.14 GNU General Public License Version 2.0, June 1991 ................................................ 3169
A.15 GNU General Public License Version 3.0, 29 June 2007 and GCC Runtime Library
Exception Version 3.1, 31 March 2009 .............................................................................  3174
A.16 GNU Lesser General Public License Version 2.1, February 1999 ...............................  3185
A.17 GNU Libtool License ...............................................................................................  3192
A.18 GNU Readline License ............................................................................................  3193
A.19 GNU Standard C++ Library (libstdc++) License ......................................................... 3193
A.20 Google Controlling Master Thread I/O Rate Patch License ........................................  3195
A.21 Google Perftools (TCMalloc utility) License ...............................................................  3195
A.22 Google SMP Patch License .....................................................................................  3196
A.23 lib_sql.cc License .............................................................................................. 3196
A.24 Libaio License .........................................................................................................  3197
A.25 libevent License .................................................................................................. 3197
A.26 Libiconv License ...................................................................................................... 3199
A.27 libintl License .................................................................................................... 3199