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!

 

How To Setup Streaming Replication In MySQL – Master Node


Hey there 🙂 I am back today with a post on how to setup Streaming Replication In MySQL. Streaming Replication is a process in which a databases(s) is/are mirrored between 2 0r more servers. In simple language, we have a MySQL master node running the database which is in sync with one or more slaves. If master node fails or crashes, one of the slaves takes over & database is made available. Any data that is written to master is automatically mirrored to its slaves. Sounds interesting? 😉 Here is how to do it. I have performed this on Windows servers because of my application. But same steps apply to Linux servers as well. Only the file location will differ. I am explaining this taking into consideration that a database already exists on master. Lets start this tutorial 🙂

Lab Description : –

  • Master node – Windows Server running MySQL 5.6.17. IP Address 192.168.0.31
  • Slave node – Windows Server running MySQL 5.6.17. IP Address 192.168.0.32

Please ensure port 3306(default) or the port you specify in MySQL configuration needs to be opened so as to enable communication between master & slave. I have to do it on my Windows servers.

Steps to be performed : –

Edit my.ini or my.cnf file.

Add below section to your existing file. Note that server-id has to be 1 for master. Change datadir to the location of your MySQL data directory. binlog-do-db has to be set to the database that you need to replicate. After saving the file, restart MySQL.

Please note that all these properties are thoroughly explained in my.ini or my.cnf files. Read that carefully and then make changes.

[replication]
server-id = 1
datadir=e:/wamp/bin/mysql/mysql5.6.17/data
log-bin=mysql-bin
binlog-do-db=your_db_name
innodb_flush_log_at_trx_commit=1
sync_binlog=1

Create replication user & grant access.

Issue below queries on master node mysql prompt. Note that the IP mentioned is of slave node.

mysql> create USER repl_user@192.168.0.32;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO repl_user@192.168.0.32 IDENTIFIED BY 'PASSWORD';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Flush tables with read lock.

Issue below sql queries on your already open mysql prompt.

mysql> use your_db_name;

Database changed
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 120      |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Note down these values i.e “File name” & “Position”. You will need these to configure slave.

Dump the database data to a file.

Now, its time to dump all the database data to a file. This file will be used to generate data on slave.

E:\wamp\bin\mysql\mysql5.6.17\bin>mysqldump -uroot -p --optyour_db_name >your_db_name.sql
Enter password: *****

Copy this file to the slave node. Use below PowerShell command for Windows 🙂

PS C:\Windows\system32> copy E:\wamp\bin\mysql\mysql5.6.17\bin\your_db_name.sql \\192.168.0.32\c$\your_db_name.sql

Unlock the tables. Its time to unlock the tables.

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> quit;
Bye

Here, we are done with configuration of master node. I will explain slave configuration in my next post. Stay tuned 🙂