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!

Advertisements

Leave a comment

Filed under Software Engineering

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s