MySQL remote connection fails with "unknown authentication method"

Question

I am trying to remotely connect to MySQL server online from my local machine, but I am getting the following error:

Warning: PDO::__construct(): The server requested authentication 
method unknown to the client [mysql_old_password] in 
C:\xampp\htdocs\ticket\terminal\sync.php

SQLSTATE[HY000] [2054] The server requested authentication method 
umknown to the client

My local MySQL server version is 5.5.27, libmysql - mysqlnd 5.0.10 The remote MySQL server version is 5.5.23, the mysqlnd version isn't exposed.

I guess it's an incompatible password hash issue, but I do not know how to resolve it. Below is part of my connection code

$dsn = 'mysql:host=184.173.209.193;dbname=my_db_name';
$options = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
); 

try {
    $online_dbh = new PDO($dsn, 'myusername', 'mypassword', $options);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Congratulations!";
} catch (PDOException $e) {
    echo $e->getMessage();
} 

Answer 1

alter user 'username'@'localhost' identified with mysql_native_password by 'password'; would fix it.

Answer 2

This may help someone with this issue. This is how I fixed it in my situation. From the MySQL PHP API (PDO_MYSQL) website

When running a PHP version before 7.1.16, or PHP 7.2 before 7.2.4, set MySQL 8 Server's default password plugin to mysql_native_password or else you will see errors similar to The server requested authentication method unknown to the client [caching_sha2_password] even when caching_sha2_password is not used.

This is because MySQL 8 defaults to caching_sha2_password, a plugin that is not recognized by the older PHP (mysqlnd) releases. Instead, change it by setting default_authentication_plugin=mysql_native_password in my.cnf. The caching_sha2_password plugin will be supported in a future PHP release. In the meantime, the mysql_xdevapi extension does support it.