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!

Related Posts

Powered By Related Posts for WordPress
Click Here to Learn More About Related Posts for WordPress

11 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

  4. An easier way is via an SSH tunnel, easily done in MySQL Workbench

    In Workbench (or any mysql tool), select connection method, Standard TCP/IP over SSH

    Port is usually 2222 for vagrant SSH:

    Hostname: 127.0.0.1:2222
    Username: vagrant
    SSH key file: /path/to/local/site/.vagrant/machines/default/virtualbox/private_key

    Then enter your DB settings next and hey presto…
    hostname: 127.0.0.1
    port: 3306
    username: root

    No need for any vagrant or mysql changes.

  5. I have noticed you don’t monetize your website, don’t waste your
    traffic, you can earn additional cash every month because you’ve got hi quality content.
    If you want to know how to make extra bucks, search for: Ercannou’s
    essential tools best adsense alternative

  6. Many thanks for this tutorial, worked like a charm! You sir made my day 🙂

    Have a good one !

  7. Very nice and informative to me. Thank You So Much.

  8. Thanks for the great post keep up the amazing work.

  9. First of all, congratulations for writing such a wonderful post. Thanks For Sharing With Us.

  10. Thanks for sharing beauty with us. I hope you will share some more info. Please keep sharing!

Leave a Reply

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