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 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 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? 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) 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 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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.