25

I am trying to connect HeidiSql from the host to my WSL Mysql but I could not get it to connect it

Error "can't connect to Mysql server on '127.0.0.1'"

Tried SSH too but could not connect to the server

enter image description here

common sense
  • 3,775
  • 6
  • 22
  • 31
joseph emmanuel
  • 261
  • 1
  • 3
  • 4
  • I finally figured out that I need to upgrade MySQL Workbench from 6 to 8 since WSL 1 had MySQL 8 installed: https://stackoverflow.com/a/67963231/470749 – Ryan Jun 13 '21 at 23:01

6 Answers6

12

I'm also hosting mysql-server on WSL and running MySQL workbench on Windows.

I had to get the IP inside of WSL ifconfig

And use this IP in MySQL Workbench enter image description here

Shige
  • 176
  • 2
  • 6
  • For any future readers, you're looking for the `flags=4163` config – 9 Guy Apr 05 '21 at 18:32
  • 1
    What I think "9 Guy" means is that `ifconfig`'s output will often show *multiple* IP addresses, and the way to figure out which one to use is to look for the line after the line containing `flags=4163`. – Ryan Jun 13 '21 at 22:37
  • 3
    another alternative: `wsl hostname -I` to get WSL IP's from cmd. – Permana Aug 04 '21 at 01:41
12

By default, MySql only listens on 127.0.0.1. If you are connecting using the IP address returned by wsl hostname -I (as mentioned by Permana) then you need to change /etc/mysql/mysql.conf.d/mysqld.cnf to listen on all IPs or your specific IP.

Edit your MySql config by typing this in your Ubuntu instance: sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Change the line: bind-address = 127.0.0.1

To: bind-address = 0.0.0.0

or change 0.0.0.0 to the IP returned by wsl hostname -I.

Restart MySql using: sudo service mysql restart

Andy Hoyle
  • 668
  • 6
  • 8
  • Just wanted to mention that this did not work. I did change my mysql's bind config to `0.0.0.0`. Also, the IP reported by ifconfig is different from the one reported by `wsl hostname -I`. Connecting to the machine IP given by ifconfig worked though. EDIT: Nevermind, I used `wsl hostname -i` instead of `-I` (notice the capital I) – ThatAnonyG Nov 06 '22 at 23:13
8

Since your question is asked before WSL2 release, I assume you were using WSL1.

For WSL1

You can access WSL1 MySQL directly from Windows, but you were attempting access in a wrong way.

In the Network type, you should choose MariaDb or MySQL(TCP/IP) instead of MySQL (SSH Tunnel).

For WSL2

Check this WSL github issue. Save @edwindijas's powershell script and execute it by administrator. If you still cannot access MySQL and got access denied for user ... <you-computer-name>.mshome.net, you need to allow this user access from this host.

For example: let's say root, you need execute this in mysql cli:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%<you-computer-name>.mshome.net' IDENTIFIED BY '<password>';

Or allow root user access WSL2 MySQL from any host:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password';

Ryan
  • 22,332
  • 31
  • 176
  • 357
Fangxing
  • 5,716
  • 2
  • 49
  • 53
  • 6
    `ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY 'mypassword'' at line 1` – Unnikrishnan Jul 19 '21 at 21:21
  • 1
    on Mysql 8 Use CREATE USER instead, followed by the GRANT statement https://stackoverflow.com/questions/54377052/how-to-connect-to-wsl-mysql-from-host-windows – Undigo Factory Apr 27 '22 at 15:47
  • If you are getting the syntax error as mentioned above, try this: `GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'localhost'`. Once I had done this, Windows Workbench was able to connect as root to my WSL2 MySQL server install – charlesdeb Jul 16 '22 at 19:31
5

As for me who's also using WSL for making web based application

first make sure mysql is running on WSL like sudo service mysql start

then once started, open HeidiSql and simply connect to it, here the example on my part

enter image description here

make sure the IP address is 127.0.0.1 not any IP, not your IP used to connect on the internet

Fil
  • 8,225
  • 14
  • 59
  • 85
  • 1
    this one works for me, unlike the other answer – cikatomo Apr 12 '21 at 23:07
  • 1
    @cikatomo Are you using WSL 1 or WSL 2? I can't get 127.0.0.1 (or anything else) to work for me on WSL 1. – Ryan Jun 13 '21 at 22:38
  • @Ryan I am using WSL1. I haven't use this setup in a while tho. Like few months – cikatomo Jun 14 '21 at 01:22
  • 1
    @cikatomo Thanks for your response. I finally figured out that I need to upgrade MySQL Workbench from 6 to 8 since WSL 1 had MySQL 8 installed: https://stackoverflow.com/a/67963231/470749 – Ryan Jun 14 '21 at 14:28
  • I was having problems connecting to the WSL MySQL, and this worked, but I needed to disable my windows MySQL80 services and stop it by restating or smt else. – zhyp Sep 29 '21 at 11:57
0

Configure MYSQL

First of all, make sure your mysql/ mariadb is bound to the interface bind-address = 0.0.0.0. The config file can be edited by sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf while mariadb is sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf.

Connect through client software

For your client, simply use localhost as your host don't use an ip address!

.

Emmanuel Aliji
  • 377
  • 3
  • 9
0

You need these steps:

  1. open the file "sudo vim /etc/mysql/my.cnf", press i to enter the insert mode and use arrow keys to go to bottom of the file.
  2. add the following line to the end of file port = 33061.
  3. save the file and exit. press esc' and then type :x` to save & exit vim.
  4. finally run this command sudo update-rc.d mysql defaults.
  5. now you can open your client(Navicat) and add a new connection. for the Host put localhost and for the port put 33061, if you are using username or password please provide those too!
VeRJiL
  • 415
  • 4
  • 13