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 🙂

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