Shulou(Shulou.com)06/01 Report--

The following content mainly brings you the details of the MySQL my.cnf configuration file. The knowledge mentioned here, which is slightly different from books, is summed up by professional and technical personnel in the process of contact with users, and has a certain value of experience sharing. I hope to bring help to the majority of readers.

The official website says that my-default.cnf files will not be available in binary packages since 5.7.18. Reference: https://dev.mysql.com/doc/refman/5.7/en/binary-installation.html has been tested, in version 5.7.18, the use of tar.gz installation, that is, unzipped to install this, no longer need my.cnf files to run normally. The my.cnf file is to configure the following parameters when starting MySQL on the command line with the cnf file, so that you no longer need to add parameters to the command when the download starts. The my.cnf file can be a custom location, or you can use the following default location, as long as it is placed in the default location, MySQL automatically recognizes (installed through the deb or APT source Initial location is listed below): read options on Unix and Unix-like systems filename destination / etc/my.cnf global options / etc/mysql/my.cnf global options SYSCONFDIR/my.cnf global options $MYSQL_HOME/my.cnf CVM specific options (server only) defaults -files specified by extra-file-- defaults-extra-file (if any) ~ / .my.cnf user-specific options ~ / .mylogin.cnf user-specific login path options (client only)

In the above table, ~ represents the home directory of the current user (the value of $HOME). First, it will look for the file / etc/my.cnf, and if the file does not exist, then it will look for the file / etc/mysql/my.cnf. And so on, for more details, please refer to the official explanation: https://dev.mysql.com/doc/refman/5.7/en/option-files.htmlhttps://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html?spm=5176.7920929.0.0.42e941d6WvwfAQ#sysvar_block_encryption_modehttps://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#ngram_token_ Size https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#innodb_monitor_enablehttps://github.com/xpchild/SQL/wiki/Changes-in-AliSQL-5.6.32-(2016-09-15)#10-sql-filter, anyway, Whether it is installed using the APT source or the deb package, or the binary (compressed package tar.gz) package, the startup configuration and tuning of MySQL can be achieved through the my.cnf file. Since the sample file my-default.cnf is no longer included in the binary package since 5.7.18, I extracted the sample from version 5.7.17, but found that there are not many configurations in it. The my-default.cnf content is as follows: # For advice on how to change settings please see# # * * DO NOT EDIT THIS FILE. It's a template which will be copied to the# * * default location during install, and will be replaced if you# * * upgrade to a newer version of MySQL. [mysqld] # Remove leading # and set to the amount of RAM for the most important data# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M# Remove leading # to turn on a very important data integrity option: logging# changes to the binary log between backups.# log_bin# These are commonly set Remove the # and set as required.# basedir =. # datadir =. # port =. # server_id =. # socket =. # Remove leading # to set options mainly useful for reporting servers.# The server defaults are faster for transactions and fast SELECTs.# Adjust sizes as needed, experiment to find the optimal values.# join_buffer_size = 128M# sort_buffer_size = 2M# read_rnd_buffer_size = 2m sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES actually These items are command line parameters. On the official website, Mysql parameter optimization from this page is difficult for beginners to understand. In fact, this parameter optimization is a very complex thing. It is related to different websites, their online traffic, the number of visits, the number of posts, network conditions, and machine hardware configuration. Optimization can not be completed at one time, requiring constant observation and debugging. To get the best results. The following is an example of my.cnf: * * [client] default-character-set = utf8mb4 [mysql] # enable tab completion # auto-rehashdefault-character-set = utf8mb4 [mysqld] port=3306basedir=/data/server/mysql57/datadir=/data/server/mysql57/data/socket=/data/ Server/mysql57/data/mysql.socksymbolic-links=0log-error=/data/logs/mysql57/mysqld.logpid-file=/data/server/mysql57/data/mysqld57.pid# disables hostname resolution skip-name-resolve# default database engine default-storage-engine = InnoDBinnodb-file-per-table=1innodb_force_recovery = some pits group_concat_max_len = 10240sql_mode=expire_logs_days = 7memlock### character set configuration character-set-client-handshake = FALSEcharacter- Set-server = utf8mb4collation-server = utf8mb4_unicode_ciinit_connect='SET NAMES utf8mb4'### GTIDserver_id = 33075 to ensure the stability of GTID replication Row-level log binlog _ format = row# enable gtid function gtid_mode = on# guarantee GTID transaction security # when enforce_gtid_consistency function is enabled, # MySQL only allows transaction security, and SQL statements that can be logged can be executed, # like create table. Select and create temporarytable statements, # and SQL statements or transactions that update both transactional and non-transactional tables are not allowed to perform the following two configuration enforce-gtid-consistency = true# master-slave switches Database highly available must be configured # enable binlog log function log_bin = mysql57-bin # enable update binlog log from library log-slave-updates = on#slave replication process does not start skip_slave_start=1### slow log with mysql startup # turn on slow log function slow_query_log = "query for more than 2 seconds is recorded long_query_time =" record does not make Automatic repair with index query log_queries_not_using_indexes = 0slow_query_log_file = / data/logs/mysql57/slow.log#log=/data/logs/mysql57/all.log### # record relay.info to data table relay_log_info_repository = TABLE# record master.info to data table master_info_repository = TABLE# enable automatic repair function of relaylog relay_log_recovery = on# in SQL thread execution Automatically delete relay_log_purge = 1 security # data security configuration # wei turn off the function of master to create function log_bin_trust_function_creators = on# force writing to disk sync_binlog = "timestamp" column if it is not explicitly defined as not null Then the null attribute # sets the column value of timestamp to null and will not be set to current timestampexplicit_defaults_for_timestamp=true### optimized configuration # optimized Chinese full-text fuzzy index ft_min_word_len = default library name table name is saved in lowercase Case-insensitive lower_case_table_names = "maximum size limit for single record writes # too small may cause write (import) data to fail max_allowed_packet = 256M# semi-synchronous replication on # rpl_semi_sync_master_enabled = 1#rpl_semi_sync_slave_enabled =" semi-synchronous replication timeout setting # rpl_semi_sync_master_timeout = 100" replication mode Type (keep the system default) # rpl_semi_sync_master_wait_point = AFTER_SYNC# backend as long as one receives the log and writes the relaylog even if it succeeds # rpl_semi_sync_master_wait_slave_count = multithreaded replication # parallel replication based on group submission slave_parallel_type = the number of SQL threads parallel to logical_clock# This parameter can only be set to 1

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.