Allowing MySQL users access from all hosts – Fixing mysql ERROR 1045 (28000): Access denied for user ‘root’@’remote-admin.com’ (using password: YES)

Friday, 20th June 2014

mysql_allow_access-from-remote-any-host-fix-access-error-after-sql-migration

I recently migrated MySQL database server from host A to host B (remotesystemadministration.com), because I wanted to have the mysql database server on a separate machine (have separation of server running services and have a dedicated mysql server).

MySQL server host (running on localhost previously was set from my mysql config my.cnf to listen and serve connections on localhost with

bind-address = 127.0.0.1

). MySQL is used by a Tomcat running Java application on localhost and my task was to set the Tomcat to use the MySQL database remotely to MySQL host B (new remote hostname where MySQL is moved is  remotesystemadminsitration.com and is running on IP 83.228.93.76).

Migration from MySQL Db server 1 (host A) to MySQL Db server 2 (host B) is done by binary copying the mysql database directory which in this case is (as it is a Debian server installed MySQL), the standard directory where mysql stores its database data is /var/lib/mysql ( datadir = /var/lib/mysql in /etc/mysql/my.cnf)

Binary copying of data from MySQL db (host A) to MySQL Db (host B) is done with rsync

After migrating and trying to login on migrated mysql  database on remotesystemadministration.net with mysql cli client:

remotesysadmin:~$ mysql -u root -p

I got following error:
 

ERROR 1045 (28000): Access denied for user 'root'@'remotesystemadministration.com' (using password: YES)


To fix the issue I had to login remotely from old migration server mysql (host A) cli:

mysql:~$ mysql -u root -p -h remotesystemadministration.com

and  run SQL commands:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'remotesystemadministration.com' WITH GRANT OPTION;
GRANT USAGE ON *.* TO 'root'@'remotesystemadministration.com' IDENTIFIED BY 'secret-mysql-pass';
FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.03 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)


Another way to solve the problem is to add the root user to be able to connect from any host (Enable MySQL root access from all host), to do so issue:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

Note: In newer version of MySQL, flush privileges could be omitted.

Another approach if you want to substitute access from localhost for all users and enable all users to be able to authenticate to mysql remotely is to execute SQL Query:

UPDATE USER SET host='%' WHERE host='localhost';

Allowing all users to be able to connect from anywhere on the internet is a very bad security practice anyways, if you already have a tight firewall setup and you can only access the server via specific remote IP addresses allowing MySQL access from all hosts / ips should be ok.

Share this on:

Download PDFDownload PDF

Tags: , , , , , , , , , , , , , , , ,

Leave a Reply

CommentLuv badge