MySQL 8.4 community version and replication process setup using caching_sha2_password and secure connection

Question

Trying to setup a replication process between two MySQL Server 8.4.1 servers using a replication user with 'caching_sha2_password' I received the following error using the command "start replica USER='replicationuser' PASSWORD='****' DEFAULT_AUTH='caching_sha2_password" to start the replication process:

Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

I have temporarily resolve this issue by setting up both MySQL servers to support 'mysql_native_password'. However, this is not the best solution since 'mysql_native_password' is deprecated and not supported in next MySQL Server versions.

Does anyone have set up a secure connection in a replication process using 'caching_sha2_password'?

I tried to setup a replication process with a user authentication 'caching_sha2_password' without success because a secure connection is required.

Answer 1

Does anyone have set up a secure connection in a replication process using 'caching_sha2_password'?

I did setup source-replica using caching_sha2_password , both MySQL versions are 8.0.37.

Add GET_MASTER_PUBLIC_KEY=1 on the CHANGE MASTER TO statement.

 CHANGE MASTER TO 
  MASTER_HOST='your_source_ip',
  MASTER_USER='your_source_user', 
  MASTER_PASSWORD='pass',
  MASTER_PORT=3306, 
  MASTER_LOG_FILE='mysql-bin.001419', 
  MASTER_LOG_POS=48489886,
  GET_MASTER_PUBLIC_KEY=1,
  MASTER_CONNECT_RETRY=10;  

Note, Master is deprecated and it is been replaced by Source so some warnings will be thrown. You could use the CHANGE REPLICATION SOURCE TO Statement syntax

 CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='source2.example.com',
  SOURCE_USER='replication',
  SOURCE_PASSWORD='password',
  SOURCE_PORT=3306,
  SOURCE_LOG_FILE='source2-bin.001',
  SOURCE_LOG_POS=4,
  GET_SOURCE_PUBLIC_KEY=1,
  SOURCE_CONNECT_RETRY=10;