Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I had to stop the slave server to test something. After I started the server again there is a problem with replication on MySQL server

On the problematic server

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: servera
                  Master_User: replica
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: servera-bin.000024
          Read_Master_Log_Pos: 808459481
               Relay_Log_File: serverb-relay-bin.000071
                Relay_Log_Pos: 4
        Relay_Master_Log_File: servera-bin.000024
             Slave_IO_Running: No
            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: 808459481
              Relay_Log_Space: 106
              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: 1129
                Last_IO_Error: error connecting to master 'replica@servera:3306' - retry-time: 60  retries: 86400
               Last_SQL_Errno: 0
               Last_SQL_Error:

on the problematic server:

SELECT user, host FROM mysql.user WHERE Repl_slave_priv = 'Y';
+---------+-----------+
| user    | host      |
+---------+-----------+
| root    | localhost |
| root    | serverb   |
| root    | 127.0.0.1 |
| replica | servera   |
| replica | serverb   |
+---------+-----------+

on the main server:

SELECT user, host FROM mysql.user WHERE Repl_slave_priv = 'Y';
+---------+-----------+
| user    | host      |
+---------+-----------+
| root    | localhost |
| root    | servera   |
| root    | 127.0.0.1 |
| replica | servera   |
| replica | serverb   |
+---------+-----------+

according to what I've read, there is a need to execute the following command om the main server:

mysql> FLUSH HOSTS; 

What will happen then? if there is any application connected to it - will it disconnect it also?

share|improve this question
    
There should be an error message in your error log telling you why the IO thread disconnected. Please locate that error message. If you cannot find the error message, run STOP SLAVE; START SLAVE;. If the Slave_IO_Running still says not, a new error message will appear in the error log. Please find it and post in your question. –  RolandoMySQLDBA Mar 20 '13 at 15:27
    
I cannot find the log file at all :( –  user1977050 Mar 20 '13 at 15:40
    
Please run SHOW VARIABLES LIKE 'log_error'; What does it say ? –  RolandoMySQLDBA Mar 20 '13 at 18:17
    
from the doc dev.mysql.com/doc/refman/5.0/en/flush.html - basically all hosts are maintained on mysql server when you flush all hosts are deleted from the list. in your case looks like you NEED to do it. –  silviud Apr 19 '13 at 23:29
    
do you have skip-name-resolve in your my.ini file? is your server have a static IP address? are you sure that that IP address is not shared by any other machines on the same network? –  Mike Sep 23 at 23:55

2 Answers 2

If your error is

ERROR 1129 (HY000): Host 'serverb' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

you can run a FLUSH HOSTS on master, it will flush the list of blocked host which exceed the number or fail connections attempts to your mysql master

It won't affect any running connection, it will only clean the list of blocked hosts.

share|improve this answer
    
i don't have such lines in the configurationfile –  user1977050 Mar 20 '13 at 15:53
    
try to connect from slave to master using the mysql command line tool, and see what is the error code. –  altmannmarcelo Mar 20 '13 at 15:56
    
[admin@serverb ~]$ mysql -p*** -hservera –uroot ERROR 1129 (HY000): Host 'serverb' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts' –  user1977050 Mar 20 '13 at 16:00
    
mysql> FLUSH HOSTS; Query OK, 0 rows affected (0.00 sec) –  user1977050 Mar 20 '13 at 16:27
    
try to connect again from command line from slave to master using the replication user. –  altmannmarcelo Mar 20 '13 at 17:17

Some Possibilities

It's possibly a permissions problem on the master allowing access from your replication client. From the client shell, attempt to ping the master:

ping servera

Next, eliminate name resolution problem on your master by pinging the client:

ping serverb

If you can ping successfully, attempt to connect using the MySQL client:

mysql -vvv -u replica -h servera -p

If you are rejected using the username and password, then you have a permissions problem. Pay close attention to the error message output.

Another possibility is SELinux. If you have not set up SELinux properly, then make sure you:

setenforce 0
# then restart mysqld
share|improve this answer
    
I've added a comment I've added a comment about connecting: [admin@serverb ~]$ mysql -p*** -hservera –uroot ERROR 1129 (HY000): Host 'serverb' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts' –  user1977050 Mar 20 '13 at 15:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.