MYSQL主从配置

• 起一台主镜像

docker run -d -e MYSQL_ROOT_PASSWORD=monitor@20191122 \
 --name master \
  -v ~/mysql/master/my.cnf:/etc/mysql/my.cnf \
 -v ~/mysql/master/data:/var/lib/mysql \
  -p 3301:3306 mysql:5.7

• 起一台从镜像

docker run -d -e MYSQL_ROOT_PASSWORD=monitor@20191122 \
 --name slave \
  -v ~/mysql/slave/my.cnf:/etc/mysql/my.cnf \
  -v ~/mysql/slave/data:/var/lib/mysql \
  -p 3302:3306 lxkj/mysql:5.7

• 主机配置文件

[mysqld]
server-id = 1
log-bin= mysql-bin
read-only=0
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
interactive_timeout=31536000
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
lower_case_table_names=1

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

• 从服务器配置文件

[mysqld]
server_id = 2
log-bin= mysql-bin
read-only=1
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
interactive_timeout=31536000
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
lower_case_table_names=1
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

• 进入MYSQL创建同步用户

CREATE USER 'backup'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* to 'backup'@'%' identified by '123456';
FLUSH PRIVILEGES;

• 在从机查询主机状态

show master status;

• 在从机修改相关配置

change master to master_host='192.168.0.107', 
    master_port=3306, 
    master_user='backup', 
    master_password='123456', 
    master_log_file='mysql-bin.000003', 
    master_log_pos=830;

• 启动主从同步

start slave;

查询状态

show slave status ;

• 查询镜像IP地址

docker inspect --format='{{.NetworkSettings.IPAddress}}

---