I’ve been playing around with Vagrant to set up development environments.
One thing that I thought would be useful would be to connect to the database which is running inside your Vagrant machine from the host machine. That way it would be easier to inspect or tweak what the database was doing while the application is running with a GUI like MySQL Workbench. Additionally you could reset the database with a SQL script from the host without dropping into vagrant ssh.
It’s not hard, but there are a couple steps you have to do to make that connection work.
Set up port forwarding
Inside the Vagrantfile, the line would look something like this
config.vm.network :forwarded_port, guest: 3306, host: 3309
Make sure the MySQL user can connect from outside localhost
We can do this at provision time when you create the database and create the user. One way to do this is for the MySQL provisioner to reference a SQL file to set it up:
mysqladmin -u root password root
mysql –user=root –password=root –host=localhost –port=3306 < /vagrant/mysql_boostrap.sql
create schema appdb;
create user ‘dbuser’@’%’ identified by ‘dbuserpassword’;
grant all on appdb.* to ‘dbuser’@’%’;
Rebind MySQL Host inside the VM
We need to rebind the mysql inside the VM. Edit your my.conf file (say, sudo emacs /etc/mysql/my.cnf) and comment out the following lines
We’re ready to connect from the host! To connect from the mysql client on the command line, we need to use the TLS setting. Your command line would look like this:
mysql –user=dbuser –password=dbuserpassword –host=127.0.0.1 –protocol=TCP –port=3309
If you use MySQL query browser or MySQL Workbench, the connection form requires 127.0.0.1, not localhost in connection setting dialog
Voila! Now you can connect to MySQL from the client on your host to the server running inside your Vagrant VM!