mysql replication - slave can't connect to master

mysql 5.7.18 (both master and slave)

error in slave status

Last_IO_Error: Master command COM_REGISTER_SLAVE failed: Access denied for user 'repl'@'xxx.xxx.xxx.xxx' (using password: YES) (Errno: 1045)

error on master (error.log)

2017-05-29T16:40:39.718290Z 213 [Note] Aborted connection 213 to db: 'unconnected' user: 'repl' host: 'xxx.xxx.xxx.xxx' (Got an error reading communication packets)

connecting from slave to master via mysql client

But if I login from the slave servers command line like this there is no error and the slave connects to the master perfectly. So there must be something in the master-slave configuration thats not right

mysql -h xxx.xxx.xxx.xxx -u repl -pxxxxxxxxxxxx
mysql> show grants;
+--------------------------------------------+
| Grants for repl@xxx.xxx.xxx.xxx            |
+--------------------------------------------+
| GRANT USAGE ON *.* TO 'repl'@'xxx.xxx.xxx.xxx' |
+--------------------------------------------+
1 row in set (0.00 sec)

create replication user on master

CREATE USER 'repl'@'xxx.xxx.xxx.xxx' IDENTIFIED BY 'xxxxx';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'xxx.xxx.xxx.xxx';
FLUSH PRIVILEGES;

master my.cnf

```
[mysqld]

# GENERAL #
user = mysql
port = 3306
default_storage_engine = InnoDB
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock

# SAFETY #
max_allowed_packet = 16M
max_connect_errors = 1000000
skip_name_resolve
sql_mode = STRICT_ALL_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO
sysdate_is_now = 1
symbolic_links = 0

# DATA STORAGE #
datadir = /var/lib/mysql/

# BINARY LOGGING #
server_id = 1
log_bin = /var/lib/mysql/mysql-bin
log_slave_updates
expi

Clark you mentioned USAGE only as the grants applied you also need REPLICATION SLAVE.

mysql> show grants;
+--------------------------------------------+
| Grants for repl@xxx.xxx.xxx.xxx            |
+--------------------------------------------+
| GRANT USAGE ON *.* TO 'repl'@'xxx.xxx.xxx.xxx' |
+--------------------------------------------+
1 row in set (0.00 sec)

If usage means you can only connect to the DB Server and not anything else. Whereas in this case REPLICATION SLAVE will require to read and pull binlog events from Master.

To Fix this:

On Master Execute:

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'xxx.xxx.xxx.xxx' identified by 'xxxxx';

flush privileges;

[difference this time is you are granting with the password for REPLICATION SLAVE GRANT]

Show Grants:

If you do show grants; after you give GRANT REPLICATION command. You should get below grants from same slave host via CLI.

mysql> show grants;
+-------------------------------------------------------------------------------+
| Grants for repl@xxx.xxx.xxx.xxx                                                              |
+-------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO repl@xxx.xxx.xxx.xxx  IDENTIFIED BY PASSWORD  |
+-------------------------------------------------------------------------------+
1 row in set (0.03 sec)

mysql>

StackExchange Database Administrators Q#174842, answer score: 9