How to install and configure MySQL server inside Ubuntu

Install and configure MySQL into Ubuntu

In this tutorial, I am going to show you how to install and configure MySQL server inside Ubuntu OS. Besides, you will learn how to access MySQL server data from outside the server with the help of the MySQL client application. Here I am using a newly created clean Digital Ocean Droplet inside install Ubuntu 22.04 OS.

Step 1: Open the droplet console and run these commands inside the console, press yes, then keep the local version, ok and finally restart the server.

sudo apt update && sudo apt upgrade

sudo reboot

Step 2: Install MySQL server with the following command and follow instructions

sudo apt install mysql-server

Step 3: Now check Mysql server is wokring properly or not with this command

systemctl status mysql --no-pager -l

Step 4: Enter inside mysql form console with root access. Here user root 
and default password is password
sudo mysql -u root -p

Step 5: Update mysql root password with following command
ALTER USER 'root'@'localhost' IDENTIFIED WITH 
mysql_native_password BY 'new_mysql_password';

Step 6:  To Secure MySQL Server follow these steps
Run the following command
sudo mysql_secure_installation

The script will ask these questions. Follow instructions like below
Enter the password for user root: type your set password and then press ENTER.
Change the password for root? Press N, then ENTER.
Remove anonymous users? Press Y, then ENTER.
Disallow root login remotely? Press Y, then ENTER.
Remove the test database and access to it? Press Y, then ENTER.
Reload privilege tables now? Press Y, then ENTER.

Now you can create a database for your application with the following commends 
inside mysql console.
CREATE DATABASE yourdb;
CREATE USER 'youruser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON yourdb.* to 'youruser'@'localhost';
FLUSH PRIVILEGES;
quit;

Some basic MySQL commands are given below:
It will show all databases name
Show Databases; 
It will select a database 
Use DatabaseName; 
It will show all tables inside selected database 
Show tables;

It will display all data from users table 
Select * From users;  
It will create a new database name test_db
Create DATABASE test_db; 

It will show all database name 
Show Databases;

It will create a new user
CREATE USER 'test_user'@'localhost' 
IDENTIFIED BY 'test_user_password';

It will provide grand privilage to test_user to test_database
GRANT ALL PRIVILEGES ON test_db.* To 'test_user'@'localhost';

It will export data from Mysql to a directory where this command executed and 
this command is executed outside mysql console. Here db_backup.sql has 
been generated
mysqldump -u root -p test_db > db_backup.sql

It will import data from a backup.sql file to test_db mysql database 

mysql -u root -p test_db < db_backup.sql

Connect mysql from outside server with mysql client. 
Open MySQL client application and put all required values from droplet 
and connect.



 
Step 7: After successfull connection you will get a success message. Then enter 
inside MySQL client application and you will see interface like below from where 
you can access your databases.