没有合适的资源?快使用搜索试试~ 我知道了~
首页MySQL8.0参考手册:开发版详解
MySQL8.0参考手册:开发版详解
5星 · 超过95%的资源 需积分: 15 23 下载量 120 浏览量
更新于2024-07-18
收藏 37.67MB PDF 举报
"MySQL8手册包含了从基础到高级的MySQL数据库系统使用指南,涵盖安装与升级、教程、相关程序、管理、安全、备份恢复、优化以及语言等各个方面。本手册适用于MySQL8.0版本,详细记录了从8.0.0到8.0.13的所有更新内容。"
MySQL8.0作为开发系列,不推荐用于生产环境。MySQL Cluster在8.0版本中未得到支持,若需了解相关信息,建议查阅MySQL NDB Cluster 7.5和7.6的相关文档。
手册中详述了MySQL8.0的一些特色功能,但请注意,这些特性并非每个版本都包含,具体包含哪些特性取决于您所获得的MySQL8.0的授权版。对于所授权版本的特性疑问,应参考您的MySQL8.0许可协议或联系Oracle的销售代表。
关于每个发行版的具体改动,可以在MySQL8.0的发行说明中找到详细信息。对于法律相关资讯,请参阅法律通知。在遇到使用问题时,可以访问MySQL论坛或邮件列表,与其他MySQL用户交流讨论。
MySQL8.0的许可信息指出,该产品可能包含第三方软件,这些软件是根据许可协议使用的。如果使用的是MySQL8.0的社区发布版,可以查看MySQL8.0社区发布版的许可信息。
在"安装和升级"部分,用户将了解到如何在不同操作系统上安装MySQL8.0,包括配置选项、依赖库的安装以及升级现有MySQL实例的步骤。
"教程"章节则为初学者提供了快速入门的指导,包括创建数据库、表,以及基本的SQL查询语法。
"MySQL程序"章节介绍了各种用于管理MySQL服务器的工具,如`mysql`, `mysqldump`, `mysqladmin`等,以及它们的用途和命令行选项。
"MySQL管理"部分涵盖了数据库的日常维护,如用户权限管理、性能监控、日志配置和错误处理。
"安全"章节讲解了如何设置和强化MySQL的安全性,包括用户认证、权限控制和加密技术。
"备份与恢复"提供了关于数据备份策略、使用mysqldump工具进行全量和增量备份,以及在数据丢失或故障时如何恢复数据的详细步骤。
"优化"部分涉及查询优化、索引设计、内存管理和服务器配置,以提高MySQL的性能。
"语言"章节可能涉及到SQL语言的详细规范,包括DML(数据操纵语言)、DDL(数据定义语言)和DCL(数据控制语言)等。
这份手册是MySQL8.0用户的重要参考资料,无论你是新手还是经验丰富的开发者,都能从中获取所需的信息。
MySQL 8.0 Reference Manual
Pre-General
Availability
Draft xvi
Pre-General
Availability
Draft
15.7.11 InnoDB Tablespace Encryption ......................................................................... 2362
15.8 InnoDB Tables and Indexes .......................................................................................... 2366
15.8.1 InnoDB Tables ................................................................................................... 2367
15.8.2 InnoDB Indexes ................................................................................................. 2391
15.9 InnoDB Table and Page Compression ........................................................................... 2399
15.9.1 InnoDB Table Compression ................................................................................ 2399
15.9.2 InnoDB Page Compression ................................................................................. 2413
15.10 InnoDB Row Storage and Row Formats ....................................................................... 2417
15.10.1 Overview of InnoDB Row Storage ..................................................................... 2417
15.10.2 Specifying the Row Format for a Table .............................................................. 2417
15.10.3 DYNAMIC and COMPRESSED Row Formats .................................................... 2419
15.10.4 COMPACT and REDUNDANT Row Formats ..................................................... 2420
15.11 InnoDB Disk I/O and File Space Management .............................................................. 2421
15.11.1 InnoDB Disk I/O ............................................................................................... 2421
15.11.2 File Space Management ................................................................................... 2422
15.11.3 InnoDB Checkpoints ......................................................................................... 2423
15.11.4 Defragmenting a Table ..................................................................................... 2424
15.11.5 Reclaiming Disk Space with TRUNCATE TABLE ............................................... 2424
15.12 InnoDB and Online DDL .............................................................................................. 2425
15.12.1 Online DDL Overview ....................................................................................... 2425
15.12.2 Online DDL Performance, Concurrency, and Space Requirements ...................... 2431
15.12.3 Online DDL SQL Syntax ................................................................................... 2435
15.12.4 Simplifying DDL Statements with Online DDL .................................................... 2435
15.12.5 Online DDL Implementation Details ................................................................... 2436
15.12.6 Online DDL for Partitioned Tables ..................................................................... 2437
15.12.7 Online DDL Limitations ..................................................................................... 2439
15.13 InnoDB Startup Options and System Variables ............................................................. 2440
15.14 InnoDB INFORMATION_SCHEMA Tables .................................................................... 2532
15.14.1 InnoDB INFORMATION_SCHEMA Tables about Compression ........................... 2533
15.14.2 InnoDB INFORMATION_SCHEMA Transaction and Locking Information ............. 2534
15.14.3 InnoDB INFORMATION_SCHEMA Schema Object Tables ................................. 2541
15.14.4 InnoDB INFORMATION_SCHEMA FULLTEXT Index Tables .............................. 2547
15.14.5 InnoDB INFORMATION_SCHEMA Buffer Pool Tables ........................................ 2550
15.14.6 InnoDB INFORMATION_SCHEMA Metrics Table ............................................... 2555
15.14.7 InnoDB INFORMATION_SCHEMA Temporary Table Info Table .......................... 2563
15.14.8 Retrieving InnoDB Tablespace Metadata from INFORMATION_SCHEMA.FILES .. 2565
15.15 InnoDB Integration with MySQL Performance Schema .................................................. 2566
15.15.1 Monitoring ALTER TABLE Progress for InnoDB Tables Using Performance
Schema ........................................................................................................................ 2568
15.15.2 Monitoring InnoDB Mutex Waits Using Performance Schema .............................. 2570
15.16 InnoDB Monitors ......................................................................................................... 2574
15.16.1 InnoDB Monitor Types ...................................................................................... 2574
15.16.2 Enabling InnoDB Monitors ................................................................................ 2574
15.16.3 InnoDB Standard Monitor and Lock Monitor Output ............................................ 2576
15.17 InnoDB Backup and Recovery ..................................................................................... 2581
15.17.1 InnoDB Backup ................................................................................................ 2581
15.17.2 InnoDB Recovery ............................................................................................. 2582
15.18 InnoDB and MySQL Replication ................................................................................... 2585
15.19 InnoDB memcached Plugin .......................................................................................... 2587
15.19.1 Benefits of the InnoDB memcached Plugin ........................................................ 2588
15.19.2 InnoDB memcached Architecture ...................................................................... 2589
15.19.3 Setting Up the InnoDB memcached Plugin ........................................................ 2593
15.19.4 InnoDB memcached Multiple get and Range Query Support ............................... 2599
15.19.5 Security Considerations for the InnoDB memcached Plugin ................................ 2602
MySQL 8.0 Reference Manual
Pre-General
Availability
Draft xvii
Pre-General
Availability
Draft
15.19.6 Writing Applications for the InnoDB memcached Plugin ...................................... 2603
15.19.7 The InnoDB memcached Plugin and Replication ................................................ 2616
15.19.8 InnoDB memcached Plugin Internals ................................................................. 2620
15.19.9 Troubleshooting the InnoDB memcached Plugin ................................................ 2639
15.20 InnoDB Troubleshooting .............................................................................................. 2641
15.20.1 Troubleshooting InnoDB I/O Problems ............................................................... 2642
15.20.2 Forcing InnoDB Recovery ................................................................................. 2642
15.20.3 Troubleshooting InnoDB Data Dictionary Operations .......................................... 2644
15.20.4 InnoDB Error Handling ...................................................................................... 2645
16 Alternative Storage Engines ..................................................................................................... 2647
16.1 Setting the Storage Engine ............................................................................................ 2651
16.2 The MyISAM Storage Engine ........................................................................................ 2652
16.2.1 MyISAM Startup Options .................................................................................... 2654
16.2.2 Space Needed for Keys ..................................................................................... 2656
16.2.3 MyISAM Table Storage Formats ......................................................................... 2656
16.2.4 MyISAM Table Problems .................................................................................... 2659
16.3 The MEMORY Storage Engine ...................................................................................... 2661
16.4 The CSV Storage Engine .............................................................................................. 2665
16.4.1 Repairing and Checking CSV Tables .................................................................. 2666
16.4.2 CSV Limitations ................................................................................................. 2667
16.5 The ARCHIVE Storage Engine ...................................................................................... 2667
16.6 The BLACKHOLE Storage Engine ................................................................................. 2669
16.7 The MERGE Storage Engine ......................................................................................... 2671
16.7.1 MERGE Table Advantages and Disadvantages .................................................... 2674
16.7.2 MERGE Table Problems ..................................................................................... 2675
16.8 The FEDERATED Storage Engine ................................................................................. 2677
16.8.1 FEDERATED Storage Engine Overview .............................................................. 2677
16.8.2 How to Create FEDERATED Tables ................................................................... 2678
16.8.3 FEDERATED Storage Engine Notes and Tips ..................................................... 2681
16.8.4 FEDERATED Storage Engine Resources ............................................................ 2682
16.9 The EXAMPLE Storage Engine ..................................................................................... 2682
16.10 Other Storage Engines ................................................................................................ 2683
16.11 Overview of MySQL Storage Engine Architecture ......................................................... 2683
16.11.1 Pluggable Storage Engine Architecture .............................................................. 2683
16.11.2 The Common Database Server Layer ................................................................ 2684
17 High Availability and Scalability ................................................................................................ 2687
17.1 Using ZFS Replication ................................................................................................... 2689
17.1.1 Using ZFS for File System Replication ................................................................ 2690
17.1.2 Configuring MySQL for ZFS Replication .............................................................. 2691
17.1.3 Handling MySQL Recovery with ZFS .................................................................. 2692
17.2 Using MySQL with memcached ..................................................................................... 2692
17.2.1 Installing memcached ......................................................................................... 2694
17.2.2 Using memcached .............................................................................................. 2695
17.2.3 Developing a memcached Application ................................................................. 2715
17.2.4 Getting memcached Statistics ............................................................................. 2741
17.2.5 memcached FAQ ............................................................................................... 2750
18 Replication ............................................................................................................................... 2755
18.1 Configuring Replication .................................................................................................. 2757
18.1.1 Binary Log File Position Based Replication Configuration Overview ....................... 2757
18.1.2 Setting Up Binary Log File Position Based Replication ......................................... 2758
18.1.3 Replication with Global Transaction Identifiers ..................................................... 2767
18.1.4 MySQL Multi-Source Replication ......................................................................... 2778
18.1.5 Changing Replication Modes on Online Servers ................................................... 2782
18.1.6 Replication and Binary Logging Options and Variables ......................................... 2788
MySQL 8.0 Reference Manual
Pre-General
Availability
Draft xviii
Pre-General
Availability
Draft
18.1.7 Common Replication Administration Tasks .......................................................... 2872
18.2 Replication Implementation ............................................................................................ 2875
18.2.1 Replication Formats ............................................................................................ 2875
18.2.2 Replication Implementation Details ...................................................................... 2883
18.2.3 Replication Channels .......................................................................................... 2885
18.2.4 Replication Relay and Status Logs ...................................................................... 2888
18.2.5 How Servers Evaluate Replication Filtering Rules ................................................ 2893
18.3 Replication Solutions ..................................................................................................... 2901
18.3.1 Using Replication for Backups ............................................................................ 2901
18.3.2 Handling an Unexpected Halt of a Replication Slave ............................................ 2905
18.3.3 Monitoring Row-based Replication ...................................................................... 2907
18.3.4 Using Replication with Different Master and Slave Storage Engines ...................... 2908
18.3.5 Using Replication for Scale-Out .......................................................................... 2909
18.3.6 Replicating Different Databases to Different Slaves .............................................. 2910
18.3.7 Improving Replication Performance ..................................................................... 2911
18.3.8 Switching Masters During Failover ...................................................................... 2913
18.3.9 Setting Up Replication to Use Encrypted Connections .......................................... 2915
18.3.10 Semisynchronous Replication ............................................................................ 2916
18.3.11 Delayed Replication .......................................................................................... 2922
18.4 Replication Notes and Tips ............................................................................................ 2925
18.4.1 Replication Features and Issues ......................................................................... 2925
18.4.2 Replication Compatibility Between MySQL Versions ............................................. 2952
18.4.3 Upgrading a Replication Setup ............................................................................ 2953
18.4.4 Troubleshooting Replication ................................................................................ 2954
18.4.5 How to Report Replication Bugs or Problems ...................................................... 2955
19 MySQL Shell User Guide ......................................................................................................... 2957
19.1 MySQL Shell Features .................................................................................................. 2957
19.2 Getting Started with MySQL Shell .................................................................................. 2959
19.2.1 MySQL Shell Connections .................................................................................. 2959
19.2.2 MySQL Shell Sessions ....................................................................................... 2965
19.2.3 MySQL Shell Global Variables ............................................................................ 2966
19.2.4 MySQL Shell Code History ................................................................................. 2968
19.3 MySQL Shell Code Execution ........................................................................................ 2969
19.3.1 Interactive Code Execution ................................................................................. 2969
19.3.2 Code Autocompletion ......................................................................................... 2970
19.3.3 Batch Code Execution ........................................................................................ 2972
19.3.4 Output Formats .................................................................................................. 2973
19.3.5 Active Language ................................................................................................ 2975
19.3.6 Batch Mode Made Interactive ............................................................................. 2976
19.4 Configuring MySQL Shell .............................................................................................. 2977
19.4.1 MySQL Shell Commands .................................................................................... 2977
19.5 MySQL Shell Utilities ..................................................................................................... 2979
19.6 MySQL Shell Application Log ........................................................................................ 2980
19.6.1 Application Log .................................................................................................. 2980
19.7 Customizing MySQL Shell ............................................................................................. 2981
19.7.1 Working With Start-Up Scripts ............................................................................. 2982
19.7.2 Adding Module Search Paths .............................................................................. 2982
19.7.3 Customizing the Prompt ..................................................................................... 2983
20 Using MySQL as a Document Store ......................................................................................... 2985
20.1 Preproduction Status — Legal Notice ............................................................................. 2986
20.2 Key Concepts ............................................................................................................... 2986
20.3 Setting Up MySQL as a Document Store ....................................................................... 2987
20.3.1 Installing MySQL Shell ....................................................................................... 2990
20.3.2 Starting MySQL Shell ......................................................................................... 2993
MySQL 8.0 Reference Manual
Pre-General
Availability
Draft xix
Pre-General
Availability
Draft
20.4 Quick-Start Guide: MySQL Shell for JavaScript .............................................................. 2994
20.4.1 Introduction ........................................................................................................ 2994
20.4.2 Import Database Sample .................................................................................... 2995
20.4.3 MySQL Shell ...................................................................................................... 2996
20.4.4 Documents and Collections ................................................................................ 2997
20.4.5 Relational Tables ............................................................................................... 3008
20.4.6 Documents in Tables .......................................................................................... 3014
20.5 Quick-Start Guide: MySQL Shell for Python ................................................................... 3015
20.5.1 Introduction ........................................................................................................ 3015
20.5.2 Import Database Sample .................................................................................... 3016
20.5.3 MySQL Shell ...................................................................................................... 3017
20.5.4 Documents and Collections ................................................................................ 3018
20.5.5 Relational Tables ............................................................................................... 3029
20.5.6 Documents in Tables .......................................................................................... 3035
20.6 Quick-Start Guide: MySQL for Visual Studio ................................................................... 3036
20.7 X Plugin ....................................................................................................................... 3037
20.7.1 Using Secure Connections with X Plugin ............................................................. 3037
20.7.2 Using X Plugin with the Caching SHA-2 Authentication Plugin ............................... 3038
20.7.3 X Plugin Options and Variables .......................................................................... 3038
20.7.4 Monitoring X Plugin ............................................................................................ 3054
21 Group Replication .................................................................................................................... 3059
21.1 Group Replication Background ...................................................................................... 3060
21.1.1 Replication Technologies .................................................................................... 3061
21.1.2 Group Replication Use Cases ............................................................................. 3063
21.1.3 Group Replication Details ................................................................................... 3063
21.2 Getting Started .............................................................................................................. 3065
21.2.1 Deploying Group Replication in Single-Primary Mode ........................................... 3065
21.3 Monitoring Group Replication ......................................................................................... 3076
21.3.1 Replication_group_member_stats ........................................................................ 3077
21.3.2 Replication_group_members ............................................................................... 3078
21.3.3 Replication_connection_status ............................................................................ 3078
21.3.4 Replication_applier_status ................................................................................... 3079
21.3.5 Group Replication Server States ......................................................................... 3079
21.4 Group Replication Operations ........................................................................................ 3080
21.4.1 Deploying in Multi-Primary or Single-Primary Mode .............................................. 3080
21.4.2 Tuning Recovery ................................................................................................ 3082
21.4.3 Network Partitioning ........................................................................................... 3084
21.4.4 Using MySQL Enterprise Backup with Group Replication ...................................... 3089
21.5 Group Replication Security ............................................................................................ 3091
21.5.1 IP Address Whitelisting ....................................................................................... 3091
21.5.2 Secure Socket Layer Support (SSL) .................................................................... 3092
21.5.3 Virtual Private Networks (VPN) ........................................................................... 3096
21.6 Group Replication System Variables .............................................................................. 3096
21.7 Requirements and Limitations ........................................................................................ 3115
21.7.1 Group Replication Requirements ......................................................................... 3115
21.7.2 Group Replication Limitations .............................................................................. 3116
21.8 Frequently Asked Questions .......................................................................................... 3117
21.9 Group Replication Technical Details ............................................................................... 3121
21.9.1 Group Replication Plugin Architecture ................................................................. 3121
21.9.2 The Group ......................................................................................................... 3123
21.9.3 Data Manipulation Statements ............................................................................ 3123
21.9.4 Data Definition Statements ................................................................................. 3123
21.9.5 Distributed Recovery .......................................................................................... 3124
21.9.6 Observability ...................................................................................................... 3130
MySQL 8.0 Reference Manual
Pre-General
Availability
Draft xx
Pre-General
Availability
Draft
21.9.7 Group Replication Performance .......................................................................... 3131
22 InnoDB Cluster ........................................................................................................................ 3135
22.1 Introducing InnoDB Cluster ............................................................................................ 3135
22.2 Creating an InnoDB Cluster ........................................................................................... 3137
22.2.1 Deployment Scenarios ........................................................................................ 3137
22.2.2 InnoDB Cluster Requirements ............................................................................. 3138
22.2.3 Methods of Installing .......................................................................................... 3138
22.2.4 Sandbox Deployment of InnoDB Cluster .............................................................. 3139
22.2.5 Production Deployment of InnoDB Cluster ........................................................... 3142
22.2.6 Adopting a Group Replication Deployment ........................................................... 3150
22.3 Using MySQL Router with InnoDB Cluster ..................................................................... 3151
22.4 Working with InnoDB Cluster ......................................................................................... 3154
22.5 Known Limitations ......................................................................................................... 3165
23 Partitioning .............................................................................................................................. 3167
23.1 Overview of Partitioning in MySQL ................................................................................. 3168
23.2 Partitioning Types ......................................................................................................... 3171
23.2.1 RANGE Partitioning ............................................................................................ 3173
23.2.2 LIST Partitioning ................................................................................................. 3177
23.2.3 COLUMNS Partitioning ....................................................................................... 3179
23.2.4 HASH Partitioning .............................................................................................. 3187
23.2.5 KEY Partitioning ................................................................................................. 3191
23.2.6 Subpartitioning ................................................................................................... 3192
23.2.7 How MySQL Partitioning Handles NULL .............................................................. 3194
23.3 Partition Management ................................................................................................... 3198
23.3.1 Management of RANGE and LIST Partitions ........................................................ 3199
23.3.2 Management of HASH and KEY Partitions .......................................................... 3206
23.3.3 Exchanging Partitions and Subpartitions with Tables ............................................ 3207
23.3.4 Maintenance of Partitions ................................................................................... 3215
23.3.5 Obtaining Information About Partitions ................................................................. 3216
23.4 Partition Pruning ........................................................................................................... 3218
23.5 Partition Selection ......................................................................................................... 3221
23.6 Restrictions and Limitations on Partitioning ..................................................................... 3227
23.6.1 Partitioning Keys, Primary Keys, and Unique Keys ............................................... 3232
23.6.2 Partitioning Limitations Relating to Storage Engines ............................................. 3236
23.6.3 Partitioning Limitations Relating to Functions ....................................................... 3237
24 Stored Programs and Views ..................................................................................................... 3239
24.1 Defining Stored Programs ............................................................................................. 3240
24.2 Using Stored Routines (Procedures and Functions) ........................................................ 3241
24.2.1 Stored Routine Syntax ........................................................................................ 3242
24.2.2 Stored Routines and MySQL Privileges ............................................................... 3242
24.2.3 Stored Routine Metadata .................................................................................... 3243
24.2.4 Stored Procedures, Functions, Triggers, and LAST_INSERT_ID() ......................... 3243
24.3 Using Triggers .............................................................................................................. 3243
24.3.1 Trigger Syntax and Examples ............................................................................. 3244
24.3.2 Trigger Metadata ................................................................................................ 3248
24.4 Using the Event Scheduler ............................................................................................ 3248
24.4.1 Event Scheduler Overview .................................................................................. 3249
24.4.2 Event Scheduler Configuration ............................................................................ 3250
24.4.3 Event Syntax ...................................................................................................... 3252
24.4.4 Event Metadata .................................................................................................. 3252
24.4.5 Event Scheduler Status ...................................................................................... 3253
24.4.6 The Event Scheduler and MySQL Privileges ........................................................ 3253
24.5 Using Views .................................................................................................................. 3256
24.5.1 View Syntax ....................................................................................................... 3257
剩余5343页未读,继续阅读
2019-12-27 上传
2018-10-11 上传
2023-07-28 上传
2023-06-28 上传
2023-07-27 上传
2023-11-28 上传
2023-10-04 上传
2023-07-04 上传
alissn
- 粉丝: 1
- 资源: 1
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功