[Howto]: Connect laravel in docker with MySQL on host OS

[wcaaan]

Have you already checked elsewhere?

• I have read the Devilbox documentation
• I have checked the Devilbox Troubleshooting Guide
• I have checked the Devilbox FAQ
• I have checked the Devilbox Howto
• I have checked the Devilbox Forum
• I have checked existing issues
• I have googled already with no luck
• I have not done any of the above

What are you struggling with?

OS / ENVIRONMENT:
Host operating system and version: MacOS Monterey 12.5.1
Docker desktop version: 4.12.0 (85629)
Docker desktop engine: Engine: 20.10.17
Docker desktop compose version: v2.10.2

I'm trying to connect the laravel app inside docker with MySQL on my host machine.

What have you tried already?

On my host machine MySQL is running is connected with following credentials.

Host OS MySQL
MySQL version: 8.0.27
Host: 127.0.0.1
Port: 3307
Password: No password

** Devilbox .env file:**

Expose MySQL Port to Host

HOST_PORT_MYSQL=3306

Laravel .env file
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=hanger
DB_USERNAME=root
DB_PASSWORD=

If i run docker-compose up -d php httpd mysql everything work fine i'm able to connect to database via devilbox phpmyadmin.

As per the documentation https://devilbox.readthedocs.io/en/latest/corporate-usage/use-external-databases.html#database-on-host-os i ran this command docker-compose up -d php httpd bind but i unable to connect to the database from inside the docker to host OS MySQL.

What is your goal?

I want to connect the laravel app in docker with MySQL on my host machine.

[vikas5914]

@wcaaan I am confused here. If your Host Mysql is running on port 3307, why are you using port 3306 in Laravel .env.

[wcaaan]

I tried it with 3307 as well. it does not connect.

[wcaaan]

I tried it with 3307 as well. it does not connect. Are you implying that this configuration is right and only port is wrong ? if that's the case it means there is something else wrong.

[vikas5914]

@wcaaan did you set HOST_PORT_MYSQL or DB_PORT port to 3307?

[wcaaan]

i tried both.

[vikas5914]

@wcaaan Remove HOST_PORT_MYSQL from the Devilbox .env. make sure that Devilbox's MySQL container is stopped.

Now set your host's MySQL to some random port like 15001 and double check that you can connect to that MySQL via some 3rd party software like MySQL Workbench.

Then set Laravel's DB_PORT to your host's Mysql port and try.

If still having issues, please provide the log file of Laravel or Laravel screenshot error.

[wcaaan]

okay thankyou will check and get back to you.

[wcaaan]

Here is what id did

1: There are no containers running so i removed the devilbox .env port HOST_PORT_MYSQL=

2: I changed the port of my host OS MySQL to 15001 and using sequel ace i successfully connected to mysql with these credentials
Host: 127.0.0.1
user: root
database: hanger
port: 15001

3: Now i set Laravel .env MySQL connection to this
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=15001
DB_DATABASE=hanger
DB_USERNAME=root
DB_PASSWORD=

4: now i ran this command docker-compose up -d php httpd bind

5: now i open laravel application try to connect and it say SQLSTATE[HY000] [2002] Connection refused (SQL: select * from `users` where `email` = superadministrator@app.com and `users`.`deleted_at` is null limit 1)

6: The laravel log file inside project/storage/logs/laravel.log is completely empty there is not a single line in it.

Any suggestion ??

[wcaaan]

I even tried this as well, in laravel .env i set the DB_HOST=host.docker.internal as per the documentation https://devilbox.readthedocs.io/en/latest/advanced/connect-to-host-os.html and throw me this error SQLSTATE[HY000] [2002] php_network_getaddresses: getaddrinfo failed: Name or service not known (SQL: select * from `users` where `email` = superadministrator@app.com and `users`.`deleted_at` is null limit 1)

[vikas5914]

@wcaaan, you can do one thing. In the host's Mysql, Instead of 127.0.0.1, use the local IP (it should be like 192.168.X.X) and Make sure the host's MySQL is bind to the local intranet IP.

[wcaaan]

I checked my IP in system preferences > network > My wifi connection > advanced > TCP/IP > under IPv4 192.168.43.182

DB_CONNECTION=mysql
DB_HOST=192.168.43.182
DB_PORT=15001
DB_DATABASE=hanger
DB_USERNAME=root
DB_PASSWORD=

This time it given me this error which may lead to something.

SQLSTATE[HY000] [1130] Host 'wcan-mbp' is not allowed to connect to this MySQL server (SQL: select * from `users` where `email` = superadministrator@app.com and `users`.`deleted_at` is null limit 1)

[wcaaan]

How do i make this sure Make sure the host's MySQL is bind to the local intranet IP. ?

[vikas5914]

@wcaaan check this to slow the not allowed to connect to this server https://stackoverflow.com/questions/11223235/mysql-root-access-from-all-hosts

[wcaaan]

Okay, @vikas5914 I really appreciate your help, time and guidance. This is how i did it, the application is CONNECTED 😄 but there is still one problem 😟 and i cannot figure out. Why host.docker.internal is NOT resolving my IP from docker ?

1: docker-compose down (Delete all the containers)

2: I removed the devilbox .env port HOST_PORT_MYSQL=

3: I changed the port of my host OS MySQL to 3306 and using sequel ace i successfully connected to mysql with these credentials
Host: 127.0.0.1
user: root
database: hanger
port: 3306

4: In order to connect from docker to my Host OS MySQL i had to edit my my.cnf file OR in this case created a new one for MySQL here the my.cnf

[mysqld]
bind_address = 0.0.0.0 # default is 127.0.0.1 Change to 0.0.0.0 to allow remote connections

5: Restarted the MySQL server and confirmed that MySQL can now listen to all IP's and NOT just localhost

6: use this command

netstat -anp tcp | grep 3306 OR netstat -ap tcp | grep -i "listen"

tcp4 0 0 127.0.0.1.3306 127.0.0.1.52469 ESTABLISHED
tcp4 0 0 127.0.0.1.52469 127.0.0.1.3306 ESTABLISHED
tcp4 0 0 127.0.0.1.3306 127.0.0.1.52468 ESTABLISHED
tcp4 0 0 127.0.0.1.52468 127.0.0.1.3306 ESTABLISHED
tcp4 0 0 127.0.0.1.3306 127.0.0.1.52464 ESTABLISHED
tcp4 0 0 127.0.0.1.52464 127.0.0.1.3306 ESTABLISHED
tcp4 0 0 *.3306 . LISTEN
tcp46 0 0 *.33060 . LISTEN
tcp4 0 0 192.168.18.190.3306 192.168.18.190.52566 TIME_WAIT
tcp4 0 0 192.168.18.190.3306 192.168.18.190.52567 TIME_WAIT
tcp4 0 0 192.168.18.190.3306 192.168.18.190.52568 TIME_WAIT

7: Once its confirmed that 3306 is listeing need to create a MySQL user which would be connected from other than localhost

8: In mysql shell execute these quesies, since I'm using MySQL 8.0.27 the creating user and granting previliges must be in seperate queries.

CREATE USER 'root'@'%' IDENTIFIED BY 'root'; // remember this root password we will use it in Laravel .env
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

9: To make sure the root@% user is created type

SELECT User, Host FROM mysql.user; you should be seeing two root users one with host set to localhost and second one will be %

10: Now its time to Edit Laravel .env MySQL section

DB_CONNECTION=mysql
DB_HOST=192.168.18.190
DB_PORT=3306
DB_DATABASE=hanger
DB_USERNAME=root
DB_PASSWORD=root

Note: my DB_HOST did not work with 127.0.0.1 OR host.docker.internal so i thought it may work with my local IP, which it did.

11: To find out your local IP on MAC go to system preferences > network > My wifi connection > advanced > TCP/IP > under IPv4 192.168.43.182

The thing I'm concerned about is that my local IP keep changing, and as per the documentation The following sections will give you the IP address and/or the CNAME where the host os can be reached from within a container. https://devilbox.readthedocs.io/en/latest/advanced/connect-to-host-os.html#docker-18-03-0-ce-and-docker-compose-1-20-1 The docker should be able to connect through host.docker.internal to my Host machine, which it does not and i don't know why. @vikas5914 Can you please point me in the direction what should i do to figure out this issue ?

[vikas5914]

@wcaaan I think it is easier to get a static local IP. You google for Router and How to set a Static IP for the MAC address.

I also have a question. Why are you using MySQL from the Devilbox?

[wcaaan]

okay, I'll google it, but that i not the permenant solution to the problem, if the doucmentation says that host.docker.internal should resolve to IP, why is it not working my system. I'm using static MySQL from devilbox because i cannot setup all the databases deperately, I already have around 40 databases on different versions of MySQL, if i use devilbox MySQL for one project i have to setup second one and third one, the thing i don't know is if i can run all MySQL;s at the same time, so i'm better off with using host OS MySQL. if you have a solution to my problem on how to use run all the MySQL's of all different project at once i'm open to suggestion.

[vikas5914]

@wcaaan Try this, ping host.docker.internal from the devil box and see which IP it resolves. And then use the IP in the Laravel env.

My note: You should look and try to upgrade the database so the majority can be used on the same version. At least try to make it so that you will need only two different MySQL versions. That is more possible than running different kinds of MySQL versions at once.

[wcaaan]

Again as usual an error.

ping host.docker.internal
ping: host.docker.internal: Name or service not known

[vikas5914]

Inside the docker, run this: cat /etc/hosts