mysql主从

安装。。(docker 为例)

docker network create —subnet=172.10.0.0/24 mbsnet

mkdir -p mysql-master && cd $_ && mkdir {conf,data}
mkdir -p mysql-slave1 && cd $_ && mkdir {conf,data}

docker run --restart=unless-stopped --name mysql-master --net mbsnet --ip 172.10.0.11 -v /home/mysql-master/conf/my.cnf:/etc/mysql/my.cnf -v /home/mysql-master/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql

docker run --restart=unless-stopped --name mysql-slave1 --net mbsnet --ip 172.10.0.12 -v /home/mysql-slave1/conf/my.cnf:/etc/mysql/my.cnf -v /home/mysql-slave1/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql

主my.cnf

# Copyright (c) 2017, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
#
# The MySQL Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
[mysqld]
server-id=1
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql

log-bin=mbssystem

#binlog-do-db=mbsdb
##指定不需要同步的数据库名称
binlog-ignore-db=mysql  
# 开启二进制日志功能
#log-bin=mall-mysql-bin  
# 设置二进制日志使用内存大小（事务）
binlog_cache_size=1M  
# 设置使用的二进制日志格式（mixed,statement,row）
binlog_format=mixed  
# 二进制日志过期清理时间。默认值为0，表示不自动清理。
expire_logs_days=7  
# 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
# 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062

# 普通用户只能读 OFF是关闭状态
#read_only=off 
# 超级用户只能读 OFF是关闭状态
#super_read_only=off

# 限制导入导出文件目录的, 可自行搜索, 我暂时没有用到过, 下面这样表示不限制目录
secure-file-priv =

character_set_server = utf8mb4
collation_server = utf8mb4_bin

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Custom config should go here
!includedir /etc/mysql/conf.d/

max_connections=1000
wait_timeout=120
interactive_timeout=300
lower_case_table_names=1

# 这里是配置加密方式的
# 启动警告 'default_authentication_plugin' is deprecated and will be removed in a future release. Please use authentication_policy instead.
default_authentication_plugin= mysql_native_password

从my.cnf

# Copyright (c) 2017, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
#
# The MySQL Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
[mysqld]
server-id=2
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql

## 指定不需要同步的数据库名称
binlog-ignore-db=mysql
#[可选]设置需要复制的数据库,默认全部记录。比如：binlog-do-db=mbssys
#binlog-do-db=需要复制的主数据库名字
### 开启二进制日志功能，以备Slave作为其它数据库实例的Master时使用
log-bin=
### 设置二进制日志使用内存大小（事务）
binlog_cache_size=1M
### 设置使用的二进制日志格式（mixed,statement,row）
binlog_format=mixed 
### 二进制日志过期清理时间。默认值为0，表示不自动清理。
expire_logs_days=7
### 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
### 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062
### relay_log配置中继日志
relay_log=mbssystem
### log_slave_updates表示slave将复制事件写进自己的二进制日志
log_slave_updates=1
### slave设置为只读（具有super权限的用户除外）
read_only=1

# 普通用户只能读 on是开启状态 
#read_only=on 
# 超级用户只能读 on是开启状态 
#super_read_only=on


# 限制导入导出文件目录的, 可自行搜索, 我暂时没有用到过, 下面这样表示不限制目录
secure-file-priv =

character_set_server = utf8mb4
collation_server = utf8mb4_bin

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Custom config should go here
!includedir /etc/mysql/conf.d/

max_connections=1000
wait_timeout=120
interactive_timeout=300
lower_case_table_names=1

# 这里是配置加密方式的
# 启动警告 'default_authentication_plugin' is deprecated and will be removed in a future release. Please use authentication_policy instead.
default_authentication_plugin= mysql_native_password

在主机执行(mysql 8)

CREATE USER 'slave1'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'%';
#此语句必须执行。
ALTER USER 'slave1'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
flush privileges;

查看主机状态

show master status;

记录 File 和 Position

从机执行(如果要重置则使用reset slave

CHANGE MASTER TO MASTER_HOST='172.10.0.11',MASTER_USER='slave1',MASTER_PASSWORD='123456',MASTER_LOG_FILE='主机上的File',MASTER_LOG_POS=主机上的Position;
START SLAVE;
show slave status \G

或者

change replication source to source_host='172.10.0.11', source_user='slave1', source_password='123456', source_port=3306,source_log_file='主机上的File', source_log_pos=主机上的Position;
start replica;
show slave status \G