How to Install MariaDB on Ubuntu Server (Create a Database and User)
WordPress, Nextcloud, Bookstack, Matomo — most self-hosted web apps keep their data in a MySQL-style database. MariaDB is the community version of MySQL, works identically for everything you'll run, and is what Ubuntu ships. Install it once and every app gets its own database and login inside it.
Quick reference
| Task | Command |
|---|---|
| Install | sudo apt install mariadb-server -y |
| Secure it | sudo mysql_secure_installation |
| Open the prompt as root | sudo mysql |
| Make a database | CREATE DATABASE wordpress; |
| Make a user | CREATE USER 'wp'@'localhost' IDENTIFIED BY 'password'; |
| Give access | GRANT ALL ON wordpress.* TO 'wp'@'localhost'; |
| Back up everything | sudo mysqldump --all-databases > all.sql |
1. Install
sudo apt install mariadb-server -y
sudo systemctl enable --now mariadb
2. Secure it
sudo mysql_secure_installation
Press Enter for the current root password (there isn't one), n to switching to unix_socket (it's already on), n to changing the root password (sudo access is what protects it), then Y to everything else: remove anonymous users, disallow remote root, remove the test database, reload.
3. Open the database prompt
sudo mysql
The prompt changes to MariaDB [(none)]>. Every command ends with a semicolon. exit; leaves.
4. Create a database and a user for an app
Each app gets its own database and its own user that can only see that database:
CREATE DATABASE wordpress;
CREATE USER 'wp'@'localhost' IDENTIFIED BY 'a-strong-password';
GRANT ALL PRIVILEGES ON wordpress.* TO 'wp'@'localhost';
FLUSH PRIVILEGES;
exit;
Test the login:
mysql -u wp -p wordpress
Type the password; if you get the prompt, the app will be able to connect with those details.
5. Looking around
SHOW DATABASES;
USE wordpress;
SHOW TABLES;
SELECT user, host FROM mysql.user;
6. Back it up
sudo mysqldump --all-databases | gzip > ~/db-$(date +%F).sql.gz
Or one database: sudo mysqldump wordpress > wordpress.sql. Restore with sudo mysql wordpress < wordpress.sql. Put the dump in your nightly backup script (tutorial 23).
Letting another machine connect
By default MariaDB only listens on the server itself, which is what you want. If an app on another machine (or in Docker) genuinely needs in, edit /etc/mysql/mariadb.conf.d/50-server.cnf, change bind-address to 0.0.0.0, restart, create the user as 'wp'@'192.168.1.%', and allow port 3306 through UFW only from your network.
Troubleshooting
"Access denied for user 'root'@'localhost'." Root uses your sudo, not a password: sudo mysql, not mysql -u root -p.
The app says it can't connect to the database. Check host is localhost (or 127.0.0.1), and the user was created with @'localhost' — a user made as @'%' and one as @'localhost' are different users.
"Can't connect to local server through socket." MariaDB isn't running: sudo systemctl status mariadb and look at the error.
Forgot an app user's password. sudo mysql, then ALTER USER 'wp'@'localhost' IDENTIFIED BY 'new-password';.
Disk full and MariaDB won't start. Free space (tutorial 15), then start it. It's robust about this but check journalctl -u mariadb for anything about corruption.
Wrapping up
Install, run the secure script, sudo mysql, and three lines per app: create database, create user, grant. Dump it nightly. The same four commands serve every database-backed app you'll ever install.
