Recently I've done a migration of MySQL server from host A (running and configured to serve requests on (localhost – 127.0.0.1) to host B (server2.host.com)
There are already existing users in mysql which are allowed to only access the database server from localhost as until now the applciation was sending SQL queries straight on localhost. Now the architecture has to change to use the MySQL Database remotely.
Hence I've migrated the MySQL server by dumping all the existing the databases on MySQL host A with:
mysqldump -u root -p --all-databases > alldbs_dump.sql
And then importing the databases on host B with
mysql -u root -p < alldbs_dump.sql
Though this migrated the data from Host A to Host B, still the application on Host A was failing to succesfully use its data from database on Host B, because of inability to properly authenticate. It couldn't authenticate because MySQL on Host B's users are not configured to have access from IP address of Host A, but only allowed the application users to be able to connect on localhost..
I've used following SQL CLI query to check Hosts allowed to connect to MySQL (in this case localhost):
# mysql -u root -p
mysql> use mysql;
mysql> select * from user where user like '%eameiotest%' and Host='localhost';
To fix that I logged on MySQL server on Host B with mysql cli and issued for each of the users the application was using:
UPDATE mysql.user SET Host='%' WHERE Host='localhost' AND User='eameiotest';
UPDATE mysql.user SET Host='%' WHERE Host='localhost' AND User='eameiotest2';
UPDATE mysql.user SET Host='%' WHERE Host='localhost' AND User='eameiotest3';
On execution, If you get errors like:
ERROR 1062 (23000): Duplicate entry '%-eameiotest' for key 'PRIMARY'
Don't think that there is no solution, as I've read some threads online claiming the only way to get around this issue is to dump mysql database and re-import it, this is not necessery. There is a work around to this MySQL bug.
To work-around the error, you will first have to set the user allowed access host to empty – ' ' :
UPDATE mysql.user SET Host='' WHERE Host='localhost' AND User='eameiotest';
UPDATE mysql.user SET Host='' WHERE Host='localhost' AND User='eameiotest2';
UPDATE mysql.user SET Host='' WHERE Host='localhost' AND User='eameiotest3';
And re-issue again commands:
UPDATE mysql.user SET Host='%' WHERE Host='localhost' AND User='eameiotest';
UPDATE mysql.user SET Host='%' WHERE Host='localhost' AND User='eameiotest2';
UPDATE mysql.user SET Host='%' WHERE Host='localhost' AND User='eameiotest3';
You might want to also issue:
GRANT ALL PRIVILEGES ON yourdatabase-name.* TO 'eameiotest1'@'server-host';
GRANT ALL PRIVILEGES ON yourdatabase-name.* TO 'eameiotest2'@'server-host';
GRANT ALL PRIVILEGES ON yourdatabase-name.* TO 'eameiotest3'@'server-host';
This should have solve the app connection issues, Cheers 🙂
More helpful Articles
Tags: application, com, configured, databases, existing users, localhost, mysql database, server2, threads, update
