本文详细介绍了MySQL中各种日志配置选项，包括错误日志、查询日志、慢查询日志等，以及性能相关的参数调整，如连接数、缓存大小、锁策略等，帮助读者理解如何优化MySQL服务器的性能。
--log-error-suppression-list=name
Comma-separated list of error-codes. Error messages
corresponding to these codes will not be included in the
error log. Only events with a severity of Warning or
Information can be suppressed; events with System or
Error severity will always be included. Requires the
filter 'log_filter_internal' to be set in
@@global.log_error_services, which is the default.
--log-error-verbosity=#
How detailed the error log should be. 1, log errors only.
2, log errors and warnings. 3, log errors, warnings, and
notes. Messages sent to the client are unaffected by this
setting.
--log-isam[=name] Log all MyISAM changes to file.
--log-output=name Syntax: log-output=value[,value...], where "value" could
be TABLE, FILE or NONE
--log-queries-not-using-indexes
Log queries that are executed without benefit of any
index to the slow log if it is open
--log-raw Log to general log before any rewriting of the query. For
use in debugging, not production as sensitive information
may be logged.
--log-short-format Don't log extra information to update and slow-query
logs.
--log-slave-updates Tells the slave to log the updates from the slave thread
to the binary log.
(Defaults to on; use --skip-log-slave-updates to disable.)
--log-slow-admin-statements
Log slow OPTIMIZE, ANALYZE, ALTER and other
administrative statements to the slow log if it is open.
--log-slow-extra Print more attributes to the slow query log file. Has no
effect on logging to table.
--log-slow-slave-statements
Log slow statements executed by slave thread to the slow
log if it is open.
--log-statements-unsafe-for-binlog
Log statements considered unsafe when using statement
based binary logging.
(Defaults to on; use --skip-log-statements-unsafe-for-binlog to disable.)
--log-tc=name Path to transaction coordinator log (used for
transactions that affect more than one storage engine,
when binary log is disabled).
--log-tc-size=# Size of transaction coordinator log.
--log-throttle-queries-not-using-indexes=#
Log at most this many 'not using index' warnings per
minute to the slow log. Any further warnings will be
condensed into a single summary line. A value of 0
disables throttling. Option has no effect unless
--log_queries_not_using_indexes is set.
--log-timestamps=name
UTC to timestamp log files in zulu time, for more concise
timestamps and easier correlation of logs from servers
from multiple time zones, or SYSTEM to use the system's
local time. This affects only log files, not log tables,
as the timestamp columns of the latter can be converted
at will.
--long-query-time=# Log all queries that have taken more than long_query_time
seconds to execute to file. The argument will be treated
as a decimal value with microsecond precision
--low-priority-updates
INSERT/DELETE/UPDATE has lower priority than selects
--lower-case-table-names[=#]
If set to 1 table names are stored in lowercase on disk
and table names will be case-insensitive. Should be set
to 2 if you are using a case insensitive file system
--mandatory-roles=name
All the specified roles are always considered granted to
every user and they can't be revoked. Mandatory roles
still require activation unless they are made into
default roles. The granted roles will not be visible in
the mysql.role_edges table.
--master-info-file=name
The location and name of the file that remembers the
master and where the I/O replication thread is in the
master's binlogs. Deprecated option that shall be removed
eventually without a replacement.
--master-info-repository=name
Defines the type of the repository for the master
information.
--master-retry-count=#
The number of tries the slave will make to connect to the
master before giving up. Deprecated option, use 'CHANGE
MASTER TO master_retry_count = <num>' instead.
--master-verify-checksum
Force checksum verification of logged events in binary
log before sending them to slaves or printing them in
output of SHOW BINLOG EVENTS. Disabled by default.
--max-allowed-packet=#
Max packet length to send to or receive from the server
--max-binlog-cache-size=#
Sets the total size of the transactional cache
--max-binlog-dump-events=#
Option used by mysql-test for debugging and testing of
replication.
--max-binlog-size=# Binary log will be rotated automatically when the size
exceeds this value. Will also apply to relay logs if
max_relay_log_size is 0
--max-binlog-stmt-cache-size=#
Sets the total size of the statement cache
--max-connect-errors=#
If there is more than this number of interrupted
connections from a host this host will be blocked from
further connections
--max-connections=# The number of simultaneous clients allowed
--max-delayed-threads=#
Don't start more than this number of threads to handle
INSERT DELAYED statements. If set to zero INSERT DELAYED
will be not used. This variable is deprecated along with
INSERT DELAYED.
--max-digest-length=#
Maximum length considered for digest text.
--max-error-count=# Max number of errors/warnings to store for a statement
--max-execution-time=#
Kill SELECT statement that takes over the specified
number of milliseconds
--max-heap-table-size=#
Don't allow creation of heap tables bigger than this
--max-join-size=# Joins that are probably going to read more than
max_join_size records return an error
--max-length-for-sort-data=#
This variable is deprecated and will be removed in a
future release.
--max-points-in-geometry[=#]
Maximum number of points in a geometry
--max-prepared-stmt-count=#
Maximum number of prepared statements in the server
--max-relay-log-size=#
If non-zero: relay log will be rotated automatically when
the size exceeds this value; if zero: when the size
exceeds max_binlog_size
--max-seeks-for-key=#
Limit assumed max number of seeks when looking up rows
based on a key
--max-sort-length=# The number of bytes to use when sorting long values with
PAD SPACE collations (only the first max_sort_length
bytes of each value are used; the rest are ignored)
--max-sp-recursion-depth[=#]
Maximum stored procedure recursion depth
--max-user-connections=#
The maximum number of active connections for a single
user (0 = no limit)
--max-write-lock-count=#
After this many write locks, allow some read locks to run
in between
--memlock Lock mysqld in memory.
--min-examined-row-limit=#
Don't write queries to slow log that examine fewer rows
than that
--myisam-block-size=#
Block size to be used for MyISAM index pages
--myisam-data-pointer-size=#
Default pointer size to be used for MyISAM tables
--myisam-max-sort-file-size=#
Don't use the fast sort index method to created index if
the temporary file would get bigger than this
--myisam-mmap-size=#
Restricts the total memory used for memory mapping of
MySQL tables
--myisam-recover-options[=name]
Syntax: myisam-recover-options[=option[,option...]],
where option can be DEFAULT, BACKUP, FORCE, QUICK, or OFF
--myisam-repair-threads=#
If larger than 1, when repairing a MyISAM table all
indexes will be created in parallel, with one thread per
index. The value of 1 disables parallel repair
--myisam-sort-buffer-size=#
The buffer that is allocated when sorting the index when
doing a REPAIR or when creating indexes with CREATE INDEX
or ALTER TABLE
--myisam-stats-method=name
Specifies how MyISAM index statistics collection code
should treat NULLs. Possible values of name are
NULLS_UNEQUAL (default behavior for 4.1 and later),
NULLS_EQUAL (emulate 4.0 behavior), and NULLS_IGNORED
--myisam-use-mmap Use memory mapping for reading and writing MyISAM tables
--mysql-native-password-proxy-users
If set to FALSE (the default), then the
mysql_native_password plugin will not signal for
authenticated users to be checked for mapping to proxy
users. When set to TRUE, the plugin will flag associated
authenticated accounts to be mapped to proxy users when
the server option check_proxy_users is enabled.
--mysqlx[=name] Enable or disable mysqlx plugin. Possible values are ON,
OFF, FORCE (don't start if the plugin fails to load).
--mysqlx-bind-address[=name]
Address to which X Plugin should bind the TCP socket
optionally followed by a network namespace delimited with
/. E.g., the string value 127.0.0.1/red specifies to
listen on IP address 127.0.0.1 from the network namespace
'red'.
--mysqlx-cache-cleaner[=name]
Enable or disable mysqlx_cache_cleaner plugin. Possible
values are ON, OFF, FORCE (don't start if the plugin
fails to load).
--mysqlx-compression-algorithms[=name]
Compression algorithms: where option can be
DEFLATE_STREAM, LZ4_MESSAGE, ZSTD_STREAM
--mysqlx-connect-timeout[=#]
Maximum allowed waiting time for connection to setup a
session (in seconds).
--mysqlx-deflate-default-compression-level[=#]
Default value of compression level for deflate algorithm
--mysqlx-deflate-max-client-compression-level[=#]
Max value of compression level for deflate algorithm
--mysqlx-document-id-unique-prefix[=#]
Unique prefix is a value assigned by InnoDB cluster to
the instance, which is meant to make document id unique
across all replicasets from the same cluster
--mysqlx-enable-hello-notice
Hello notice is a X Protocol message send by the server
after connection establishment, using this variable it
can be disabled
(Defaults to on; use --skip-mysqlx-enable-hello-notice to disable.)
--mysqlx-idle-worker-thread-timeout[=#]
Time after which an idle worker thread is terminated (in
seconds).
--mysqlx-interactive-timeout[=#]
Default value for "mysqlx_wait_timeout", when the
connection is interactive. The value defines number or
seconds that X Plugin must wait for activity on
interactive connection
--mysqlx-lz4-default-compression-level[=#]
Default value of compression level for lz4 algorithm
--mysqlx-lz4-max-client-compression-level[=#]
Max value of compression level for lz4 algorithm
--mysqlx-max-allowed-packet[=#]
Size of largest message that client is going to handle.
--mysqlx-max-connections[=#]
Maximum number of concurrent X protocol connections.
Actual number of connections is also affected by the
general max_connections.
--mysqlx-min-worker-threads[=#]
Minimal number of worker threads.
--mysqlx-port[=#] Port on which X Plugin is going to accept incoming
connections.
--mysqlx-port-open-timeout[=#]
How long X Plugin is going to retry binding of server
socket (in case of failure)
--mysqlx-read-timeout[=#]
Number or seconds that X Plugin must wait for blocking
read operation to complete
--mysqlx-socket[=name]
X Plugin's unix socket for local connection.
--mysqlx-ssl-ca=name
CA file in PEM format.
--mysqlx-ssl-capath=name
CA directory.
--mysqlx-ssl-cert=name
X509 cert in PEM format.
--mysqlx-ssl-cipher=name
SSL cipher to use.
--mysqlx-ssl-crl=name
Certificate revocation list.
--mysqlx-ssl-crlpath=name
Certificate revocation list path.
--mysqlx-ssl-key=name
X509 key in PEM format.
--mysqlx-wait-timeout[=#]
Number or seconds that X Plugin must wait for activity on
noninteractive connection
--mysqlx-write-timeout[=#]
Number or seconds that X Plugin must wait for blocking
write operation to complete
--mysqlx-zstd-default-compression-level[=#]
Default value of compression level for zstd algorithm
--mysqlx-zstd-max-client-compression-level[=#]
Max value of compression level for zstd algorithm
--named-pipe Enable the named pipe (NT)
--named-pipe-full-access-group=name
Name of Windows group granted full access to the named
pipe
--net-buffer-length=#
Buffer length for TCP/IP and socket communication
--net-read-timeout=#
Number of seconds to wait for more data from a connection
before aborting the read
--net-retry-count=# If a read on a communication port is interrupted, retry
this many times before giving up
--net-write-timeout=#
Number of seconds to wait for a block to be written to a
connection before aborting the write
-n, --new Use very new possible "unsafe" functions
--ngram[=name] Enable or disable ngram plugin. Possible values are ON,
OFF, FORCE (don't start if the plugin fails to load).
--ngram-token-size=#
InnoDB ngram full text plugin parser token size in
characters
--no-dd-upgrade Abort restart if automatic upgrade or downgrade of the
data dictionary is needed. Deprecated option. Use
--upgrade=NONE instead.
--no-monitor Disable monitor process.
--offline-mode Make the server into offline mode
--old Use compatible behavior
--old-alter-table Use old, non-optimized alter table
--old-style-user-limits
Enable old-style user limits (before 5.0.3, user
resources were counted per each user+host vs. per
account).
--open-files-limit=#
If this is not 0, then mysqld will use this value to
reserve file descriptors to use with setrlimit(). If this
value is 0 then mysqld will reserve max_connections*5 or
max_connections + table_open_cache*2 (whichever is
larger) number of file descriptors
--optimizer-prune-level=#
Controls the heuristic(s) applied during query
optimization to prune less-promising partial plans from
the optimizer search space. Meaning: 0 - do not apply any
heuristic, thus perform exhaustive search; 1 - prune
plans based on number of retrieved rows
--optimizer-search-depth=#
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 compile
a query. Values smaller than the number of tables in a
relation result in faster optimization, but may produce
very bad query plans. If set to 0, the system will
automatically pick a reasonable value
--optimizer-switch=name
optimizer_switch=option=val[,option=val...], where option
is one of {index_merge, index_merge_union,
index_merge_sort_union, index_merge_intersection,
engine_condition_pushdown, index_condition_pushdown, mrr,
mrr_cost_based, materialization, semijoin, loosescan,
firstmatch, duplicateweedout,
subquery_materialization_cost_based, skip_scan,
block_nested_loop, batched_key_access,
use_index_extensions, condition_fanout_filter,
derived_merge, hash_join} and val is one of {on, off,
default}
--optimizer-trace=name
Controls tracing of the Optimizer:
optimizer_trace=option=val[,option=val...], where option
is one of {enabled, one_line} and val is one of {on,
default}
--optimizer-trace-features=name
Enables/disables tracing of selected features of the
Optimizer:
optimizer_trace_features=option=val[,option=val...],
where option is one of {greedy_search, range_optimizer,
dynamic_range, repeated_subselect} and val is one of {on,
off, default}
--optimizer-trace-limit=#
Maximum number of shown optimizer traces
--optimizer-trace-max-mem-size=#
Maximum allowed cumulated size of stored optimizer traces
--optimizer-trace-offset=#
Offset of first optimizer trace to show; see manual
--parser-max-mem-size=#
Maximum amount of memory available to the parser
--partial-revokes Access of database objects can be restricted, even if
user has global privileges granted.
--password-history=#
The number of old passwords to check in the history. Set
to 0 (the default) to turn the checks off
--password-require-current
Current password is needed to be specified in order to
change it
--password-reuse-interval=#
The minimum number of days that need to pass before a
password can be reused. Set to 0 (the default) to turn
the checks off
--performance-schema
Enable the performance schema.
(Defaults to on; use --skip-performance-schema to disable.)
--performance-schema-accounts-size=#
Maximum number of instrumented user@host accounts. Use 0
to disable, -1 for automated scaling.
--performance-schema-consumer-events-stages-current
Default startup value for the events_stages_current
consumer.
--performance-schema-consumer-events-stages-history
Default startup value for the events_stages_history
consumer.
--performance-schema-consumer-events-stages-history-long
Default startup value for the events_stages_history_long
consumer.
--performance-schema-consumer-events-statements-current
Default startup value for the events_statements_current
consumer.
(Defaults to on; use --skip-performance-schema-consumer-events-statements-current to disable.)
--performance-schema-consumer-events-statements-history
Default startup value for the events_statements_history
consumer.
(Defaults to on; use --skip-performance-schema-consumer-events-statements-history to disable.)
--performance-schema-consumer-events-statements-history-long
Default startup value for the
events_statements_history_long consumer.
--performance-schema-consumer-events-transactions-current
Default startup value for the events_transactions_current
consumer.
(Defaults to on; use --skip-performance-schema-consumer-events-transactions-current to disable.)
--performance-schema-consumer-events-transactions-history
Default startup value for the events_transactions_history
consumer.
(Defaults to on; use --skip-performance-schema-consumer-events-transactions-history to disable.)
--performance-schema-consumer-events-transactions-history-long
Default startup value for the
events_transactions_history_long consumer.
--performance-schema-consumer-events-waits-current
Default startup value for the events_waits_current
consumer.
--performance-schema-consumer-events-waits-history
Default startup value for the events_waits_history
consumer.
--performance-schema-consumer-events-waits-history-long
Default startup value for the events_waits_history_long
consumer.
--performance-schema-consumer-global-instrumentation
Default startup value for the global_instrumentation
consumer.
(Defaults to on; use --skip-performance-schema-consumer-global-instrumentation to disable.)
--performance-schema-consumer-statements-digest
Default startup value for the statements_digest consumer.
(Defaults to on; use --skip-performance-schema-consumer-statements-digest to disable.)
--performance-schema-consumer-thread-instrumentation
Default startup value for the thread_instrumentation
consumer.
(Defaults to on; use --skip-performance-schema-consumer-thread-instrumentation to disable.)
--performance-schema-digests-size=#
Size of the statement digest. Use 0 to disable, -1 for
automated sizing.
--performance-schema-error-size=#
Number of server errors instrumented.
--performance-schema-events-stages-history-long-size=#
Number of rows in EVENTS_STAGES_HISTORY_LONG. Use 0 to
disable, -1 for automated sizing.
--performance-schema-events-stages-history-size=#
Number of rows per thread in EVENTS_STAGES_HISTORY. Use 0
to disable, -1 for automated sizing.
--performance-schema-events-statements-history-long-size=#
Number of rows in EVENTS_STATEMENTS_HISTORY_LONG. Use 0
to disable, -1 for automated sizing.
--performance-schema-events-statements-history-size=#
Number of rows per thread in EVENTS_STATEMENTS_HISTORY.
Use 0 to disable, -1 for automated sizing.
--performance-schema-events-transactions-history-long-size=#
Number of rows in EVENTS_TRANSACTIONS_HISTORY_LONG. Use 0
to disable, -1 for automated sizing.
--performance-schema-events-transactions-history-size=#
Number of rows per thread in EVENTS_TRANSACTIONS_HISTORY.
Use 0 to disable, -1 for automated sizing.
--performance-schema-events-waits-history-long-size=#
Number of rows in EVENTS_WAITS_HISTORY_LONG. Use 0 to
disable, -1 for automated sizing.
--performance-schema-events-waits-history-size=#
Number of rows per thread in EVENTS_WAITS_HISTORY. Use 0
to disable, -1 for automated sizing.
--performance-schema-hosts-size=#
Maximum number of instrumented hosts. Use 0 to disable,
-1 for automated scaling.
--performance-schema-instrument[=name]
Default startup value for a performance schema
instrument.
--performance-schema-max-cond-classes=#
Maximum number of condition instruments.
--performance-schema-max-cond-instances=#
Maximum number of instrumented condition objects. Use 0
to disable, -1 for automated scaling.
--performance-schema-max-digest-length=#
Maximum length considered for digest text, when stored in
performance_schema tables.
--performance-schema-max-digest-sample-age=#
The time in seconds after which a previous query sample
is considered old. When the value is 0, queries are
sampled once. When the value is greater than zero,
queries are re sampled if the last sample is more than
performance_schema_max_digest_sample_age seconds old.
--performance-schema-max-file-classes=#
Maximum number of file instruments.
--performance-schema-max-file-handles=#
Maximum number of opened instrumented files.
--performance-schema-max-file-instances=#
Maximum number of instrumented files. Use 0 to disable,
-1 for automated scaling.
--performance-schema-max-index-stat=#
Maximum number of index statistics for instrumented
tables. Use 0 to disable, -1 for automated scaling.
--performance-schema-max-memory-classes=#
Maximum number of memory pool instruments.
--performance-schema-max-metadata-locks=#
Maximum number of metadata locks. Use 0 to disable, -1
for automated scaling.
--performance-schema-max-mutex-classes=#
Maximum number of mutex instruments.
--performance-schema-max-mutex-instances=#
Maximum number of instrumented MUTEX objects. Use 0 to
disable, -1 for automated scaling.
--performance-schema-max-prepared-statements-instances=#
Maximum number of instrumented prepared statements. Use 0
to disable, -1 for automated scaling.
--performance-schema-max-program-instances=#
Maximum number of instrumented programs. Use 0 to
disable, -1 for automated scaling.
--performance-schema-max-rwlock-classes=#
Maximum number of rwlock instruments.
--performance-schema-max-rwlock-instances=#
Maximum number of instrumented RWLOCK objects. Use 0 to
disable, -1 for automated scaling.
--performance-schema-max-socket-classes=#
Maximum number of socket instruments.
扫一扫
评论
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
查看更多评论
添加红包
