7

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 [email protected]            |
+--------------------------------------------+
| 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;
Improve this question
0

6 Answers 6

Reset to default
9

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

mysql> show grants;
+--------------------------------------------+
| Grants for [email protected]            |
+--------------------------------------------+
| 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 [email protected]                                                              |
+-------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO [email protected]  IDENTIFIED BY PASSWORD <secret> |
+-------------------------------------------------------------------------------+
1 row in set (0.03 sec)

mysql>
Improve this answer
6
  • 1
    strange now I get ERROR 1045 (28000): Access denied for user 'root'@'localhost' when trying to execute the statement from root user.. When I look into mysql.user all columns is set to Y on root
    – clarkk
    Commented May 31, 2017 at 9:50
  • have also tried to service mysql restart but still get the error when trying to grant privileges to repl from root
    – clarkk
    Commented May 31, 2017 at 9:57
  • So you mean to say you can't login to master as root which you were able to before?
    – Mannoj
    Commented May 31, 2017 at 9:59
  • yes.. I cant grant anything from root anymore.. show grants => GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' (with grant option is missing even if it is set to Y in mysql.user)
    – clarkk
    Commented May 31, 2017 at 10:04
  • follow this method howtoforge.com/setting-changing-resetting-mysql-root-passwords and recover mysql root password see section under Recover MySQL root password
    – Mannoj
    Commented May 31, 2017 at 10:08
3

Sounds like a password issue. 1045 Does your password have the # character? remove it Is your password longer than 32 characters? shorten it

See TROUBLESHOOTING MYSQL REPLICATION ERROR 1045

Improve this answer
0
2

This is not the correct solution to the problem (the error code is really different), but I'll leave it, as I wasted lot of time searching for a solution to this problem. The answer might be useful to somebody after all.

I had similar case with error in the slave log file

error connecting to master '...' - retry-time: 60  retries: 12, Error_code: 3159

The problem was that the master required secure connection. Starting the slave with

Master_SSL_Allowed  Yes

Fixed my problems

Improve this answer
0
0

Glad previous answer was not deleted, it pointed me in the right direction. I have a master-master environment set up in VirtualBox with SSL enabled. At master1 when I ran CHANGE MASTER TO MASTER_HOST, I did not set MASTER_SSL=1. However, replication was working. At master2 I was getting the following err msg. After RESET SLAVE, I ran the CHANGE MASTER TO MASTER_HOST with the correct parameters on both masters to fix the issue.

Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
Last_IO_Errno: 2026
Last_IO_Error: error connecting to master '[email protected]:3306' - retry-time: 60  retries: 1

CHANGE MASTER TO MASTER_HOST='xxx.xxx.xxx.xxx', MASTER_USER='RepAcc', MASTER_PASSWORD='***', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154, MASTER_SSL=1, MASTER_SSL_CA='/path/to/ca-cert.pem', MASTER_SSL_CERT = '/path/to/master-public.pem', MASTER_SSL_KEY = '/path/to/master-private.pem';
Improve this answer
0

mysql version > 8.0

if you are creating user mode:

create user 'repl'@'%' identified by '123456'

the sign method is: caching_sha2_password,we need add options GET_MASTER_PUBLIC_KEY=1 to the CHANGE MASTER TO

use:

CHANGE MASTER TO MASTER_HOST='master_ip',MASTER_PORT=3306, MASTER_USER='RepAcc', MASTER_PASSWORD='***', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154,GET_MASTER_PUBLIC_KEY=1;

it work!

or

if you are creating user mode: create user 'repl'@'%' identified with mysql_native_password by '123456';

use:

CHANGE MASTER TO MASTER_HOST='master_ip',MASTER_PORT=3306, MASTER_USER='repl', MASTER_PASSWORD='***', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;

it work also！

last exec:

show slave status \G;:

Improve this answer
-1

If it is mysql 8.4 & above, you need to add this statement alongwith defining the replication parameters

GET_SOURCE_PUBLIC_KEY=1;

The reason is, from 8.4 onwards, mysql_native_password module is deprecated and it is no longer present at all in mysql 9 +, so it does not allow plain text password. By default it uses "caching_sha2_password" for authentication. Hence, adding this, will enable the secure authentication.

Your replication parameters should look like this

CHANGE REPLICATION SOURCE TO
SOURCE_HOST='127.0.0.5',
SOURCE_PORT=3306,
SOURCE_USER='replica_user',
SOURCE_PASSWORD='ReplicaPassword',
SOURCE_LOG_FILE='mysql-bin.000001',
SOURCE_LOG_POS=888,
GET_SOURCE_PUBLIC_KEY=1;
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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