使用 Sequel Pro 连接到 Vagrant 实例上的 MySQL

Connect to MySQL on Vagrant instance with Sequel Pro

提问by user3692109

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

我在 Vagrant 上运行 Laravel，我正在尝试连接 Sequel Pro。

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

我刚刚开始使用 Vagrant，我已经学习了一些关于连接 Sequel Pro 的教程，但是它们都没有成功。

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:

来自my.cnf：

bind-address            = 127.0.0.1 

Here is my /etc/hosts

这是我的 /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

编辑：将绑定地址更改为 0.0.0.0 仍然不起作用 在 Sequel Pro 我有

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

编辑：这是我的流浪主机文件 - 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

回答by JakeGould

The issue is that MySQL as you have it setup in my.cnfnow can only connect via localhostwithin the Vagrant server:

问题是你现在设置的 MySQLmy.cnf只能通过localhostVagrant 服务器连接：

bind-address            = 127.0.0.1 

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

要在 MySQL 中启用网络，您应该将该设置更改my.cnf为：

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:

然后重启MySQL服务。不确定在 Vagrant 中会如何发生，但在 Ubuntu 中你会输入这样的命令：

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 localhostor 127.0.0.1—as well.

您可能必须检查您的 MySQL 用户权限，以确保 MySQL 中的用户实际上可以从任何 IP 地址使用——有时它们也被严格设置为localhost或127.0.0.1。

As explained in the official MySQL documentation:

如官方 MySQL 文档中所述：

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.

服务器处理不同类型的地址如下：

• 如果地址为 0.0.0.0，则服务器接受所有服务器主机 IPv4 接口上的 TCP/IP 连接。

• 如果地址是 ::，则服务器接受所有服务器主机 IPv4 和 IPv6 接口上的 TCP/IP 连接。使用此地址允许所有服务器接口上的 IPv4 和 IPv6 连接。

• 如果地址是 IPv4 映射地址，则服务器接受该地址的 TCP/IP 连接（IPv4 或 IPv6 格式）。例如，如果服务器绑定到 ::ffff:127.0.0.1，则客户端可以使用 --host=127.0.0.1 或 --host=::ffff:127.0.0.1 进行连接。

• 如果地址是“常规”IPv4 或 IPv6 地址（例如 127.0.0.1 或 ::1），则服务器仅接受针对该 IPv4 或 IPv6 地址的 TCP/IP 连接。

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.

也就是说，向世界公开 MySQL 或任何数据库服务器是不可取的。但是在这样的本地开发的情况下是可以接受的。

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.

因此，如果启用 MySQL 网络不是一个选项，您还可以使用 Sequel Pro 中内置的 SSH 隧道功能通过 SSH 连接到 MySQL。有关所有不同连接类型的详细信息，请参见此处的 Sequel Pro 官方网站。但是这个截图很好地总结了它。

Basically you just set your localhost/127.0.0.1MySQL 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.

基本上，您只需像往常一样设置您的localhost/ 127.0.0.1MySQL 信息。但是您还要将用于 SSH 的 SSH 信息添加到您的服务器中。Sequel Pro 将使用该 SSH 连接来无缝连接到 MySQL。这可能是更好的处理方式，而不是处理 MySQL 网络和用户权限问题。

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

对于 Sequel Pro 中的 SSH 隧道，您只需要执行以下操作：

• Name:The name you want for the connection.
• MySQL Host:The IP address of the MySQL host which should be localhostor 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.

• 名称：您想要的连接名称。
• MySQL 主机：MySQL 主机的 IP 地址，它应该是localhost或127.0.0.1
• 用户名：MySQL 数据库用户名。
• 密码：连接到该 MySQL 数据库用户名的密码。
• 数据库：可选（您要连接的数据库）
• 端口：默认为 3306，因此只有在您必须设置为其他任何内容时才更改它。

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

现在在这里你为你的 Vagrant 安装设置 SSH 设置：

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

• SSH 主机：您的 Vagrant 机器的主机名或 IP 地址。
• SSH 用户：该 Vagrant 机器的 SSH 用户名。
• SSH 密码：连接到该 SSH 用户的密码。
• SSH 端口：默认为 22，因此只有在您必须设置为其他任何内容时才更改它。

回答by pangpang

this is my configration:

这是我的配置：

Vagrant machine default ssh userand ssh passwordall are vagrant.

Vagrant 机器默认的ssh 用户和ssh 密码都是vagrant。

回答by Mediabeastnz

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

找到将设置为 127.0.0.0 的 bind_address 设置并将其更改为 0.0.0.0

After that restart mySQL:

之后重启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).

然后像往常一样连接（将端口更改为您在 vagrant 文件中设置的端口）。

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

请注意，这里存在安全问题，因为它都非常开放，但对于开发工作来说很好。

回答by Keeprock

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 passwdto set a new password. Use password vagrantfor consistency.

• 使用连接到您的 vagrant 实例 vagrant ssh
• 当只需键入passwd以设置新密码时。使用密码vagrant保持一致性。

It will connect easily after that steps taken.

采取这些步骤后，它将轻松连接。

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

感谢@hugo 在我如何使用 PuPHPet 续集 Pro？

回答by Alex

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

试试https://github.com/AlexDisler/mysql-vagrant，它可以让你在没有 ssh 隧道的情况下进行连接（查看 install.sh 了解具体设置）。

回答by jose mera

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.

关于绑定地址的答案是正确的，但是我发现与其将值更改为 0.0.0.0，不如编辑 my.cnf 文件并完全注释掉绑定地址。

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

如果您这样做，然后按照原始答案的其余部分进行操作，您应该可以通过 sequel pro 进行连接

回答by Lukas

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:

对我来说，重要的部分是使用 0.0.0.0 作为 MySQL 主机而不是 127.0.0.1。其他一切都照常进行（使用 SSH）。这对我有用：

回答by AbdulBasit

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

下面是我使用 mysql 工作台从 MAC 连接的 vagrant 的屏幕截图

回答by ctlockey

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.

您必须通过 SSH 连接到 Vagrant box。所以如果你之前已经连接过，并且对你的 Vagrant box 做了任何重大改变（比如销毁/重建），你可能有一个新的私钥，需要刷新你的 .ip 中的 IP 地址记录~/.ssh/known_hosts。为此，请在 vim 中打开该文件，然后删除以您的 vagrant box IP 地址开头的行。

回答by obotezat

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

可以vagrant port用来检查当前的端口转发（以防有几个 vagrant 框）。

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_hostsby:

此外，如果情况不止一个 vagrant box，则问题可能是必须通过以下方式将新的 ssh 密钥添加到主机.ssh/known_hosts：

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

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

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

1. 征求意见的第一流浪汉信箱主机到主机的.ssh/known_hosts;

2. 通过 Sequel Pro 测试连接对话框添加第二个 vagrant box 主机；

3. 在取消对第一个无业游民信箱主机到主机的.ssh/known_hosts。

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

所以这两个流浪盒子（每个都有自己的钥匙）现在都知道了。