mysql悬案 之 为什么用docker启动的mysql配置文件不生效

 

 

故事前景

接了个私活,需要安装canal,canal需要mysql开启binlog功能,查看了mysql的配置文件,看到已经写了log_bin参数,此时进入mysql,执行sql语句确认binlog功能是否为ON [sql语句:show variables like 'log_bin';],结果显示为OFF,于是开启了排查之路

查看docker启动时挂载了哪些目录

docker inspect 9e33b294e948 | grep Binds -A 4

预期出现类似如下的输出,以本地实际环境为准

docker run启动的时候,-v参数所挂载的目录,会在docker inspectBinds这块找到

"Binds": [
    "/etc/localtime:/etc/localtime:ro",
    "/data/mysql-test/conf:/etc/mysql",
    "/data/mysql-test/data:/var/lib/mysql"
],

这时,查看一下本地持久化配置文件的目录,发现,只有一个my.cnf文件

问题就出现在这一块:本地直接使用yum安装的mysql默认的配置文件存储路径是/etc/mysql/my.cnf

但是docker容器其实并非如此

# tree /data/mysql-test/conf
/data/mysql-test/conf
└── my.cnf

使用相同镜像启动一个mysql

因为只是查看一下mysql的配置文件情况,就简单的启动mysql即可

如果不给-e MYSQL_ROOT_PASSWORD=root参数,容器无法在后台运行,就无法把配置文件获取到宿主机

docker run -d -e MYSQL_ROOT_PASSWORD=root  mysql:5.7

新建一个目录用来存放容器内的mysql配置文件

mkdir -p /data/mysql-new/conf

复制容器内的mysql配置文件到本地

docker cp <容器ID>:/etc/mysql/ /data/mysql-new/conf/

查看mysql配置文件目录结构

为什么要拿到本地?

反正也要拿到本地重新挂载,早晚都要拿,总不能手撸配置文件吧

# tree /data/mysql-new/conf/
/data/mysql-new/conf/
├── conf.d
│   ├── docker.cnf
│   ├── mysql.cnf
│   └── mysqldump.cnf
├── my.cnf -> /etc/alternatives/my.cnf
├── my.cnf.fallback
├── mysql.cnf
└── mysql.conf.d
    └── mysqld.cnf

那么问题来了,这么多文件,到底哪个才是默认的配置文件呢,那就一个个看吧

conf/conf.d/docker.cnf

[mysqld]
skip-host-cache
skip-name-resolve

conf/conf.d/mysql.cnf

[mysql]

conf/conf.d/mysqldump.cnf

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

conf/my.cnf

这个文件在本地看不了,因为他是一个软连接文件,文件链接的路径是/etc/alternatives/my.cnf

/etc/alternatives/my.cnf这个文件也是一个软连接文件,文件的连接路径是/etc/mysql/mysql.cnf

咱也不知道官方为啥要这样套娃,咱也不敢问

conf/my.cnf.fallback

#
# 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

!includedir /etc/mysql/conf.d/

conf/mysql.cnf

# Copyright (c) 2016, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License, version 2.0,
# as published by the Free Software Foundation.
#
# This program is also distributed with certain software (including
# but not limited to OpenSSL) that is licensed under separate terms,
# as designated in a particular file or component or in included license
# documentation.  The authors of MySQL hereby grant you an additional
# permission to link the program and your derivative works with the
# separately licensed software that they have included with MySQL.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License, version 2.0, for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

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

mysql.conf.d/mysqld.cnf

# Copyright (c) 2014, 2016, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License, version 2.0,
# as published by the Free Software Foundation.
#
# This program is also distributed with certain software (including
# but not limited to OpenSSL) that is licensed under separate terms,
# as designated in a particular file or component or in included license
# documentation.  The authors of MySQL hereby grant you an additional
# permission to link the program and your derivative works with the
# separately licensed software that they have included with MySQL.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License, version 2.0, for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

#
# The MySQL  Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
#log-error      = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address   = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

真假配置文件已经显而易见了

docker容器启动的mysql默认的配置文件其实是/etc/mysql/mysql.conf.d/mysqld.conf

因此,如果需要将本地配置文件挂载到容器里面,只需要挂载这一个文件即可,此时我们修改本地的mysql.conf.d/mysqld.conf文件,开启binlog,并验证是否修改成功

启动mysql容器

精简一下本地mysql配置文件目录,就保留一个mysqld.cnf文件即可

# tree /data/mysql-new/conf/
/data/mysql-new/conf/
└── mysqld.cnf

mysqld.cnf文件最后加上这两行,用来开启binlog日志

log_bin=mysql-bin
server_id=33091

启动mysql容器

docker run -d \
-e MYSQL_ROOT_PASSWORD=root \
-v /etc/localtime:/etc/localtime \
-v /data/mysql-new/conf/mysqld.cnf:/etc/mysql/mysql.conf.d/mysqld.cnf \
-v /data/mysql-new/data:/var/lib/mysql \
-p 3309:3306 \
--name mysql-new \
mysql:5.7

数据库就不进去了,直接使用-e参数将结果返回到终端页面

# mysql -uroot -p -P3309 -h192.168.100.200 -e "show variables like 'log_bin';"
Enter password:
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
  • 此时,找到了为何已经启动的mysql容器加载不到配置文件的原因了

  • 同时,也学到了一个新的经验,当容器需要持久化的时候,最好是简单启动一下这个容器,查看一下持久化目录的结构以及是否存在依赖的情况,根据实际来选择到底是目录挂载,还是单配置文件挂载,避免本地错误目录结构覆盖了容器内的目录结构,当一些配置没有更新的时候,排查真的很头疼

  • 后续将会在头脑清醒的时候去修复已经启动的mysql环境,预知后事如何,请看下集 [填别人留下的坑,真的难顶]

posted @ 2022-02-19 23:33  月巴左耳东  阅读(2591)  评论(0编辑  收藏  举报