如何连接到 VirtualBox Vagrant 中的 Mysql Server？

How to connect to Mysql Server inside VirtualBox Vagrant?

提问by rizidoro

I mounted a new VirtualBox Machine with Vagrant, and inside that VM I installed Mysql Server. How can I connect to that server outside the vm? I already forward the port 3306 of the Vagrantfile , but when I try to connect to the mysql server, it`s resposts with the error: 'reading initial communication packet'

我使用 Vagrant 安装了一个新的 VirtualBox 机器，并在该 VM 中安装了 Mysql Server。如何连接到虚拟机外的服务器？我已经转发了 Vagrantfile 的 3306 端口，但是当我尝试连接到 mysql 服务器时，它重新发布了错误：'reading initial communication packet'

ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0

回答by Dror Bereznitsky

Make sure MySQL binds to 0.0.0.0 and not 127.0.0.1 or it will not be accessible from outside the machine

确保 MySQL 绑定到 0.0.0.0 而不是 127.0.0.1 否则它将无法从机器外部访问

You can ensure this by editing your my.conf file and looking for the bind-addressitem--you want it to look like bind-address = 0.0.0.0. Then save this and restart mysql:

您可以通过编辑 my.conf 文件并查找bind-address项目来确保这一点——您希望它看起来像bind-address = 0.0.0.0. 然后保存并重新启动mysql：

sudo service mysql restart

If you are doing this on a production server, you want to be aware of the security implications, discussed here: https://serverfault.com/questions/257513/how-bad-is-setting-mysqls-bind-address-to-0-0-0-0

如果您在生产服务器上执行此操作，则需要注意安全隐患，此处讨论：https: //serverfault.com/questions/257513/how-bad-is-setting-mysqls-bind-address-to -0-0-0-0

回答by Youri van den Bogert

Log in to your box with ssh [email protected] -p 2222(password vagrant)

使用ssh [email protected] -p 2222(password vagrant)登录您的盒子

Then: sudo nano /etc/mysql/my.cnfand comment out the following lines with #

然后：sudo nano /etc/mysql/my.cnf并用 # 注释掉以下几行

#skip-external-locking 
#bind-address

save it & exit

保存并退出

then: sudo service mysql restart

然后： sudo service mysql restart

Then you can connect through SSH to your MySQL server.

然后您可以通过 SSH 连接到您的 MySQL 服务器。

回答by radiohub

I came across this issue recently. I used PuPHPet to generate a config.

我最近遇到了这个问题。我使用 PuPHPet 生成配置。

To connect to MySQL through SSH, the "vagrant" password was not working for me, instead I had to authenticate through the SSH key file.

要通过 SSH 连接到 MySQL，“vagrant”密码对我不起作用，我必须通过 SSH 密钥文件进行身份验证。

To connect with MySQL Workbench

连接 MySQL Workbench

Connection method

连接方式

Standard TCP/IP over SSH

SSH 上的标准 TCP/IP

SSH

SSH

Hostname: 127.0.0.1:2222 (forwarded SSH port)
Username: vagrant
Password: (do not use)
SSH Key File: C:\vagrantpath\puphpet\files\dot\ssh\insecure_private_key
              (Locate your insercure_private_key)

MySQL

MySQL

Server Port: 3306
username: (root, or username)
password: (password)

Test the connection.

测试连接。

回答by Sarmen B.

For anyone trying to do this using mysql workbench or sequel pro these are the inputs:

对于尝试使用 mysql workbench 或 sequel pro 执行此操作的任何人，这些是输入：

Mysql Host: 192.168.56.101 (or ip that you choose for it)
username: root (or mysql username u created)
password: **** (your mysql password)
database: optional
port: optional (unless you chose another port, defaults to 3306)

ssh host: 192.168.56.101 (or ip that you choose for this vm, like above)
ssh user: vagrant (vagrants default username)
ssh password: vagrant (vagrants default password)
ssh port: optional (unless you chose another)

source: https://coderwall.com/p/yzwqvg

来源：https: //coderwall.com/p/yzwqvg

回答by AbstractVoid

Well, since neither of the given replies helped me, I had to look more, and found solution in thisarticle.

那么，既然没有给出答复的帮助我，我不得不寻找更多的研究，发现解决这个文章。

And the answer in a nutshell is the following:

简而言之，答案如下：

Connecting to MySQL using MySQL Workbench

使用 MySQL Workbench 连接到 MySQL

Connection Method: Standard TCP/IP over SSH
SSH Hostname: <Local VM IP Address (set in PuPHPet)>
SSH Username: vagrant (the default username)
SSH Password: vagrant (the default password)
MySQL Hostname: 127.0.0.1
MySQL Server Port: 3306
Username: root
Password: <MySQL Root Password (set in PuPHPet)>

Using given approach I was able to connect to mysql database in vagrant from host Ubuntu machine using MySQL Workbench and also using Valentina Studio.

使用给定的方法，我能够使用 MySQL Workbench 和 Valentina Studio 从主机 Ubuntu 机器连接到流浪中的 mysql 数据库。

回答by Rajkaran Mishra

Here are the steps that worked for me after logging into the box:

以下是登录框后对我有用的步骤：

Locate MySQL configuration file:

找到 MySQL 配置文件：

$ mysql --help | grep -A 1 "Default options"

Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

On Ubuntu 16, the path is typically /etc/mysql/mysql.conf.d/mysqld.cnf

在 Ubuntu 16 上，路径通常是 /etc/mysql/mysql.conf.d/mysqld.cnf

Change configuration file for bind-address:

更改绑定地址的配置文件：

If it exists, change the value as follows. If it doesn't exist, add it anywhere in the [mysqld] section.

如果存在，请按如下方式更改值。如果它不存在，请将其添加到 [mysqld] 部分的任何位置。

bind-address = 0.0.0.0

Save your changes to the configuration file and restart the MySQL service.

保存对配置文件的更改并重新启动 MySQL 服务。

service mysql restart

Create / Grant access to database user:

创建/授予对数据库用户的访问权限：

Connect to the MySQL database as the root user and run the following SQL commands:

以 root 用户连接 MySQL 数据库并运行以下 SQL 命令：

mysql> CREATE USER 'username'@'%' IDENTIFIED BY 'password';

mysql> GRANT ALL PRIVILEGES ON mydb.* TO 'username'@'%';

回答by DannyFeliz

This worked for me: Connect to MySQL in Vagrant

这对我有用：Connect to MySQL in Vagrant

username: vagrant password: vagrant

sudo apt-get update sudo apt-get install build-essential zlib1g-dev
git-core sqlite3 libsqlite3-dev sudo aptitude install mysql-server
mysql-client


sudo nano /etc/mysql/my.cnf change: bind-address            = 0.0.0.0


mysql -u root -p

use mysql GRANT ALL ON *.* to root@'33.33.33.1' IDENTIFIED BY
'jarvis'; FLUSH PRIVILEGES; exit


sudo /etc/init.d/mysql restart




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

Vagrant::Config.run do |config|

  config.vm.box = "lucid32"

  config.vm.box_url = "http://files.vagrantup.com/lucid32.box"

  #config.vm.boot_mode = :gui

  # Assign this VM to a host-only network IP, allowing you to access
it   # via the IP. Host-only networks can talk to the host machine as
well as   # any other machines on the same network, but cannot be
accessed (through this   # network interface) by any external
networks.   # config.vm.network :hostonly, "192.168.33.10"

  # Assign this VM to a bridged network, allowing you to connect
directly to a   # network using the host's network device. This makes
the VM appear as another   # physical device on your network.   #
config.vm.network :bridged

  # Forward a port from the guest to the host, which allows for
outside   # computers to access the VM, whereas host only networking
does not.   # config.vm.forward_port 80, 8080

  config.vm.forward_port 3306, 3306

  config.vm.network :hostonly, "33.33.33.10"


end