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] 段添加以下配置：如果上面挂载了这步可以忽略

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

重启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';

启动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)