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 inspect
的Binds
这块找到
"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环境,预知后事如何,请看下集 [填别人留下的坑,真的难顶]