How to connect R to MySQL? Failed to connect to database: Error: Plugin caching_sha2_password could not be loaded

The R mysql library depends on libmysqlclient/libmariadbclient. The missing caching_sha2_password seems to be an indicator that an old mysqlclient version or a libmariadbclient isn't installed. Only very recently did caching_sha2_password get added to mariadb (3.0.8)

An alternative, like this answer, is to change the user in mysql to use a different authentication mechanism:

You set the user back to mysql_native_password:

ALTER USER 'username'@'localhost'
IDENTIFIED WITH mysql_native_password BY 'password'

To make this the default for all newly created users change the my.cnf/my.ini setting default_authentication_plugin=mysql_native_password