Connect to MySQL on your Vagrant Machine

I’ve been working on setting up a Go server in vagrant today and one thing I ran into was the lack of phpMyAdmin. Because it’s a Go server there’s no PHP and phpMyAdmin needs, surprise, PHP.

I’ve used MySQL Workbench in the past for remote database management so I figured I’d just use that instead of phpMyAdmin. Connecting to my vagrant box turned out to require a bit more work than simply entering the IP address and port though.

Please note that the following post is meant for local development and not for live servers.

Connecting to your Vagrant box via MySQL Workbench

So in order to be able to connect to your MySQL server, running on your Vagrant box, you need to change/setup a couple of things.

Port Forwarding

First thing you need to do is forward the default MySQL port (3306) on your virtual box / Vagrant machine. You can do so by editing your Vagrantfile and add the following code:

config.vm.network "forwarded_port", guest: 3306, host: 3306

It’s also very convenient if your Vagrant box has a static IP address, not just for this purpose. You can assign a static IP to your Vagrant box by adding the following line to the (same) Vagrantfile file:

config.vm.network "private_network", ip: "192.168.33.10"

I’m using this IP address because my local network is 192.168.33.xxx. Use an IP address that is free in your LAN.

Allow External Connections on your MySQL Service

Allowing external connections to your MySQL server is normally a bad thing. That’s why MySQL has it (somewhat) disabled by default. In order to allow external connections, disable the following lines in your /etc/mysql/my.cnf file by adding a # in front of them. You can edit this file like this: sudo nano /etc/mysql/my.cnf

#skip-external-locking
#bind-address

After you’ve changed these lines, restart your MySQL service. You can do so like this: sudo service mysql restart

Create a MySQL user that can connect from anywhere

The final step is to have a MySQL user that is allowed to connect to your MySQL server from a different host than localhost. After you’ve created your user you can run the following command in your MySQL shell* to allow this user to connect from anywhere.

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

* You can enter your MySQL shell by entering mysql -u root -p in your Vagrant shell.

Note that I’m allowing the root user to be able to login from any host now. Everything in this post is meant for local development and should NOT be done on a live server. I just wanted to point this out again because enabling this on a live server would be very unwise.

If you’re creating a new user to be able to connect from any host, please note that you need to grant it permission to connect from localhost first. Your GRANT commands would then look like this:

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

I hope this helped you in connecting to your Vagrant box’s MySQL server with a remote tool like MySQL Workbench!

Click Here to Learn More About Related Posts for WordPress

3 thoughts on “Connect to MySQL on your Vagrant Machine

  1. thq for sharing good information here

  2. Thank you for your information. It really helped me to solve my problem.
    God bless you! 🙂

  3. Works perfect with HeidiSQL

Leave a Reply

Your email address will not be published. Required fields are marked *