MySQL 主备同步设置

描述

数据库主备同步，将mysqlA 中的数据同步mysqlB 中，mysqlA 作为主库，mysqlB作为备库

准备

1.两个数据库mysqlA,mysqlB 要同步的库为test

数据库信息

库名	IP地址	端口	同步用户名	同步密码
mysqlA	127.0.0.1	3306	replic	replic
mysqB	127.0.0.1	3307

(模拟两个库，我们在同一台电脑上装了2个mysql 端口分别为3306,3307)

2.创建同步账号

CREATE USER replic IDENTIFIED BY 'replic';  
-- GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%';
-- GRANT ALL PRIVILEGES ON *.* TO 'canal'@'%' ;
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replic'@'%';
FLUSH PRIVILEGES;

3.开启mysqlA(主库) 的binlog日志

查看mysql binlog 是否开启

命令：show VARIABLES LIKE '%log_bin%'

开启主从同步配置（binlog）

修改：my.ini(my.cnf)文件

[mysql]
#设置msyql客户端默认字符集
default-character-set=utf8
[mysqld]
bind-address=0.0.0.0
port=3306
#设置mysql的安装目录
basedir=D://soft\mysql-5.7.41-winx64
#设置MySQL数据库数据的存放目录
datadir=D://soft\mysql-5.7.41-winx64\data

#开启主从同步
#开启binlog日志 一个等同下面三行命令
log_bin=D://soft\mysql-5.7.41-winx64\data\logbin\mysql-bin
#binlog日志的基本文件名，需要注意的是启动mysql的用户需要对这个目录
#log_bin_basename=D://soft\mysql-5.7.41-winx64\data\logbin\mysql-bin
#binlog文件的索引文件，管理所有binlog文件
#log_bin_index=D://soft\mysql-5.7.41-winx64\data\logbin\mysql-bin.index
#设置binlog的清理时间
expire_logs_days=100
# 配置binlog日志的格式
binlog_format = ROW
# 配置 MySQL replaction 需要定义，不能和 canal 的 slaveId 重复
server-id=1

4.配置mysqlB(备库)同步信息

1.配置my.ini(my.cnf)信息

[mysql]
#设置msyql客户端默认字符集
default-character-set=utf8
[mysqld]
bind-address=0.0.0.0
port=3307
#设置mysql的安装目录
basedir=D:/soft/mysql-5.7.41-winx64-slaver
#设置MySQL数据库数据的存放目录
datadir=D:/soft/mysql-5.7.41-winx64-slaver/data

#开启主从同步

[mysqld]
#每个MySQL实例必须有一个唯一的服务器ID
server-id = 3
#开启binlog日志，如果不想制定路径，只写mysql-bin即可
#log_bin=mysql-bin
log_bin=D:/soft/mysql-5.7.41-winx64-slaver/logbin\mysql-bin
#从服务器用于存储中继日志的位置
relay-log =D:/soft/mysql-5.7.41-winx64-slaver/relaylog\mysql-relay-bin
#忽略掉不需要同步的表 数据库名.表名
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=performance_schema.%
replicate-wild-ignore-table=information_schema.%
replicate-wild-ignore-table=sys.%


#binlog日志的基本文件名，需要注意的是启动mysql的用户需要对这个目录
#log_bin_basename=D://soft\mysql-5.7.41-winx64\data\logbin\mysql-bin
#binlog文件的索引文件，管理所有binlog文件
#log_bin_index=D://soft\mysql-5.7.41-winx64\data\logbin\mysql-bin.index
#设置binlog的清理时间
expire_logs_days=33
# 配置binlog日志的格式
binlog_format = ROW
#跳过所有错误执行语句（存在主备库数据不一致的情况，会导致语句错误，使slave_sql_runing 停止）
slave-skip-errors=all

2.配置主库信息

在mysqlB(从库运行下面命令)

注释：MASTER_LOG_FILE,MATER_LOG_POS 可以在主库中运行SHOW MASTER STATUS 获取信息

 在从库中运行下面sql

-- 配置信息
-- MASTER_HOST 主库的IP地址,
-- MASTER_PORT 主库的端口号,
-- MASTER_USER 主库用户名,
-- MASTER_PASSWORD 主库密码,
-- MASTER_LOG_FILE  指定从哪个二进制日志文件开始复制,
-- MASTER_LOG_POS 指定从哪个位置开始复制;

CHANGE MASTER TO
MASTER_HOST='127.0.0.1',
MASTER_PORT =3306,
MASTER_USER='replic',
MASTER_PASSWORD='replic',
MASTER_LOG_FILE='mysql-bin.000004',
MASTER_LOG_POS=6030;
-- 开启
 -- START SLAVE;
-- 停止
 -- STOP SLAVE;
 --重置
 -- RESET SLAVE;

5.启动从库配置

1.启动

运行: START SLAVE;

2.查看结果

运行： SHOW SLAVE STATUS; 

出现如下图信息 说明配置成功