Getting started with MySQL in WSL 2

MySQL

MySQL is an open-source database management system. It uses a relational database and SQL (Structured Query Language) to manage its data.

WSL 2

The Windows Subsystem for Linux lets developers run a GNU/Linux environment including most command-line tools, utilities, and applications directly on Windows, unmodified, without the overhead of a traditional virtual machine or dualboot setup. You can install by follwoing instruction on WSL 2.

Install MySQL on Ubuntu 20.04 inside WSL 2

To install MySQL, update the package index on Ubuntu with apt:

sudo apt update

Then install the default package:

sudo apt install mysql-server

Configure MySQL:

sudo mysql_secure_installation

It will prompt to set root user password and other options, You select default option or change based on your need.

Adjustig MySQL user authentication

In case you wish to connect MySQL from external program or MySQL Workbench from window (we will start later). In order to use a password to connect to MySQL as root, you will need to switch its authentication method from auth_socket to mysql_native_password. To do this, open up the MySQL prompt from your terminal:

sudo mysql

Check user authentication method for each of MySQL user accounts with the following command:

SELECT user,authentication_string,plugin,host FROM mysql.user;

In output, you can see that the root user does in fact authenticate using the auth_socket plugin. To configure the root account to authenticate with a password, run the following ALTER USER command.

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Then, run FLUSH PRIVILEGES which tells the server to reload the grant tables and put your new changes into effect:

FLUSH PRIVILEGES;

Verify the authentication method for users again.

SELECT user,authentication_string,plugin,host FROM mysql.user;

Exit MySQL shell

exit

Create new user

Login to MySQL shell using root

mysql -u root -p

Create new user with password

CREATE USER 'raj'@'localhost' IDENTIFIED BY 'password';

Grant priviledges to newly created user.

GRANT ALL PRIVILEGES ON *.* TO 'raj'@'localhost' WITH GRANT OPTION;

Exit MySQL shell.

Start MySQL in WSL 2

Run following command in shell prompt

sudo /etc/init.d/mysql start

Install MySQL Workbench

Download MySQL Workbench as per operating system.
MySQL Workbench
Installation is simple, follow the on screen steps and once done, connect to database using the user details.