Mysql主从复制配置

Mysql主从复制配置

准备两台服务器，服务器的IP地址分别为192.168.56.101、192.168.56.102，在两台服务器上分别使用Docker部署Mysql，Master部署在192.168.56.101服务器，Slave部署在192.168.56.102服务器，实现Mysql主从同步功能。

一、Docker部署Mysql

• Master节点

  sudo docker run -p 3306: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
  

• Slave节点

  sudo docker run -p 3306:3306 --name mysql-slave \
  -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
  

二、Mysql配置文件

• Master节点的my.cnf配置

  cd /mydata/mysql/master/conf
  vim my.cnf
  

  my.cnf配置文件的内容

  [client]
  default-character-set=utf8
  [mysql]
  default-character-set=utf8
  [mysqld]
  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一定要加，否则会很慢
  skip-name-resolve
  
  server_id=1
  log-bin=mysql-bin
  # Master可读可写
  read-only=0
  # 指定需要同步的数据库
  binlog-do-db=mall_ums
  binlog-do-db=mall_pms
  binlog-do-db=mall_oms
  binlog-do-db=mall_sms
  binlog-do-db=mall_wms
  binlog-do-db=mall_admin
  
  # 不主从复制的数据库
  replicate-ignore-db=mysql
  replicate-ignore-db=sys
  replicate-ignore-db=information_schema
  replicate-ignore-db=performance_schema
  

• Slave节点的my.cnf配置

  cd /mydata/mysql/slave/conf
  vim my.cnf
  

  my.cnf配置文件的内容

  [client]
  default-character-set=utf8
  [mysql]
  default-character-set=utf8
  [mysqld]
  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一定要加，否则会很慢
  skip-name-resolve
  
  server_id=2
  log-bin=mysql-bin
  # Slave只读
  read-only=1
  # 指定需要同步的数据库
  binlog-do-db=mall_ums
  binlog-do-db=mall_pms
  binlog-do-db=mall_oms
  binlog-do-db=mall_sms
  binlog-do-db=mall_wms
  binlog-do-db=mall_admin
  
  # 不主从复制的数据库
  replicate-ignore-db=mysql
  replicate-ignore-db=sys
  replicate-ignore-db=information_schema
  replicate-ignore-db=performance_schema
  

  注意：Master和Slave的server_id必须不一样

  重启Master和Slave节点Mysql容器

  docker restart mysql-master
  docker restart mysql-slave
  

三、Mysql主从配置

进入Mysql容器内部，或使用数据库连接工具如Navicat连接数据库(需要关闭防火墙，或者开放3306端口)

• Master节点Mysql执行

  # 在mysql命令行中
  GRANT REPLICATION SLAVE ON *.* to 'backup'@'%' identified by '123456'
  
  SHOW MASTER STATUS;
  

• Slave节点Mysql执行

  # 设置主从链接
  change master to
  master_host='192.168.56.101',master_user='backup',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=0,master_port=3306;
  
  start slave;
  show slave status;
  

  如果执行结果如下，表示主从复制配置成功：

主从复制测试

在Master节点的Mysql创建mall_ums、mall_pms、mall_oms、mall_sms、mall_wms、mall_admin数据库，并在数据库中创建表，向表中加入数据，刷新Slave节点的Mysql，可以看到数据库，以及表和表中的数据都被自动同步。