How to Install MySQL Server on Ubuntu 21.04 (Step by Step Tutorial)
How to Install MySQL Server on Ubuntu 21.04. With using databases, it is more than necessary to have the best tools to manage them in terms of security, dynamism, ease of use and scalability. One of the applications that best adapts to these characteristics is MySQL.
What Is MySQL server?
MySQL is the most popular and the first RDBMS a relational database management system based on SQL (Structured Query Language) that they use for communications. Today, there is many more competitors to MySQL, but it has been used on the market for over 20 years and it has managed to maintain it’s reputation in the industry. MySQL database server is not the only database on the market, but it is one of the most popular ones and only second to Oracle Database. It should be noted that all MySQL variants have a syntax identical to MySQL, which implies that all of these variations are supported by MySQL.
MySQL was built in the mid 1990s to maintain SQL for the purpose of searching for all entries in the database and was originally intended to keep data orderly. MySQL was taken over by Sun Microsystems in 2008 while MySQL was taken over by Oracle in 2009. Like other RDBMS, MySQL utilizes rows and columns as tables to save data. It initiates and saves processes to maintain tables and supports several keys such as Primary Keys and Foreign Keys to ensure data integrity and consistency.
Key Features of MySQL Server
- One of the market’s earliest open source software.
- Comprehensive, free and high capacity database management system.
- Works under the client / server scheme or can be used in embedded systems.
- Supports the use of atomic data definition statements (Atomic DDL).
- Very scalable and adaptable.
- It offers excellent performance and accessibility for numerous users to utilize anytime and anywhere.
- It enables DevOps tools and is robust transactional support and can be simply integrated with data stores.
- Many of the server operating systems like FreeBSD, Linux, Solaris, OS X, and Windows are supported.
- The MySQL business version features a proprietary code that is only accessed by MySQL users.
- The MySQL router enables routing but does not support data analysis.
- It consists of 1600 fork processes.
Follow this post below to learn how how to Install MySQL Server on Ubuntu 21.04.
Prerequisites
Before following the below steps to install MySQL, make sure you have Ubuntu 21.04 installed a system with a user who has sudo privileges.
Install MySQL Server on Ubuntu 21.04
At the time of writing this tutorial, the latest version of MySQL is 8. By default, it is included in the Ubuntu 21.04 default repository. You can install it by just running the following command:
apt-get install mysql-server -y
After the installation, start and enable the MySQL service with the following command:
systemctl start mysql
systemctl enable mysql
To check the MySQL status, run the following command:
systemctl status mysql
Sample output:
● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: active (running) since Fri 2021-10-15 11:43:50 UTC; 39s ago
Process: 74015 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
Main PID: 74045 (mysqld)
Status: "Server is operational"
Tasks: 38 (limit: 1041)
Memory: 349.2M
CGroup: /system.slice/mysql.service
└─74045 /usr/sbin/mysqld
Oct 15 11:43:50 ubuntu systemd[1]: Starting MySQL Community Server...
Oct 15 11:43:50 ubuntu systemd[1]: Started MySQL Community Server.
To verify the MySQL version, run:
mysql -V
You should see the MySQL version in the following output:
mysql Ver 8.0.26-0ubuntu0.21.04.3 for Linux on x86_64 ((Ubuntu))
Secure MySQL Installation
By default, MySQL installation is not secured so you will need to secure it and set a MySQL root password.
mysql_secure_installation
You will be asked to setup validate password component as shown below:
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 password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?
Press y|Y for Yes, any other key for No:
Just press Enter. You will be asked to set a MySQL root password:
Please set the password for root here.
New password:
Re-enter new password:
Set your MySQL root password and press Enter. You will be asked to remove anonymous users:
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
Press Y and hit Enter. You will be asked to disallow root login remotely:
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
Press Y and hit Enter. You will be asked to remove a test database:
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
Press Y and hit Enter to remove a test database. You will be asked to reload the privilege tables:
- 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
Press Y and hit Enter to finish the setup:
Success.
All done!
Create a Database and User in MySQL
In this section of the guide how to install MySQL Server on Ubuntu 21.04, firstly, login to the MySQL shell using the command below:
mysql -u root -p
You will be asked to provide a MySQL root password:
Enter password:
Provide your root password and hit Enter. Once you are connected to the MySQL. You should see the MySQL interface:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.26-0ubuntu0.21.04.3 (Ubuntu)
Copyright (c) 2000, 2021, 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>
To create a database called salesdb, run:
mysql> CREATE DATABASE salesdb;
To create a user called salesuser and set a password, run:
mysql> CREATE USER 'salesuser'@'localhost' IDENTIFIED BY 'password';
To grant all the privileges on the salesdb to salesuser, run:
mysql> GRANT ALL PRIVILEGES ON salesdb.* TO 'salesuser'@'localhost' WITH GRANT OPTION;
To apply all the changes, run:
mysql> FLUSH PRIVILEGES;
To verify granted privileges, run:
mysql> SHOW GRANTS FOR 'salesuser'@'localhost';
Sample output:
+----------------------------------------------------------------------------------+
| Grants for salesuser@localhost |
+----------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `salesuser`@`localhost` |
| GRANT ALL PRIVILEGES ON `salesdb`.* TO `salesuser`@`localhost` WITH GRANT OPTION |
+----------------------------------------------------------------------------------+
To list all databases, run:
mysql> SHOW databases;
Sample output:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| salesdb |
| sys |
+--------------------+
To list all users, run:
mysql> SELECT User FROM mysql.user;
Sample output:
+------------------+
| User |
+------------------+
| debian-sys-maint |
| mysql.infoschema |
| mysql.session |
| mysql.sys |
| root |
| salesuser |
+------------------+
Create a Table in MySQL
Next step in the guide how to install MySQL Server on Ubuntu 21.04 is to create a table in MySQL. Firstly switch the database to salesdb using the following command:
mysql> USE salesdb;
To create a table called account, run:
mysql> create table account(
my_id INT NOT NULL AUTO_INCREMENT,
product VARCHAR(100) NOT NULL,
submission_date DATE,
time_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY ( my_id )
);
To insert some data to the account table, run:
mysql> INSERT into account (product) VALUES ('Watch');
mysql> INSERT into account (product) VALUES ('Radio');
mysql> INSERT into account (product) VALUES ('Television');
To list all tables, run:
mysql> SHOW TABLES;
Sample output:
+-------------------+
| Tables_in_salesdb |
+-------------------+
| account |
+-------------------+
To see the structure of the table, run:
mysql> DESCRIBE account;
Sample output:
+-----------------+--------------+------+-----+-------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+-------------------+-------------------+
| my_id | int | NO | PRI | NULL | auto_increment |
| product | varchar(100) | NO | | NULL | |
| submission_date | date | YES | | NULL | |
| time_created | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+-----------------+--------------+------+-----+-------------------+-------------------+
To display the data stored in the account table, run:
mysql> select * from account;
Sample output:
+-------+------------+-----------------+---------------------+
| my_id | product | submission_date | time_created |
+-------+------------+-----------------+---------------------+
| 1 | Watch | NULL | 2021-10-15 11:48:20 |
| 2 | Radio | NULL | 2021-10-15 11:48:26 |
| 3 | Television | NULL | 2021-10-15 11:48:32 |
+-------+------------+-----------------+---------------------+
Finally, exit from the MySQL interface using the following command:
mysql> EXIT;
Backup and Restore MySQL Database
Next in how to install MySQL Server on Ubuntu 21.04 is in MySQL which provides a mysqldump command line utility to backup your database and tables. To back up a database named salesdb and create a backup file named saledb.dump, run the following command:
mysqldump -u root -p salesdb > salesdb.dump
To back up all databases, run the following command:
mysqldump -u root -p --all-databases > alldb.dump
To restore a backup from the salesdb.dump backup file, run the following command:
mysql -u root -p salesdb < salesdb.dump
To restore all databases, run the following command:
mysql -u root -p < alldb.dump
Delete a Database, User and Table in MySQL
To delete a table, run the following command:
mysql> DROP TABLE account;
To delete a database, run the following command:
mysql> DROP DATABASE salesdb;
To delete a user, run the following command:
mysql> DROP USER salesuser@localhost;
Uninstall MySQL Database
If you want to remove the MySQL database server from your server, run the following command:
apt-get remove mysql-server --purge
After removing the MySQL database, some other unwanted dependencies are still persisting in your system. You can remove them by running the following command:
apt-get autoremove
Next, remove all package cache with the following command:
apt-get clean
How to Install MySQL Server on Ubuntu 21.04 Conclusion
In the above guide, we explained how to install a MySQL server on Ubuntu 21.04. We also explained how to create and remove a database, user and table in MySQL. I hope you have now enough understanding of the MySQL command line. You can now use MySQL as a database with your application.
