MySql – set the.cnf location at server start

Two servers are not allowed to have the same server_id. Also, If you omit --server-id from a master, the default ID is 0, in which case a master refuses connections from all slaves, and a slave refuses to connect to a master.

For more information, see Section 15.1.1.2, “Setting the Replication Slave Configuration”.

Even if both servers have different server_id values, please make sure the new master has binary logging enabled.

Replication setup on a windows server is equivalent to doing it in other systems: The official manual provides you a detailed guide. In short:

1. Configure on both servers a different server-id, and activate the binary log on the master. If it is not already done, the most typical way to do that is modify the my.ini file and restart the service (net restart mysql):

   [mysqld]
   log-bin
   server-id = 1
   

   Make also sure that the master is accesible from the slave node and it is not bound to localhost (bind-address = 127.0.0.1)

2. Create a user on the master for the slave to connect with REPLICATION SLAVE privileges (use MySQL-command line for this):

   mysql master> CREATE USER 'user_name'@'ip.of.the.slave' IDENTIFIED BY 'password';
   mysql master> GRANT REPLICATION SLAVE ON *.* TO 'user_name'@'ip.of.the.slave';        
   

3. Make a backup of the master and apply it to the slave. There are several ways to do that (stopping the server and copying the files works), but the easiest would be to se mysqldump (navigate to the bin directory of your sql-server installation (f.e. C:\Program Files\MySQL\MySQL Server 5.7\bin). Also you may need to provide credentials by appending this to both commands: -uroot -pPassword)

   (on the master windows command line) mysqldump --all-databases --master-data > dump.sql
   (on the slave windows command line ) mysql < dump.sql
   

4. At the beginning the dump.sql you will find a line like this:

   -- CHANGE MASTER TO MASTER_LOG_FILE = 'file_name', MASTER_LOG_POS = file_pos;
   

   Use those parameters to configure the replication on the slave:

   mysql slave> CHANGE MASTER TO MASTER_HOST = 'ip.of.the.master',
                    MASTER_USER = 'user_name_you_just_created',
                    MASTER_PASSWORD = 'password_you_just_defined',
                    MASTER_LOG_FILE = 'file_name',
                    MASTER_LOG_POS = file_pos;
   

   (beware, no quotes for integers, like MASTER_LOG_POS).

5. If everything is ok until now, just start replication on the slave:

   mysql slave> START SLAVE;
   

   To check that it is running, execute:

   mysql slave> SHOW SLAVE STATUS;
   

   And look for:

      Slave_IO_Running: Yes
     Slave_SQL_Running: Yes
   

GTID-based replication is a bit different, and I have not mentioned it.