GVKun编程网logo

修改 docker 下 mysql 配置（docker修改mysql配置文件）

9

本文将分享修改docker下mysql配置的详细内容，并且还将对docker修改mysql配置文件进行详尽解释，此外，我们还将为大家带来关于dockermysql主从同步配置、dockermysql主

本文将分享修改 docker 下 mysql 配置的详细内容，并且还将对docker修改mysql配置文件进行详尽解释，此外，我们还将为大家带来关于docker mysql 主从同步配置、docker mysql 主从配置、docker MySQL 修改密码、Docker mysql主从配置的相关知识，希望对你有所帮助。

本文目录一览：

修改 docker 下 mysql 配置（docker修改mysql配置文件）

修改 docker 下 mysql 配置（docker修改mysql配置文件）

1. 在 /home/smile/docker/mysql/config/ 目录下增加一个文件 my.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 as published by
# the Free Software Foundation; version 2 of the License.
#
# 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 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/

[mysqld]
sql_mode=''ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION''
lower_case_table_names=0

2. 删除原有的容器，新建一个

docker stop mysql
docker rm mysql
cd /home/smile/docker/mysql
docker run -p 3306:3306 --name mysql -v $PWD/conf/my.cnf:/etc/mysql/my.cnf -v $PWD/logs:/logs -v $PWD/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7

 

docker mysql 主从同步配置

docker mysql 主从同步配置

在 centos 中创建两个 mysql 容器

docker run --name q_master -p 13306:3306  -e MYSQL_ROOT_PASSWORD=123456 -d mysql:latest --character-set-server=utf8 --collation-server=utf8_general_ci

docker run --name q_slave -p 13307:3306  -e MYSQL_ROOT_PASSWORD=123456 -d mysql:latest --character-set-server=utf8 --collation-server=utf8_general_ci

通过命令更改 master 和 slave 的配置文件

master:

 

 

slave 配置：

 

 

更改配置文件通过  vi my.cnf，更改，进入对应的容器，cd /etc/mysql 切换到 /etc/mysql 目录下，然后 vi my.cnf 对 my.cnf 进行编辑

如果出现  bash: vi: command not found，需要我们在 docker 容器内部自行安装 vim。使用 apt-get install vim 命令安装

配置编写完成后，保存退出

在 Master 进入 mysql（Navicat Premium - 工具 - 命令列界面），执行 show master status;

 

Slave 中进入 mysql，执行 change master to master_host=''172.17.0.2'', master_user=''slave'', master_password=''123456'', master_port=3306, master_log_file=''mysql-bin.000003'', master_log_pos= 155, master_connect_retry=30;

开启同步 

根据同步日志：

1.

Authentication plugin ''caching_sha2_password'' cannot be loaded: ....

最新版本密码规则变化导致

可对 slave 账号进行如下相同的处理：

 

 

 

2 

COULD NOT FIND FIRST LOG FILE NAME IN BINARY LOG INDEX FILE

则先停止从库，停止命令 stop salve 获取最新的同步文件后再次执行命令即可

docker mysql 主从配置

docker mysql 主从配置

1、首先创建两个文件my-m.cnf（主库配置） 、my-s.cnf（从库配置）
my-m.cnf 内容如下

# Copyright (c) 2014, 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 as published by
# the Free Software Foundation; version 2 of the License.
#
# 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 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 Community Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
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
explicit_defaults_for_timestamp

log-bin = mysql-bin 
server-id = 1 

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address = 127.0.0.1

#log-error = /var/log/mysql/error.log

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

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

主要是这两行，只需要在原来的配置里面加上就行

log-bin = mysql-bin 
server-id = 1

my-s.cnf 内容如下

# Copyright (c) 2014, 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 as published by
# the Free Software Foundation; version 2 of the License.
#
# 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 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 Community Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
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
explicit_defaults_for_timestamp

log-bin = mysql-bin 
server-id = 2

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address = 127.0.0.1

#log-error = /var/log/mysql/error.log

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

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

同样，主要的是这两行

log-bin = mysql-bin 
server-id = 2

2、OK，有了配置文件，就可以启动mysql了，先启动主库

$ docker run -d -e MYSQL_ROOT_PASSWORD=admin --name mysql-master -v /soft/my-m.cnf:/etc/mysql/my.cnf -p 3307:3306 mysql

3、启动从库

$ docker run -d -e MYSQL_ROOT_PASSWORD=admin --name mysql-slave -v /soft/my-s.cnf:/etc/mysql/my.cnf -p 3308:3306 mysql

4、连接主库，并运行以下命令，创建一个用户用来同步数据

$ GRANT REPLICATION SLAVE ON *.* to ''backup''@''%'' identified by ''123456'';

5、查看主库状态

$ show master status;

记住File、Position的值，如果没查到数据，请检查第一、第二步，配置问题。
我查出来的是mysql-bin.000004、312

6、连接到从库，运行以下命令，设置主库链接

$ change master to master_host=''121.32.32.54'',master_user=''backup'',master_password=''123456'',
master_log_file=''mysql-bin.000004'',master_log_pos=312,master_port=3307;

7、启动同步

$ start slave;

8、查看同步状态

$ show slave status

如果看到Waiting for master send event.. 什么的就成功了，你现在在主库上的修改，都会同步到从库上

docker MySQL 修改密码

docker MySQL 修改密码

1、 flush privileges;

2、 set password for root@localhost =password("hhwy@root");

 

3、设置远程主机登录，注意下面的your username 和 your password改成你需要设置的用户和密码

mysql>GRANT ALL PRIVILEGES ON *.* TO ''your username''@''%'' IDENTIFIED BY ''your password'' WITH
GRANT OPTION;

4、 flush privileges;

Docker mysql主从配置

Docker mysql主从配置

一：MysqL基于Docker的主从复制搭建

1：安装docker，安装步骤可见我之前的文章：Docker-常用基建的安装与部署

docker ps 命令查询当前的容器状态，这就是我们最后要达到的效果。

 

2：首先拉取MysqL官方镜像

bash> docker pull MysqL:5.7

演示环境是在同一台服务器上部署。因为docker创建容器时默认采用bridge网络，会自行分配ip，不允许指定，重启容器会导致ip变更。

所以我们需要创建自定义的bridge网络，这样创建容器的时候才能指定ip。

bash> docker network create --subnet=172.18.0.0/16 mynetwork

3：创建三个MysqL（一主两从）容器：

docker run -p 3306:3306 --name MysqL_master --net mynetwork --ip 172.18.0.36 -e TZ=Asia/Shanghai -v /home/data/docker/MysqL/MysqL1/logs:/logs -v /home/data/docker/MysqL/MysqL1/data:/var/lib/MysqL -v /home/data/docker/MysqL/MysqL1/conf:/etc/MysqL -e MysqL_ROOT_PASSWORD=Abcd12345 -d --restart=always MysqL:5.7

docker run -p 3307:3306 --name MysqL_slave1 --net mynetwork --ip 172.18.0.37 -e TZ=Asia/Shanghai -v /home/data/docker/MysqL/MysqL2/logs:/logs -v /home/data/docker/MysqL/MysqL2/data:/var/lib/MysqL -v /home/data/docker/MysqL/MysqL2/conf:/etc/MysqL -e MysqL_ROOT_PASSWORD=Abcd12345 -d --restart=always MysqL:5.7

docker run -p 3308:3306 --name MysqL_slave2 --net mynetwork --ip 172.18.0.38 -e TZ=Asia/Shanghai -v /home/data/docker/MysqL/MysqL3/logs:/logs -v /home/data/docker/MysqL/MysqL3/data:/var/lib/MysqL -v /home/data/docker/MysqL/MysqL3/conf:/etc/MysqL -e MysqL_ROOT_PASSWORD=Abcd12345 -d --restart=always MysqL:5.7

 上面的docker run命令中的参数都有其独特的含义，比如 -p 3307:3306  表示当前环境主键的端口(3307)：docker容器内的端口(3306)

-v /home/data/docker/MysqL/MysqL1/conf:/etc/MysqL  这个配置的是MysqL的配置文件路径，

对于MysqL_master 需要在当前系统/home/data/docker/MysqL/MysqL1/conf目录下创建 my.cnf文件，内容如下：

[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
log-bin=/var/lib/MysqL/MysqL-bin
server-id=36
## 主从复制的格式（mixed,statement,row，默认格式是statement）
binlog_format=mixed  
## 二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。
expire_logs_days=7
max_allowed_packet=20M

剩余的两个从库配置，/home/data/docker/MysqL/MysqL2/conf目录下创建 my.cnf文件

[MysqLd]
server-id=37
log-bin=/var/lib/MysqL/MysqL-slave1-bin

/home/data/docker/MysqL/MysqL3/conf目录下创建 my.cnf文件

[MysqLd]
server-id=38
log-bin=/var/lib/MysqL/MysqL-slave2-bin

 

配置修改完成后，重启容器：

docker restart MysqL_master
docker restart MysqL_slave1
docker restart MysqL_slave2

 

二：主从配置

1：进入主库：docker exec -it MysqL_master /bin/bash

bash> MysqL -uroot -p
MysqL> SHOW MASTER STATUS;
 File             | Position | binlog_Do_DB | binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| MysqL-bin.000001 |      154|

 2：同步MysqL_slave1 和 MysqL_slave2

# 进入MysqL_salve1
bash> docker exec -it MysqL_slave1 /bin/bash
# 执行同步
MysqL> change master to master_host='172.18.0.36', master_user='root',master_password='Abcd12345',master_port=3306,master_log_file='MysqL-bin.000001',master_log_pos=154,master_connect_retry=30;
MysqL> start slave;

# 进入MysqL_salve2
bash> docker exec -it MysqL_slave2 /bin/bash
# 执行同步
MysqL> change master to master_host='172.18.0.36', master_user='root',master_password='Abcd12345',master_port=3306,master_log_file='MysqL-bin.000001',master_log_pos=154,master_connect_retry=30;
MysqL> start slave;

master_port：Master的端口号，指的是容器的端口号
master_user：用于数据同步的用户
master_password：用于同步的用户的密码
master_log_file：指定 Slave 从哪个日志文件开始复制数据，即主库中提到的 File 字段的值
master_log_pos：从哪个 Position 开始读，即主库中提到的 Position 字段的值
master_connect_retry：如果连接失败，重试的时间间隔，单位是秒，默认是60秒


最后，可以自己验证下同步的效果了。


 
我们今天的关于修改 docker 下 mysql 配置和docker修改mysql配置文件的分享已经告一段落，感谢您的关注，如果您想了解更多关于docker mysql 主从同步配置、docker mysql 主从配置、docker MySQL 修改密码、Docker mysql主从配置的相关信息，请在本站查询。

		

	

本文标签：
 

		





      

    

    

      
 上一篇【Android】【问题解决记录】Error obtaining UI hierarchy :Error while obtaining UI hierarchy XML file: com.a...   

      
 下一篇剑指offer（62）二叉搜索树的第K个节点（二叉搜索树的第k个结点）   

    


    

      
相关文章