In this tutorial, we'll see how to install and configure MariaDB on Ubuntu 24.04 server.
In the world of web hosting, a reliable database server is the backbone of many applications. MariaDB, a popular fork of MySQL, delivers high performance, robust security, and active community support. In this guide, we’ll walk through installing MariaDB on a Ubuntu 24.04 server and performing essential configuration to ensure smooth operation for our hosting environment.
Why Choose MariaDB?
MariaDB offers several advantages for web hosting:
- Performance: Optimized query execution and storage engines.
- Compatibility: Seamless drop-in replacement for MySQL.
- Security: Regular updates and built-in encryption options.
- Open Source: Backed by a strong developer community.
Embracing MariaDB ensures that our hosted applications benefit from a fast, secure, and well-supported database platform.
Prerequisites
Before beginning, ensure that our server meets the following requirements:
- A Ubuntu 24.04 installed dedicated server or KVM VPS.
- Root or Sudo Privileges: You should have sudo privileges to install packages and make system-wide changes.
With these in place, we’re ready to install MariaDB.
Install and Configure MariaDB on Ubuntu 24.04
Installing MariaDB Server
sudo apt update
sudo apt install mariadb-server mariadb-client -y
This command fetches MariaDB packages from official repositories and installs both server and client tools.
Once installation completes, MariaDB binaries and configuration files are placed under /etc/my.cnf or /etc/mysql/ (depending on distribution), ready for initial setup.
Starting and Enabling the Service
After installing, we’ll start the MariaDB service and configure it to launch at boot:
sudo systemctl start mariadb
sudo systemctl enable mariadb
Checking the service status confirms that the database server is running:
sudo systemctl status mariadb
A status of “active (running)” indicates that MariaDB is operational and will automatically start after any server reboot.
Securing MariaDB with mysql_secure_installation
To protect our hosting environment, running MariaDB’s built-in security script is essential. This script guides through disabling remote root logins, removing anonymous users, and setting a strong root password. Execute:
sudo mysql_secure_installation
During the prompts:
Set root password? Choose Y and enter a robust password.
Remove anonymous users? Choose Y.
Disallow root login remotely? Y ensures root access is local only.
Remove test database? Choose Y to eliminate unused databases.
Reload privilege tables? Y applies changes immediately.
Completing this script enhances the baseline security of our database server, guarding against unauthorized access.
Basic Configuration Tweaks
MariaDB’s main configuration file—typically /etc/mysql/my.cnf or /etc/my.cnf—allows customization of performance and access controls. We recommend the following initial tweaks:
Bind Address
By default, MariaDB listens on all interfaces. For added security, restrict it to localhost or a private network IP.
[mysqld]
bind-address = 127.0.0.1
This setting ensures that only applications on the same server (or permitted hosts via SSH tunnels) can connect.
Adjusting Buffer Sizes
For shared hosting or moderate traffic sites, increasing the key buffer can improve performance:
key_buffer_size = 64M
Memory-heavy environments may require higher values; adjust based on available RAM.
Enable Slow Query Log
Identifying inefficient queries helps optimize database performance. Enable logging of slow queries:
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
Queries exceeding 2 seconds will be recorded, allowing proactive debugging.
After editing, restart MariaDB to apply changes:
sudo systemctl restart mariadb
Creating an Administrative User
Beyond the root account, maintaining a dedicated database administrator (DBA) user is best practice. From the MariaDB prompt, run:
CREATE USER 'dba'@'localhost' IDENTIFIED BY 'StrongPassw0rd!';
GRANT ALL PRIVILEGES ON *.* TO 'dba'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;
This dba user can manage databases without using the root account, reducing the risk of unintended system-level operations.
Testing the Connection
To confirm that everything is working as expected, connect with the DBA user:
mysql -u dba -p
After entering the password, we should see the MariaDB prompt. A simple version check can be performed with:
SELECT VERSION();
Successful output verifies that the database server is ready for hosting applications.
Perform CRUD
Below is a simple set of CRUD (Create, Read, Update, Delete) operations against a sample users table in MariaDB. We’ll assume we’ve already logged in as our administrative user and switched to the appropriate database.
1. Create a Sample Table
First, let’s define a table to work with:
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(150) UNIQUE NOT NULL,
registered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
This creates a users table with an auto-incrementing primary key, user name, unique email, and a timestamp.
2. Create (INSERT)
To add new records into users:
INSERT INTO users (name, email)
VALUES
('Alice Johnson', 'alice@example.com'),
('Bob Kumar', 'bob.kumar@example.org');
Here, we’ve inserted two users in a single statement.
3. Read (SELECT)
a. Select All Users
SELECT * FROM users;
b. Select a Specific User
SELECT id, name, email
FROM users
WHERE email = 'alice@example.com';
These queries retrieve either every row or a filtered subset.
4. Update (UPDATE)
To modify an existing record—for example, correcting Bob’s email address:
UPDATE users
SET email = 'bob.kumar@example.com'
WHERE name = 'Bob Kumar';
After running this, exactly those matching rows will have their email updated.
5. Delete (DELETE)
To remove a user—for instance, deleting Alice’s account:
DELETE FROM users
WHERE name = 'Alice Johnson';
This deletes all rows matching the WHERE clause. If we need to be extra cautious, we can first SELECT to confirm which rows will be removed.
6. Verifying Changes
After each operation, we can run:
SELECT * FROM users;
to ensure our data reflects the intended state.
Conclusion
By following these steps, we’ve installed MariaDB on our Linux server, enforced critical security measures, and fine-tuned basic configuration settings. This setup forms a solid foundation for any database-driven website or application within our hosting environment. With MariaDB’s robust performance and security, we can confidently support dynamic workloads and scale as our clients’ needs evolve.
