Reputation: 331

How to connect to WSL mysql from Host Windows

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

Upvotes: 32

Answers (7)

Manikandan

Reputation: 1816

In my case, I am already running a MySQL server on my Windows machine and tried to connect to MySQL which is running from WSL. This video helped me, please check this : https://www.youtube.com/watch?v=DBsyCk2vZw4

Those who facing the issue, simply change the port in WSL. I have changed 3306 to 33061 in my.cnf file.

[mysqld]
port = 33061

And then restarted the service

sudo service mysql restart

Then

sudo update-rc.d mysql defaults

At last, when I connected with the mysql workbench it worked. Also please make sure, your user is identified by native_password.

Upvotes: 0

VeRJiL

Reputation: 564

You need these steps:

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.
add the following line to the end of file port = 33061.
save the file and exit. press esc' and then type :x` to save & exit vim.
finally run this command sudo update-rc.d mysql defaults.
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!

Upvotes: 1

Emmanuel Aliji

Reputation: 453

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!

Upvotes: 0

Andy Hoyle

Reputation: 846

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

Upvotes: 20

Fangxing

Reputation: 6115

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';

Upvotes: 9