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!
thq for sharing good information here
Thank you for your information. It really helped me to solve my problem.
God bless you! 🙂
Works perfect with HeidiSQL
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.
I really want to thank you for this comment. Brilliant!
Finally, host (Win 10) and guest (vagrant Ubuntu 18) are connected. Thank you. I am elated.
Many thanks for this tutorial, worked like a charm! You sir made my day 🙂
Have a good one !
Very nice and informative to me. Thank You So Much.
Thanks for sharing beauty with us. I hope you will share some more info. Please keep sharing!
Thanks so much for posting, works like a boss w/ SequelPro as well!
This is a must read for the new coders. Thought it would be hard to code the connection to MySql, now it looks manageable with your guide here. Keep it up.
Good info and a nice guide especialy for new coders. Thanks for this!
Thanks for this useful MySQL tutorial, it really helped me..
ABTEC SOLUTIONS LTD. (aka ABTec Solutions) our guiding principle is simple: To help you Digitally Shine.
ABTec stepped in and partnered with what we have believed that it could be a revolutionary LMS platform boosted with artificial intelligence (AI), and gamification
Explore meditation exercises and yoga routines to enhance mental clarity and physical vitality, while adopting healthy habits for sustained fitness and optimal nutrition. Dive into emotional health strategies and spiritual growth principles, including faith-based living and Christian living, to nurture your mind, body, and spirit. With self-care practices, relaxation techniques, and personalized fitness routines, empower yourself to achieve a harmonious and fulfilling lifestyle.
Explore meditation exercises and yoga routines to enhance mental clarity and physical vitality, while adopting healthy habits for sustained fitness and optimal nutrition. Dive into emotional health strategies and spiritual growth principles, including faith-based living and Christian living, to nurture your mind, body, and spirit. With self-care practices, relaxation techniques, and personalized fitness routines, empower yourself to achieve a harmonious and fulfilling lifestyle.