Skip to content

Hosting

Databases for Hosting (MySQL / MariaDB)

Most hosted sites — WordPress, Joomla, Magento, Laravel apps, and countless custom CMSes — keep their content, users, and settings in a relational database. On Linux hosting that database is almost always MariaDB or MySQL. This page covers installing and securing the server, carving out a least-privilege database and user for one website, connecting from an app, the role (and risks) of phpMyAdmin, and day-to-day backup and restore with mysqldump.

Tested on

AlmaLinux 9 / RHEL 9 with mariadb-server from AppStream (MariaDB 10.5). Debian/Ubuntu notes are inline. MariaDB is a drop-in fork of MySQL, so almost everything here applies to both; differences are called out where they matter.

MariaDB vs MySQL

Both speak the same SQL dialect and use the same client tools (mysql, mysqldump). MariaDB began as a community fork of MySQL and is the default on RHEL-family distros; Oracle MySQL is also available. For typical website hosting either is fine — pick whichever your application documents support. The commands below use MariaDB but work identically against Oracle MySQL.

Install the server

sudo dnf install -y mariadb-server
sudo systemctl enable --now mariadb
systemctl status mariadb --no-pager

To install Oracle MySQL instead, use sudo dnf install -y mysql-server and enable the mysqld service.

sudo apt update
sudo apt install -y mariadb-server
sudo systemctl enable --now mariadb
systemctl status mariadb --no-pager

The server listens on TCP port 3306 and also accepts connections over a local Unix socket (/var/run/mysqld/mysqld.sock on Debian, /var/lib/mysql/mysql.sock on RHEL).

Keep the database private

For single-server hosting the database should only ever be reached from localhost. Do not expose port 3306 to the internet. If you must allow remote app servers, restrict it with the firewall and bind only to the internal interface — see How web hosting works.

Secure the installation

A fresh install ships with insecure defaults (anonymous users, a test database, remote root login). Fix them with the bundled hardening script:

sudo mysql_secure_installation

Answer the prompts:

Prompt Recommended answer
Switch to unix_socket authentication / set root password Set a strong root password (or keep socket auth on MariaDB)
Remove anonymous users Yes
Disallow root login remotely Yes
Remove test database Yes
Reload privilege tables now Yes

Socket auth for root

On modern MariaDB the root database account often authenticates via the Unix socket, meaning you log in simply with sudo mysql as the OS root user — no password needed locally. That is intentional and secure; keep it and create separate password accounts for applications.

Create a database and a least-privilege user

Never let a website connect as root. Give each site its own database and its own user, and grant that user rights to only that database. Open a client session:

sudo mysql

Then run (replace the names and use a strong, unique password):

-- One database for this website
CREATE DATABASE mysite_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- A dedicated user that may only connect from this server
CREATE USER 'mysite_user'@'localhost' IDENTIFIED BY 'S0me-Strong-Passw0rd!';

-- Grant only what the app needs, scoped to that one database
GRANT ALL PRIVILEGES ON mysite_db.* TO 'mysite_user'@'localhost';

-- Apply the changes
FLUSH PRIVILEGES;

GRANT ALL PRIVILEGES ON mysite_db.* gives full control of that single database (create/alter tables, read, write) but no access to any other database and no server-wide admin rights. The 'mysite_user'@'localhost' host part restricts the account to local connections.

Tighter still

Some applications never alter their schema after install. For those you can drop down to data-only rights and skip CREATE, ALTER, and DROP:

GRANT SELECT, INSERT, UPDATE, DELETE ON mysite_db.* TO 'mysite_user'@'localhost';

Confirm the grant, then exit:

SHOW GRANTS FOR 'mysite_user'@'localhost';
EXIT;

Connect from an application

A PHP app (see PHP and PHP-FPM) needs four things: host, database name, username, password. For WordPress-style config:

DB_HOST     = localhost      # or 127.0.0.1
DB_NAME     = mysite_db
DB_USER     = mysite_user
DB_PASSWORD = S0me-Strong-Passw0rd!

There is a subtle but important difference between localhost and 127.0.0.1:

  • localhost — the client connects over the local Unix socket, which is faster and is the usual default.
  • 127.0.0.1 — forces a TCP connection to the loopback interface. Use this if the app or driver doesn't know the socket path, or to be explicit.

Test the website's credentials from the shell without involving the app:

mysql -u mysite_user -p mysite_db

If you connect and SHOW TABLES; works, the app will too.

phpMyAdmin

phpMyAdmin is a web-based database administration tool written in PHP. It gives you a browser GUI to browse tables, run SQL, import/export data, and manage users — handy for clients who don't use the command line. Most control panels (see Control panels) bundle it.

phpMyAdmin is a frequent attack target

Because it is so common, automated bots constantly scan for exposed phpMyAdmin installs and hammer them with credential-stuffing and exploit attempts. If you run it:

  • Do not leave it at a predictable URL like /phpmyadmin; rename the alias.
  • Restrict access by IP (web-server allow/deny) or put it behind HTTP auth / a VPN.
  • Always serve it over HTTPS — see HTTPS with Let's Encrypt.
  • Keep it patched, and remove it entirely if no one is using it.

For routine work the mysql CLI and mysqldump are safer and scriptable.

Backups with mysqldump

mysqldump produces a plain-text SQL file (the schema plus INSERT statements) that recreates the database when replayed. It is the workhorse of database backups.

Back up a single database:

mysqldump -u root -p --single-transaction --quick mysite_db > /backup/mysite_db.sql

Back up all databases on the server in one file:

mysqldump -u root -p --single-transaction --quick --all-databases > /backup/all-databases.sql

What the flags do:

  • --single-transaction — takes a consistent snapshot without locking tables (for InnoDB, the usual storage engine), so the site stays online during the dump.
  • --quick — streams rows instead of buffering the whole table in memory.

Compress on the fly to save space:

mysqldump -u root -p --single-transaction --quick mysite_db | gzip > /backup/mysite_db-$(date +%F).sql.gz

Automate it

A database dump is only useful if it runs every night. Wrap one of these commands in a script and schedule it with cron or a systemd timer — see Cron and timers and the dedicated Backups and migration page. Store the dumps off the server (the 3-2-1 rule), and keep them somewhere only root can read, since they contain all your data.

Restore from a dump

Restoring is just feeding the SQL file back into the client. The target database must already exist:

# Create the (empty) database first if it isn't there
sudo mysql -e "CREATE DATABASE IF NOT EXISTS mysite_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

# Replay the dump into it
mysql -u root -p mysite_db < /backup/mysite_db.sql

For a gzipped dump, decompress as you pipe it in:

gunzip < /backup/mysite_db-2026-06-09.sql.gz | mysql -u root -p mysite_db

An --all-databases dump already contains the CREATE DATABASE statements, so you restore it without naming a database:

mysql -u root -p < /backup/all-databases.sql

Restoring overwrites

Importing a dump replaces matching tables. Always restore to a scratch database first if you only want to inspect or recover a few rows.

Basic operational issues

A few things you will eventually run into when hosting databases:

  • Where the data lives. The actual files sit in the data directory: /var/lib/mysql on both RHEL and Debian. Each database is a subdirectory. This is on a real disk you must size and monitor — see Storage and filesystems. Don't back up by copying these files while the server is running; use mysqldump or stop the service first.

  • max_connections. The server allows a finite number of simultaneous client connections (default 151). A busy or misbehaving site can exhaust them, after which new visitors get ERROR 1040: Too many connections. Check the current value and busiest counts:

    sudo mysql -e "SHOW VARIABLES LIKE 'max_connections'; SHOW STATUS LIKE 'Max_used_connections';"

    Raise it in the config (/etc/my.cnf.d/ on RHEL, /etc/mysql/mariadb.conf.d/ on Debian) under [mysqld], e.g. max_connections = 300, then restart — but first investigate why connections aren't being released, since that's usually the real bug.

  • The slow query log. When a site is sluggish, the database is a common culprit. Enable the slow query log to record queries that take too long:

    [mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

    After restarting, queries running longer than long_query_time seconds are logged with their execution time, so you can find the offenders and add indexes.

Verify your work

# Service is up and enabled
systemctl is-active mariadb && systemctl is-enabled mariadb

# The website database and user exist with the right scope
sudo mysql -e "SHOW DATABASES LIKE 'mysite_db';"
sudo mysql -e "SHOW GRANTS FOR 'mysite_user'@'localhost';"

# The app user can connect and see its tables (will prompt for password)
mysql -u mysite_user -p -e "SHOW TABLES;" mysite_db

# A dump produces a real, non-empty SQL file
mysqldump -u root -p --single-transaction mysite_db | head -n 20

You should see the service active, the database listed, grants limited to mysite_db.*, a successful login as the app user, and readable SQL at the top of the dump.

Summary

  • MariaDB and MySQL are interchangeable for most hosting; install mariadb-server, then enable --now the service.
  • Always run mysql_secure_installation on a fresh install.
  • Give every website its own database and its own user, granted rights on that database only (GRANT ... ON db.*), then FLUSH PRIVILEGES.
  • Apps connect with host (localhost = socket, 127.0.0.1 = TCP), database, user, and password.
  • phpMyAdmin is convenient but a heavy attack target — lock it down or skip it.
  • Back up with mysqldump (--single-transaction --quick), automate it on a schedule, store it off-box, and test your restores.
  • Watch max_connections, know that data lives in /var/lib/mysql, and use the slow query log to chase performance problems.

Test yourself