How to set up a MariaDB database on Linux

By the end of this guide you'll have MariaDB installed on a Linux server, locked down with sensible defaults, and a real database with its own dedicated user ready for an app to connect to. This is written for anyone running their own server or VPS who needs a SQL database for a website, a Discord bot, or any project that has to store data. You don't need to be a database expert to follow along.

MariaDB is a drop in replacement for MySQL. It started as a fork years ago and the two are close enough that most tools, commands, and even the mysql client work the same way across both. If a tutorial somewhere mentions MySQL, the steps here will usually apply with no changes.

Before you start

You'll need a Linux server you can reach over SSH and an account that can run sudo. The commands below assume Debian or Ubuntu, which is what most VPS images ship with. Where things differ on Rocky, AlmaLinux, or other RHEL family systems, I'll point it out. MariaDB is light on resources for small projects, so even a 1 GB plan handles a personal site or a bot just fine.

One habit worth picking up early: run sudo commands deliberately and read what they print. A database holds your data, and a sloppy command can wipe it. Take your time.

Step 1: Install MariaDB

First refresh your package lists, then install the server and the client tools. The server is the background service that actually stores data. The client is the mysql command you'll use to talk to it.

sudo apt update
sudo apt install mariadb-server mariadb-client

On a RHEL family system the equivalent is:

sudo dnf install mariadb-server mariadb

Once it finishes, start the service and set it to launch on every boot. If you skip the enable part, the database will be down after your next reboot and you'll spend a confusing ten minutes wondering why your app can't connect.

sudo systemctl start mariadb
sudo systemctl enable mariadb

Check that it's actually running:

sudo systemctl status mariadb

You're looking for a green active (running) line in the output. Press q to exit the status view and get your prompt back.

Step 2: Run mysql_secure_installation

A fresh install is not safe to leave as is. There's a script that walks you through tightening it up, and you should run it every single time you set up a new server.

sudo mysql_secure_installation

It asks a short series of questions. Here's what each one means so you're not guessing:

• Enter current password for root: on a brand new install there isn't one yet, so just press Enter.
• Switch to unix_socket authentication: on recent MariaDB versions the root user logs in through the system socket rather than a password. Answering yes is fine and it's the modern default. It means you log in as root by being root on the box, using sudo.
• Change the root password: if you want a password as well as socket login, set one here. Pick something long and store it in a password manager.
• Remove anonymous users: yes. These let people connect without an account, which you never want.
• Disallow root login remotely: yes. Root should only ever connect from the local machine.
• Remove test database: yes. It's a leftover that anyone can access.
• Reload privilege tables now: yes, so your choices take effect right away.

Say yes to all of those unless you have a specific reason not to. When it finishes you'll see a short "All done!" message.

Step 3: Log in for the first time

Because root now uses socket authentication, you log in with sudo and no password:

sudo mariadb

You can also type sudo mysql and land in the same place. Your prompt changes to this:

MariaDB [(none)]>

That's the SQL shell. Everything you type here is a SQL statement, and statements end with a semicolon. Forget the semicolon and the shell just sits there on a new line waiting for you to finish. To prove the connection works, list the databases that already exist:

SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

Those three are system databases. Leave them alone. You'll be adding your own next to them.

Step 4: Create a database and a dedicated user

Never let your app connect as root. Instead, make one database for the app and one user that can only touch that database. If that account ever leaks, the damage is limited to a single database instead of your whole server.

Still inside the SQL shell, create the database. I'll use appdb as the name, but swap in whatever fits your project.

CREATE DATABASE appdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

The utf8mb4 part matters more than it looks. It's the character set that stores the full range of Unicode, including emoji and many non Latin scripts. The older utf8 in MySQL land is actually incomplete and will choke on certain characters, so reach for utf8mb4 by default.

Now create a user. The 'appuser'@'localhost' form means this account can only connect from the server itself, which is what you want when your app runs on the same box as the database.

CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'a-long-random-password-here';

Pick a genuinely random password. Don't reuse one. Then grant that user full rights on the new database and nothing else:

GRANT ALL PRIVILEGES ON appdb.* TO 'appuser'@'localhost';
FLUSH PRIVILEGES;

The appdb.* part is the key. It reads as "every table inside appdb" and nothing beyond it. The FLUSH PRIVILEGES line tells MariaDB to reload its permission tables so the grant is live immediately. Exit the shell:

EXIT;

Test the new account from the command line. This time you supply the username and ask to be prompted for the password:

mariadb -u appuser -p appdb

Type the password when asked. If you land at the MariaDB [appdb]> prompt, the user works and is already pointed at the right database. That's the credentials you'll hand to your application.

Step 5: Local versus remote access and bind-address

By default MariaDB only listens on the loopback address, 127.0.0.1. In plain terms, the database accepts connections from programs running on the same machine and refuses everything coming from the outside network. For the common case, where your website or bot lives on the same server as the database, this is exactly right and you should leave it that way. A database that the whole internet can reach is a database that gets attacked.

You only need to change this if the app and the database live on separate servers. The setting that controls it is bind-address, and you'll find it in the config file:

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

On RHEL family systems the file is usually /etc/my.cnf.d/mariadb-server.cnf instead. Look for this line:

bind-address = 127.0.0.1

To accept connections from other machines, point it at the server's private network IP rather than its public one where possible:

bind-address = 10.0.0.5

You can set it to 0.0.0.0 to listen on every interface, but only do that if a firewall is doing the real gatekeeping, which I'll cover in the next step. Save the file (in nano that's Ctrl+O, Enter, then Ctrl+X) and restart so the change loads:

sudo systemctl restart mariadb

Step 6: Allow remote connections safely

Changing bind-address is only half the job. The user account you made earlier was tied to localhost, so it still won't accept a connection coming from another server. You need a user whose host matches where the connection comes from.

Log back in and create a user scoped to the IP of the machine that will connect. Say your app server sits at 10.0.0.20:

sudo mariadb

CREATE USER 'appuser'@'10.0.0.20' IDENTIFIED BY 'a-long-random-password-here';
GRANT ALL PRIVILEGES ON appdb.* TO 'appuser'@'10.0.0.20';
FLUSH PRIVILEGES;
EXIT;

Pinning the user to a specific IP is far safer than using a % wildcard, which would allow that username from anywhere. If you genuinely don't know the source IP ahead of time, narrow it as much as you can rather than opening it wide.

Next, the firewall. Only the app server should be allowed through to port 3306, the default MariaDB port. With the UFW firewall on Ubuntu you'd write a rule that names the source:

sudo ufw allow from 10.0.0.20 to any port 3306

That single line is the difference between "one trusted server can reach my database" and "every bot scanning the internet can hammer my login." Do not open 3306 to the world. From the app server, confirm you can reach the database:

mariadb -u appuser -p -h 10.0.0.5 appdb

The -h flag is the database server's address. If you get a prompt and then the SQL shell, remote access is working.

Step 7: Back up your database with mysqldump

A database is only as good as its last backup. The built in tool for this is mysqldump, which writes the entire database out as a plain text file full of SQL statements. Run it from a normal shell, not from inside the SQL prompt:

mysqldump -u appuser -p appdb > appdb-backup.sql

That produces appdb-backup.sql in your current folder. It's a regular text file, so you can open it and see the CREATE TABLE and INSERT lines that rebuild your data. For anything beyond a tiny database, compress it on the way out to save space:

mysqldump -u appuser -p appdb | gzip > appdb-backup.sql.gz

Restoring is the reverse. You feed the file back into a database, which must already exist:

mariadb -u appuser -p appdb < appdb-backup.sql

To make this automatic, drop a dump command into a small script and schedule it with cron. This example runs every night at 2 AM and stamps each file with the date:

0 2 * * * /usr/bin/mysqldump -u appuser -pYourPassword appdb | gzip > /home/backups/appdb-$(date +\%Y\%m\%d).sql.gz

Notice there's no space between -p and the password when you bake it into a script. It's a little ugly, but a scheduled job can't stop to ask you for input. A cleaner option is a .my.cnf file in the user's home directory with the credentials and tight permissions, which keeps the password out of the cron line entirely. Either way, copy those backups off the server now and then, because a backup sitting on the same disk as the database won't help you if the disk dies. We've watched plenty of people learn that the hard way.

Troubleshooting

Access denied for user

An error like ERROR 1045 (28000): Access denied for user 'appuser'@'localhost' almost always means the password is wrong or the host part doesn't match. Remember that 'appuser'@'localhost' and 'appuser'@'%' are treated as two separate accounts. Log in as root with sudo mariadb and check what actually exists:

SELECT User, Host FROM mysql.user;

If the host column doesn't include where you're connecting from, create the user for that host, or reset the password with SET PASSWORD FOR 'appuser'@'localhost' = PASSWORD('newpass'); followed by FLUSH PRIVILEGES;.

Cannot connect remotely

If a local connection works but a remote one times out or is refused, walk through it in order. Confirm bind-address is no longer set to 127.0.0.1 and that you restarted MariaDB after editing it. Confirm the user exists for the connecting IP, not just for localhost. Then check the firewall actually allows the port from that source. You can see what MariaDB is listening on with:

sudo ss -tlnp | grep 3306

If that shows 127.0.0.1:3306, the bind-address change didn't take. If it shows 0.0.0.0:3306 but you still can't connect, the problem is the firewall or the user host.

Forgotten root password

If you set a root password and lost it, you can recover without losing data. Stop the service, then start it in a mode that skips permission checks:

sudo systemctl stop mariadb
sudo mysqld_safe --skip-grant-tables --skip-networking &

Connect with a plain mariadb, then reset the password:

FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new-root-password';
FLUSH PRIVILEGES;
EXIT;

Then stop the unsafe instance and start the service normally again:

sudo systemctl start mariadb

The --skip-networking flag matters here. It keeps the temporarily unprotected database off the network while it has no password checks, so nobody can sneak in during the few minutes you're working.

The service won't start

If systemctl status mariadb shows a failed state, the logs tell you why. Read them with:

sudo journalctl -u mariadb --no-pager -n 40

The usual culprits are a typo in a config file you just edited, a full disk (check with df -h), or a leftover process holding the port. Fix the config or free up space, then start the service again.

Wrap up

You now have MariaDB installed, secured, and serving a dedicated database through a user that can only touch what it needs. You also know how to open it to another server when you have to, and how to take backups so a bad day doesn't become a disaster. From here you can plug those credentials into a website, a bot, or any app that speaks SQL. If you're running this on a Bytte.cloud VPS, the same steps apply unchanged, and our Discord is there if you get stuck on a step. Keep your backups current and your database off the public internet, and you'll be in good shape.