Databases are almost everywhere where data is involved. They play a key role in storing and managing data. Databases can be defined as a systematic collection of data stored electronically in a computer system. The database is normally controlled by a database management system (DBMS) When data, DMBS and all the associated applications are put together, they make a database system, which is often shortened to just a database.

There are several types of databases. The most popular ones are:

  • Relational databases: where data is stored in tables with rows and columns
  • Object-oriented databases: here, data is represented in the form of objects, as in object-oriented programming.
  • Distributed databases: they consist of two or more files stored in different computers in the same physical location scattered over.
  • NoSQL databases: They are also referred to as nonrelational databases. They allow both unstructured and semistructured data to be stored and manipulated.
  • Graph databases: They store data by creating relationships between entities.
  • Document/JSON database: They are used to store and manage document-oriented information

MySQL is a relational database that was developed by a Swedish company MySQL AB in 1995. The main purpose of creating this database was to provide an efficient and reliable way to manage data. In 2008, MySQL was acquired by Sun Microsystems. In 2009, Oracle entered into a definitive agreement and acquired Sun Microsystems.

Through several developments and improvements, the latest release of MySQL is version 8.1. This release has several features and improvements. Some of them are:

  • Capturing EXPLAIN FORMAT=JSON output: In this new release, the EXPLAIN FORMAT=JSON with an INTOoption has been extended. This provides the ability to save JSON-formatted EXPLAIN output in a user variable where it can be worked with using MySQL JSON functions.
  • Client comments preserved: In the previous releases, the default behaviour from the MySQL client was stripping of comments. In this new release, the default behaviour is preserving such comments.
  • MySQL Replication: SOURCE_RETRY_COUNT change. In MySQL 8.1 the default value for SOURCE_RETRY_COUNT of CHANGE REPLICATION SOURCE TO option has been set to 10.
  • Logging of the shutdown process has been improved, now startup and shutdown messages for the MySQL server, plugins, and components have been added.

The below steps will help you install MySQL 8.1 on Debian / Ubuntu.

1. Prepare Your System

Start by updating the system and installing the required packages:

sudo apt update
sudo apt install wget vim -y

On Debian 12 Systems, you need the libssl1.1 and mecab-ipadic-utf8 which can be installed from the Debian 11 repo by running the commands:

echo 'deb http://security.debian.org/debian-security bullseye-security main' | sudo tee -a /etc/apt/sources.list
sudo apt update && sudo apt install libssl1.1 mecab-ipadic-utf8

Once installed, disable the repository

sudo sed -i 's/^deb http:\/\/security.debian.org\/debian-security bullseye-security main/#&/' /etc/apt/sources.list

2. Install MySQL 8.1 Packages on Debian / Ubuntu

There are two major ways that can be used to install MySQL 8.1 on Debian / Ubuntu. These methods are:

  • Install MySQL 8.1 using APT repo
  • Install MySQL 8.1 using binaries

Follow any of the below methods that best works for you:

Method 1. Install MySQL 8.1 using APT Repository

In this method, there are a series of steps you need to follow. These steps are, begin by adding the MySQL repository to your Debian / Ubuntu system. You can download the package from the MySQL downloads page.

Alternatively, you can use wget to get the package:

wget https://dev.mysql.com/get/mysql-apt-config_0.8.26-1_all.deb

Now configure the repository by running the command:

sudo dpkg -i mysql-apt-config_0.8.26-1_all.deb

Proceed and select your system, either Debian/Ubuntu. (There is no support for Debian 12, so you can just use the Debian 11 repo on Debian 12 systems)

Install MySQL 8.1 on DebianUbuntu

After selecting the correct system, select the MySQL server and Cluster option.

Install MySQL 8.1 on DebianUbuntu 1

Now select the MySQL version to install. Here, we will go for the MySQL-innovation(MySQL 8.1)

Install MySQL 8.1 on DebianUbuntu 2

Complete the setup by selecting Ok

Install MySQL 8.1 on DebianUbuntu 3

After the repository has been set up, we can easily install MySQL 8.1 on Debian / Ubuntu. First, update the package index:

sudo apt update -y

Now install MySQL 8.1 packages with the command:

sudo apt install mysql-client mysql-community-server mysql-server

Dependency tree:

Building dependency tree... Done
Reading state information... Done
The following additional packages will be installed:
  libaio1 mysql-common mysql-community-client mysql-community-client-core
  mysql-community-client-plugins mysql-community-server-core
The following NEW packages will be installed:
  libaio1 mysql-client mysql-common mysql-community-client mysql-community-client-core
  mysql-community-client-plugins mysql-community-server mysql-community-server-core
  mysql-server
0 upgraded, 9 newly installed, 0 to remove and 128 not upgraded.
Need to get 35.3 MB of archives.
After this operation, 305 MB of additional disk space will be used.
Do you want to continue? [Y/n] y

Finish the installation by setting a strong root password as shown below.

Method 2. Install MySQL 8.1 using Binaries

This method involves downloading MySQL 8.1 .DEB binary files from the MySQL downloads page. While on this page, select the appropriate binary for your system.

You can also pull the binaries using wget as shown:

##For Ubuntu 22.04
wget https://dev.mysql.com/get/mysql-server_8.1.0-1ubuntu22.04_amd64.deb-bundle.tar

##For Debian 11/Debian 12(with libssl 1.1 and mecab-ipadic-utf8 installed)
wget https://dev.mysql.com/get/mysql-server_8.1.0-1debian11_amd64.deb-bundle.tar

Once the package has been downloaded, extract it with the command:

tar -xvf mysql-server_*

Now install the packages with the command:

sudo dpkg -i mysql-{common,community-client-plugins,community-client-core,community-client,client,community-server-core,community-server,server}_*.deb

Note: This method does not provide binaries for Ubuntu 20.04, for that case, users can use Method 1. Debian 12 users can use Debian 11 binaries after having libssl 1.1 and mecab-ipadic-utf8 installed as in step 1

Finish MySQL 8.1 installation

With any of the above methods selected, you need to finish the installation by setting a strong MySQL root password:

Install MySQL 8.1 on DebianUbuntu 4

Confirm the provided password

Install MySQL 8.1 on DebianUbuntu 5

Set the authentication mechanism. Here, we will set the Strong Password Encryption mechanism.

Install MySQL 8.1 on DebianUbuntu 6

Verify if the service is running:

$ systemctl status mysql
● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; preset: enabled)
     Active: active (running) since Thu 2023-09-28 06:55:01 EDT; 4min ago
       Docs: man:mysqld(8)
             http://dev.mysql.com/doc/refman/en/using-systemd.html
   Main PID: 3374 (mysqld)
     Status: "Server is operational"
      Tasks: 37 (limit: 4623)
     Memory: 390.7M
        CPU: 8.092s
     CGroup: /system.slice/mysql.service
             └─3374 /usr/sbin/mysqld

3. Secure the MySQL Installation

After installing MySQL, it is recommended to secure it. To achieve that, run:

sudo mysql_secure_installation

Proceed as shown, provide the root password you set above and proceed.

Securing the MySQL server deployment.

Enter password for user root: <Enter set root 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: y  

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
...
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  
Success.

All done!

4. Access and Use MySQL

Now you can access and use MySQL locally. To access the MySQL shell, run:

mysql -u root -p

Provide the root password and you will granted access:

Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.1.0 MySQL Community Server - GPL

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>

Now you are set to use the MySQL 8.1 database as desired. To check the version, run:

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.1.0     |
+-----------+
1 row in set (0.00 sec)

Create a database and User

To create a database and user with the permissions to access it, issue SQL commands with the below syntax:

CREATE DATABASE mydb;
CREATE USER 'mydbuser'@'%' IDENTIFIED BY 'DBUserStr0ngPassw0d';
GRANT ALL PRIVILEGES ON mydb.* TO 'mydbuser'@'%'; 
FLUSH PRIVILEGES;
exit

You can test if the user can access the database:

mysql -u mydbuser -p

Show the databases:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

Create Tables and Insert Data

Once connected, create a table in the database:

USE mydb;
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50)
);

Add data to the table

INSERT INTO users (username) VALUES
    ('JohnDoe'),
    ('JaneSmith'),
    ('BobJohnson');

Query the data:

mysql> SELECT * FROM users;
+----+------------+
| id | username   |
+----+------------+
|  1 | JohnDoe    |
|  2 | JaneSmith  |
|  3 | BobJohnson |
+----+------------+
3 rows in set (0.00 sec)

mysql> exit

5. Configure MySQL to Accept Remote Connections

To access this MySQL instance remotely, you need to make a few configurations. Open the MySQL configuration file

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

In the file, set or add the bind address as shown:

# By default we only accept connections from localhost 
#bind-address   = 127.0.0.1 
bind-address   = 0.0.0.0

Once the changes have been made, save the file and restart the service:

sudo systemctl restart mysql

Allow the service through the firewall:

# Open MySQL port 3306 on the firewall
sudo ufw allow mysql

# Allow specific addresses to connect to mysql
sudo ufw allow from <Remote_IP> to any port 3306

Now you can access the MySQL instance remotely using a command with the syntax:

mysql -u <user> -h <database_server_ip> -p

For example:

mysql -u mydbuser -h 192.168.200.56 -p

Closing Thoughts

This guide has provided all the steps required to install MySQL 8.1 on Debian / Ubuntu. I hope this worked for you too. You can learn more about MySQL by reading the below books:

See more on this page:

LEAVE A REPLY

Please enter your comment!
Please enter your name here