2026年10月 – 但行好事莫问前程

Oracle 19c如何将文件映射到物理设备

Posted on 2026年10月14日 by YongJing(敬勇)

Oracle如何将文件映射到物理设备
在数据文件为文件系统文件的环境中，要了解表空间和底层设备之间的关联是相对简单的。Oracle数据库提供了DBA_TABLESPACES、 DBA_DATA_FILES、V$DATAFILE等视图，提供了文件到设备的映射。这些映射和设备统计信息可以用来评估I/O性能。

然而，随着基于主机的逻辑卷管理器(LVM)和提供RAID (Redundant Array of Inexpensive Disks)特性的复杂存储子系统的引入，确定文件到设备的映射变得不容易。这带来了一个问题，因为当你的“最热门”文件隐藏在“黑盒”之后时，很难确定它们。本节介绍Oracle数据库解决这个问题的方法。

本节概述Oracle数据库文件映射接口，并解释如何使用DBMS_STORAGE_MAP包和动态性能视图来公开文件到物理设备的映射。您可以通过Oracle Enterprise Manager Cloud Control更轻松地访问此功能。它提供了一个易于使用的图形界面来映射文件到物理设备。有关更多信息，请参阅云控制在线帮助。

.Oracle数据库文件映射接口概述
为了了解I/O性能，必须详细了解文件所在的存储层次结构。

.Oracle文件映射接口是如何工作的
Oracle数据库文件映射包括以下组件:FMON(后台进程)、FMPUTL进程和映射库。

.使用Oracle数据库文件映射接口
您可以通过Oracle数据库文件映射接口启用文件映射，并在一组视图中获取有关文件映射的信息。

.映射文件例子
示例说明了Oracle数据库文件映射特性的一些强大功能。

Oracle数据库文件映射接口概述
要了解I/O性能，必须详细了解文件所在的存储层次结构。

Oracle数据库提供了一种机制来显示文件到逻辑卷中间层到实际物理设备的完整映射。这是通过一组动态性能视图(V$视图)来实现的。使用这些视图，可以定位文件的任何块所在的确切磁盘。

为了构建这些视图，存储供应商必须提供负责映射其特定I/O堆栈元素的映射库。数据库通过一个外部的非oracle数据库进程与这些库通信，该进程由一个名为FMON的后台进程派生。FMON负责管理映射信息。Oracle提供了一个PL/SQL包DBMS_STORAGE_MAP，您可以使用它来调用映射操作来填充映射视图。

注意:
如果您没有使用Oracle自动存储管理，那么文件映射接口在Windows平台上是不可用的。如果您使用Oracle自动存储管理，那么文件映射接口在所有平台上都是可用的。

Oracle文件映射接口是如何工作的
Oracle数据库文件映射包括以下组件:FMON(后台进程)、FMPUTL进程和映射库。
.文件映射组件:文件映射机制包括几个组件。

.映射结构:您必须了解映射结构和这些结构的Oracle数据库表示，才能解释映射视图中的信息。

.映射结构的例子:一个示例说明了映射结构。

.配置ID:配置ID捕获与元素或文件相关联的版本信息。

文件映射组件
文件映射机制包括几个组件。
.FMON
只要FILE_MAPPING初始化参数设置为true, FMON就是数据库启动的一个后台进程。FMON构建映射信息，并在发生变化时刷新映射信息。

.外部进程(FMPUTL)
FMON生成一个名为FMPUTL的外部非oracle数据库进程，该进程直接与厂商提供的映射库通信。

.映射库
Oracle数据库使用映射库来发现属于特定映射库的元素的映射信息。

FMON
只要FILE_MAPPING初始化参数设置为true, FMON就是数据库启动的一个后台进程。FMON构建映射信息，并在发生变化时刷新映射信息。

FMON负责:
.构建映射信息，存储在SGA中。该信息由以下结构组成:
– 文件
– 文件系统扩展
– 元素
– 子元素

.当由于以下原因发生变更时，刷新映射信息:
– 改变数据文件(大小)
– 添加或删除数据文件
– 改变存储配置(不频繁)

.将映射信息保存在数据字典中，以维护跨启动和关闭操作持久保存的信息视图

.在实例启动时将映射信息恢复到SGA。这避免了在每次实例启动时重新构建映射信息的潜在昂贵需求。

大家可以使用DBMS_STORAGE_MAP包调用的过程来帮助控制这个映射。

外部进程(FMPUTL)
FMON生成一个外部非oracle数据库进程FMPUTL，它直接与供应商提供的映射库通信。

该过程通过I/O栈的所有级别获取映射信息，假设所有级别都存在映射库。在某些平台上，外部进程需要将SETUID位设置为On，因为需要根权限才能映射到I/O映射栈的所有级别。

外部进程负责发现映射库，并将它们动态加载到其地址空间中。

映射库
Oracle数据库使用映射库来发现属于特定映射库的元素的映射信息。

通过这些映射库传递有关各个I/O栈元素的信息。此信息用于填充可由用户查询的动态性能视图。

映射库需要存在于栈的所有级别，以便映射完整，而且不同的库可能拥有自己的I/O映射栈部分。例如，VERITAS VxVM库将拥有与VERITAS卷管理器相关的堆栈元素，而EMC库将拥有I/O映射堆栈的所有EMC存储特定层。

映射库是供应商提供的。然而，Oracle目前为EMC存储提供了一个映射库。数据库服务器可用的映射库在名为filemap.ora的特殊文件中标识。

映射结构
您必须了解映射结构和这些结构的Oracle数据库表示，才能解释映射视图中的信息。以下是组成映射信息的主要结构:
.文件
文件映射结构为文件提供了一组属性，包括文件大小、文件组成的文件系统区段数量和文件类型。

.文件系统区段
文件系统区段映射结构描述驻留在一个元素上的连续块。这包括设备偏移量、区段大小、文件偏移量、类型(数据或奇偶校验)以及区段所在元素的名称。

注意:
文件系统的区与Oracle数据库的区不同。文件系统区段是写入由文件系统管理的设备的物理连续数据块。数据库区是由数据库管理的逻辑结构，如表空间区。

.元素
元素映射结构是描述I/O堆栈中的存储组件的抽象映射结构。元素可以是镜像、条带、分区、RAID5、连接的元素和磁盘。这些结构是映射的构建块。

.子元素
子元素映射结构描述了I/O映射堆栈中一个元素和下一个元素之间的链接。该结构包含子元素号、大小、子元素所在的元素名称和元素偏移量。
下面的示例说明了所有这些映射结构

映射结构的示例
一个示例说明了映射结构。

考虑一个Oracle数据库，它由两个数据文件X和y组成X和Y位于挂载在卷a上的文件系统上。文件X由两个区组成，而文件Y仅由一个区组成。

文件X的两个区间和文件Y的一个区间都映射到元素A。元素A被条带化到元素B和C。元素A分别通过子元素B0和C1映射到元素B和C。

元素C镜像到元素E和F(都是物理磁盘)上，并分别通过子元素E0和F1镜像到这些物理磁盘。

请注意，所表示的映射结构足以描述Oracle数据库实例的整个映射信息，从而将文件中的每个逻辑块映射到I/O堆栈中每个级别的(元素名称、元素偏移量)元组(如果是镜像，则更多)。

配置ID
配置ID捕获与元素或文件相关的版本信息。

供应商库提供配置ID，并在发生更改时更新它。如果没有配置ID，数据库就无法判断映射是否已更改。

配置id有两种:
.持久
这些配置id在实例关闭期间是持久的

.非持久
配置id在实例关闭期间不持久。数据库只能在实例启动时刷新映射信息。

使用Oracle数据库文件映射接口
您可以通过Oracle数据库文件映射接口启用文件映射，并在一组视图中获取有关文件映射的信息。

启用文件映射

为了启用文件映射。
1.确保filemap.ora文件是有效的。对于32位平台，在/opt/ORCLfmap/prot1_32/etc目录下;对于64位平台，在/opt/ORCLfmap/prot1_64/etc目录下。

[root@19c1 /]# find / -name filemap.ora
/opt/ORCLfmap/prot1_64/etc/filemap.ora
/u01/app/19.0.0/grid/rdbms/install/filemap.ora
/u01/app/oracle/19.0.0/db/rdbms/install/filemap.ora
[root@19c1 /]# cat /opt/ORCLfmap/prot1_64/etc/filemap.ora
###############################################################################
#                                                                             #
# This is the configuration file that describes all the mapping libraries     #
# available.                                                                  #
#                                                                             #
# The following row needs to be created for each library:                     #
# lib={vendor name}: {mapping library path}                                   #
#                                                                             #
# Note that the ordering of the libraries in this file is extremely           #
# important. The libraries are queried based on their order in the            #
# configuration file.                                                         #
#                                                                             #
# Oracle provides a mapping library for EMC Symmetrix arrays. This library    #
# (LIBMAPSYM.SO) uses the SYMAPI and SYMLVM EMC libraries.                    #
#                                                                             #
# UNCOMMENT THE ROW CORRESPONDING TO THAT LIBRARY ONLY IF THE SYM LIBRARIES   #
# ARE AVAILABLE ON SOLARIS.                                                   #
###############################################################################

## The next row needs to be commented out only for Solaris on EMC storage
#lib=Oracle: /u01/app/19.0.0/grid/lib/%s_mapsym%

## The next row can be comented out on ALL UNIX PLATFORMS if no other
## libraries exist
#lib=Oracle: /u01/app/19.0.0/grid/lib/%s_mapdummy%

注意:虽然这里讨论了文件映射表（filemap.ora）文件的格式和内容，但仅供参考。文件映射表（filemap.ora）文件是由数据库在系统安装时创建的。在供应商提供自己的库之前，文件映射表（filemap.ora）文件中将只有一条记录，即Oracle提供的EMC库。如果拥有EMC Symmetrix阵列，则应手动修改此文件，仅需取消注释此条记录。

filemap.ora文件是描述所有可用映射库的配置文件。 FMON 要求存在一个 `filemap.ora` 文件，并且该文件指向一个有效的映射库路径。否则，FMON 将无法成功启动。

filemap中必须包含下一行。
lib=vendor_name:mapping_library_path

这里;
. vendor_name应该是Oracle对EMC的对称库
. mapping_library_path是映射库的全路径

请注意，这个文件中库的顺序非常重要。库是根据它们在配置文件中的顺序查询的。

即使没有可用的映射库，也可以启动文件映射服务。filemap.ora文件仍然必须存在，即使它是空的。在这种情况下，映射服务受到限制，因为不能发现新的映射信息。在这种配置中，只允许恢复和删除操作。

2.将FILE_MAPPING初始化参数设置为TRUE。
不必关闭实例即可设置此参数。您可以使用下面的ALTER SYSTEM语句设置它:

SQL> show parameter file_mapping

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
file_mapping                         boolean     FALSE
SQL> ALTER SYSTEM SET FILE_MAPPING=TRUE scope=both sid='*';

System altered.

SQL> show parameter file_mapping

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
file_mapping                         boolean     TRUE


[root@19c1 ~]# ps -ef | grep fmon
oracle   15552     1  0 15:24 ?        00:00:00 ora_fmon_hy1
root     16269 16207  0 15:24 pts/1    00:00:00 grep --color=auto fmon

3.调用适当的DBMS_STORAGE_MAP映射过程。你有两个选择：
.在冷启动场景中，Oracle数据库刚刚启动，尚未调用任何映射操作。执行DBMS_STORAGE_MAP.MAP_ALL过程为与数据库关联的整个I/O子系统构建映射信息。

.在已经构建了映射信息的热启动场景中，您可以选择调用DBMS_STORAGE_MAP.MAP_SAVE过程将映射信息保存到数据字典中。(注意，这个过程是在调用DBMS_STORAGE_MAP.MAP_ALL（）过程中默认调用的)。这将强制将SGA中的所有映射信息刷新到磁盘。

重新启动数据库后，使用DBMS_STORAGE_MAP.RESTORE（）将映射信息恢复到SGA中。如果需要,可以调用DBMS_STORAGE_MAP.MAP_ALL（）来刷新映射信息。

使用DBMS_STORAGE_MAP包
DBMS_STORAGE_MAP包允许您控制映射操作。
下表描述了您可以使用的各种程序。
MAP_OBJECT:构建由对象名称、所有者和类型标识的数据库对象的映射信息

MAP_ELEMENT:
为指定元素生成映射信息

MAP_FILE:为指定的文件名生成映射信息

MAP_ALL:为所有类型的数据库文件构建完整的映射信息（存档日志除外）

DROP_ELEMENT:删除指定元素的映射信息

DROP_FILE:删除指定文件名的文件映射信息

DROP_ALL:删除此实例的SGA中的所有映射信息

SAVE:将重新生成整个映射所需的信息保存到数据字典中

RESTORE:将数据字典中的整个映射信息加载到实例的共享内存中

LOCK_MAP:在SGA中锁定此实例的映射信息

UNLOCK_MAP:解锁该实例在SGA中的映射信息

从文件映射视图获取信息
DBMS_STORAGE_MAP包生成的映射信息在动态性能视图中捕获。
V$MAP_LIBRARY:包含由外部进程动态加载的所有映射库的列表

V$MAP_FILE:包含实例的共享内存中所有文件映射结构的列表

V$MAP_FILE_EXTENT:包含实例的共享内存中所有文件系统区段映射结构的列表

V$MAP_ELEMENT:包含实例的SGA中所有元素映射结构的列表

V$MAP_EXT_ELEMENT:包含所有元素映射的补充信息

V$MAP_SUBELEMENT:包含实例共享内存中所有子元素映射结构的列表

V$MAP_COMP_LIST:包含所有元素映射结构的补充信息。

V$MAP_FILE_IO_STACK:文件存储容器的层次结构排列显示为一系列行。每行表示层次结构中的一个级别。

然而，由 DBMS_STORAGE_MAP.MAP_OBJECT 过程生成的信息会被捕获到一个名为 MAP_OBJECT 的全局临时表中。此表展示了对象存储容器的层级排列。表中的每一行代表层级结构中的一个层级。以下是对 MAP_OBJECT 表的描述。

SQL> desc MAP_OBJECT;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJECT_NAME                                        VARCHAR2(2000)
 OBJECT_OWNER                                       VARCHAR2(2000)
 OBJECT_TYPE                                        VARCHAR2(2000)
 FILE_MAP_IDX                                       NUMBER
 DEPTH                                              NUMBER
 ELEM_IDX                                           NUMBER
 CU_SIZE                                            NUMBER
 STRIDE                                             NUMBER
 NUM_CU                                             NUMBER
 ELEM_OFFSET                                        NUMBER
 FILE_OFFSET                                        NUMBER
 DATA_TYPE                                          VARCHAR2(2000)
 PARITY_POS                                         NUMBER
 PARITY_PERIOD                                      NUMBER

内存映射文件例子
示例说明了Oracle数据库文件映射特性的一些强大功能。

这些功能包括：
.映射跨特定设备的所有数据库文件的能力

.将特定文件映射到相应设备的能力

.映射特定数据库对象的能力，包括它在I/O堆栈内所有级别上的块分布

例1：映射跨设备的所有数据库文件
一个示例说明返回与主机设备关联的所有Oracle数据库文件。

下面的查询将返回与+/dev/asmdisk02主机设备关联的所有Oracle数据库文件：

SQL> SELECT UNIQUE me.ELEM_NAME, mf.FILE_NAME
  2  FROM V$MAP_FILE_IO_STACK fs, V$MAP_FILE mf, V$MAP_ELEMENT me
  3  WHERE mf.FILE_MAP_IDX = fs.FILE_MAP_IDX
  4  AND me.ELEM_IDX = fs.ELEM_IDX
  5  AND me.ELEM_NAME =  '+/dev/asmdisk02';

ELEM_NAME            FILE_NAME
-------------------- --------------------------------------------------------------------------------
+/dev/asmdisk02      +DATA/HY/DATAFILE/sysaux.259.1035285279
+/dev/asmdisk02      +DATA/HY/811A060B58140C01E053DC06E80AF3C6/DATAFILE/undotbs1.268.1035285455
+/dev/asmdisk02      +DATA/HY/DATAFILE/undotbs1.260.1035285305
+/dev/asmdisk02      +DATA/HY/811A060B58140C01E053DC06E80AF3C6/DATAFILE/system.266.1035285455
+/dev/asmdisk02      +DATA/HY/811A060B58140C01E053DC06E80AF3C6/DATAFILE/sysaux.267.1035285455
+/dev/asmdisk02      +DATA/HY/A105A63457DE7172E053BF828A0A80E2/DATAFILE/sysaux.276.1035286627
+/dev/asmdisk02      +DATA/HY/A105A63457DE7172E053BF828A0A80E2/DATAFILE/undotbs1.274.1035286627
+/dev/asmdisk02      +DATA/HY/DATAFILE/system.258.1035285225
+/dev/asmdisk02      +DATA/HY/DATAFILE/users.261.1035285305
+/dev/asmdisk02      +DATA/HY/DATAFILE/undotbs2.270.1035285777
+/dev/asmdisk02      +DATA/HY/A105A63457DE7172E053BF828A0A80E2/DATAFILE/system.275.1035286627
+/dev/asmdisk02      +DATA/HY/A105A63457DE7172E053BF828A0A80E2/DATAFILE/undo_2.278.1035286673
+/dev/asmdisk02      +DATA/HY/A105A63457DE7172E053BF828A0A80E2/DATAFILE/users.279.1035286689

13 rows selected.

例2：将文件映射到对应的设备
示例显示数据文件的拓扑图。

下面的查询显示了/oracle/dbs/t_db1的拓扑图。F数据文件：

FILE_NAME               ELEM_NAME
----------------------- -------------------------------------------------
/oracle/dbs/t_db1.f    _sym_plex_/dev/vx/rdsk/ipfdg/ipf-vol1_-1_-1
/oracle/dbs/t_db1.f       _sym_subdisk_/dev/vx/rdsk/ipfdg/ipf-vol1_0_0_0
/oracle/dbs/t_db1.f          /dev/vx/rdmp/c2t1d0s2
/oracle/dbs/t_db1.f            _sym_symdev_000183600407_00C
/oracle/dbs/t_db1.f               _sym_hyper_000183600407_00C_0
/oracle/dbs/t_db1.f               _sym_hyper_000183600407_00C_1
/oracle/dbs/t_db1.f       _sym_subdisk_/dev/vx/rdsk/ipfdg/ipf-vol1_0_1_0
/oracle/dbs/t_db1.f          /dev/vx/rdmp/c2t1d1s2
/oracle/dbs/t_db1.f            _sym_symdev_000183600407_00D
/oracle/dbs/t_db1.f              _sym_hyper_000183600407_00D_0
/oracle/dbs/t_db1.f              _sym_hyper_000183600407_00D_1

示例3：映射数据库对象
一个示例显示了一个表的I/O堆栈中所有级别的块分布。

这个例子显示了scott.bonus表的I/O堆栈中所有级别的块分布。

MAP_OBJECT（）操作必须先执行如下：

SQL> EXECUTE DBMS_STORAGE_MAP.MAP_OBJECT('BONUS','SCOTT','TABLE');

PL/SQL procedure successfully completed.


SQL> SELECT io.OBJECT_NAME o_name, io.OBJECT_OWNER o_owner, io.OBJECT_TYPE o_type,
  2  mf.FILE_NAME, me.ELEM_NAME, io.DEPTH,
  3  (SUM(io.CU_SIZE * (io.NUM_CU - DECODE(io.PARITY_PERIOD, 0, 0,
  4  TRUNC(io.NUM_CU / io.PARITY_PERIOD)))) / 2) o_size
  5  FROM MAP_OBJECT io, V$MAP_ELEMENT me, V$MAP_FILE mf
  6  WHERE io.OBJECT_NAME = 'MGMT_SNAPSHOT'
  7  AND io.OBJECT_OWNER = 'DBSNMP'
  8  AND io.OBJECT_TYPE = 'TABLE'
  9  AND me.ELEM_IDX = io.ELEM_IDX
 10  AND mf.FILE_MAP_IDX = io.FILE_MAP_IDX
 11  GROUP BY io.ELEM_IDX, io.FILE_MAP_IDX, me.ELEM_NAME, mf.FILE_NAME, io.DEPTH,
 12  io.OBJECT_NAME, io.OBJECT_OWNER, io.OBJECT_TYPE
 13  ORDER BY io.DEPTH;

O_NAME O_OWNER O_TYPE FILE_NAME           ELEM_NAME                     DEPTH  O_SIZE
------ ------- ------ ------------------- ----------------------------- ------ ------
BONUS SCOTT    TABLE  /oracle/dbs/t_db1.f dev/vx/dsk/ipfdg/ipf-vol1     0      20
BONUS SCOTT    TABLE  /oracle/dbs/t_db1.f _sym_plex_/dev/vx/rdsk/ipf    1      20
                                          pdg/if-vol1_-1_-1
BONUS SCOTT    TABLE  /oracle/dbs/t_db1.f _sym_subdisk_/dev/vx/rdsk/    2      12
                                          ipfdg/ipf-vol1_0_1_0
BONUS SCOTT    TABLE /oracle/dbs/t_db1.f  _sym_subdisk_/dev/vx/rdsk/ipf 2      8
                                          dg/ipf-vol1_0_2_0
BONUS SCOTT    TABLE /oracle/dbs/t_db1.f /dev/vx/rdmp/c2t1d1s2          3      12
BONUS SCOTT    TABLE /oracle/dbs/t_db1.f /dev/vx/rdmp/c2t1d2s2          3      8
BONUS SCOTT    TABLE /oracle/dbs/t_db1.f _sym_symdev_000183600407_00D   4      12
BONUS SCOTT    TABLE /oracle/dbs/t_db1.f _sym_symdev_000183600407_00E   4      8
BONUS SCOTT    TABLE /oracle/dbs/t_db1.f _sym_hyper_000183600407_00D_0  5      12
BONUS SCOTT    TABLE /oracle/dbs/t_db1.f _sym_hyper_000183600407_00D_1  5      12
BONUS SCOTT    TABLE /oracle/dbs/t_db1.f _sym_hyper_000183600407_00E_0  6      8
BONUS SCOTT    TABLE /oracle/dbs/t_db1.f _sym_hyper_000183600407_00E_1  6      8

MySQL 5.7 存储程序的二进制日志

Posted on 2026年10月14日 by YongJing(敬勇)

存储程序的二进制日志
二进制日志包含修改数据库内容的SQL语句信息。这些信息以描述修改的“事件”的形式存储。二进制日志有两个重要的目的。
.对于复制，主复制服务器使用二进制日志记录要发送到从服务器的语句。主服务器将包含在其二进制日志中的事件发送给从服务器，从服务器执行这些事件以进行与主服务器相同的数据更改。

.某些数据恢复操作需要使用二进制日志。恢复备份文件后，将重新执行二进制日志中记录的备份后的事件。这些事件使数据库从备份点更新到最新状态。

但是，如果日志记录发生在语句级别，存储程序（存储过程和函数、触发器和事件）会出现某些二进制日志记录问题：
.在某些情况下，一条语句可能会影响主从端的不同行集。

.在从服务器上执行的复制语句由具有完全特权的从SQL线程处理。一个过程可能在主服务器和从服务器上遵循不同的执行路径，因此用户可以编写包含危险语句的例程，该例程仅在由具有完全特权的线程处理的从服务器上执行

.如果修改数据的存储程序是不确定的，则它是不可重复的。这可能导致主服务器和从服务器上的数据不同，或者导致恢复的数据与原始数据不同。

本节描述MySQL如何处理存储程序的二进制日志记录。它陈述了实现对存储程序使用的当前条件，以及您可以做些什么来避免日志记录问题。它还提供了关于这些条件的原因的额外信息。
一般来说，当SQL语句级别发生二进制日志记录时，就会出现这里描述的问题。如果使用基于行的二进制日志，则日志包含由于执行SQL语句而对单个行所做的更改。当例程或触发器执行时，记录行更改，而不是进行更改的语句。对于存储过程，这意味着不会记录CALL语句。对于存储函数，在函数内所做的行更改将被记录，而不是函数调用。对于触发器，触发器所做的行更改将被记录。在从端，只会看到行上的变化，而不会看到对存储程序的调用。

除非另有说明，否则这里的注释假定您通过使用——log-bin选项启动服务器，从而启用了二进制日志记录。如果未启用二进制日志，则无法进行复制，也无法使用二进制日志进行数据恢复。

在MySQL中使用存储函数的条件可以总结如下。这些条件不适用于存储过程或Event Scheduler事件，除非启用了二进制日志记录，否则这些条件不适用。

.要创建或修改存储函数，除了具有create权限外，还必须具有SUPER权限通常需要的ROUTINE或ALTER ROUTINE权限。(根据函数定义中的DEFINER 值，无论是否启用二进制日志记录，都可能需要SUPER。）

.创建存储函数时，必须声明它是确定性的，或者声明它不修改数据。否则，可能导致数据恢复或复制不安全。

默认情况下，要接受一个CREATE FUNCTION语句，至少要满足DETERMINISTIC、NO SQL或READS SQL DATA必须显式指定。否则会出现错误：

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL,
or READS SQL DATA in its declaration and binary logging is enabled
(you *might* want to use the less safe log_bin_trust_function_creators
variable)

这个函数是确定的（并且不修改数据），所以它是安全的：

CREATE FUNCTION f1(i INT)
RETURNS INT
DETERMINISTIC
READS SQL DATA
BEGIN
RETURN i;
END;

这个函数使用不确定的UUID()，所以这个函数也不确定，也不安全：

CREATE FUNCTION f2()
RETURNS CHAR(36) CHARACTER SET utf8
BEGIN
RETURN UUID();
END;

这个函数修改数据，所以它可能不安全：

CREATE FUNCTION f3(p_id INT)
RETURNS INT
BEGIN
UPDATE t SET modtime = NOW() WHERE id = p_id;
RETURN ROW_COUNT();
END;

对函数性质的评估是基于创建者的“诚实”：MySQL不会检查被声明为DETERMINISTIC性的函数是否没有产生不确定性结果的语句。

.虽然可以在不指定deterministic的情况下创建确定性存储函数，但不能使用基于语句的二进制日志记录执行该函数。要执行这样的函数，必须使用基于行或混合二进制日志记录。另外，如果在函数定义中显式指定DETERMINISTIC，则可以使用任何类型的日志记录，包括基于语句的日志记录。

.要放宽上述创建函数的条件（必须具有SUPER权限，并且必须声明函数是确定的或不能修改数据），请将全局 log_bin_trust_function_creators系统变量设置为1。默认情况下，这个变量的值为0，但你可以这样修改它：

mysql> SET GLOBAL log_bin_trust_function_creators = 1;

您也可以在启动服务器时使用–log-bin-trust-function-creators=1选项来设置这个变量。

.有关可能对复制不安全的内置函数（从而导致使用它们的存储函数也不安全）的信息，请参阅第16.4.1节，“复制功能和问题”。

触发器类似于存储函数，因此前面关于函数的注释也适用于触发器，但有以下例外：CREATE TRIGGER没有可选的DETERMINISTIC特征，因此假定触发器始终是确定性的。然而，这种假设在某些情况下可能是无效的。例如，UUID（）函数是不确定的（并且不复制）。在触发器中使用这类函数时要小心。

触发器可以更新表，因此，如果您没有所需的权限，则CREATE TRIGGER会出现与存储函数类似的错误消息。在从服务器端，从服务器使用触发器 DEFINER属性来确定哪个用户被认为是触发器的创建者。
本节的其余部分将提供有关日志记录实现及其含义的更多细节。除非您对存储例程使用中当前与日志相关的条件的基本原理的背景知识感兴趣，否则您不需要阅读它。本讨论仅适用于基于语句的日志记录，而不适用于基于行的日志记录，但第一项除外：无论日志记录模式如何，CREATE和 DROP语句都作为语句记录。

.服务器写入CREATE EVENT, CREATE PROCEDURE, CREATE FUNCTION, ALTER EVENT, ALTER PROCEDURE, ALTER FUNCTION, DROP EVENT, DROP PROCEDURE,DROP FUNCTION语句到二进制日志中。

.如果函数更改了数据并且发生在语句中，那么存储函数调用将被记录为SELECT语句，否则不会被记录。这可以防止由于在非日志语句中使用存储函数而导致的数据更改的非复制。例如，SELECT语句不会写入二进制日志，但是SELECT语句可能会调用进行更改的存储函数。为了处理这个问题，当给定函数进行更改时，将SELECT func_name（）语句写入二进制日志。假设在主服务器上执行以下语句：

CREATE FUNCTION f1(a INT) RETURNS INT
BEGIN
IF (a < 3) THEN
INSERT INTO t2 VALUES (a);
END IF;
RETURN 0;
END;

CREATE TABLE t1 (a INT);

INSERT INTO t1 VALUES (1),(2),(3);

SELECT f1(a) FROM t1;

在执行SELECT语句时，函数f1（）被调用三次。其中两个调用插入一行，MySQL为每个调用记录一个SELECT语句。也就是说，MySQL将以下语句写入二进制日志：

SELECT f1(1);
SELECT f1(2);

当存储函数调用导致错误的存储过程时，服务器还会记录存储函数调用的SELECT语句。在这种情况下，服务器将SELECT语句连同预期的错误代码一起写入日志。在从属服务器上，如果出现相同的错误，这就是预期的结果，复制将继续进行。否则，复制停止。
.记录存储函数调用，而不是记录函数执行的语句，对复制有安全隐患，这源于两个因素：
.一个函数可能在主服务器和从服务器上遵循不同的执行路径。

.在从属线程上执行的语句由具有完全权限的从属SQL线程处理。

这意味着，尽管用户必须具有CREATE ROUTINE权限才能创建函数，但用户可以编写一个包含危险语句的函数，该语句将只在具有完全权限的线程处理的从机上执行。例如，如果主服务器和从服务器的服务器ID分别为1和2，则主服务器上的用户可以创建并调用不安全函数unsafe_func()，如下所示：

mysql> delimiter //
mysql> CREATE FUNCTION unsafe_func () RETURNS INT
-> BEGIN
-> IF @@server_id=2 THEN dangerous_statement; END IF;
-> RETURN 1;
-> END;
-> //
mysql> delimiter ;
mysql> INSERT INTO t VALUES(unsafe_func());

CREATE FUNCTION和INSERT语句被写入二进制日志，因此从属服务器将执行它们。因为从SQL线程拥有全部特权，所以它将执行危险的语句。因此，函数调用对主服务器和从服务器有不同的影响，并且不是复制安全的。

为了防止启用二进制日志记录的服务器出现这种危险，存储函数创建者除了需要通常的CREATE ROUTINE权限外，还必须具有SUPER权限。类似地，要使用ALTER FUNCTION，除了具有ALTER ROUTINE权限外，还必须具有SUPER权限。如果没有SUPER权限，将会出现错误：

ERROR 1419 (HY000): You do not have the SUPER privilege and
binary logging is enabled (you *might* want to use the less safe
log_bin_trust_function_creators variable)

如果您不想要求函数创建者拥有SUPER权限（例如，如果在您的系统上具有CREATE ROUTINE权限的所有用户都是经验丰富的应用程序开发人员），则将全局log_bin_trust_function_creators系统变量设置为1。您也可以在启动服务器时使用–log-bin-trust-function-creators=1选项来设置此变量。如果没有启用二进制日志记录，log_bin_trust_function_creators将不适用。创建函数时不需要SUPER，除非如前所述，函数定义中的DEFINER值需要它。

.如果执行更新的函数是不确定的，则它是不可重复的。这可能会产生两种不良影响：
.它将使从服务器与主服务器不同。

.恢复后的数据将与原始数据不同。

为了处理这些问题，MySQL强制执行以下要求：在主服务器上，除非您声明函数是确定的或不修改数据，否则拒绝创建和更改函数。这里适用两组函数特征：
.DETERMINISTIC和NOT DETERMINISTIC特征表明对于给定的输入，函数是否总是产生相同的结果。如果两个特征都没有给出，默认值是NOT DETERMINISTIC。要声明函数是确定性的，必须显式指定deterministic。

.CONTAINS SQL、NO SQL、READS SQL DATA和modified SQL DATA特性提供了函数是否读取或写入数据的信息。NO SQL或READS SQL DATA表示函数不更改数据，但您必须显式指定其中一个，因为如果没有给定特征，默认值是CONTAINS SQL。

默认情况下，要接受一个CREATE FUNCTION语句，至少要满足DETERMINISTIC、NO SQL或READS SQL DATA必须显式指定。否则会出现错误：

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL,
or READS SQL DATA in its declaration and binary logging is enabled
(you *might* want to use the less safe log_bin_trust_function_creators
variable)

如果将log_bin_trust_function_creators设置为1，则不再要求函数是确定性的或不修改数据。

.存储过程调用在语句级别记录，而不是在CALL级别记录。也就是说，服务器不记录CALL语句，而是记录实际执行的过程中的那些语句。因此，在主服务器上发生的相同更改将在从服务器上被观察到。这可以防止由于过程在不同的机器上具有不同的执行路径而导致的问题。

通常，在存储过程中执行的语句将使用与以独立方式执行的语句相同的规则写入二进制日志。在记录过程语句时需要特别注意，因为过程中的语句执行与非过程上下文中的语句执行不完全相同：
.要记录的语句可能包含对局部过程变量的引用。这些变量不存在于存储过程上下文之外，因此引用此类变量的语句不能按字面记录。相反，为了记录日志，每个对局部变量的引用都被这个结构替换：

NAME_CONST(var_name, var_value)

Var_name是本地变量名，var_value是一个常量，指示该变量在记录语句时所具有的值。NAME_CONST（）有一个var_value值和一个
var_name的“name”。因此，如果你直接调用这个函数，你会得到这样的结果：

mysql> SELECT NAME_CONST('myname', 14);
+--------+
| myname |
+--------+
|     14 |
+--------+
1 row in set (0.00 sec)

NAME_CONST（）允许在从机上执行已记录的独立语句，其效果与存储过程中在主机上执行的原始语句相同。

使用NAME_CONST（）可能会导致CREATE TABLE…SELECT语句当源列表达式引用局部变量时，。将这些引用转换为NAME_CONST（）表达式可能导致主服务器和从服务器上的列名不同，或者列名太长而不能作为合法的列标识符。一种解决方法是为引用局部变量的列提供别名。考虑当myvar的值为1时的这个语句：

CREATE TABLE t1 SELECT myvar;

它将被重写如下：

CREATE TABLE t1 SELECT NAME_CONST(myvar, 1);

为了确保主表和从表具有相同的列名，编写这样的语句：

CREATE TABLE t1 SELECT myvar AS myvar;

改写后的语句变成：

CREATE TABLE t1 SELECT NAME_CONST(myvar, 1) AS myvar;

.要记录的语句可能包含对用户定义变量的引用。为了解决这个问题，MySQL在二进制日志中写入一个SET语句，以确保从服务器上的变量与主服务器上的变量值相同。例如，如果有一条语句引用了变量@my_var，那么在二进制日志中，这条语句的前面会有以下语句，其中value是@my_var 在master上的值：

SET @my_var = value;

.过程调用可以发生在已提交或回滚的事务中。考虑了事务上下文，以便正确地复制过程执行的事务方面。也就是说，服务器在实际执行和修改数据的过程中记录这些语句，并在必要时记录BEGIN、COMMIT和ROLLBACK语句。例如，如果过程只更新事务表，并且在回滚的事务中执行，则不会记录这些更新。如果该过程发生在已提交的事务中，则BEGIN和COMMIT语句将随更新一起记录。对于在回滚事务中执行的过程，其语句使用与以独立方式执行语句相同的规则记录：
.对事务表的更新不记录日志。

.对非事务性表的更新要记录日志，因为回滚不会取消它们。

.对于事务表和非事务表的混合更新都被记录在BEGIN和ROLLBACK中，这样从服务器就可以和主服务器一样进行相同的更改和回滚。

.如果从存储函数中调用存储过程，则不会在语句级别将存储过程调用写入二进制日志。在这种情况下，唯一记录的是调用函数的语句（如果它发生在记录日志的语句中）或DO语句（如果它发生在没有记录日志的语句中）。因此，在使用调用过程的存储函数时应该谨慎，即使过程本身是安全的。

MySQL 5.7 存储程序和视图的访问控制

Posted on 2026年10月14日 by YongJing(敬勇)

存储程序和视图的访问控制
存储程序和视图是在使用之前定义的，当被引用时，会在一个安全上下文中执行，这个安全上下文中决定了它们的权限。这些权限由它们的 DEFINER属性控制，如果有，则由它们的SQL SECURITY控制。

所有存储程序（过程、函数、触发器和事件）和视图都可以有一个DEFINER属性来命名MySQL帐户。如果在存储程序或视图定义中省略了DEFINER 属性，则默认帐户是创建对象的用户。

触发器和事件没有SQL SECURITY特性，总是在定义器上下文中执行。服务器根据需要自动调用这些对象，因此不存在调用用户。

定义器和调用程序安全上下文的区别如下：
.在definer安全上下文中执行的存储程序或视图使用由其definer属性命名的帐户的特权执行。这些权限可能与调用用户的权限完全不同。调用者必须具有适当的权限来引用对象（例如，EXECUTE以调用存储过程，或SELECT以从视图中选择），但是当对象执行时，调用者的权限被忽略，只有定义者帐户权限重要。如果该帐户的权限很少，则对象可以执行的操作就会相应地受到限制。如果定义者帐户具有高度特权（例如root帐户），那么无论谁调用它，对象都可以执行强大的操作。

.在调用者安全上下文中执行的存储例程或视图只能执行调用者具有权限的操作。可以指定DEFINER属性，但对在调用者上下文中执行的对象无效。
考虑下面的存储过程：

CREATE DEFINER = 'admin'@'localhost' PROCEDURE p1()
SQL SECURITY DEFINER
BEGIN
UPDATE t1 SET counter = counter + 1;
END;

任何拥有p1的EXECUTE权限的用户都可以用CALL语句调用它。然而，当p1执行时，它是在definer安全上下文中执行的，因此以` admin ` @ ` localhost `的特权执行，该帐户在definer属性中命名。该帐户必须具有p1的EXECUTE权限以及表t1的UPDATE权限。否则，该过程将失败。

现在考虑这个存储过程，它与p1相同，只是它的SQL SECURITY是INVOKER：

CREATE DEFINER = 'admin'@'localhost' PROCEDURE p2()
SQL SECURITY INVOKER
BEGIN
UPDATE t1 SET counter = counter + 1;
END;

与p1不同，P2在invoker安全上下文中执行。DEFINER属性是不相关的，p2以调用用户的权限执行。如果调用者缺少p2的EXECUTE特权或表t1的 UPDATE特权，p2就会失败。

MySQL使用以下规则来控制用户可以在对象DEFINER属性中指定哪些帐户：
.只有当您具有SUPER权限时，才能指定除您自己的帐户之外的DEFINER值。

.如果您没有SUPER权限，那么唯一合法的用户值就是您自己的帐户，可以直接功或使用CURRENT_USER指定，您不能将定义器设置为其他帐户。

为了尽量减少存储程序和视图创建和使用的潜在风险，请遵循以下指导方针
.对于存储例程或视图，尽可能在对象定义中使用SQL SECURITY INVOKER，以便仅由具有该对象执行的操作的适当权限的用户使用。

.如果在使用具有SUPER权限的帐户时创建定义上下文存储的程序或视图，请指定一个显式的DEFINER属性，该属性指定仅拥有该对象执行的操作所需权限的帐户。只有在绝对必要时才指定具有高度特权的DEFINER帐户。

.管理员可以通过不授予用户SUPER权限来阻止用户指定高权限的DEFINER帐户。

.在编写定义器-上下文对象时，应牢记它们可能能够访问调用用户没有特权的数据。在某些情况下，可以通过不授予未授权用户特定权限来阻止对这些对象的引用：
.没有EXECUTE权限的用户不能引用存储过程或函数。

.视图不能被没有相应权限的用户引用（SELECT用于从中选择，INSERT用于向其中插入，等等）。

但是，触发器不存在这种控制，因为用户不直接引用它们。触发器总是在definer上下文中执行，通过访问与其关联的表来激活，即使是没有特权的用户访问普通表也可以。如果定义帐户是高度特权，触发器可以执行敏感或危险的操作。如果从创建触发器的用户的帐户中撤销了创建触发器所需的SUPER和TRIGGER权限，则仍然如此。管理员在授予用户这种特权组合时应该特别小心。