windows下mysql主从配置

1.安装主服务器
下载安装mysql，并修改my.ini配置如下(仅列出需要修改的部分)：

[client]
port=3306
[mysqld]
port=3306
basedir=F:/MySQL/3306/MySQL Server 5.7
datadir=F:/MySQL/3306/Data
log-bin=master-bin
log-bin-index=master-bin.index
server-id=1

2.安装从服务器
1).在服务中停止主服务，复制mysql文件夹，并修改my.ini配置如下(仅列出需要修改的部分)：

[client]
port=3307
[mysqld]
port=3307
basedir=F:/MySQL/3307/MySQL Server 5.7
datadir=F:/MySQL/3307/Data
log-bin=slave-bin
log-bin-index=slave-bin.index
server-id=2

备注：server-id必须不同，若是多台机器，可用ip作为id
2).以管理员身份打开cmd，进入从服务器bin目录，初始化data，执行

mysqld --initifize; 

3).安装mysql服务，执行

mysqld install mysql3307 --default-file="F:\MySQL\3307\MySQL Server 5.7\my.ini"

4).修改注册表信息，打开regedit，修改HKEY_LOCAL_MACHINE–>SYSTEM–>CurrentControlSet–>Services–>master，的ImagePath为

"F:\MySQL\3307\MySQL Server 5.7\bin\mysqld" --defaults-file="F:\MySQL\3307\MySQL Server 5.7\my.ini" MySQL3307

3.配置主从
1).打开cmd进入主服务器，设置授权，查看bin-log

F:\MySQL\3306\MySQL Server 5.7\bin>mysql -P3306 -uroot -p
Enter password: ******
mysql> grant replication slave on *.* to 'root'@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.09 sec)
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000002 |     1590 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

备注：一般不用root，可新建用户，分配对应权限，修改’%'为对应ip
2).配置从服务器，设置master的bin-log，启动服务器复制

F:\MySQL\3307\MySQL Server 5.7\bin>mysql -P3307 -uroot -p
Enter password: ******
mysql>change master to master_host='127.0.0.1',master_user='root',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=1590;
Query OK, 0 rows affected, 2 warnings (0.44 sec)
mysql>start slave; 
Query OK, 0 rows affected (0.13 sec)

执行start slave，若报错

ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

可先先执行reset slave，再执行change master。。。
3).检查从服务器状态

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000002
          Read_Master_Log_Pos: 599
               Relay_Log_File: DESKTOP-5EMO5NE-relay-bin.000003
                Relay_Log_Pos: 483
        Relay_Master_Log_File: master-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

确认Slave_IO_Running，Slave_SQL_Running状态为Yes
4.测试
登录主服务器，执行sql，查看从服务上的数据，保持一致即可