title: Windows configures mysql master-slave replication
date: 2019-09-21 08:45:18
tags: MySQL

1. Install two mysql on the same machine

Install slightly...

Here are different ports, 33063307

2. master configuration:

1. Configure my.ini

my.ini profile:

[mysqld]
port = 3306					#master library port number
server_id = 1				#The unique ID number of the server. There is no conflict between the master and slave. The default is 1
log-bin=mysql-bin			#Start binlog logging function
# binlog-do-db=dongyi	    #Database to synchronize, all libraries are default
binlog-ignore-db=mysql		
binlog-format = mixed       #The log record format is mixed by default. This is also recommended

2. Configure it to users from the library

Note: mgw is my user name. Here, the mgw account must have the permission to operate the database. ip is the ip of the slave database. Because it is on the same machine, write 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;  # Refresh permissions, effective now

SHOW MASTER STATUS; #View the status of the primary server and record the File and Position fields from the server to the

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 configuration

1. Configure the slave library my.ini

my.ini profile:

[mysqld]
port = 3307	
server-id = 2  #Server unique ID number, cannot be duplicate
log-bin   = mysql-bin  
binlog-format = mixed  #No configuration is required. The default value is mixed. When log bin is not configured, this value does not take effect
#The local transaction log of the slave database is updated. By default, the operation records synchronized from the master database are not written to the local transaction log of the slave database,
#It will affect our transaction log backup function on the slave database. Open here
#If the transaction log backup (incremental backup) function is not available on the slave database, it is recommended not to enable it to reduce disk IO
log-slave-updates = 1  

2. Add main database information

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 and master log POS are the log files and locations of master

3. Start the copy from library function

START SLAVE; #Start copy from feature

STOP SLAVE; #Command to stop copying from

RESET SLAVE; #Resetting the configuration of the slave copy function will clear the master.info and relay-log.info files

4. View copy from library status

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)

**Note: * * both are Yes, which means the configuration is successful

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

4. test

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)

Mining pit: slave IO running due to duplicate server ID:

Set the global server ID to:

SET GLOBAL server_id=2;

WeChat public address: a little bit of Technology