How To Setup Streaming Replication In MySQL – Slave Node


In my last post, you read about configuring the master node. Here, you will learn how you can configure slave for streaming replication. Lets start this tutorial 🙂

Create the same database as on master.

As you know, our example slave node has IP 192.168.0.32 & that will run our replicated database. So, create the same database here.

mysql> create database your_db_name;

Import the dump file to populate data.

Now, exit the mysql prompt & import the dump file that you copied from master node. This will populate our database with the data from master.

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysql -uroot -p asset < C:\your_db_name.sql
Enter password: *****

Edit my.ini or my.cnf file.

Here we will edit the configuration file to indicate which one is our master & similar settings. Enter below details to your file. Read the file carefully & make changes accordingly. datadir is optional. Save the file & restart MySQL. On Windows, you need to go to Services & restart MySQL service. After that, you will see the data populated from dump file.

[mysqld]
server-id=2
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
datadir=C:\Program Files\MySQL\MySQL Server 5.6\data
replicate-do-db=your_db_name
master-host=192.168.0.31
master-user=repl_user
master-password=PASSWORD

Stop slave.

Login to your MySQL & issue below to stop the slave.

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

Configure slave to start replication from master. 

Issue below query to start the replication process. Please change the values accordingly. In the last 2 properties, use the values that you noted down while configuring master. Its the File & Value property that you need to enter from that output. After this, start your slave.

mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.0.31',
-> MASTER_USER='repl_user',
-> MASTER_PASSWORD='PASSWORD',
-> MASTER_LOG_FILE='mysql-bin.000003',
-> MASTER_LOG_POS=120;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

Now you are done 🙂 Just create new tables or add record in your master database. All that will be mirrored to your slave database 🙂 Check it for yourself!

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s