860 lines
25 KiB
Plaintext
860 lines
25 KiB
Plaintext
# Example MySQL config file for very large systems.
|
|
#
|
|
# This is for a large system with memory of 1G-2G where the system runs mainly
|
|
# MySQL.
|
|
#
|
|
# You can copy this file to
|
|
# /etc/my.cnf to set global options,
|
|
# mysql-data-dir/my.cnf to set server-specific options (in this
|
|
# installation this directory is /var/lib/mysql) or
|
|
# ~/.my.cnf to set user-specific options.
|
|
#
|
|
# In this file, you can use all long options that a program supports.
|
|
# If you want to know which options a program supports, run the program
|
|
# with the "--help" option.
|
|
|
|
# The following options will be passed to all MySQL clients
|
|
[client]
|
|
port = 3306
|
|
socket = /tmp/mysql.sock
|
|
|
|
# Here follows entries for some specific programs
|
|
# The following values assume you have at least 32M ram
|
|
|
|
# This was formally known as [safe_mysqld]. Both versions are currently parsed.
|
|
[mysqld_safe]
|
|
socket = /tmp/mysql.sock
|
|
nice = 0
|
|
|
|
open-files-limit = 1048576
|
|
innodb-open-files = 1048576
|
|
|
|
|
|
# The MySQL server
|
|
[mysqld]
|
|
port = 3306
|
|
socket = /tmp/mysql.sock
|
|
|
|
## - sql_mode
|
|
## -
|
|
## - Since Version 8.0.11 NO_AUTO_CREATE_USER is no longer available
|
|
## -
|
|
## - For now, we will use the default value, it seems to be ok.
|
|
## -
|
|
#sql-mode = "ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
|
|
|
|
## - secure-file-priv
|
|
## -
|
|
## - This variable is used to limit the effect of data import and export operations,
|
|
## - such as those performed by the LOAD DATA and SELECT ... INTO OUTFILE statements
|
|
## - and the LOAD_FILE() function. These operations are permitted only to users who
|
|
## - have the FILE privilege.
|
|
## -
|
|
## - i.e.: GRANT FILE on *.* to piwik@localhost
|
|
## -
|
|
## - secure_file_priv may be set as follows:
|
|
## -
|
|
## - - If empty, the variable has no effect. (That means no restrictions).
|
|
## - This is not a secure setting.
|
|
## - - If set to the name of a directory, the server limits import and export
|
|
## - operations to work only with files in that directory. The directory
|
|
## - must exist; the server will not create it.
|
|
## - - If set to NULL, the server disables import and export operations.
|
|
## - This value is permitted as of MySQL 5.7.6.
|
|
## -
|
|
## - Since MySQL v 5.7.16 the default 'secure_file_priv' value has changed
|
|
## -
|
|
## - NULL (>= MySQL 5.7.16), empty (< MySQL 5.7.16)
|
|
## -
|
|
## - We use empty ("")
|
|
## -
|
|
## - Note:
|
|
## - We changed the default to empty ("") while building from source
|
|
## - using CMake option (-DINSTALL_SECURE_FILE_PRIVDIR=""). So you can ignore
|
|
## - this option.
|
|
## -
|
|
#secure-file-priv = ""
|
|
|
|
|
|
## - local-infile
|
|
## -
|
|
## - 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.
|
|
## -
|
|
## - You can check your 'local-infile' setting on mysql prompt:
|
|
## -
|
|
## - SHOW GLOBAL VARIABLES LIKE 'local_infile';
|
|
## -
|
|
#local-infile = 1
|
|
|
|
|
|
## - skip_external_locking
|
|
## -
|
|
## - Affects only MyISAM table access.
|
|
## -
|
|
## - This is OFF if mysqld uses external locking (system locking),
|
|
## - ON if external locking is disabled.
|
|
## -
|
|
## - Default: ON
|
|
## -
|
|
skip-external-locking
|
|
|
|
|
|
# Don't listen on a TCP/IP port at all. This can be a security enhancement,
|
|
# if all processes that need to connect to mysqld run on the same host.
|
|
# All interaction with mysqld must be made via Unix sockets or named pipes.
|
|
# Note that using this option without enabling named pipes on Windows
|
|
# (via the "enable-named-pipe" option) will render mysqld useless!
|
|
#
|
|
#skip-networking
|
|
|
|
|
|
# Instead of skip-networking the default is now to listen only on
|
|
# localhost which is more compatible and is not less secure.
|
|
#
|
|
#bind-address = 127.0.0.1
|
|
bind-address = 192.168.3.123
|
|
|
|
|
|
# Replication Master Server (default)
|
|
# binary logging is required for replication
|
|
#log-bin=mysql-bin
|
|
|
|
# required unique id between 1 and 2^32 - 1
|
|
# defaults to 1 if master-host is not set
|
|
# but will not function as a master if omitted
|
|
server-id = 1
|
|
|
|
# Replication Slave (comment out master section to use this)
|
|
#
|
|
# To configure this host as a replication slave, you can choose between
|
|
# two methods :
|
|
#
|
|
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
|
|
# the syntax is:
|
|
#
|
|
# CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
|
|
# MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
|
|
#
|
|
# where you replace <host>, <user>, <password> by quoted strings and
|
|
# <port> by the master's port number (3306 by default).
|
|
#
|
|
# Example:
|
|
#
|
|
# CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
|
|
# MASTER_USER='joe', MASTER_PASSWORD='secret';
|
|
#
|
|
# OR
|
|
#
|
|
# 2) Set the variables below. However, in case you choose this method, then
|
|
# start replication for the first time (even unsuccessfully, for example
|
|
# if you mistyped the password in master-password and the slave fails to
|
|
# connect), the slave will create a master.info file, and any later
|
|
# change in this file to the variables' values below will be ignored and
|
|
# overridden by the content of the master.info file, unless you shutdown
|
|
# the slave server, delete master.info and restart the slaver server.
|
|
# For that reason, you may want to leave the lines below untouched
|
|
# (commented) and instead use CHANGE MASTER TO (see above)
|
|
#
|
|
# required unique id between 2 and 2^32 - 1
|
|
# (and different from the master)
|
|
# defaults to 2 if master-host is set
|
|
# but will not function as a slave if omitted
|
|
#server-id = 2
|
|
#
|
|
# The replication master for this slave - required
|
|
#master-host = <hostname>
|
|
#
|
|
# The username the slave will use for authentication when connecting
|
|
# to the master - required
|
|
#master-user = <username>
|
|
#
|
|
# The password the slave will authenticate with when connecting to
|
|
# the master - required
|
|
#master-password = <password>
|
|
#
|
|
# The port the master is listening on.
|
|
# optional - defaults to 3306
|
|
#master-port = <port>
|
|
#
|
|
# binary logging - not required for slaves, but recommended
|
|
#log-bin=mysql-bin
|
|
#
|
|
# binary logging format - mixed recommended
|
|
#binlog_format=mixed
|
|
|
|
# Point the following paths to different dedicated disks
|
|
#tmpdir = /tmp/
|
|
#log-update = /path-to-dedicated-directory/hostname
|
|
|
|
|
|
## - max_connections
|
|
## -
|
|
## - Die zulässige Anzahl nebenläufiger Clientverbindungen. Wenn dieser Wert erhöht
|
|
## - wird, erhöht sich auch die Anzahl der Dateideskriptoren, die mysqld benötigt.
|
|
## -
|
|
## - Vorgabewert ist 100
|
|
## -
|
|
#max-connections = 300
|
|
max-connections = 101
|
|
|
|
|
|
## - explicit_defaults_for_timestamp
|
|
## -
|
|
## - This variable was added in MySQL 5.6.6
|
|
## -
|
|
## - In MySQL, the TIMESTAMP data type differs in nonstandard ways
|
|
## - from other data types. See MySQL Dokumentation.
|
|
## -
|
|
## - [Warning] TIMESTAMP with implicit DEFAULT value is deprecated.
|
|
## - Please use --explicit_defaults_for_timestamp server option (see
|
|
## - documentation for more details).
|
|
## -
|
|
## - As indicated by the warning, to turn off the nonstandard behaviors,
|
|
## - enable the new .
|
|
## -
|
|
explicit-defaults-for-timestamp = TRUE
|
|
|
|
|
|
## - MySQL Fehlermeldungen
|
|
## -
|
|
## - !! Notice
|
|
## - erst ab für mysql 5.5.x
|
|
## -
|
|
## - lc-messages=de_DE
|
|
## - lc-messages-dir=/usr/local/mysql/share
|
|
## -
|
|
## - bis 5.1.x
|
|
## -
|
|
## - language=/usr/local/mysql/share/german
|
|
## -
|
|
|
|
|
|
## - low-priority-updates
|
|
## -
|
|
## - Give table-modifying operations (INSERT, REPLACE, DELETE,
|
|
## - UPDATE) lower priority than selects.
|
|
## -
|
|
## -
|
|
low-priority-updates = 1
|
|
|
|
|
|
## - concurrent_insert
|
|
## -
|
|
## - If activated (1 or AUTO, the default), MySQL permits INSERT
|
|
## - and SELECT statements to run concurrently for MyISAM tables
|
|
## - that have no free blocks in the middle of the data file.
|
|
## -
|
|
## - If set to 2 or ALWAYS, MySQL enables concurrent inserts for
|
|
## - all MyISAM tables, even those that have holes. For a table with
|
|
## - a hole, new rows are inserted at the end of the table if it is
|
|
## - in use by another thread. Otherwise, MySQL acquires a normal
|
|
## - write lock and inserts the row into the hole.
|
|
## -
|
|
concurrent-insert = 2
|
|
|
|
|
|
## - open-files-limit
|
|
## -
|
|
## - put the following lines into /etc/security/limits.conf
|
|
## -
|
|
## - @staff hard nofile 32768
|
|
## - root hard nofile 32768
|
|
## -
|
|
## - see also http://linux-vserver.org/Ulimit_Nofiles
|
|
## -
|
|
open-files-limit = 1048576
|
|
innodb-open-files = 1048576
|
|
|
|
|
|
## -------------------------
|
|
## InnoDB specific variables
|
|
|
|
## - innodb_file_per_table
|
|
## -
|
|
## - When innodb_file_per_table is enabled (the default in 5.6.6 and higher),
|
|
## - InnoDB stores the data and indexes for each newly created table in a
|
|
## - separate .ibd file, rather than in the system tablespace.
|
|
## -
|
|
innodb-file-per-table = 1
|
|
|
|
## - innodb_data_home_dir
|
|
## -
|
|
## - Default: MySQL data directory
|
|
## -
|
|
#innodb-data-home_dir = /data/mysql
|
|
|
|
#innodb-data-file-path = ibdata1:2000M;ibdata2:10M:autoextend
|
|
|
|
## - innodb_log_group_home_dir
|
|
## -
|
|
## - The directory path to the InnoDB redo log files, whose number
|
|
## - is specified by innodb_log_files_in_group.
|
|
## -
|
|
## - If you do not specify any InnoDB log variables, the default is
|
|
## - to create two files named ib_logfile0 and ib_logfile1 in the MySQL
|
|
## - data directory. Their size is given by the size of the
|
|
## - innodb_log_file_size system variable.
|
|
## -
|
|
#innodb-log-group-home-dir = /var/lib/mysql/
|
|
|
|
## - innodb-buffer-pool-size
|
|
## -
|
|
## - The size in bytes of the buffer pool, the memory area where InnoDB
|
|
## - caches table and index data.
|
|
## -
|
|
## - You can set .._buffer_pool_size up to 50 - 80 %
|
|
## - of RAM but beware of setting memory usage too high
|
|
## -
|
|
## - Note:
|
|
## - When the size of the buffer pool is greater than 1GB, setting
|
|
## - innodb_buffer_pool_instances to a value greater than 1 can improve
|
|
## - the scalability on a busy server.
|
|
## -
|
|
## - default: 134217728 (128M)
|
|
## -
|
|
#innodb-buffer-pool-size = 384M
|
|
#innodb-buffer-pool-size = 1024M
|
|
innodb_buffer_pool_size = 4G
|
|
|
|
|
|
## - innodb_additional_mem_pool_size
|
|
## -
|
|
## - The size in bytes of a memory pool InnoDB uses to store data dictionary
|
|
## - information and other internal data structures.
|
|
## -
|
|
## - Default: 8388608 (8M)
|
|
## -
|
|
## - Note !!
|
|
## - innodb_additional_mem_pool_size is deprected since version 5.6.3
|
|
## - innodb_additional_mem_pool_size is removed since version 5.7
|
|
## -
|
|
## - innodb_additional_mem_pool_size is also removed on MariaDB 10.3.x
|
|
## -
|
|
#innodb-additional-mem-pool-size = 20M
|
|
#innodb-additional-mem-pool-size = 40M
|
|
|
|
|
|
## - innodb_buffer_pool_instances
|
|
## -
|
|
## - The number of regions that the InnoDB buffer pool is divided into.
|
|
## -
|
|
## - Note:
|
|
## - For systems with buffer pools in the multi-gigabyte range, dividing
|
|
## - the buffer pool into separate instances can improve concurrency, by
|
|
## - reducing contention as different threads read and write to cached pages.
|
|
## -
|
|
## - Default: 1
|
|
## -
|
|
#innodb-buffer-pool-instances = 1
|
|
|
|
|
|
## - innodb_log_file_size
|
|
## -
|
|
## - The size in bytes of each log file in a log group.
|
|
## -
|
|
## - Default: 5242880 (5M)
|
|
## -
|
|
## (Set .._log_file_size to 25 % of buffer pool size)
|
|
## -
|
|
#innodb-log-file-size = 100M
|
|
#innodb-log-file-size = 256M
|
|
innodb_log_file_size = 750M
|
|
|
|
## - innodb_log_buffer_size
|
|
## -
|
|
## - The size in bytes of the buffer that InnoDB uses to write to the
|
|
## - log files on disk.
|
|
## -
|
|
## - Default: 8388608 (8M)
|
|
## -
|
|
#innodb-log-buffer-size = 8M
|
|
#innodb-log-buffer-size = 32M
|
|
innodb_log_buffer_size = 64M
|
|
|
|
## - innodb_flush_log_at_trx_commit
|
|
## -
|
|
## - Controls the balance between strict ACID compliance for commit
|
|
## - operations, and higher performance that is possible when
|
|
## - commit-related I/O operations are rearranged and done in batches.
|
|
## - You can achieve better performance by changing the default value,
|
|
## - but then you can lose up to one second worth of transactions in a crash.
|
|
## -
|
|
## - In case of extrem slowly restores set
|
|
## -
|
|
## - innodb_flush_log_at_trx_commit = 2
|
|
## - innodb_log_file_size = 256M
|
|
## -
|
|
## - Also try to add (befor DROP/CREATE/INSET Statements) to the dumpfile:
|
|
## -
|
|
## - ...
|
|
## - SET FOREIGN_KEY_CHECKS=0;
|
|
## - SET unique_checks=0;
|
|
## - SET AUTOCOMMIT=0;
|
|
## -
|
|
## - DROP TABLE IF EXISTS..
|
|
## - ...
|
|
## -
|
|
## - Default: innodb-flush-log-at-trx-commit = 1
|
|
## -
|
|
#innodb-flush-log-at-trx-commit = 1
|
|
#innodb-flush-log-at-trx-commit = 2
|
|
|
|
## - innodb_lock_wait_timeout
|
|
## -
|
|
## - The length of time in seconds an InnoDB transaction waits for a row
|
|
## - lock before giving up.
|
|
## -
|
|
## - Default: 50
|
|
## -
|
|
#innodb-lock-wait-timeout = 50
|
|
|
|
## InnoDB specific variables
|
|
## -------------------------
|
|
|
|
|
|
## - sort_buffer_size
|
|
## -
|
|
## - Each session that needs to do a sort allocates a buffer of this size.
|
|
## - sort_buffer_size is not specific to any storage engine and applies
|
|
## - in a general manner for optimization.
|
|
## -
|
|
## - Default: 2097152 (2M)
|
|
## -
|
|
#sort-buffer-size = 2M
|
|
sort-buffer-size = 64M
|
|
|
|
|
|
#_ - key_buffer_size
|
|
## -
|
|
## - key_buffer_size is a MyISAM parameter
|
|
## -
|
|
## - 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.
|
|
## -
|
|
## - Default: 8388608 (8M)
|
|
## -
|
|
key-buffer-size = 384M
|
|
|
|
|
|
## - read_buffer_size
|
|
## -
|
|
## - Each thread that does a sequential scan for a MyISAM table allocates
|
|
## - a buffer of this size (in bytes) for each table it scans. If you do
|
|
## - many sequential scans, you might want to increase this value.
|
|
## -
|
|
## - Default: 131072 (128K)
|
|
## -
|
|
#read-buffer-size = 2M
|
|
read-buffer-size = 32M
|
|
|
|
## - read_rnd_buffer_size
|
|
## -
|
|
## - This variable is used for reads from MyISAM tables, and, for any
|
|
## - storage engine, for Multi-Range Read optimization.
|
|
## -
|
|
## - Default: 262144 (256K)
|
|
## -
|
|
#read-rnd-buffer-size = 8M
|
|
read-rnd-buffer-size = 32M
|
|
|
|
|
|
## - myisam_sort_buffer_size
|
|
## -
|
|
## - 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.
|
|
## -
|
|
## - Default: 8388608 (8M)
|
|
## -
|
|
#myisam-sort-buffer-size = 64M
|
|
myisam-sort-buffer-size = 256M
|
|
|
|
|
|
## - max_allowed_packet
|
|
## -
|
|
## - The maximum size of one packet or any generated/intermediate string, or
|
|
## - any parameter sent by the mysql_stmt_send_long_data() C API function.
|
|
##
|
|
## - Default: 4MB (MySQL 5.6.6), 1MB before that.
|
|
## -
|
|
#max-allowed-packet = 4M
|
|
#max-allowed-packet = 32M
|
|
max-allowed-packet = 64M
|
|
|
|
|
|
## - table_open_cache
|
|
## -
|
|
## - The number of open tables for all threads. Increasing this value
|
|
## - increases the number of file descriptors that mysqld requires.
|
|
## -
|
|
## - You can check whether you need to increase the table cache by checking
|
|
## - the Opened_tables status variable. If the value of Opened_tables is large
|
|
## - and you do not use FLUSH TABLES often (which just forces all tables to be
|
|
## - closed and reopened), then you should increase the value of the
|
|
## - table_open_cache variable.
|
|
## -
|
|
#table-open-cache = 512
|
|
table_open_cache = 8192
|
|
|
|
## - table_definition_cache
|
|
## -
|
|
## - The number of table definitions (from .frm files) that can be stored
|
|
## - in the definition cache.
|
|
## -
|
|
## - Default: (400 + (table_open_cache / 2) since 5.6.8, 400 before
|
|
## -
|
|
#table-definition-cache = 1680
|
|
#table_definition_cache = 5120
|
|
|
|
## - max_connect_errors
|
|
## -
|
|
## - Default: 100 (5.6.6), 10 (before)
|
|
## -
|
|
max-connect-errors = 999999
|
|
|
|
## - thread_concurrency
|
|
## -
|
|
## - NOTE:
|
|
## - This variable is specific to Solaris 8 and earlier systems.
|
|
## -
|
|
## - This variable is deprecated as of MySQL 5.6.1 and is removed in MySQL 5.7.
|
|
## - You should remove this from MySQL configuration files whenever you see it
|
|
## - unless they are for Solaris 8 or earlier
|
|
## -
|
|
## - (Try number of CPU's*2 for thread_concurrency)
|
|
## -
|
|
#thread-concurrency = 16
|
|
|
|
## - thread_cache_size
|
|
## -
|
|
## - How many threads the server should cache for reuse. When a client
|
|
## - disconnects, the client's threads are put in the cache if there are
|
|
## - fewer than thread_cache_size threads there.
|
|
## -
|
|
## - Default: 8 + (max_connections / 100) (5.6.8) , 0 (before)
|
|
## -
|
|
#thread-cache-size = 8
|
|
thread_cache_size = 32
|
|
|
|
## - thread_stack
|
|
## -
|
|
## - The stack size for each thread. Many of the limits detected by
|
|
## - the crash-me test are dependent on this value.
|
|
## -
|
|
## - The default of 192KB (256KB for 64-bit systems) is large enough
|
|
## - for normal operation. If the thread stack size is too small, it
|
|
## - limits the complexity of the SQL statements that the server can handle,
|
|
## - the recursion depth of stored procedures, and other memory-consuming
|
|
## - actions.
|
|
## - Default: 262144 (256K)
|
|
## -
|
|
thread-stack = 262144
|
|
|
|
|
|
## - Unbenutze Datenbank Engines deaktivieren
|
|
## -
|
|
|
|
## - skip-innodb
|
|
## -
|
|
## - Deaktiviert die Unterstützung für InnoDB
|
|
## -
|
|
## - Sincs version 5.5, you have to set default-storage-engine
|
|
## - to MyISAM, if using skip-innodb
|
|
## -
|
|
#default-storage-engine=MyISAM
|
|
#skip-innodb
|
|
|
|
|
|
## - log-error
|
|
## -
|
|
## - Log errors and startup messages to this file. If you omit the file
|
|
## - name, MySQL uses host_name.err. If the file name has no extension,
|
|
## - the server adds an extension of .err.
|
|
## -
|
|
log-error = /var/log/mysql/mysql-err.log
|
|
|
|
|
|
## - Query Log
|
|
## -
|
|
#general-log = on
|
|
#general-log_file = /var/log/mysql/mysql.log
|
|
|
|
|
|
## - ft_min_word_len
|
|
## -
|
|
## - Die minimale Länge des Wortes, das in einem FULLTEXT-Index enthalten sein darf.
|
|
## -
|
|
## - Notice!
|
|
## - if you set
|
|
## - [mysqld]
|
|
## - ft_min_word_len=3
|
|
## -
|
|
## - you should also set
|
|
## - [myisamchk]
|
|
## - ft_min_word_len=3
|
|
## -
|
|
## -
|
|
## - Vorgabewert ist 4
|
|
#ft-min-word-len = 3
|
|
|
|
## - ft_stopword_file
|
|
## -
|
|
## - Datei, aus der die Liste der Stoppwörter für die Volltextsuche ausgelesen wird.
|
|
## - Es werden alle Wörter aus der Datei verwendet; Kommentare hingegen werden nicht
|
|
## - berücksichtigt. Standardmäßig wird eine eingebaute Liste mit Stoppwörtern (wie
|
|
## - in der Datei myisam/ft_static.c definiert) verwendet. Wird diesee Variable auf den
|
|
## - Leer-String gesetzt (''), wird die Ausfilterung von Stoppwörtern deaktiviert.
|
|
## -
|
|
## - Hinweis: Wird diese Variable geändern oder den Inhalt der Stoppwortdatei selbst,
|
|
## - müssen die FULLTEXT-Indizes neu erstellt werden (REPAIR TABLE tbl_name QUICK. ).
|
|
## -
|
|
#ft-stopword-file = /usr/local/mysql/stopwords_utf8_iso8859-15.txt
|
|
|
|
|
|
|
|
## -------------
|
|
## - query cache
|
|
|
|
|
|
## - query_cache_type
|
|
## -
|
|
## - 0 : verhindert das Speichern von Abfragen im und
|
|
## - das Abrufen aus dem Cache
|
|
## - 1 : gestattet das Speichern von Abfragen im Cache.
|
|
## - Ausgenommen sind Anweisungen, die mit
|
|
## - SELECT SQL_NO_CACHE beginnen.
|
|
## - 2 : speichert nur diejenigen Anweisungen im Cache,
|
|
## - die mit SELECT SQL_CACHE beginnen.
|
|
## -
|
|
## - Removed since MySQL Version 8.0.3
|
|
## -
|
|
query_cache_type = 1
|
|
|
|
|
|
## - query_cache_limit
|
|
## -
|
|
## - Gibt die maximale Größe einzelner Abfrageergebnisse an, die im
|
|
## - Cache gespeichert werden können.
|
|
## -
|
|
## - Vorgeabewert ist 1Mbyte
|
|
## -
|
|
## -
|
|
## - Removed since MySQL Version 8.0.3
|
|
## -
|
|
#query-cache-limit = 4M
|
|
#query_cache_limit = 8M
|
|
query_cache_limit = 16M
|
|
|
|
|
|
## - query_cache_min_res_unit
|
|
## -
|
|
## - Die im Abfrage-Cache abgelegten Ergebnisse, werden nicht am Stück
|
|
## - verwaltet. Der Abfrage-Cache reserviert Blöcke zur Speicherung dieser
|
|
## - Daten nach Bedarf, d. h. wenn ein Block voll ist, wird der nächste
|
|
## - zugewiesen. Da der Speicherreservierungsvorgang (in zeitlicher Hinsicht)
|
|
## - aufwändig ist, reserviert der Abfrage-Cache die Blöcke mit einer
|
|
## - Mindestgröße, die durch die Systemvariable query_cache_min_res_unit
|
|
## - festgelegt wird. Wird eine Abfrage ausgeführt, dann wird der letzte
|
|
## - Ergebnisblock auf die tatsächliche Datengröße zugeschnitten, sodass
|
|
## - unbenutzter Speicher freigegeben wird.
|
|
## -
|
|
## - Siehe auch http://dev.mysql.com/doc/refman/5.1/de/query-cache-configuration.html
|
|
## -
|
|
## - Vorgabewert ist 4Kbyte
|
|
## -
|
|
## -
|
|
## - Removed since MySQL Version 8.0.3
|
|
## -
|
|
query-cache-min-res-unit = 8K
|
|
|
|
|
|
## - query_cache_size
|
|
## -
|
|
## - Die Größe des Abfrage-Caches.
|
|
## -
|
|
## - Wird query_cache_size auf einen Wert größer Null gesetzt, so ist zu beachten,
|
|
## - dass der Abfrage-Cache eine Mindestgröße von ca. 40 Kbyte benötigt, um seine
|
|
## - Strukturen zuzuweisen. (Der exakte Wert hängt von der Systemarchitektur ab.)
|
|
## - Wird der Wert zu niedrig angesetzt, wird eine Warnung ausgegeben.
|
|
## -
|
|
## - Vorgabewert ist 0, d. h. der Abfrage-Cache ist vorgabeseitig deaktiviert.
|
|
## -
|
|
## -
|
|
## - Removed since MySQL Version 8.0.3
|
|
## -
|
|
#query_cache_size = 32M
|
|
#query-cache-size = 128M
|
|
query_cache_size = 1024M
|
|
|
|
## - query cache
|
|
## -------------
|
|
|
|
|
|
## --------------
|
|
## - slow queries
|
|
|
|
## - slow_query_log
|
|
## -
|
|
## - Gibt an, ob das Logging für langsame Abfragen eingeschaltet (1 oder ON)
|
|
## - bzw ausgeschaltet (0 oder OFF) ist.
|
|
## -
|
|
## - Vorgabewert ist 0 oder OFF
|
|
## -
|
|
slow-query-log = 1
|
|
|
|
|
|
## - long_query_time
|
|
## -
|
|
## - Wenn eine Abfrage länger dauert als durch diese Variable (in Sekunden) angegeben,
|
|
## - erhöht der Server die Statusvariable Slow_queries entsprechend. Wird die Option
|
|
## - --log-slow-queries verwendet, wird die Abfrage in der Logdatei für langsame Abfragen
|
|
## - protokolliert. Dieser Wert wird als Echtzeit (nicht als Prozessorzeit) gemessen, d. h.
|
|
## - eine Abfrage, die bei einem System mit geringer Belastung den Schwellwert
|
|
## - unterschreitet, kann bei einem stark belasteten System bereits darüber liegen.
|
|
## - Der Mindestwert ist 1.
|
|
## -
|
|
## - Vorgabewert ist 10
|
|
## -
|
|
long-query-time = 1
|
|
|
|
|
|
## - slow_query_log_file
|
|
## -
|
|
## - Name der Logdatei, in die langsame Abfragen gespeichert werden.
|
|
## -
|
|
## - Vorgabewert ist <host-name>-slow.log
|
|
## -
|
|
slow-query-log-file = /var/log/mysql/slow_query.log
|
|
|
|
|
|
## - log-queries-not-using-indexes
|
|
## -
|
|
## - Gibt an, ob Abfragen, die keine Indizes benutzen in der Logdatei
|
|
## - für langsame Abfragen mitgespeichert werden sollen.
|
|
## -
|
|
## - Vorgabewert ist 0
|
|
## -
|
|
#log-queries-not-using-indexes = 1
|
|
log-queries-not-using-indexes = 0
|
|
|
|
## - slow queries
|
|
## --------------
|
|
|
|
## - join_buffer_size
|
|
## -
|
|
## - Die Größe des Puffers, der für Joins benutzt wird, die keine Indizes verwenden
|
|
## - und deswegen vollständige Tabellenscans durchführen. Normalerweise besteht die
|
|
## - beste Möglichkeit der Realisierung schneller Joins darin, Indizes hinzuzufügen.
|
|
## - Erhöhen Sie den Wert von join_buffer_size, um einen schnelleren vollständigen
|
|
## - Join zu implementieren, wenn das Hinzufügen von Indizes nicht möglich ist. Für
|
|
## - jeden vollständigen Join zwischen zwei Tabellen wird ein Join-Puffer hinzugefügt.
|
|
## - Für einen komplexen Join zwischen mehreren Tabellen, für den Indizes nicht verwendet
|
|
## - werden, sind unter Umständen mehrere Join-Puffer erforderlich.
|
|
## -
|
|
## - Wird die Option --log-slow-queries (ON) verwendet, werden Abfragen, die keine
|
|
## - Indizes verwenden, in das Log für langsame Abfragen geschrieben.
|
|
## -
|
|
## - Vorgabewert ist 128K
|
|
## -
|
|
#join-buffer-size = 384K
|
|
#join_buffer_size = 768K
|
|
#join_buffer_size = 1024K
|
|
join_buffer_size = 1536K
|
|
|
|
|
|
|
|
## - max_heap_table_size
|
|
## -
|
|
## - Diese Variable bestimmt die maximale Größe, auf die MEMORY-Tabellen anwachsen dürfen.
|
|
## - Der Wert der Variable wird zur Berechnung von MAX_ROWS-Werte für MEMORY-Tabellen
|
|
## - verwendet. Die Einstellung der Variable hat keine Auswirkungen auf bereits vorhandene
|
|
## - MEMORY-Tabellen, sofern diese nicht mit einer Anweisung wie CREATE TABLE neu erstellt
|
|
## - oder mit ALTER TABLE oder TRUNCATE TABLE modifiziert werden.
|
|
## -
|
|
## - Vorgabewert ist 16Mbyte
|
|
## -
|
|
#max-heap-table-size = 96M
|
|
#max_heap_table_size = 128M
|
|
#max_heap_table_size = 768M
|
|
#max_heap_table_size = 1024M
|
|
max_heap_table_size = 2048M
|
|
|
|
|
|
## - tmp_table_size
|
|
## -
|
|
## - Überschreitet eine temporäre Tabelle im Arbeitsspeicher diese Größe, wandelt MySQL
|
|
## - sie automatisch in eine MyISAM-Tabelle auf der Festplatte um.
|
|
## -
|
|
## - Werden viele erweiterte GROUP-BY-Anfragen ausgeführt (und ist genügend Speicher
|
|
## - vorhanden), so sollte diese Variable erhöht werden.
|
|
##
|
|
## - Default: 16777216 (16M)
|
|
## -
|
|
## - Note:
|
|
## - Effective in-memory tmp_table_size is limited to max_heap_table_size.
|
|
## -
|
|
#tmp-table-size = 96M
|
|
#tmp_table_size = 768M
|
|
tmp_table_size = 2048M
|
|
|
|
|
|
## - angepasste Einstellungen
|
|
## ------------------------------------------
|
|
|
|
|
|
[mysqldump]
|
|
quick
|
|
max-allowed-packet = 32M
|
|
|
|
[mysql]
|
|
no-auto-rehash
|
|
# Remove the next comment character if you are not familiar with SQL
|
|
#safe-updates
|
|
local-infile = 1
|
|
|
|
[myisamchk]
|
|
key-buffer-size = 256M
|
|
sort-buffer-size = 256M
|
|
read-buffer = 2M
|
|
write-buffer = 2M
|
|
|
|
|
|
## ------------------------------------------
|
|
## - angepasste Einstellungen
|
|
|
|
## - ft_min_word_len
|
|
## -
|
|
## - Die minimale Länge des Wortes, das in einem FULLTEXT-Index enthalten sein darf.
|
|
## -
|
|
## - Notice!
|
|
## - if you set
|
|
## - [mysqld]
|
|
## - ft_min_word_len=3
|
|
## -
|
|
## - you should also set
|
|
## - [myisamchk]
|
|
## - ft_min_word_len=3
|
|
## -
|
|
## -
|
|
## - Vorgabewert ist 4
|
|
#ft-min-word-len = 3
|
|
|
|
## - angepasste Einstellungen
|
|
## ------------------------------------------
|
|
|
|
|
|
[mysqlhotcopy]
|
|
interactive-timeout
|
|
|
|
|
|
## - sql_mode
|
|
## -
|
|
## - Since Version 8.0.11 NO_AUTO_CREATE_USER is no longer available
|
|
## -
|
|
## - For now, we will use the default value, it seems to be ok.
|
|
## -
|
|
#sql-mode = "ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
|