设为首页 收藏本站
查看: 1197|回复: 0

[经验分享] Ubuntu下面MySQL的参数文件my.cnf浅析

[复制链接]

尚未签到

发表于 2019-2-18 11:33:41 | 显示全部楼层 |阅读模式
  前几天刚接手一个 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.cnf
root@mylnx12:~# locate my.cnf
/etc/alternatives/my.cnf
/etc/mysql/my.cnf
/etc/mysql/my.cnf.fallback
/var/lib/dpkg/alternatives/my.cnf
root@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
  从上面这些信息判断,MySQL 的参数文件为 /etc/mysql/my.cnf, 但是其他几个 my.cnf 又是什么情况呢?

root@mylnx12:~# ls -lrt /etc/alternatives/my.cnf
lrwxrwxrwx 1 root root 20 Sep 28 16: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.cnf
auto
/etc/mysql/my.cnf
/etc/mysql/my.cnf.fallback
100
/etc/mysql/mysql.cnf
200
  光从上面这些信息,我们还看不出 /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
#
# 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 file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!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 -lrt
total 8
-rw-r--r-- 1 root root   21 Feb  4  2017 mysqld_safe_syslog.cnf
-rw-r--r-- 1 root root 3148 Oct  6 23:34 mysqld.cnf
root@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.sock
nice            = 0
[mysqld]
#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
log_bin        =  mylnx12_bin
server_id      = 0
character-set-server=utf8mb4
collation-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
#
# * Fine Tuning
#
key_buffer_size         = 16M
max_allowed_packet      = 100M
thread_stack            = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit       = 1M
query_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 runtime!
#general_log_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.log
expire_logs_days        = 10
max_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 many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# 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



运维网声明 1、欢迎大家加入本站运维交流群:群②:261659950 群⑤:202807635 群⑦870801961 群⑧679858003
2、本站所有主题由该帖子作者发表,该帖子作者与运维网享有帖子相关版权
3、所有作品的著作权均归原作者享有,请您和我们一样尊重他人的著作权等合法权益。如果您对作品感到满意,请购买正版
4、禁止制作、复制、发布和传播具有反动、淫秽、色情、暴力、凶杀等内容的信息,一经发现立即删除。若您因此触犯法律,一切后果自负,我们对此不承担任何责任
5、所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其内容的准确性、可靠性、正当性、安全性、合法性等负责,亦不承担任何法律责任
6、所有作品仅供您个人学习、研究或欣赏,不得用于商业或者其他用途,否则,一切后果均由您自己承担,我们对此不承担任何法律责任
7、如涉及侵犯版权等问题,请您及时通知我们,我们将立即采取措施予以解决
8、联系人Email:admin@iyunv.com 网址:www.yunweiku.com

所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其承担任何法律责任,如涉及侵犯版权等问题,请您及时通知我们,我们将立即处理,联系人Email:kefu@iyunv.com,QQ:1061981298 本贴地址:https://www.yunweiku.com/thread-673935-1-1.html 上篇帖子: ubuntu命令 下篇帖子: Docker的Ubuntu16.04容器如何汉化
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

扫码加入运维网微信交流群X

扫码加入运维网微信交流群

扫描二维码加入运维网微信交流群,最新一手资源尽在官方微信交流群!快快加入我们吧...

扫描微信二维码查看详情

客服E-mail:kefu@iyunv.com 客服QQ:1061981298


QQ群⑦:运维网交流群⑦ QQ群⑧:运维网交流群⑧ k8s群:运维网kubernetes交流群


提醒:禁止发布任何违反国家法律、法规的言论与图片等内容;本站内容均来自个人观点与网络等信息,非本站认同之观点.


本站大部分资源是网友从网上搜集分享而来,其版权均归原作者及其网站所有,我们尊重他人的合法权益,如有内容侵犯您的合法权益,请及时与我们联系进行核实删除!



合作伙伴: 青云cloud

快速回复 返回顶部 返回列表