How to migrate mysql authentication from mysql_native_password to caching_sha2_password?

Question

So far I'm only using mysql_native_password authentication, either from java applications or bash scripts as follows.

application.properties:

spring.datasource.username=root
spring.datasource.password=rootpw

Or native bash scripts:

export MYSQL_PWD=rootpw; mysql --user=root --database=mytable -e "INSERT INTO mytable VALUES(...)"

Question: how can I migrate those applications for caching_sha2_password, but still being able to authenticate the applications with username/password combination as above?

Answer 1

first of all open your MySQL configuration, which is my.cnf or my.ini, then under [mysqld] section write like below:

default_authentication_plugin=caching_sha2_password

now go to MySQL server using the root account and write this SQL command:

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'root_password';

and in your Spring Boot and application.properties enter like this:

spring.datasource.username=root
spring.datasource.password=root_password

at the end for native bash scripts, just use the MYSQL_PWD environment variable!

something like this:

export MYSQL_PWD=rootpw; mysql --user=root --database=mytable -e "INSERT INTO mytable VALUES(...)"