没有合适的资源?快使用搜索试试~ 我知道了~
首页MySQL 8.0官方参考手册:特性介绍与最新版本
MySQL 8.0官方参考手册:特性介绍与最新版本
需积分: 10 0 下载量 184 浏览量
更新于2024-07-16
收藏 45.27MB PDF 举报
MySQL 8.0 Reference Manual 是一个详尽的文档,涵盖了 MySQL 8.0 到 8.0.17 版本,以及基于 NDB(MySQL 的一种分布式存储引擎)的 NDBCluster 8.0 的相关功能。该文档不仅提供了关于 MySQL 数据库系统的核心技术信息,还包含了针对未发布的特性的说明。它强调了不同版本之间的差异,指出某些功能可能仅存在于特定的 MySQL 8.0 发行版中,用户应参照许可协议或联系 Oracle 销售代表了解所使用的版本特性。
作为关系型数据库管理系统(RDBMS)的领导者,MySQL 8.0 在 Web 应用场景中表现出色,它的性能优化是文档的重点之一。手册详细介绍了新功能、改进的查询处理能力、安全性增强、存储引擎优化以及对 SQL 标准的支持程度。这包括了如索引、视图、事务管理、并发控制、存储过程等核心数据库概念和技术。
此外,文档还提到了版本更新的变更记录,这些更改可能涉及新的功能特性、错误修复、性能提升或兼容性改进,用户在升级或迁移时可以参考这些信息。法律部分则包含了许可协议和注意事项,确保用户在使用过程中遵循相应的法规和条款。
对于用户支持,手册鼓励读者访问 MySQL 论坛或邮件列表进行交流,以便解决他们在使用过程中遇到的问题,并与其他 MySQL 用户分享经验和知识。文档的最后部分列出了详细的目录,方便读者快速查找所需的信息。
MySQL 8.0 Reference Manual 是开发者、管理员和数据库设计师不可或缺的参考资料,无论是初学者还是经验丰富的专业人士,都可以从中找到深入理解和操作 MySQL 8.0 的关键信息。通过阅读并理解这份文档,用户可以有效地管理和优化他们的 MySQL 数据库系统,提高应用的性能和可靠性。
MySQL 8.0 Reference Manual
xvi
15.6.4 Doublewrite Buffer .............................................................................................. 2687
15.6.5 Redo Log ........................................................................................................... 2687
15.6.6 Undo Logs ......................................................................................................... 2688
15.7 InnoDB Locking and Transaction Model ......................................................................... 2690
15.7.1 InnoDB Locking .................................................................................................. 2690
15.7.2 InnoDB Transaction Model .................................................................................. 2695
15.7.3 Locks Set by Different SQL Statements in InnoDB ............................................... 2704
15.7.4 Phantom Rows ................................................................................................... 2707
15.7.5 Deadlocks in InnoDB .......................................................................................... 2708
15.8 InnoDB Configuration .................................................................................................... 2712
15.8.1 InnoDB Startup Configuration .............................................................................. 2712
15.8.2 Configuring InnoDB for Read-Only Operation ....................................................... 2718
15.8.3 InnoDB Buffer Pool Configuration ........................................................................ 2719
15.8.4 Configuring Thread Concurrency for InnoDB ........................................................ 2734
15.8.5 Configuring the Number of Background InnoDB I/O Threads ................................. 2735
15.8.6 Using Asynchronous I/O on Linux ....................................................................... 2736
15.8.7 Configuring the InnoDB Master Thread I/O Rate .................................................. 2736
15.8.8 Configuring Spin Lock Polling ............................................................................. 2737
15.8.9 Configuring InnoDB Purge Scheduling ................................................................. 2738
15.8.10 Configuring Optimizer Statistics for InnoDB ........................................................ 2738
15.8.11 Configuring the Merge Threshold for Index Pages .............................................. 2750
15.8.12 Enabling Automatic Configuration for a Dedicated MySQL Server ........................ 2752
15.9 InnoDB Table and Page Compression ........................................................................... 2754
15.9.1 InnoDB Table Compression ................................................................................ 2755
15.9.2 InnoDB Page Compression ................................................................................. 2769
15.10 InnoDB Row Formats .................................................................................................. 2772
15.11 InnoDB Disk I/O and File Space Management .............................................................. 2779
15.11.1 InnoDB Disk I/O ............................................................................................... 2779
15.11.2 File Space Management ................................................................................... 2780
15.11.3 InnoDB Checkpoints ......................................................................................... 2781
15.11.4 Defragmenting a Table ..................................................................................... 2782
15.11.5 Reclaiming Disk Space with TRUNCATE TABLE ............................................... 2782
15.12 InnoDB and Online DDL .............................................................................................. 2783
15.12.1 Online DDL Operations ..................................................................................... 2784
15.12.2 Online DDL Performance and Concurrency ........................................................ 2798
15.12.3 Online DDL Space Requirements ...................................................................... 2801
15.12.4 Simplifying DDL Statements with Online DDL .................................................... 2802
15.12.5 Online DDL Failure Conditions .......................................................................... 2803
15.12.6 Online DDL Limitations ..................................................................................... 2803
15.13 InnoDB Startup Options and System Variables ............................................................. 2804
15.14 InnoDB INFORMATION_SCHEMA Tables .................................................................... 2898
15.14.1 InnoDB INFORMATION_SCHEMA Tables about Compression ........................... 2898
15.14.2 InnoDB INFORMATION_SCHEMA Transaction and Locking Information ............. 2900
15.14.3 InnoDB INFORMATION_SCHEMA Schema Object Tables ................................. 2907
15.14.4 InnoDB INFORMATION_SCHEMA FULLTEXT Index Tables .............................. 2913
15.14.5 InnoDB INFORMATION_SCHEMA Buffer Pool Tables ........................................ 2916
15.14.6 InnoDB INFORMATION_SCHEMA Metrics Table ............................................... 2921
15.14.7 InnoDB INFORMATION_SCHEMA Temporary Table Info Table .......................... 2930
15.14.8 Retrieving InnoDB Tablespace Metadata from INFORMATION_SCHEMA.FILES .. 2931
15.15 InnoDB Integration with MySQL Performance Schema .................................................. 2932
15.15.1 Monitoring ALTER TABLE Progress for InnoDB Tables Using Performance
Schema ........................................................................................................................ 2934
15.15.2 Monitoring InnoDB Mutex Waits Using Performance Schema .............................. 2936
15.16 InnoDB Monitors ......................................................................................................... 2940
MySQL 8.0 Reference Manual
xvii
15.16.1 InnoDB Monitor Types ...................................................................................... 2940
15.16.2 Enabling InnoDB Monitors ................................................................................ 2940
15.16.3 InnoDB Standard Monitor and Lock Monitor Output ............................................ 2942
15.17 InnoDB Backup and Recovery ..................................................................................... 2947
15.17.1 InnoDB Backup ................................................................................................ 2947
15.17.2 InnoDB Recovery ............................................................................................. 2948
15.18 InnoDB and MySQL Replication ................................................................................... 2951
15.19 InnoDB memcached Plugin .......................................................................................... 2953
15.19.1 Benefits of the InnoDB memcached Plugin ........................................................ 2953
15.19.2 InnoDB memcached Architecture ...................................................................... 2955
15.19.3 Setting Up the InnoDB memcached Plugin ........................................................ 2958
15.19.4 InnoDB memcached Multiple get and Range Query Support ............................... 2964
15.19.5 Security Considerations for the InnoDB memcached Plugin ................................ 2967
15.19.6 Writing Applications for the InnoDB memcached Plugin ...................................... 2968
15.19.7 The InnoDB memcached Plugin and Replication ................................................ 2981
15.19.8 InnoDB memcached Plugin Internals ................................................................. 2985
15.19.9 Troubleshooting the InnoDB memcached Plugin ................................................ 2990
15.20 InnoDB Troubleshooting .............................................................................................. 2992
15.20.1 Troubleshooting InnoDB I/O Problems ............................................................... 2992
15.20.2 Forcing InnoDB Recovery ................................................................................. 2993
15.20.3 Troubleshooting InnoDB Data Dictionary Operations .......................................... 2994
15.20.4 InnoDB Error Handling ...................................................................................... 2996
16 Alternative Storage Engines ..................................................................................................... 2999
16.1 Setting the Storage Engine ............................................................................................ 3003
16.2 The MyISAM Storage Engine ........................................................................................ 3004
16.2.1 MyISAM Startup Options .................................................................................... 3006
16.2.2 Space Needed for Keys ..................................................................................... 3008
16.2.3 MyISAM Table Storage Formats ......................................................................... 3008
16.2.4 MyISAM Table Problems .................................................................................... 3011
16.3 The MEMORY Storage Engine ...................................................................................... 3013
16.4 The CSV Storage Engine .............................................................................................. 3017
16.4.1 Repairing and Checking CSV Tables .................................................................. 3018
16.4.2 CSV Limitations ................................................................................................. 3019
16.5 The ARCHIVE Storage Engine ...................................................................................... 3019
16.6 The BLACKHOLE Storage Engine ................................................................................. 3021
16.7 The MERGE Storage Engine ......................................................................................... 3023
16.7.1 MERGE Table Advantages and Disadvantages .................................................... 3026
16.7.2 MERGE Table Problems ..................................................................................... 3027
16.8 The FEDERATED Storage Engine ................................................................................. 3029
16.8.1 FEDERATED Storage Engine Overview .............................................................. 3029
16.8.2 How to Create FEDERATED Tables ................................................................... 3030
16.8.3 FEDERATED Storage Engine Notes and Tips ..................................................... 3033
16.8.4 FEDERATED Storage Engine Resources ............................................................ 3034
16.9 The EXAMPLE Storage Engine ..................................................................................... 3034
16.10 Other Storage Engines ................................................................................................ 3035
16.11 Overview of MySQL Storage Engine Architecture ......................................................... 3035
16.11.1 Pluggable Storage Engine Architecture .............................................................. 3035
16.11.2 The Common Database Server Layer ................................................................ 3036
17 Replication ............................................................................................................................... 3039
17.1 Configuring Replication .................................................................................................. 3041
17.1.1 Binary Log File Position Based Replication Configuration Overview ....................... 3041
17.1.2 Setting Up Binary Log File Position Based Replication ......................................... 3042
17.1.3 Replication with Global Transaction Identifiers ..................................................... 3052
17.1.4 MySQL Multi-Source Replication ......................................................................... 3071
MySQL 8.0 Reference Manual
xviii
17.1.5 Changing Replication Modes on Online Servers ................................................... 3075
17.1.6 Replication and Binary Logging Options and Variables ......................................... 3081
17.1.7 Common Replication Administration Tasks .......................................................... 3181
17.2 Replication Implementation ............................................................................................ 3184
17.2.1 Replication Formats ............................................................................................ 3185
17.2.2 Replication Implementation Details ...................................................................... 3193
17.2.3 Replication Channels .......................................................................................... 3194
17.2.4 Replication Relay and Status Logs ...................................................................... 3198
17.2.5 How Servers Evaluate Replication Filtering Rules ................................................ 3203
17.3 Replication Solutions ..................................................................................................... 3211
17.3.1 Using Replication for Backups ............................................................................ 3211
17.3.2 Handling an Unexpected Halt of a Replication Slave ............................................ 3215
17.3.3 Monitoring Row-based Replication ...................................................................... 3217
17.3.4 Using Replication with Different Master and Slave Storage Engines ...................... 3218
17.3.5 Using Replication for Scale-Out .......................................................................... 3219
17.3.6 Replicating Different Databases to Different Slaves .............................................. 3220
17.3.7 Improving Replication Performance ..................................................................... 3222
17.3.8 Switching Masters During Failover ...................................................................... 3223
17.3.9 Setting Up Replication to Use Encrypted Connections .......................................... 3225
17.3.10 Encrypting Binary Log Files and Relay Log Files ................................................ 3227
17.3.11 Semisynchronous Replication ............................................................................ 3229
17.3.12 Delayed Replication .......................................................................................... 3235
17.4 Replication Notes and Tips ............................................................................................ 3238
17.4.1 Replication Features and Issues ......................................................................... 3238
17.4.2 Replication Compatibility Between MySQL Versions ............................................. 3264
17.4.3 Upgrading a Replication Setup ............................................................................ 3265
17.4.4 Troubleshooting Replication ................................................................................ 3266
17.4.5 How to Report Replication Bugs or Problems ...................................................... 3267
18 Group Replication .................................................................................................................... 3269
18.1 Group Replication Background ...................................................................................... 3270
18.1.1 Replication Technologies .................................................................................... 3271
18.1.2 Group Replication Use Cases ............................................................................. 3273
18.1.3 Group Replication Details ................................................................................... 3274
18.2 Getting Started .............................................................................................................. 3275
18.2.1 Deploying Group Replication in Single-Primary Mode ........................................... 3275
18.3 Monitoring Group Replication ......................................................................................... 3287
18.3.1 Group Replication Server States ......................................................................... 3288
18.3.2 The replication_group_members Table ................................................................ 3288
18.3.3 Replication_group_member_stats ........................................................................ 3289
18.4 Group Replication Operations ........................................................................................ 3290
18.4.1 Deploying in Multi-Primary or Single-Primary Mode .............................................. 3290
18.4.2 Configuring an Online Group .............................................................................. 3292
18.4.3 Transaction Consistency Guarantees .................................................................. 3296
18.4.4 Tuning Recovery ................................................................................................ 3303
18.4.5 Network Partitioning ........................................................................................... 3304
18.4.6 Support For IPv6 And For Mixed IPv6 And IPv4 Groups ....................................... 3310
18.4.7 Using MySQL Enterprise Backup with Group Replication ...................................... 3311
18.5 Group Replication Security ............................................................................................ 3313
18.5.1 IP Address Whitelisting ....................................................................................... 3313
18.5.2 Secure Socket Layer Support (SSL) .................................................................... 3315
18.6 Group Replication Performance ..................................................................................... 3319
18.6.1 Fine Tuning the Group Communication Thread .................................................... 3319
18.6.2 Flow Control ...................................................................................................... 3319
18.6.3 Message Compression ....................................................................................... 3321
MySQL 8.0 Reference Manual
xix
18.6.4 Message Fragmentation ..................................................................................... 3323
18.6.5 XCom Cache Management ................................................................................. 3323
18.7 Upgrading Group Replication ......................................................................................... 3324
18.7.1 Group Replication Offline Upgrade ...................................................................... 3324
18.7.2 Group Replication Online Upgrade ...................................................................... 3325
18.8 Group Replication System Variables .............................................................................. 3330
18.9 Requirements and Limitations ........................................................................................ 3357
18.9.1 Group Replication Requirements ......................................................................... 3357
18.9.2 Group Replication Limitations .............................................................................. 3359
18.10 Frequently Asked Questions ........................................................................................ 3361
18.11 Group Replication Technical Details ............................................................................. 3364
18.11.1 Group Replication Plugin Architecture ................................................................ 3364
18.11.2 The Group ....................................................................................................... 3366
18.11.3 Data Manipulation Statements ........................................................................... 3366
18.11.4 Data Definition Statements ................................................................................ 3366
18.11.5 Distributed Recovery ........................................................................................ 3367
18.11.6 Observability .................................................................................................... 3373
19 MySQL Shell ........................................................................................................................... 3375
20 Using MySQL as a Document Store ......................................................................................... 3377
20.1 Interfaces to a MySQL Document Store ......................................................................... 3378
20.2 Document Store Concepts ............................................................................................. 3378
20.3 JavaScript Quick-Start Guide: MySQL Shell for Document Store ...................................... 3379
20.3.1 Import Database Sample .................................................................................... 3380
20.3.2 MySQL Shell ...................................................................................................... 3381
20.3.3 Documents and Collections ................................................................................ 3382
20.3.4 Relational Tables ............................................................................................... 3393
20.3.5 Documents in Tables .......................................................................................... 3399
20.4 Python Quick-Start Guide: MySQL Shell for Document Store ........................................... 3400
20.4.1 Import Database Sample .................................................................................... 3401
20.4.2 MySQL Shell ...................................................................................................... 3402
20.4.3 Documents and Collections ................................................................................ 3403
20.4.4 Relational Tables ............................................................................................... 3414
20.4.5 Documents in Tables .......................................................................................... 3421
20.5 X Plugin ....................................................................................................................... 3422
20.5.1 Checking X Plugin Installation ............................................................................. 3422
20.5.2 Disabling X Plugin .............................................................................................. 3422
20.5.3 Using Secure Connections with X Plugin ............................................................. 3422
20.5.4 Using X Plugin with the Caching SHA-2 Authentication Plugin ............................... 3423
20.5.5 X Plugin Options and Variables .......................................................................... 3423
20.5.6 Monitoring X Plugin ............................................................................................ 3435
21 InnoDB Cluster ........................................................................................................................ 3441
21.1 Introducing InnoDB Cluster ............................................................................................ 3441
21.2 Creating an InnoDB Cluster ........................................................................................... 3444
21.2.1 Deployment Scenarios ........................................................................................ 3444
21.2.2 InnoDB Cluster Requirements ............................................................................. 3444
21.2.3 Methods of Installing .......................................................................................... 3445
21.2.4 Production Deployment of InnoDB Cluster ........................................................... 3445
21.2.5 Sandbox Deployment of InnoDB Cluster .............................................................. 3455
21.2.6 Adopting a Group Replication Deployment ........................................................... 3458
21.3 Using MySQL Router with InnoDB Cluster ..................................................................... 3458
21.4 Working with InnoDB Cluster ......................................................................................... 3461
21.5 Known Limitations ......................................................................................................... 3484
22 MySQL NDB Cluster 8.0 .......................................................................................................... 3485
22.1 NDB Cluster Overview .................................................................................................. 3489
MySQL 8.0 Reference Manual
xx
22.1.1 NDB Cluster Core Concepts ............................................................................... 3491
22.1.2 NDB Cluster Nodes, Node Groups, Replicas, and Partitions ................................. 3493
22.1.3 NDB Cluster Hardware, Software, and Networking Requirements .......................... 3496
22.1.4 What is New in NDB Cluster ............................................................................... 3498
22.1.5 Options, Variables, and Parameters Added, Deprecated or Removed in NDB 8.0 ... 3502
22.1.6 MySQL Server Using InnoDB Compared with NDB Cluster ................................... 3503
22.1.7 Known Limitations of NDB Cluster ...................................................................... 3506
22.2 NDB Cluster Installation ................................................................................................ 3516
22.2.1 The NDB Cluster Auto-Installer ........................................................................... 3518
22.2.2 Installation of NDB Cluster on Linux .................................................................... 3541
22.2.3 Installing NDB Cluster on Windows ..................................................................... 3550
22.2.4 Initial Configuration of NDB Cluster ..................................................................... 3559
22.2.5 Initial Startup of NDB Cluster .............................................................................. 3561
22.2.6 NDB Cluster Example with Tables and Data ........................................................ 3562
22.2.7 Safe Shutdown and Restart of NDB Cluster ......................................................... 3566
22.2.8 Upgrading and Downgrading NDB Cluster ........................................................... 3567
22.3 Configuration of NDB Cluster ......................................................................................... 3567
22.3.1 Quick Test Setup of NDB Cluster ........................................................................ 3568
22.3.2 Overview of NDB Cluster Configuration Parameters, Options, and Variables .......... 3570
22.3.3 NDB Cluster Configuration Files .......................................................................... 3587
22.3.4 Using High-Speed Interconnects with NDB Cluster ............................................... 3772
22.4 NDB Cluster Programs .................................................................................................. 3773
22.4.1 ndbd — The NDB Cluster Data Node Daemon .................................................... 3773
22.4.2 ndbinfo_select_all — Select From ndbinfo Tables ....................................... 3782
22.4.3 ndbmtd — The NDB Cluster Data Node Daemon (Multi-Threaded) ....................... 3783
22.4.4 ndb_mgmd — The NDB Cluster Management Server Daemon .............................. 3784
22.4.5 ndb_mgm — The NDB Cluster Management Client ............................................... 3793
22.4.6 ndb_blob_tool — Check and Repair BLOB and TEXT columns of NDB Cluster
Tables .......................................................................................................................... 3795
22.4.7 ndb_config — Extract NDB Cluster Configuration Information ............................ 3798
22.4.8 ndb_delete_all — Delete All Rows from an NDB Table ................................... 3807
22.4.9 ndb_desc — Describe NDB Tables ................................................................... 3808
22.4.10 ndb_drop_index — Drop Index from an NDB Table ........................................ 3814
22.4.11 ndb_drop_table — Drop an NDB Table ......................................................... 3815
22.4.12 ndb_error_reporter — NDB Error-Reporting Utility ...................................... 3816
22.4.13 ndb_import — Import CSV Data Into NDB ...................................................... 3817
22.4.14 ndb_index_stat — NDB Index Statistics Utility .............................................. 3831
22.4.15 ndb_move_data — NDB Data Copy Utility ....................................................... 3837
22.4.16 ndb_perror — Obtain NDB Error Message Information .................................... 3840
22.4.17 ndb_print_backup_file — Print NDB Backup File Contents ......................... 3842
22.4.18 ndb_print_file — Print NDB Disk Data File Contents ................................... 3842
22.4.19 ndb_print_frag_file — Print NDB Fragment List File Contents .................... 3843
22.4.20 ndb_print_schema_file — Print NDB Schema File Contents ........................ 3844
22.4.21 ndb_print_sys_file — Print NDB System File Contents ............................... 3844
22.4.22 ndb_redo_log_reader — Check and Print Content of Cluster Redo Log .......... 3845
22.4.23 ndb_restore — Restore an NDB Cluster Backup ............................................ 3848
22.4.24 ndb_select_all — Print Rows from an NDB Table ........................................ 3869
22.4.25 ndb_select_count — Print Row Counts for NDB Tables ................................ 3873
22.4.26 ndb_setup.py — Start browser-based Auto-Installer for NDB Cluster ................ 3873
22.4.27 ndb_show_tables — Display List of NDB Tables ............................................ 3877
22.4.28 ndb_size.pl — NDBCLUSTER Size Requirement Estimator ........................... 3879
22.4.29 ndb_top — View CPU usage information for NDB threads ................................ 3882
22.4.30 ndb_waiter — Wait for NDB Cluster to Reach a Given Status .......................... 3887
剩余6449页未读,继续阅读
130 浏览量
2021-11-17 上传
2022-12-31 上传
240 浏览量
250 浏览量
723 浏览量
2025-01-01 上传
2025-01-01 上传
2025-01-01 上传
2025-01-01 上传
wangk2001
- 粉丝: 0
- 资源: 2
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功