Posts Tagged ‘remotesystemadministration’

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

Friday, June 20th, 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.

How to configure ssh to automatically connect to non standard ssh port numbers (!port 22)

Tuesday, August 2nd, 2011

SSH Artistic Logo, don't give away your password

Today I’ve learned from a admin colleague, a handy tip.
I’m administrating some Linux servers which are configured on purpose not to run on the default ssh port number (22) and therefore each time I connect to a host I have to invoke the ssh command with -p PORT_NUMBER option.

This is not such a problem, however when one has to administrate a dozen of servers each of which is configured to listen for ssh connections on various port numbers, every now and then I had to check in my notes which was the correct ssh port number I’m supposed to connect to.

To get around this silly annoyance the ssh client has a feature, whether a number of ssh server hosts can be preconfigured from the ~/.ssh/config in order to later automatically recognize the port number to which the corresponding host will be connecting (whenever) using the ssh user@somehost without any -p argument specified.

In order to make the “auto detection” of the ssh port number, the ~/.ssh/config file should look something similar to:

hipo@noah:~$ cat ~/.ssh/config
Host home.*.pc-freak.net
User root
Port 2020
Host www.remotesystemadministration.com
User root
Port 1212
Host sub.pc-freak.net
User root
Port 2222
Host www.example-server-host.com
User root
Port 1234

The *.pc-freak.net specifies that all ssh-able subdomains belonging to my domain pc-freak.net should be by default sshed to port 2020

Now I can simply use:

hipo@noah:~$ ssh root@myhosts.com

And I can connect without bothering to remember port numbers or dig into an old notes.
Hope this ssh tip is helpful.