Windows server 2012 R2配置MySQL主从之详细步骤

---

title: Windows配置mysql主从复制
date: 2019-09-21 08:45:18
tags: MySQL

---

环境：

mysql 8.0.17

windows 10

1. 在同一台机器上安装两个mysql

安装略…

这里是采用不同的端口，分别为3306,3307

2. master配置：

1. 配置my.ini

my.ini配置文件：

[mysqld]
port = 3306					#master库端口号
server_id = 1				#服务器的唯一ID号，主从之间不能冲突，默认是1
log-bin=mysql-bin			#启动binlog 日志功能
# binlog-do-db=dongyi	    #要同步的数据库，默认所有库
binlog-ignore-db=mysql		
binlog-format = mixed       #日志的记录格式，默认是mixed，推荐也是这个

2. 配置给从库使用的用户

说明： mgw为我的用户名，这里mgw账号必须要有操作数据库的权限，ip是从库的ip，由于在同一台机器，所以写127.0.0.1

CREATE USER 'mgw'@'127.0.0.1' IDENTIFIED BY 'PASSWORD'; 
GRANT REPLICATION SLAVE ON *.* TO 'mgw'@'127.0.0.1'; 
FLUSH PRIVILEGES;  # 刷新权限，立即生效

SHOW MASTER STATUS; #查看主服务器的状态，并记录 File和Position字段，从服务器要使用到

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     2658 | dongyi       | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

3. slave配置

1.配置从库my.ini

my.ini配置文件：

[mysqld]
port = 3307	
server-id = 2  #服务器唯一ID号，不能重复
log-bin   = mysql-bin  
binlog-format = mixed  #可以不用配置，默认是mixed，在没有配置log-bin时，这个值并不生效
#从库的本地事务日志更新，默认从主库同步过来的操作记录并不写入从库的本地事务日志，
#会影响到我们在从库上做 事务日志 备份功能，此处开启
#如果不会在从库上做事务日志备份（增量备份）功能，建议不开启，减少磁盘IO
log-slave-updates = 1  

2.添加主库信息

CHANGE MASTER TO MASTER_HOST='127.0.0.1',MASTER_PORT=3306,MASTER_USER='mgw',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=2658;

# MASTER_LOG_FILE 和 MASTER_LOG_POS为master的日志文件和位置

3.启动从库复制功能

START SLAVE; #启动从复制功能

STOP SLAVE; #停止从复制功能的命令

RESET SLAVE; #重置从复制功能的配置，会清除 master.info 和 relay-log.info 两个文件

4.查看从库复制状态

SHOW SLAVE STATUS\G;

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: mgw
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 2658
               Relay_Log_File: MY-20190430BUDR-relay-bin.000003
                Relay_Log_Pos: 523
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 2658
              Relay_Log_Space: 741
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: eb57479b-a6d0-11e9-b4e1-507b9d99d971
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set (0.00 sec)

**注意：**这两项都为Yes, 即为配置成功

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

4.测试

master中：

mysql> create table mgw_test(
    -> id int not null primary key auto_increment,
    -> name varchar(10),
    -> age int);
Query OK, 0 rows affected (0.03 sec)

slave中：

mysql> show tables;
+------------------+
| Tables_in_dongyi |
+------------------+
| mgw_test         |
+------------------+
1 rows in set (0.00 sec)

采坑： 由于server_id 重复，导致Slave_IO_Running: No

设置全局server_id即可：

SET GLOBAL server_id=2;

微信公众号：一丁点技术