1

I have a aws ec2 ubuntu, apache server setup with mysql. My issue is I am able to connect to mysql via the command line. However if I try to use like Mysql Workbench or a program like that to connect to the database I am able to login to the server and see all databases and tables but not able to run any select queries or do anything with the database other then see it.

I tried doing a

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

However it does not seem to fix the issue? I get an error that says

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

I confirmed I have the bind-address set to 0.0.0.0 . When I look at the users table on the command line I do see a row for user root with host % listed.

  • 2
    Is your goal to store data for anyone on the whole Internet, available to anybody, and persisted until somebode (else) deletes it? Because it seems like that. – Law29 Jun 10 at 15:37
  • I want anyone using the username and password to be able to access the database regardless of what machine they are accessing the database from . – Jayreis Jun 10 at 15:39
0

You must not open MySQL port to the internet, it is too insecure. More information can be found at https://security.stackexchange.com/questions/63881/is-it-not-safe-to-open-mysqls-port-to-the-internet.

Most preferred way is to have an app running on top of MySQL, which handles the user interface / API for data access.

If there is a real need for remote SQL access to database, you should use SSH tunneling to achieve it. https://dev.mysql.com/doc/workbench/en/wb-mysql-connections-methods-ssh.html shows the instructions for that.

Your database passphrases should also be always secure.

| improve this answer | |
  • So your saying I should install/use something like phpmyadmin or something like that on the server? instead of just using like mysql workbench which is on my desktop and creating a user/pass to be able to get in with? – Jayreis Jun 12 at 1:48
  • Any app that gives enough access for your purposes is fine. Optimal would be an app that gives the provides the minimum required access and nothing more. I also updated my answer with a secure way of using MySQL WorkBench. – Tero Kilkanen Jun 12 at 6:48
  • Thank you Tero. That does not solve my question in my OP. if I ssh in to my server via Putty and then login to mysql I can do everything in mysql (create, select update delete). However if i use the same credentials to login to mysql from mysql workbench I am able to login but any query I get an access denied. If I try to use the ssh tunnel option you propose with mysql workbench I get a authentication failed for "none" error message? if it helps my mysql is on an AWS EC2 so I use a ppk file to ssh in not a password. – Jayreis Jun 12 at 19:40
  • You should use some other user name than root, since root user might have special handling in the setup. – Tero Kilkanen Jun 13 at 7:09
0

Just use an SSH tunnel. ssh -L3307:localhost:3306 ec2-user@your_ec2_host; then point your Mysql Workbench to localhost:3307. Note: these are two different localhost's; the first refers to your_ec2_host from its own perspective, the second refers to your dev machine, where you ssh from.

Now, I know you've heard this 500 times; hear it once more.

I have the bind-address set to 0.0.0.0

— don't do this.

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

Don't do this either. Create a separate user, and grant it the minimum permissions it needs.

I am able to login to the server and see all databases and tables but not able to run any select queries or do anything with the database other then see it.

The reason for this — is likely that your mysqld has another user, root@localhost, with only permissions to access the DB schema but nothing more. To MySQL, this is different user; further, the GRANT rules for root@localhost win over root@'%' — because the former is more specific than the latter %-wildcard.

You'd know this — had you not cheated by going for the root user and neglecting to learn how MySQL users and permissions work. Seriously: create a separate user.

| improve this answer | |
0

MySQL Workbench supports ssh tunneling: https://dev.mysql.com/doc/workbench/en/wb-mysql-connections-methods-ssh.html

MySQL Workbench is using internally paramiko python module. In the past, because of some bugs, I had to update it manually. But newer versions of MySQL Workbench are coming with updated version of paramiko. So make sure you are using the latest version of MySQL Workbench.

You will need either an username/password pair, or username/ssh_key pair to authenticate, and the SSH server hostname or IP.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.