MySQL / MariaDB Database Management: Install, Users, Backups

WordPress, e-commerce, enterprise apps — they all sit on top of a database. MySQL and its fork MariaDB are the world’s most-used relational databases. This guide covers install, user/DB management, backups, and common performance issues on a Linux VDS.

MySQL vs MariaDB — which?

• MySQL: owned by Oracle, commercial + community
• MariaDB: open-source fork of MySQL, ~95% compatible, sometimes faster

For most projects, interchangeable — whichever’s installed on your system is fine. WordPress supports both. For a fresh install, we recommend MariaDB.

1. Install

# Ubuntu/Debian
sudo apt update
sudo apt install mariadb-server -y

# AlmaLinux/RHEL
sudo dnf install mariadb-server -y
sudo systemctl enable --now mariadb

Service status:

sudo systemctl status mariadb

2. Security (always the first step)

sudo mysql_secure_installation

Steps:

• Set root password: a strong one
• Remove anonymous users: Yes
• Disallow root login remotely: Yes
• Remove test database: Yes
• Reload privilege tables: Yes

3. Connect

sudo mysql -u root -p
# Enter password

Exit: EXIT; or Ctrl+D

4. Create a database

CREATE DATABASE blog_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SHOW DATABASES;

utf8mb4 supports emoji + non-Latin scripts. Old utf8 is 3-byte and breaks emoji.

5. Create user + grant privileges

-- Create user
CREATE USER 'blog_user'@'localhost' IDENTIFIED BY 'STRONG_PASSWORD';

-- Full privileges on a DB
GRANT ALL PRIVILEGES ON blog_db.* TO 'blog_user'@'localhost';

-- Read-only privileges
GRANT SELECT ON blog_db.* TO 'reader'@'localhost';

-- Access from a specific IP
CREATE USER 'app_user'@'192.168.1.50' IDENTIFIED BY 'PASSWORD';
GRANT ALL ON app_db.* TO 'app_user'@'192.168.1.50';

FLUSH PRIVILEGES;

@'localhost' allows access only from the server, @'%' from anywhere (insecure — only use with specific IP).

6. View / remove privileges

-- Show grants for a user
SHOW GRANTS FOR 'blog_user'@'localhost';

-- Revoke
REVOKE INSERT, UPDATE ON blog_db.* FROM 'blog_user'@'localhost';

-- Drop user
DROP USER 'old_user'@'localhost';

7. Backup (mysqldump)

Single DB

mysqldump -u root -p blog_db > blog_db.sql

Compressed:

mysqldump -u root -p blog_db | gzip > blog_db_$(date +%Y%m%d).sql.gz

All DBs

mysqldump -u root -p --all-databases | gzip > all_dbs.sql.gz

Production: lock-free backup

mysqldump -u root -p --single-transaction --routines --triggers blog_db > blog_db.sql

--single-transaction produces a consistent backup without locks (for InnoDB tables).

Add to cron for automation:

0 3 * * * mysqldump -u root -p'PASSWORD' blog_db | gzip > /backup/blog_$(date +\%Y\%m\%d).sql.gz

Cron post for details, 3-2-1 backup post for strategy.

8. Restore

# Uncompressed
mysql -u root -p blog_db < blog_db.sql

# .gz file
zcat blog_db.sql.gz | mysql -u root -p blog_db

If DB doesn’t exist, CREATE it first.

9. Remote access

By default, MySQL listens only on localhost. For remote:

/etc/mysql/mariadb.conf.d/50-server.cnf:

bind-address = 0.0.0.0

sudo systemctl restart mariadb

Then:

CREATE USER 'remote_user'@'YOUR_IP' IDENTIFIED BY 'STRONG_PASSWORD';
GRANT ALL ON db_name.* TO 'remote_user'@'YOUR_IP';
FLUSH PRIVILEGES;

In UFW, open 3306 only to the specific IP:

sudo ufw allow from YOUR_IP to any port 3306

⚠️ Never bind-address = 0.0.0.0 + GRANT % + UFW open — public MySQL = brute-force target.

10. Performance tuning

/etc/mysql/mariadb.conf.d/50-server.cnf:

[mysqld]
# Buffer pool — 50-70% of RAM
innodb_buffer_pool_size = 2G

# Query cache (MariaDB)
query_cache_size = 64M
query_cache_type = 1

# Connection limit
max_connections = 200

# Slow query log (queries over 3s)
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 3

sudo systemctl restart mariadb

11. Useful commands

-- DB sizes
SELECT table_schema "Database",
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) "Size (MB)"
FROM information_schema.tables GROUP BY table_schema;

-- Active queries
SHOW PROCESSLIST;

-- Slow query log
SELECT * FROM mysql.slow_log ORDER BY query_time DESC LIMIT 10;

-- Repair table
REPAIR TABLE table_name;

-- Optimize (defragment)
OPTIMIZE TABLE table_name;

12. phpMyAdmin (GUI option)

sudo apt install phpmyadmin -y

Bound to Apache config or manual setup at /etc/apache2/conf-available/phpmyadmin.conf. Browser http://vds-ip/phpmyadmin → root login.

⚠️ Don’t expose phpMyAdmin to the public internet in production — restrict by IP via .htaccess or use Cloudflare Access.

Common errors

• “Access denied for user”: wrong password, or user @'host' doesn’t match
• “Too many connections”: bump max_connections, kill idle connections
• “Disk full”: log files grew too large → clean /var/log/mysql/
• “InnoDB: Operating system error number 28”: disk full → Linux Disk Extension
• “Can’t connect to MySQL server”: service down → sudo systemctl status mariadb
• Slow queries: enable slow log, use EXPLAIN SELECT ..., add INDEX

Conclusion

MySQL/MariaDB is core infrastructure — whether for WordPress or your own software. Install in 5 minutes; production-ready with regular backups + security + tuning.

KavesNET VDS with NVMe SSD is ideal for MySQL — disk I/O is the lifeblood of databases.

Related: WordPress Manual Install · Server Backups