How to Create a Database in MySQL/MariaDB on Debian 12 Bookworm System

Whether you’re setting up a web application, developing a custom script, or managing a large system backend, at some point you’ll likely need a database. On Debian 12 Bookworm, two of the most popular relational database systems available are MySQL and MariaDB. While these two systems are technically distinct, they are compatible in many ways, especially at the user level for basic operations like creating and managing databases.

This guide will walk you through the process of creating a database in MySQL or MariaDB on Debian 12 Bookworm, including installation, basic configuration, and best practices.

Why Use MySQL or MariaDB?

MySQL and MariaDB are both open-source relational database management systems (RDBMS) known for their performance, reliability, and support across many applications, including popular content management systems like WordPress, Joomla, and Drupal.

• MySQL: Backed by Oracle Corporation, MySQL is widely used in enterprise and web applications.
• MariaDB: A community-developed fork of MySQL, maintained by the original MySQL developers. It aims to remain free under the GNU GPL and provides additional features and performance improvements.

Both are available in the official Debian repositories.

Step 1: Update Your System

Before installing any software, it’s best to update the package list and existing packages:

sudo apt update && sudo apt upgrade -y

Step 2: Install MySQL or MariaDB

You can choose to install MySQL or MariaDB, depending on your preference or the software requirements.

Installing MariaDB (Recommended for Debian)

MariaDB is the default in many Debian-based systems.

sudo apt install mariadb-server mariadb-client -y

Installing MySQL (If you prefer MySQL)

If you need MySQL instead, you may have to add the official MySQL APT repository, as the version in Debian’s repositories may be outdated:

sudo apt install wget lsb-release gnupg -y
wget https://dev.mysql.com/get/mysql-apt-config_0.8.29-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.29-1_all.deb
sudo apt update
sudo apt install mysql-server -y

Note: The version of the .deb package may change. Check MySQL’s APT repository for the latest.

Step 3: Secure the Installation

Both MySQL and MariaDB come with a script to enhance security:

sudo mysql_secure_installation

You’ll be prompted to:

• Set the root password (for MySQL)
• Remove anonymous users
• Disallow root login remotely
• Remove test databases
• Reload privilege tables

Answer yes (Y) to each for a more secure setup.

Step 4: Log into the MySQL/MariaDB Shell

Once installed and secured, you can access the MySQL or MariaDB shell using the following command:

sudo mysql

Or, if you set a password for the root user and want to login with it:

mysql -u root -p

You should now see a prompt like:

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.11.6-MariaDB-1 Debian 12

MariaDB [(none)]>

Step 5: Create a New Database

Now that you’re inside the MySQL/MariaDB shell, you can create a database with a simple SQL command:

CREATE DATABASE your_database_name;

Replace your_database_name with something descriptive, like wordpress_db, inventory, or school_data.

Example:

CREATE DATABASE blog_site;

You can verify the database was created:

SHOW DATABASES;

Step 6: Create a User and Grant Privileges

Creating a separate user for each application or service is a best practice for both security and manageability.

Create a New User

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

Replace username and password with your desired credentials.

Example:

CREATE USER 'bloguser'@'localhost' IDENTIFIED BY 'StrongPassword123!';

Grant Privileges

Next, give the user full access to the database you created:

GRANT ALL PRIVILEGES ON your_database_name.* TO 'username'@'localhost';

Example:

GRANT ALL PRIVILEGES ON blog_site.* TO 'bloguser'@'localhost';

Apply Changes

FLUSH PRIVILEGES;

This makes sure all changes take immediate effect.

Step 7: Test the New User

Exit the root shell:

EXIT;

Now try logging in as the new user:

mysql -u bloguser -p

You’ll be prompted for the password you set earlier. Once logged in, you can select your database and begin working with it:

USE blog_site;

Step 8: Basic SQL Operations

With your new database ready, here are a few basic operations you might want to perform.

Create a Table

CREATE TABLE posts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(100),
  content TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Insert Data

INSERT INTO posts (title, content) VALUES ('First Post', 'Hello world! This is our first blog post.');

Query Data

SELECT * FROM posts;

Step 9: Enable and Monitor the Service

Ensure the database server is enabled to start on boot:

sudo systemctl enable mariadb
# or for MySQL
sudo systemctl enable mysql

You can also check the status of the service:

sudo systemctl status mariadb
# or
sudo systemctl status mysql

If something goes wrong, the logs are your best friend:

sudo journalctl -u mariadb
# or
sudo journalctl -u mysql

Bonus: Connect from an Application

Once your database is created and a user has been granted access, connecting from applications like PHP, Python (via mysql-connector-python or PyMySQL), or web platforms like WordPress is simple.

You’ll usually need the following details:

• Host: localhost (or IP if remote)
• Database Name: blog_site
• Username: bloguser
• Password: your password

This information is typically configured in the application’s settings or configuration file.

Best Practices

1. Use Strong Passwords – Always use complex passwords for database users.
2. Limit Access – Only grant necessary permissions to each user.
3. Regular Backups – Use mysqldump or tools like Percona XtraBackup.
4. Secure Remote Access – Use SSH tunnels or SSL for remote connections.
5. Monitor Usage – Use tools like mysqltuner or performance_schema for optimization.

Conclusion

Creating a database in MySQL or MariaDB on Debian 12 Bookworm is a straightforward process, but it’s crucial to follow best practices to maintain performance and security. Whether you’re developing a small blog or managing a large enterprise application, understanding how to create and manage databases is an essential skill.

With Debian’s stability and the power of MySQL/MariaDB, you’re well-equipped to handle many types of data-driven projects. From here, you can explore more advanced topics like database replication, performance tuning, and high availability.