360SDN.COM

首页/MySQL/列表

MySQL 8.0 Server System Variables【二】英文

来源:  2018-08-02 20:03:47    评论:0点击:

来源:https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_use_secondary_engine


 Server System Variables


  • general_log_file

    Property Value
    Command-Line Format --general-log-file=file_name
    System Variable general_log_file
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type file name
    Default Value host_name.log

    The name of the general query log file. The default value is host_name.log, but the initial value can be changed with the --general_log_file option.

  • group_concat_max_len

    Property Value
    Command-Line Format --group-concat-max-len=#
    System Variable group_concat_max_len
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies Yes
    Type integer
    Default Value 1024
    Minimum Value 4
    Maximum Value (64-bit platforms) 18446744073709551615
    Maximum Value (32-bit platforms) 4294967295

    The maximum permitted result length in bytes for the GROUP_CONCAT() function. The default is 1024.

  • have_compress

    YES if the zlib compression library is available to the server, NO if not. If not, the COMPRESS() and UNCOMPRESS() functions cannot be used.

  • have_crypt

    This system variable was removed in MySQL 8.0.3.

  • have_dynamic_loading

    YES if mysqld supports dynamic loading of plugins, NO if not. If the value is NO, you cannot use options such as --plugin-load to load plugins at server startup, or the INSTALL PLUGIN statement to load plugins at runtime.

  • have_geometry

    YES if the server supports spatial data types, NO if not.

  • have_openssl

    This variable is an alias for have_ssl.

  • have_profiling

    YES if statement profiling capability is present, NO if not. If present, the profiling system variable controls whether this capability is enabled or disabled. See Section 13.7.6.31, “SHOW PROFILES Syntax”.

    This variable is deprecated and will be removed in a future MySQL release.

  • have_query_cache

    The query cache was removed in MySQL 8.0.3. have_query_cache is deprecated, always has a value of NO, and will be removed in a future MySQL release.

  • have_rtree_keys

    YES if RTREE indexes are available, NO if not. (These are used for spatial indexes in MyISAM tables.)

  • have_ssl

    YES if mysqld supports SSL connections, NO if not. DISABLED indicates that the server was compiled with SSL support, but was not started with the appropriate --ssl-xxx options. For more information, see Section 6.4.5, “Building MySQL with Support for Encrypted Connections”.

  • have_statement_timeout

    Property Value
    System Variable have_statement_timeout
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type boolean

    Whether the statement execution timeout feature is available (see Statement Execution Time Optimizer Hints). The value can be NO if the background thread used by this feature could not be initialized.

  • have_symlink

    YES if symbolic link support is enabled, NO if not. This is required on Unix for support of the DATA DIRECTORY and INDEX DIRECTORY table options. If the server is started with the --skip-symbolic-links option, the value is DISABLED.

    This variable has no meaning on Windows.

    Note

    Symbolic link support, along with the the --symbolic-links option that controls it, is deprecated and will be removed in a future version of MySQL. In addition, the option is disabled by default. The relatedhave_symlink system variable also is deprecated and will be removed in a future version of MySQL.

  • histogram_generation_max_mem_size

    Property Value
    Command-Line Format --histogram-generation-max-mem-size=#
    Introduced 8.0.2
    System Variable histogram_generation_max_mem_size
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 20000000
    Minimum Value 1000000
    Maximum Value (64-bit platforms) 18446744073709551615
    Maximum Value (32-bit platforms) 4294967295

    The maximum amount of memory available for generating histogram statistics. See Section 8.9.6, “Optimizer Statistics”, andSection 13.7.3.1, “ANALYZE TABLE Syntax”.

    The global and session values may be set at runtime. The SYSTEM_VARIABLES_ADMIN or SUPER privilege is required, even for the session value.

  • host_cache_size

    Property Value
    System Variable host_cache_size
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value -1 (signifies autosizing; do not assign this literal value)
    Minimum Value 0
    Maximum Value 65536

    The size of the internal host cache (see Section 8.12.4.2, “DNS Lookup Optimization and the Host Cache”). Setting the size to 0 disables the host cache. Changing the cache size at runtime implicitly causes a FLUSH HOSTS operation to clear the host cache and truncate the host_cache table.

    The default value is 128, plus 1 for a value of max_connections up to 500, plus 1 for every increment of 20 over 500 in themax_connections value, capped to a limit of 2000.

    Use of --skip-host-cache is similar to setting the host_cache_size system variable to 0, but host_cache_size is more flexible because it can also be used to resize, enable, or disable the host cache at runtime, not just at server startup.

    If you start the server with --skip-host-cache, that does not prevent changes to the value of host_cache_size, but such changes have no effect and the cache is not re-enabled even if host_cache_size is set larger than 0.

  • hostname

    Property Value
    System Variable hostname
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type string

    The server sets this variable to the server host name at startup.

  • identity

    This variable is a synonym for the last_insert_id variable. It exists for compatibility with other database systems. You can read its value with SELECT @@identity, and set it using SET identity.

  • init_connect

    Property Value
    Command-Line Format --init-connect=name
    System Variable init_connect
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type string

    A string to be executed by the server for each client that connects. The string consists of one or more SQL statements, separated by semicolon characters. For example, each client session begins by default with autocommit mode enabled. For older servers (before MySQL 5.5.8), there is no global autocommit system variable to specify that autocommit should be disabled by default, but as a workaround init_connect can be used to achieve the same effect:

    SET GLOBAL init_connect='SET autocommit=0';

    The init_connect variable can also be set on the command line or in an option file. To set the variable as just shown using an option file, include these lines:

    [mysqld]
    init_connect='SET autocommit=0'

    For users that have the CONNECTION_ADMIN or SUPER privilege, the content of init_connect is not executed. This is done so that an erroneous value for init_connect does not prevent all clients from connecting. For example, the value might contain a statement that has a syntax error, thus causing client connections to fail. Not executing init_connect for users that have the CONNECTION_ADMIN or SUPER privilege enables them to open a connection and fix the init_connect value.

    As of MySQL 8.0.5, init_connect execution is skipped for any client user with an expired password. This is done because such a user cannot execute arbitrary statements, and thus init_connect execution will fail, leaving the client unable to connect. Skipping init_connect execution enables the user to connect and change password.

    The server discards any result sets produced by statements in the value of init_connect.

  • information_schema_stats_expiry

    Property Value
    Command-Line Format --information-schema-stats-expiry=value
    Introduced 8.0.3
    System Variable information_schema_stats_expiry
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 86400
    Minimum Value 0
    Maximum Value 31536000

    Some INFORMATION_SCHEMA tables contain columns that provide table statistics:

    STATISTICS.CARDINALITY
    TABLES.AUTO_INCREMENT    
    TABLES.AVG_ROW_LENGTH    
    TABLES.CHECKSUM    
    TABLES.CHECK_TIME    
    TABLES.CREATE_TIME    
    TABLES.DATA_FREE    
    TABLES.DATA_LENGTH    
    TABLES.INDEX_LENGTH    
    TABLES.MAX_DATA_LENGTH    
    TABLES.TABLE_ROWS    
    TABLES.UPDATE_TIME

    Those columns represent dynamic table metadata; that is, information that changes as table contents change.

    By default, MySQL retrieves cached values for those columns from the mysql.index_stats and mysql.table_stats dictionary tables when the columns are queried, which is more efficient than retrieving statistics directly from the storage engine. If cached statistics are not available or have expired, MySQL retrieves the latest statistics from the storage engine and caches them in the mysql.index_stats and mysql.table_stats dictionary tables. Subsequent queries retrieve the cached statistics until the cached statistics expire.

    The information_schema_stats_expiry session variable defines the period of time before cached statistics expire. The default is 86400 seconds (24 hours), but the time period can be extended to as much as one year.

    To update cached values at any time for a given table, use ANALYZE TABLE.

    To always retrieve the latest statistics directly from the storage engine and bypass cached values, setinformation_schema_stats_expiry to 0.

    Querying statistics columns does not store or update statistics in the mysql.index_stats and mysql.table_stats dictionary tables under these circumstances:

    information_schema_stats_expiry is a session variable, and each client session can define its own expiration value. Statistics that are retrieved from the storage engine and cached by one session are available to other sessions.

    For related information, see Section 8.2.3, “Optimizing INFORMATION_SCHEMA Queries”.

  • init_file

    Property Value
    Command-Line Format --init-file=file_name
    System Variable init_file
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type file name

    The name of the file specified with the --init-file option when you start the server. This should be a file containing SQL statements that you want the server to execute when it starts. Each statement must be on a single line and should not include comments. For more information, see the description of --init-file.

  • innodb_xxx

    InnoDB system variables are listed in Section 15.13, “InnoDB Startup Options and System Variables”. These variables control many aspects of storage, memory use, and I/O patterns for InnoDB tables, and are especially important now that InnoDB is the default storage engine.

  • insert_id

    The value to be used by the following INSERT or ALTER TABLE statement when inserting an AUTO_INCREMENT value. This is mainly used with the binary log.

  • interactive_timeout

    Property Value
    Command-Line Format --interactive-timeout=#
    System Variable interactive_timeout
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 28800
    Minimum Value 1

    The number of seconds the server waits for activity on an interactive connection before closing it. An interactive client is defined as a client that uses the CLIENT_INTERACTIVE option to mysql_real_connect(). See also wait_timeout.

  • internal_tmp_disk_storage_engine

    Property Value
    Command-Line Format --internal-tmp-disk-storage-engine=#
    System Variable internal_tmp_disk_storage_engine
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type enumeration
    Default Value INNODB
    Valid Values

    MYISAM

    INNODB

    The storage engine for on-disk internal temporary tables (see Section 8.4.4, “Internal Temporary Table Use in MySQL”). Permitted values are MYISAM and INNODB (the default).

    The optimizer uses the storage engine defined by internal_tmp_disk_storage_engine for on-disk internal temporary tables.

    When using internal_tmp_disk_storage_engine=INNODB (the default), queries that generate on-disk internal temporary tables that exceed InnoDB row or column limits will return Row size too large or Too many columns errors. The workaround is to set internal_tmp_disk_storage_engine to MYISAM.

  • internal_tmp_mem_storage_engine

    Property Value
    Command-Line Format --internal-tmp-mem-storage-engine=#
    Introduced 8.0.2
    System Variable internal_tmp_mem_storage_engine
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies Yes
    Type enumeration
    Default Value TempTable
    Valid Values

    TempTable

    MEMORY

    The storage engine for in-memory internal temporary tables (see Section 8.4.4, “Internal Temporary Table Use in MySQL”). Permitted values are TempTable (the default) and MEMORY.

    The optimizer uses the storage engine defined by internal_tmp_mem_storage_engine for in-memory internal temporary tables.

  • join_buffer_size

    Property Value
    Command-Line Format --join-buffer-size=#
    System Variable join_buffer_size
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies Yes
    Type integer
    Default Value 262144
    Minimum Value 128
    Maximum Value (Other, 64-bit platforms) 18446744073709547520
    Maximum Value (Other, 32-bit platforms) 4294967295
    Maximum Value (Windows) 4294967295

    The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans. Normally, the best way to get fast joins is to add indexes. Increase the value of join_buffer_size to get a faster full join when adding indexes is not possible. One join buffer is allocated for each full join between two tables. For a complex join between several tables for which indexes are not used, multiple join buffers might be necessary.

    Unless Batched Key Access (BKA) is used, there is no gain from setting the buffer larger than required to hold each matching row, and all joins allocate at least the minimum size, so use caution in setting this variable to a large value globally. It is better to keep the global setting small and change to a larger setting only in sessions that are doing large joins. Memory allocation time can cause substantial performance drops if the global size is larger than needed by most queries that use it.

    When BKA is used, the value of join_buffer_size defines how large the batch of keys is in each request to the storage engine. The larger the buffer, the more sequential access will be to the right hand table of a join operation, which can significantly improve performance.

    The default is 256KB. The maximum permissible setting for join_buffer_size is 4GB−1. Larger values are permitted for 64-bit platforms (except 64-bit Windows, for which large values are truncated to 4GB−1 with a warning).

    For additional information about join buffering, see Section 8.2.1.6, “Nested-Loop Join Algorithms”. For information about Batched Key Access, see Section 8.2.1.11, “Block Nested-Loop and Batched Key Access Joins”.

  • keep_files_on_create

    Property Value
    Command-Line Format --keep-files-on-create=#
    System Variable keep_files_on_create
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type boolean
    Default Value OFF

    If a MyISAM table is created with no DATA DIRECTORY option, the .MYD file is created in the database directory. By default, if MyISAM finds an existing .MYD file in this case, it overwrites it. The same applies to .MYI files for tables created with no INDEX DIRECTORY option. To suppress this behavior, set the keep_files_on_create variable to ON (1), in which case MyISAM will not overwrite existing files and returns an error instead. The default value is OFF (0).

    If a MyISAM table is created with a DATA DIRECTORY or INDEX DIRECTORY option and an existing .MYD or .MYI file is found, MyISAM always returns an error. It will not overwrite a file in the specified directory.

  • key_buffer_size

    Property Value
    Command-Line Format --key-buffer-size=#
    System Variable key_buffer_size
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 8388608
    Minimum Value 8
    Maximum Value (64-bit platforms) OS_PER_PROCESS_LIMIT
    Maximum Value (32-bit platforms) 4294967295

    Index blocks for MyISAM tables are buffered and are shared by all threads. key_buffer_size is the size of the buffer used for index blocks. The key buffer is also known as the key cache.

    The maximum permissible setting for key_buffer_size is 4GB−1 on 32-bit platforms. Larger values are permitted for 64-bit platforms. The effective maximum size might be less, depending on your available physical RAM and per-process RAM limits imposed by your operating system or hardware platform. The value of this variable indicates the amount of memory requested. Internally, the server allocates as much memory as possible up to this amount, but the actual allocation might be less.

    You can increase the value to get better index handling for all reads and multiple writes; on a system whose primary function is to run MySQL using the MyISAM storage engine, 25% of the machine's total memory is an acceptable value for this variable. However, you should be aware that, if you make the value too large (for example, more than 50% of the machine's total memory), your system might start to page and become extremely slow. This is because MySQL relies on the operating system to perform file system caching for data reads, so you must leave some room for the file system cache. You should also consider the memory requirements of any other storage engines that you may be using in addition to MyISAM.

    For even more speed when writing many rows at the same time, use LOCK TABLES. See Section 8.2.5.1, “Optimizing INSERT Statements”.

    You can check the performance of the key buffer by issuing a SHOW STATUS statement and examining the Key_read_requests,Key_reads, Key_write_requests, and Key_writes status variables. (See Section 13.7.6, “SHOW Syntax”.) TheKey_reads/Key_read_requests ratio should normally be less than 0.01. The Key_writes/Key_write_requests ratio is usually near 1 if you are using mostly updates and deletes, but might be much smaller if you tend to do updates that affect many rows at the same time or if you are using the DELAY_KEY_WRITE table option.

    The fraction of the key buffer in use can be determined using key_buffer_size in conjunction with the Key_blocks_unusedstatus variable and the buffer block size, which is available from the key_cache_block_size system variable:

    1 - ((Key_blocks_unused * key_cache_block_size) / key_buffer_size)

    This value is an approximation because some space in the key buffer is allocated internally for administrative structures. Factors that influence the amount of overhead for these structures include block size and pointer size. As block size increases, the percentage of the key buffer lost to overhead tends to decrease. Larger blocks results in a smaller number of read operations (because more keys are obtained per read), but conversely an increase in reads of keys that are not examined (if not all keys in a block are relevant to a query).

    It is possible to create multiple MyISAM key caches. The size limit of 4GB applies to each cache individually, not as a group. SeeSection 8.10.2, “The MyISAM Key Cache”.

  • key_cache_age_threshold

    Property Value
    Command-Line Format --key-cache-age-threshold=#
    System Variable key_cache_age_threshold
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 300
    Minimum Value 100
    Maximum Value (64-bit platforms) 18446744073709551615
    Maximum Value (32-bit platforms) 4294967295

    This value controls the demotion of buffers from the hot sublist of a key cache to the warm sublist. Lower values cause demotion to happen more quickly. The minimum value is 100. The default value is 300. See Section 8.10.2, “The MyISAM Key Cache”.

  • key_cache_block_size

    Property Value
    Command-Line Format --key-cache-block-size=#
    System Variable key_cache_block_size
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 1024
    Minimum Value 512
    Maximum Value 16384

    The size in bytes of blocks in the key cache. The default value is 1024. See Section 8.10.2, “The MyISAM Key Cache”.

  • key_cache_division_limit

    Property Value
    Command-Line Format --key-cache-division-limit=#
    System Variable key_cache_division_limit
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 100
    Minimum Value 1
    Maximum Value 100

    The division point between the hot and warm sublists of the key cache buffer list. The value is the percentage of the buffer list to use for the warm sublist. Permissible values range from 1 to 100. The default value is 100. See Section 8.10.2, “The MyISAM Key Cache”.

  • large_files_support

    Property Value
    System Variable large_files_support
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No

    Whether mysqld was compiled with options for large file support.

  • large_pages

    Property Value
    Command-Line Format --large-pages
    System Variable large_pages
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Platform Specific Linux
    Type boolean
    Default Value FALSE

    Whether large page support is enabled (via the --large-pages option). See Section 8.12.3.2, “Enabling Large Page Support”.

  • large_page_size

    Property Value
    System Variable large_page_size
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type integer
    Default Value 0

    If large page support is enabled, this shows the size of memory pages. Large memory pages are supported only on Linux; on other platforms, the value of this variable is always 0. See Section 8.12.3.2, “Enabling Large Page Support”.

  • last_insert_id

    The value to be returned from LAST_INSERT_ID(). This is stored in the binary log when you use LAST_INSERT_ID() in a statement that updates a table. Setting this variable does not update the value returned by the mysql_insert_id() C API function.

  • lc_messages

    Property Value
    Command-Line Format --lc-messages=name
    System Variable lc_messages
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type string
    Default Value en_US

    The locale to use for error messages. The default is en_US. The server converts the argument to a language name and combines it with the value of lc_messages_dir to produce the location for the error message file. See Section 10.11, “Setting the Error Message Language”.

  • lc_messages_dir

    Property Value
    Command-Line Format --lc-messages-dir=dir_name
    System Variable lc_messages_dir
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type directory name

    The directory where error messages are located. The server uses the value together with the value of lc_messages to produce the location for the error message file. See Section 10.11, “Setting the Error Message Language”.

  • lc_time_names

    Property Value
    System Variable lc_time_names
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type string

    This variable specifies the locale that controls the language used to display day and month names and abbreviations. This variable affects the output from the DATE_FORMAT(), DAYNAME() and MONTHNAME() functions. Locale names are POSIX-style values such as 'ja_JP' or 'pt_BR'. The default value is 'en_US' regardless of your system's locale setting. For further information, see Section 10.15, “MySQL Server Locale Support”.

  • license

    Property Value
    System Variable license
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type string
    Default Value GPL

    The type of license the server has.

  • local_infile

    Property Value
    System Variable local_infile
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type boolean
    Default Value (>= 8.0.2) OFF
    Default Value (<= 8.0.1) ON

    This variable controls server-side LOCAL capability for LOAD DATA statements. Depending on the local_infile setting, the server refuses or permits local data loading by clients that have LOCAL enabled on the client side.

    To explicitly cause the server to refuse or permit LOAD DATA LOCAL statements (regardless of how client programs and libraries are configured at build time or runtime), start mysqld with local_infile disabled or enabled, respectively. local_infile can also be set at runtime. For more information, see Section 6.1.6, “Security Issues with LOAD DATA LOCAL”.

  • lock_wait_timeout

    Property Value
    Command-Line Format --lock-wait-timeout=#
    System Variable lock_wait_timeout
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies Yes
    Type integer
    Default Value 31536000
    Minimum Value 1
    Maximum Value 31536000

    This variable specifies the timeout in seconds for attempts to acquire metadata locks. The permissible values range from 1 to 31536000 (1 year). The default is 31536000.

    This timeout applies to all statements that use metadata locks. These include DML and DDL operations on tables, views, stored procedures, and stored functions, as well as LOCK TABLES, FLUSH TABLES WITH READ LOCK, and HANDLER statements.

    This timeout does not apply to implicit accesses to system tables in the mysql database, such as grant tables modified by GRANTor REVOKE statements or table logging statements. The timeout does apply to system tables accessed directly, such as withSELECT or UPDATE.

    The timeout value applies separately for each metadata lock attempt. A given statement can require more than one lock, so it is possible for the statement to block for longer than the lock_wait_timeout value before reporting a timeout error. When lock timeout occurs, ER_LOCK_WAIT_TIMEOUT is reported.

    lock_wait_timeout also defines the amount of time that a LOCK INSTANCE FOR BACKUP statement waits for a lock before giving up.

  • locked_in_memory

    Property Value
    System Variable locked_in_memory
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No

    Whether mysqld was locked in memory with --memlock.

  • log_error

    Property Value
    Command-Line Format --log-error[=file_name]
    System Variable log_error
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type file name

    The default error log destination. If the destination is the console, the value is stderr. Otherwise, the destination is a file and thelog_error value is the file name. See Section 5.4.2, “The Error Log”.

  • log_error_filter_rules

    Property Value
    Command-Line Format --log-error-filter-rules
    Introduced 8.0.2
    Removed 8.0.4
    System Variable log_error_filter_rules
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type string
    Default Value set by server

    The filter rules for error logging. This variable is unused. It was removed in MySQL 8.0.4.

  • log_error_services

    Property Value
    Command-Line Format --log-error-services
    Introduced 8.0.2
    System Variable log_error_services
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type string
    Default Value log_filter_internal; log_sink_internal

    The components to enable for error logging. The variable may contain a list with 0, 1, or many elements. In the latter case, elements may be delimited by semicolon or (as of MySQL 8.0.12) comma, optionally followed by space. A given setting cannot use both semicolon and comma separators. Component order is significant because the server executes components in the order listed. Any loadable (not built in) component named in the log_error_services value must first be installed with INSTALL COMPONENT. For more information, see Section 5.4.2.1, “Error Log Component Configuration”.

  • log_error_suppression_list

    Property Value
    Command-Line Format --log-error-suppression-list=value
    Introduced 8.0.13
    System Variable log_error_suppression_list
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type string
    Default Value empty string

    This variable enables specifying which diagnostics should not be written to the error log when they occur with a severity ofWARNING or INFORMATION. For example, if a particular type of warning occurs frequently but is not of interest (and thus may be considered undesirable noise in the error log), it can be suppressed.

    The variable value may be the empty string for no suppression, or a list of one or more comma-separated values indicating the error codes to suppress.

    The numeric value of each code to be suppressed must be in a permitted range:

    • 1 up to (but not including) 1000: Global error codes that are shared by the server and clients

    • 10000 and higher: Server error codes intended to be written to the error log (not sent to clients)

    Attempts to assign an error code not in a permitted range produce an error and the variable value remains unchanged.

    Error codes may be specified in numeric or symbolic form. A numeric code may be specified with or without the MY- prefix. Leading zeros in the numeric part are not significant. Examples of permitted code formats:

    31
    00031
    MY-31
    MY-00031
    ER_SERVER_SHUTDOWN_COMPLETE

    For a list of error codes and symbols, see Section B.3, “Server Error Codes and Messages”.

    The server can generate messages for a given error code at differing severities, so suppression for a message associated with an error code listed in log_error_suppression_list depends on its severity. Suppose that the variable has a value of '10000,10001,MY-10002'. Messages for those codes are not written to the error log if generated with a severity of WARNING orINFORMATION. Messages generated with a severity of ERROR or SYSTEM are not suppressed and are written to the error log.

    The effect of log_error_suppression_list combines with that of log_error_verbosity. Consider a server started with these settings:

    [mysqld]
    log_error_verbosity=2     # error and warning messages only
    log_error_suppression_list='10000,10001,MY-10002'

    In this case, log_error_verbosity discards all messages with INFORMATION severity. Of the remaining messages,log_error_suppression_list discards messages with WARNING severity and any of the named error codes.

    Note

    The log_error_verbosity value shown in the example (2) is also its default value, so its effect on suppression of all INFORMATION messages is by default as just described. You must setlog_error_verbosity to 3 if you want log_error_suppression_list to affect messages with INFORMATION severity.

    Consider a server started with this setting:

    [mysqld]
    log_error_verbosity=1     # error messages only

    In this case, log_error_verbosity discards all messages with WARNING or INFORMATION severity. Settinglog_error_suppression_list has no effect because all error codes it might suppress are already discarded due to thelog_error_verbosity setting.

    log_error_suppression_list (like log_error_verbosity) affects the log_filter_internal error log filter, which is enabled by default. If that filter is disabled, error code suppression does not occur and must be modeled using whatever filter service is used instead where desired (for example, with individual filter rules when using log_filter_dragnet). For information about filter configuration, see Section 5.4.2.1, “Error Log Component Configuration”.

  • log_error_verbosity

    Property Value
    Command-Line Format --log-error-verbosity=#
    System Variable log_error_verbosity
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value (>= 8.0.4) 2
    Default Value (<= 8.0.3) 3
    Minimum Value 1
    Maximum Value 3

    The verbosity for handling events intended for the error log, as filtered by the log_filter_internal error log filter component, which is enabled by default. If log_filter_internal is disabled, log_error_verbosity has no effect.

    The following table shows the permitted verbosity values.

    Desired Log Filtering log_error_verbosity Value
    Error messages 1
    Error and warning messages 2
    Error, warning, and information messages 3

    Selected important system messages about non-error situations are printed to the error log regardless of thelog_error_verbosity value. These messages include startup and shutdown messages, and some significant changes to settings.

    The effect of log_error_verbosity combines with that of log_error_suppression_list. See the description of the latter for examples.

    For additional information, see Section 5.4.2.5, “Error Log Filtering”, and Section 5.5.1, “Error Log Components”.

  • log_output

    Property Value
    Command-Line Format --log-output=name
    System Variable log_output
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type set
    Default Value FILE
    Valid Values

    TABLE

    FILE

    NONE

    The destination for general query log and slow query log output. The value can be a comma-separated list of one or more of the words TABLE (log to tables), FILE (log to files), or NONE (do not log to tables or files). The default value is FILE. NONE, if present, takes precedence over any other specifiers. If the value is NONE log entries are not written even if the logs are enabled. If the logs are not enabled, no logging occurs even if the value of log_output is not NONE. For more information, see Section 5.4.1, “Selecting General Query and Slow Query Log Output Destinations”.

  • log_queries_not_using_indexes

    Property Value
    Command-Line Format --log-queries-not-using-indexes
    System Variable log_queries_not_using_indexes
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type boolean
    Default Value OFF

    Whether queries that do not use indexes are logged to the slow query log. See Section 5.4.5, “The Slow Query Log”.

  • log_slow_admin_statements

    Property Value
    System Variable log_slow_admin_statements
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type boolean
    Default Value OFF

    Include slow administrative statements in the statements written to the slow query log. Administrative statements include ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, and REPAIR TABLE.

  • log_syslog

    Property Value
    Command-Line Format --log-syslog[={0|1}]
    Deprecated 8.0.2 (removed in 8.0.13)
    System Variable log_syslog
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type boolean
    Default Value (Windows, <= 8.0.1) ON
    Default Value (Unix, <= 8.0.1) OFF
    Default Value (>= 8.0.2) ON (when error logging to system log is enabled)

    Prior to MySQL 8.0, this variable controlled whether to perform error logging to the system log (the Event Log on Windows, and syslog on Unix and Unix-like systems).

    In MySQL 8.0, the log_sink_syseventlog log component implements error logging to the system log (see Section 5.4.2.3, “Error Logging to the System Log”), and log_syslog is removed. (Prior to MySQL 8.0.13, log_syslog exists but is deprecated and has no effect.)

  • log_syslog_facility

    Property Value
    Command-Line Format --log-syslog-facility=value
    Removed 8.0.13
    System Variable log_syslog_facility
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type string
    Default Value daemon

    This variable was removed in MySQL 8.0.13 and replaced by syseventlog.facility.

  • log_syslog_include_pid

    Property Value
    Command-Line Format --log-syslog-include-pid[={0|1}]
    Removed 8.0.13
    System Variable log_syslog_include_pid
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type boolean
    Default Value ON

    This variable was removed in MySQL 8.0.13 and replaced by syseventlog.include_pid.

  • log_syslog_tag

    Property Value
    Command-Line Format --log-syslog-tag=tag
    Removed 8.0.13
    System Variable log_syslog_tag
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type string
    Default Value empty string

    This variable was removed in MySQL 8.0.13 and replaced by syseventlog.tag.

  • log_timestamps

    Property Value
    Command-Line Format --log-timestamps=#
    System Variable log_timestamps
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type enumeration
    Default Value UTC
    Valid Values

    UTC

    SYSTEM

    This variable controls the time zone of timestamps in messages written to the error log, and in general query log and slow query log messages written to files. It does not affect the time zone of general query log and slow query log messages written to tables (mysql.general_log, mysql.slow_log). Rows retrieved from those tables can be converted from the local system time zone to any desired time zone with CONVERT_TZ() or by setting the session time_zone system variable.

    Permitted log_timestamps values are UTC (the default) and SYSTEM (local system time zone).

    Timestamps are written using ISO 8601 / RFC 3339 format: YYYY-MM-DDThh:mm:ss.uuuuuu plus a tail value of Z signifying Zulu time (UTC) or ±hh:mm (an offset from UTC).

  • log_throttle_queries_not_using_indexes

    Property Value
    System Variable log_throttle_queries_not_using_indexes
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 0

    If log_queries_not_using_indexes is enabled, the log_throttle_queries_not_using_indexes variable limits the number of such queries per minute that can be written to the slow query log. A value of 0 (the default) means no limit. For more information, see Section 5.4.5, “The Slow Query Log”.

  • log_warnings

    Property Value
    Command-Line Format --log-warnings[=#]
    Deprecated Yes (removed in 8.0.3)
    System Variable log_warnings
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 2
    Minimum Value 0
    Maximum Value (64-bit platforms) 18446744073709551615
    Maximum Value (32-bit platforms) 4294967295

    This system variable was removed in MySQL 8.0.3. Use the log_error_verbosity system variable instead.

  • long_query_time

    Property Value
    Command-Line Format --long-query-time=#
    System Variable long_query_time
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type numeric
    Default Value 10
    Minimum Value 0

    If a query takes longer than this many seconds, the server increments the Slow_queries status variable. If the slow query log is enabled, the query is logged to the slow query log file. This value is measured in real time, not CPU time, so a query that is under the threshold on a lightly loaded system might be above the threshold on a heavily loaded one. The minimum and default values of long_query_time are 0 and 10, respectively. The value can be specified to a resolution of microseconds. For logging to a file, times are written including the microseconds part. For logging to tables, only integer times are written; the microseconds part is ignored. See Section 5.4.5, “The Slow Query Log”.

  • low_priority_updates

    Property Value
    Command-Line Format --low-priority-updates
    System Variable low_priority_updates
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type boolean
    Default Value FALSE

    If set to 1, all INSERT, UPDATE, DELETE, and LOCK TABLE WRITE statements wait until there is no pending SELECT or LOCK TABLE READ on the affected table. This affects only storage engines that use only table-level locking (such as MyISAM, MEMORY, andMERGE).

  • lower_case_file_system

    Property Value
    System Variable lower_case_file_system
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type boolean

    This variable describes the case sensitivity of file names on the file system where the data directory is located. OFF means file names are case-sensitive, ON means they are not case-sensitive. This variable is read only because it reflects a file system attribute and setting it would have no effect on the file system.

  • lower_case_table_names

    Property Value
    Command-Line Format --lower-case-table-names[=#]
    System Variable lower_case_table_names
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type integer
    Default Value 0
    Minimum Value 0
    Maximum Value 2

    If set to 0, table names are stored as specified and comparisons are case-sensitive. If set to 1, table names are stored in lowercase on disk and comparisons are not case sensitive. If set to 2, table names are stored as given but compared in lowercase. This option also applies to database names and table aliases. For additional details, see Section 9.2.2, “Identifier Case Sensitivity”.

    On Windows the default value is 1. On macOS, the default value is 2.

    You should not set lower_case_table_names to 0 if you are running MySQL on a system where the data directory resides on a case-insensitive file system (such as on Windows or macOS). It is an unsupported combination that could result in a hang condition when running an INSERT INTO ... SELECT ... FROM tbl_name operation with the wrong tbl_name letter case. With MyISAM, accessing table names using different letter cases could cause index corruption.

    An error message is printed and the server exits if you attempt to start the server with --lower_case_table_names=0 on a case-insensitive file system.

    If you are using InnoDB tables, you should set this variable to 1 on all platforms to force names to be converted to lowercase.

    The setting of this variable in MySQL 8.0 affects the behavior of replication filtering options with regard to case sensitivity. (Bug #51639) See Section 17.2.5, “How Servers Evaluate Replication Filtering Rules”, for more information.

    It is prohibited to start the server with a lower_case_table_names setting that is different from the setting used when the server was initialized. The restriction is necessary because collations used by various data dictionary table fields are based on the setting defined when the server is initialized, and restarting the server with a different setting would introduce inconsistencies with respect to how identifiers are ordered and compared.

  • mandatory_roles

    Property Value
    Command-Line Format --mandatory-roles=value
    Introduced 8.0.2
    System Variable mandatory_roles
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type string
    Default Value empty string

    Roles the server should treat as mandatory. In effect, these roles are automatically granted to every user, although setting mandatory_roles does not actually change any user accounts, and the granted roles are not visible in the mysql.role_edgessystem table.

    The variable value is a comma-separated list of role names. Example:

    SET PERSIST mandatory_roles = '`role1`@`%`,`role2`,role3,role4@localhost';

    Setting mandatory_roles requires the ROLE_ADMIN privilege, in addition to the SYSTEM_VARIABLES_ADMIN or SUPER privilege normally required to set a global system variable.

    Role names consist of a user part and host part in user_name@host_name format. The host part, if omitted, defaults to %. For additional information, see Section 6.2.5, “Specifying Role Names”.

    User names and host names, if quoted, must be written in a fashion permitted for quoting within quoted strings.

    Roles named in the value of mandatory_roles cannot be revoked with REVOKE or dropped with DROP ROLE or DROP USER.

    Mandatory roles, like explicitly granted roles, do not take effect until activated (see Activating Roles). At login time, role activation occurs for all granted roles if the activate_all_roles_on_login system variable is enabled, or only for roles that are set as default roles otherwise. At runtime, SET ROLE activates roles.

    Roles that do not exist when assigned to mandatory_roles but are created later may require special treatment to be considered mandatory. For details, see Defining Mandatory Roles.

    SHOW GRANTS displays mandatory roles according to the rules described in Section 13.7.6.21, “SHOW GRANTS Syntax”.

  • max_allowed_packet

    Property Value
    Command-Line Format --max-allowed-packet=#
    System Variable max_allowed_packet
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value (>= 8.0.3) 67108864
    Default Value (<= 8.0.2) 4194304
    Minimum Value 1024
    Maximum Value 1073741824

    The maximum size of one packet or any generated/intermediate string, or any parameter sent by themysql_stmt_send_long_data() C API function. The default is 64MB.

    The packet message buffer is initialized to net_buffer_length bytes, but can grow up to max_allowed_packet bytes when needed. This value by default is small, to catch large (possibly incorrect) packets.

    You must increase this value if you are using large BLOB columns or long strings. It should be as big as the largest BLOB you want to use. The protocol limit for max_allowed_packet is 1GB. The value should be a multiple of 1024; nonmultiples are rounded down to the nearest multiple.

    When you change the message buffer size by changing the value of the max_allowed_packet variable, you should also change the buffer size on the client side if your client program permits it. The default max_allowed_packet value built in to the client library is 1GB, but individual client programs might override this. For example, mysql and mysqldump have defaults of 16MB and 24MB, respectively. They also enable you to change the client-side value by setting max_allowed_packet on the command line or in an option file.

    The session value of this variable is read only. The client can receive up to as many bytes as the session value. However, the server will not send to the client more bytes than the current global max_allowed_packet value. (The global value could be less than the session value if the global value is changed after the client connects.)

  • max_connect_errors

    Property Value
    Command-Line Format --max-connect-errors=#
    System Variable max_connect_errors
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 100
    Minimum Value 1
    Maximum Value (64-bit platforms) 18446744073709551615
    Maximum Value (32-bit platforms) 4294967295

    If more than this many successive connection requests from a host are interrupted without a successful connection, the server blocks that host from further connections. You can unblock blocked hosts by flushing the host cache. To do so, issue a FLUSH HOSTS statement or execute a mysqladmin flush-hosts command. If a connection is established successfully within fewer than max_connect_errors attempts after a previous connection was interrupted, the error count for the host is cleared to zero. However, once a host is blocked, flushing the host cache is the only way to unblock it. The default is 100.

  • max_connections

    Property Value
    Command-Line Format --max-connections=#
    System Variable max_connections
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 151
    Minimum Value 1
    Maximum Value 100000

    The maximum permitted number of simultaneous client connections. By default, this is 151. See Section B.5.2.6, “Too many connections”, for more information.

    Increasing this value increases the number of file descriptors that mysqld requires. If the required number of descriptors are not available, the server reduces the value of max_connections. See Section 8.4.3.1, “How MySQL Opens and Closes Tables”, for comments on file descriptor limits.

    mysqld actually permits max_connections+1 clients to connect. The extra connection is reserved for use by accounts that have the CONNECTION_ADMIN or SUPER privilege. By granting the SUPER privilege to administrators and not to normal users (who should not need it), an administrator can connect to the server and use SHOW PROCESSLIST to diagnose problems even if the maximum number of unprivileged clients are connected. See Section 13.7.6.29, “SHOW PROCESSLIST Syntax”.

    Connections refused because the max_connections limit is reached increment the Connection_errors_max_connectionsstatus variable.

  • max_delayed_threads

    Property Value
    Command-Line Format --max-delayed-threads=#
    Deprecated Yes
    System Variable max_delayed_threads
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 20
    Minimum Value 0
    Maximum Value 16384

    This system variable is deprecated (because DELAYED inserts are not supported), and will be removed in a future release.

  • max_digest_length

    Property Value
    Command-Line Format --max-digest-length=#
    System Variable max_digest_length
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type integer
    Default Value 1024
    Minimum Value 0
    Maximum Value 1048576

    The maximum number of bytes available for computing normalized statement digests. Once that amount of space is used during digest computation, truncation occurs: no further tokens from a parsed statement are collected or figure into its digest value. Statements that differ only after that many bytes of parsed tokens produce the same normalized statement digest and are considered identical if compared or if aggregated for digest statistics.

    Decreasing the max_digest_length value reduces memory use but causes the digest value of more statements to become indistinguishable if they differ only at the end. Increasing the value permits longer statements to be distinguished but increases memory use, particularly for workloads that involve large numbers of simultaneous sessions (the server allocatesmax_digest_length bytes per session).

    The parser uses this system variable as a limit on the maximum length of normalized statement digests that it computes. The Performance Schema, if it tracks statement digests, makes a copy of the digest value, using theperformance_schema_max_digest_length. system variable as a limit on the maximum length of digests that it stores. Consequently, if performance_schema_max_digest_length is less than max_digest_length, digest values stored in the Performance Schema are truncated relative to the original digest values.

    For more information about statement digesting, see Section 25.9, “Performance Schema Statement Digests and Sampling”.

  • max_error_count

    Property Value
    Command-Line Format --max-error-count=#
    System Variable max_error_count
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies Yes
    Type integer
    Default Value (>= 8.0.3) 1024
    Default Value (<= 8.0.2) 64
    Minimum Value 0
    Maximum Value 65535

    The maximum number of error, warning, and information messages to be stored for display by the SHOW ERRORS and SHOW WARNINGS statements. This is the same as the number of condition areas in the diagnostics area, and thus the number of conditions that can be inspected by GET DIAGNOSTICS.

  • max_execution_time

    Property Value
    Command-Line Format --max-execution-time=#
    System Variable max_execution_time
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies Yes
    Type integer
    Default Value 0

    The execution timeout for SELECT statements, in milliseconds. If the value is 0, timeouts are not enabled.

    max_execution_time applies as follows:

    • The global max_execution_time value provides the default for the session value for new connections. The session value applies to SELECT executions executed within the session that include no MAX_EXECUTION_TIME(N) optimizer hint or for which N is 0.

    • max_execution_time applies to read-only SELECT statements. Statements that are not read only are those that invoke a stored function that modifies data as a side effect.

    • max_execution_time is ignored for SELECT statements in stored programs.

  • max_heap_table_size

    Property Value
    Command-Line Format --max-heap-table-size=#
    System Variable max_heap_table_size
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies Yes
    Type integer
    Default Value 16777216
    Minimum Value 16384
    Maximum Value (64-bit platforms) 1844674407370954752
    Maximum Value (32-bit platforms) 4294967295

    This variable sets the maximum size to which user-created MEMORY tables are permitted to grow. The value of the variable is used to calculate MEMORY table MAX_ROWS values. Setting this variable has no effect on any existing MEMORY table, unless the table is re-created with a statement such as CREATE TABLE or altered with ALTER TABLE or TRUNCATE TABLE. A server restart also sets the maximum size of existing MEMORY tables to the global max_heap_table_size value.

    This variable is also used in conjunction with tmp_table_size to limit the size of internal in-memory tables. See Section 8.4.4, “Internal Temporary Table Use in MySQL”.

    max_heap_table_size is not replicated. See Section 17.4.1.21, “Replication and MEMORY Tables”, and Section 17.4.1.39, “Replication and Variables”, for more information.

  • max_insert_delayed_threads

    Property Value
    Deprecated Yes
    System Variable max_insert_delayed_threads
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer

    This variable is a synonym for max_delayed_threads.

    This system variable is deprecated (because DELAYED inserts are not supported), and will be removed in a future release.

  • max_join_size

    Property Value
    Command-Line Format --max-join-size=#
    System Variable max_join_size
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies Yes
    Type integer
    Default Value 18446744073709551615
    Minimum Value 1
    Maximum Value 18446744073709551615

    Do not permit statements that probably need to examine more than max_join_size rows (for single-table statements) or row combinations (for multiple-table statements) or that are likely to do more than max_join_size disk seeks. By setting this value, you can catch statements where keys are not used properly and that would probably take a long time. Set it if your users tend to perform joins that lack a WHERE clause, that take a long time, or that return millions of rows.

    Setting this variable to a value other than DEFAULT resets the value of sql_big_selects to 0. If you set the sql_big_selectsvalue again, the max_join_size variable is ignored.

  • max_length_for_sort_data

    Property Value
    Command-Line Format --max-length-for-sort-data=#
    System Variable max_length_for_sort_data
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies Yes
    Type integer
    Default Value (>= 8.0.1) 4096
    Default Value (8.0.0) 1024
    Minimum Value 4
    Maximum Value 8388608

    The cutoff on the size of index values that determines which filesort algorithm to use. See Section 8.2.1.14, “ORDER BY Optimization”.

  • max_points_in_geometry

    Property Value
    Command-Line Format --max-points-in-geometry=integer
    System Variable max_points_in_geometry
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies Yes
    Type integer
    Default Value 65536
    Minimum Value 3
    Maximum Value 1048576

    The maximum value of the points_per_circle argument to the ST_Buffer_Strategy() function.

  • max_prepared_stmt_count

    Property Value
    Command-Line Format --max-prepared-stmt-count=#
    System Variable max_prepared_stmt_count
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 16382
    Minimum Value 0
    Maximum Value 1048576

    This variable limits the total number of prepared statements in the server. It can be used in environments where there is the potential for denial-of-service attacks based on running the server out of memory by preparing huge numbers of statements. If the value is set lower than the current number of prepared statements, existing statements are not affected and can be used, but no new statements can be prepared until the current number drops below the limit. The default value is 16,382. The permissible range of values is from 0 to 1 million. Setting the value to 0 disables prepared statements.

  • max_seeks_for_key

    Property Value
    Command-Line Format --max-seeks-for-key=#
    System Variable max_seeks_for_key
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies Yes
    Type integer
    Default Value (64-bit platforms) 18446744073709551615
    Default Value (32-bit platforms) 4294967295
    Minimum Value 1
    Maximum Value (64-bit platforms) 18446744073709551615
    Maximum Value (32-bit platforms) 4294967295

    Limit the assumed maximum number of seeks when looking up rows based on a key. The MySQL optimizer assumes that no more than this number of key seeks are required when searching for matching rows in a table by scanning an index, regardless of the actual cardinality of the index (see Section 13.7.6.22, “SHOW INDEX Syntax”). By setting this to a low value (say, 100), you can force MySQL to prefer indexes instead of table scans.

  • max_sort_length

    Property Value
    Command-Line Format --max-sort-length=#
    System Variable max_sort_length
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies Yes
    Type integer
    Default Value 1024
    Minimum Value 4
    Maximum Value 8388608

    The number of bytes to use when sorting data values. The server uses only the first max_sort_length bytes of each value and ignores the rest. Consequently, values that differ only after the first max_sort_length bytes compare as equal for GROUP BY, ORDER BY, and DISTINCT operations.

    Increasing the value of max_sort_length may require increasing the value of sort_buffer_size as well. For details, see Section 8.2.1.14, “ORDER BY Optimization”

  • max_sp_recursion_depth

    Property Value
    Command-Line Format --max-sp-recursion-depth[=#]
    System Variable max_sp_recursion_depth
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 0
    Maximum Value 255

    The number of times that any given stored procedure may be called recursively. The default value for this option is 0, which completely disables recursion in stored procedures. The maximum value is 255.

    Stored procedure recursion increases the demand on thread stack space. If you increase the value of max_sp_recursion_depth, it may be necessary to increase thread stack size by increasing the value of thread_stack at server startup.

  • max_tmp_tables

    This system variable was removed in MySQL 8.0.3.

  • max_user_connections

    Property Value
    Command-Line Format --max-user-connections=#
    System Variable max_user_connections
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 0
    Minimum Value 0
    Maximum Value 4294967295

    The maximum number of simultaneous connections permitted to any given MySQL user account. A value of 0 (the default) means no limit.

    This variable has a global value that can be set at server startup or runtime. It also has a read-only session value that indicates the effective simultaneous-connection limit that applies to the account associated with the current session. The session value is initialized as follows:

    • If the user account has a nonzero MAX_USER_CONNECTIONS resource limit, the session max_user_connections value is set to that limit.

    • Otherwise, the session max_user_connections value is set to the global value.

    Account resource limits are specified using the CREATE USER or ALTER USER statement. See Section 6.3.6, “Setting Account Resource Limits”.

  • max_write_lock_count

    Property Value
    Command-Line Format --max-write-lock-count=#
    System Variable max_write_lock_count
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value (64-bit platforms) 18446744073709551615
    Default Value (32-bit platforms) 4294967295
    Minimum Value 1
    Maximum Value (64-bit platforms) 18446744073709551615
    Maximum Value (32-bit platforms) 4294967295

    After this many write locks, permit some pending read lock requests to be processed in between.

  • mecab_rc_file

    Property Value
    Command-Line Format --mecab-rc-file
    System Variable mecab_rc_file
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type directory name

    The mecab_rc_file option is used when setting up the MeCab full-text parser.

    The mecab_rc_file option defines the path to the mecabrc configuration file, which is the configuration file for MeCab. The option is read-only and can only be set at startup. The mecabrc configuration file is required to initialize MeCab.

    For information about the MeCab full-text parser, see Section 12.9.9, “MeCab Full-Text Parser Plugin”.

    For information about options that can be specified in the MeCab mecabrc configuration file, refer to the MeCab Documentationon the Google Developers site.

  • metadata_locks_cache_size

    Property Value
    Deprecated Yes (removed in 8.0.13)
    System Variable metadata_locks_cache_size
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type integer
    Default Value 1024
    Minimum Value 1
    Maximum Value 1048576

    This system variable was removed in MySQL 8.0.13.

  • metadata_locks_hash_instances

    Property Value
    Deprecated Yes (removed in 8.0.13)
    System Variable metadata_locks_hash_instances
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type integer
    Default Value 8
    Minimum Value 1
    Maximum Value 1024

    This system variable was removed in MySQL 8.0.13.

  • min_examined_row_limit

    Property Value
    Command-Line Format --min-examined-row-limit=#
    System Variable min_examined_row_limit
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 0
    Minimum Value 0
    Maximum Value (64-bit platforms) 18446744073709551615
    Maximum Value (32-bit platforms) 4294967295

    Queries that examine fewer than this number of rows are not logged to the slow query log.

  • multi_range_count

    Property Value
    Command-Line Format --multi-range-count=#
    Deprecated Yes (removed in 8.0.3)
    System Variable multi_range_count
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 256
    Minimum Value 1
    Maximum Value 4294967295

    This system variable was removed in MySQL 8.0.3.

  • myisam_data_pointer_size

    Property Value
    Command-Line Format --myisam-data-pointer-size=#
    System Variable myisam_data_pointer_size
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 6
    Minimum Value 2
    Maximum Value 7

    The default pointer size in bytes, to be used by CREATE TABLE for MyISAM tables when no MAX_ROWS option is specified. This variable cannot be less than 2 or larger than 7. The default value is 6. See Section B.5.2.11, “The table is full”.

  • myisam_max_sort_file_size

    Property Value
    Command-Line Format --myisam-max-sort-file-size=#
    System Variable myisam_max_sort_file_size
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value (64-bit platforms) 9223372036854775807
    Default Value (32-bit platforms) 2147483648

    The maximum size of the temporary file that MySQL is permitted to use while re-creating a MyISAM index (during REPAIR TABLE,ALTER TABLE, or LOAD DATA INFILE). If the file size would be larger than this value, the index is created using the key cache instead, which is slower. The value is given in bytes.

    If MyISAM index files exceed this size and disk space is available, increasing the value may help performance. The space must be available in the file system containing the directory where the original index file is located.

  • myisam_mmap_size

    Property Value
    Command-Line Format --myisam-mmap-size=#
    System Variable myisam_mmap_size
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type integer
    Default Value (64-bit platforms) 18446744073709551615
    Default Value (32-bit platforms) 4294967295
    Minimum Value 7
    Maximum Value (64-bit platforms) 18446744073709551615
    Maximum Value (32-bit platforms) 4294967295

    The maximum amount of memory to use for memory mapping compressed MyISAM files. If many compressed MyISAM tables are used, the value can be decreased to reduce the likelihood of memory-swapping problems.

  • myisam_recover_options

    Property Value
    System Variable myisam_recover_options
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No

    The value of the --myisam-recover-options option. See Section 5.1.6, “Server Command Options”.

  • myisam_repair_threads

    Property Value
    Command-Line Format --myisam-repair-threads=#
    System Variable myisam_repair_threads
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 1
    Minimum Value 1
    Maximum Value (64-bit platforms) 18446744073709551615
    Maximum Value (32-bit platforms) 4294967295

    If this value is greater than 1, MyISAM table indexes are created in parallel (each index in its own thread) during the Repair by sorting process. The default value is 1.

    Note

    Multithreaded repair is still beta-quality code.

  • myisam_sort_buffer_size

    Property Value
    Command-Line Format --myisam-sort-buffer-size=#
    System Variable myisam_sort_buffer_size
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 8388608
    Minimum Value 4096
    Maximum Value (Other, 64-bit platforms) 18446744073709551615
    Maximum Value (Other, 32-bit platforms) 4294967295
    Maximum Value (Windows, 64-bit platforms) 18446744073709551615
    Maximum Value (Windows, 32-bit platforms) 4294967295

    The size of the buffer that is allocated when sorting MyISAM indexes during a REPAIR TABLE or when creating indexes with CREATE INDEX or ALTER TABLE.

  • myisam_stats_method

    Property Value
    Command-Line Format --myisam-stats-method=name
    System Variable myisam_stats_method
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type enumeration
    Default Value nulls_unequal
    Valid Values

    nulls_equal

    nulls_unequal

    nulls_ignored

    How the server treats NULL values when collecting statistics about the distribution of index values for MyISAM tables. This variable has three possible values, nulls_equal, nulls_unequal, and nulls_ignored. For nulls_equal, all NULL index values are considered equal and form a single value group that has a size equal to the number of NULL values. For nulls_unequal, NULLvalues are considered unequal, and each NULL forms a distinct value group of size 1. For nulls_ignored, NULL values are ignored.

    The method that is used for generating table statistics influences how the optimizer chooses indexes for query execution, as described in Section 8.3.8, “InnoDB and MyISAM Index Statistics Collection”.

  • myisam_use_mmap

    Property Value
    Command-Line Format --myisam-use-mmap
    System Variable myisam_use_mmap
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type boolean
    Default Value OFF

    Use memory mapping for reading and writing MyISAM tables.

  • mysql_native_password_proxy_users

    Property Value
    Command-Line Format --mysql-native-password-proxy-users=[={OFF|ON}]
    System Variable mysql_native_password_proxy_users
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type boolean
    Default Value OFF

    This variable controls whether the mysql_native_password built-in authentication plugin supports proxy users. It has no effect unless the check_proxy_users system variable is enabled. For information about user proxying, see Section 6.3.11, “Proxy Users”.

  • named_pipe

    Property Value
    System Variable named_pipe
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Platform Specific Windows
    Type boolean
    Default Value OFF

    (Windows only.) Indicates whether the server supports connections over named pipes.

  • net_buffer_length

    Property Value
    Command-Line Format --net-buffer-length=#
    System Variable net_buffer_length
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 16384
    Minimum Value 1024
    Maximum Value 1048576

    Each client thread is associated with a connection buffer and result buffer. Both begin with a size given by net_buffer_lengthbut are dynamically enlarged up to max_allowed_packet bytes as needed. The result buffer shrinks to net_buffer_length after each SQL statement.

    This variable should not normally be changed, but if you have very little memory, you can set it to the expected length of statements sent by clients. If statements exceed this length, the connection buffer is automatically enlarged. The maximum value to which net_buffer_length can be set is 1MB.

    The session value of this variable is read only.

  • net_read_timeout

    Property Value
    Command-Line Format --net-read-timeout=#
    System Variable net_read_timeout
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 30
    Minimum Value 1

    The number of seconds to wait for more data from a connection before aborting the read. When the server is reading from the client, net_read_timeout is the timeout value controlling when to abort. When the server is writing to the client,net_write_timeout is the timeout value controlling when to abort. See also slave_net_timeout.

  • net_retry_count

    Property Value
    Command-Line Format --net-retry-count=#
    System Variable net_retry_count
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 10
    Minimum Value 1
    Maximum Value (64-bit platforms) 18446744073709551615
    Maximum Value (32-bit platforms) 4294967295

    If a read or write on a communication port is interrupted, retry this many times before giving up. This value should be set quite high on FreeBSD because internal interrupts are sent to all threads.

  • net_write_timeout

    Property Value
    Command-Line Format --net-write-timeout=#
    System Variable net_write_timeout
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 60
    Minimum Value 1

    The number of seconds to wait for a block to be written to a connection before aborting the write. See also net_read_timeout.

  • new

    Property Value
    Command-Line Format --new
    System Variable new
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Disabled by skip-new
    Type boolean
    Default Value FALSE

    This variable was used in MySQL 4.0 to turn on some 4.1 behaviors, and is retained for backward compatibility. Its value is always OFF.

  • ngram_token_size

    Property Value
    Command-Line Format --ngram-token-size
    System Variable ngram_token_size
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type integer
    Default Value 2
    Minimum Value 1
    Maximum Value 10

    Defines the n-gram token size for the n-gram full-text parser. The ngram_token_size option is read-only and can only be modified at startup. The default value is 2 (bigram). The maximum value is 10.

    For more information about how to configure this variable, see Section 12.9.8, “ngram Full-Text Parser”.

  • offline_mode

    Property Value
    Command-Line Format --offline-mode=val
    System Variable offline_mode
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type boolean
    Default Value OFF

    Whether the server is in offline mode, which has these characteristics:

    • Connected client users who do not have the CONNECTION_ADMIN or SUPER privilege are disconnected on the next request, with an appropriate error. Disconnection includes terminating running statements and releasing locks. Such clients also cannot initiate new connections, and receive an appropriate error.

    • Connected client users who have the CONNECTION_ADMIN or SUPER privilege are not disconnected, and can initiate new connections to manage the server.

    • Replication slave threads are permitted to keep applying data to the server.

    Only users who have the SYSTEM_VARIABLES_ADMIN or SUPER privilege can control offline mode. To put a server in offline mode, change the value of the offline_mode system variable from OFF to ON. To resume normal operations, change offline_modefrom ON to OFF. In offline mode, clients that are refused access receive an ER_SERVER_OFFLINE_MODE error.

  • old

    Property Value
    Command-Line Format --old
    System Variable old
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No

    old is a compatibility variable. It is disabled by default, but can be enabled at startup to revert the server to behaviors present in older versions.

    When old is enabled, it changes the default scope of index hints to that used prior to MySQL 5.1.17. That is, index hints with noFOR clause apply only to how indexes are used for row retrieval and not to resolution of ORDER BY or GROUP BY clauses. (SeeSection 8.9.4, “Index Hints”.) Take care about enabling this in a replication setup. With statement-based binary logging, having different modes for the master and slaves might lead to replication errors.

  • old_alter_table

    Property Value
    Command-Line Format --old-alter-table
    System Variable old_alter_table
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type boolean
    Default Value OFF

    When this variable is enabled, the server does not use the optimized method of processing an ALTER TABLE operation. It reverts to using a temporary table, copying over the data, and then renaming the temporary table to the original, as used by MySQL 5.0 and earlier. For more information on the operation of ALTER TABLE, see Section 13.1.8, “ALTER TABLE Syntax”.

    ALTER TABLE ... DROP PARTITION with old_alter_table=ON rebuilds the partitioned table and attempts to move data from the dropped partition to another partition with a compatible PARTITION ... VALUES definition. Data that cannot be moved to another partition is deleted. In earlier releases, ALTER TABLE ... DROP PARTITION with old_alter_table=ON deletes data stored in the partition and drops the partition.

  • old_passwords

    Property Value
    Deprecated Yes (removed in 8.0.11)
    System Variable old_passwords
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type enumeration
    Default Value 0
    Valid Values

    0

    2

    This system variable was removed in MySQL 8.0.11.

  • open_files_limit

    Property Value
    Command-Line Format --open-files-limit=#
    System Variable open_files_limit
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type integer
    Default Value 5000, with possible adjustment
    Minimum Value 0
    Maximum Value platform dependent

    The number of files that the operating system permits mysqld to open. The value of this variable at runtime is the real value permitted by the system and might be different from the value you specify at server startup. The value is 0 on systems where MySQL cannot change the number of open files.

    The effective open_files_limit value is based on the value specified at system startup (if any) and the values of max_connections and table_open_cache, using these formulas:

    1) 10 + max_connections + (table_open_cache * 2)
    2) max_connections * 5
    3) operating system limit if positive
    4) if operating system limit is Infinity:
       open_files_limit value specified at startup, 5000 if none

    The server attempts to obtain the number of file descriptors using the maximum of those three values. If that many descriptors cannot be obtained, the server attempts to obtain as many as the system will permit.

  • optimizer_prune_level

    Property Value
    Command-Line Format --optimizer-prune-level[=#]
    System Variable optimizer_prune_level
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies Yes
    Type boolean
    Default Value 1

    Controls the heuristics applied during query optimization to prune less-promising partial plans from the optimizer search space. A value of 0 disables heuristics so that the optimizer performs an exhaustive search. A value of 1 causes the optimizer to prune plans based on the number of rows retrieved by intermediate plans.

  • optimizer_search_depth

    Property Value
    Command-Line Format --optimizer-search-depth[=#]
    System Variable optimizer_search_depth
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies Yes
    Type integer
    Default Value 62
    Minimum Value 0
    Maximum Value 62

    The maximum depth of search performed by the query optimizer. Values larger than the number of relations in a query result in better query plans, but take longer to generate an execution plan for a query. Values smaller than the number of relations in a query return an execution plan quicker, but the resulting plan may be far from being optimal. If set to 0, the system automatically picks a reasonable value.

  • optimizer_switch

    Property Value
    Command-Line Format --optimizer-switch=value
    System Variable optimizer_switch
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies Yes
    Type set
    Valid Values (>= 8.0.13)

    batched_key_access={on|off}

    block_nested_loop={on|off}

    condition_fanout_filter={on|off}

    derived_merge={on|off}

    duplicateweedout={on|off}

    engine_condition_pushdown={on|off}

    firstmatch={on|off}

    index_condition_pushdown={on|off}

    index_merge={on|off}

    index_merge_intersection={on|off}

    index_merge_sort_union={on|off}

    index_merge_union={on|off}

    loosescan={on|off}

    materialization={on|off}

    mrr={on|off}

    mrr_cost_based={on|off}

    semijoin={on|off}

    skip_scan={on|off}

    subquery_materialization_cost_based={on|off}

    use_index_extensions={on|off}

    use_invisible_indexes={on|off}

    Valid Values (>= 8.0.3, <= 8.0.12)

    batched_key_access={on|off}

    block_nested_loop={on|off}

    condition_fanout_filter={on|off}

    derived_merge={on|off}

    duplicateweedout={on|off}

    engine_condition_pushdown={on|off}

    firstmatch={on|off}

    index_condition_pushdown={on|off}

    index_merge={on|off}

    index_merge_intersection={on|off}

    index_merge_sort_union={on|off}

    index_merge_union={on|off}

    loosescan={on|off}

    materialization={on|off}

    mrr={on|off}

    mrr_cost_based={on|off}

    semijoin={on|off}

    subquery_materialization_cost_based={on|off}

    use_index_extensions={on|off}

    use_invisible_indexes={on|off}

    Valid Values (<= 8.0.2)

    batched_key_access={on|off}

    block_nested_loop={on|off}

    condition_fanout_filter={on|off}

    derived_merge={on|off}

    duplicateweedout={on|off}

    engine_condition_pushdown={on|off}

    firstmatch={on|off}

    index_condition_pushdown={on|off}

    index_merge={on|off}

    index_merge_intersection={on|off}

    index_merge_sort_union={on|off}

    index_merge_union={on|off}

    loosescan={on|off}

    materialization={on|off}

    mrr={on|off}

    mrr_cost_based={on|off}

    semijoin={on|off}

    subquery_materialization_cost_based={on|off}

    use_index_extensions={on|off}

    The optimizer_switch system variable enables control over optimizer behavior. The value of this variable is a set of flags, each of which has a value of on or off to indicate whether the corresponding optimizer behavior is enabled or disabled. This variable has global and session values and can be changed at runtime. The global default can be set at server startup.

    To see the current set of optimizer flags, select the variable value:

    mysql> SELECT @@optimizer_switch\G
    *************************** 1. row ***************************
    @@optimizer_switch: index_merge=on,index_merge_union=on,
                        index_merge_sort_union=on,
                        index_merge_intersection=on,
                        engine_condition_pushdown=on,
                        index_condition_pushdown=on,
                        mrr=on,mrr_cost_based=on,
                        block_nested_loop=on,batched_key_access=off,
                        materialization=on,semijoin=on,loosescan=on,
                        firstmatch=on,duplicateweedout=on,
                        subquery_materialization_cost_based=on,
                        use_index_extensions=on,
                        condition_fanout_filter=on,derived_merge=on,
                        use_invisible_indexes=off,skip_scan=on

    For more information about the syntax of this variable and the optimizer behaviors that it controls, see Section 8.9.3, “Switchable Optimizations”.

  • optimizer_trace

    Property Value
    System Variable optimizer_trace
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type string

    This variable controls optimizer tracing. For details, see MySQL Internals: Tracing the Optimizer.

  • optimizer_trace_features

    Property Value
    System Variable optimizer_trace_features
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type string

    This variable enables or disables selected optimizer tracing features. For details, see MySQL Internals: Tracing the Optimizer.

  • optimizer_trace_limit

    Property Value
    System Variable optimizer_trace_limit
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 1

    The maximum number of optimizer traces to display. For details, see MySQL Internals: Tracing the Optimizer.

  • optimizer_trace_max_mem_size

    Property Value
    System Variable optimizer_trace_max_mem_size
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies Yes
    Type integer
    Default Value (>= 8.0.4) 1048576
    Default Value (<= 8.0.3) 16384

    The maximum cumulative size of stored optimizer traces. For details, see MySQL Internals: Tracing the Optimizer.

  • optimizer_trace_offset

    Property Value
    System Variable optimizer_trace_offset
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value -1

    The offset of optimizer traces to display. For details, see MySQL Internals: Tracing the Optimizer.

  • performance_schema_xxx

    Performance Schema system variables are listed in Section 25.14, “Performance Schema System Variables”. These variables may be used to configure Performance Schema operation.

  • parser_max_mem_size

    Property Value
    Command-Line Format --parser-max-mem-size=N
    System Variable parser_max_mem_size
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value (64-bit platforms) 18446744073709551615
    Default Value (32-bit platforms) 4294967295
    Minimum Value 10000000
    Maximum Value (64-bit platforms) 18446744073709551615
    Maximum Value (32-bit platforms) 4294967295

    The maximum amount of memory available to the parser. The default value places no limit on memory available. The value can be reduced to protect against out-of-memory situations caused by parsing long or complex SQL statements.

  • password_history

    Property Value
    Command-Line Format --password-history=#
    Introduced 8.0.3
    System Variable password_history
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 0
    Minimum Value 0
    Maximum Value 4294967295

    This variable defines the global policy for controlling reuse of previous passwords based on required minimum number of password changes. For an account password used previously, this variable indicates the number of subsequent account password changes that must occur before the password can be reused. If the value is 0 (the default), there is no reuse restriction based on number of password changes.

    Changes to this variable apply immediately to all accounts defined with the PASSWORD HISTORY DEFAULT option.

    The global number-of-changes password reuse policy can be overridden as desired for individual accounts using the PASSWORD HISTORY option of the CREATE USER and ALTER USER statements. See Section 6.3.8, “Password Management”.

  • password_require_current

    Property Value
    Command-Line Format --password-require-current[={OFF|ON}]
    Introduced 8.0.13
    System Variable password_require_current
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type boolean
    Default Value OFF

    This variable defines the global policy for controlling whether attempts to change an account password must specify the current password to be replaced.

    Changes to this variable apply immediately to all accounts defined with the PASSWORD REQUIRE CURRENT DEFAULT option.

    The global verification-required policy can be overridden as desired for individual accounts using the PASSWORD REQUIRE option of the CREATE USER and ALTER USER statements. See Section 6.3.8, “Password Management”.

  • password_reuse_interval

    Property Value
    Command-Line Format --password-reuse-interval=#
    Introduced 8.0.3
    System Variable password_reuse_interval
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 0
    Minimum Value 0
    Maximum Value 4294967295

    This variable defines the global policy for controlling reuse of previous passwords based on time elapsed. For an account password used previously, this variable indicates the number of days that must pass before the password can be reused. If the value is 0 (the default), there is no reuse restriction based on time elapsed.

    Changes to this variable apply immediately to all accounts defined with the PASSWORD REUSE INTERVAL DEFAULT option.

    The global time-elapsed password reuse policy can be overridden as desired for individual accounts using the PASSWORD REUSE INTERVAL option of the CREATE USER and ALTER USER statements. See Section 6.3.8, “Password Management”.

  • persisted_globals_load

    Property Value
    Command-Line Format --persisted-globals-load[=ON|OFF]
    System Variable persisted_globals_load
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type boolean
    Default Value ON

    Whether to load persisted configuration settings from the mysqld-auto.cnf file in the data directory. The server normally processes this file at startup after all other option files (see Section 4.2.6, “Using Option Files”). Disablingpersisted_globals_load causes the server startup sequence to skip mysqld-auto.cnf.

    To modify the contents of mysqld-auto.cnf, use the SET PERSIST or PERSIST_ONLY statement, or the RESET PERSISTstatement. See Section 13.7.5.1, “SET Syntax for Variable Assignment”.

  • pid_file

    Property Value
    Command-Line Format --pid-file=file_name
    System Variable pid_file
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type file name

    The path name of the process ID file. This variable can be set with the --pid-file option. The server creates the file in the data directory unless an absolute path name is given to specify a different directory. If you specify the --pid-file option, you must specify a value. If you do not specify the --pid-file option, MySQL uses a default value of host_name.pid, where host_name is the name of the host machine.

    The process ID file is used by other programs such as mysqld_safe to determine the server's process ID. On Windows, this variable also affects the default error log file name. See Section 5.4.2, “The Error Log”.

  • plugin_dir

    Property Value
    Command-Line Format --plugin-dir=dir_name
    System Variable plugin_dir
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type directory name
    Default Value BASEDIR/lib/plugin

    The path name of the plugin directory.

    If the plugin directory is writable by the server, it may be possible for a user to write executable code to a file in the directory using SELECT ... INTO DUMPFILE. This can be prevented by making plugin_dir read only to the server or by setting --secure-file-priv to a directory where SELECT writes can be made safely.

  • port

    Property Value
    Command-Line Format --port=#
    System Variable port
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type integer
    Default Value 3306
    Minimum Value 0
    Maximum Value 65535

    The number of the port on which the server listens for TCP/IP connections. This variable can be set with the --port option.

  • preload_buffer_size

    Property Value
    Command-Line Format --preload-buffer-size=#
    System Variable preload_buffer_size
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 32768
    Minimum Value 1024
    Maximum Value 1073741824

    The size of the buffer that is allocated when preloading indexes.

  • profiling

    If set to 0 or OFF (the default), statement profiling is disabled. If set to 1 or ON, statement profiling is enabled and the SHOW PROFILE and SHOW PROFILES statements provide access to profiling information. See Section 13.7.6.31, “SHOW PROFILES Syntax”.

    This variable is deprecated and will be removed in a future MySQL release.

  • profiling_history_size

    The number of statements for which to maintain profiling information if profiling is enabled. The default value is 15. The maximum value is 100. Setting the value to 0 effectively disables profiling. See Section 13.7.6.31, “SHOW PROFILES Syntax”.

    This variable is deprecated and will be removed in a future MySQL release.

  • protocol_version

    Property Value
    System Variable protocol_version
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type integer

    The version of the client/server protocol used by the MySQL server.

  • proxy_user

    Property Value
    System Variable proxy_user
    Scope Session
    Dynamic No
    SET_VAR Hint Applies No
    Type string

    If the current client is a proxy for another user, this variable is the proxy user account name. Otherwise, this variable is NULL. SeeSection 6.3.11, “Proxy Users”.

  • pseudo_slave_mode

    Property Value
    System Variable pseudo_slave_mode
    Scope Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer

    This variable is for internal server use.

  • pseudo_thread_id

    Property Value
    System Variable pseudo_thread_id
    Scope Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
为您推荐

友情链接 |九搜汽车网 |手机ok生活信息网|ok生活信息网|ok微生活
 Powered by www.360SDN.COM   京ICP备11022651号-4 © 2012-2016 版权