linux的dokers的mysql与window11的MySQL实现主从复制

Mac：ubuntu20.04
Docker：2.0.0.3//采取docker安装MySQL主要是管理维护方便、独立IP、启动秒级
MySQL-master：8.0.20//主服务器
MySQL-master IP：192.168.124.128
MySQL-slave：8.0.20//从服务器
MySQL-slave IP：127.0.0.1

一：Linux的ubuntu20.04的配置。

ubuntu20.04的ip：192.168.124.128（ifconfig获得），安装好dockers

#先关掉防火墙
sudo ufw disable

#用docker下安装mysql的镜像。
 sudo docker pull mysql:8.0.20 

#创建文件
sudo mkdir -p /user/local/conf /usr/local/mysql/logs /user/local/mysql/data
sudo gedit /user/local/conf/hmy.cnf

在hmy.cnf配置

[mysqld]

port = 3306
#服务id,在一个主从复制集群中要唯一,值范围1-255

server-id = 121

#开启log-bin日志,非常重要,复制原理也是基于这一个,后面的值随便写我这里就写mysql-master-bin了

log-bin = mysql-bin

#从机复制时,忽略的数据库,也就是说这里配置的数据库不会被从机同步

binlog-ignore-db = mysql,information_schema,performance_schema,sys

然后创建

/usr/local/mysql/conf# docker run -p 3306:3306 \
>     --name mysql \
>     -e MYSQL_ROOT_PASSWORD=123456 \
>     -v /usr/local/mysql/conf:/etc/mysql/conf.d \
>     -v /usr/local/mysql/logs:/var/log/mysql \
>     -v /usr/local/mysql/data:/var/lib/mysql \
>     -d mysql:8.0.20

3. 进入容器
docker exec -it mysql-master bash
4. 登录MySQL
mysql -uroot -p123456
5.创建slave的远程连接
create user 'slave'@'%' identified by '123456';//创建用户
grant replication slave,replication client on *.* to 'slave'@'%';//设置用户权限
flush privileges;//刷新权限
show grants for 'slave'@'%';//查看用户权限

show master status

 window11的mysql的配置my.setting

[mysqld]
server-id=3
#二进制日志文件格式
log-bin=mysql-bin
# 从库二进制日志
relay-log=mysql-relay
#binlog日志格式
binlog_format=mixed
# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir=D:\Java_Zong\C_Java\Java\mysql\mysql-8.0.20-winx64 (1)\mysql-8.0.20-winx64 
# 设置mysql数据库的数据的存放目录
datadir=D:\Java_Zong\C_Java\Java\mysql\mysql-8.0.20-winx64 (1)\mysql-8.0.20-winx64\data
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8

重启mysql的服务

在window的mysqL:

CHANGE MASTER TO
    -> MASTER_HOST='192.168.124.128',MASTER_USER='slave',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=156;

mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.17.0.3
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 961
Relay_Log_File: 87dc5224655d-relay-bin.000003
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes //表示I/O线程读取成功
Slave_SQL_Running: Yes //表示SQL线程执行成功
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 961
Relay_Log_Space: 892
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 45540733-4e0c-11ea-b0ac-0242ac110003
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:

如果不会可以看MySQL 的主从复制实践-阿里云开发者社区