remote MySQL Server access

[kotinkarwak]

Hi,
In a muddle.
On DigitalOcean, installed ebase v5 and oracle, tried to install mysql and that played up so thought best spin another server and have it run mysql on its own.
Problem: Creating a connection to my development machine i.e remote to DigitalOcean. Getting this error

Code: Select all

JavaException: com.ebasetech.ufs.kernel.FormException: java.sql.SQLException: SQLException in StandardPoolDataSource:getConnection exception: java.sql.SQLException: SQLException in StandardPoolDataSource:getConnection no connection available java.sql.SQLException: Cannot get connection for URL jdbc:mysql://46.101.95.129/?autoReconnect=true : Could not create connection to database server. Attempted reconnect 3 times. Giving up.

The database connection wizard reads
Server Host Name: 46.101.95.129
User Name: <username>
Password: <password>

When selecting find of database name above message is output.

My understanding is since the root account created for mysql access is registered as root@localhost, then I needed to create a new user and grant privileges accordingly. monty is defined as monty@% within the database for this purpose. flushed privileges and restarted mysql service.

[Ian]

Hi,

The issue is I believe to do with the privileges associated with the user.
Did you grant this user access to the specific database you are trying to access?

This is what I do:
From the command line access MySql as root then type in the following:

CREATE USER 'monty'@'%' IDENTIFIED BY 'somepassword';
USE MyData;
GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%';
FLUSH PRIVILEGES;

Where MyData is the name of the database you want to access.

Have you tried to access this database outside of Ebase using something like heidieSQL

Regards

Ian Bramford

[kotinkarwak]

Thanks.
This lead me to further investigate after performing the grant for @% with no success only to find that I needed to comment out the following line in file

/etc/mysql/my.cnf
#bind-address = 127.0.0.1

after which it worked. MySQL Workbench and ebase Xi 5.0.1 local to my machine.

This might have security implications I assume hence will be wary unless I know this is a scenario for managing/working with the database.

[Ian]

Actually all you need do is change the bind statement to the external IP address of your server.

And yes this does have security implications but these can be mitigated by having reasonably hard passwords for the MySql users.

Ian

[kotinkarwak]

Ian, not at my machine, do you mean that line should point to the IP address of my of where I need to connect from?

[Ian]

The bind address IP would be the external IP of the server itself.

Example

bind-address = 46.101.95.129

The reason you couldn't access before was because in the Ebase connection wizard you had put in the servers external IP address.

The database connection wizard reads
Server Host Name: 46.101.95.129
User Name: <username>
Password: <password>


If you had put in the IP address to which MySql was bound (127.0.0.1 or localhost) the connection would have worked.

The database connection wizard reads
Server Host Name: localhost
User Name: <username>
Password: <password>

But this would have meant that you would only have been able to access MySql when you were physically logged onto the server.

Putting in the external IP address in the bind-address parameter means that you should also be able to access MySql via a client side UI (heideSql) running on your local computer.

This is a good thing to be able to do as it lets you work directly with the database, its tables and their data.

Ian

[kotinkarwak]

Thanks
Sorted now.
My mistake was assuming the IP was for my local machine as opposed to the server (digitalocean).