Ssh – Connect to Database on Same Network But Different Machine From Mysql Workbench

MySQLssh

I've got a web server/database server running on my local network. I've set the static ip to 192.168.1.210. I've installed mysql on the server, set the my.cnf bind-address to the static ip.

I am trying to use mysql workbench on my dev machine to connect to that database (on a different machine but in the same local network) so that I can manage the database through a gui rather than having to manage it through SSH.

I've tried setting up a new connection using the static ip as the host, default 3306 port. I've tried setting up the connection over standard TCP/IP and also TCP/IP through SSH in workbench and I can't connect.

I know networking to the server is working because I can SSH into it just fine. I am trying to use root as the username from workbench but I never get to the point where it asks me for a password.

Is there a guide somewhere out there? I can't seem to find any good resources.

Best Answer

There were a few things I had to do.

First log into mysql using root from local machine (the server, but I used ssh) and add a new remote user.

mysql -u root -p

It will prompt for a password. Then create a new user that has access from anywhere.

CREATE USER 'username'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'username'@'%';
FLUSH PRIVILEGES;

Then quit and restart mysql.

sudo service mysql restart

I also had to edit the /etc/mysql/my.cnf and comment out the bind-address line which is normally set to 127.0.0.1 which means that access is only allows from local machine...basically.

Last but not least, I am using the basic ubuntu server firewall so I had to allow access to port 3306 (default mysql port which can also be changed in my.cnf).

sudo ufw allow 3306

Then check the status to make sure it worked.

sudo ufw status

You'll see port 3306 in the list.

You can test access to the database using your new username with the following command.

mysql -h <server ip address> -u username -p

It will prompt for your new password and should connect properly.

I had gotten stuck on the firewall portion. It never crossed my mind to check for that.

Related Topic