mysql8主从环境搭建,mysql8主从复制环境搭建docker实现-Go语言中文社区

实验效果演示

两个server的id是不能一样的，master的crud操作会同步到slave里面

安装环境

操作系统：Linux VM_0_12_centos 3.10.0-693.el7.x86_64 #1 SMP Tue Aug 22 21:09:27 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux

数据版本：mysql 8.0.11

docker版本：Docker version 1.13.1, build 94f4240/1.13.1

docker-compose版本：docker-compose version 1.8.1, build 878cff1

主机A：172.18.4.24 master

主机B: 172.18.4.25 slave

数据库工具版本：navicat12

因为这里使用的是mysql8，低版本的mysql客户端需要注意，不升级的话，连接数据库比较麻烦，所以建议还是升级的好。

编写master的my.cnf

下面是master.my.cnf文件

# Copyright (c) 2017, 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 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

secure-file-priv= NULL

server-id=1

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

symbolic-links=0

# Custom config should go here

!includedir /etc/mysql/conf.d/

编写slave的my.cnf

下面是slave.my.cnf文件

# Copyright (c) 2017, 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 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

secure-file-priv= NULL

server-id=2

relay_log_recovery=0

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

symbolic-links=0

# Custom config should go here

!includedir /etc/mysql/conf.d/

编写docker-compose文件

下面是docker-compose.yml文件

version: '2'

services:

mysql-master:

image: docker.io/mysql

networks:

jznet:

ipv4_address: 172.18.4.24

volumes:

-/root/data/mysql-master:/var/lib/mysql -/home/wen/master.my.cnf:/etc/mysql/my.cnf ports:

-"3306:3306" environment:

-MYSQL_DATABASE=root -MYSQL_ROOT_PASSWORD=123456 mysql-slave:

image: docker.io/mysql

networks:

jznet:

ipv4_address: 172.18.4.25

volumes:

-/root/data/mysql-slave:/var/lib/mysql -/home/wen/slave.my.cnf:/etc/mysql/my.cnf ports:

-"3307:3306" environment:

-MYSQL_DATABASE=root -MYSQL_ROOT_PASSWORD=123456networks:

jznet:

driver: bridge

ipam:

driver: default

config:

-subnet: 172.18.4.0/26

执行操作命令

文件位置：

linux 下面执行下面命令

[root@VM_0_12_centos wen]# docker-compose up -d

Creating network "wen_jznet" with driver "bridge"

Creating wen_mysql-slave_1

Creating wen_mysql-master_1

使用navicat12连接上数据库，在master的机器执行下面命令，创建用户之后，授予用户复制的权限。

CREATE USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'Ron_master_1';

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

然后在master机器上查看master机器的状态：

然后我切换到slave的数据库，执行下面命令，设置master的信息

CHANGE MASTER TO

MASTER_HOST='172.18.4.24',

MASTER_USER='repl',

MASTER_PASSWORD='Ron_master_1',

MASTER_LOG_FILE='binlog.000002',

MASTER_LOG_POS=642;

然后在slave数据再执行下面命令,开始同步

start slave;

show slave status;

执行完的结果是

实验到这里，搭建就完成了，然后就可以见到最上面的实验演示的结果了，master的操作CRUD，都会同步到slave上面。

实验错误汇总

Slave failed to initialize relay log info structure from the repository