asked on

MySQL Replication Setup - master / slave connectivity issue

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

arnold

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?

Siva Dasari

ASKER

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

arnold

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.

arnold

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.

Siva Dasari

ASKER

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

SOLUTION

arnold

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Siva Dasari

ASKER

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

SOLUTION

arnold

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Siva Dasari

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Siva Dasari

ASKER

The ports are blocked on both the instances A & B. I have opened them in the firewall rules which resolved the issue.