Mysql Replication setup for master to slave on windows

Mysql Replication setup for  master to slave on windows

Description :

You can back up your MySQL database using the built-in dump tool, but the second you do that backup is out of date. Since so many companies and web-based tools rely heavily on databases, it’s crucial that those databases are as up-to-date as possible. One very simple solution is database replication, which keeps a real-time copy of the database on a remote server. With this in place, if something happens to the primary database, it will be much easier to get your database back up and running with current information.

The are easy steps to Mysql Replication setup for master to slave on windows

What we need to setup correctly replication on windows are :

1 : Two correctly configured MySQL servers.

2 : Root access and access to the database administrator on both servers.

3 : Ability to work from the command line.

The setup will use two machine master and slave.

First we start setup from master configuration

Suppose 192.168.1.34 is a master and 192.168.1.35 is slave

On Master Machine Configuration

Step 1 : Open the my.ini/my.cnf file and add the following lines below [mysqld]

log-bin=mysql-bin

server-id=1

innodb_flush_log_at_trx_commit=1

sync_binlog=1

binlog-do-db=database_to_be_replicated

Save the my.ini/my.cnf file and restart the mysql server on master machine

For linux : /etc/inid.t/mysqld restart

For windows you can restart from services by control panel.

Step 2 : Setup a user for replication privileges.

Log on to master with root privileges with command line

Mysql –u root –pxxxx

CREATE USER ‘replication_user’@ ‘X.X.X.X’ IDENTIFIED BY ‘password’;

GRANT REPLICATION SLAVE ON *.* TO ‘replication_user’@'X.X.X.X’ IDENTIFIED BY ‘password’;

Replace the X.X.X.X with ip address of slave machine.

Step 3 : To unlock the database do the following commands

FLUSH TABLES WITH READ LOCK;

Now unlock the tables

Show master status to user later

UNLOCK TABLES;

On Slave Configuration :

Mysql Replication setup for  master to slave on windows are as follows

Step 4 : Open the my.ini/my.cnf file from slave server

Add the following lines below [mysqld] line

server-id=2

Step 5 : Go to the master and issue the following command as

SHOW MASTER STATUS; 

mysql> show master status;

+------------------+-----------+--------------+------------------+

| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+-----------+--------------+------------------+

| mysql-bin.000001 | 346934211 |              |                  |

+------------------+-----------+--------------+------------------+

1 row in set (0.00 sec)

Step 6 : Again go to the slave machine and issue the following command as

STOP SLAVE;

Step 7 : You must run the following command as

CHANGE MASTER TO MASTER_HOST=’IP_ADDRESS_OF_MASTER’, MASTER_USER=’USER’, MASTER_PASSWORD=’USER_PASSWORD’, MASTER_LOG_FILE=’mysql-bin.007′, MASTER_LOG_POS=NUMBER;

Where the following applies:

IP_ADDRESS_OF_MASTER is the actual IP address of the Master

USER is the MySQL admin user

USER_PASSWORD is the password for the USER

NUMBER is the Position Number reported from the SHOW MASTER STATUS command.

For Example

CHANGE MASTER TO 

MASTER_HOST=’192.168.1.34’,

MASTER_USER='replication_user',

MASTER_PASSWORD='password', 

MASTER_PORT=3306,

MASTER_LOG_FILE='mysql-bin.000001',

MASTER_LOG_POS=346934211;

Save the file and exit.

Step 8 : Restart the Slave by issuing the command 

SLAVE START; 

Now you can follow these steps to make sure everything is working fine.

SHOW SLAVE STATUS;.

mysql> show slave status \G

*************************** 1. row *********************

               Slave_IO_State: Waiting for master to sen

                  Master_Host: 192.168.1.34

                  Master_User: replication_user

                  Master_Port: 3306

                Connect_Retry: 10

              Master_Log_File: mysql-bin.000001

          Read_Master_Log_Pos: 346934211

               Relay_Log_File: WIN-THQO1NKG27F-relay-bin

                Relay_Log_Pos: 785

        Relay_Master_Log_File: mysql-bin.000002

             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: 1146

In the above output if you see the following

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

Then all is fine. Replicated MySQL database. 

Congratulations!