Vagrant port forwarding for Mysql
Solution 1
was finally able to make it work -
edit the /etc/mysql/my.cnf
file and make sure, either
- you have
bind-address = 0.0.0.0
- or you comment the line
#bind-address ...
You may need to add it to the mysqld section of the my.cnf file:
[mysqld]
bind-address = 0.0.0.0
make sure to restart your mysql server after the change
$ sudo service mysql restart
Then you can connect from your host - so I first had an error like
$ mysql -h127.0.0.1 -P 3309 -uroot -p
Enter password:
ERROR 1130 (HY000): Host '172.16.42.2' is not allowed to connect to this MySQL server
so I came back to the guest and did
vagrant@precise64:~$ mysql -h127.0.0.1 -uroot -p
...
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'172.16.42.2' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
Then I had no issue to connect from the host machine
$ mysql -h127.0.0.1 -P 3309 -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 36
Server version: 5.5.44-0ubuntu0.12.04.1 (Ubuntu)
Solution 2
The first answer is right but not enough.when I connect MySQL, I get a error:
Host '10.0.2.2' is not allowed to connect to this MySQL server
Solution:
create user 'root'@'10.0.2.2' identified by 'password';
grant all privileges on . to 'root'@'10.0.2.2' with grant option;
flush privileges;
aha, all problems are solved,
Solution 3
Personally I don't bother with modifying MySQL for development Vagrant boxes - it's time consuming and difficult to script in a provisioner meaning you have to do it by hand every time you vagrany destroy
or a new developer starts contributing. Instead, I connect via SSH Tunnel which is made super easy using Vagrant's generated private_key
file. No additional post-install tweaking necessary.
Follow these steps to SSH Tunnel with SequelPro, MySql Workbench, or any other client that supports SSH connectivity:
- Choose SSH connection option
- The "Host" in SSH mode becomes localhost or specifically
127.0.0.1
(more predictable cross-os) - The username/password is the database username/password. For development you can just use
root
and the password is defined/created in the provisioner (see snippet below) - The SSH username is
Vagrant
- No SSH password - just use the
private_key
for the Vagrant machine instead, located in.vagrant/machines/default/virtualbox
in the root of your VM project; note that on most OS's this directory, and all starting with a.
are hidden
To automate installation and root password creation, add this to your Vagrant provisioner script file (config.vm.provision
in Vagrantfile), commonly named provisioner.sh
:
debconf-set-selections <<< 'mysql-server mysql-server/root_password password SuperSecretPasswordHere'
debconf-set-selections <<< 'mysql-server mysql-server/root_password_again password SuperSecretPasswordHere'
apt-get install -y mysql-server
Hope this helps save someone else some time!
Alex Blex
Updated on June 09, 2022Comments
-
Alex Blex almost 2 years
I am trying to setup port forwarding in Vagrantfile to connect to guest mysqld from host system, but get
reading initial communication packet
error. Host: Yosemite, Guest: Trusty, vagrant 1.7.4Vagrantfile(host):
config.vm.network "forwarded_port", guest: 80, host: 8080 config.vm.network "forwarded_port", guest: 3306, host: 3309
my.ini(guest):
bind-address = 127.0.0.1
8080 forwarding works like a charm.
mysql -h127.0.0.1 -uroot -p
from guest also works.mysql -h127.0.0.1 -P 3309 -uroot -p
from host results withreading initial communication packet
error.When I telnet from host, the connection instantly closes:
$ telnet localhost 3309 Trying 127.0.0.1... Connected to localhost. Escape character is '^]'. Connection closed by foreign host.
Port forwarding works when I ssh to vagrant box from host:
$ssh [email protected] -p 2222 -L3308:localhost:3306
Then I can connect from host
mysql -h127.0.0.1 -P3308 -uroot -p
without problems, which I use as a temporary workaround. -
Alex Blex over 8 yearsThanks Frederic. It also works with
bind-address = 10.0.2.15
in my.cnf. -
Alex Blex over 8 yearsApparently, virtualbox uses this interface for port forwarding. I couldn't find a way to forward ports to lo interface other than ssh to the box, which is not very convenient, yet kinda make sense.
-
Frederic Henri over 8 yearsright with VirtualBox, Vagrant requires the first network device attached to the virtual machine to be a NAT device. The NAT device is used for port forwarding, which is how Vagrant gets SSH access to the virtual machine.
-
Cesc almost 7 yearsI want to add that it kind of took me some time for it to work (this answer is good). (at first I would get "ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 2" even when I've got all the configuration settings "properly", I gave up so I decided to use the ssh forward tunnel workaround, which I could finally made it work, but by then, I tried again directly mysql to teh port, without tunnel, and it worked... So others, maybe just be patient, reload vagrant and mysql several times before giving up.