Replication on MySQL server

Question

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?

Answer 1

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.

Answer 2

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