How to Install MySQL on Debian

Technical writer

Debian MySQL

14.01.2025

Reading time: 8 min

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.

This combination is especially beneficial for administrators, DB analysts, and industries that demand a dependable database solution for dealing with huge data. Additionally, MySQL's comprehensive guide and supporters help make it simpler to troubleshoot problems and enhance operations.

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

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

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

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

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

Step 3: Refresh the System

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

sudo apt update

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

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:

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

mysql --version

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

Step 6: MySQL Secure Installation

The key or password that the individual created at the initialising procedure is currently protecting the root DB user on the server. MySQL also includes other insecure defaults, such as remote access to test databases and the root database user on the server.

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:

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.
Eliminate Anonymous Users: It is advised to eliminate the accessibility of anonymous users. For this, input Y and Enter when prompted.
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.
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.
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

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

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:

Create a Database:

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

CREATE DATABASE hostmandb;

Display All Databases:

List all databases to make sure hostmandb is created:

SHOW DATABASES;

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

Give Permissions to the User:

Give complete access to the hostmandb to the new user:

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

Flush Privileges:

To implement the modifications, refresh the table:

FLUSH PRIVILEGES;

Exit the Shell:

For closing the interface, utilize the EXIT statement:

EXIT;

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

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

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

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

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

bind-address = 0.0.0.0

Reload the MySQL service:

sudo systemctl restart mysql

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

If you have a firewall activated, you need to open the MySQL port 3306 to traffic. Set up the firewall following the below steps:

Allow traffic through MySQL:

sudo ufw allow mysql

Now, activate the UFW on the system:

sudo ufw enable

Reload the firewall:

sudo ufw reload

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

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)

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

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.

Debian MySQL

14.01.2025

Reading time: 8 min

Similar

Debian

How to Install MySQL on Debian

How to Install MySQL on Debian

Step 1: Download MySQL Package

Step 2: MySQL Configuration Package Installation

Step 3: Refresh the System

Step 4: MySQL Installation

Step 5: Managing the Services

Step 6: MySQL Secure Installation

Step 7: Access MySQL

Step 8: Basic MySQL Operations

Step 9: Configuration for Remote Access

Step 10: Firewall Configuration

Step 11: Restore and Backup

Step 12: Optimize MySQL Operations (Optional)

Conclusion

Similar

How to Install the Latest Static Build of FFmpeg on Debian

How to Install and Use Docker on Debian

Installing Debian on Ubuntu via VirtualBox

Do you have questions, comments, or concerns?

Do you have questions,
comments, or concerns?