MYSQL relication -- unable to connect to master

SiemensSEN
SiemensSEN used Ask the Experts™
on
Hello ,
  I am setting up mysql replication between a master and slave and I get the error "error connecting to master".

MASTER > mysql> show grants for replicator;
+-----------------------------------------------------------------------------------------------------------------------+
| Grants for replicator@%                                                                                               |
+-----------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%' IDENTIFIED BY PASSWORD '*526C0DBFF12E548685FD9C696D0F117D43E4' |
+-----------------------------------------------------------------------------------------------------------------------+

SLAVE

 mysql -h142.25.0.14 -ureplicator -ppass
----------------------------------------------------------------------
ERROR

ter_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1045
                Last_IO_Error: error connecting to master 'replicator@myserver.dev.global-intra.net:3306' - retry-time: 60  retries: 86400
               Last_SQL_Errno: 0


Thanks for the help
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Make sure your master has replicator user with proper privileges and master is not using firewall/iptables to stop access to slave. Did you try accessing master mysql server using command line from slave server?
theGhost_k8Database Consultant

Commented:
On Master:
Is the port open?
Do you have local-ip bound or skip-networking uncommented in my.cnf?
If this is new user created have u fired: FLUSH PRIVILEGES.

Can you directly connect master from slave using -h::--

mysql -uuser -ppass -h<MASTER-HOST-NAME>
theGhost_k8Database Consultant

Commented:
Provided different Server-ids??
Sit back - relax - verify steps one by one...
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Author

Commented:
Thanks for your response

Yes the port us open 3306

Master  cnf snippet
#skip-networking
bind-address = 172.25.0.14
server-id       =2
------------------------------------------------
SLAVE
#skip-networking
bind-address = 172.20.32.86
server-id       = 1
--------------------------------------------------------

Yes. I execute the flush privileges
-----------------------------------------------------------------

GRANT statement

MASTER
mysql> show grants for replicator_ath;
+---------------------------------------------------------------------------------------------------------------------------+
| Grants for replicator_ath@%                                                                                               |
+---------------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'replicator_ath'@'%' IDENTIFIED BY PASSWORD '*526C08913DBFF12E548685FD9C696D0F117D43E4' |
+---------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

----------------------------------------------------------------------------------------------------------------

Cannot connect to the master from the slave

mysql -h172.25.0.14 -ureplicator_ath -pmypassword
ERROR 1045 (28000): Access denied for user 'replicator_ath'@'usbrws02' (using password: YES)
------------------------------------------------------------------------------------------------------------------------------

I excute the followng command from the slave

mysql> stop slave
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> reset slave;
Query OK, 0 rows affected (0.03 sec)

mysql> CHANGE MASTER TO
    ->  MASTER_HOST='172.25.0.14',
    ->  MASTER_USER='replicator',
    ->  MASTER_PASSWORD='mypassword',
    ->  MASTER_LOG_FILE='mysql-bin.000001',
    ->  MASTER_LOG_POS =107;
Query OK, 0 rows affected (0.03 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)


Here is the output
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 172.25.0.14
                  Master_User: replicator
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 107
               Relay_Log_File: usbrws02-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 107
              Relay_Log_Space: 107
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1045
                Last_IO_Error: error connecting to master 'replicator@172.25.0.14:3306' - retry-time: 60  retries: 86400
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 0
1 row in set (0.00 sec)

Author

Commented:
Sorry the screen print above for the slave status is not correct.

Here is the correct one

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 172.25.0.14
                  Master_User: replicator_ath
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 107
               Relay_Log_File: usbrws02-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 107
              Relay_Log_Space: 107
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1045
                Last_IO_Error: error connecting to master 'replicator_ath@172.25.0.14:3306' - retry-time: 60  retries: 86400
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 0
1 row in set (0.00 sec)
Did you issue a "flush privileges;" command to ensure the password
was activated by MySQL?

Also your example of setting the password for the replicator account
looks odd to me for what you use as the password in that I am
thinking you may not be using the correct password.

You do know that in your example, your password for the account
will actually be
'*526C0DBFF12E548685FD9C696D0F117D43E4', not some decrypted
version of it.

If you want the password to be say "MyTrickyPswd", then the command
would be:

GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%' IDENTIFIED BY PASSWORD
'MyTrickyPswd'


Author

Commented:
I did "flush privileges"

I did use the GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%' IDENTIFIED BY PASSWORD
'MyTrickyPswd' command . However, this information that I include is the "show grants" command for the user ID. MYSQL encript the password when it saves it


I am able login to the master with the replicator password. Just not able to connect to the master from the slave.

Thanks
You did everything correct therefore by MySQL's rules. It appears something on the server
is refusing you access.

Here is a Unix command you can try on the slave server:

telnet xyz 3306

where xyz is the IP address of the master server and 3306 is the port
number for the MySQL server.

If you have proper connectivity to the port, it should tell you that you can
press ^[ (which means press Control and [ key together) to exit.
If it does not offer that, then it is an indication of something like a firewall
preventing access on that port to the master server.
(In which case you need to press control-C a few times to get out of
the telnet connection mode).
Database Consultant
Commented:
Until you succeed in
mysql -h172.25.0.14 -ureplicator_ath -pmypassword
replication wont start :)

did you check both master and slave's my.cnf for:
skip-networking
bind-address
Make sure both are commented! If not you will have to change it and restart mysql.
SiemensSEN,

What was the issue?

Author

Commented:
There was a few issue:

1: The port was not configure properly
2: The grants for the mySQl user was conflictiing with another "myrepluser@localhost and mReplUser@''.

After network administrator fix the port issue and I removed the mysql@'' then my replication started to work

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial