mysql搭建主从

mysql搭建主从:

1:拉取mysql镜像

docker pull mysql

2:创建主从对应目录

3:建立一个简易的mysql

docker run -it --name mytest -e MYSQL_ROOT_PASSWORD=123 -d mysql

4:进入这个简易的mysql;从中获取my.cnf文件

docker exec -it mytest bash

5:从容器中将my.cnf拷贝到 /3306/conf

docker cp mytest:/etc/mysql/my.cnf ./

6:获取到my.cnf文件后;将简易的mysql容器删除;

7:创建 主(master) :mysql_3306

docker run \
-it \
--name mysql_3306 \
--privileged \
--network wn_docker_net \
--ip 172.18.12.2 \
-p 3306:3306 \
-v  /usr/local/software/mysql/3306/conf/my.cnf:/etc/mysql/my.cnf \
-v  /usr/local/software/mysql/3306/data:/var/lib/mysql \
-v /usr/local/software/mysql/3306/mysql-files:/var/lib/mysql-files \
-e MYSQL_ROOT_PASSWORD=123 \
-d mysql

8:用navicat 测试是否能够连接mysql_3306

9:master服务器配置:my.cnf

# Custom config should go here
server-id=200    #server‘id
log_bin=wnhz-master-logbin    #logbin name
binlog_format=row

10:进入主(master),查看 File 和 position 的值

11:将my.cnf文件下载;然后分别上传到3310,3311文件夹下的conf文件夹

12:创建从(slave): mysql_3310

docker run \
-it \
--name mysql_3310 \
--privileged \
--network wn_docker_net \
--ip 172.18.12.3 \
-p 3310:3306 \
-v  /usr/local/software/mysql/3310/conf/my.cnf:/etc/mysql/my.cnf \
-v  /usr/local/software/mysql/3310/data:/var/lib/mysql \
-v /usr/local/software/mysql/3310/mysql-files:/var/lib/mysql-files \
-e MYSQL_ROOT_PASSWORD=123 \
-d mysql

13:用navicat测试 slave 是否能够连接

14:slave 服务器配置 my.conf

server-id=201    #server‘id
log_bin=wnhz-slave-01-logbin    #logbin name
relay_log=wnhz-slave-01-relay
read-only=1

15:进入主(master)容器,创建用户slave进行主从关联

create user 'slave'@'%' IDENTIFIED WITH mysql_native_password BY '123';
GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'slave'@'%';
flush privileges;

16:进入slave容器，连接mysql_3306

change master to master_host='172.18.12.2', master_user='slave',master_password='123',MASTER_LOG_FILE='wnhz-master-logbin.000001',MASTER_LOG_POS=855;

change master to master_host='172.18.12.2', master_user='slave',master_password='123',MASTER_LOG_FILE='wnhz-master-logbin.000001',MASTER_LOG_POS=156;

17:查看slave 状态

18:实战测试,在master创建数据库;看slave是否能够同步

19:特别注意

在修改容器的配置文件之后;一定要将容器重启;不然会导致对配置文件的修改不生效;

20:出现问题及其解决方法

1. 关闭slave

   stop slave;
   

2. 重置slave: replaylog

   reset slave;
   

3. 重新配置 change to

   show master status;    #maseter
   先在 master 中执行 show master status; 
   将查到的 MASTER_LOG_FILE,MASTER_LOG_POS 分别取代以下的 xxx
   
   change master to master_host='172.18.12.2', master_user='slave',master_password='123',MASTER_LOG_FILE='xxx',MASTER_LOG_POS=xxx;  #slave
   

4. 重新运行slave

start slave;

21:创建从(slave) mysql_3311

重复创建 mysql_3310 的步骤

22:开放端口的步骤

1. firewall-cmd --add-port=3312/tcp --permanent
2. firewall-cmd --reload
3. firewall-cmd --list-ports