MySQL Replication – Slave Can’t Connect to Master

MySQLreplication

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
expire_logs_days               = 14
sync_binlog                    = 1
log_bin_trust_function_creators= 1
binlog_format                  = ROW

# REPLICATION #
gtid_mode                      = ON
enforce_gtid_consistency

# CACHES AND LIMITS #
tmp_table_size                 = 64M
max_heap_table_size            = 64M
query_cache_type               = 0
query_cache_size               = 0
max_connections                = 500
thread_cache_size              = 50
open_files_limit               = 65535
table_definition_cache         = 4096
table_open_cache               = 4096
wait_timeout                   = 60

# INNODB #
innodb_flush_method                 = O_DIRECT
innodb_log_files_in_group           = 2
innodb_log_file_size                = 512M
innodb_log_buffer_size              = 64M
innodb_flush_log_at_trx_commit      = 1
innodb_file_per_table               = 1
innodb_buffer_pool_size             = 40G
innodb_buffer_pool_instances        = 12
innodb_autoinc_lock_mode            = 2
innodb_adaptive_hash_index          = 0
innodb_change_buffering             = none

# LOGGING #
log_error                      = /var/lib/mysql/mysql-error.log
log_queries_not_using_indexes  = 1
slow_query_log                 = 1
slow_query_log_file            = /var/lib/mysql/mysql-slow.log
long_query_time                = 1

basedir                        = /usr
tmpdir                         = /tmp
lc_messages_dir                = /usr/share/mysql
explicit_defaults_for_timestamp

slave 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                      = 2
log_bin                        = /var/lib/mysql/mysql-bin
log_slave_updates
expire_logs_days               = 14
sync_binlog                    = 1
log_bin_trust_function_creators= 1
binlog_format                  = ROW

# REPLICATION #
gtid_mode                      = ON
enforce_gtid_consistency

# CACHES AND LIMITS #
tmp_table_size                 = 64M
max_heap_table_size            = 64M
query_cache_type               = 0
query_cache_size               = 0
max_connections                = 500
thread_cache_size              = 50
open_files_limit               = 65535
table_definition_cache         = 4096
table_open_cache               = 4096
wait_timeout                   = 60

# INNODB #
innodb_flush_method                 = O_DIRECT
innodb_log_files_in_group           = 2
innodb_log_file_size                = 512M
innodb_log_buffer_size              = 64M
innodb_flush_log_at_trx_commit      = 1
innodb_file_per_table               = 1
innodb_buffer_pool_size             = 12G
innodb_buffer_pool_instances        = 8
innodb_autoinc_lock_mode            = 2
innodb_adaptive_hash_index          = 0
innodb_change_buffering             = none

# LOGGING #
log_error                      = /var/lib/mysql/mysql-error.log
log_queries_not_using_indexes  = 1
slow_query_log                 = 1
slow_query_log_file            = /var/lib/mysql/mysql-slow.log
long_query_time                = 1

basedir                        = /usr
tmpdir                         = /tmp
lc_messages_dir                = /usr/share/mysql
explicit_defaults_for_timestamp

master status

show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000005
         Position: 194
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: e4e90c71-4264-11e7-b970-4061862b8d34:1-12803
1 row in set (0.00 sec)

slave status

show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting to reconnect after a failed registration on master
                  Master_Host: xxx.xxx.xxx.xxx
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File:
          Read_Master_Log_Pos: 4
               Relay_Log_File: Worker-Intel-i7-2600-16gb-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File:
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
          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: 0
              Relay_Log_Space: 154
              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: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1130
                Last_IO_Error: Master command COM_REGISTER_SLAVE failed: Access denied for user 'repl'@'176.9.137.3' (using password: YES) (Errno: 1045)
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 0
                  Master_UUID:
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp: 170529 17:24:04
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set: 8976078a-40a0-11e7-8043-c8600054b161:1-1085
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

start/setup slave

# mysql -u root -pxxxxxxxxxxx
# stop slave;
# CHANGE MASTER TO 
# MASTER_HOST = 'xxx.xxx.xxx.xxx',
# MASTER_PORT = 3306,
# MASTER_USER = 'repl',
# MASTER_PASSWORD = 'xxxxxxxxxxxxx',
# MASTER_AUTO_POSITION = 1;
# start slave;
# show slave status\G;

Best Answer

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 <secret> |
+-------------------------------------------------------------------------------+
1 row in set (0.03 sec)

mysql>

Related Solutions

Mysql – Create a thesql slave database from master database

Going by your question, I will like to review what I believe you did thus far:

You stopped mysql on the Master
You copied Master's /var/lib/mysql to the Slave's /var/lib/mysql
I surmise the binlogs on the Master were copied as well

Look at the Slave's last binlog. From the question, it should be

mydbm1-bin.008524
Filesize 1330529

Believe it or not, you have to do a few things:

1) On the Master, create a replication user like this:

GRANT REPLICATION SLAVE,REPLICATION CLIENT
ON *.* TO replicator@'%'
IDENTIFIED BY 'r3plic4t0R';

2) Make /var/lib/mysql on the Slave owned by mysql user

chown -R mysql:mysql /var/lib/mysql

3) Make sure Master's server_id is explicitly set in my.cnf

[mysqld]
server_id = 1

4) Make sure Slave's server_id is explicitly set in my.cnf

[mysqld]
server_id = 2

5) Startup mysql on the Slave

service mysql start

6) Setup replication by running this on the Slave

CHANGE MASTER TO
MASTER_HOST='IPAddressOfMaster',
MASTER_PORT=3306,
MASTER_USER='replicator',
MASTER_PASSWORD='r3plic4t0R',
MASTER_LOG_FILE='mydbm1-bin.008524',
MASTER_LOG_POS=1330529;
START SLAVE;
SELECT SLEEP(5);
SHOW SLAVE STATUS\G

You will see something like this:

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 10.48.20.253
                Master_User: replicant
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000254
        Read_Master_Log_Pos: 858190247
             Relay_Log_File: relay-bin.066069
              Relay_Log_Pos: 873918
      Relay_Master_Log_File: mysql-bin.000254
           Slave_IO_Running: Yes
          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: 858190247
            Relay_Log_Space: 873772
            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: 0
1 row in set (0.00 sec)

If Slave_IO_Running and Slave_SQL_Running are both Yes, CONGRATULATIONS !!!

I already answered a post back on Feb 06, 2012 ( How to setup replication(Master/slave) in MySQL 5.5.20? ) with essentially the same steps.

I wanted to add additional posts I made for setting up Circular Replication should you decide to setup the two DB servers as Master/Master

Sep 24, 2012 : Setting up MySQL circular replication in existing replication topology?
May 07, 2012 : Setting Circular Replication in mysql

MySQL – Differences Between mysqld_safe and mysqld

As we all know, mysqld_safe and mysqld are very different

mysqld : The database server instance daemon

mysqld_safe : Control program that examines and sets the environment for mysqld to execute. The mysqld executable is actually launched in a loop. When mysqld terminates, the mysqld_safe program will examine the return results and decide whether

mysqld terminated normally (intentional shutdown), leaves mysqld_safe
mysqld terminated abnormally (crash or kill -9 of mysqld)
- Loop back, mysqld fails on retry, leaves mysqld_safe
- Loop back, mysqld starts up, stays in the mysqld_safe loop

Why is it important to have mysqld and mysqld_safe using the same MySQL version?

Let me illustrate it this way: Percona Server sometimes has additional features in mysqld_safe for manipulating the OS. For example, I have seen numactl --interleave=all in a Percona Server mysqld_safe. If that line was not there, the mysqld for Percona Server may run into issues with memory and swapping.

The same scenario could possibly be the case for Oracle's (ugh, still hate saying that) mysqld and mysqld_safe. There could be improvements from one major release to another that would be removed if the mysqld_safe was older.

Rather than exploring the possibilities of using a old mysqld_safe and a new mysqld (or vica versa), please make your life simple and reinstall MySQL 5.5.30 from scratch.

Before doing so, please run

updatedb
locate mysqld_safe

in Linux and see if there are two lingering. If there are, get the paths straightened out. Otherwise, you may have to reinstall MySQL 5.5.30.