We know thatMySQL databaseAfter the installation is completeMy. cnf configuration fileMake proper modifications to make full use of the functions of the MySQL database. However, it seems difficult for beginners to modify the my. cnf configuration file. To solve this problem, we have explained the configuration file for your reference and hope to help you.
The following describes the parameters in the my. cnf configuration file:
- [Client]
- Port = 3309
- 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 = 3309
- 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 # Allow interactive_timeout to replace wait_timeout before closing the connection. 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
The explanation of the MySQL database my. cnf configuration file is introduced here. I hope this introduction will be helpful to you!