MySQL 5.7官方手册：NDB 7.5集群版特性详解

需积分: 8 170 浏览量更新于2024-07-17 收藏 33.57MB PDF 举报

MySQL 5.7 Reference Manual 是一个全面的文档，涵盖了 MySQL 数据库管理系统的关键特性和功能。该手册特别关注于 MySQL 5.7 的不同版本，从 5.7.0 到 5.7.13，以及基于 NDB 7.5 的 MySQL Cluster 版本，如 5.7.11-ndb-7.5.2。这个手册旨在帮助用户理解 MySQL 5.7 的核心功能，这些功能可能因许可证差异而不一定在所有版

MySQL 5.7 Reference Manual

xvi

15.4.2 CSV Limitations ................................................................................................. 2489

15.5 The ARCHIVE Storage Engine ...................................................................................... 2490

15.6 The BLACKHOLE Storage Engine ................................................................................. 2491

15.7 The MERGE Storage Engine ........................................................................................ 2493

15.7.1 MERGE Table Advantages and Disadvantages ................................................... 2496

15.7.2 MERGE Table Problems .................................................................................... 2497

15.8 The FEDERATED Storage Engine ................................................................................. 2499

15.8.1 FEDERATED Storage Engine Overview .............................................................. 2499

15.8.2 How to Create FEDERATED Tables ................................................................... 2500

15.8.3 FEDERATED Storage Engine Notes and Tips ..................................................... 2503

15.8.4 FEDERATED Storage Engine Resources ............................................................ 2504

15.9 The EXAMPLE Storage Engine ..................................................................................... 2504

15.10 Other Storage Engines ................................................................................................ 2505

15.11 Overview of MySQL Storage Engine Architecture ......................................................... 2505

15.11.1 Pluggable Storage Engine Architecture .............................................................. 2505

15.11.2 The Common Database Server Layer ............................................................... 2506

16 High Availability and Scalability ................................................................................................ 2509

16.1 Using MySQL within an Amazon EC2 Instance .............................................................. 2511

16.1.1 Setting Up MySQL on an EC2 AMI ..................................................................... 2512

16.1.2 EC2 Instance Limitations .................................................................................... 2513

16.1.3 Deploying a MySQL Database Using EC2 ........................................................... 2514

16.2 Using ZFS Replication .................................................................................................. 2516

16.2.1 Using ZFS for File System Replication ................................................................ 2518

16.2.2 Configuring MySQL for ZFS Replication .............................................................. 2519

16.2.3 Handling MySQL Recovery with ZFS .................................................................. 2519

16.3 Using MySQL with memcached ..................................................................................... 2520

16.3.1 Installing memcached ......................................................................................... 2521

16.3.2 Using memcached ............................................................................................. 2522

16.3.3 Developing a memcached Application ................................................................. 2542

16.3.4 Getting memcached Statistics ............................................................................. 2568

16.3.5 memcached FAQ ............................................................................................... 2577

17 Replication .............................................................................................................................. 2581

17.1 Configuring Replication ................................................................................................. 2582

17.1.1 Binary Log File Position Based Replication Configuration Overview ....................... 2583

17.1.2 Setting Up Binary Log File Position Based Replication ......................................... 2583

17.1.3 Replication with Global Transaction Identifiers ..................................................... 2593

17.1.4 MySQL Multi-Source Replication ......................................................................... 2603

17.1.5 Changing Replication Modes on Online Servers .................................................. 2607

17.1.6 Replication and Binary Logging Options and Variables ......................................... 2613

17.1.7 Common Replication Administration Tasks .......................................................... 2705

17.2 Replication Implementation ........................................................................................... 2708

17.2.1 Replication Formats ........................................................................................... 2708

17.2.2 Replication Implementation Details ...................................................................... 2716

17.2.3 Replication Channels ......................................................................................... 2718

17.2.4 Replication Relay and Status Logs ..................................................................... 2721

17.2.5 How Servers Evaluate Replication Filtering Rules ................................................ 2727

17.3 Replication Solutions .................................................................................................... 2733

17.3.1 Using Replication for Backups ............................................................................ 2734

17.3.2 Using Replication with Different Master and Slave Storage Engines ....................... 2737

17.3.3 Using Replication for Scale-Out .......................................................................... 2739

17.3.4 Replicating Different Databases to Different Slaves .............................................. 2740

17.3.5 Improving Replication Performance ..................................................................... 2741

17.3.6 Switching Masters During Failover ...................................................................... 2742

17.3.7 Setting Up Replication to Use Secure Connections .............................................. 2744

MySQL 5.7 Reference Manual

xvii

17.3.8 Semisynchronous Replication ............................................................................. 2746

17.3.9 Delayed Replication ........................................................................................... 2751

17.4 Replication Notes and Tips ........................................................................................... 2752

17.4.1 Replication Features and Issues ......................................................................... 2752

17.4.2 Replication Compatibility Between MySQL Versions ............................................. 2779

17.4.3 Upgrading a Replication Setup ........................................................................... 2780

17.4.4 Troubleshooting Replication ................................................................................ 2782

17.4.5 How to Report Replication Bugs or Problems ...................................................... 2783

18 MySQL Cluster NDB 7.5 .......................................................................................................... 2785

18.1 MySQL Cluster Overview .............................................................................................. 2789

18.1.1 MySQL Cluster Core Concepts ........................................................................... 2790

18.1.2 MySQL Cluster Nodes, Node Groups, Replicas, and Partitions ............................. 2793

18.1.3 MySQL Cluster Hardware, Software, and Networking Requirements ...................... 2796

18.1.4 What is New in MySQL Cluster NDB 7.5 ............................................................. 2797

18.1.5 MySQL Server Using InnoDB Compared with MySQL Cluster ............................... 2800

18.1.6 Known Limitations of MySQL Cluster .................................................................. 2803

18.2 MySQL Cluster Installation ............................................................................................ 2812

18.2.1 The MySQL Cluster Auto-Installer ....................................................................... 2815

18.2.2 Installation of MySQL Cluster on Linux ................................................................ 2830

18.2.3 Installing MySQL Cluster on Windows ................................................................. 2836

18.2.4 Initial Configuration of MySQL Cluster ................................................................. 2845

18.2.5 Initial Startup of MySQL Cluster .......................................................................... 2848

18.2.6 MySQL Cluster Example with Tables and Data .................................................... 2849

18.2.7 Safe Shutdown and Restart of MySQL Cluster .................................................... 2852

18.2.8 Upgrading and Downgrading MySQL Cluster ....................................................... 2853

18.3 Configuration of MySQL Cluster .................................................................................... 2853

18.3.1 Quick Test Setup of MySQL Cluster ................................................................... 2854

18.3.2 Overview of MySQL Cluster Configuration Parameters, Options, and Variables ...... 2856

18.3.3 MySQL Cluster Configuration Files ..................................................................... 2899

18.3.4 Using High-Speed Interconnects with MySQL Cluster ........................................... 3031

18.4 MySQL Cluster Programs ............................................................................................. 3033

18.4.1 ndbd — The MySQL Cluster Data Node Daemon ................................................ 3033

18.4.2 ndbinfo_select_all — Select From ndbinfo Tables ....................................... 3040

18.4.3 ndbmtd — The MySQL Cluster Data Node Daemon (Multi-Threaded) ................... 3042

18.4.4 ndb_mgmd — The MySQL Cluster Management Server Daemon .......................... 3043

18.4.5 ndb_mgm — The MySQL Cluster Management Client ........................................... 3051

18.4.6 ndb_blob_tool — Check and Repair BLOB and TEXT columns of MySQL

Cluster Tables .............................................................................................................. 3053

18.4.7 ndb_config — Extract MySQL Cluster Configuration Information ........................ 3056

18.4.8 ndb_cpcd — Automate Testing for NDB Development ........................................ 3064

18.4.9 ndb_delete_all — Delete All Rows from an NDB Table ................................... 3064

18.4.10 ndb_desc — Describe NDB Tables ................................................................. 3065

18.4.11 ndb_drop_index — Drop Index from an NDB Table ........................................ 3069

18.4.12 ndb_drop_table — Drop an NDB Table ........................................................ 3071

18.4.13 ndb_error_reporter — NDB Error-Reporting Utility ...................................... 3071

18.4.14 ndb_index_stat — NDB Index Statistics Utility .............................................. 3072

18.4.15 ndb_print_backup_file — Print NDB Backup File Contents ......................... 3078

18.4.16 ndb_print_file — Print NDB Disk Data File Contents ................................... 3079

18.4.17 ndb_print_schema_file — Print NDB Schema File Contents ........................ 3079

18.4.18 ndb_print_sys_file — Print NDB System File Contents ............................... 3080

18.4.19 ndbd_redo_log_reader — Check and Print Content of Cluster Redo Log ........ 3080

18.4.20 ndb_restore — Restore a MySQL Cluster Backup .......................................... 3081

18.4.21 ndb_select_all — Print Rows from an NDB Table ........................................ 3095

18.4.22 ndb_select_count — Print Row Counts for NDB Tables ................................ 3098

MySQL 5.7 Reference Manual

xviii

18.4.23 ndb_setup.py — Start browser-based Auto-Installer for MySQL Cluster ............ 3099

18.4.24 ndb_show_tables — Display List of NDB Tables ............................................ 3102

18.4.25 ndb_size.pl — NDBCLUSTER Size Requirement Estimator ........................... 3103

18.4.26 ndb_waiter — Wait for MySQL Cluster to Reach a Given Status ...................... 3106

18.4.27 Options Common to MySQL Cluster Programs — Options Common to MySQL

Cluster Programs ......................................................................................................... 3109

18.5 Management of MySQL Cluster ..................................................................................... 3113

18.5.1 Summary of MySQL Cluster Start Phases ........................................................... 3113

18.5.2 Commands in the MySQL Cluster Management Client ......................................... 3115

18.5.3 Online Backup of MySQL Cluster ....................................................................... 3119

18.5.4 MySQL Server Usage for MySQL Cluster ............................................................ 3124

18.5.5 Performing a Rolling Restart of a MySQL Cluster ................................................. 3126

18.5.6 Event Reports Generated in MySQL Cluster ........................................................ 3128

18.5.7 MySQL Cluster Log Messages ........................................................................... 3139

18.5.8 MySQL Cluster Single User Mode ...................................................................... 3153

18.5.9 Quick Reference: MySQL Cluster SQL Statements .............................................. 3154

18.5.10 The ndbinfo MySQL Cluster Information Database ............................................. 3157

18.5.11 INFORMATION_SCHEMA Tables for MySQL Cluster ......................................... 3189

18.5.12 MySQL Cluster Security Issues ......................................................................... 3190

18.5.13 MySQL Cluster Disk Data Tables ...................................................................... 3197

18.5.14 Adding MySQL Cluster Data Nodes Online ........................................................ 3203

18.5.15 Distributed MySQL Privileges for MySQL Cluster ............................................... 3215

18.5.16 NDB API Statistics Counters and Variables ....................................................... 3218

18.6 MySQL Cluster Replication ........................................................................................... 3230

18.6.1 MySQL Cluster Replication: Abbreviations and Symbols ....................................... 3231

18.6.2 General Requirements for MySQL Cluster Replication .......................................... 3232

18.6.3 Known Issues in MySQL Cluster Replication ....................................................... 3233

18.6.4 MySQL Cluster Replication Schema and Tables .................................................. 3240

18.6.5 Preparing the MySQL Cluster for Replication ....................................................... 3244

18.6.6 Starting MySQL Cluster Replication (Single Replication Channel) .......................... 3245

18.6.7 Using Two Replication Channels for MySQL Cluster Replication ........................... 3247

18.6.8 Implementing Failover with MySQL Cluster Replication ........................................ 3248

18.6.9 MySQL Cluster Backups With MySQL Cluster Replication .................................... 3250

18.6.10 MySQL Cluster Replication: Multi-Master and Circular Replication ....................... 3256

18.6.11 MySQL Cluster Replication Conflict Resolution .................................................. 3260

18.7 MySQL Cluster Release Notes ...................................................................................... 3274

19 Partitioning .............................................................................................................................. 3275

19.1 Overview of Partitioning in MySQL ................................................................................ 3277

19.2 Partitioning Types ......................................................................................................... 3280

19.2.1 RANGE Partitioning ........................................................................................... 3282

19.2.2 LIST Partitioning ................................................................................................ 3286

19.2.3 COLUMNS Partitioning ....................................................................................... 3288

19.2.4 HASH Partitioning .............................................................................................. 3297

19.2.5 KEY Partitioning ................................................................................................ 3300

19.2.6 Subpartitioning ................................................................................................... 3302

19.2.7 How MySQL Partitioning Handles NULL .............................................................. 3305

19.3 Partition Management ................................................................................................... 3309

19.3.1 Management of RANGE and LIST Partitions ....................................................... 3311

19.3.2 Management of HASH and KEY Partitions .......................................................... 3317

19.3.3 Exchanging Partitions and Subpartitions with Tables ............................................ 3318

19.3.4 Maintenance of Partitions ................................................................................... 3326

19.3.5 Obtaining Information About Partitions ................................................................ 3327

19.4 Partition Pruning ........................................................................................................... 3329

19.5 Partition Selection ......................................................................................................... 3333

MySQL 5.7 Reference Manual

xix

19.6 Restrictions and Limitations on Partitioning .................................................................... 3339

19.6.1 Partitioning Keys, Primary Keys, and Unique Keys ............................................... 3345

19.6.2 Partitioning Limitations Relating to Storage Engines ............................................. 3349

19.6.3 Partitioning Limitations Relating to Functions ....................................................... 3350

19.6.4 Partitioning and Locking ..................................................................................... 3351

20 Stored Programs and Views .................................................................................................... 3353

20.1 Defining Stored Programs ............................................................................................. 3354

20.2 Using Stored Routines (Procedures and Functions) ........................................................ 3355

20.2.1 Stored Routine Syntax ....................................................................................... 3356

20.2.2 Stored Routines and MySQL Privileges ............................................................... 3356

20.2.3 Stored Routine Metadata .................................................................................... 3357

20.2.4 Stored Procedures, Functions, Triggers, and LAST_INSERT_ID() ......................... 3357

20.3 Using Triggers .............................................................................................................. 3357

20.3.1 Trigger Syntax and Examples ............................................................................. 3358

20.3.2 Trigger Metadata ............................................................................................... 3362

20.4 Using the Event Scheduler ............................................................................................ 3362

20.4.1 Event Scheduler Overview ................................................................................. 3363

20.4.2 Event Scheduler Configuration ............................................................................ 3364

20.4.3 Event Syntax ..................................................................................................... 3366

20.4.4 Event Metadata ................................................................................................. 3366

20.4.5 Event Scheduler Status ...................................................................................... 3367

20.4.6 The Event Scheduler and MySQL Privileges ........................................................ 3368

20.5 Using Views ................................................................................................................. 3370

20.5.1 View Syntax ...................................................................................................... 3371

20.5.2 View Processing Algorithms ............................................................................... 3371

20.5.3 Updatable and Insertable Views ......................................................................... 3373

20.5.4 The View WITH CHECK OPTION Clause ............................................................ 3376

20.5.5 View Metadata ................................................................................................... 3377

20.6 Access Control for Stored Programs and Views ............................................................. 3377

20.7 Binary Logging of Stored Programs ............................................................................... 3379

21 INFORMATION_SCHEMA Tables ............................................................................................ 3387

21.1 The INFORMATION_SCHEMA CHARACTER_SETS Table ............................................ 3390

21.2 The INFORMATION_SCHEMA COLLATIONS Table ...................................................... 3391

21.3 The INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY Table . 3391

21.4 The INFORMATION_SCHEMA COLUMNS Table ........................................................... 3391

21.5 The INFORMATION_SCHEMA COLUMN_PRIVILEGES Table ........................................ 3393

21.6 The INFORMATION_SCHEMA ENGINES Table ............................................................ 3393

21.7 The INFORMATION_SCHEMA EVENTS Table .............................................................. 3394

21.8 The INFORMATION_SCHEMA FILES Table .................................................................. 3397

21.9 The INFORMATION_SCHEMA GLOBAL_STATUS and SESSION_STATUS Tables ......... 3404

21.10 The INFORMATION_SCHEMA GLOBAL_VARIABLES and SESSION_VARIABLES

Tables ................................................................................................................................. 3405

21.11 The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table ..................................... 3405

21.12 The INFORMATION_SCHEMA ndb_transid_mysql_connection_map Table .................... 3407

21.13 The INFORMATION_SCHEMA OPTIMIZER_TRACE Table ........................................... 3408

21.14 The INFORMATION_SCHEMA PARAMETERS Table ................................................... 3408

21.15 The INFORMATION_SCHEMA PARTITIONS Table ...................................................... 3409

21.16 The INFORMATION_SCHEMA PLUGINS Table ........................................................... 3412

21.17 The INFORMATION_SCHEMA PROCESSLIST Table .................................................. 3413

21.18 The INFORMATION_SCHEMA PROFILING Table ........................................................ 3414

21.19 The INFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS Table .......................... 3415

21.20 The INFORMATION_SCHEMA ROUTINES Table ........................................................ 3415

21.21 The INFORMATION_SCHEMA SCHEMATA Table ....................................................... 3417

21.22 The INFORMATION_SCHEMA SCHEMA_PRIVILEGES Table ...................................... 3417

MySQL 5.7 Reference Manual

21.23 The INFORMATION_SCHEMA STATISTICS Table ...................................................... 3418

21.24 The INFORMATION_SCHEMA TABLES Table ............................................................. 3419

21.25 The INFORMATION_SCHEMA TABLESPACES Table .................................................. 3420

21.26 The INFORMATION_SCHEMA TABLE_CONSTRAINTS Table ...................................... 3421

21.27 The INFORMATION_SCHEMA TABLE_PRIVILEGES Table .......................................... 3421

21.28 The INFORMATION_SCHEMA TRIGGERS Table ........................................................ 3422

21.29 The INFORMATION_SCHEMA USER_PRIVILEGES Table ........................................... 3424

21.30 The INFORMATION_SCHEMA VIEWS Table ............................................................... 3424

21.31 INFORMATION_SCHEMA Tables for InnoDB ............................................................... 3425

21.31.1 The INFORMATION_SCHEMA INNODB_CMP and INNODB_CMP_RESET

Tables ......................................................................................................................... 3426

21.31.2 The INFORMATION_SCHEMA INNODB_CMP_PER_INDEX and

INNODB_CMP_PER_INDEX_RESET Tables ................................................................. 3427

21.31.3 The INFORMATION_SCHEMA INNODB_CMPMEM and

INNODB_CMPMEM_RESET Tables .............................................................................. 3428

21.31.4 The INFORMATION_SCHEMA INNODB_TRX Table .......................................... 3430

21.31.5 The INFORMATION_SCHEMA INNODB_LOCKS Table ..................................... 3432

21.31.6 The INFORMATION_SCHEMA INNODB_LOCK_WAITS Table ........................... 3433

21.31.7 The INFORMATION_SCHEMA INNODB_SYS_TABLES Table ........................... 3434

21.31.8 The INFORMATION_SCHEMA INNODB_SYS_INDEXES Table ......................... 3436

21.31.9 The INFORMATION_SCHEMA INNODB_SYS_COLUMNS Table ....................... 3438

21.31.10 The INFORMATION_SCHEMA INNODB_SYS_FIELDS Table ........................... 3439

21.31.11 The INFORMATION_SCHEMA INNODB_SYS_FOREIGN Table ....................... 3439

21.31.12 The INFORMATION_SCHEMA INNODB_SYS_FOREIGN_COLS Table ............ 3440

21.31.13 The INFORMATION_SCHEMA INNODB_SYS_TABLESTATS View .................. 3441

21.31.14 The INFORMATION_SCHEMA INNODB_SYS_DATAFILES Table .................... 3442

21.31.15 The INFORMATION_SCHEMA INNODB_SYS_TABLESPACES Table .............. 3443

21.31.16 The INFORMATION_SCHEMA INNODB_SYS_VIRTUAL Table ........................ 3447

21.31.17 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE Table ...................... 3448

21.31.18 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE_LRU Table .............. 3450

21.31.19 The INFORMATION_SCHEMA INNODB_BUFFER_POOL_STATS Table .......... 3452

21.31.20 The INFORMATION_SCHEMA INNODB_METRICS Table ................................ 3454

21.31.21 The INFORMATION_SCHEMA INNODB_FT_CONFIG Table ............................ 3456

21.31.22 The INFORMATION_SCHEMA INNODB_FT_DEFAULT_STOPWORD Table ..... 3457

21.31.23 The INFORMATION_SCHEMA INNODB_FT_INDEX_TABLE Table .................. 3458

21.31.24 The INFORMATION_SCHEMA INNODB_FT_INDEX_CACHE Table ................. 3459

21.31.25 The INFORMATION_SCHEMA INNODB_FT_DELETED Table ......................... 3461

21.31.26 The INFORMATION_SCHEMA INNODB_FT_BEING_DELETED Table ............. 3462

21.31.27 The INFORMATION_SCHEMA INNODB_TEMP_TABLE_INFO Table ............... 3462

21.32 Extensions to SHOW Statements ................................................................................ 3463

22 MySQL Performance Schema .................................................................................................. 3467

22.1 Performance Schema Quick Start .................................................................................. 3469

22.2 Performance Schema Configuration ............................................................................... 3475

22.2.1 Performance Schema Build Configuration ............................................................ 3475

22.2.2 Performance Schema Startup Configuration ........................................................ 3477

22.2.3 Performance Schema Runtime Configuration ....................................................... 3479

22.3 Performance Schema Queries ....................................................................................... 3500

22.4 Performance Schema Instrument Naming Conventions ................................................... 3500

22.5 Performance Schema Status Monitoring ........................................................................ 3503

22.6 Performance Schema Atom and Molecule Events ........................................................... 3506

22.7 Performance Schema Statement Digests ....................................................................... 3507

22.8 Performance Schema General Table Characteristics ...................................................... 3510

22.9 Performance Schema Table Descriptions ....................................................................... 3510

22.9.1 Performance Schema Table Index ...................................................................... 3510

剩余4635页未读，继续阅读

liquid2041

粉丝: 0
资源: 3

MySQL 5.7官方手册：NDB 7.5集群版特性详解

MySQL 5.7 Reference Manual

MySQL 5.7 Reference Manual 5.7.20

MySQL 5.7 Reference Manual 官方参考手册（HTML版）

mysql5.7参考手册

mysql89兼容mysql5.7么

MySQL NDB Cluster和MySQL Cluster 区别

MySQL NDB 集群 配置部署

评价MySQL NDB Cluster

mysql5.7索引

最新资源

MySQL NDB 集群配置部署