Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "option setting of my.cnf files in MySQL". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

The mysqld server maintains two variables. Global variables affect the global operation of the server. Session variables affect specific client connection-related operations.

You can set global variables in the options file or on the command line.

Set the session variable with the Set command, but it can also set the global variable.

Just like the initialization parameter file in oracle. Here are some commonly used options, which are also available in MySQL's management documentation.

[@ more@]

Such as

Mysql > SET GLOBAL sort_buffer_size = 10 * 1024 * 1024

Mysql > SET SESSION sort_buffer_size = 10 * 1024 * 1024

If you want to use the SET statement to limit the maximum value that the system variable can be set, you can specify it with the option of-- maximum-var_name when the server starts.

For example, to prevent the value of query_cache_size from exceeding 32MB at run time, use the option-- maximum-query_cache_size=32M.

Mysqld_safe option

-- basedir=path

The path to the MySQL installation directory.

-- core-file-size

The size of the kernel file that mysqld can create. The option value is passed to ulimit-c.

-- datadir=path

The path to the data directory.

-- defaults-file=path

The name of the option file that is read in place of the common options file. If given, this option must be preferred.

-- log-error=path

Writes the error log to the given file.

-nice=priority

Use the nice program to set the scheduling priority of the server based on a given value.

-- open-files-limit=count

The number of files that mysqld can open. The option value is passed to ulimit-n. Please note that you need to start mysqld_safe with root to ensure that it works correctly!

-- pid-file=path

The path to the process ID file.

-- port=port_num

The port number used to listen to the TCP/IP connection. The port number must be 1024 or greater unless MySQL is running as a root system user.

The Unix socket file and TCP/ IP port number for each mysqld must be different.

-- ledir=path

The path to the directory that contains the mysqld program. Use this option to explicitly indicate the server location.

-- no-defaults

Do not read any option files. If given, this option must be preferred.

-- skip-character-set-client-handshake

Ignore the character set information sent by the client and use the default character set of the server.

-- socket=path

Unix socket file for local connections.

The Unix socket file and TCP/ IP port number for each mysqld must be different.

-- timezone=zone

Sets the TZ time zone environment variable for the given option value.

-- user= {user_name | user_id}

Run the mysqld server as user name user_name or digital user ID user_id.

The "user" refers to the system login account, not the MySQL user in the authorization table.

Mysqld command line options

Mysqld reads options from [mysqld] and [server] groups

-- ansi

Use standard (ANSI) SQL syntax instead of MySQL syntax.

-- basedir=path,-b path

The path to the MySQL installation directory. Usually all paths are resolved according to that path.

-- bootstrap

Mysql_install_db scripts use this option to create MySQL authorization tables without starting the MySQL server

-- console

Write error log messages to stderr and stdout, even if-- log-error is specified.

-- character_sets_dir=path

The directory where the character set is installed.

-- chroot=path

Place the mysqld server in a closed environment during startup through the chroot () system call. This is a recommended safety measure. Note that you can use this option to restrict LOAD DATA INFILE and SELECT. INTO OUTFILE .

-- character_set_server=charset

Use charset as the default server character set.

-- core_file

If mysqld terminates, write the kernel file.

-- datadir=path,-h path

The path to the data directory.

-- default_table_type=type

Sets the default type of the table.

-- debug [= debug_options],-# [debug_options]

If MySQL is configured with-- with-debug, you can use this option to get a trace file that tracks what mysqld is doing. The debug_options string is usually'd _ v _ v _ r _ name'.

-- default_time_zone=type

Sets the default server time zone. This option sets the global time_zone system variable. The default time zone is the same as the system time zone (given with the system_time_zone system variable value)

-- delay_key_write [= OFF | ON | ALL]

How to use the DELAYED KEYS option. The key write delay causes the key buffer not to be emptied when the MyISAM table is written again. This option applies only to MyISAM tables.

OFF DELAY_KEY_WRITE is ignored.

ON MySQL uses the DELAY_KEY_WRITE option in CREATE TABLE. This is the default value.

ALL processes all newly opened tables in the same way that the DELAY_KEY_WRITE option is enabled to create tables.

If DELAY_KEY_WRITE is enabled, the key buffer of the table that uses this item is not emptied each time the index is updated, but only when the table is closed.

But if you use this feature, you apply the-- myisam_recover option to start the server and add automatic checks for all MyISAM tables.

-- external_locking

Lock it with the system. Please note that if you use this option on a system where lockd does not work fully (for example, in Linux), mysqld is prone to deadlock.

If you use this option to update the MyISAM table in many MySQL processes, you must ensure that the following conditions are met:

A cache that uses a query that is being updated by another process is not available.

You should not use-- delay-key-write=ALL or DELAY_KEY_WRITE=1 in shared tables.

The easiest way is to use a combination of-- external-locking and-- delay-key-write=OFF-- query-cache-size=0.

It is not possible by default, because in many settings, it is useful to use the above options in combination.

-- flush

Clear the changes to the hard drive after executing the SQL statement. In general, MySQL writes all changes to the hard disk after the SQL statement is executed, allowing the operating system to process synchronization with the hard disk.

-- init_file=file

Read the SQL statement from this file at startup. Each statement must be on the same line and should not include comments.

-language=lang_name,-L lang_name

The client error message is given in the given language. By default, mysqld gives error messages in English.

-- large_pages

Some hardware / operating system architectures support memory pages that are larger than the default (usually 4 KB). The actual support depends on the hardware and OS used.

Applications that access a lot of memory can improve performance by reducing Translation Lookaside Buffer (TLB) losses by using larger pages.

This option is disabled by default.

-log [= file],-l [file]

If you want to know what's going on inside mysqld, you should start it with the-- log [= file_name] or-l [file_name] options. If no value is given for file_name, the default name is host_name.log. All connections and statements are recorded to the log file. This log can be useful when you suspect that an error occurred on the client and want to know exactly what statements the client sent to mysqld.

Mysqld logs statements to the query log in the order it receives. This may be different from the order in which it is executed.

-log_bin= [file]

Binary log file. Record all queries that change the data in this file. Used for backup and replication.

It is recommended that you specify a file name, otherwise MySQL uses host_name-bin as the base name of the log file.

When running the server, if binary logging is enabled, the performance is about 1% slower.

-- binlog_do_db=db_name

Tell the primary server that if the current database (that is, the database selected by USE) is db_name, updates should be recorded in the binary log.

-- binlog_ignore_db=db_name

Tell the primary server that if the current database (that is, the database selected by USE) is db_name, updates should not be saved to the binary log.

-- log_error [= file]

Log errors and startup messages for the file.

If you do not specify a file name, MySQL uses host_name.err as the file name. If the file name does not have an extension, add the .err extension.

-- log_isam [= file]

Record all MyISAM changes in this file

-- log_slow_queries [= file]

Log all queries that take more than long_query_time seconds to execute in this file.

-log_warnings,-W

Will warn such as Aborted connection... Print to the error log. It is recommended that you enable this option, which is also enabled by default.

-- memlock

Lock the mysqld process in memory. Note that you need to run the server as root when using this option, but it is not a good idea for security reasons.

-- myisam_recover [= option [, option...]]

The storage engine MyISAM is set to recovery mode. The option value is any combination of DEFAULT, BACKUP, FORCE, or quick values.

If you specify multiple values, separate them with commas.

If this option is used, when mysqld opens the MyISAM table, check to see if the table is marked as crashing or is not properly closed.

If this is the case, mysqld checks the table. If the table is corrupted, mysqld attempts to maintain it.

DEFAULT is the same as not using the-- myisam-recover option.

BACKUP if the data file is changed during the recovery process, back up the tbl_name.MYD file as tbl_name-datetime.BAK.

FORCE recovers even if the .MYD file will lose multiple lines.

QUICK do not check the rows in the table if you do not delete the block.

-- open_files_limit=count

Used to change the number of mysqld file descriptors.

-- pid_file=path

The path to the process ID file used by mysqld_safe.

-- port=port_num,-P port_num

The port number used to listen to the TCP/IP connection.

-- safe_mode

Skip some optimization phases.

-- skip_bdb

Disable the BDB storage engine. This saves memory and may speed up some operations. Do not use this option if you need a BDB table.

-- skip_concurrent_insert

Turn off the ability to select and insert in MyISAM tables at the same time. Use this option only if you find a defect.

-- skip_grant_tables

This option prevents the server from using the permissions system. This permission allows users accessing the server to have unrestricted access to all databases

-- skip_external_locking

Do not use system locking.

-- skip_host_cache

For faster resolution between name-IP, do not use internal hostname caching. Instead, query the DNS server each time the client connects.

-- skip_innodb

Disable the InnoDB storage engine. This saves memory and may speed up some operations.

-- skip_networking

No frame listens to the TCP/IP connection. Mysqld interoperability must be done through named pipes or shared memory (in Windows) or Unix socket files (in Unix).

This option is highly recommended for systems that only allow local clients.

-- socket=path

In Unix, this option specifies the Unix socket file used for local connections.

-- skip_safemalloc

If MySQL is configured with-- with-debug=full, all MySQL programs check for memory overflows during memory allocation and release.

Checking is slow, so if you don't need it, you can use the-- skip-safemalloc option to avoid it.

-- transaction_isolation=level

Sets the default transaction isolation level, which can be READ-UNCOMMITTED, READ-COMMITTEE, REPEATABLE-READ or SERIALIZABLE.

The default is REPEATABLE-READ.

-- tmpdir=path,-t path

Create the directory path of the temporary file.

-- temp_pool

This option causes most temporary files created by the server to use a series of file names instead of a unique file name for each new file.

-- bdb_cache_size

The size of the buffer allocated to cache indexes and rows for the BDB table. If you do not use the BDB table, you apply-- skip-bdb to start mysqld so that the cache is not wasted.

-- bdb_home

The base directory of the BDB. Should have the same value as the datadir variable.

-- bdb_log_buffer_size

The size of the buffer allocated to cache indexes and rows for the BDB table.

-- bdb_logdir

The directory where the BDB storage engine writes its log files.

-- bdb_max_lock

The maximum number of locks that can be activated under the BDB table (default is 10000).

-- binlog_cache_size

The cache size that holds binary log SQL statements during a transaction.

Binary log caching is the memory allocated for each client if the server supports the transaction storage engine and the server enables binary logging (--log-bin option).

-- bulk_insert_buffer_size

MyISAM uses a dedicated tree cache to make INSERT. SELECT 、 INSERT... VALUES (...), (...), And LOAD DATA INFILE's chunks.

Get in faster. Note: this cache is used only if data is added to a non-empty table. The default value is 8MB.

-- character_set_client

The character set of the statement from the client.

-- character_set_database

The character set used by the default database. The server sets this variable when the default database changes.

If there is no default database, the variable is the same as character_set_server.

-- character_set_results

The character set used to return query results to the client.

-- character_sets_dir

Character set installation directory.

-- completion_type

Transaction end type:

If the value is 0 (the default), COMMIT and ROLLBACK are not affected.

If the value is 1 million commit and ROLLBACK are equal to COMMIT AND CHAIN and ROLLBACK AND CHAIN respectively.

(the new transaction starts immediately with the same interval level as the transaction that just ended).

If the value is 2, ROLLBACK and commit are equivalent to COMMIT RELEASE and ROLLBACK RELEASE, respectively. (the server is disconnected after the transaction is terminated).

-- concurrent_insert

If ON (the default), MySQL allows INSERT and SELECT statements to run in parallel in an MyISAM table with no empty blocks in the middle.

0 pass

1 (default) enables parallel insertion in MyISAM tables with no empty data blocks

2 enable parallel inserts for all MyISAM tables. If the table has an empty record or is being used by another thread, the new row is inserted at the end of the table.

If the table is not in use, MySQL performs a normal read lock and inserts a new row into the empty record.

-- delayed_insert_limit

After inserting the delayed_insert_limit delay line, the INSERT DELAYED processor thread checks for pending SELECT statements. If so, allow delayed rows to execute before continuing to insert them.

-- delayed_insert_timeout

The amount of time an INSERT DELAYED processor thread should wait for an INSERT statement before terminating.

-- delayed_queue_size

This is the limit on the number of rows in the queue when processing INSERT DELAYED statements in each table. If the queue is full, the client executing the INSERT DELAYED statement should wait until there is more room in the queue.

-- div_precision_increment

This variable indicates the number of bits of accuracy that can be increased by performing a division operation with the / operator. The default value is 4. The minimum and maximum values are 0 and 30, respectively.

-- expire_logs_days

The number of days that the binary log is automatically deleted. The default value is 0, which means "no automatic deletion". May be deleted at startup and during binary log loops.

-- flush_time

If set to a non-zero value, all tables are closed every flush_time seconds to free hard disk resources and synchronize uncleaned data.

We recommend using this option only in Windows 9x or Me, or in systems with minimal resources.

-- init_connect

The string executed by the server for each connected client. A string consists of one or more SQL statements. To specify multiple statements, separate them with semicolons.

For example: [mysqld]

Init_connect='SET AUTOCOMMIT=0'

-- join_buffer_size

The size of the buffer used for full join (use join operation when indexes are not used).

-- key_buffer_size

The index block of the MyISAM table allocates a buffer that is shared by all threads. Key_buffer_size is the size of the index block buffer. The key buffer is the key cache.

The maximum allowable setting for key_buffer_size is 4GB. Typically 25% of the memory of a machine that primarily runs MySQL.

To check the performance of the key buffer by executing the SHOW STATUS statement and checking the Key_read_requests, Key_reads, Key_write_requests, and Key_writes state variables.

The proportion of Key_reads/Key_read_requests should be less than 0.01generally. If you use updates and deletes, the Key_writes/Key_write_requests ratio is usually close to 1, but if you update multiple rows at the same time or if you are using the DELAY_KEY_WRITE option, it may be much smaller.

1-(Key_blocks_unused * key_cache_block_size) / key_buffer_size)

This value is a divisor because part of the key buffer is allocated for internal management structure.

-- key_cache_block_size

The byte size of the block in the key value cache. The default value is 1024.

-- locked_in_memory

Whether to lock the mysqld in memory with-memlock.

-- lower_case_file_system

This variable indicates whether the file system in which the data directory resides is case-sensitive. ON indicates that it is not sensitive to the case of file names, while OFF indicates that it is sensitive.

-- lower_case_table_names

If set to 1, the table name is saved to the hard disk in lowercase, and the table name comparison is not case-sensitive.

If set to 2, the table name is saved as specified, but compared in lowercase.

Each table in the database corresponds to at least one file in the database directory (or multiple, depending on the storage engine).

Therefore, the case sensitivity of the operating system used determines the case sensitivity of database and table names.

This means that database and table names are case-sensitive in most Unix and insensitive in Windows.

-- max_binlog_cache_size

If a multi-statement transaction requires more memory, you will get the error Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage.

-- max_binlog_size

If the binary log writes more than the given value, the log scrolls. You cannot set this variable to be greater than 1GB or less than 4096 bytes. The default value is 1GB.

If you are using transactions: transactions are written to binary logs in one block, so they cannot be split by several binary logs.

Therefore, if you have large transactions, the binary log may be larger than max_binlog_size.

-- max_connect_errors

If the number of broken connections to the host exceeds that number, the host blocks subsequent connections. You can use the FLUSH HOSTS statement to unlock the locked host.

-- max_connections

The number of parallel client connections allowed.

-- max_delayed_threads

Do not start more than this number of threads to process INSERT DELAYED statements. If all INSERT DELAYED threads are already in use

When you want to insert data into the new table, the row insert does not seem to have a DELAYED attribute specified. If you set this value to 0, MySQL will not create a thread to process DELAYED lines

The result is that DELAYED is completely disabled.

-- max_error_count

Saves the maximum number of errors, warnings, and comments displayed by SHOW ERRORS or SHOW WARNINGS.

-- max_heap_table_size

This variable sets the maximum size to which the MEMORY (HEAP) table can grow. This variable is used to calculate the MAX_ Rows value of the MEMORY table.

Setting this variable on an existing MEMORY table has no effect unless you recreate the table with statements such as CREATE TABLE or TRUNCATE TABLE.

-- max_join_size

SELECT statements that may need to be checked for more than max_join_size rows (for a single table statement) or row combinations (for multiple table statements) or that may execute more than max_join_size hard disk queries are not allowed. By setting this value, you can capture SELECT statements that are not used correctly and may take a long time.

Set the variable to a value other than DEFAULT, and reset the value of SQL_BIG_SELECTS to 0.

-- max_length_for_sort_data

Determines the limit of the index value size of the filesort algorithm used.

-- max_relay_log_size

If the replication exceeds the given value when writing to the relay log from the server, the relay log is scrolled. With this variable, you can set different restrictions on relay logs and binary logs. However, setting this variable to 0j MySQL can use max_binlog_size for binary and relay logs. Max_relay_log_size must be set between 4096 bytes and 1GB (inclusive), or 0. The default value is 0.

-max_seeks_for_key

Limits the maximum number of searches when finding rows based on key values. The MySQL optimizer assumes that when you use scan keys to search for matching rows within a table, you do not need to search for more than that number of keys, regardless of the actual cardinality of the keys. By setting this value to a lower value (100?), you can force MySQL to select a key value instead of a table scan.

-Max_sort_length

The number of bytes used when sorting BLOB or text values. Only the first max_sort_length bytes of each value are used; the rest are ignored.

-- max_tmp_tables

The maximum number of temporary tables that clients can open at the same time.

-max_user_connections

The maximum number of simultaneous connections allowed for any given MySQL account. A value of 0 means "no limit".

-max_write_lock_count

Partial read locking is allowed when the write lock limit is exceeded.

-- max_allowed_packet

The maximum size of the package or any generated / intermediate string.

The packet message buffer is initialized to net_buffer_length bytes, but can grow to max_allowed_packet bytes if needed.

This value is small by default to capture large (possibly incorrect) packets.

If you use large BLOB columns or long strings, you must increase this value. It should be as big as the largest BLOB you want to use.

The protocol of max_allowed_packet is limited to 1GB.

-myisam_data_pointer_size

The default pointer size, in bytes, is used by CREATE TABLE to create the MyISAM table when the MAX_ROWS option is not specified.

The variable cannot be less than 2 or greater than 7. The default value is 6.

-myisam_sort_buffer_size

Sorts the buffer allocated by the MyISAM index during REPAIR TABLE or creating an index with CREATE INDEX or ALTER TABLE.

-myisam_max_sort_file_size

The maximum space size of temporary files that MySQL is allowed to use when rebuilding MyISAM indexes (during REPAIR TABLE, ALTER TABLE, or LOAD DATA INFILE).

If the size of the file exceeds this value, the index is created using the key-value cache, which is much slower. The value is measured in bytes.

-myisam_stats_method

How the server handles null values when collecting statistics about index value distribution for the MyISAM table.

This variable has two possible values, nulls_equal and nulls_unequal.

Nulls_equal means that all null values are the same, and nulls_unequal means that each null value is treated as a different value.

-- net_buffer_length

Resets the communication buffer to this value between queries. In general, it should not be changed, but if the memory is small

It can be set to the desired length of the SQL statement sent by the client. If the statement exceeds that length, the buffer automatically expands to max_allowed_packet bytes.

-query_cache_limit

Do not cache results greater than this value. The default value is 1048576 (1MB).

-query_cache_min_res_unit

The size (in bytes) of the smallest block allocated by the query cache. The default value is 4096 (4KB).

-query_cache_size

The amount of memory allocated for caching query results. The default value is 0, which disables query caching.

-query_cache_type

Sets the query cache type. This variable is set to ON by default.

0 or OFF do not cache or query results. Note that this does not cancel the allocated query cache. To cancel, you should set query_cache_size to 0.

1 or ON caches all query results except that start with SELECT SQL_NO_CACHE.

2 or DEMAND caches only query results that start with SELECT SQL_NO_CACHE.

-query_cache_wlock_invalidate

In general, when the client locks the MyISAM table with WRITE, if the query result is in the query cache, the other clients are not locked

You can query the table. Setting this variable to 1 allows WRITE locking on the table, making all queries against the table in the query cache illegal.

In this way, when the lock takes effect, you can force other clients trying to access the table to wait.

-query_prealloc_size

The size of the fixed buffer used for query analysis and execution. The buffer is not released between queries. If you execute complex queries

Assigning a larger query_prealloc_ size value can help improve performance because it reduces the need for the server to allocate memory during the query process.

-read_buffer_size

The size (in bytes) of the buffer allocated for each table scanned when each thread scans continuously. If you perform multiple consecutive scans, you may need to increase this value

The default value is 131072.

-read_only

When the variable pair replication slave server is set to ON, the slave server does not allow updates unless the slave server thread or user has SUPER permission.

-relay_log_purge

Disable or enable automatic emptying of relay logs when relay logs are no longer needed. The default value is 1 (enabled).

-- read_rnd_buffer_size

When the rows are read in the sorted order, the rows are read through the buffer to avoid searching the hard disk. Setting this variable to a larger value can greatly improve the performance of ORDER BY. However, this is a buffer allocated for each client, so you should not set the global variable to a larger value. Instead, change the session variable only for clients that need to run large queries.

-skip_networking

This value is ON if the server only allows local (non-TCP/IP) connections. In Unix, local connections use Unix socket files.

In Windows, local connections use named pipes or shared memory

-sort_buffer_size

The size of the buffer allocated by each sort thread. Increase this value to speed up ORDER BY or GROUP BY operations.

-sync_binlog

If positive, when each sync_binlog'th writes to the binary log, the MySQL server synchronizes its binary log to the hard disk (fdatasync ()).

Note that in autocommit mode, each statement executed is written to the binary log, otherwise each transaction is written.

-sync_frm

If this variable is set to 1, its .frm files are synchronized to the hard disk (fdatasync ()) when a non-temporary table is created; this is slower but safer in the event of a crash.

The default value is 1.

-table_cache

The number of tables opened by all threads. Increasing this value increases the number of file descriptors required by mysqld.

You can check the Opened_tables status variable to see if you need to add table cache.

-thread_cache_size

How many threads should the server cache for reuse. When the client disconnects, if the thread is less than thread_cache_size, the client thread is cached. When a thread is requested, the thread can be reused from the cache if allowed, and a new thread is created only if the cache is empty. If there are many new connections, you can increase this variable to improve performance. In general, if the thread executes well, the performance improvement is not significant. Check the difference between Connections and Threads_created state variables, and you can see the efficiency of thread caching.

This is the end of the content of "option Settings for my.cnf Files in MySQL". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.