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?
Features Of MySQL
- Quick and Reliable
- Data Types
- Character Sets
- Support for large Databases
- High Flexibility
- Allows Rollback
- GUI Support and many more
How to Install MySQL on Ubuntu 22.04
- 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:
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:
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:
#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 '[email protected]';
Run the Secure Installation Script Again:
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.