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
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!