Install MySQL 8.0 in Debian 12 Bookworm

In this guide we will see how to perform theinstall MySQL in Debian 12 Bookworm with apt, the default package manager

installing MySQL is very common in our Debian Server Management interventions, as it is the base for LAMP (Linux, Apache, MySQL, PHP), LEMP (NginX) stacks and several others, needed for Web Apps, CMS and e-commerce

Add the official MySQL mirror

As a first step we download the configuration package of the official MySQL repository

sudo apt update && sudo apt install wget

cd /tmp
wget https://dev.mysql.com/get/mysql-apt-config_0.8.32-1_all.deb

We install the downloaded package

sudo apt install ./mysql-apt-config_0.8.32-1_all.deb

While installing MySQL we can select the desired version, displaying one of these screens:

 -------- Configuring mysql-apt-config --------
  
  │ Which MySQL product do you wish to configure?                           │ 
  │ │ 
  │ MySQL Server & Cluster (Currently selected: mysql-8.4-lts) │ 
  │ MySQL Connectors (Currently selected: Enabled) │

Configuring mysql-apt-config
----------------------------
The MySQL APT Repository features MySQL Server along with a variety of components. You may select the desired
products to install and update from the official MySQL Repository, and also select the associated version series.
Select "Ok" to save the configuration, and then execute "apt update" to load the selected package list. This
configuration can be updated later, depending on your needs.
 MySQL Server & Cluster (Currently selected: mysql-8.0) 3. Ok
 MySQL Connectors (Currently selected: Enabled)
Which MySQL product do you wish to configure? 3

Let's change, in the"MySQL Server & Cluster" selection, to mysql-8.0 in the first case using arrows and enter, in the second using the number 1 from the keyboard

If you prefer to use MySQL 8.4, change the selection to mysql-8.4-lts and follow the guide Install MySQL 8.4 in Debian 12 Bookworm

Install MySQL in Debian 12 Bookworm

Let's update the list of available packages

sudo apt update

We will notice new lines containing the new MySQL 8.0 mirror for Debian 12 Bookworm

Get:10 http://repo.mysql.com/apt/debian bookworm/mysql-8.0 Sources [946 B].
Get:11 http://repo.mysql.com/apt/debian bookworm/mysql-apt-config amd64 Packages [566 B]
Get:12 http://repo.mysql.com/apt/debian bookworm/mysql-8.0 amd64 Packages [12.7 kB]
Get:13 http://repo.mysql.com/apt/debian bookworm/mysql-tools amd64 Packages [4129 B]

We proceed to install MySQL with this command

sudo apt install mysql-server

The process will prompt us whether to enter a password, we leave it blank, so authentication will be via Socket, local only. We will configure it later

The installation at this point, asks us whether to use Strong Password Encryption or Legacy Authentication. I recommend keeping the recommended "Strong Passwords" if we don't have legacy applications that don't support it.

 ┌──────────────┤ Configuring mysql-community-server ├─────────────┐
  │ MySQL 8 uses a new authentication based on improved SHA256-based    
  │ password methods. It is recommended that all new MySQL Server      
  │ installations use this method going forward. This new   
  │ authentication plugin requires new versions of connectors and   
  │ clients, with support for this new authentication method      
  │ (caching_sha2_password). Currently MySQL 8 Connectors and   
  │ community drivers built with libmysqlclient21 support this new   
  │ method. Clients built with older versions of libmysqlclient may   
  │ not be able to connect to the new server.                           
  │ 
  | To retain compatibility with older client software, the default 
  | authentication plugin can be set to the legacy value 
  | (mysql_native_password) This should only be done if required
  | third-party software has not been updated to work with the new
  | authentication method. The change will be written to the file 
  | /etc/mysql/mysql.conf.d/default-auth-override.cnf                                                 
  |
  │ After installation, the default can be changed by setting the 
  | default_authentication_plugin server setting.
  |
  │ Select default authentication plugin
  │
  │ Use Strong Password Encryption (RECOMMENDED)
  │ Use Legacy Authentication Method (Retain MySQL 5.x)
  │
  │
  │
  | 
  |
  └────────────────────────────────────────────────────────────────┘

Checking status and restarting MySQL service

Checking the status of MySQL

After the installation is finished, the MySQL server should already be running, let's check if the process is active

sudo service mysql status

○ mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; preset: enabled)
     Active: inactive (dead) since Mon 2024-08-26 08:33:45 UTC; 1s ago
   Duration: 1min 18.811s
       Docs: man:mysqld(8)
             http://dev.mysql.com/doc/refman/en/using-systemd.html
    Process: 4614 ExecStartPre=/usr/share/mysql-8.0/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
    Process: 4650 ExecStart=/usr/sbin/mysqld (code=exited, status=0/SUCCESS)
   Main PID: 4650 (code=exited, status=0/SUCCESS)
     Status: "Server shutdown complete"
        CPU: 1.541s

MySQL service restart

If the service, as in this case is not active(Active: inactive) we can restart MySQL and repeat the check

sudo service mysql restart

sudo service mysql status

● mysql.service - MySQL Community Server.
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; preset: enabled)
     Active: active (running) since Mon 2024-08-26 08:32:25 UTC; 5s ago
       Docs: man:mysqld(8)
             http://dev.mysql.com/doc/refman/en/using-systemd.html
    Process: 4614 ExecStartPre=/usr/share/mysql-8.0/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
   Main PID: 4650 (mysqld)
     Status: "Server is operational"
      Tasks: 38 (limit: 9513)
     Memory: 371.2M
        CPU: 1.003s
     CGroup: /system.slice/mysql.service
             └─4650 /usr/sbin/mysqld

Let's check MySQL's default port, 3306 TCP, which should now be listening

sudo apt install net-tools

sudo netstat -tnplu |grep 3306

The command should return the following output, showing port 3306 listening locally

tcp6 0 0 :::3306 :::* LISTEN 4951/mysqld         
tcp6 0 0 :::33060 :::* LISTEN 4951/mysqld        

Set MySQL Root Password

To log in remotely, it will be necessary to set the MySQLroot password. We can use this command, but we recommend using a "strong" password, with numbers, upper and lower case, and special characters.

sudo mysql

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'LA_MIA_PASSWORD';
exit;

Need help?

Configuring MySQL 8.0

The server is up, for a new installation the configurations used are the default, security is poor, at this point it is recommended to run the DBMS configuration script to set authentications and remove anonymous logins

sudo mysql_secure_installation.

The script will guide us in configuring the server's security options.

As a first step we enable password control for users, and select strong passwords, with dictionary lookup of common passwords.

Securing the MySQL server deployment.
Enter password for user root:

We enter the root password we set earlier and enable password validation

VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of passwords
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

Selected strong "STRONG" passwords, we enter an effective root password

Change the password for root ? ((Press y|Y for Yes, any other key for No) : N

We remove anonymous users, to increase security

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

We configure local authentication of the root user, which will be via socket, only on the local machine

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

Remove test databases

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

Let's reload the privilege tables, so that we can apply our changes immediately.

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

Access MySQL from console

The installation is finished and we can log in with the root user from our server console, and list the databases present, for example. We will be prompted for the previously set password and the MySQL console will open

sudo mysql -u root -p
Enter password:

Having done the to MySQL we list the databases present

show databases;
+--------------------+
| Databases |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)

The output shows us the databases present, we were able to install MySQL in Debian 12 Bookworm, we can use with the usual syntax our MySQL, connect ne our Web App and CMS

Is it Complex?

Rely on our expert Debian Consulting, Free 30m analysis of your system

Common Error in Installing MySQL

mysql_secure_installation
 ... Failed! Error: SET PASSWORD has no significance for user 'root'@'localhost' as the authentication method used doesn't store authentication data in the MySQL server. Please consider using ALTER USER instead if you want to change authentication parameters.

You need to set the root password first, then run mysql_secure_installation

Changing the MySQL Root Password.

To log in remotely you will need to set the MySQL root password. We can use this command, but we recommend using a "strong" password, with numbers, upper and lower case, and special characters.

sudo mysql -p

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'LA_MIA_PASSWORD';

Logging into remote MySQL

To login to a remote MySQL, it is recommended that we use an SSH tunnel by redirecting our server's remote port 3306, which listens only in localhost, to our local port 33006

ssh root@IP-o-DNS -L 33006:localhost:3306

Thanks to the tunnel we could connect directly to our port 33006 in localhost with the root credentials created earlier

Accessing with MySQL Workbench

MySQL Workbench is a great tool for MySQL DBAs and Operation, it allows you to create DBs, manage server configuration, administer users, make backups and more, and already incorporates the SSH tunnel described above

1. Let's create a new connection
2. We select"Standard TCP/IP over SSH."
3. We enter the SSH configurations for our server of
   1. SSH Host name
   2. SSH User name
   3. SSH password or key
4. MySQL Database configurations
   1. Host name
   2. Server Port
   3. Username
   4. Password
5. Let's test the connection

FAQ Frequently asked questions

1. Is it better to use MariaDB or MySQL on Debian?

MariaDB and MySQL are similar, but MariaDB is an open-source fork of MySQL, often preferred for its greater freedom and some advanced features. If you need compatibility with existing software, choose MySQL. For new projects, MariaDB is a good alternative.

2. How to configure MySQL after installation on Debian?

After installing MySQL on Debian, you can configure it by running the command sudo mysql_secure_installation. This tool helps you set a root password, remove anonymous users, and disable remote access for the root user, improving server security.

3. What versions of MySQL are supported on Debian?

The versions supported depend on your version of Debian. For example, Debian 11 (Bullseye) supports MySQL 8.0 through the official repositories. You can also install earlier or later versions using the official MySQL repositories provided by Oracle.

4. How to upgrade MySQL on Debian without losing data?

To upgrade MySQL on Debian without losing data:

1. Make a full database backup using mysqldump.
2. Update the operating system with sudo apt update && sudo apt upgrade.
3. Check the current version of MySQL and upgrade the package with sudo apt install mysql-server.
4. Restart the MySQL service and verify that everything is working properly.

5. Is it possible to install MySQL on Debian using Docker?

Yes, you can install MySQL on Debian using Docker. Download the official MySQL image with the docker pull mysql command. Then, start a container with:

docker run --name mysql-server -e MYSQL_ROOT_PASSWORD=yourpassword -d mysql:latest

7. What are the system requirements to install MySQL on Debian?

Minimum requirements include:

• RAM: At least 512 MB (1 GB or more is recommended).
• Disk space: 600 MB for MySQL, plus additional space for databases.
• Processor: Modern CPU with 64-bit support.
• Network: Stable connection for updates and remote access.