How to Install and Use MySQL 8.0 on Debian 12

March 16, 2024 March 16, 2024 13 min read

Hello Geekandnix fellow, in this guide, I will show you how to install and use MySQL Server (8.x) on the Debian 12 server.

MySQL is an open-source and widely-used RDBMS (Relational Database Management System). In this guide, you will install and secure MySQL server, then also learn basic queries of MySQL for creating database, user, and table. Lastly, you will also learn basic CRUD operations (Create Read Update, and Delete) in MySQL.

So let’s get things done!

Prerequisites

Before you begin with this guide, you should have a Debian 12 server initialized with non-root users as sudo/administrator privileges. Follow our guide Initial Server Setup with Debian 12 (9 things todo).

Installing MySQL 8.x on Debian

On the Debian repository, there is only a MariaDB server available, not a MySQL server. To install the MySQL server, you must install it using the official MySQL repository.

To install MySQL server 8.x on Debian, carry out the following:

1. Visit the MySQL server repository for Debian and grab the link to the MySQL APT repository.

2. Run the command below to download the MySQL APT repository. Replace the link of the MySQL APT repository.

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

3. Now, run the dpkg command below to add the MySQL APT repository to your Debian system.

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

Choose the repository for MySQL Server & Cluster (Currenly selected: mysql-8.0) and select OK.

4. Once the MySQL APT repository is added, run the command below to refresh your package index.

sudo apt update

Hit:1 http://deb.debian.org/debian bookworm InRelease
Hit:2 http://deb.debian.org/debian bookworm-updates InRelease
Get:3 http://repo.mysql.com/apt/debian bookworm InRelease [17.9 kB]
Hit:4 http://security.debian.org/debian-security bookworm-security InRelease
Hit:5 http://nginx.org/packages/mainline/debian bookworm InRelease
Get:6 http://repo.mysql.com/apt/debian bookworm/mysql-8.0 Sources [948 B]
Get:7 http://repo.mysql.com/apt/debian bookworm/mysql-apt-config amd64 Packages [566 B]
Get:8 http://repo.mysql.com/apt/debian bookworm/mysql-8.0 amd64 Packages [12.6 kB]
Get:9 http://repo.mysql.com/apt/debian bookworm/mysql-tools amd64 Packages [4,094 B]

5. Lastly, run the apt install command below to install the mysql-server package to your system.

sudo apt install mysql-server

Enter y to proceed with the installation.

Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following additional packages will be installed:
  libaio1 libmecab2 mecab-ipadic mecab-ipadic-utf8 mecab-utils mysql-client mysql-common mysql-community-client mysql-community-client-core
  mysql-community-client-plugins mysql-community-server mysql-community-server-core
The following NEW packages will be installed:
  libaio1 libmecab2 mecab-ipadic mecab-ipadic-utf8 mecab-utils 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, 13 newly installed, 0 to remove and 34 not upgraded.
Need to get 42.4 MB of archives.
After this operation, 358 MB of additional disk space will be used.
Do you want to continue? [Y/n] Y

When prompted, input your MySQL root password and select OK, then repeat the password.

Now choose option Use Strong Password Encryption (RECOMMENDED) and select OK to confirm.

Securing MySQL server on Debian

With the MySQL server installed, you must ensure the secure of the MySQL deployment. In this section, you will utilize the mysql_secure_installation to secure the MySQL server.

To secure MySQL server installation, complete the following actions:

1. First, run the following command to secure your MySQL server installation.

sudo mysql_secure_installation

2. Input your MySQL root password when asked and hit Enter.

Securing the MySQL server deployment.

Enter the password for user root:

3. When asked to set up the VALIDATE PASSWORD component and change MySQL root password, input No.

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: No
Using existing password for root.
Change the password for root ? ((Press y|Y for Yes, any other key for No) : No

4. Input Y to remove the default anonymous user from MySQL.

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.

5. Now input Y to disable remote login for MySQL root user.

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.

7. When prompted to remove the default database test, input Y again.

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.

8. Lastly, enter Y to reload table privileges and apply changes.

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!

Managing MySQL service on Debian

Now that the MySQL server is secured, let’s move on to manage the MySQL service via the systemctl. This is important because you always need to restart or reload MySQL whenever making changes to the MySQL configuration.

Learn how to manage MySQL on Debian with the following:

1. To start the MySQL server, run the systemctl command below.

sudo systemctl start mysql

2. Once MySQL starts, run the command below to ensure that the service is running. If MySQL is running, you will see an output active (running).

sudo systemctl status mysql

● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; preset: enabled)
     Active: active (running) since ; 8min ago
       Docs: man:mysqld(8)
             http://dev.mysql.com/doc/refman/en/using-systemd.html
   Main PID: 5199 (mysqld)
     Status: "Server is operational"
      Tasks: 38 (limit: 2307)
     Memory: 387.1M
        CPU: 17.635s
     CGroup: /system.slice/mysql.service
             └─5199 /usr/sbin/mysqld

3. Optionally, run the following ss command to check MySQL via server port. You will see port 3306 with the state LISTEN and used by the program mysqld.

ss -tulpn | grep 3306

tcp   LISTEN 0      70                 *:33060            *:*    users:(("mysqld",pid=5199,fd=21))
tcp   LISTEN 0      151                *:3306             *:*    users:(("mysqld",pid=5199,fd=23))

4. Now, if you need to stop the MySQL server, run the command below.

sudo systemctl stop mysql

5. Lastly, run the command below to restart MySQL when needed, especially after making changes to the MySQL server configuration.

sudo systemctl restart mysql

Creating MySQL Database and User

At this point, the MySQL server is running and secured. You can now create new databases or users for your applications.

To create a new database and user in MySQL, follow these steps:

1. Run the mysql command below to log in as a root user to the MySQL server. Type your MySQL root password when asked.

sudo mysql -u root -p

Once logged in, you will get the MySQL server prompt like the following:

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

Copyright (c) 2000, 2024, 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>

2. Now, run the following query to create a new database appdb and list available databases in the MySQL server.

CREATE DATABASE appdb;
SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| appdb              |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

3. Run the following queries to create a new user appuser and password Strongp4ssw0rd!#. Then, give privileges for the new user to the database appdb. After that, reload table privileges to apply the new changes.

CREATE USER appuser@localhost IDENTIFIED BY 'Strongp4ssw0rd!#';
GRANT ALL PRIVILEGES ON appdb.* TO appuser@localhost;
FLUSH PRIVILEGES;

4. Next, run the query below to verify the list of users on the MySQL server. Make sure the new user appuser is available.

SELECT user FROM mysql.user;

+------------------+
| user             |
+------------------+
| appuser          |
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
| root             |
+------------------+
5 rows in set (0.00 sec)

5. Now, run the following query to verify list privileges for user appuser.

SHOW GRANTS FOR appuser@localhost;

If everything goes well, you will see user appuser with privileges to access the database appdb like the following:

+------------------------------------------------------------+
| Grants for appuser@localhost                               |
+------------------------------------------------------------+
| GRANT USAGE ON *.* TO `appuser`@`localhost`                |
| GRANT ALL PRIVILEGES ON `appdb`.* TO `appuser`@`localhost` |
+------------------------------------------------------------+
2 rows in set (0.00 sec)

6. Lastly, type quit or exit or press Ctrl+d to log out from the MySQL server.

Connecting to MySQL to specific database and user

After creating the MySQL database and user, let’s verify it by logging into MySQL using your new user and database.

Carry out the following to connect to MySQL server to a specific database:

1. Run the mysql command below to connect to the MySQL server with user appuser to the specific database appdb. Input your password when asked.

sudo mysql -h localhost -u appuser -D appdb -p

2. Once logged in to the MySQL server, run the status query below to verify your connection.

status

As pointed out in the following output, you have connected to the MySQL server, specifically to the database appdb with the user appuser.

--------------
mysql  Ver 8.0.36 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id:          11
Current database:       appdb
Current user:           appuser@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.36 MySQL Community Server - GPL
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:            /var/run/mysqld/mysqld.sock
Binary data as:         Hexadecimal
Uptime:                 24 min 16 sec

Threads: 2  Questions: 23  Slow queries: 0  Opens: 185  Flush tables: 3  Open tables: 104  Queries per second avg: 0.015

Creating Table in MySQL

Now that you have connected to your database, move on to create a table on MySQL. proceed with these steps to create a table in MySQL:

1. Run the following query to create a new table players with columns such as id, name, team, and number.

CREATE TABLE players (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
team VARCHAR(50),
number INT(100)
);

2. Now, run the query below to check the list tables on your database. The table players should be shown.

SHOW TABLES;

+-----------------+
| Tables_in_appdb |
+-----------------+
| players         |
+-----------------+
1 row in set (0.00 sec)

3. Lastly, run the following query to check columns within the table players.

DESCRIBE players;

As you can see on the current database appdb there is a table players with columns id, name, team, and numbers.

+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int         | NO   | PRI | NULL    | auto_increment |
| name   | varchar(50) | YES  |     | NULL    |                |
| team   | varchar(50) | YES  |     | NULL    |                |
| number | int         | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

Basic CRUD (Create Read Update Delete) in MySQL

After the table is created in MySQL, you will learn MySQL queries for basic CRUD (Create, Read, Update, and Delete) operations.

Discover basic CRUD (Create, Read, Update, Delete) operations in MySQL with the following:

1. To insert data to MySQL, run the INSERT query followed by the table name and columns like the following.

INSERT INTO players (name, team, number)
VALUES ('Jude Bellingham', 'Real Madrid', 5);

You can also insert multiple data at once with the same query as below.

INSERT INTO players (name, team, number)
VALUES ('David Alaba', 'Real Madrid', 4),
('Luka Modric', 'Real Madrid', 10),
('Rodrygo', 'Real Madrid', 11),
('Kepa Arrizabalaga', 'Real Madrid', 27);

mysql> INSERT INTO players (name, team, number)
    -> VALUES ('Jude Bellingham', 'Real Madrid', 5);
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO players (name, team, number)
    -> VALUES ('David Alaba', 'Real Madrid', 4),
    -> ('Luka Modric', 'Real Madrid', 10),
    -> ('Rodrygo', 'Real Madrid', 11),
    -> ('Kepa Arrizabalaga', 'Real Madrid', 27);
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

2. Now, run the SELECT query below to retrieve data from the MySQL server. The symbol * will show data from all columns within the table players.

SELECT * FROM players;

+----+-------------------+-------------+--------+
| id | name              | team        | number |
+----+-------------------+-------------+--------+
|  1 | Jude Bellingham   | Real Madrid |      5 |
|  2 | David Alaba       | Real Madrid |      4 |
|  3 | Luka Modric       | Real Madrid |     10 |
|  4 | Rodrygo           | Real Madrid |     11 |
|  5 | Kepa Arrizabalaga | Real Madrid |     27 |
+----+-------------------+-------------+--------+
5 rows in set (0.00 sec)

If you want to retrieve data from a specific column, then replace * with the column names like the following:

SELECT name, team FROM players;

+-------------------+-------------+
| name              | team        |
+-------------------+-------------+
| Jude Bellingham   | Real Madrid |
| David Alaba       | Real Madrid |
| Luka Modric       | Real Madrid |
| Rodrygo           | Real Madrid |
| Kepa Arrizabalaga | Real Madrid |
+-------------------+-------------+
5 rows in set (0.00 sec)

3. Next, run the UPDATE query below to change data from a specific column. In this example, you’ll update the number to 27 for the name = Jama Murray.

UPDATE players
SET number = 25
WHERE name = 'Kepa Arrizabalaga';

mysql> UPDATE players
    -> SET number = 25
    -> WHERE name = 'Kepa Arrizabalaga';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Once updated, run the SELECT query below to verify the changes.

SELECT * FROM players
WHERE name = 'Kepa Arrizabalaga';

As you can see below, the number has been changed to 25.

+----+-------------------+-------------+--------+
| id | name              | team        | number |
+----+-------------------+-------------+--------+
|  5 | Kepa Arrizabalaga | Real Madrid |     25 |
+----+-------------------+-------------+--------+
1 row in set (0.00 sec)

4. Lastly, if you need to remove data from MySQL, run the DELETE query below. Make sure to change the selector in the WHERE parameter with specific data that you want to remove.

DELETE FROM players
WHERE id = 5;

Now check again your data with the SELECT query below. You will see the data for id = 5 is removed from the table players.

SELECT * FROM players;

mysql> DELETE FROM players
    -> WHERE id = 5;
Query OK, 1 row affected (0.02 sec)

...

+----+-----------------+-------------+--------+
| id | name            | team        | number |
+----+-----------------+-------------+--------+
|  1 | Jude Bellingham | Real Madrid |      5 |
|  2 | David Alaba     | Real Madrid |      4 |
|  3 | Luka Modric     | Real Madrid |     10 |
|  4 | Rodrygo         | Real Madrid |     11 |
+----+-----------------+-------------+--------+
4 rows in set (0.00 sec)

Deleting Table and Database in MySQL

To clean up your environment, delete the table and database that you have created with the following:

1. To remove the table in MySQL, run the DROP TABLE query below.

DROP TABLE players;

2. Now, run the USE query to switch the database to mysql.

USE mysql;

3. Lastly, if you want to delete the database on MySQL, run the DROP DATABASE query below.

DROP DATABASE appdb;

Conclusion

In this guide, you have learned how to install and secure MySQL server on a Debian 12 server. You also learned how to manage MySQL service, and how to create databases and users in MySQL. Lastly, you learned basic queries for creating a table and CRUD (Create, Read, Update, and Delete) operation.

From here, you can continue to install LAMP or LEMP Stack software with the following:

ArvidL

System administrator and devops enthusiast, leveraging over 10+ years of Linux expertise to optimize operations. Proficient in FreeBSD, VMWare, KVM, Proxmox, PfSense, Ansible, Docker, and Kubernetes.