How to Install MySQL on Debian

Installing MySQL on Debian effectively creates a robust and flexible database (DB) infrastructure that accommodates a wide range of applications as well as services. It is renowned for its scalability, dependability, and durability. By setting it, individuals experience the operations in an efficient manner and enhance the overall efficiency of DB infrastructure.

Administrators, database analysts, and industries that require a reliable database solution for handling massive amounts of data will particularly benefit from this combination. Additionally, troubleshooting issues and improving operations are made easier by MySQL's extensive guide and supporters.

In this guide, we will demonstrate the thorough procedure for installing and configuring MySQL on Debian.

Installing MySQL on Debian can be confusing at first

How to Install MySQL on Debian

The default repositories do not contain the MySQL database server package on Debian. To install it on a  Linux system follow the below instructions. We will download the recent version of the MySQL.

Step 1: Download MySQL Package

Let us obtain the MySQL repository information package, which is in the .deb format:

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

Downloading the mysql-apt-config package from the official MySQL repository

Note: To authenticate the most updated release, go to the MySQL repository webpage.

Step 2: MySQL Configuration Package Installation

Then, employ the .deb file for initializing the installation via dpkg:

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

Installing the MySQL configuration packag using dpkg.

Respond to the prompt. For instance, pick MySQL Server & Cluster and hit Enter for starting configurations:

Selecting MySQL Server & Cluster from the configuration options in the MySQL APT repository setup

For picking a version such as (mysql-8.4-lts), scroll downward and hit OK for the next step:

Choosing the desired MySQL server version (for example, mysql-8.4-lts) during the configuration process

Step 3: Refresh the System

Now, update the server's package indexes to implement the updated MySQL info:

sudo apt update

Updating the package lists on Debian before installing MySQL

Step 4: MySQL Installation

Debian's default manager makes sure to install MySQL in an easier manner. Installing the package with this command:

sudo apt install mysql-server -y

Installing MySQL Server on Debian using apt

You will see the interface for setting the root account. Input a stronger password to secure the database. In the end, hit the Ok button:

Setting the root password for the MySQL server during installation

Check the version on the server via the --version utility:

mysql --version

Verifying the installed MySQL version using the mysql --version command

Step 5: Managing the Services

Now, you can enable the MySQL service to initialize automatically at boot time:

sudo systemctl enable mysql

Activate the service via the systemctl utility:

sudo systemctl start mysql

Check if the system service is operational by viewing its status:

sudo systemctl status mysql

Verifying that the MySQL service is active and running using systemctl status mysql

Before you open port 3306 to clients, take a look at Creating an SSH Tunnel for MySQL tutorial—you’ll learn how to tunnel your connections over SSH so you never have to expose MySQL directly to the internet.

Step 6: MySQL Secure Installation

The root database user on the server is now protected by the key or password that the person created during the initialization process. Other unsafe defaults in MySQL include remote access to test databases and the server's root database user.

It is vital to secure the MySQL installation after it has been completed as well as disable all unsafe default settings. There is a security script that can assist us in this procedure. Run the script:

sudo mysql_secure_installation

Running mysql_secure_installation to configure security options and enable password validation policies

To activate the VALIDATE PASSWORD component and guarantee stringent password procedures, type Y and hit Enter.

Next, you will need to configure several security settings:

1. Set the Root Password: Select a strong password and make sure that it is correct. Configure the password policy for the DB server. For instance, type 2 to permit only the strong passwords on the server and hit Enter. When required to modify the root password, input N; alternatively, input Y to modify the password.
2. Eliminate Anonymous Users: It is advised to eliminate the accessibility of anonymous users. For this, input Y and Enter when prompted.
3. Prevent Accessibility of Remote Root: It is a better practice to avoid remote root login for multiple security concerns. To prevent the root user from having a remote access, input Y and hit Enter.
4. Delete the Test DB: For enhancing security, the test database, which is utilized for testing, can be deleted. To do so, input Y and hit Enter.
5. Refreshing Privilege Tables: It guarantees that all modifications are implemented instantly. To implement the configuration and edit the privileges table, hit Enter.

Removing the default test database and reloading privilege tables for improved security

Step 7: Access MySQL

Utilizing the mysql client utility, MySQL establishes the connection and provides access to the database server console. 

Now, access the shell interface and run general statements on the DB server. Let’s input the root and the password created at the time of the safe installation procedure:

sudo mysql -u root -p

Logging into the MySQL shell as the root user with sudo mysql -u root -p

Step 8: Basic MySQL Operations

The creation of a DB and a new user for your applications rather than utilizing the root is a better practice. To accomplish the task, employ the given instructions:

1. Create a Database:

First, create a database. For instance, hostmandb is created via the below command:

CREATE DATABASE hostmandb;

Creating a new database named hostmandb inside MySQL

2. Display All Databases:

List all databases to make sure hostmandb is created:

SHOW DATABASES;

Listing available databases with the SHOW DATABASES; command

3. Create of a New User:

Create a user and assign a strong password. In our example, we set Qwer@1234 as a password for the user  minhal. Replace these values with your data.

CREATE USER 'minhal'@'localhost' IDENTIFIED BY 'Qwer@1234';

Creating a new MySQL user with a secure password

4. Give Permissions to the User:

Give complete access to the hostmandb to the new user:

GRANT ALL PRIVILEGES ON hostmandb.* TO 'minhal'@'localhost';

Granting all privileges on the hostmandb database to the new user

5. Flush Privileges:

To implement the modifications, refresh the table:

FLUSH PRIVILEGES;

Applying the changes with the FLUSH PRIVILEGES; command

6. Exit the Shell:

For closing the interface, utilize the EXIT statement:

EXIT;

Exiting the MySQL shell with the EXIT; command

7. Access MySQL Console as the Particular User

For the purpose of testing hostmandb access, log in to MySQL as the new user, in our case minhal.

sudo mysql -u minhal -p

It accesses the console after entering the minhal user password when prompted:

Logging into MySQL with the newly created user account

For verification, display all DBs and confirm that the hostmandb is available:

SHOW DATABASES;

Step 9: Configuration for Remote Access

Setting up the server for supporting remote accessibility is necessary if an individual is required to access MySQL remotely. Follow these steps:

1. Access the mysql.cnf file and modify the particular file for MySQL:

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

Opening the MySQL configuration file (mysqld.cnf) to enable remote access

2. Look for the line with the bind-address and change it to:

bind-address = 0.0.0.0

Updating the bind-address parameter to 0.0.0.0 to allow remote connections

3. Reload the MySQL service:

sudo systemctl restart mysql

Restarting the MySQL service to apply the configuration changes

4. Permit the user to have remote access:

sudo mysql -u root -p

GRANT ALL PRIVILEGES ON hostmandb.* TO 'minhal'@'localhost';
FLUSH PRIVILEGES;
EXIT;

Granting privileges, flushing changes, and exiting the MySQL shell.

Step 10: Firewall Configuration

You must allow traffic on MySQL port 3306 if you have a firewall turned on. Follow these procedures to set up the firewall:

1. Allow traffic through MySQL:

sudo ufw allow mysql

Allowing MySQL traffic through the firewall with ufw allow mysql

2. Now, activate the UFW on the system:

sudo ufw enable

Enabling the UFW firewall to start protecting the system

3. Reload the firewall:

sudo ufw reload

Reloading the UFW firewall to apply the updated rules

Step 11: Restore and Backup

Maintaining regular backups is crucial to avoiding data loss. The mysqldump utility is provided by MySQL for backup creation. To achieve this, consider these instructions:

• Backup a Single Database:

This command employs mysqldump to create the backup of the hostmandb as a hostmandb_backup.sql file:

sudo mysqldump -u root -p hostmandb> hostmandb_backup.sql

Creating a backup of the hostmandb database using mysqldump

• Backup All Databases:

For creating a backup of all databases as a file named all_databases_backup.sql with root privileges, utilize mysqldump:

sudo mysqldump -u root -p --all-databases > all_databases_backup.sql

Exporting all MySQL databases into a single backup file

• Restore a Particular Database:

Now, restore the hostmandb from the backup file hostmandb_backup.sql:

sudo mysql -u root -p hostmandb < hostmandb_backup.sql

Restoring the hostmandb database from a backup file

Step 12: Optimize MySQL Operations (Optional)

Depending on the workload and server resources, you can adjust settings to guarantee peak performance. These instructions will help you maximize MySQL's speed:

• Adjust InnoDB Buffer Pool Size:

Caches for data and indexes are kept in the InnoDB buffer pool. Expanding its size can enhance its functionality. Edit the MySQL configuration file:

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

The below line should be added or changed:

innodb_buffer_pool_size = 1G

using MySQL performance by setting the InnoDB buffer pool size in the configuration file

Its size should be adjusted according to the amount of memory on the server.

• Enable Query Cache:

The query cache stores the outcome of SELECT queries. Enabling it can enhance operations for repetitive queries. Modify the .cnf file:

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

Add or edit the below lines:

query_cache_type = 1
query_cache_size = 64M

Configuring query cache settings in the MySQL configuration file for better performance

• Optimize Table Structure:

Frequently optimize your customers table in hostmandb to recover wasted space and boost efficiency:

USE hostmandb;
OPTIMIZE TABLE customers;

Running OPTIMIZE TABLE on the customers table to improve efficiency and reclaim unused space

• Analyze Operations:

DB operations can be tracked and analyzed with tools like MySQL Workbench and mysqltuner. Using the command below, install mysqltuner:

sudo apt install mysqltuner

Installing mysqltuner to analyze and optimize MySQL performance

Run mysqltuner to get performance recommendations:

sudo mysqltuner

Running mysqltuner to analyze the MySQL server configuration and receive performance recommendations

Conclusion

Installing a MySQL environment is important in today's digital world. By following this instruction, you'll be able to safely install and connect to your MySQL database. This strategy not only increases security but also improves remote database maintenance efficiency. It helps to prevent breaches and ensures the confidentiality of your data.

This article has given thorough instructions for the installation of MySQL's database environment on Debian. It is suggested that MySQL servers should be regularly monitored and optimized to guarantee optimum performance and dependability.

In addition, Hostman offers pre-configured and ready-to-use cloud databases, including cloud MySQL. 

When you know how to install MySQL on Debian