WIN10下MySQL主从详细配置

一、基本信息

主机信息：

127.0.0.1：3307

从机信息：

127.0.0.1：3308

二、主机配置

• 1、主机完整配置（my.ini）

[client]
port=3307
default-character-set = utf8

[mysql]
default-character-set = utf8

[mysqldump]
default-character-set = utf8

[mysqld]
port=3307
character_set_server = utf8
character-set-server = utf8
basedir = E:/mysql-5.7.10-master-slave/mysql-5.7.10-master
datadir = E:/mysql-5.7.10-master-slave/mysql-5.7.10-master/data

max_allowed_packet = 120M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

server_id=2
log-bin=mysql-bin-master
binlog_format=mixed
expire_logs_days=7
socket = E:/mysql-5.7.10-master-slave/mysql-5.7.10-master/data/mysql.sock

#忽略指定库
binlog_ignore_db = information_schema
binlog_ignore_db = mysql
binlog_ignore_db = performance_schema
binlog_ignore_db = sys

#innodb_buffer_pool_size = 2G
#innodb_log_file_size = 1G

#skip-grant-tables

 

• 2、建立账户并赋予主从权限并刷新

CREATE  USER  'songjy'@'127.0.0.%'  IDENTIFIED  BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'songjy'@'127.0.0.%' IDENTIFIED BY '123456';
FLUSH PRIVILEGES;

如下图：

• 3、查看主机状态：

show master status\G

记住其中上述File、Position值，在从库配置会用到！

三、从库配置

• 1、从机完整配置（my.ini）

[client]
port=3308
default-character-set = utf8

[mysql]
default-character-set = utf8

[mysqldump]
default-character-set = utf8

[mysqld]
port=3308
character_set_server = utf8
character-set-server = utf8
basedir = E:/mysql-5.7.10-master-slave/mysql-5.7.10-slave
datadir = E:/mysql-5.7.10-master-slave/mysql-5.7.10-slave/data

max_allowed_packet = 120M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

server_id=3

log-bin=mysql-bin-slave
expire_logs_days=7
socket = E:/mysql-5.7.10-master-slave/mysql-5.7.10-slave/data/mysql.sock

#从库设置为只读模式
read-only=1

#忽略指定库
binlog_ignore_db = information_schema
binlog_ignore_db = mysql
binlog_ignore_db = performance_schema
binlog_ignore_db = sys


relay_log_recovery=1
relay-log=mysqld-relay-bin

#innodb_buffer_pool_size = 2G
#innodb_log_file_size = 1G

#skip-grant-tables

• 2、关闭同步线程

STOP SLAVE;

• 3、设置同步账号信息

CHANGE MASTER TO MASTER_HOST = '127.0.0.1',
MASTER_PORT = 3307,
MASTER_USER = 'songjy',
MASTER_PASSWORD = '123456',
MASTER_LOG_FILE = 'mysql-bin-master.000001',
MASTER_LOG_POS = 154 ;

• 4、启动同步线程

START SLAVE;

如下图：

上述MASTER_LOG_FILE和MASTER_LOG_POS配置项值来自主库状态显示的值

• 5、查看从机状态

show slave status\G

当看到红框中所示状态都是Yes，说明主从配置成功

四、示例测试SQL

在主库中执行如下SQL后，可以在从库中查看效果，会看到主从库数据一致！

CREATE DATABASE `test` CHARACTER SET utf8mb4;

CREATE TABLE `test`.`tb_test` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `user_name` VARCHAR (64) NOT NULL COMMENT '用户名',
  `pass_word` VARCHAR (32) NOT NULL COMMENT '密码',
  PRIMARY KEY (`id`)
) CHARSET = utf8mb4 COMMENT = '用户表' ;

INSERT INTO(`user_name`,`pass_word`) VALUES('songjy','123456');

 

五、主意事项

数据的复制是顺序的，任何在从机上失败都会导致主从配置失效,所以要时刻关注Slave_IO_Running及Slave_SQL_Running的状态,其中有一个配置不是YES，则必须手动处理。目前市面上很少会用这种主从模式也是主要原因之一，建议可以采用ShardingSphere的广播模式代替。