Remotely Connecting to a MySQL Database

MySQL: How to allow remote connection to mysql

That is allowed by default on MySQL.

What is disabled by default is remote root access. If you want to enable that, run this SQL command locally:

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

And then find the following line and comment it out in your my.cnf file, which usually lives on /etc/mysql/my.cnf on Unix/OSX systems. In some cases the location for the file is /etc/mysql/mysql.conf.d/mysqld.cnf).

If it's a Windows system, you can find it in the MySQL installation directory, usually something like C:\Program Files\MySQL\MySQL Server 5.5\ and the filename will be my.ini.

Change line

 bind-address = 127.0.0.1

to

 #bind-address = 127.0.0.1

And restart the MySQL server (Unix/OSX, and Windows) for the changes to take effect.

How to remotely connect to MySQL database which is in Private EC2 instance to Visual Studio in localhost

The common way to access a private instance, rds or any other resource in a VPC from your local workstation is through ssh tunnel.

So basically, you would establish ssh tunnel to a public instance, which would then forward connections to the private instance with mysql. Having the tunnel, you would access your database using localhost on your local workstation.

There is a number of tutorials on how to do it. Examples are:

• Connecting to an AWS RDS database through an SSH tunnel
• Accessing a private RDS instance via an ssh tunnel
• SSH Tunnels (How to Access AWS RDS Locally Without Exposing it to Internet)

is it possible to remote access to mysql database on shared hostings?

In most default installations, MySQL binds to 127.0.0.1 - which only allows local connections.

If you wish to change that (ATTENTION: it might be a security issue), you need to change it in the configuration file (usually /etc/mysql/my.cnf in linux distributions).
Or in windows it might be on these locations:

C:\Windows\my.ini C:\Windows\my.cnf C:\my.ini C:\my.cnf 
C:\Program Files\MySQL\MySQL Server 5.5\my.ini 
C:\Program Files\MySQL\MySQL Server 5.5\my.cnf

Usually it's enough to comment out the bind-address line like this, which will allow connections from all IPs:

# bind-address          = 127.0.0.1

It is also worth noting that most webhosters won't allow MySQL connections from arbitrary hosts and you can't change that fact (you may only access your MySQL instance by using a local application - in most cases phpmyadmin).

Remotely connecting to a MySQL database

Use the supplied domain name ukld.db.5510597.hostedresource.com

Prepending the hostname with an IP as you are doing only changes the hostname and that is why it is failing to connect.

The hostname will be converted to an IP address behind the scenes for you. No need to do it yourself. Plus, hardcoding IPs is bad practice as they can change over time.

Connect Java remotely to MYSQL DB on another network

If you are trying to connect to remote database then yo need to change the database url from localhost to remote server ip address.

jdbc:mysql://Atomic-PC:3306/test

to

jdbc:mysql://<db-server-ip-address>:<db-server-port>/<db-name>

Assuming, remote server ip address is 10.234.05.123 and database port number is 3300 and database name is remoteDB. Then,

jdbc:mysql://10.234.05.123:3300/remoteDB

Mysql adding user for remote access

In order to connect remotely, you have to have MySQL bind port 3306 to your machine's IP address in my.cnf. Then you have to have created the user in both localhost and '%' wildcard and grant permissions on all DB's as such . See below:

my.cnf (my.ini on windows)

#Replace xxx with your IP Address 
bind-address        = xxx.xxx.xxx.xxx

Then:

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypass';
CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypass';

Then:

GRANT ALL ON *.* TO 'myuser'@'localhost';
GRANT ALL ON *.* TO 'myuser'@'%';
FLUSH PRIVILEGES;

Depending on your OS, you may have to open port 3306 to allow remote connections.

---

Related Topics