How to Install MySQL on Debian 12?
MySQL is a popular open-source relational database management system (RDBMS). MySQL assists developers and system administrators in storing, managing, and retrieving data using SQL (Structured Query Language). MySQL organizes data in the form of relational tables, thus being well-suited for small applications or large enterprise applications. Executing MySQL on Debian 12, a secure and updated Linux distribution, MySQL gives a robust and secure platform well-suited for development and production.
In this guide, you will learn how to install MySQL on Debian 12, secure your database server, and implement best practices for user authentication. You might be a seasoned developer or installing your very first server, but this guide will walk you through the fundamentals—installation to creating your first database and user.
Prerequisites
Before you begin, make sure you have the following:
A Debian 12 server instance.
SSH access to the server.
A non-root user with sudo privileges.
The server is updated with the latest packages.
To update your server, run:
wget https://dev.mysql.com/get/mysql-apt-config_0.8.30-1_all.deb
Install MySQL on Debian 12
The MySQL server package isn’t included in Debian 12’s default APT repositories. To install it, follow these steps:
Download the MySQL APT Repository Package
wget https://dev.mysql.com/get/mysql-apt-config_0.8.30-1_all.deb
Visit the MySQL APT repository to check for the latest version, or download it using wget:
wget https://dev.mysql.com/get/mysql-apt-config_0.8.30-1_all.deb
Follow the prompts in the following MySQL repository set up script to install the latest database server sources.
Configuring mysql-apt-config ├─────────────────┐ │ Which MySQL product do you wish to configure? │ │ │ │ MySQL Server & Cluster (Currently selected: mysql-8.0) │ │ MySQL Tools & Connectors (Currently selected: Enabled) │ │ MySQL Preview Packages (Currently selected: Disabled) │ │ Ok
During the configuration prompt:
Keep MySQL Server & Cluster selected.
Choose your desired version (e.g., MySQL 8.0).
Use the arrow keys to select OK, then press Enter.
Update Your APT Cache
The MySQL repository information needs to be updated in the server’s package index.
sudo apt update
Install the MySQL database server package.
sudo apt install mysql-server –y
Press Enter when prompted to enter a new password for the root database user.
Enter root password:
The password needs to be entered again and pressed Enter to take effect.
Re-enter root password:
You can enable password authentication for all database users by selecting Use Strong Password Encryption (RECOMMENDED) and pressing Enter.
Use Strong Password Encryption (RECOMMENDED)
Use Legacy Authentication Method (Retain MySQL 5.x Compatibility)
You can view the version of MySQL installed on your server by clicking here.
mysql –version
Output:
mysql Ver 8.0.38 for Linux on x86_64 (MySQL Community Server - GPL)
Manage the MySQL System Service
Through the systemd daemon, MySQL manages database server runtime processes with mysqld. You can enable the MySQL database server to automatically start at boot time by following the steps below.
At boot time, enable the MySQL database server to automatically start.
sudo systemctl enable mysql
MySQL should be started.
sudo systemctl start mysql
You can view the status of the MySQL system service and verify that it is running.
sudo systemctl status mysql
Output
mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; preset: enabled)
Active: active (running) since Fri 2024-06-28 15:20:55 UTC; 52s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Main PID: 3302 (mysqld)
Status: "Server is operational"
Tasks: 36 (limit: 1092)
Memory: 433.4M
CPU: 662ms
CGroup: /system.slice/mysql.service
└─3302 /usr/sbin/mysqld
Secure the MySQL Database Server
Even though you set a password for the MySQL root user during installation, there are still a few default settings that could leave your server vulnerable. For example, MySQL might include a test database, allow anonymous users, or permit remote access to the root account—none of which are safe for a production environment.
To lock things down and follow best practices, you’ll use a built-in script called mysql_secure_installation. These steps will guide you through a series of prompts to help secure your database quickly and effectively.
Run the security script.
sudo mysql_secure_installation
Sure! Here’s a natural and clear rewrite of that line:
When the script starts, you’ll be asked to enter the MySQL root user’s password so it can begin applying the security changes.
Securing the MySQL server deployment.
Enter password for user root:
By entering Y, you will be able to activate the VALIDATE PASSWORD COMPONENT and ensure that passwords are kept secure.
Would you like to set up a VALIDATE PASSWORD component?
Press y|Y for Yes, any other key for No:
Password strength policies can be set for MySQL database servers. To enable strong passwords on your server, enter 2 and press Enter.
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
When prompted to change the root user’s password, type N to keep the current one, or Y if you’d like to set a new password.
Change the password for root ? ((Press y|Y for Yes, any other key for No) :
If you are prompted to remove anonymous users from the database server, enter Y and press Enter.
Remove anonymous users? (Press y|Y for Yes, any other key for No):
To disable remote access to the root database user, enter Y and press Enter.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) :
The test database will be removed from your server once you enter Y and press Enter.
Remove the test database and access it? (Press y|Y for Yes, any other key for No) :
Upon entering Y and pressing Enter, MySQL’s privileges table will be updated and your configuration changes will be applied.
Reload privilege tables now? (Press y|Y for Yes, any other key for No):
Output:
Success.
All done!
Access MySQL
You can interact with your MySQL database using the built-in mysql command-line client. It gives you full access to manage databases, run queries, and perform administrative tasks. If you prefer a graphical interface, tools like MySQL Workbench or phpMyAdmin are also great options.
Below, you’ll find the steps to connect to the MySQL console and perform some basic operations to get started.
The root database user should be logged in to the MySQL console.
sudo mysql -u root –p
When prompted, enter the root user password you created earlier.
Create a new sample database shop.
mysql > CREATE DATABASE shop;
Verify that the new sample shop database is available in all databases.
mysql > SHOW DATABASES;
Output:
+--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | shop | | sys | +--------------------+ 5 rows in set (0.00 sec)
Create and Manage a Sample Database in MySQL
Once you’re inside the MySQL console, you can start creating and working with your databases. Let’s walk through how to create a sample database, add a table with some data, and manage a user who can access it.
Step 1: Switch to the Sample Database
First, switch to the database you created earlier—called shop:
mysql> USE shop;
Database changed
Step 2: Create a Table
Now let’s create a table named products to store some example data. This table will have four columns: product_id, product_name, category, and price.
mysql> CREATE TABLE products (
-> product_id INT AUTO_INCREMENT PRIMARY KEY,
-> product_name VARCHAR(50) NOT NULL,
-> category VARCHAR(40),
-> price DECIMAL(10, 2)
-> );
Query OK, 0 rows affected (0.02 sec)
Step 3: Insert Data into the Table
Add a few sample records to the products table:
mysql> INSERT INTO products (product_name, category, price) VALUES
-> ('Laptop', 'Electronics', 999.34),
-> ('Office Chair', 'Furniture', 149.19),
-> ('Blender', 'Appliances', 49.20),
-> ('Backpack', 'Accessories', 39.57);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
Step 4: View the Table Contents
Verify that the data was inserted successfully:
mysql> SELECT * FROM products; +------------+--------------+-------------+--------+ | product_id | product_name | category | price | +------------+--------------+-------------+--------+ | 1 | Laptop | Electronics | 999.34 | | 2 | Office Chair | Furniture | 149.19 | | 3 | Blender | Appliances | 49.20 | | 4 | Backpack | Accessories | 39.57 | +------------+--------------+-------------+--------+ 4 rows in set (0.00 sec)
Step 5: Create a New MySQL User
Next, create a new user that can access this database. Replace the password with a secure one that meets your policy requirements:
mysql> CREATE USER 'db_user'@'localhost' IDENTIFIED BY 'Strong@@password123';
Query OK, 0 rows affected (0.01 sec)
Step 6: Grant Permissions
Give the new user full privileges to manage the shop database:
mysql> GRANT ALL PRIVILEGES ON shop.* TO 'db_user'@'localhost';
Query OK, 0 rows affected (0.01 sec)
Apply the changes:
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
Step 7: Exit and Log In as the New User
Exit the MySQL console:
mysql> EXIT Bye
Now log in again, this time using the new db_user account:
$ sudo mysql -u db_user -p
Enter password:
Step 8: Verify Access
Once logged in, list the available databases to confirm that the shop database is accessible:
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | performance_schema | | shop | +--------------------+ 3 rows in set (0.00 sec)
Step 9: Exit the Console
You’re all set! Type EXIT to leave the MySQL console:
mysql> EXIT
Bye
Wrapping it Up
You’ve installed MySQL on a Debian 12 server and done a lot to make your database secure by establishing a strong root password, removing anonymous users, turning off remote root login, and instituting a password verification policy. Having MySQL installed and secure, your server is now prepared to house and process data both efficiently and with reliability. You’ve established a sample database and user and demonstrated how to organize and control access to your data.
MySQL is now compatible with any number of applications like PHP, Python, or Node.js to develop dynamic, data-driven web applications. Whether you’re developing a personal project, an e-commerce website, or deploying a high-scale production application, MySQL offers the performance and versatility to secure complex data operations.
