基于docker下的mysql主从同步

目录

一、创建Master和Slave

1.1.1、创建master

1.1.2、修改master配置

1.2.1、创建slave

1.2.2、修改slave配置

二、master开启日志权限，给slave分配账号权限

2.1.1、添加master主从复制部分配置

2.1.2、为master授权用户来同步数据

2.2.1、添加slave主从复制部分配置

2.2.2、配置Slater同步Master数据

---

一、创建Master和Slave

1.1.1、创建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

 

1.1.2、修改master配置

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

[client]

default-character-set=utf8

 

[mysql]

default-character-set=utf8

 

[mysqlId]

init_connect='SET collation_connection=utf8_unicode_ci'

init_connect='SET NAMES utf8'

character-set-server=utf8

collation-server=utf8_unicode_ci

skip-character-set-client-handshake

skip-name-resolve

 

 

1.2.1、创建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

1.2.2、修改slave配置

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

[client]

default-character-set=utf8

 

[mysql]

default-character-set=utf8

 

[mysqlId]

init_connect='SET collation_connection=utf8_unicode_ci'

init_connect='SET NAMES utf8'

character-set-server=utf8

collation-server=utf8_unicode_ci

skip-character-set-client-handshake

skip-name-resolve

 

二、master开启日志权限，给slave分配账号权限

2.1.1、添加master主从复制部分配置

server_id=1

log-bin=mysql-bin

read-only=0

binlog-do-db=test_db

 

replicate-ignore-db=mysql

replicate-ignore-db=sys

replicate-ignore-db=information-schema

replicate-ignore-db=performance_schema

修改完配置后重启master容器： docker restart master

2.1.2、为master授权用户来同步数据

 

1、添加用来同步的用户：  GRANT REPLICATION SLAVE ON *.* to ‘backup’@’%’ identified by ‘123456’;

2、查看master状态：Show master status;

 

2.2.1、添加slave主从复制部分配置

server_id=2

log-bin=mysql-bin

read-only=1

binlog-do-db=test_db

 

replicate-ignore-db=mysql

replicate-ignore-db=sys

replicate-ignore-db=information_schema

replicate-ignore-db=performance_schema

修改完配置后重启slave容器： docker restart slave

2.2.2、配置Slater同步Master数据

1、设置主库连接：change master to master_host=’192.168.56.133,master_user=’backup’,master_password=’root’,master_log_file=’mysql-bin.000001’,master_log_pos=0,master_port=3307;

2、启动从库同步：start slave;

3、查看从库状态：show slave status;

slave正确状态：