What is MySQL?
MySQL is an open-source relational database management system. Its name is a combination of "My", the name of co-founder Michael Widenius's daughter My, and "SQL", the abbreviation for Structured Query Language.
MySQL is the most popular open-source relational database management system supported by a huge and active community of open-source developers. It is available on over 20 platforms and operating systems, including Linux, Unix, Mac, and Windows.
MySQL is based on Client and Server Architecture. Here, the Client machine requests specific data by querying using Structured Query Language (SQL) and the server responds with the desired output to the client machine after processing the request. MySQL server is multithreaded, multitasking, and is designed to work also on heavy-load production systems. It has both transactional and non-transactional engines.
Why is MySQL so popular?
Among various databases available out there, MySQL is the most popular Database Management System that uses Structured Query Language. The one reason for its popularity is, it is open-source software that is freely available. And MySQL is flexible enough to also work in a dynamic environment. MySQL is faster than other databases and it is platform-independent and compatible with many programming languages like C++, Java, Python, Javascript, PHP, Ruby, etc. The community of MySQL is very large, so if you are stuck anywhere then you can get help from the community.
Features Of MySQL
- Open-Source
- Quick and Reliable
- Scalable
- Data Types
- Character Sets
- Secure
- Support for large Databases
- Speed
- High Flexibility
- Allows Rollback
- GUI Support and many more
Install, configure and secure MySQL on ubuntu 22.04. This article will teach how to install, configure and secure MySQL on the ubuntu 22.04 system.
How to Install MySQL on Ubuntu 22.04
1. Prerequisites
- The operating system running Ubuntu 22.04 Linux
- A root or non-root user with Sudo privileges
- Has stable internet connection
- Terminal window / Command line
2. Update Local Repositories
Updating the local package with the apt-get command. Open a terminal window and run the following command:
sudo apt-get update
sudo apt-get upgrade
3. Install MySQL Server
Please run the following command as sudo to install MySQL from APT-GET repositories.
sudo apt-get install mysql-server
The [Y / n] question to continue with the installation may appear during the installation process, press the Y key from the keyboard and hit Enter. This will install MySQL on the system.
4. Verify MySQL
After the installation, the MySQL server should start automatically. To check if it's running, run the following command:
mysql --version
5. Managing the MySQL Process
Now that you have your MySQL up and running, let's go over basic management commands.
To stop your MySQL, run this command:
sudo systemctl stop mysql
To start your MySQL, run this command:
sudo systemctl start mysql
To status your web server, run this command:
sudo systemctl status mysql
6. Configure MySQL Server
By default, MySQL lacks many basic and essential security features. Luckily, it comes with an installation script that walks you through the configuration. Use the mysql_secure_installation command:
sudo mysql_secure_installation
You will be asked to configure the VALIDATE PASSWORD PLUGIN which is used to test the strength of the MySQL user's passwords and improve their security. Type Y to start the Validate Password plugin and you will get the following prompt:
Enter the number for the password strength and press the Enter key:
On the next screen, enter and re-enter the password:
The system will then display the strength of the password you provided and also ask you if you want to continue with the password.
Type Y for Yes and press Enter.
If you get an error:
Login to MySQL First:
sudo mysql
#Output
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.30-0ubuntu0.22.04.1 (Ubuntu)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
Set Root Password:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password by 'Techvblogs@786';
Exit MySQL:
exit;
Run the Secure Installation Script Again:
sudo mysql_secure_installation
The script will ask these questions:
- 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 test database and access to it? Press Y, then ENTER.
- Reload privilege tables now? Press Y, then ENTER.
#Output
Securing the MySQL server deployment.
Enter password for user root:
The 'validate_password' component is installed on the server.
The subsequent steps will run with the existing configuration
of the component.
Using existing password for root.
Estimated strength of the password: 100
Change the password for root ? ((Press y|Y for Yes, any other key for No) : N
... skipping.
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.
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y
Success.
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 test database and access to it? (Press y|Y for Yes, any other key for No) : Y
- Dropping test database...
Success.
- Removing privileges on test database...
Success.
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!
7. Log into Mysql And Configuring
Now, when all is set, let's log in to MySQL with the root user. In the terminal, run the following command:
mysql -u root -p
To check which authentication method each MySQL user uses, run the following command:
mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;
Change the authentication method from auth_socket to mysql_native_password. You can do that by running the following command:
mysql> ALTER USER '<USER>'@'localhost' IDENTIFIED WITH mysql_native_password BY 'YOUR_STRONG_PASSWORD';
After executing the ALTER USER command, run the following command:
mysql> FLUSH PRIVILEGES;
Now, if you recheck the authentication method for your MySQL user accounts using the following command, you see that your root user is now using the mysql_native_password plugin for authentication:
mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;
Thank you for reading this blog.
If you want to manage your VPS / VM Server without touching the command line go and Checkout this link. ServerAvatar allows you to quickly set up WordPress or Custom PHP websites on VPS / VM in a matter of minutes. You can host multiple websites on a single VPS / VM, configure SSL certificates, and monitor the health of your server without ever touching the command line interface.
Read Also: How to Get Data between Two Dates in Laravel
If you have any queries or doubts about this topic please feel free to contact us. We will try to reach you.
