实验效果演示
两个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=123456
networks:
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上面。
实验错误汇总
1、有些同学在搭建的是后开启slave同步的时候出现下面这样的错误,解决方案可以参考这篇博文:https://blog.csdn.net/lwei_998/article/details/46811995
Slave failed to initialize relay log info structure from the repository