Connect to MySQL from outside vagrant

Question

I would like to connect with mycli to the MySQL server running inside a vagrant instance.

My basic Vagrantfile looks like the following code snippet:

Vagrant.configure(VAGRANTFILE_API_VERSION) do |config|
  config.ssh.shell = "bash -c 'BASH_ENV=/etc/profile exec bash'"

  config.vm.box = "debian/wheezy64"

  config.vm.provision :shell, path: "misc/bootstrap.sh"
  config.vm.network "forwarded_port", guest: 80, host: 8082

  config.vm.synced_folder ".", "/var/www/",
    owner: "vagrant",
    group: "www-data",
    mount_options: ["dmode=775,fmode=664"],
    create: true

  config.vm.provider "virtualbox" do |vb|
      vb.memory = 1536
      vb.cpus = 1
  end
end

To install the neccessary software I am using the following code snippet:

echo "mysql-server mysql-server/root_password password $DBPASSWD" | debconf-set-selections
echo "mysql-server mysql-server/root_password_again password $DBPASSWD" | debconf-set-selections

echo "phpmyadmin phpmyadmin/dbconfig-install boolean true" | debconf-set-selections
echo "phpmyadmin phpmyadmin/app-password-confirm password $DBPASSWD" | debconf-set-selections
echo "phpmyadmin phpmyadmin/mysql/admin-pass password $DBPASSWD" | debconf-set-selections
echo "phpmyadmin phpmyadmin/mysql/app-pass password $DBPASSWD" | debconf-set-selections
echo "phpmyadmin phpmyadmin/reconfigure-webserver multiselect none" | debconf-set-selections

sudo apt-get install -y apache2 libapache2-mod-php5 mysql-server php5-mysql php5 phpmyadmin php5-cli php5-curl vim
sudo ln -s /etc/phpmyadmin/apache.conf /etc/apache2/sites-available/phpmyadmin.conf
sudo ln -s /etc/apache2/sites-available/phpmyadmin.conf /etc/apache2/sites-enabled/phpmyadmin.conf

sudo a2enmod rewrite
sudo a2enmod php5
sudo cp /var/www/misc/apache2.conf /etc/apache2/sites-available/default

sudo service apache2 restart

mysql -uroot -p$DBPASSWD -e "CREATE USER '$DBUSER'@'localhost' IDENTIFIED BY '$DBPASSWD';GRANT ALL ON *.* TO '$DBUSER'@'localhost';FLUSH PRIVILEGES;CREATE DATABASE $DBNAME;"
mysql -u$DBUSER -p$DBPASSWD $DBNAME < /var/www/db.sql

I think my problem is the user credentials which I am storing into the database. But I couln't figure out answer.

you can review http://stackoverflow.com/questions/33146223/vagrant-port-forwarding-for-mysql/33172400#33172400 — Frederic Henri, Nov 30 '15 at 21:30
$DBUSER and $DBPASSWD are not shown, but probably if they are root and empty, it'll probably work, right? — Rein Baarsma, Nov 30 '15 at 21:33

score 2 · Answer 1 · edited Sep 20 '20 at 00:12

2

If you are connecting from outside of the vm, you'll need to add a port mapping that listens on the host and forwards requests to the guest. Similar to the one defined for http here:

config.vm.network "forwarded_port", guest: 80, host: 8082

For mysql (assuming your mysql listens on the default port):

config.vm.network "forwarded_port", guest: 3306, host: 33060

After, modifying the Vagrant file, vagrant reload.

Then when connecting, specify the port

mysql -P 33060 -u user -p database

edited Sep 20 '20 at 00:12

lborgav

2,371
19
28

answered Nov 30 '15 at 21:33

JRD

1,957
1
12
17

I had added the port mapping for mysql and tried to connect with your command `mysql -P 33600 -umyuser -pmypw mydb` and get the following error: `ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)` – Bene Nov 30 '15 at 21:48
You may need to restart the vm, not just provision. Did you try `vagrant reload`? – JRD Nov 30 '15 at 21:54
yeah I did `destroy` and `up` but still same error :/ – Bene Nov 30 '15 at 21:55
Try with `mysql --protocol=TCP ...` – JRD Nov 30 '15 at 21:59
Then I get the error `ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0` – Bene Nov 30 '15 at 22:01
Check if you have firewall rules enabled for the host or guest that would prevent the connection. – JRD Nov 30 '15 at 22:13
Default MySQL port is 3606, not 3660 – Paulin Trognon Feb 09 '20 at 18:24

score 1 · Answer 2 · answered Dec 01 '15 at 08:41

on top of what JRD said on the port forwarding, you need to make sure mysql listens on all port and not just locally

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

make sure to restart your mysql server after the change

$ sudo service mysql restart

Then you can connect from your host - In case you get the following error

$ mysql -h127.0.0.1 -P 33600 -uroot -p
Enter password:
ERROR 1130 (HY000): Host '172.16.42.2' is not allowed to connect to this MySQL server

Then came back to the guest and do

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 you should have no issue to connect from the host machine

$ mysql -h127.0.0.1 -P 33600 -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)

still getting the error `ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0` and entry in hosts.deny and I did `ALL:ALL` in hosts.allow — Bene, Dec 01 '15 at 18:20

Connect to MySQL from outside vagrant

2 Answers2

Linked