So recently at my internship, I had to set up MySQL on my local WSL system. It was a nice and easy process although I had to shift between 2-3 articles. So in this article, I would sum it up to help you seamlessly install and set up MySQL on your WSL/WSL2 system. Let's get started.
Step 1 Installing MySQL
NOTE: This article assumes that you've Ubuntu as your wsl distro.`
So start by fetching the latest updates and upgrades
sudo apt update && sudo apt upgrade
Then continue by installing the Mysql-Server package
sudo apt install mysql-server
Confirm your installation by checking the version number via the command
mysql --version
The output should be like this
mysql Ver 8.0.30-0ubuntu0.20.04.2 for Linux on x86_64 ((Ubuntu))
Step-2 Starting and Setting it Up
Now we would start the MySQL server. On WSL it can be achieved using the following command
sudo /etc/init.d/mysql start
Now on older tutorials, you would be told to run the security script mysql_secure_installation. But this would lead to a recursive loop which can only be exited after closing the terminal. The reason for that is when you execute the mysql_secure_installation script, the script will try to set up the password for the installation's root MySQL account but, by default on Ubuntu installations, the account is not configured to connect using a password.
So how do we do that.
First, we open the MySQL prompt
sudo mysql
Then we change the root user's authentication method to the one that uses a password. This is achieved via the following command.
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
After this exit the MySQL prompt
exit
Following that we can run, the mysql_secure_installation script without any issues. So we'll do that
sudo mysql_secure_installation
Securing the MySQL server deployment.
Connecting to MySQL using a blank password.
VALIDATE PASSWORD COMPONENT can be used to test passwords and improve security. It checks the strength of the password and allows the users to set only those passwords which are secure enough. Would you like to set up VALIDATE PASSWORD component?
Press y|Y for Yes, any other key for No: n
Please set the password for root here.
New password: root
Re-enter new password: root
By default, a MySQL installation has an anonymous user, allowing anyone to log into MySQL without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment.
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.
Note: Normally, root should only be allowed to connect from ’localhost’. This ensures that someone cannot guess the root password from the network.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : n
… skipping.
By default, MySQL comes with a database named ’test’ that anyone can access. This is >also intended only for testing, and should be removed before moving into a production >environment.
Remove the test database and access to it? (Press y|Y for Yes, any other key for No) : n
… skipping.
Reloading the privilege tables will ensure that all changes made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.
All done!
NOTE: This is okay for local development but makes sure to use a strong password and allow no external access to a production server.
Step-3 Changing back to default authentication (optional)
Now you can access your MySQL server using
mysql -u root -p
Followed by entering the password for the root user. This is completely fine but just in case you want to go back to the default authentication method, this can be done as follows Enter the mysql prompt
mysql -u root -p
Then run
ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket;
Now it's back to default and you can use mysql via the command
sudo mysql
Step-4 Starting MySQL automatically when WSL starts
So by default MySQL doesn't start when you start WSL, you can manually start it via the command
sudo service mysql start
But to automatically start it whenever you start WSL you can run the following command
sudo update-rc.d mysql defaults
You can also check if the MySQL service is running via the command
sudo service mysql status
Conclusion
I hope this tutorial helped you setup MySQL on your local WSL enviornment. You can use the same procedure to install it on the server just use this
sudo systemctl start mysql.service
To start the service.