MariaDB comes up whenever someone asks "is there a MySQL alternative that's fully compatible but not Oracle-owned?" The answer is yes, and it's MariaDB.
Created by the original MySQL developers after the Oracle acquisition, MariaDB maintains drop-in compatibility with MySQL — your connection strings, SQL syntax, and application code work without changes. In practice, for most web application use cases, the two are interchangeable. MariaDB ships some performance improvements and features that MySQL charges for in enterprise editions.
I use it on projects where I want the reliability of MySQL's ecosystem but prefer the open-source development trajectory. This guide covers installation on Ubuntu 22.04, initial security setup, and configuration for web applications.
I run MariaDB on Tencent Cloud Lighthouse with Ubuntu 22.04. It's a drop-in replacement for MySQL in any application stack — LAMP, WordPress, Laravel, and others work without code changes. A practical advantage of Lighthouse for database servers: the snapshot feature lets you capture the entire MariaDB data directory and server configuration together, so migrating to a larger plan or restoring from a problem is a single operation rather than a database export/import workflow.
- Key Takeaways
| Feature | MariaDB | MySQL |
|---|---|---|
| License | GPL (fully open source) | GPL + Commercial Oracle |
| JSON support | LONGTEXT with functions | Native JSON type (MySQL 5.7+) |
| Performance | Aria storage engine, query optimizer improvements | InnoDB focused |
| Galera Cluster | Built-in multi-master replication | Requires separate setup |
| Compatibility | Drop-in replacement for MySQL | — |
| Default Ubuntu package | MariaDB | MySQL |
For most web applications, the choice is largely interchangeable. Use MariaDB if you prefer a fully open-source stack or need Galera cluster replication. Use MySQL if your application specifically requires MySQL 8+ JSON features.
sudo apt update
sudo apt install -y mariadb-server mariadb-client
sudo systemctl start mariadb
sudo systemctl enable mariadb
# Download and run the MariaDB setup script
curl -LsS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash
# Install latest MariaDB
sudo apt update
sudo apt install -y mariadb-server mariadb-client
sudo systemctl start mariadb
sudo systemctl enable mariadb
Verify the installation:
mariadb --version
# mariadb Ver 15.1 Distrib 10.x.x-MariaDB
sudo systemctl status mariadb
sudo mariadb-secure-installation
Work through the prompts:
| Prompt | Recommended answer |
|---|---|
| Switch to unix_socket authentication? | n — keep password auth for flexibility |
| Change root password? | Y — set a strong root password |
| Remove anonymous users? | Y |
| Disallow root login remotely? | Y |
| Remove test database? | Y |
| Reload privilege tables? | Y |
After this, test the root password:
sudo mariadb -u root -p
# Enter the password you just set
# Should connect successfully
# EXIT;
sudo mariadb -u root -p
-- Create application database
CREATE DATABASE myapp_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Create application user
CREATE USER 'myapp_user'@'localhost' IDENTIFIED BY 'use_a_strong_password';
-- Grant permissions
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER
ON myapp_db.*
TO 'myapp_user'@'localhost';
FLUSH PRIVILEGES;
-- Verify
SHOW GRANTS FOR 'myapp_user'@'localhost';
SHOW DATABASES;
EXIT;
Test the application user:
mariadb -u myapp_user -p myapp_db
# Enter password → should connect
# EXIT;
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
Key settings in the [mysqld] section:
[mysqld]
# Network
bind-address = 127.0.0.1 # Local connections only
# Character set
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
# InnoDB settings (adjust based on your RAM)
# For 4 GB server:
innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances = 2
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2 # Better performance (slight risk on power loss)
# Connections
max_connections = 100
wait_timeout = 600
interactive_timeout = 600
# Logging
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_error = /var/log/mysql/error.log
# Binary logging (for replication or point-in-time recovery)
# log_bin = /var/log/mysql/mysql-bin.log
# expire_logs_days = 7
sudo systemctl restart mariadb
MariaDB includes the Aria storage engine, which offers crash-safe MyISAM-compatible tables. For tables that are mostly read:
CREATE TABLE read_heavy_table (
id INT PRIMARY KEY,
data TEXT
) ENGINE=Aria;
CREATE TABLE orders (
id INT PRIMARY KEY,
quantity INT,
price DECIMAL(10,2),
total DECIMAL(10,2) AS (quantity * price) VIRTUAL
);
Store variable attributes in a single column:
-- Store dynamic key-value attributes
CREATE TABLE items (
id INT PRIMARY KEY,
attributes BLOB
);
INSERT INTO items VALUES (1, COLUMN_CREATE('color', 'red', 'size', 'large'));
SELECT COLUMN_GET(attributes, 'color' AS CHAR) FROM items WHERE id = 1;
-- Store JSON
INSERT INTO products VALUES (1, '{"name": "Widget", "tags": ["sale", "new"]}');
-- Query JSON
SELECT JSON_EXTRACT(data, '$.name') FROM products;
SELECT JSON_CONTAINS(data, '"sale"', '$.tags') FROM products;
# Single database
mariadb-dump -u myapp_user -p myapp_db | gzip > myapp_db_$(date +%Y%m%d).sql.gz
# All databases
mariadb-dump -u root -p --all-databases | gzip > all_databases_$(date +%Y%m%d).sql.gz
gunzip -c myapp_db_20260420.sql.gz | mariadb -u myapp_user -p myapp_db
sudo mariadb -u root -p -e "SHOW STATUS LIKE 'Threads_connected';"
sudo mariadb -u root -p -e "SHOW ENGINE INNODB STATUS\G" | head -50
sudo tail -f /var/log/mysql/slow.log
MariaDB is a drop-in replacement for most MySQL workloads. But there are a few things to check when migrating:
Authentication plugins: MySQL 8.0 uses caching_sha2_password by default. MariaDB uses mysql_native_password. If you're migrating a MySQL 8.0 database, some connections may fail due to auth plugin mismatch.
Fix for applications that expect mysql_native_password:
ALTER USER 'myapp_user'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('password');
JSON columns: MySQL 8.0 has a native JSON type with different internal storage than MariaDB's implementation. If you're migrating tables with JSON columns from MySQL 8.0, test queries thoroughly.
System tables: MariaDB and MySQL have diverged on some system/privilege tables. A mysqldump --all-databases from MySQL 8.0 may not restore cleanly to MariaDB — use --databases to dump application databases only.
For fresh installations, MariaDB works perfectly as your primary database with no migration concerns.
# Connect
sudo mariadb # Root via socket
mariadb -u USER -p DATABASE # User with password
# Service
sudo systemctl start|stop|restart|status mariadb
# In MariaDB shell
SHOW DATABASES;
USE DATABASE;
SHOW TABLES;
SHOW CREATE TABLE tablename\G
SHOW PROCESSLIST; # Active connections
SHOW STATUS; # Server statistics
SHOW VARIABLES LIKE 'max_connections';
# User management
SELECT user, host, plugin FROM mysql.user;
SHOW GRANTS FOR 'user'@'localhost';
# Backup
mariadb-dump -u USER -p DB > backup.sql
mariadb-dump -u USER -p DB | gzip > backup.sql.gz
mariadb -u USER -p DB < backup.sql
| Issue | Likely Cause | Fix |
|---|---|---|
| Connection refused | Service not running or wrong port | Check systemctl status SERVICE and verify firewall rules |
| Permission denied | Wrong file ownership or permissions | Check file ownership with ls -la and use chown/chmod to fix |
| 502 Bad Gateway | Backend service not running | Restart the backend service; check logs with journalctl -u SERVICE |
| SSL certificate error | Certificate expired or domain mismatch | Run sudo certbot renew and verify domain DNS points to server IP |
| Service not starting | Config error or missing dependency | Check logs with journalctl -u SERVICE -n 50 for specific error |
| Out of disk space | Logs or data accumulation | Run df -h to identify usage; clean logs or attach CBS storage |
| High memory usage | Too many processes or memory leak | Check with htop; consider upgrading instance plan if consistently high |
| Firewall blocking traffic | Port not open in UFW or Lighthouse console | Open port in Lighthouse console firewall AND sudo ufw allow PORT |
Do I need a separate server for MariaDB?
For most individual projects and small teams, running MariaDB on the same server as your application is practical. The zero-latency between app and database (localhost communication) is a genuine advantage. Separate database servers are warranted at significant scale.
How do I back up a MariaDB database?
Use the database's built-in dump tools for logical backups (recoverable to any host). Combine with Lighthouse snapshots for full-server point-in-time recovery. Set up automated daily cron jobs for consistent backups.
How do I connect to MariaDB remotely for development?
Use SSH tunneling: ssh -L LOCAL_PORT:localhost:DB_PORT user@server. This routes your local database client through SSH without exposing the database port to the internet.
What user permissions should database application users have?
Application users should have only the permissions they need — typically SELECT, INSERT, UPDATE, DELETE on their specific database. Never use the root/admin database account in application connection strings.
htop or your database's status commands and upgrade if you see consistent memory pressure.Set up your database server today:
👉 Tencent Cloud Lighthouse — Ubuntu VPS for MariaDB
👉 View current pricing and promotions
👉 Explore all active deals and offers