windows下MySQL主从复制配置

windows下MySQL主从复制配置

前言

  • jdk1.8
  • Mycat-server-1.6
  • sqlyog
  • MySQL 版本5.7(主从库均是)
  • 主库IP：192.168.0.118，从库IP：192.168.0.145
  • 局域网内测试
  • 建库脚本在末尾

配置主库

  1. 停止主库MySQL服务

  2. 打开主库my.ini文件进行如下配置， 请注意看注释，根据自己的情况修改：

    [mysql]
# 设置mysql客户端默认字符集
default-character-set = utf8
[mysqld]
# 设置3306端口
port = 3306
# 设置mysql的安装目录
basedir = D:BtSoftWebSoftmysqlMySQL5.7
# 设置mysql数据库的数据的存放目录
datadir = D:BtSoftWebSoftmysqlMySQL5.7data
# 允许最大连接数
max_connections = 200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server = utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine = INNODB
#=========主从复制关键配置=====================
# 服务器唯一ID, 主库和从库需要不一致, 1至32, 手动设定, 这里配置为1, 等会从库配置为2, 一般取IP最后一段
server-id = 1
#主从复制的数据库的名字, 如果此项不配置所有主库都参与复制, 如果想复制多个库, 就多复制几行就好了,千万别写成这样binlog-do-db=db1, db2, db3, 我试了这样不行
binlog-do-db = db1
binlog-do-db = db2
binlog-do-db = db3
#不需要复制的库，和上项同理, 这里忽略mysql数据库
binlog-ignore-db = mysql
#启用二进制日志
log-bin = mysql-bin
#=========主从复制关键配置=====================

    如果安装的MySQL没有my.ini文件的话，自己手动创建一个放在MySQL的安装目录（如 ：D:BtSoftWebSoftmysqlMySQL5.7my.ini）即可，这里需要注意MySQL的安装目录千万不要有空格，默认安装的MySQL是在C:Program FilesMySQLMySQL Server 5.7这个目录下，我试了不行， 必须不能有空格。

  3. 配置好之后保存，重启主库MySQL服务即可。

  4. 在主库中建立一个用户（专门用给从库连接的，注意这是在主库里面建立的)，执行以下SQL：

    -- 主库执行
-- 在主库中建立一个用户（专门用给从库连接的，注意这是在主库里面建立的) 
-- daier是用户名, 后面的111111是密码, 192.168.0.145 是从库IP
grant replication slave,reload,super on *.* to daier@192.168.0.145 identified by '111111' ;

  5. 刷新权限， 执行以下SQL：

    flush privileges;

  6. 显示主库状态， 执行以下SQL：

    show master status;

    在查询结果中找到File和Position的值记录下来， 配置从库时需要使用。

从库配置

  1. 停止从库服务

  2. 打开从库my.ini文件进行如下配置， 请注意看注释，根据自己的情况修改：

    [mysql]
# 设置mysql客户端默认字符集
default-character-set = utf8
[mysqld]
#设置3306端口
port = 3306
# 设置mysql的安装目录
basedir = D:BtSoftWebSoftmysqlMySQL5.7
# 设置mysql数据库的数据的存放目录
datadir = D:BtSoftWebSoftmysqlMySQL5.7data
# 允许最大连接数
max_connections = 200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server = utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine = INNODB
#=========主从复制关键配置=====================
#服务器唯一ID, 主库和从库需要不一致, 1至32, 手动设定, 这里配置为2, 一般取IP最后一段
server-id = 2
#主从复制的数据库的名字, 如果此项不配置所有主库都参与复制, 如果想复制多个库, 就多复制几行就好了,千万别写成这样binlog-do-db=db1, db2, db3, 我试了这样不行
binlog-do-db = db1
binlog-do-db = db2
binlog-do-db = db3
#不需要复制的库，和上项同理, 这里忽略mysql数据库
binlog-ignore-db = mysql
#启用二进制日志
log-bin = mysql-bin
#=========主从复制关键配置=====================

    注意上面的server-id这个配置一定不要和主库一样

  3. 配置好之后保存，重启主库MySQL服务即可。

  4. 在从库执行以下SQL，连接主库：

    -- 从库执行
change master to master_host = '192.168.0.118', -- 这里填你主库的IP
master_user = 'daier', -- 刚才我们创建的那个用户
master_password = '111111', -- 密码
master_log_file = 'mysql-bin.000003', -- 刚才在主库里面 show master status；得到的File值
master_log_pos = 1783, -- 刚才在主库里面 show master status；得到的Position值
master_port = 3306; -- 端口，默认是3306， 如果你是3306可以不写

  5. 从库执行

    -- 停止从服务器复制功能
stop slave；

    再执行

    -- 启动从服务器复制功能
start slave;

  6. 显示从库状态， 执行以下SQL：

    -- 显示从库状态
show slave status;

  7. 检查查询结果，主要看下面几个

    • Slave_IO_Running: Yes
      此状态必须YES
    • Slave_SQL_Running: Yes
      此状态必须YES

    这里Slave_IO_Running可能为Connecting，主要有下面几个错误导致：

    • 网络不通，关闭主库防火墙
    • 密码不对
    • pos不对

  8. 测试
    读者自行测试即可，我这里测试是没问题的，主库插入数据，从库就会同步。

建库脚本

我这里使用了3个数据库：db1, db2, db3

  • db1.sql

    /*
SQLyog Ultimate v12.09 (64 bit)
MySQL - 5.7.21-log : Database - db1
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`db1` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `db1`;

/*Table structure for table `company` */

DROP TABLE IF EXISTS `company`;

CREATE TABLE `company` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
`name` varchar(20) DEFAULT NULL COMMENT '名称',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

/*Table structure for table `customer` */

DROP TABLE IF EXISTS `customer`;

CREATE TABLE `customer` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
`truename` varchar(20) DEFAULT NULL COMMENT '姓名',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

/*Table structure for table `customer_addr` */

DROP TABLE IF EXISTS `customer_addr`;

CREATE TABLE `customer_addr` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`customer_id` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `customer_id` (`customer_id`),
CONSTRAINT `customer_addr_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

/*Table structure for table `employee` */

DROP TABLE IF EXISTS `employee`;

CREATE TABLE `employee` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`age` smallint(6) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

/*Table structure for table `goods` */

DROP TABLE IF EXISTS `goods`;

CREATE TABLE `goods` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`price` decimal(10,2) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

/*Table structure for table `hotnews` */

DROP TABLE IF EXISTS `hotnews`;

CREATE TABLE `hotnews` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`title` varchar(10) DEFAULT NULL,
`msg` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20002 DEFAULT CHARSET=utf8;

/*Table structure for table `order_items` */

DROP TABLE IF EXISTS `order_items`;

CREATE TABLE `order_items` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`order_id` bigint(20) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`customer_id` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `order_id` (`order_id`),
KEY `customer_id` (`customer_id`),
CONSTRAINT `order_items_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`),
CONSTRAINT `order_items_ibfk_2` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

/*Table structure for table `orders` */

DROP TABLE IF EXISTS `orders`;

CREATE TABLE `orders` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`customer_id` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `customer_id` (`customer_id`),
CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

/*Table structure for table `travelrecord` */

DROP TABLE IF EXISTS `travelrecord`;

CREATE TABLE `travelrecord` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`msg` varchar(20) DEFAULT NULL COMMENT '描述',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

  • db2

    /*
SQLyog Ultimate v12.09 (64 bit)
MySQL - 5.7.21-log : Database - db2
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`db2` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `db2`;

/*Table structure for table `company` */

DROP TABLE IF EXISTS `company`;

CREATE TABLE `company` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
`name` varchar(20) DEFAULT NULL COMMENT '名称',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

/*Table structure for table `customer` */

DROP TABLE IF EXISTS `customer`;

CREATE TABLE `customer` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
`truename` varchar(20) DEFAULT NULL COMMENT '姓名',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

/*Table structure for table `customer_addr` */

DROP TABLE IF EXISTS `customer_addr`;

CREATE TABLE `customer_addr` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`customer_id` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `customer_id` (`customer_id`),
CONSTRAINT `customer_addr_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

/*Table structure for table `employee` */

DROP TABLE IF EXISTS `employee`;

CREATE TABLE `employee` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`age` smallint(6) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

/*Table structure for table `goods` */

DROP TABLE IF EXISTS `goods`;

CREATE TABLE `goods` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`price` decimal(10,2) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

/*Table structure for table `hotnews` */

DROP TABLE IF EXISTS `hotnews`;

CREATE TABLE `hotnews` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`title` varchar(10) DEFAULT NULL,
`msg` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1028 DEFAULT CHARSET=utf8;

/*Table structure for table `order_items` */

DROP TABLE IF EXISTS `order_items`;

CREATE TABLE `order_items` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`order_id` bigint(20) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`customer_id` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `order_id` (`order_id`),
KEY `customer_id` (`customer_id`),
CONSTRAINT `order_items_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`),
CONSTRAINT `order_items_ibfk_2` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

/*Table structure for table `orders` */

DROP TABLE IF EXISTS `orders`;

CREATE TABLE `orders` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`customer_id` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `customer_id` (`customer_id`),
CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

/*Table structure for table `travelrecord` */

DROP TABLE IF EXISTS `travelrecord`;

CREATE TABLE `travelrecord` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`msg` varchar(20) DEFAULT NULL COMMENT '描述',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5010001 DEFAULT CHARSET=utf8;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

  • db3

    /*
SQLyog Ultimate v12.09 (64 bit)
MySQL - 5.7.21-log : Database - db3
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`db3` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `db3`;

/*Table structure for table `company` */

DROP TABLE IF EXISTS `company`;

CREATE TABLE `company` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
`name` varchar(20) DEFAULT NULL COMMENT '名称',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

/*Table structure for table `hotnews` */

DROP TABLE IF EXISTS `hotnews`;

CREATE TABLE `hotnews` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`title` varchar(10) DEFAULT NULL,
`msg` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1026 DEFAULT CHARSET=utf8;

/*Table structure for table `travelrecord` */

DROP TABLE IF EXISTS `travelrecord`;

CREATE TABLE `travelrecord` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`msg` varchar(20) DEFAULT NULL COMMENT '描述',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10010001 DEFAULT CHARSET=utf8;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;


作者：不敲代码的攻城狮
出处：https://www.cnblogs.com/leigq/
任何傻瓜都能写出计算机可以理解的代码。好的程序员能写出人能读懂的代码。

　
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/leigq/p/13406600.html