Solved

MySQL Replication Setup -  master / slave connectivity issue

Posted on 2016-09-05
10
28 Views
Last Modified: 2016-09-10
Hello,

I have configured two standalone MySQL instances mysqlmaster (host ip 192.168.159.141) & mysqlslave (host ip 192.168.159.142) on my VMware virtual machine running CentOS. I have created users 'siva' on both machines who can connect from any host ip (%). Granted ALL permissions for this user on both instances .

But my remote connectivity fails with following error,

[root@mysqlmaster siva]# mysql -u siva -p -h 192.168.159.142
Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.159.142' (113)
[root@mysqlmaster siva]#

Open in new window

'mysqlmaster' configuration
[mysqld]
bind-address = *
port=3306
server-id=1
validate_password_policy=LOW
log_bin=mysql-bin.log
binlog-do-db = repl01

'mysqlslave' configuration
[mysqld]
bind-address = *
port=3306
server-id=2
validate_password_policy=LOW

Connectivity tests i have performed ,
1) PING test from both instances is successful.
2) Telnet on 3306 fails on both instances to other instance.

Not sure if this can be done if the hosts are standalone & not under any domain controller.

Thanks
0
Comment
Question by:Siva Dasari
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 78

Expert Comment

by:arnold
ID: 41785479
Seems fine , did you run the command on the slave to establish the link, though you are missing info on which databases are to be replicated, MySQL db is not to be replicated.

On the master, show master status, on the slave show slave status.....
The database repl01 exists on the slave?
0
 

Author Comment

by:Siva Dasari
ID: 41785482
I haven't created repl01 DB on slave yet. Firstly I'm trying to establish remote connection to make sure master can reach slave & vice versa. I didn't proceed further because the remote connection itself fails ...
0
 
LVL 78

Expert Comment

by:arnold
ID: 41785485
The db must exist, the connection is from the slave to the master, it is a pull setup, not a push setup.
Are you following a specific guide to setup master/slave?

What you should consider doing adding the index/increment settings just in the event you choose to switch the master/slave to master/master where the insertion can occur on either, while the replication is in both directions.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 78

Expert Comment

by:arnold
ID: 41785487
See replication write up, http://dev.mysql.com/doc/refman/5.5/en/replication-howto.html

auto_increment_increment=10
auto_increment_offset=starting value from 1-9
This will enable to have a 9 node circular replication master/master where an insert row in any will always have a unique identifier eliminating collision; two records having the same auto_incremented counter value.
0
 

Author Comment

by:Siva Dasari
ID: 41785488
Ok, let me put my question simple. Forget about replication setup for now.
Why I'm unable to make remote call / connection from instanceA to instanceB or vice versa? I know there is something blocking but not sure what it is though ...
0
 
LVL 78

Assisted Solution

by:arnold
arnold earned 500 total points
ID: 41785491
When you created the slva user, did you specify a hostname from which it San connect?

Grant replication on repl01.* to 'slva'@'%' ... This will allow slva to connect from any host.

If instead of '%' you used an IP address, slva will only be allowed to connect from that IP address/host provided if a database is part of the setting, it must be part of the command,

Try this instead of using MySQL,
telnet 192.168.159.x 3306
If you do not get a connection established, check firewall rules on each host, to make sure port 3306 incoming connections are allowed....
0
 

Author Comment

by:Siva Dasari
ID: 41785494
As I stated in my initial question the user is created with host as % so the connection can be accepted from any host & granted all permissions to the user. I made the telnet tests from A to B & B to A on 3306 port but the results are negative.

As you mention I will review the firewall settings & see if I can find something ...
0
 
LVL 78

Assisted Solution

by:arnold
arnold earned 500 total points
ID: 41785524
Which Linux distro are you using, sfw, iptables -L, ......... Will .......
0
 

Accepted Solution

by:
Siva Dasari earned 0 total points
ID: 41785544
Thanks Arnold for your guidance on this issue.

Issue is now resolved after opening the port 3306 on which mysql is configured to listen & it is done on both the instances A & B ... below are the steps I have executed.
[siva@mysqlslave ~]$ sudo firewall-cmd --zone=public --add-port=3306/tcp --permanent
[sudo] password for siva:
success
[siva@mysqlslave ~]$ sudo firewall-cmd --reload
success
[siva@mysqlslave ~]$ firewall-cmd --list-all
public (default, active)
  interfaces: eno16777736
  sources:
  services: dhcpv6-client ssh
  [b]ports: 3306/tcp[/b]
  masquerade: no
  forward-ports:
  icmp-blocks:
  rich rules:

Open in new window

0
 

Author Closing Comment

by:Siva Dasari
ID: 41792504
The ports are blocked on both the instances A & B. I have opened them in the firewall rules which resolved the issue.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Teach the user how to configure vSphere clusters to support the VMware FT feature Open vSphere Web Client: Verify vSphere HA is enabled: Verify netowrking for vMotion and FT Logging is in place or create it: Turn On FT for a virtual machine: Verify …
Advanced tutorial on how to run the esxtop command to capture a batch file in csv format in order to export the file and use it for performance analysis. He demonstrates how to download the file using a vSphere web client (or vSphere client) and exp…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question