mysql8.0参考手册

数据库mysql

需积分: 5 69 浏览量更新于2024-01-20 评论收藏 42.08MB PDF 举报

身份认证购VIP最低享 7 折!

领优惠券(最高得80元）

本文是关于MySQL 8.0参考手册的总结。MySQL 8.0参考手册是关于MySQL 8.0版本的，以及基于NDB版本8.0的NDB集群发布的8.0.29版和8.0.28-ndb-8.0.28版。手册中还可能包含尚未发布的MySQL版本的功能文档。该手册不仅提供了MySQL 8.0版本的详细说明，还涵盖了NDB集群的版本说明。 MySQL 8.0是一种功能强大的关系型数据库管理系统，具有许多令人兴奋的新功能。这个版本的手册说明了这些新功能以及如何使用它们。首先，MySQL 8.0引入了更好的性能和扩展性。它通过改进查询执行计划、优化查询优化器和引入更高效的索引数据结构来提高查询性能。此外，MySQL 8.0还引入了多线程复制和并行复制，提高了复制性能。这些改进使得MySQL 8.0能够处理更大规模的工作负载。其次，MySQL 8.0还引入了更强大的安全功能。它提供了更加严格的权限管理，可以以更细粒度的方式控制用户对数据库的访问权限。此外，MySQL 8.0还引入了加密连接和数据加密功能，保护数据库中的敏感数据免受未经授权的访问。 MySQL 8.0还引入了更好的JSON支持。它提供了一组全新的JSON函数，可以在数据库中存储和处理JSON数据类型。此外，MySQL 8.0还引入了针对JSON数据的索引支持，提高了查询性能。此外，MySQL 8.0还引入了许多其他功能，如保存点和闪回，提供了更好的事务管理和灾难恢复能力；强化的InnoDB存储引擎，提供更好的并发性和可靠性；自适应哈希索引，提高了大数据量下的查询性能；增强的在线DDL功能，允许在不中断服务的情况下修改表结构等等。总之，MySQL 8.0是一个强大且功能丰富的数据库管理系统，它提供了更好的性能、更强大的安全性和更好的功能支持。通过阅读MySQL 8.0参考手册，用户可以全面了解MySQL 8.0的各种功能和用法，从而更好地应用和管理MySQL数据库。

资源详情

资源评论

资源推荐

MySQL 8.0 Reference Manual

15.9 InnoDB Table and Page Compression ...................................................................... 3051

15.9.1 InnoDB Table Compression ........................................................................... 3051

15.9.2 InnoDB Page Compression ........................................................................... 3065

15.10 InnoDB Row Formats ............................................................................................ 3068

15.11 InnoDB Disk I/O and File Space Management ........................................................ 3074

15.11.1 InnoDB Disk I/O .......................................................................................... 3075

15.11.2 File Space Management ............................................................................. 3075

15.11.3 InnoDB Checkpoints ................................................................................... 3077

15.11.4 Defragmenting a Table ................................................................................ 3077

15.11.5 Reclaiming Disk Space with TRUNCATE TABLE .......................................... 3078

15.12 InnoDB and Online DDL ........................................................................................ 3078

15.12.1 Online DDL Operations ............................................................................... 3079

15.12.2 Online DDL Performance and Concurrency .................................................. 3093

15.12.3 Online DDL Space Requirements ................................................................ 3096

15.12.4 Online DDL Memory Management ............................................................... 3097

15.12.5 Configuring Parallel Threads for Online DDL Operations ............................... 3097

15.12.6 Simplifying DDL Statements with Online DDL ............................................... 3097

15.12.7 Online DDL Failure Conditions .................................................................... 3098

15.12.8 Online DDL Limitations ................................................................................ 3098

15.13 InnoDB Data-at-Rest Encryption ............................................................................. 3099

15.14 InnoDB Startup Options and System Variables ........................................................ 3108

15.15 InnoDB INFORMATION_SCHEMA Tables .............................................................. 3195

15.15.1 InnoDB INFORMATION_SCHEMA Tables about Compression ...................... 3196

15.15.2 InnoDB INFORMATION_SCHEMA Transaction and Locking Information ........ 3197

15.15.3 InnoDB INFORMATION_SCHEMA Schema Object Tables ............................ 3204

15.15.4 InnoDB INFORMATION_SCHEMA FULLTEXT Index Tables ......................... 3209

15.15.5 InnoDB INFORMATION_SCHEMA Buffer Pool Tables .................................. 3212

15.15.6 InnoDB INFORMATION_SCHEMA Metrics Table ......................................... 3216

15.15.7 InnoDB INFORMATION_SCHEMA Temporary Table Info Table .................... 3224

15.15.8 Retrieving InnoDB Tablespace Metadata from

INFORMATION_SCHEMA.FILES ............................................................................. 3225

15.16 InnoDB Integration with MySQL Performance Schema ............................................ 3227

15.16.1 Monitoring ALTER TABLE Progress for InnoDB Tables Using Performance

Schema .................................................................................................................. 3229

15.16.2 Monitoring InnoDB Mutex Waits Using Performance Schema ........................ 3230

15.17 InnoDB Monitors .................................................................................................... 3234

15.17.1 InnoDB Monitor Types ................................................................................ 3234

15.17.2 Enabling InnoDB Monitors ........................................................................... 3234

15.17.3 InnoDB Standard Monitor and Lock Monitor Output ...................................... 3236

15.18 InnoDB Backup and Recovery ................................................................................ 3240

15.18.1 InnoDB Backup ........................................................................................... 3240

15.18.2 InnoDB Recovery ........................................................................................ 3241

15.19 InnoDB and MySQL Replication ............................................................................. 3244

15.20 InnoDB memcached Plugin .................................................................................... 3246

15.20.1 Benefits of the InnoDB memcached Plugin ................................................... 3246

15.20.2 InnoDB memcached Architecture ................................................................. 3247

15.20.3 Setting Up the InnoDB memcached Plugin ................................................... 3251

15.20.4 InnoDB memcached Multiple get and Range Query Support .......................... 3256

15.20.5 Security Considerations for the InnoDB memcached Plugin ........................... 3259

15.20.6 Writing Applications for the InnoDB memcached Plugin ................................. 3260

15.20.7 The InnoDB memcached Plugin and Replication ........................................... 3272

15.20.8 InnoDB memcached Plugin Internals ............................................................ 3276

15.20.9 Troubleshooting the InnoDB memcached Plugin ........................................... 3280

15.21 InnoDB Troubleshooting ......................................................................................... 3282

15.21.1 Troubleshooting InnoDB I/O Problems ......................................................... 3283

15.21.2 Troubleshooting Recovery Failures .............................................................. 3283

15.21.3 Forcing InnoDB Recovery ........................................................................... 3284

15.21.4 Troubleshooting InnoDB Data Dictionary Operations ..................................... 3285

xvi

MySQL 8.0 Reference Manual

15.21.5 InnoDB Error Handling ................................................................................ 3286

15.22 InnoDB Limits ........................................................................................................ 3287

15.23 InnoDB Restrictions and Limitations ....................................................................... 3288

16 Alternative Storage Engines ................................................................................................ 3291

16.1 Setting the Storage Engine ...................................................................................... 3294

16.2 The MyISAM Storage Engine ................................................................................... 3295

16.2.1 MyISAM Startup Options ............................................................................... 3298

16.2.2 Space Needed for Keys ................................................................................ 3299

16.2.3 MyISAM Table Storage Formats .................................................................... 3299

16.2.4 MyISAM Table Problems ............................................................................... 3302

16.3 The MEMORY Storage Engine ................................................................................ 3303

16.4 The CSV Storage Engine ........................................................................................ 3308

16.4.1 Repairing and Checking CSV Tables ............................................................. 3309

16.4.2 CSV Limitations ............................................................................................ 3309

16.5 The ARCHIVE Storage Engine ................................................................................. 3309

16.6 The BLACKHOLE Storage Engine ........................................................................... 3311

16.7 The MERGE Storage Engine ................................................................................... 3313

16.7.1 MERGE Table Advantages and Disadvantages .............................................. 3316

16.7.2 MERGE Table Problems ............................................................................... 3317

16.8 The FEDERATED Storage Engine ........................................................................... 3318

16.8.1 FEDERATED Storage Engine Overview ......................................................... 3318

16.8.2 How to Create FEDERATED Tables .............................................................. 3320

16.8.3 FEDERATED Storage Engine Notes and Tips ................................................ 3322

16.8.4 FEDERATED Storage Engine Resources ....................................................... 3324

16.9 The EXAMPLE Storage Engine ................................................................................ 3324

16.10 Other Storage Engines .......................................................................................... 3324

16.11 Overview of MySQL Storage Engine Architecture .................................................... 3324

16.11.1 Pluggable Storage Engine Architecture ........................................................ 3326

16.11.2 The Common Database Server Layer .......................................................... 3326

17 Replication ......................................................................................................................... 3329

17.1 Configuring Replication ............................................................................................ 3331

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

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

17.1.3 Replication with Global Transaction Identifiers ................................................ 3343

17.1.4 Changing GTID Mode on Online Servers ....................................................... 3366

17.1.5 MySQL Multi-Source Replication ................................................................... 3372

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

17.1.7 Common Replication Administration Tasks ..................................................... 3492

17.2 Replication Implementation ...................................................................................... 3498

17.2.1 Replication Formats ...................................................................................... 3499

17.2.2 Replication Channels .................................................................................... 3506

17.2.3 Replication Threads ...................................................................................... 3510

17.2.4 Relay Log and Replication Metadata Repositories .......................................... 3513

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

17.3 Replication Security ................................................................................................. 3528

17.3.1 Setting Up Replication to Use Encrypted Connections .................................... 3528

17.3.2 Encrypting Binary Log Files and Relay Log Files ............................................ 3531

17.3.3 Replication Privilege Checks ......................................................................... 3535

17.4 Replication Solutions ............................................................................................... 3541

17.4.1 Using Replication for Backups ....................................................................... 3542

17.4.2 Handling an Unexpected Halt of a Replica ..................................................... 3545

17.4.3 Monitoring Row-based Replication ................................................................. 3548

17.4.4 Using Replication with Different Source and Replica Storage Engines .............. 3548

17.4.5 Using Replication for Scale-Out ..................................................................... 3549

17.4.6 Replicating Different Databases to Different Replicas ...................................... 3551

17.4.7 Improving Replication Performance ................................................................ 3552

17.4.8 Switching Sources During Failover ................................................................ 3553

17.4.9 Switching Sources and Replicas with Asynchronous Connection Failover ......... 3556

xvii

MySQL 8.0 Reference Manual

17.4.10 Semisynchronous Replication ...................................................................... 3559

17.4.11 Delayed Replication .................................................................................... 3565

17.5 Replication Notes and Tips ...................................................................................... 3568

17.5.1 Replication Features and Issues .................................................................... 3568

17.5.2 Replication Compatibility Between MySQL Versions ....................................... 3595

17.5.3 Upgrading a Replication Setup ...................................................................... 3596

17.5.4 Troubleshooting Replication .......................................................................... 3597

17.5.5 How to Report Replication Bugs or Problems ................................................. 3598

18 Group Replication .............................................................................................................. 3601

18.1 Group Replication Background ................................................................................. 3602

18.1.1 Replication Technologies ............................................................................... 3603

18.1.2 Group Replication Use Cases ....................................................................... 3606

18.1.3 Multi-Primary and Single-Primary Modes ........................................................ 3607

18.1.4 Group Replication Services ........................................................................... 3611

18.1.5 Group Replication Plugin Architecture ............................................................ 3613

18.2 Getting Started ........................................................................................................ 3615

18.2.1 Deploying Group Replication in Single-Primary Mode ...................................... 3615

18.2.2 Deploying Group Replication Locally .............................................................. 3628

18.3 Requirements and Limitations .................................................................................. 3629

18.3.1 Group Replication Requirements ................................................................... 3629

18.3.2 Group Replication Limitations ........................................................................ 3631

18.4 Monitoring Group Replication ................................................................................... 3634

18.4.1 GTIDs and Group Replication ........................................................................ 3635

18.4.2 Group Replication Server States .................................................................... 3636

18.4.3 The replication_group_members Table .......................................................... 3636

18.4.4 The replication_group_member_stats Table ................................................... 3637

18.5 Group Replication Operations .................................................................................. 3637

18.5.1 Configuring an Online Group ......................................................................... 3638

18.5.2 Transaction Consistency Guarantees ............................................................. 3643

18.5.3 Distributed Recovery ..................................................................................... 3649

18.5.4 Network Partitioning ...................................................................................... 3664

18.5.5 Support For IPv6 And For Mixed IPv6 And IPv4 Groups ................................. 3669

18.5.6 Using MySQL Enterprise Backup with Group Replication ................................ 3671

18.6 Group Replication Security ....................................................................................... 3677

18.6.1 Methods for Connection Security Management ............................................... 3677

18.6.2 Securing Group Communication Connections with Secure Socket Layer (SSL) . 3679

18.6.3 Securing Distributed Recovery Connections ................................................... 3681

18.6.4 Group Replication IP Address Permissions .................................................... 3685

18.7 Group Replication Performance ................................................................................ 3688

18.7.1 Fine Tuning the Group Communication Thread .............................................. 3688

18.7.2 Flow Control ................................................................................................. 3688

18.7.3 Single Consensus Leader ............................................................................. 3690

18.7.4 Message Compression .................................................................................. 3690

18.7.5 Message Fragmentation ................................................................................ 3692

18.7.6 XCom Cache Management ........................................................................... 3693

18.7.7 Responses to Failure Detection and Network Partitioning ................................ 3695

18.8 Upgrading Group Replication ................................................................................... 3701

18.8.1 Combining Different Member Versions in a Group .......................................... 3701

18.8.2 Group Replication Offline Upgrade ................................................................ 3703

18.8.3 Group Replication Online Upgrade ................................................................ 3704

18.9 Group Replication System Variables ......................................................................... 3707

18.10 Frequently Asked Questions ................................................................................... 3749

19 MySQL Shell ...................................................................................................................... 3755

20 Using MySQL as a Document Store ................................................................................... 3757

20.1 Interfaces to a MySQL Document Store .................................................................... 3758

20.2 Document Store Concepts ....................................................................................... 3758

20.3 JavaScript Quick-Start Guide: MySQL Shell for Document Store ................................ 3759

20.3.1 MySQL Shell ................................................................................................ 3760

xviii

MySQL 8.0 Reference Manual

20.3.2 Download and Import world_x Database ........................................................ 3761

20.3.3 Documents and Collections ........................................................................... 3762

20.3.4 Relational Tables .......................................................................................... 3772

20.3.5 Documents in Tables .................................................................................... 3778

20.4 Python Quick-Start Guide: MySQL Shell for Document Store ..................................... 3779

20.4.1 MySQL Shell ................................................................................................ 3779

20.4.2 Download and Import world_x Database ........................................................ 3781

20.4.3 Documents and Collections ........................................................................... 3781

20.4.4 Relational Tables .......................................................................................... 3792

20.4.5 Documents in Tables .................................................................................... 3798

20.5 X Plugin .................................................................................................................. 3799

20.5.1 Checking X Plugin Installation ....................................................................... 3799

20.5.2 Disabling X Plugin ........................................................................................ 3799

20.5.3 Using Encrypted Connections with X Plugin ................................................... 3799

20.5.4 Using X Plugin with the Caching SHA-2 Authentication Plugin ......................... 3800

20.5.5 Connection Compression with X Plugin .......................................................... 3801

20.5.6 X Plugin Options and Variables ..................................................................... 3804

20.5.7 Monitoring X Plugin ...................................................................................... 3824

21 InnoDB Cluster ................................................................................................................... 3827

22 InnoDB ReplicaSet ............................................................................................................. 3831

23 MySQL NDB Cluster 8.0 .................................................................................................... 3833

23.1 General Information ................................................................................................. 3835

23.2 NDB Cluster Overview ............................................................................................. 3837

23.2.1 NDB Cluster Core Concepts .......................................................................... 3838

23.2.2 NDB Cluster Nodes, Node Groups, Fragment Replicas, and Partitions ............. 3841

23.2.3 NDB Cluster Hardware, Software, and Networking Requirements .................... 3843

23.2.4 What is New in NDB Cluster ......................................................................... 3845

23.2.5 Options, Variables, and Parameters Added, Deprecated or Removed in NDB

8.0 .......................................................................................................................... 3867

23.2.6 MySQL Server Using InnoDB Compared with NDB Cluster ............................. 3872

23.2.7 Known Limitations of NDB Cluster ................................................................. 3875

23.3 NDB Cluster Installation ........................................................................................... 3886

23.3.1 Installation of NDB Cluster on Linux .............................................................. 3888

23.3.2 Installing NDB Cluster on Windows ............................................................... 3896

23.3.3 Initial Configuration of NDB Cluster ............................................................... 3905

23.3.4 Initial Startup of NDB Cluster ........................................................................ 3907

23.3.5 NDB Cluster Example with Tables and Data .................................................. 3907

23.3.6 Safe Shutdown and Restart of NDB Cluster ................................................... 3911

23.3.7 Upgrading and Downgrading NDB Cluster ..................................................... 3911

23.3.8 The NDB Cluster Auto-Installer (No longer supported) .................................... 3914

23.4 Configuration of NDB Cluster ................................................................................... 3934

23.4.1 Quick Test Setup of NDB Cluster .................................................................. 3935

23.4.2 Overview of NDB Cluster Configuration Parameters, Options, and Variables ..... 3937

23.4.3 NDB Cluster Configuration Files .................................................................... 3957

23.4.4 Using High-Speed Interconnects with NDB Cluster ......................................... 4131

23.5 NDB Cluster Programs ............................................................................................ 4132

23.5.1 ndbd — The NDB Cluster Data Node Daemon ............................................... 4132

23.5.2 ndbinfo_select_all — Select From ndbinfo Tables ........................................... 4142

23.5.3 ndbmtd — The NDB Cluster Data Node Daemon (Multi-Threaded) .................. 4147

23.5.4 ndb_mgmd — The NDB Cluster Management Server Daemon ........................ 4148

23.5.5 ndb_mgm — The NDB Cluster Management Client ........................................ 4160

23.5.6 ndb_blob_tool — Check and Repair BLOB and TEXT columns of NDB Cluster

Tables ..................................................................................................................... 4165

23.5.7 ndb_config — Extract NDB Cluster Configuration Information .......................... 4171

23.5.8 ndb_delete_all — Delete All Rows from an NDB Table ................................... 4183

23.5.9 ndb_desc — Describe NDB Tables ............................................................... 4187

23.5.10 ndb_drop_index — Drop Index from an NDB Table ....................................... 4197

23.5.11 ndb_drop_table — Drop an NDB Table ........................................................ 4201

xix

MySQL 8.0 Reference Manual

23.5.12 ndb_error_reporter — NDB Error-Reporting Utility ......................................... 4205

23.5.13 ndb_import — Import CSV Data Into NDB .................................................... 4207

23.5.14 ndb_index_stat — NDB Index Statistics Utility .............................................. 4223

23.5.15 ndb_move_data — NDB Data Copy Utility ................................................... 4231

23.5.16 ndb_perror — Obtain NDB Error Message Information .................................. 4236

23.5.17 ndb_print_backup_file — Print NDB Backup File Contents ............................. 4239

23.5.18 ndb_print_file — Print NDB Disk Data File Contents ..................................... 4244

23.5.19 ndb_print_frag_file — Print NDB Fragment List File Contents ........................ 4244

23.5.20 ndb_print_schema_file — Print NDB Schema File Contents ........................... 4245

23.5.21 ndb_print_sys_file — Print NDB System File Contents .................................. 4245

23.5.22 ndb_redo_log_reader — Check and Print Content of Cluster Redo Log .......... 4246

23.5.23 ndb_restore — Restore an NDB Cluster Backup ........................................... 4248

23.5.24 ndb_select_all — Print Rows from an NDB Table ......................................... 4280

23.5.25 ndb_select_count — Print Row Counts for NDB Tables ................................. 4286

23.5.26 ndb_setup.py — Start browser-based Auto-Installer for NDB Cluster

(DEPRECATED) ...................................................................................................... 4290

23.5.27 ndb_show_tables — Display List of NDB Tables ........................................... 4294

23.5.28 ndb_size.pl — NDBCLUSTER Size Requirement Estimator ........................... 4298

23.5.29 ndb_top — View CPU usage information for NDB threads ............................. 4301

23.5.30 ndb_waiter — Wait for NDB Cluster to Reach a Given Status ........................ 4306

23.5.31 ndbxfrm — Compress, Decompress, Encrypt, and Decrypt Files Created by

NDB Cluster ............................................................................................................ 4312

23.6 Management of NDB Cluster ................................................................................... 4316

23.6.1 Commands in the NDB Cluster Management Client ........................................ 4316

23.6.2 NDB Cluster Log Messages .......................................................................... 4322

23.6.3 Event Reports Generated in NDB Cluster ...................................................... 4341

23.6.4 Summary of NDB Cluster Start Phases .......................................................... 4352

23.6.5 Performing a Rolling Restart of an NDB Cluster ............................................. 4354

23.6.6 NDB Cluster Single User Mode ..................................................................... 4356

23.6.7 Adding NDB Cluster Data Nodes Online ........................................................ 4357

23.6.8 Online Backup of NDB Cluster ...................................................................... 4367

23.6.9 MySQL Server Usage for NDB Cluster .......................................................... 4373

23.6.10 NDB Cluster Disk Data Tables .................................................................... 4375

23.6.11 Online Operations with ALTER TABLE in NDB Cluster .................................. 4381

23.6.12 Distributed MySQL Privileges with NDB_STORED_USER ............................. 4384

23.6.13 NDB API Statistics Counters and Variables .................................................. 4385

23.6.14 ndbinfo: The NDB Cluster Information Database ........................................... 4395

23.6.15 INFORMATION_SCHEMA Tables for NDB Cluster ....................................... 4465

23.6.16 Quick Reference: NDB Cluster SQL Statements ........................................... 4465

23.6.17 NDB Cluster Security Issues ....................................................................... 4471

23.7 NDB Cluster Replication .......................................................................................... 4479

23.7.1 NDB Cluster Replication: Abbreviations and Symbols ..................................... 4480

23.7.2 General Requirements for NDB Cluster Replication ........................................ 4481

23.7.3 Known Issues in NDB Cluster Replication ...................................................... 4482

23.7.4 NDB Cluster Replication Schema and Tables ................................................. 4488

23.7.5 Preparing the NDB Cluster for Replication ..................................................... 4495

23.7.6 Starting NDB Cluster Replication (Single Replication Channel) ........................ 4497

23.7.7 Using Two Replication Channels for NDB Cluster Replication .......................... 4498

23.7.8 Implementing Failover with NDB Cluster Replication ....................................... 4499

23.7.9 NDB Cluster Backups With NDB Cluster Replication ....................................... 4501

23.7.10 NDB Cluster Replication: Bidrectional and Circular Replication ...................... 4507

23.7.11 NDB Cluster Replication Conflict Resolution ................................................. 4511

23.8 NDB Cluster Release Notes ..................................................................................... 4523

24 Partitioning ......................................................................................................................... 4525

24.1 Overview of Partitioning in MySQL ........................................................................... 4526

24.2 Partitioning Types .................................................................................................... 4529

24.2.1 RANGE Partitioning ...................................................................................... 4530

24.2.2 LIST Partitioning ........................................................................................... 4534

剩余6025页未读，继续阅读

jason_zzh

粉丝: 3
资源: 24

会员权益专享

MySQL 8.0 参考手册：特性详解与NDB集群包括

评论0

会员权益专享

最新资源

MySQL 8.0 参考手册：特性详解与NDB集群包括

评论0

MySQL8.0参考手册中文版

MySQL 8.0参考手册 Including MySQL NDB Cluster 8.0

MySQL8.0参考手册.pdf

MySQL 8.0数据库安装与配置详解

MySQL 8.0的安全机制与用户管理

MySQL 8.0数据库基础概念与架构介绍

备份与恢复策略：MySQL 8.0最佳实践

表设计与规范化：MySQL 8.0实践

mysql8.0参考手册中文

mysql8.0参考手册

mysql8.0中文参考手册 下载

MySQL8.0安装手册

mysqlslap 生成测试数据语句mysql8.0

mysql8.0 宕机数据文件恢复

mysql 8 中文手册下载

check the manual that corresponds to your MySQL server version for the right syntax to use near 'RECURSI使用报错

Mysql数据库设计文献

linux mysql8安装

check the manual that corresponds to your mysql

MySQL 8.0 参考手册教程pdf

会员权益专享

最新资源

mysql8.0中文参考手册下载