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

This article is about how to view the directory where mysql reads my.cnf by default. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

View the directory where mysql reads my.cnf by default

If it is not set to use the specified directory when my.cnf,mysql starts, it will read the installation directory root directory and the my.cnf file under the default directory, and view the default directory for reading configuration files when mysql starts. The command is as follows:

Mysql-- help | grep 'my.cnf'

What is in the command output is that mysql searches the directory of my.cnf by default, first in order.

[client]

#

# #

# MySQL client configuration #

# #

#

Port = 3306

# MySQL client default port number

Socket = / data/mysql/my3306/mysql.sock

# Storage path of Unix socket files for local connections

Default-character-set = utf8mb4

# MySQL client default character set

[mysql]

#

# #

# MySQL command line configuration #

# #

#

Auto-rehash

# enable tab completion feature

Socket = / data/mysql/my3306/mysql.sock

# Storage path of Unix socket files for local connections

Default-character-set = utf8mb4

# MySQL client default character set

Max_allowed_packet = 256m

# specifies the maximum amount of messages transmitted at one time in network transmission. The system default value is 1MB, the maximum value is 1GB, and a multiple of 1024 must be set.

[mysqld]

#

# #

# MySQL server configuration #

# #

#

# #

# #

# General #

# #

# #

Port = 3306

# TCP/IP port that the MySQL server listens to by default

Socket = / data/mysql/my3306/mysql.sock

# Storage path of Unix socket files for local connections

Pid_file = / data/mysql/my3306/mysql.pid

# path where process ID files are stored

Basedir = / app/mysql

# MySQL Software installation path

Datadir = / data/mysql/my3306

# path for storing MySQL data files

Tmpdir = / data/mysql/my3306

# MySQL temporary file storage path

Character_set_server = utf8mb4

# MySQL server character set

Collation_server = utf8mb4_general_ci

# MySQL server-side alignment rules

Default-storage-engine = InnoDB

# set the default storage engine to InnoDB

Autocommit = OFF

# defaults to ON, sets it to OFF, and disables automatic transaction commit

Transaction_isolation = READ-COMMITTED

# MySQL supports four transaction isolation levels, which are:

# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE.

# if not specified, MySQL defaults to REPEATABLE-READ,ORACLE and defaults to READ-COMMITTED

Event_scheduler = ON

# enable event Scheduler event_scheduler

# explicit_defaults_for_timestamp = ON

# controls the characteristics of the TIMESTAMP data type. The default is OFF. The timestamp column is automatically updated when it is set to ON,update. (will be abandoned in the future)

Lower_case_table_names = 1

# whether the library name and table name are case sensitive. The default is 0 and the setting is 1, which is case-insensitive. The tables and databases created are all stored on disk in lowercase.

# #

# #

# Network & Connection #

# #

# #

Max_connections = 1000

The maximum number of concurrent connections allowed by # MySQL, which is the default value of 151.This value needs to be increased if Too Many Connections error prompts occur frequently.

Max_user_connections = 1000

# the maximum number of connections for each database user (the maximum number of connections that the same account can connect to the mysql service at the same time). Default is 0, which means there is no limit.

Back_log = 500,

# the backlog request stack size set when MySQL listens to the TCP port. The default is 50 (max_connections/5), and the maximum is no more than 900.

Max_connect_errors = 10000

# the maximum number of connection requests for each host that were abnormally interrupted. For the same host, if there is an incorrect connection that exceeds the number of values for this parameter, the host will be disabled from connecting. To unblock the host, execute: FLUSH HOST.

Interactive_timeout = 28800

# the number of seconds the server waits for activity before closing the interactive connection. An interactive client is defined as a client that uses the CLIENT_INTERACTIVE option in mysql_real_connect (). Default: 28800 seconds (8 hours)

Wait_timeout = 28800

# the number of seconds the server waits for activity before closing a non-interactive connection. Default: 28800 seconds (8 hours)

# specify the maximum connection time for a request. When the MySQL connection is idle for more than a certain period of time, it will be forcibly closed. For servers with memory around the 4GB, you can set it to 5: 10.

# if there are frequent Too Many Connections error prompts, or if the show processlist command finds a large number of sleep processes, you need to reduce both interactive_timeout and wait_ timeout values.

Connect_timeout = 28800

# the timeout number of seconds to wait for a handshake when obtaining a connection will only take effect when logging in. The main purpose is to prevent the application of reconnection from increasing the number of connections too fast when the network is not good, which is generally fine by default.

Open_files_limit = 5000

# mysqld can open the maximum number of files, the default minimum of 1024, if there are too mant open files and so on, you need to increase this value.

Max_allowed_packet = 256m

# specifies the maximum amount of messages transmitted at one time in network transmission. The system default value is 1MB, the maximum value is 1GB, and a multiple of 1024 must be set.

# #

# #

# Thread & Buffer #

# #

# #

Sort_buffer_size = 2m

# sort buffer size, connection-level parameter. Default size is 2MB. If you want to increase the speed of ORDER BY, first see if you can let MySQL use the index, and then you can try to increase the value.

Read_buffer_size = 160m

# Sequential read buffer size, connection-level parameter. The allocated memory corresponding to this parameter is exclusive per connection. A request for a sequential scan of the table will be allocated a read buffer.

Read_rnd_buffer_size = 160m

# Random read buffer size, connection-level parameter. The allocated memory corresponding to this parameter is exclusive per connection. The default is 256KB and the maximum is 4GB. When rows are read in any order, a random read cache is allocated.

Join_buffer_size = 320m

# Joint query buffer size, connection-level parameter. The allocated memory corresponding to this parameter is exclusive to each connection.

Bulk_insert_buffer_size = 64m

# bulk insert data cache size, which can effectively improve the insertion efficiency. The default is 8m.

Thread_cache_size = 8

# the maximum number of connection threads stored in the server thread buffer pool. The default value is 8. If there is still space in the cache when disconnecting, the client thread will be placed in the cache and will be read from the cache first when the thread is requested again.

# the rules for setting physical memory are as follows: 1G-> 8pr 2G-> 16je 3G-> 32, greater than 3G-> 64

Thread_stack = 256K

# the memory allocated to each connection by mysql when it is created. The default 192KB, which satisfies most scenarios, does not touch it unless necessary, but sets the range 128KB~4GB.

Query_cache_type = 0

# disable query caching

Query_cache_size = 0

# query cache size, in systems with high concurrency and large write volume, it is recommended to disable this feature.

Query_cache_limit = 4m

# specify the buffer size that can be used by a single query. The default is 1m

Tmp_table_size = 1024m

# MySQL's heap (stacking) table cache size, that is, memory temporary table, the default size is 32m. If this value is exceeded, the temporary table is written to disk. Increase this value in a DW environment where many advanced GROUP BY queries are frequently done.

# what actually limits is the minimum values of tmp_table_size and max_heap_table_size.

Max_heap_table_size = 1024m

# the size of the memory table (memory table) that the user can create, which is used to calculate the maximum row value of the memory table.

Table_definition_cache = 400

# Table defines the cache area and caches the frm file. The table definition (global) is global and can be effectively shared by all connections.

Table_open_cache = 1000

# all SQL threads can open the number of table caches and cache ibd/MYI/MYD files. Open tables (session level) are used per thread and per table.

Table_open_cache_instances = 4

# the number of partitions that can be split into table cache to reduce lock competition, with a maximum of 64.

# #

# #

# Safety #

# #

# #

# sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY

Sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER

# SQL syntax mode supported by MySQL, SQL Mode combination mode is helpful when data migration is carried out between other heterogeneous databases.

Local_infile = OFF

# disable LOAD DATA LOCAL command

Plugin-load = validate_password.so

# encryption authentication plug-in to force mysql to set complex passwords

Skip-external-locking

# skip-locking

# avoid external locking of MySQL, reduce the probability of error, and enhance stability.

Skip-name-resolve

# prohibit MySQL from performing DNS resolution on external connections, and eliminate MySQL for DNS resolution. If this option is enabled, all remote host connection authorizations must use IP addresses, otherwise MySQL will not be able to process connection requests properly!

# skip-networking

# CP/IP connections are not allowed, only through named pipes (Named Pipes), shared memory (Shared Memory), or Unix sockets (Socket) files.

# if the Web server accesses the MySQL database server by remote connection, do not turn on this option, otherwise you will not be able to connect properly!

# it is suitable for applications and databases to share a server, and other clients cannot access the database remotely through the network

# #

# #

# Logs #

# #

# #

# General Log # #

General_log = OFF

# close the general query log

General_log_file = / data/mysql/my3306/general.log

# General query log storage path

# Slow Log # #

Slow_query_log = ON

# enable slow query log

Slow_query_log_file = / data/mysql/my3306/slow.log

# Storage path of slow query logs

Long_query_time = 10

# queries over 10 seconds are recorded to the slow query log. Default is 10.

Log_queries_not_using_indexes = ON

# for queries that do not use indexes, slow query logs may be recorded, which may lead to rapid growth of slow query logs

Log_slow_admin_statements = ON

# execute slow management statements and record to slow query log

# for example, ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, and REPAIR TABLE.

# Error Log #

Log_error = / data/mysql/my3306/error.log

# error log storage path

Log_error_verbosity = 2

# Global dynamic variables, default 3, range: 1 to 3

# indicates the information recorded in the error log. 1: only record error information; 2: record error and warnings information; 3: record error, warnings and ordinary notes information

# #

# #

# Replication #

# #

# #

# Bin Log # #

Server_id = 6

# Database Server ID

Log_bin = / data/mysql/my3306/binlog

# binary log storage path

Log_bin_index = / data/mysql/my3306/binlog.index

# same as binlog, define the location and name of binlog

Binlog_format = row

# binlog format, there are 3 modes of STATEMENT,ROW,MIXED for replication

Expire_logs_days = 10

# keep only binlog logs for the last 10 days

Max_binlog_size = 50m

# maximum capacity of each binlog log file

Binlog_cache_size = 2m

# binlog cache size allocated by each session

# logs generated before a transaction is committed are recorded in Cache; after a transaction is committed, the log is persisted to disk

Log_slave_updates = ON

# enable log_slave_updates and record the update operation of the library into the binlog log

Sync_binlog = 1

# sync_binlog=0 (default), the MySQL does not refresh the binlog_cache to disk after the transaction is committed, but let the Filesystem decide, or synchronize only when the cache is full.

# sync_binlog=n, after every n transaction commits, MySQL forcibly writes the data in binlog_cache to disk.

Binlog_rows_query_log_events = ON

# record sql statements in row mode to binlog log. Default is 0 (off)

# Relay Log # #

Relay_log = / data/mysql/my3306/relaylog

# Relay log storage path

Relay_log_index = / data/mysql/my3306/relaylog.index

# same as relay_log, define the location and name of relay_log

# binlog_checksum = CRC32

# Session-Thread generates checksum when writing Event to Binlog. The default is (NONE), which is compatible with older versions of mysql.

Master_verify_checksum = ON

# Dump-Thread validates checksum when reading Event in Binlog

Slave_sql_verify_checksum = ON

# when writing Event to Relaylog from the I/O-Thread of the library, generate checksum; from the SQL-Thread of the library and verify the checksum when reading Event from Relaylog

Master_info_repository = TABLE

Relay_log_info_repository = TABLE

# keep master.info and relay.info in the table. The default is Myisam engine. It is officially recommended to change it to Innodb engine to prevent table damage and repair it by yourself.

Relay_log_purge = ON

Relay_log_recovery = ON

# enable the automatic repair feature of relaylog to avoid the master-slave stop due to the damage of the log caused by external factors such as network.

Skip_slave_start = OFF

# restart the database. The replication process is not started by default

Slave_net_timeout = 5

# when the network between master and slave is interrupted, the I/O-Thread of slave waits for 5 seconds to reconnect master

Sync_master_info = 10000

# interval between slave updating mysql.slave_master_info table

Sync_relay_log = 10000

Sync_relay_log_info = 10000

# interval between slave updating mysql.slave_relay_log_info table

Gtid_mode = ON

Enforce_gtid_consistency = ON

# GTID is the global transaction ID (global transaction identifier), and GTID is composed of UUID+TID.

# UUID is the unique identity of an MySQL instance, and TID represents the number of transactions that have been committed on that instance and increases monotonously as the transaction commits.

# GTID guarantees the execution of each MySQL instance transaction (does not repeat the same transaction and completes transactions that are not executed). Here is a concrete form of GTID:

# 4e659069-3cd8-11e5-9a49-001c4270714e:1-77

Auto_increment_offset = 1

# in dual-master replication, the initial values of the self-growth fields of the two servers are configured as 1 and 2, respectively, and the range of values is 1. 65535

Auto_increment_increment = 2

# in dual-master replication, each increment of the self-growth field of two servers is configured as 2, the default value is 1, and the value range is 1. 65535

# #

# #

# InnoDB #

# #

# #

Innodb_data_home_dir = / data/mysql/my3306

# data file directory of innodb table

Innodb_file_per_table = ON

# manage using independent tablespaces

Innodb_data_file_path = ibdata1:1G:autoextend

# InnoDB shares tablespace disk files, stores data dictionaries and online redo logs

Innodb_log_group_home_dir = / data/mysql/my3306

Before the transaction is committed and written to the tablespace disk file, the transaction data is stored in the redo log file of InnoDB. These logs are located in the directory defined by the innodb_log_group_home_dir variable

Innodb_buffer_pool_size = 2G

# InnoDB is used to cache data, indexes, locks, insert buffers, and data dictionary buffers. The higher the value, the higher the cache hit rate, but too much leads to page swapping.

Innodb_buffer_pool_instances = 8

# Open 8 memory buffer pools and hash the data that need to be buffered to different buffer pools, so that you can read and write memory in parallel and reduce internal cache access conflicts caused by concurrency.

# InnoDB cache system will divide the parameter innodb_buffer_pool_size of specified size into innodb_buffer_pool_instances buffer_pool

# innodb_additional_mem_pool_size = 16m

# InnoDB stores data dictionary and buffer pool size of internal data structures, similar to Oracle's library cache

Innodb_log_file_size = 256m

# InnoDB redo log size, corresponding to the ib_logfile0 file.

# ib_logfile* is a guarantee of Innodb multi-version buffering. This log records redo and undo information, that is, the data before commit, and is used for rollback operations.

# the recommended setting for official documents is innodb_log_file_size = innodb_buffer_pool_size/innodb_log_files_in_group

Innodb_log_buffer_size = 64m

# memory buffer size used by redo logs

Innodb_log_files_in_group = 4

# the number of redo log files. The default is 2. Logs are written sequentially.

Innodb_max_dirty_pages_pct = 90

# the maximum proportion of dirty pages in the cache pool. The default value is 75%. If the number of dirty pages reaches or exceeds this value, the background thread of InnoDB will start cache refresh.

# "cache refresh" means that when InnoDB cannot find a clean available cache page or the checkpoint is triggered, the background thread of InnoDB begins to write back the "dirty cache page" to the disk file.

Innodb_flush_log_at_trx_commit = 1

# set to 0, write cache & flush disk per second

# set to 1, write cache & flush disk every time commit

# set to 2, write cache every commit, and then flush disk according to the innodb_flush_log_at_timeout (default is 1s) time

Innodb_lock_wait_timeout = 10

# InnoDB has a built-in deadlock detection mechanism that can cause outstanding transactions to be rolled back. However, if you use MyISAM's lock tables statement or a third-party transaction engine with InnoDB, InnoDB does not recognize deadlocks.

# to eliminate this possibility, you can set innodb_lock_wait_timeout to an integer value that tells MySQL how long to wait (in seconds) before allowing other transactions to modify data that will eventually be rolled back by the transaction.

Innodb_sync_spin_loops = 40

# the number of revolutions of the spin lock can be viewed through show engine innodb status.

# if you see a lot of spin waiting and spin rotation, it wastes a lot of cpu resources. This value can be used to strike a balance between wasted cpu time and unnecessary context switching.

Innodb_support_xa = ON

# first, support for multi-instance distributed transactions (external xa transactions), which is commonly used in distributed database environments.

# second, support internal xa transactions, that is, data consistency between binlog and innodb redo log.

# innodb_file_format = barracuda

# InnoDB file format, Antelope is the file format of innodb-base, Barracude is the file format introduced after innodb-plugin, and Barracude also supports Antelope file format.

Innodb_flush_method = O_DIRECT

# set the open and write mode of innodb data file and redo log, fdatasync (default), Olympus DSYNCGrad object direct

# default is fdatasync. Call fsync () to scan the data file and buffer of redo log

# when set to O_DSYNC, innodb opens and writes redo log in O_SYNC mode, and writes data files using fsync ()

# when set to O_DIRECT, innodb uses O_DIRECT to open the data file, and uses fsync () to write the data file and redo log

Innodb_strict_mode = ON

# enable InnoDB strict check mode. In some cases, return errors instead of warnings. The default value is OFF.

Innodb_checksum_algorithm = strict_crc32

The algorithm of # checksum function. Default is crc32. The following values can be set: innodb, crc32, none, strict_innodb, strict_crc32, strict_none

Innodb_status_file = 1

# enable status file of InnoDB for easy viewing and monitoring by administrators

Innodb_open_files = 3000

# limit the data of tables that can be opened by Innodb. The default is 300. if there are a lot of tables in the database, you can increase it to 1000.

Innodb_thread_concurrency = 8

# number of threads processed in the Innodb kernel at the same time. The number of CPU on the server is set to several, and the default value is recommended.

Innodb_thread_sleep_delay = 500,

# innodb_file_io_threads = 16

# File reads and writes the number of Ibind O, this parameter only works on Windows. On LINUX, it will only be equal to 4, which is fine by default.

Innodb_read_io_threads = 16

# set read thread (number of reading threads. Default is 4)

Innodb_write_io_threads = 16

# set write thread (number of write threads. Default is 4)

Innodb_io_capacity = 2000

# Throughput of disk io. Default is 200. The number of pages refreshed to the disk is controlled as a percentage of inodb_io_capacity.

Log_bin_trust_function_creators = 1

# after enabling log-bin, you can create a function at will, which may cause potential data security problems.

Innodb_purge_threads = 1

# use separate threads for purge operations.

# Undo pages are generated for each DML operation, and these undo pages need to be cleaned regularly by the system, which is called purge operation.

Innodb_purge_batch_size = 32

# in full purge, the number of recycled Undo pages is 20 by default, which can be increased appropriately.

Innodb_old_blocks_pct = 75

# LRU algorithm, the default value is 37, which is inserted into 37% of the end of the LRU list, which is about 3% of the position of 8.

# innodb calls the list after midpoint the old list, and the previous list as the new list, which can be understood to mean that the pages in the new list are the most active hot data.

Innodb_change_buffering = all

# options for turning on various Buffer. The optional values for this parameter are: inserts, deletes, purges, changes, all, none.

# changes: enable inserts and deletes,all: enable all, and none: none. The default value of this parameter is all.

[mysqldump]

Max_allowed_packet = 256m

Quick

# mysqldump is useful when exporting large tables, forcing records to be output directly from server queries, rather than caching all records to memory.

[mysqlhotcopy]

Interactive-timeout

[mysqld_safe]

# ledir = / app/mysql/bin

# contains the software installation path for the mysqld program, using this option to explicitly indicate the server location.

Thank you for reading! This is the end of this article on "how to view the directory where mysql reads my.cnf by default". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

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.