4

How to setup replication(Master/slave) in MySQL 5.6 while both servers are windows based? All the things i have fond are Linux based replication. kindly someone help who experienced replication with windows based server.

7

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.

  • Could you explain more detail about the file_name? Is it user defined? – LeonanCarvalho Dec 19 '18 at 18:38
  • @LeonanCarvalho You can find the file_name and file_pos by executing this query on the master SHOW MASTER STATUS; – Red Apr 2 at 13:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.