Detailed description of Log Type and file configuration for my. cnf configuration in mysql

Next, let's take a look at the Log Type and file configuration details of my. cnf configuration in mysql. I hope the article will help you.

Mysql has the following types of logs:
Error log: log-err
Query log: log
Slow query log: log-slow-queries
Update log: log-update
Binary log: log-bin

 

 

[Client]
Port = 3306
Socket =/home/mysql/tmp/mysql. sock
[Mysqld]
! Include/home/mysql/etc/mysqld. cnf # contains the configuration file and stores the user name and password file separately.
Port = 3306
Socket =/home/mysql/tmp/mysql. sock
Pid-file =/longxibendi/mysql/var/mysql. pid
Basedir =/home/mysql/
Datadir =/longxibendi/mysql/var/
# Tmp dir settings
Tmpdir =/home/mysql/tmp/
Slave-load-tmpdir =/home/mysql/tmp/
# Used when slave executes load data infile
# Language =/home/mysql/share/mysql/english/
Character-sets-dir =/home/mysql/share/mysql/charsets/
# Skip options
When using skip-name-resolve # grant, you must use an ip address instead of a host name.
Skip-symbolic-links # connection files cannot be used
Skip-external-locking # Do not use system lock. To use myisamchk, you must disable the server.
Skip-slave-start # start mysql, do not start Replication
# Sysdate-is-now
# Res settings
Back_log = 50 # accept the queue. If no tcp connection is established, the request queue is placed in the cache. The queue size is back_log, which is restricted by operating system parameters.
Max_connections = 1000 # maximum number of concurrent connections. To increase this value, you must increase the number of file descriptors that can be opened.
Max_connect_errors = 10000 # If a user initiates a connection error that exceeds this value, the next connection of the user will be blocked until the Administrator executes the flush hosts command to prevent hackers
# Open_files_limit = 10240
Connect-timeout = 10 # maximum number of seconds before connection timeout. on Linux, this timeout is also used to wait for the first response from the server.
Wait-timeout = 28800 # wait for the connection to be closed
Interactive-timeout = 28800 # Before closing the connection, interactive_timeout (replacing wait_timeout) seconds are allowed for inactivity. The client's session wait_timeout variable is set to the value of the session interactive_timeout variable.
Slave-net-timeout = 600 # The slave server can also handle network connection interruptions. However, network interruption is notified only when the slave server does not receive data from the master server within seconds after slave_net_timeout.
Net_read_timeout = 30 # timeout for reading information from the server
Net_write_timeout = 60 # timeout for writing information from the server
Net_retry_count = 10 # If the read operation on a communication port is interrupted, retry multiple times before giving up.
Net_buffer_length = 16384 # The packet message buffer is initialized to net_buffer_length bytes, but can be increased to max_allowed_packet bytes as needed
Max_allowed_packet = 64 M #
# Table_cache = 512 # number of tables opened by all threads. Increasing this value can increase the number of file descriptors required by mysqld.
Thread_stack = 192 K # the size of each thread's stack
Thread_cache_size = 20 # thread Cache
Thread_concurrency = 8 # the data of the Concurrently Running thread should be twice the number of CPUs. Number of CPUs configured on the local machine
# Qcache settings
Query_cache_size = 256 M # query cache size
Query_cache_limit = 2 M # Do not cache query results greater than this value
Query_cache_min_res_unit = 2 K # query the minimum block size allocated by the cache
# Default settings
# Time zone
Default-time-zone = system # server time zone
Character-set-server = utf8 # server-level character set
Default-storage-engine = InnoDB # default storage
# Tmp & heap
Tmp_table_size = 512 M # size of the temporary table. If this value is exceeded, the result is stored in the disk.
Max_heap_table_size = 512 M # This variable sets the maximum size of the MEMORY (HEAP) Table space that can be increased
Log-bin = mysql-bin # these paths are relative to datadir
Log-bin-index = mysql-bin.index
Relayrelay-log = relay-log
Relayrelay_log_index = relay-log.index
# Warning & error log
Log-warnings = 1
Log-error =/home/mysql/log/mysql. err
Log_output = FILE # The log_output parameter specifies the slow query output format. The default format is FILE. You can set it to TABLE and then query the slow_log TABLE in mysql architecture.
# Slow query log
Slow_query_log = 1
Long-query-time = 1 # Slow query if the query time exceeds 1 second
Slow_query_log_file =/home/mysql/log/slow. log
# Log-queries-not-using-indexes
# Log-slow-slave-statements
General_log = 1
General_log_file =/home/mysql/log/mysql. log
Max_binlog_size = 1G
Max_relay_log_size = 1G
# If use auto-ex, set to 0
Relay-log-purge = 1 # delete logs without relay. This operation is completed by an SQL thread.
# Max binlog keeps days
Expire_logs_days = 30 # delete a binlog that exceeds 30 days
Binlog_cache_size = 1 M # session level
# Replication
Replicate-wild-ignore-table = mysql. % # ignore databases and tables during replication
Replicate-wild-ignore-table = test. % # ignore databases and tables during replication
# Slave_skip_errors = all
Key_buffer_size = 256 M # myisam index buffer, only the key does not have data
Sort_buffer_size = 2 M # Sort buffer size; Thread level
Read_buffer_size = 2 M # buffer size of data scanned in full table Scan (Sequential Scan) mode; Thread level
Join_buffer_size = 8 M # join buffer size; Thread level
Read_rnd_buffer_size = 8 M # MyISAM uses the index Scan (Random Scan) method to Scan the buffer size of data; Thread level
Bulk_insert_buffer_size = 64 M # size of the tree buffer used by MyISAM in block insertion optimization. Note: This is a per thread restriction.
Myisam_sort_buffer_size = 64 M # MyISAM sets the buffer size used to restore the TABLE. The buffer allocated by sorting the MyISAM INDEX during the repair table or create index or alter table Process
Myisam_max_sort_file_size = 10G # If the temporary file of MyISAM exceeds the index, do not use the Quick Sort index method to create an index. Note: This parameter provides the maximum size of temporary files used by MySQL when re-indexing MyISAM indexes (during repair table, alter table, or load data infile) in bytes. If the file size exceeds this value, it is much slower to use the key value cache to create an index. The unit of the value is byte.
Myisam_repair_threads = 1 # If the value is greater than 1, create the MyISAM Table index in parallel during the Repair by sorting process (each index is in its own thread)
Myisam_recover = 64 K # Maximum length of the result of the allowed GROUP_CONCAT () function
Transaction_isolation = REPEATABLE-READ
Innodb_file_per_table
# Innodb_status_file = 1
# Innodb_open_files = 2048
Innodb_additional_mem_pool_size = 100 M # The control object of the frame cache must apply for cache from this place. Therefore, this value corresponds to innodb_buffer_pool.
Innodb_buffer_pool_size = 2G # data page, index page, insert cache, lock information, adaptive hash, data dictionary Information
Innodb_data_home_dir =/longxibendi/mysql/var/
# Innodb_data_file_path = ibdata1: 1G: autoextend
Innodb_data_file_path = ibdata1: 500 M; ibdata2: 2210 M: autoextend # tablespace
Innodb_file_io_threads = 4 # Number of I/O threads
Innodb_thread_concurrency = 16 # InnoDB tries to keep the number of operating system threads in InnoDB less than or equal to the limit given by this parameter
Innodb_flush_log_at_trx_commit = 1 # data in each commit log cache is flushed to the disk.
Innodb_log_buffer_size = 8 M # transaction log Cache
Innodb_log_file_size = 500 M # transaction log size
# Innodb_log_file_size = 100 M
Innodb_log_files_in_group = 2 # two groups of transaction logs
Innodb_log_group_home_dir =/longxibendi/mysql/var/# log Group
Innodb_max_dirty_pages_pct = 90 # the innodb main thread refreshes the data in the cache pool so that the proportion of dirty data is less than 90%.
Innodb_lock_wait_timeout = 50 # InnoDB transactions can wait for a lock timeout period before being rolled back. InnoDB automatically detects the transaction deadlock and rolls back the transaction in its own locked table. InnoDB uses the lock tables statement to notice the LOCK settings. The default value is 50 seconds.
# Innodb_flush_method = O_DSYNC
[Mysqldump]
Quick
Max_allowed_packet = 64 M
[Mysql]
Disable-auto-rehash # allow prompt by the TAB key
Default-character-set = utf8
Connect-timeout = 3