Database replication issue:

Problem scenario with logs:

Running ubuntu on two VMs with mysql server running on both. I am in the process of doing mySQL database replication. I did all the configuration on MASTER and SLAVE VMSs Was able to export and import the database dump onto the slave and checked that database was there with all the fields.

1. After configuring the slave user on the Master,

When I ran SHOW SLAVE STATUS\G

Slave_IO_Running = Connecting

Slave_SQL_Running = YES

Got the following error:

Last I/O error: Error connecting to source ' s..r@192.168.x.x:3306'. Message: Authentication plugin'caching_sha2_password' reported error: Authentication requires secure connection:

 

2. So on Master modified user permissions to take care of the secure authentication issue:

ALTER USER 'slave_user1'@'%' IDENTIFIED WITH 'mysql_native_password' BY 'your_password';

 

3. Then generated a new mysql.bin file and position file by running SHOW MASTER STATUS and used this to configure the SLAVE.

Now on checking slave status, got

Slave_IO_Running = YES

Slave_SQL_Running = NO

Restarted the SQL server, saw the same issue and error which was :

"Coordinator stopped because there were error(s) in the worker(s). Worker 1 failed executing transaction ' Annonymous" at source log mysql.bin.000012, end_log_pos_446.See error log for more details."

Error log showed: Same as above but with error code MY-001396

 

4. Removed and restored the Database and restarted mysql, still same status and error.

 

5. When I ran show GRANTS FOR 'slave_user1' on the slave machine,

Got the message There is no such grant privileges defined for user 'salve_user1' on the (slave host name.)

 

Question:

What steps do I need to take to be able to get both values (slave I/o running and slave sql running) to be yes, so that I can get database replication running: