不允许Perl / MySQL'主机'和'失败：客户端不支持服务器请求的身份验证协议'错误？

Question

I am writing a Perl script that is being executed from a remote UNIX server. The script is supposed to connect to my localhost machine, and post some data to the MySQL server running by XAMPP. I used the following code to initiate a connection:

my $dbHost = "my-machine-name";
my $dbUsername    = "root";
my $dbPassword    = "";
my $databaseName = "myDB";
my $connection = DBI->connect("DBI:mysql:$databaseName;host=$dbHost", 
                               $dbUsername, $dbPassword, { RaiseError => 1 } ) 
                 or die ( "Couldn't connect to database: " . DBI->errstr );

At first I was getting:

Host ... is not allowed to connect to this MySQL server

Then I read that I need to add the rejected host to the list of users. So I opened XAMPP Control, clicked on XAMPP->ADMIN to open phpMyAdmin, then navigated to the user table, copied the row that said host=127.0.0.1 and then modified the 127.0.0.1 to the rejected IP I was getting. Then I opened XAMPP Control again, stopped both Apache and MySQL, and then started them.

Now, I am getting:

DBI connect('myDB;host=my-machine-name','root',...) failed: Client does not support authentication protocol requested by server; consider upgrading MySQL client at ...

I tried changing the hostname from the string-name to the IPv4 Address, and still got the same error. Also tried adding the :3306 to the end of the host, but got the same error.

Any idea what I can do to access a localhosted MySQL from a remote server?

Answer 1

This should work:

$server = 'your server';
$port = '3306';
$user = 'your_user';
$pass = 'your_pass';
$database = 'database';
$dbConn = DBI->connect("DBI:mysql:$database:$server:$port", "$user", "$pass")
or die "Couldn't connect to database: " . DBI->errstr;