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

This article mainly introduces what is the use of the parameter file my.cnf of MySQL in Ubuntu. It is very detailed and has certain reference value. Friends who are interested must finish it!

Preface

As for the understanding of MySQL, I think a lot of performance optimization work and master-slave master replication are adjusting parameters to adapt to different orders of magnitude of data in different periods.

Therefore, a thorough understanding of the parameters in my.cnf is an eternal topic; only by thoroughly understanding the parameter settings can we tune the database in some aspects.

Just took over a MySQL data a few days ago, the operating system is Ubuntu 16.04.5 LTS, and the database version is 5.7.23-0ubuntu0.16.04.1 (MySQL installed in APT). The configuration file my.cnf of MySQL under this operating system is a little uncomfortable in many places (different from the previous MySQL environment, which used to maintain MySQL in operating systems such as RHEL, CentOS, and so on).

So the study summed up. The details are as follows:

Root@mylnx12:~# find /-name "my.cnf" / etc/alternatives/my.cnf/etc/mysql/my.cnf/var/lib/dpkg/alternatives/my.cnfroot@mylnx12:~# locate my.cnf/etc/alternatives/my.cnf/etc/mysql/my.cnf/etc/mysql/my.cnf.fallback/var/lib/dpkg/alternatives/my.cnfroot@mylnx12:~# mysql-- help | grep my.cnf order of preference, my.cnf, $MYSQL_TCP_PORT / etc/my.cnf / etc/mysql/my.cnf ~ / .my.cnf root@mylnx12:~# mysqld-- verbose-- help | grep-A 1 'Default options'Default options are read from the following files in the given order:/etc/my.cnf / etc/mysql/my.cnf ~ / .my.cnf

Judging from the above information, the parameter file for MySQL is / etc/mysql/my.cnf, but what about the other my.cnf?

Root@mylnx12:~# ls-lrt / etc/alternatives/my.cnflrwxrwxrwx 1 root root 20 Sep 28 16:28 / etc/alternatives/my.cnf-> / etc/mysql/mysql.cnf

As you can see from the above information, / etc/alternatives/my.cnf is actually a soft connection that points to the parameter file / etc/mysql/mysql.cnf

Root@mylnx12:~# cat / var/lib/dpkg/alternatives/my.cnfauto/etc/mysql/my.cnf / etc/mysql/my.cnf.fallback100/etc/mysql/mysql.cnf200

From the above information, we can't see how / var/lib/dpkg/alternatives/my.cnf is related to other configuration files my.cnf. So let's first look at the parameter file / etc/mysql/my.cnf. From the following information, we can see that "/ etc/mysql/my.cnf" is a global configuration, and "~ / .my.cnf" hidden file is a personal user setting.

Root@mylnx12:~# cat / etc/mysql/my.cnf## The MySQL database server configuration file.## You can copy this to one of:#-"/ etc/mysql/my.cnf" to set global options #-"~ / .my.cnf" to set user-specific options.# # One can use all long options that the program supports.# Run program with-- help to get a list of available options and with#-- print-defaults to see which it would actually understand and use.## For explanations see# http://dev.mysql.com/doc/mysql/en/server-system-variables.html # # * IMPORTANT: Additional settings that can override those from this fileholders # The files must end with '.cnf' Otherwise they'll be ignored.#! includedir / etc/mysql/conf.d/!includedir / etc/mysql/mysql.conf.d/

However, there are no parameter settings under the / etc/mysql/my.cnf parameter file, only the following two lines of settings, indicating that the configuration files in these two directories are imported.

! includedir / etc/mysql/conf.d/

# indicates that the configuration file under the path / etc/mysql/conf.d/ is included, provided that .cnf is the suffix

! includedir / etc/mysql/mysql.conf.d/

# indicates that the configuration file under the path / etc/mysql/mysql.conf.d/ is included, provided that .cnf is the suffix

In fact, the relevant configurations of MySQL are located under mysqld.cnf (/ etc/mysql/mysql.conf.d/mysqld.cnf). It is tested with the relevant parameters, and it really works. This setting does make people who have just come into contact with it a little uncomfortable. That's all for the time being!

Root@mylnx12:~# cd / etc/mysql/mysql.conf.d/root@mylnx12:/etc/mysql/mysql.conf.d# ls-lrttotal 8 Mustang RWKI Rafael-1 root root 21 Feb 4 2017 mysqld_safe_syslog.cnf-rw-r--r-- 1 root root 3148 Oct 6 23:34 mysqld.cnfroot@mylnx12:/etc/mysql/mysql.conf.d# cat mysqld.cnf## The MySQL database server configuration file.## You can copy this to one of:# -"/ etc/mysql/my.cnf" to set global options #-"~ / .my.cnf" to set user-specific options.# # One can use all long options that the program supports.# Run program with-- help to get a list of available options and with#-- print-defaults to see which it would actually understand and use.## For explanations see# http://dev.mysql.com/doc/mysql/en/server-system-variables.html # This will be passed to all mysql clients# It has been reported that passwords should be enclosed with ticks/quotes# escpecially If they contain "#" chars...# Remember to edit / etc/mysql/debian.cnf when changing the socket location. # Here is entries for some specific programs# The following values assume you have at least 32m ram [mysqld_safe] socket = / var/run/mysqld/mysqld.socknice = 0 [mysqld] # # * Basic Settings#user = mysqlpid-file = / var/run/mysqld/mysqld.pidsocket = / var/run/mysqld/mysqld.sockport = 3306basedir = / usrdatadir = / var/lib/mysqltmpdir = / tmplc-messages-dir = / usr/share/mysqlskip-external -lockinglog_bin = mylnx12_binserver_id = 0character-set-server=utf8mb4collation-server=utf8mb4_general_ci # # Instead of skip-networking the default is now to listen only on# localhost which is more compatible and is not less secure.bind-address = 10.21.6.7 This replaces the startup script and checks MyISAM tables if needed# the first time they are touchedmyisam- # * Fine Tuning#key_buffer_size = 16Mmax_allowed_packet = 100Mthread_stack = 192Kthread_cache_size = "This replaces the startup script and checks MyISAM tables if needed# the first time they are touchedmyisam-" Recover-options = BACKUP#max_connections = 100#table_cache = 64#thread_concurrency = 10 years # * Query Cache Configuration#query_cache_limit = 1Mquery_cache_size = 16M## * Logging and Replication## Both location gets rotated by the cronjob.# Be aware that this log type is a performance killer.# As of 5.1 you can enable the log at runtimestones generalizable logbooks file = / var/log/mysql/mysql.log#general_log = 1 # # Error log- should be very few entries.#log_error = / var/log/mysql/error.log## Here you can see queries with especially long duration#log_slow_queries = / var/log/mysql/mysql-slow.log#long_query_time = 2#log-queries-not-using-indexes## The following can be used as easy to replay backup logs or for replication.# note: if you are setting up a replication slave See README.Debian about# other settings you may need to change.#server-id = 1#log_bin = / var/log/mysql/mysql-bin.logexpire_logs_days = 10max_binlog_size = 100M#binlog_do_db = include_database_name#binlog_ignore_db = include_database_name## * InnoDB## InnoDB is enabled by default with a 10MB datafile in / var/lib/mysql/.# Read the manual for more InnoDB related options. There are manybirds # * Security Features## Read the manual, too, if you want chrootones # chroot = / var/lib/mysql/## For generating SSL certificates I recommend the OpenSSL GUI "tinyca". # # ssl-ca=/etc/mysql/cacert.pem# ssl-cert=/etc/mysql/server-cert.pem#: ssl-key=/etc/mysql/server-key.pem is all the contents of this article entitled "what is the use of MySQL's parameter file my.cnf in Ubuntu". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.