The tutorial for today explains how to install MySQL 8.0 on Ubuntu 22.04|20.04. MySQL is a popular open-source relational database management system (RDBMS) that is widely used in web applications and other data-driven software. It is developed, distributed, and supported by Oracle Corporation.
MySQL uses a client-server architecture, where client programs can connect to a server to query, modify, and manage databases. The MySQL server manages databases and tables, enforces data consistency and integrity, and provides various features for data storage, retrieval, and manipulation.
MySQL supports the Structured Query Language (SQL) and provides a wide range of features for database administration and management, including user authentication and access control, backup and recovery, replication, and more. MySQL finds use in several web applications and content management systems, including WordPress, Drupal, and Joomla, among others. Its popularity and open-source nature have also led to the development of many third-party tools and libraries for working with MySQL databases.
Features of MySQL 8.0
- Improvements to XML and InnoDB.
- Dictionary of transactional data.
- Improved document storage capabilities and Native JSON data support.
- Typical Table Expressions
- Windows Features
- Error numbers and reduced verbosity in the error log are two enhancements.
The procedures outlined below explain how to install and set up MySQL 8.0 on Ubuntu 22.04|20.04.
#1. Install MySQL 8.0 on Ubuntu 22.04|20.04
MySQL 8.0 is available in the default Ubuntu 22.04|20.04 repositories. As a result, installing MySQL 8.0 on Ubuntu 22.04 is a simple process.
Begin by updating your system package index.
sudo apt updateThen run the following command to install MySQL 8 on Ubuntu 22.04|20.04;
sudo apt install mysql-serverSample results:
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following additional packages will be installed:
libaio1 libcgi-fast-perl libcgi-pm-perl libevent-core-2.1-7 libevent-pthreads-2.1-7 libfcgi-bin libfcgi-perl
libfcgi0ldbl libhtml-template-perl libmecab2 libprotobuf-lite23 mecab-ipadic mecab-ipadic-utf8 mecab-utils
mysql-client-8.0 mysql-client-core-8.0 mysql-common mysql-server-8.0 mysql-server-core-8.0
Suggested packages:
libipc-sharedcache-perl mailx tinyca
The following NEW packages will be installed:
libaio1 libcgi-fast-perl libcgi-pm-perl libevent-core-2.1-7 libevent-pthreads-2.1-7 libfcgi-bin libfcgi-perl
libfcgi0ldbl libhtml-template-perl libmecab2 libprotobuf-lite23 mecab-ipadic mecab-ipadic-utf8 mecab-utils
mysql-client-8.0 mysql-client-core-8.0 mysql-common mysql-server mysql-server-8.0 mysql-server-core-8.0
0 upgraded, 20 newly installed, 0 to remove and 133 not upgraded.
Need to get 29.1 MB of archives.
After this operation, 242 MB of additional disk space will be used.
Do you want to continue? [Y/n] yEnsure the service is started and enabled:
sudo systemctl start mysql
sudo systemctl enable mysqlCheck the status of the service:
$ systemctl status mysql
● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: active (running) since Mon 2023-05-01 10:44:06 EAT; 56s ago
Main PID: 164652 (mysqld)
Status: "Server is operational"
Tasks: 39 (limit: 4575)
Memory: 365.1M
CGroup: /system.slice/mysql.service
└─164652 /usr/sbin/mysqld
Cam 01 10:44:05 tutornix systemd[1]: Starting MySQL Community Server...
Cam 01 10:44:06 tutornix systemd[1]: Started MySQL Community Server.Check the version of installed MySQL 8.0;
$ mysql -V
mysql Ver 8.0.32-0ubuntu0.20.04.2 for Linux on x86_64 ((Ubuntu))#2. Secure MySQL 8.0 Installation on Ubuntu 22.04|20.04
You should run the security script that comes with the database management system when installing MySQL for the first time.
This script modifies some of the less secure default settings, such as those that forbid remote root logins and get rid of test users.
Open the MySQL prompt by typing:
sudo mysqlSample output;
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.32-0ubuntu0.20.04.2 (Ubuntu)
Copyright (c) 2000, 2023, 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 switch the root user’s authentication mechanism to one that utilizes a password, issue the following ALTER USER command. The authentication mechanism is changed to mysql native password in the following example:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Passw0rd!';Replace ‘Passw0rd!’ with your preferred root password. Exit the MySQL prompt after making this change:
mysql> exitTo protect the installed MySQL Server you have to run a script known as mysql_secure_installation. You will be required to utilize the VALIDATE PASSWORD plugin, which assesses the strength of your passwords.
If you want to install the plugin, type “Y” and hit ENTER. The plugin offers three different levels of password security: LOW, MEDIUM, and STRONG. Choose STRONG;
sudo mysql_secure_installationProceed as shown below to harden the MySQL 8.0 instance:
Securing the MySQL server deployment.
Enter password for user root: Provide_root_password_here
....
There are three levels of password validation policy:
LOW Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file
Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2
....
#Please set the password for root here.
Change the password for root ? ((Press y|Y for Yes, any other key for No) : y
New password: Provide_password_here
Re-enter new password: Provide_password_here_again
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
...
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.
.......
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.
.........
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
Success.
All done! #3. Using MySQL 8.0 on Ubuntu 22.04|20.04
After installing it, you can use MySQL 8.0 as desired. First, access the shell using the created root password:
$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.32-0ubuntu0.20.04.2 (Ubuntu)
Copyright (c) 2000, 2023, 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> Check the database version:
mysql> SELECT VERSION();
+-------------------------+
| VERSION() |
+-------------------------+
| 8.0.32-0ubuntu0.20.04.2 |
+-------------------------+
1 row in set (0.00 sec)
mysql> Create a User and Database on MySQL
We will begin by creating a test user and database on MySQL 8.0. The commands for that are:
CREATE DATABASE sampledb;
CREATE USER 'test_user'@'%' IDENTIFIED BY 'Passw0rd!';
GRANT ALL ON sampledb.* TO 'test_user'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;Once created, check the available databases:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sampledb |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> Check the available users on MySQL:
mysql> SELECT User, Host FROM mysql.user;
+------------------+-----------+
| User | Host |
+------------------+-----------+
| test_user | % |
| debian-sys-maint | localhost |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
6 rows in set (0.00 sec)
mysql> To delete a user on MySQL, use the command with the below syntax:
DROP USER 'username'@'host';Create a Table on MySQL
With a database created, you can add tables to it. For this guide, we will add a sample table as shown:
USE sampledb;
CREATE TABLE homes (
equip_id serial PRIMARY KEY,
type varchar (50) NOT NULL,
color varchar (25) NOT NULL,
location varchar(25) check (location in ('north', 'south', 'west', 'east', 'northeast', 'southeast', 'southwest', 'northwest')),
install_date date
);To add data to the table, use:
INSERT INTO homes (type, color, location, install_date) VALUES ('One bedroom', 'blue', 'south', '2017-04-28');
INSERT INTO homes (type, color, location, install_date) VALUES ('two bedroom', 'yellow', 'northwest', '2018-08-16');
INSERT INTO homes (type, color, location, install_date) VALUES ('three bedroom', 'green', 'south', '2019-08-16');Once added, view the data:
SELECT * FROM homes;Sample Output:
To exit the shell:
mysql> exit
Bye#4. Configure MySQL 8.0 On Ubuntu 22.04|20.04.
You can also make several configurations for your MySQL 8.0 server. The configurations include bind port, interface, data path etc.
First, stop MySQL:
sudo systemctl stop mysqlTo make modifications, access the configuration file with the command:
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnfOnce opened, you can make the changes.
a. Change MySQL Listening Address
To change the bind address, for example, if you want to allow MySQL to listen on all interfaces, make the below change:
[mysqld]
bind-address = 0.0.0.0
mysqlx-bind-address = 0.0.0.0b. Change the MySQL 8.0 Data Directory
You can also configure MySQL 8.0 to use a custom data path. Begin by creating the path on your system.
sudo mkdir -p /mysql/data
sudo chmod -R 775 /mysql/data
sudo chown -R mysql:mysql /mysql/dataNow we need to sync the contents to the directory from the old one. To get the old directory, use:
$ sudo grep -R --color datadir /etc/mysql/*
/etc/mysql/mysql.conf.d/mysqld.cnf:# datadir = /var/lib/mysqlFrom the output, the data directory is /var/lib/mysql in the configuration file at /etc/mysql/mysql.conf.d/mysqld.cnf.
We can use rsync to sync the contents:
sudo apt install rsync -y
sudo rsync -av /var/lib/mysql/ /mysql/dataOpen the config file for modification:
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnfNow modify the MySQL 8.0 config to accommodate the new data path:
[mysqld]
datadir= /mysql/dataFor the changes made to apply, we first need to configure AppArmor:
sudo vim /etc/apparmor.d/tunables/aliasAdd the path here:
alias /var/lib/mysql/ -> /mysql/data/,Save the changes and restart the service:
sudo systemctl restart apparmorc. Configure the MySQL Bind Port
You can also configure your MySQL 8.0 server to listen on a custom port rather than the default port 3306. To do this, locate the below lines in the config.
[mysqld]
port = 4000For this guide, I have switched to port 4000. Save the changes and allow the port through the firewall:
sudo ufw allow 4000/tcpd. Start the MySQL 8.0 Service
After making the desired changes, start the MySQL 8.0 service:
sudo systemctl start mysqlNow verify if the service is listening on the set port and address:
$ sudo ss -plunt|grep 4000
tcp LISTEN 0 151 0.0.0.0:4000 0.0.0.0:* users:(("mysqld",pid=166644,fd=23)) #5. Access MySQL 8.0 Remotely
Now after configuring the listen address, you can access MySQL 8.0 from a remote server with the MySQL client installed.
The command for this has the below syntax:
mysql -h <IP_Address> -u <user> -p -P <port>For this guide after making modifications to the listening port, the command will be:
mysql -h 192.168.205.11 -u test_user -p -P 4000Sample Output:
Conclusion
We have learned how to install MySQL 8.0 on Ubuntu 22.04|20.04. We have also learned how to configure and use the database. I hope this was informative.
Read more about MySQL here.
Other tutorials:
great post – thanks !!
Thanks alot @keto