Table Level Replication in MySQL on Windows OS

Hello guys,

Hope you are going great. 

In this article, we are going to talk about configuring MySQL replication between two servers. Here there will be one master and one slave server. It’s simple setup there are basically 6 major steps which are below…

Step 1 -->

============================================================================

/*Take dump of the tables which we are planning to replicate*/

 

mysqldump -u mysqlbk -p --master-data=2 -f -vv DB_Name Table_1 Table_2  >D:\DB_Name_tablesdump.sql

 

Step 2 -->

===============================================================================

/*Once backup starts, please note-down master position from the dump file...*/

/*

-- Position to start replication or point-in-time recovery from

-- CHANGE MASTER TO MASTER_LOG_FILE='CLTSQLPW01-bin.000040', MASTER_LOG_POS=146261;

*/

 

Step 3 -->

===============================================================================

/*

Restore ON SLAVE SERVER

Copy the dump file on slave and restore the dump. */

                                                                                                                                 

mysql -u mysqlbk -p DB_Name -vv < D:\DB_Name_tablesdump.sql

 

Step 4

===============================================================================

/* Slave user creation. Create the user on master server */

 

mysql> create user 'clt_exslave'@'10.10.19.13' IDENTIFIED by "**********";

mysql> Grant replication slave on DB_Name.* to 'clt_exslave'@'10.10.19.13';

mysql> flush PRIVILEGES;

 

 

Step 5 -->

===============================================================================

/*Change my.cnf in SLAVE server with the listed tables.*/

 

1.

#Start tables for replication

replicate-do-table      = DB_Name.Table_1

replicate-do-table      = DB_Name.Table_2

 

--Note for sample we have copied only two table here.

 

2.

default-authentication-plugin=mysql_native_password

3.

server-id=100 /*make sure server id must be different on both the servers.*/

server-id=101 /*make sure server id must be different on both the servers.*/

Restart MySQL on SLAVE Server.

 

Step 6 -->

===============================================================================

/*Run below command on SLAVE server*/

mysql>  CHANGE REPLICATION SOURCE TO SOURCE_HOST='10.10.19.5',SOURCE_USER='clt_exslave',SOURCE_PASSWORD='********',SOURCE_LOG_FILE='CLTSQLPW01-bin.000040',SOURCE_LOG_POS=146261,SOURCE_PORT=3306;

mysql> START REPLICA;

mysql> SHOW REPLICA STATUS\G

 

Its done!!!!!

===============================================================================