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.

And if you’re looking for a reliable, high-performance, and budget-friendly solution for your workflows, Hostman has you covered with Linux VPS Hosting options, including Debian VPS, Ubuntu VPS, and VPS CentOS. 

In this guide, we will demonstrate the thorough procedure for installing and configuring MySQL on Debian. If you'd like to integrate MySQL on your website, you can check our Wordpress hosting.

How to Install MySQL on Debian

Copy link

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

Copy link

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

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

Step 2: MySQL Configuration Package Installation

Copy link

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

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

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

MySQL installer window on Debian

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

MySQL installer window on Debian

Copy link

Step 3: Refresh the System

Copy link

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

sudo apt update

Step 4: MySQL Installation

Copy link

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

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:

MySQL installer window on Debian

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

mysql --version

Step 5: Managing the Services

Copy link

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

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

Copy link

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

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.

Step 7: Access MySQL

Copy link

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

Step 8: Basic MySQL Operations

Copy link

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;

2. Display All Databases:

List all databases to make sure hostmandb is created:

SHOW DATABASES;

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';

4. Give Permissions to the User:

Give complete access to the hostmandb to the new user:

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

5. Flush Privileges:

To implement the modifications, refresh the table:

FLUSH PRIVILEGES;

6. Exit the Shell:

For closing the interface, utilize the EXIT statement:

EXIT;

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:

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

SHOW DATABASES;

Step 9: Configuration for Remote Access

Copy link

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

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

bind-address = 0.0.0.0

3. Reload the MySQL service:

sudo systemctl restart mysql

4. Permit the user to have remote access:

sudo mysql -u root -p

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

Step 10: Firewall Configuration

Copy link

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

2. Now, activate the UFW on the system:

sudo ufw enable

3. Reload the firewall:

sudo ufw reload

Step 11: Restore and Backup

Copy link

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

• 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

• Restore a Particular Database:

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

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

Step 12: Optimize MySQL Operations (Optional)

Copy link

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

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

• Optimize Table Structure:

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

USE hostmandb;
OPTIMIZE TABLE customers;

• 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

Run mysqltuner to get performance recommendations:

sudo mysqltuner

Conclusion

Copy link

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.

Hostman can help you deploy NoSQL or SQL cloud database on one of the most popular engines in just seconds. With an intuitive interface and around-the-clock free support, deploying MySQL cloud or Postgres cloud becomes much easier. Don't forget to check our free cloud databases!

Frequently Asked Questions (FAQ)

Copy link

How do I install actual MySQL (not MariaDB) on Debian? 

Copy link

Since Debian ships with MariaDB by default, you must add the official MySQL repository first:

Download the repository package:

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

Check for the latest version before running the command.

Install it:

sudo dpkg -i mysql-apt-config*

Update and install:

sudo apt update && sudo apt install mysql-server

What is the difference between MySQL and MariaDB on Debian? 

Copy link

MariaDB is a community-developed fork of MySQL and is the default database provider in Debian repositories. If you run sudo apt install default-mysql-server, you will get MariaDB. You generally only need Oracle's MySQL if you have specific legacy compatibility needs.

How do I secure my MySQL installation? 

Copy link

Immediately after installing, run the included security script:

sudo mysql_secure_installation

This tool guides you through setting a root password, removing anonymous users, and disabling remote root login.

How do I check if the MySQL service is running? 

Copy link

Run the command:

sudo systemctl status mysql

Note: Even if you installed MariaDB, the service often responds to both mysql and mariadb commands.

How do I allow remote connections to MySQL? 

Copy link

By default, MySQL only listens on localhost. To change this:

Edit the config file:

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

Change bind-address = 127.0.0.1 to bind-address = 0.0.0.0.

Restart the service:

sudo systemctl restart mysql

How do I create a new user and database? 

Copy link

Log in to the shell:

sudo mysql -u root -p

Run:

CREATE DATABASE my_app;
CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON my_app.* TO 'user'@'localhost';
FLUSH PRIVILEGES;