Connect to MySQL on Vagrant instance with Sequel Pro

Question

I am running Laravel on Vagrant and I am trying to connect Sequel Pro.

I have just started using Vagrant, I have followed a few tutorials on connecting to Sequel Pro however they were all unsuccessful.

Here is my Vagrant file:

# -*- mode: ruby -*-
# vi: set ft=ruby :

Vagrant.configure('2') do |config|
config.vm.hostname = 'laravel'
config.vm.boot_timeout = 3600
config.vm.box = 'debian-73-i386-virtualbox-puppet'
config.vm.box_url = 'http://puppet-vagrant-boxes.puppetlabs.com/debian-73-i386-virtualbox-puppet.box'

config.vm.network :forwarded_port, guest: 8000, host: 8000
config.vm.network :forwarded_port, guest: 8500, host: 8500

config.vm.provider :virtualbox do |vb|
vb.customize ['modifyvm', :id, '--memory', '1536']
end

config.vm.provision :puppet do |puppet|
puppet.manifests_path = 'puppet/'
puppet.manifest_file  = 'init.pp'
puppet.module_path    = 'puppet/modules/'
# puppet.options      = '--verbose --debug'
end

end

From my.cnf:

bind-address            = 127.0.0.1 

Here is my /etc/hosts

127.0.0.1       localhost
127.0.1.1       laravel
# The following lines are desirable for IPv6 capable hosts
::1     localhost ip6-localhost ip6-loopback
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters

EDIT: changed bind address to 0.0.0.0 still does not work In Sequel Pro I have

MySQL Host: 0.0.0.0
username: root
Password: (mysql password)
SSH Host 0.0.0.0
SSH User: vagrant
SSH Password: vagrant

EDIT: Here is my vagrant hosts file - etc/hosts

This is my hosts file

127.0.0.1       localhost
127.0.1.1       laravel
# The following lines are desirable for IPv6 capable hosts
::1     localhost ip6-localhost ip6-loopback
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters

Answer 1

The issue is that MySQL as you have it setup in my.cnf now can only connect via localhost within the Vagrant server:

bind-address            = 127.0.0.1 

To enable networking in MySQL, you should change that setting in my.cnf to:

bind-address            = 0.0.0.0

And then restart the MySQL service. Unsure of how that would happen in Vagrant, but in Ubuntu you would enter a command like this:

sudo service mysql restart

You might have to check your MySQL user permissions to ensure that the user within MySQL can actually be used from any IP address—sometimes they are set strictly to localhost or 127.0.0.1—as well.

As explained in the official MySQL documentation:

The server treats different types of addresses as follows:

• If the address is 0.0.0.0, the server accepts TCP/IP connections on all server host IPv4 interfaces.

• If the address is ::, the server accepts TCP/IP connections on all server host IPv4 and IPv6 interfaces. Use this address to permit both IPv4 and IPv6 connections on all server interfaces.

• If the address is an IPv4-mapped address, the server accepts TCP/IP connections for that address, in either IPv4 or IPv6 format. For example, if the server is bound to ::ffff:127.0.0.1, clients can connect using --host=127.0.0.1 or --host=::ffff:127.0.0.1.

• If the address is a “regular” IPv4 or IPv6 address (such as 127.0.0.1 or ::1), the server accepts TCP/IP connections only for that IPv4 or IPv6 address.

That said, exposing MySQL—or any database server—to the world is not advisable. But is acceptable in a case of local development like this.

So if enabling MySQL networking is not an option, you can also use the built in SSH tunneling capabilities in Sequel Pro to connect to MySQL via SSH. Details on all of the different connection types are shown on the official Sequel Pro site here. But this screenshot sums it up nicely.

Basically you just set your localhost/127.0.0.1 MySQL info as you normally would. But you also add the SSH info you would use to SSH into your server. And Sequel Pro will use that SSH connection to tunnel in & connect to MySQL seamlessly. This might be the better way to handle instead of dealing with MySQL networking & user permission issues.

For SSH tunneling in Sequel Pro you just need to do the following:

• Name: The name you want for the connection.
• MySQL Host: The IP address of the MySQL host which should be localhost or 127.0.0.1
• Username: MySQL database username.
• Password: The password connected to that MySQL database username.
• Database: Optional (database you want to connect to)
• Port: Default is 3306 so only change this if you definitely have to set to anything else.

Now here you set the SSH settings for your Vagrant install:

• SSH Host: The hostname or IP address of your Vagrant machine.
• SSH User: The SSH username to that Vagrant machine.
• SSH Password: The password connected to that SSH user.
• SSH Port: Default is 22 so only change this if you definitely have to set to anything else.

Answer 2

this is my configration:

Vagrant machine default ssh user and ssh password all are vagrant.

Answer 3

All you need to do is edit the bind address located here:

$ sudo nano /etc/mysql/my.cnf

Find the bind_address setting which will be set to 127.0.0.0 and change it to 0.0.0.0

After that restart mySQL:

$ sudo service mysql restart

The final step is just updating permissions:

$ mysql -u root -proot -e "GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION; FLUSH PRIVILEGES;"

Then connect as you normally would (changing port to what ever you set in your vagrant file).

Note there are security issues here since it's all pretty open but for dev work it's fine.

Answer 4

Try https://github.com/AlexDisler/mysql-vagrant, it lets you connect without an ssh tunnel (check out install.sh for the specific settings).

Answer 5

All the above answers didn't work for me until I have tried this:

• Connect to your vagrant instance using vagrant ssh
• When just type passwd to set a new password. Use password vagrant for consistency.

It will connect easily after that steps taken.

Thanks to @hugo at How do I Sequel Pro with PuPHPet?

Answer 6

The answer about the bind-address is right, however I have found that instead of changing the value to 0.0.0.0, it's better to just edit the my.cnf file and comment out the bind-address altogether.

If you do that and then follow the rest of the original answer, you should be able to connect via sequel pro

Answer 7

For me, the vital part was using 0.0.0.0 as MySQL host instead of 127.0.0.1. Everything else is business as usual (using SSH). This works for me:

Answer 8

Below are my screenshot for vagrant connected using mysql workbench from MAC

Answer 9

Your connection to your Vagrant box has to be made via SSH. So if you have connected before, and have made any major changes to your Vagrant box (such as a destroy/rebuild), you may have a new private key and need to refresh your IP address record in ~/.ssh/known_hosts. To do that, open the file in vim and just delete the line that starts with your vagrant box IP address.

Answer 10

One can use vagrant port to check the current port forwarding (in case one has several vagrant boxes).

Also if the case is more than one vagrant box the issue could be that a new ssh key must be added to the host's .ssh/known_hosts by:

1. commenting the first vagrant box host in the host's .ssh/known_hosts;

2. adding the second vagrant box host through the Sequel Pro test connection dialog;

3. uncommenting the first vagrant box host in the host's .ssh/known_hosts.

So both vagrant boxes (each one with it's own key) are now known.