What is MySQL Server?
Primarily, MySQL is a free and open source relational database management system (RDBMS). Also, MySQL is a robust and reliable database server, that stores and manages data for many different applications. Particularly popular for use with web based applications and is a central component of the LAMP (Linux, Apache, MySQL, PHP/Perl/Python) stack, a widely used open source web application development platform.
Oracle Corporation develops, distributes, and supports MySQL. Written in C and C++ languages and runs on various platforms, including Linux, Windows, and macOS. Well, MySQL is known for its speed, reliability and flexibility. Makes it a popular choice for web applications, data warehousing, and logging applications.
Above all, MySQL software uses a variant of the Structured Query Language (SQL) called MySQL SQL, which is a standard programming language for interacting with relational databases. Moreover, MySQL supports a wide range of data types, including numbers, strings, and dates, and provides several powerful features for managing and querying data.
Features of MySQL Server
MySQL is a feature rich database management system that offers a wide range of capabilities and functionality. Some of the key features of MySQL include:
- High Availability: there are several options for ensuring high availability of data, including replication and clustering. Replication allows you to create copies of your data on multiple servers, while clustering allows you to create a group of servers that work together to provide failover and load balancing.
- Security: MySQL provides several security features to help protect your data. These include support for secure connections, user management, and data encryption.
- Data Types: MySQL supports a wide range of data types, including numbers, strings, and dates. It also supports complex data types such as arrays, JSON, and spatial data.
- Stored Procedures and Functions: MySQL allows you to define stored procedures and functions using SQL and other programming languages such as C and Python. This allows you to create custom logic that is stored and reused within the database.
- Triggers: MySQL supports the use of triggers, which are special types of stored procedures that are automatically executed in response to specific events, such as the insertion or deletion of a record.
- Indexing: MySQL supports the use of indexes to improve the performance of queries. Indexes allow you to create a separate data structure that stores a specific set of columns from your table, making it faster to retrieve data from the table.
- Views: MySQL assists with the use of views, which are virtual tables that are derived from one or more existing tables. Views allow you to create a custom view of your data that is queried just like a regular table.
Advantages of MySQL Server
In addition, MySQL is a popular and widely used database management system that offers several advantages over other database systems. Some of the key benefits of MySQL include:
- Open source: Available as open source software, which means that it is freely used, modified and distributed. This makes it an attractive option for many developers and organizations, as it allows them to use the software without incurring any licensing fees.
- High Performance: MySQL is known for its fast performance and ability to handle large amounts of data. Uses a multi threaded architecture that allows it to handle multiple client connections simultaneously, making it well suited for high concurrency environments.
- Scalability: A scalable database system that is easily expanded to meet the needs of growing applications. Supports a wide range of storage engines, each with its own unique set of features and performance characteristics, allowing you to choose the one that best fits your needs.
- Widely used: One of the most popular database systems in the world, with millions of installations. This makes it easy to find support and resources for working with MySQL, and there is a large community of users and developers who provide assistance and insights.
- Comprehensive feature set: MySQL offers a wide range of features and capabilities, including support for stored procedures, triggers, views and transactions. This makes it a powerful and flexible database system that is used for a wide variety of applications.
We have reached the main part of the article how to Install MySQL on Ubuntu 22.04 (Step by Step).
How To Install MySQL on Ubuntu 22.04 (Step by Step)
In this section, we explain how to install MySQL on Ubuntu 22.04.
Prerequisites
- An Ubuntu 22.04 server is installed on your system.
- A root user or a user with sudo privileges.
Step 1 - Getting Started
Let’s start updating all of your system packages to the updated version using the following command.
apt update -y
apt upgrade -y
After updating all the system packages, you proceed to install the MySQL server.
Step 2 - Install MySQL Server
By default, the MySQL package is included in the Ubuntu default repository. Install it easily using the following command.
apt install mysql-server -y
Once MySQL is installed, please verify the MySQL version using the following command.
mysql --version
You should see the MySQL version in the following output.
mysql Ver 8.0.31-0ubuntu0.22.04.1 for Linux on x86_64 ((Ubuntu))
Step 3 - Manage MySQL Service
By default, the MySQL service is managed by systemd. You manage it easily using the systemctl command line utility.
First, start the MySQL service using the following command.
systemctl start mysql
Next, enable the MySQL service so it starts at system reboot.
systemctl enable mysql
Now, you verify the status of the MySQL with the following command.
systemctl status mysql
You should see the MySQL status in the following output.
● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: active (running) since Fri 2023-01-06 05:09:53 UTC; 25s ago
Process: 85644 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
Main PID: 85652 (mysqld)
Status: "Server is operational"
Tasks: 39 (limit: 4579)
Memory: 363.3M
CPU: 1.005s
CGroup: /system.slice/mysql.service
└─85652 /usr/sbin/mysqld
Jan 06 05:09:53 linux systemd[1]: Starting MySQL Community Server...
Jan 06 05:09:53 linux systemd[1]: Started MySQL Community Server.
By default, MySQL listens on port 3306. You check it with the following command.
ss -antpl | grep mysql
You will get the following output.
LISTEN 0 151 127.0.0.1:3306 0.0.0.0:* users:(("mysqld",pid=85652,fd=23))
LISTEN 0 70 127.0.0.1:33060 0.0.0.0:* users:(("mysqld",pid=85652,fd=21))
Step 4 - Secure MySQL Installation
First, log in to the MySQL console with the following command.
mysql
Once you are logged in, set a MySQL root password using the following command.
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'securepassword';
Now, exit from the MySQL shell with the following command.
EXIT;
Next, run the mysql_secure_installation script to secure the MySQL installation.
mysql_secure_installation
This command will remove anonymous users, disallow root login remotely and remove the test database.
Enter password for user root:
Press y|Y for Yes, any other key for No: n
Change the password for root ? ((Press y|Y for Yes, any other key for No) : n
Remove anonymous users? (Press y|Y for Yes, any other key for No) : Y
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : Y
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y
Step 5 - Create a User in MySQL
In this step, log in to the MySQL shell as a root user with the following command.
mysql -u root -p
After the successful login, create a user named user1 and set a password using the following command.
CREATE USER 'user1'@'localhost' IDENTIFIED BY 'user1password';
To grant all privileges on all databases, run the following command.
GRANT ALL PRIVILEGES ON *.* TO 'user1'@'localhost' WITH GRANT OPTION;
If you want to grant specific privileges on all databases, run the following command.
GRANT CREATE, ALTER, DROP, INSERT, UPDATE, INDEX, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'user1'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;
EXIT;
Now, verify the MySQL version using the following command.
mysqladmin -p -u user1 version
You should see the following output.
Enter password:
mysqladmin Ver 8.0.31-0ubuntu0.22.04.1 for Linux on x86_64 ((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.
Server version 8.0.31-0ubuntu0.22.04.1
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 4 min 40 sec
Threads: 3 Questions: 22 Slow queries: 0 Opens: 183 Flush tables: 3 Open tables: 102 Queries per second avg: 0.078
Step 6 - Create a Database and Table in MySQL
First, log in to the MySQL with the following command.
mysql -u root -p
Once you are connected to MySQL, create a new database named database1 using the following command.
CREATE DATABASE database1;
Next, verify the created database using the following command.
SHOW DATABASES;
You should get a list of all databases in the following output.
+--------------------+
| Database |
+--------------------+
| database1 |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
Now, switch the database to database1 and create a new table named student using the following command.
USE database1;
CREATE table student(id varchar(8), name varchar(10), phone varchar(13), age int);
Next, verify the created table using the following command.
SHOW tables;
You should see your table in the following output.
+---------------------+
| Tables_in_database1 |
+---------------------+
| student |
+---------------------+
To check the table information, run the following command.
desc database1.student;
You should see the following output.
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | varchar(8) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| phone | varchar(13) | YES | | NULL | |
| age | int | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
Thank you for reading How To Install MySQL on Ubuntu 22.04 (Step by Step). We shall conclude.
How To Install MySQL on Ubuntu 22.04 (Step by Step) Conclusion
In this post, we showed you how to install MySQL on Ubuntu 22.04. We also explained how to secure the MySQL installation, and create a database, user, and tables. Finally, MySQL is known for its fast performance and ability to handle large amounts of data. It uses a multi threaded architecture that allows it to handle multiple client connections simultaneously, making it well-suited for high concurrency environments.
Check out our blog for more MySQL information by clicking to our blog over here.
