MySQL → How to connect to your Ubuntu VM remotely using MySQL Workbench from Oracle

Published in

Code Kings

3 min readSep 26, 2015

INTRO

Sometimes you end up chasing your tail all day long working on a project until you finally realize that you are a human, and you don’t have a tail. That’s what happened to me on this issue and now that I resolved it, I wanted to save you the time. This tutorial will assume you have all your VM programs installed, such as MySQL, and MySQL Workbench.

STEPS

Virtual Machine

SSH into your VM and log into MySQL as root

1. ssh Your_Username@YourHost.TLD2. mysql -u YOUR_MYSQL_ROOT_USERNAME -p

3. Enter your password for your MySQL user

4. Run these commands in Mysql to create a new admin user and give them admin rights

mysql> CREATE USER 'YOUR_USERNAME_HERE'@`%` IDENTIFIED BY 'YOUR_PASSWORD_IN_PLAIN_TEXT_HERE';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'YOUR_USERNAME_HERE'@`%`WITH GRANT OPTION;

Now your new user has been granted admin rights. The % sign signifies a wildcard, meaning that your new user will have access to all schemas in MySQL.

You will want to flush the privileges so that MySQL will load your user with its new rights.

mysql > FLUSH PRIVILEGES;

You can now exit mysql.

In order to allow remote connections to your MySQL from MySQL Workbench, you will need to modify the my.conf file located in /etc/mysql/my.cnf. You will want to sudo vim into that file:

sudo vim /etc/mysql/my.cnf

Once inside if you don’t see bind-address, you may add it at the bottom of the page under the group [mysqld]. Below is an example:

Setting the bind-address to 0.0.0.0 allows mysql to listen to all the network interfaces and await connections. If you want only a specific IP address to access MySQL on your VM, simply put the valid IP Address that you want to allow connections to MySQL. For this example, 0.0.0.0 is sufficient.

Save this file and restart the MySQL service