主从同步

1. 下载mysql镜像

docker pull mysql:5.7

2.创建master实例并启动

docker run \
    -p 3307:3306 \
    --name mysql-master \
    -v /mydata/mysql/master/log:/var/log/mysql \
    -v /mydata/mysql/master/data:/var/lib/mysql \
  	-v /mydata/mysql/master/conf:/etc/mysql \
    -e MYSQL_ROOT_PASSWORD=root \
    -d mysql:5.7

修改配置文件:

vi /mydata/mysql/master/conf/my.cnf

[client]                                        # 客户端设置，即客户端默认的连接参数
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
[mysqld]                                        # 服务端基本设置
init_connect='SET collation_connection = utf8_unicode_ci'        
init_connect='SET NAMES utf8mb4'       
character-set-server = utf8mb4   
collation-server = utf8mb4_general_ci
skip-character-set-client-handshake
skip_name_resolve 

#添加master主从复制部分配置
server_id = 1                                  # Mysql服务的唯一编号 每个mysql服务Id需唯一
log-bin = mysql-bin                            # 开启mysql binlog功能
read-only=0                                    # 是否只读,1是0否
binlog_do_db=gulimall_ums                      # 哪一个数据库的增删改查需要记录改动日志
binlog_do_db=gulimall_pms
binlog_do_db=gulimall_oms
binlog_do_db=gulimall_sms
binlog_do_db=gulimall_wms
binlog_do_db=gulimall_admin
#...
replicate-ignore-db=mysql                      # 哪些表需要忽略,一般是mysql的配置相关表
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema

3. 创建slave实例并启动

docker run \
    -p 3317:3306 \
    --name mysql-slaver-01 \
    -v /mydata/mysql/slaver/log:/var/log/mysql \
    -v /mydata/mysql/slaver/data:/var/lib/mysql \
    -v /mydata/mysql/slaver/conf:/etc/mysql \
    -e MYSQL_ROOT_PASSWORD=root \
    -d mysql:5.7

修改配置文件:

[client]                                        # 客户端设置，即客户端默认的连接参数
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
[mysqld]                                        # 服务端基本设置
init_connect='SET collation_connection = utf8_unicode_ci'        
init_connect='SET NAMES utf8mb4'       
character-set-server = utf8mb4   
collation-server = utf8mb4_general_ci
skip-character-set-client-handshake
skip_name_resolve 

#添加slaver主从复制部分配置
server_id = 2                                  # Mysql服务的唯一编号 每个mysql服务Id需唯一
log-bin = mysql-bin                            # 开启mysql binlog功能
read-only=1                                    # 是否只读,1是0否
binlog_do_db=gulimall_ums                      # 哪一个数据库的增删改查需要记录改动日志
binlog_do_db=gulimall_pms
binlog_do_db=gulimall_oms
binlog_do_db=gulimall_sms
binlog_do_db=gulimall_wms
binlog_do_db=gulimall_admin
#...
replicate-ignore-db=mysql                      # 哪些表需要忽略,一般是mysql的配置相关表
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema

4. 为master授权用户来同步他的数据

4.1. 进入master容器(两个mysql都要执行)

docker exec -it mysql-master /bin/bash

4.2. 进入mysql内部

mysql -uroot -p

4.3. 授权root用户可以远程访问(这一步与主从同步无关),放开3307和3317端口

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;

FLUSH PRIVILEGES;

4.4. 添加用来同步的用户(授权一个用户可以访问主节点，进行日志复制,主库执行)

GRANT REPLICATION SLAVE ON *.* to 'backup'@'%' identified by '123456';

4.5. 查看master状态

show master status

4.6. 从库设置连接主库,告诉从库需要同步哪个主节点(从库执行)

change master to master_host='49.234.180.32',master_user='backup',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=154,master_port=3307;

4.7. 启动从库同步

start slave;

4.8. 查看从库状态

show slave status;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BcuXT7Ey-1612435428782)(C:\Users\dell\AppData\Roaming\Typora\typora-user-images\image-20210204183916742.png)]

导入sql文件即可测试(加入创建库)