Ubuntu中MySQL的参数文件my.cnf有什么用

2025-03-27 技术教程

这篇文章主要介绍Ubuntu中MySQL的参数文件my.cnf有什么用，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！

前言

对于MySQL的理解，我认为很多性能优化工作、主从主主复制都是在调整参数，来适应不同时期不同数量级的数据。

故，理解透彻my.cnf里的参数是永恒的话题；只有理解透彻了参数设置，才能在某些方面对数据库进行调优。

前几天刚接手一个MySQL数据，操作系统为Ubuntu 16.04.5 LTS，数据库版本为5.7.23-0ubuntu0.16.04.1（APT方式安装的MySQL）。这个操作系统下的MySQL的配置文件my.cnf很多地方都让人有点不适应（跟之前的MySQL环境有些出入，之前都是维护RHEL、CentOS等操作系统环境下的MySQL）。

遂研究总结了一下。具体如下所示：

root@mylnx12:~#find/-name"my.cnf"/etc/alternatives/my.cnf/etc/mysql/my.cnf/var/lib/dpkg/alternatives/my.cnfroot@mylnx12:~#locatemy.cnf/etc/alternatives/my.cnf/etc/mysql/my.cnf/etc/mysql/my.cnf.fallback/var/lib/dpkg/alternatives/my.cnfroot@mylnx12:~#mysql--help|grepmy.cnforderofpreference,my.cnf,$MYSQL_TCP_PORT,/etc/my.cnf/etc/mysql/my.cnf~/.my.cnfroot@mylnx12:~#mysqld--verbose--help|grep-A1'Defaultoptions'Defaultoptionsarereadfromthefollowingfilesinthegivenorder:/etc/my.cnf/etc/mysql/my.cnf~/.my.cnf

从上面这些信息判断，MySQL的参数文件为/etc/mysql/my.cnf，但是其他几个my.cnf又是什么情况呢？

root@mylnx12:~#ls-lrt/etc/alternatives/my.cnflrwxrwxrwx1rootroot20Sep2816:28/etc/alternatives/my.cnf->/etc/mysql/mysql.cnf

从上面信息可以看出，/etc/alternatives/my.cnf 其实是一个软连接，指向参数文件/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

光从上面这些信息，我们还看不出/var/lib/dpkg/alternatives/my.cnf与其它配置文件my.cnf是什么关系。那么我们先来看看参数文件/etc/mysql/my.cnf,从下面信息，可以看出“/etc/mysql/my.cnf”是全局配置，“~/.my.cnf”隐藏文件是个人用户设置.

root@mylnx12:~#cat/etc/mysql/my.cnf##TheMySQLdatabaseserverconfigurationfile.##Youcancopythistooneof:#-"/etc/mysql/my.cnf"tosetglobaloptions,#-"~/.my.cnf"tosetuser-specificoptions.##Onecanusealllongoptionsthattheprogramsupports.#Runprogramwith--helptogetalistofavailableoptionsandwith#--print-defaultstoseewhichitwouldactuallyunderstandanduse.##Forexplanationssee#http://dev.mysql.com/doc/mysql/en/server-system-variables.html##*IMPORTANT:Additionalsettingsthatcanoverridethosefromthisfile!#Thefilesmustendwith'.cnf',otherwisethey'llbeignored.#!includedir/etc/mysql/conf.d/!includedir/etc/mysql/mysql.conf.d/

但是/etc/mysql/my.cnf参数文件下面没有任何参数设置，只看到下面两行设置，表示导入这两个目录里面的配置文件。

!includedir /etc/mysql/conf.d/

#表示包含/etc/mysql/conf.d/这个路径下面的配置文件，前提是必须以为.cnf为后缀

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

#表示包含/etc/mysql/mysql.conf.d/这个路径下面的配置文件，前提是必须以为.cnf为后缀

其实MySQL的相关配置都位于mysqld.cnf（/etc/mysql/mysql.conf.d/mysqld.cnf）下面。使用相关参数测试了一下，确实都能生效。这种设置确实有点让刚接触的人有点不适应。暂时先总结到此！

root@mylnx12:~#cd/etc/mysql/mysql.conf.d/root@mylnx12:/etc/mysql/mysql.conf.d#ls-lrttotal8-rw-r--r--1rootroot21Feb42017mysqld_safe_syslog.cnf-rw-r--r--1rootroot3148Oct623:34mysqld.cnfroot@mylnx12:/etc/mysql/mysql.conf.d#catmysqld.cnf##TheMySQLdatabaseserverconfigurationfile.##Youcancopythistooneof:#-"/etc/mysql/my.cnf"tosetglobaloptions,#-"~/.my.cnf"tosetuser-specificoptions.##Onecanusealllongoptionsthattheprogramsupports.#Runprogramwith--helptogetalistofavailableoptionsandwith#--print-defaultstoseewhichitwouldactuallyunderstandanduse.##Forexplanationssee#http://dev.mysql.com/doc/mysql/en/server-system-variables.html#Thiswillbepassedtoallmysqlclients#Ithasbeenreportedthatpasswordsshouldbeenclosedwithticks/quotes#escpeciallyiftheycontain"#"chars...#Remembertoedit/etc/mysql/debian.cnfwhenchangingthesocketlocation.#Hereisentriesforsomespecificprograms#Thefollowingvaluesassumeyouhaveatleast32Mram[mysqld_safe]socket=/var/run/mysqld/mysqld.socknice=0[mysqld]##*BasicSettings#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##Insteadofskip-networkingthedefaultisnowtolistenonlyon#localhostwhichismorecompatibleandisnotlesssecure.bind-address=10.21.6.7##*FineTuning#key_buffer_size=16Mmax_allowed_packet=100Mthread_stack=192Kthread_cache_size=8#ThisreplacesthestartupscriptandchecksMyISAMtablesifneeded#thefirsttimetheyaretouchedmyisam-recover-options=BACKUP#max_connections=100#table_cache=64#thread_concurrency=10##*QueryCacheConfiguration#query_cache_limit=1Mquery_cache_size=16M##*LoggingandReplication##Bothlocationgetsrotatedbythecronjob.#Beawarethatthislogtypeisaperformancekiller.#Asof5.1youcanenablethelogatruntime!#general_log_file=/var/log/mysql/mysql.log#general_log=1##Errorlog-shouldbeveryfewentries.#log_error=/var/log/mysql/error.log##Hereyoucanseequerieswithespeciallylongduration#log_slow_queries=/var/log/mysql/mysql-slow.log#long_query_time=2#log-queries-not-using-indexes##Thefollowingcanbeusedaseasytoreplaybackuplogsorforreplication.#note:ifyouaresettingupareplicationslave,seeREADME.Debianabout#othersettingsyoumayneedtochange.#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##InnoDBisenabledbydefaultwitha10MBdatafilein/var/lib/mysql/.#ReadthemanualformoreInnoDBrelatedoptions.Therearemany!##*SecurityFeatures##Readthemanual,too,ifyouwantchroot!#chroot=/var/lib/mysql/##ForgeneratingSSLcertificatesIrecommendtheOpenSSLGUI"tinyca".##ssl-ca=/etc/mysql/cacert.pem#ssl-cert=/etc/mysql/server-cert.pem#:ssl-key=/etc/mysql/server-key.pem

以上是“Ubuntu中MySQL的参数文件my.cnf有什么用”这篇文章的所有内容，感谢各位的阅读！希望分享的内容对大家有帮助，更多相关知识，欢迎关注亿速云行业资讯频道！