Linux下基于Docker搭建MySQL主从复制

Linux下基于Docker搭建MySQL主从复制

拉取 MySQL 容器镜像

docker pull mysql:5.7

这里我使用的是 5.7 版本，如果你想要拉取最新版本的镜像，可以使用：

docker pull mysql:latest

docker images

REPOSITORY TAG  IMAGE ID     CREATED    SIZE
mysql      5.7  f6509bac4980 4 days ago 373MB

创建 MySQL 容器

可提前完成配置文件并 挂载到/data/mysql,准备好mysqld.cnf文件放到 master、slave1、slave2目录

master目录的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
log-bin=mysql-bin
server-id=2

slave1的mysqld.cnf文件 ，注意 server-id 不能相同

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

# 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
server-id=41
default-storage-engine=MyISAM 

 

m; 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
server-id=3
default-storage-engine=MyISAM 

slave2目录的 mysqld.cnf文件如下

 

创建主数据库容器

docker run --name mysql-master -p 3306:3306 -v /data/mysql/master:/etc/mysql/mysql.conf.d -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7

创建从数据库容器

docker run --name mysql-slave1 -p 3307:3306 -v /data/mysql/slave1:/etc/mysql/mysql.conf.d -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
docker run --name mysql-slave2 -p 3308:3306 -v /data/mysql/slave2:/etc/mysql/mysql.conf.d -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7

这里我们分别使用了 3306 和 3307、3308 端口

配置主服务

首先，进入容器：

docker run --name mysql-master -p 3307:3306 -v

连接 MySQL

mysql -u root -p123456

修改 root 可以通过任何客户端连接

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

 

修改 MySQL 配置文档 /etc/mysql/mysql.conf.d/mysqld.cnf ，在 [mysqld] 段添加以下配置：如果上面挂载了这步可以mysqld.cnf文件，可以忽略

log-bin=mysql-bin   //[必须]启用二进制日志
server-id=2         //[必须]服务器标识ID，每台服务器唯一

进入容器内部编辑

cd /etc/mysql切换到/etc/mysql目录下，然后vi my.cnf对my.cnf进行编辑。此时会报出bash: vi: command not found，需要我们在docker容器内部自行安装vim。使用apt-get install vim命令安装vim

会出现如下问题：

Reading package lists... Done
Building dependency tree       
Reading state information... Done
E: Unable to locate package vim

执行apt-get update，然后再次执行apt-get install vim即可成功安装vim。然后我们就可以使用vim编辑my.cnf，在my.cnf中添加如下配置：

[mysqld]
## 同一局域网内注意要唯一
server-id=100  
## 开启二进制日志功能，可以随便取（关键）
log-bin=mysql-bin

配置完成之后，需要重启mysql服务使配置生效。使用service mysql restart完成重启。重启mysql服务时会使得docker容器停止，我们还需要docker start mysql-master启动容器。

 

重启Master服务器

docker restart mysql-maser

再次进入maser容器

docker exec -it mysql-master /bin/bash

连接 MySQL

mysql -u root -p123456

查看数据库状态：

mysql> show master status;

+------------------+----------+--------------+------------------+---------------

----+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

Executed_Gtid_Set |

+------------------+----------+--------------+------------------+---------------

----+

| mysql-bin.000004 |      154 |             |                 |              

    |

+------------------+----------+--------------+------------------+---------------

----+

 

记住上面的 file 和position

 

配置从服务器

首先，进入容器：

docker exec -it mysql-slave1 /bin/bash

连接 MySQL

mysql -u root -p123456

修改 root 可以通过任何客户端连接（默认root用户可以对从数据库进行编辑的）

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

修改配置文件mysqld.cnf

server-id=3
default-storage-engine=MyISAM # 修改引擎为MyISAM 【选择配置不是必须的，后面有说明】

配置从同步主服务数据,执行如下SQL

 

change master to
master_host='192.168.2.130',
master_user='root',
master_log_file='mysql-bin.000004',
master_log_pos=154,
master_port=3306,
master_password='123456';

master_host ：Master的地址，指的是容器的独立ip,可以通过docker inspect --format='{{.NetworkSettings.IPAddress}}' 容器名称|容器id查询容器的ip

 

 

master_port：Master的端口号，指的是容器的端口号

master_user：用于数据同步的用户

master_password：用于同步的用户的密码

master_log_file：指定 Slave 从哪个日志文件开始复制数据，即上文中提到的 File 字段的值

master_log_pos：从哪个 Position 开始读，即上文中提到的 Position 字段的值

master_connect_retry：如果连接失败，重试的时间间隔，单位是秒，默认是60秒

启动slave服务

mysql>start slave;

查看slave状态

show slave status \G;

 

 出现两个yes代表主从配置成功

测试主从搭建结果

在 master 容器中创建一张 user 表；

Create Database TestUser;
Use TestUser;

mysql> CREATE TABLE `user` (
       `user_id` bigint(20) AUTO_INCREMENT,
       `username` varchar(30) NOT NULL,
       `password` varchar(30) DEFAULT NULL,
       PRIMARY KEY (`user_id`)
     ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

然后在 slave 容器查看：

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| user           |
+----------------+
1 row in set (0.00 sec)

目前看到数据表已经同步过来

在 master 服务器 user 表插入一条数据：

mysql> insert into user(username, password) values ('feng', 
'123456');

然后看看 slave 服务器是否有同步(也可以navigat查看)

这里我们只是测试，所以把数据表也同步过来了。一般情况下数据表是不同步的，先各自

在 master 和 salve 创建好。 因为一般来说 master 和 slave 的表的存储引擎是不一样的，

master 一般用的是 InnoDB ，因为它要写数据比较多。 而 slave 表一般用的是 MyISAM 引擎，

因为它是没有写数据操作，只有读，用 MyISAM 引擎能大大节省资源，速度也会快一些。

★ 重点：不要在从服务执行数据的DML操作，一旦执行成功MySQL主从直接就被破坏掉，需要再

次执行 change sql语句重新恢复

 

停止全部容器以及删除全部容器

docker stop $(docker ps -q) & docker rm $(docker ps -aq)