MYSQL database replication Error MY-002061

Question

I've been trying to get two databases in docker containers to replicate. I've been working off this article: https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-in-mysql (and others).

The state I've been able to get to is that the master and slave dbs have loaded cnf files that set the server-ids, and after the DBs are stood up, I am able to run a few commands that should get the DBs to be connected:

MS_STATUS=`docker exec mysql-master sh -c 'export MYSQL_PWD=password; mysql -u root -e "SHOW MASTER STATUS"'`
CURRENT_LOG=`echo $MS_STATUS | awk '{print $6}'`
CURRENT_POS=`echo $MS_STATUS | awk '{print $7}'`

start_slave_stmt="CHANGE MASTER TO MASTER_HOST='mysql-master',MASTER_USER='replication_user',MASTER_PASSWORD='replicationPW',MASTER_LOG_FILE='$CURRENT_LOG',MASTER_LOG_POS=$CURRENT_POS; START SLAVE;"

start_slave_cmd='export MYSQL_PWD=password; mysql -u root -e "'

start_slave_cmd+="$start_slave_stmt"

start_slave_cmd+='"'

docker exec mysql-slave sh -c "$start_slave_cmd"

This sets the master_log_file and Read_master_log_pos to the same values in both the master and slave dbs.

The error I am getting is:

mysql-slave               | 2018-09-20T20:13:41.547886Z 11 [ERROR] [MY-010584] [Repl] Slave I/O for channel '': error connecting to master 'replication_user@mysql-master:3306' - retry-time: 60  retries: 1, Error_code: MY-002061

I've looked at the documentation, but I don't see any mention of that error code (2061). What am I missing?

I've named the replication user in both database the same... could that be the issue?

Answer 1

Also, you just can run

CHANGE MASTER TO GET_MASTER_PUBLIC_KEY=1;

but this solution has a restriction (see the documentation https://dev.mysql.com/doc/refman/8.0/en/caching-sha2-pluggable-authentication.html)

Use of a trusted local copy of the public key enables the client to avoid a round trip in the client/server protocol, and is more secure than requesting the public key from the server. On the other hand, requesting the public key from the server is more convenient (it requires no management of a client-side file) and may be acceptable in secure network environments.

Answer 2

1. MY-002061 - it`s problem authentication;
2. When creating a User, you must explicitly specify the authentication plugin sha256_password;
3. For example: CREATE USER 'slaveuser'@'%' IDENTIFIED WITH sha256_password BY '0000';
4. Then: GRANT REPLICATION SLAVE ON *.* TO 'slaveuser'@'%';
5. After that, of course, re-create everything.

Answer 3

This works for me. Try to use your replication user to connect to the Master server. Example. in your Slave node do below,

mysql -h Master_Server_Ip -u replication_user -p

then monitor your slave node after doing above via SHOW SLAVE STATUS command

Answer 4

On the master SHOW GLOBAL VARIABLES LIKE 'caching_sha2_password_public_key_path'. Copy that file to the slave.

On the slave, use the CHANGE MASTER TO statement with the MASTER_PUBLIC_KEY_PATH option to specify the RSA public key file

Answer 5

During creation you can use old mechanism: CREATE USER 'replicationuser'@'%' IDENTIFIED WITH mysql_native_password BY replicpass;

this mysql_native_password will use old method and in case you are using linklocal to connect to slave is enough.

Answer 6

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;