Accessing MySQL Instance In Vagrant VM

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:

shell provisioner:

mysqladmin -u root password root

mysql --user=root --password=root --host=localhost --port=3306 < /vagrant/mysql_boostrap.sql

mysql_bootstrap.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

# skip-external-locking

# bind-address

Connect

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!