Chapter 9
Chapter 9 — phpMyAdmin
phpMyAdmin is a browser-based MySQL administration interface that lets you browse tables, run SQL queries, export backups, and manage users without touching the command line. It's widely used and genuinely useful — but its default installation leaves a well-known URL open to the internet with no extra protection. This chapter covers the full installation, the four security hardening steps you must complete before using it, navigating the interface, and the common tasks you'll do through the UI.
What this chapter covers:
apt install phpmyadmin walkthrough with the interactive prompts explained. What the package creates. Why the defaults are insecure and the four steps to fix them: change the URL alias, IP restriction, HTTP Basic Auth as a second credential layer, config.inc.php hardening. Navigating the interface. Creating databases and tables. Running SQL queries. Export and Import. Managing users via the Privileges tab. Cloudflare Access as an alternative auth layer. Keeping phpMyAdmin updated.
Installation
$ sudo apt update && sudo apt install phpmyadmin
The installer is interactive. It asks three questions:
Please choose the web server that should be automatically configured to run phpMyAdmin:
→ Select: apache2 (press Space to tick, then Enter)
This writes
/etc/apache2/conf-enabled/phpmyadmin.conf automatically — you don't need to configure Apache manually. If you miss this step, phpMyAdmin still installs but the Apache config isn't created and the URL won't work.Configure database for phpmyadmin with dbconfig-common?
→ Select: Yes
Creates the
phpmyadmin MySQL database used internally by phpMyAdmin to store bookmarks and settings, and creates a phpmyadmin MySQL user with privileges on that database only.MySQL application password for phpmyadmin:
→ Enter a strong password (or leave blank to auto-generate)
This is the password for the internal
phpmyadmin MySQL user — not your login password. Generate a strong random one: openssl rand -base64 24. You don't need to remember it; it's stored in /etc/phpmyadmin/config-db.php.What the package creates
/usr/share/phpmyadmin/
The application itself — PHP files, CSS, JS. Do not edit files here; they'll be overwritten on upgrade.
/etc/phpmyadmin/
Configuration directory.
config.inc.php is the main config you'll edit. config-db.php has the internal database credentials./etc/apache2/conf-enabled/
phpmyadmin.conf
phpmyadmin.conf
Apache config that defines the
/phpmyadmin URL alias. This is the file you'll edit to change the URL.MySQL: phpmyadmin user
MySQL: phpmyadmin DB
MySQL: phpmyadmin DB
The
phpmyadmin MySQL user has privileges only on the phpmyadmin internal database — not on your application databases.# After installation, the default URL is accessible immediately
# http://yourserver/phpmyadmin ← this needs to change before you use it
# Verify PHP sees all required extensions
$ php -m | grep -E "mbstring|zip|gd|json|curl|mysqli"
curl
gd
json
mbstring
mysqli
# If mbstring is missing (needed by phpMyAdmin):
$ sudo apt install php-mbstring && sudo systemctl restart apache2
Why the Default Installation Needs Hardening
The /phpmyadmin URL is one of the first things bots scan for. Within minutes of a server becoming internet-accessible, automated scanners will probe
/phpmyadmin, /pma, /mysql, and similar paths looking for phpMyAdmin login pages. A fresh install with the default URL is hammered with brute-force login attempts around the clock. The four steps below eliminate most of that risk.
1
Change the URL alias — make the path unpredictable
Edit the Apache config to change
/phpmyadmin to something that isn't in any bot's wordlist.2
IP restriction — allow only your IP or LAN
If you only access the server from home or a known IP, restrict the phpMyAdmin URL to that address. Nobody else can reach the login page at all.
3
HTTP Basic Auth — second credential layer
Add an Apache password prompt in front of phpMyAdmin. Even if someone finds the URL, they need a second set of credentials before they see the MySQL login screen.
4
config.inc.php hardening — disable root login and blank passwords
Configure phpMyAdmin itself to reject attempts to log in as root or with no password, regardless of what MySQL allows.
Step 1 — Change the URL Alias
# The file that controls the URL
$ sudo nano /etc/apache2/conf-enabled/phpmyadmin.conf
# Find the Alias line (near the top)
Alias /phpmyadmin /usr/share/phpmyadmin
# Change it to something unpredictable — use letters+numbers, no dictionary words
Alias /dbadmin-x7k2 /usr/share/phpmyadmin
# Also update the <Directory> block path to match (it's directly below the Alias line)
# Change: <Directory /usr/share/phpmyadmin> → stays the same
# The Directory block doesn't change — only the Alias line
# Reload Apache to apply
$ sudo systemctl reload apache2
# Your phpMyAdmin is now at: http://yourserver/dbadmin-x7k2
# The old /phpmyadmin URL returns 404
Choose a good alias: avoid common abbreviations (pma, dba, myadmin, phpadmin, db). Use something like
/manage-x9q4r or /panel-kzw2 — a short memorable path mixed with random characters. Write it somewhere safe; if you forget it, just re-read the config file.
Step 2 — IP Restriction
If you only need to access phpMyAdmin from home, you can restrict access to your IP address or local network entirely. Anyone from any other IP gets a 403 Forbidden before they even see the login page.
$ sudo nano /etc/apache2/conf-enabled/phpmyadmin.conf
# Find the <Directory /usr/share/phpmyadmin> block
# Add access controls inside it — below the existing Options/AllowOverride lines
<Directory /usr/share/phpmyadmin>
Options SymLinksIfOwnerMatch
DirectoryIndex index.php
AllowOverride None
# ── IP restriction — choose ONE of these approaches ──
# Option A: Allow only your home IP (replace with your actual public IP)
Require ip 203.0.113.42
# Option B: Allow your entire LAN (if accessing from local network only)
Require ip 192.168.1.0/24
# Option C: Allow multiple specific IPs (home + work VPN, etc.)
Require ip 203.0.113.42 198.51.100.15
# Option D: Allow loopback only (access via SSH tunnel — most restrictive)
Require ip 127.0.0.1 ::1
</Directory>
$ sudo systemctl reload apache2
Cloudflare Tunnel users — use Option D (localhost only): if your server's internet access goes through Cloudflare Tunnel, incoming requests arrive from localhost or Cloudflare's own IPs — not your home IP. The cleanest approach is to restrict phpMyAdmin to localhost (
Require ip 127.0.0.1) and access it via an SSH tunnel from your dev machine: ssh -L 8080:127.0.0.1:80 user@server -N, then browse to http://127.0.0.1:8080/dbadmin-x7k2. phpMyAdmin is never exposed to the internet at all. Alternatively, use Cloudflare Access (covered at the end of this chapter).
# Find your current public IP (run this on your home machine, not the server)
$ curl -s https://ifconfig.me
203.0.113.42
# Test the restriction is working (403 = access denied from the wrong IP)
$ curl -I http://yourserver/dbadmin-x7k2
HTTP/1.1 403 Forbidden
Step 3 — HTTP Basic Auth (Second Credential Layer)
HTTP Basic Auth adds an Apache-level password prompt that appears before the phpMyAdmin login form. Even if the URL is found and your IP restriction isn't in place, an attacker needs a second set of credentials.
# Create the htpasswd file with a username (e.g. "dbadmin")
# -c creates the file — only use -c the first time, it overwrites
$ sudo htpasswd -c /etc/phpmyadmin/.htpasswd dbadmin
New password: [enter a strong password — different from your MySQL passwords]
Re-type new password:
Adding password for user dbadmin
# Add a second user later (no -c flag)
$ sudo htpasswd /etc/phpmyadmin/.htpasswd anotheruser
# Restrict the htpasswd file so only root and www-data can read it
$ sudo chown root:www-data /etc/phpmyadmin/.htpasswd
$ sudo chmod 640 /etc/phpmyadmin/.htpasswd
# Add Basic Auth directives to the phpmyadmin.conf Directory block
$ sudo nano /etc/apache2/conf-enabled/phpmyadmin.conf
<Directory /usr/share/phpmyadmin>
Options SymLinksIfOwnerMatch
DirectoryIndex index.php
AllowOverride None
Require ip 127.0.0.1 # keep your IP restriction too
# ── HTTP Basic Auth ──────────────────────────────────────────
AuthType Basic
AuthName "Database Administration"
AuthUserFile /etc/phpmyadmin/.htpasswd
# Require BOTH a valid IP AND a valid htpasswd credential
<RequireAll>
Require ip 127.0.0.1
Require valid-user
</RequireAll>
</Directory>
$ sudo systemctl reload apache2
# Visiting /dbadmin-x7k2 now shows a browser password prompt first,
# then phpMyAdmin's own login page after that prompt is passed
Step 4 — config.inc.php Hardening
$ sudo nano /etc/phpmyadmin/config.inc.php
<?php
/* Authentication type and info */
$cfg['Servers'][$i]['auth_type'] = 'cookie'; // browser cookie — the right choice
/* ── blowfish_secret: MUST be set to a 32-character random string ── */
/* Used to encrypt the cookie phpMyAdmin stores login credentials in. */
/* An empty or short secret makes cookies forgeable. */
$cfg['blowfish_secret'] = 'Rk7vX2mQp9wYnLjHd4sBtZeAcNuF8gK3';
/* Generate your own: php -r "echo bin2hex(random_bytes(16)) . PHP_EOL;" */
/* ── Disable root login ─────────────────────────────────────────── */
$cfg['Servers'][$i]['AllowRoot'] = false;
/* ── Disable no-password logins ─────────────────────────────────── */
$cfg['Servers'][$i]['AllowNoPassword'] = false;
/* ── Session timeout (seconds) — log out idle sessions ─────────── */
$cfg['LoginCookieValidity'] = 1440; // 24 minutes
/* ── Hide phpMyAdmin version in page title (minor obscurity) ─────── */
$cfg['ShowPhpInfo'] = false;
$cfg['ShowChgPassword'] = true;
# Generate a random 32-character blowfish_secret
$ php -r "echo bin2hex(random_bytes(16)) . PHP_EOL;"
Rk7vX2mQp9wYnLjHd4sBtZeAcNuF8gK3
# Test config — phpMyAdmin should show no warnings at the bottom of the page
# Look for a green bar or no "Configuration of phpMyAdmin" warning section
Logging In
After passing HTTP Basic Auth (if configured), you'll see phpMyAdmin's own login form. Log in with a MySQL user account — the same credentials you'd use in a mysql -u username -p command.
Don't log in as root: phpMyAdmin running as root can drop any database, modify any user, or delete system tables with a single mis-click. Log in as an application user (like
bookshop_app) for day-to-day browsing, or create a dedicated admin user with only the privileges you need for admin tasks. If you've followed Step 4, root login is blocked by AllowRoot = false anyway.
# Create a phpMyAdmin admin user in MySQL — with all privileges except GRANT OPTION
mysql> CREATE USER 'pma_admin'@'localhost' IDENTIFIED BY 'StrongAdminPass!';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'pma_admin'@'localhost';
mysql> FLUSH PRIVILEGES;
# This user can do anything except create other users with GRANT OPTION.
# Use this account in phpMyAdmin for admin tasks.
# Use bookshop_app (from Chapter 3) when browsing application data only.
Navigating the Interface
Left panel → Server
The top level when you first log in. Shows all databases your user can see. Click a database name to expand it.
Left panel → Database
Expanding a database shows all its tables. Click a table name to open it and see its data.
Left panel → Table
Click a table to go directly to its Browse view (data rows) in the main panel.
Tab: Structure
Columns and indexes. Shows every column with type, null, default, and index status. Add/edit/drop columns here. Click an index entry to view or delete it.
Tab: SQL
Run arbitrary SQL. A multi-line editor pre-scoped to the current database. Run SELECT, INSERT, UPDATE, CREATE TABLE — anything. Results appear below the editor with column headers you can click to sort.
Tab: Browse
All rows in the table with pagination. Click any row's edit icon (pencil) to update it inline. Tick checkboxes to delete multiple rows.
Tab: Insert
Form to insert a new row — one field per column. Useful for adding test data without writing SQL.
Tab: Search
Per-column filter form — builds a WHERE clause for you. Useful for finding rows without writing SQL.
Tab: Export
Download a SQL dump of the database or selected tables. Equivalent to
mysqldump via the browser.Tab: Import
Upload and run a SQL file. Use for restoring from a mysqldump file or running a large schema migration. Size limit set by PHP's
upload_max_filesize.Top nav: Privileges
Visible at the Server level. Shows all MySQL users, their hosts, and granted privileges. Add/edit/drop users here — the UI equivalent of CREATE USER, GRANT, and REVOKE.
Common Tasks Through the UI
Create a new database
Server level → Databases tab → "Create database"
Enter the database name, choose utf8mb4_unicode_ci as the collation (not utf8 — see Chapter 8), click Create. The database appears in the left panel immediately.
Create a table
Click database in left panel → "Create table" panel on the right
Enter table name and number of columns, click Go. You then fill in each column: name, type (INT, VARCHAR, TEXT, DATETIME…), length, null, default, index. Set your primary key column's Index to PRIMARY. Click Save to generate and run the CREATE TABLE statement — phpMyAdmin shows you the SQL it executed.
Browse and edit table data
Click table name → Browse tab (default view)
Click the pencil icon on any row to edit it inline. Click the red X to delete a row (you'll get a confirmation). Use the checkbox column to tick multiple rows, then use the "With selected" dropdown at the bottom to delete them in bulk.
Run a SQL query
Click database → SQL tab (or click a table → SQL tab)
Type your SQL in the editor. Click Go (or press Ctrl+Enter). Results appear as a sortable table. Click any column header to sort. You can export the result set directly from the results view. The query history at the bottom of the page shows all previous queries in this session — click one to re-run it.
Export (backup) a database
Click database → Export tab → Quick → Go
Quick export produces a standard mysqldump-compatible SQL file. Use Custom export if you need to select specific tables, exclude data (schema only), or add INSERT IGNORE instead of INSERT. The output downloads as a .sql file to your browser's downloads folder.
Import a SQL file
Click database → Import tab → Choose file → Go
Select the .sql or .sql.gz file. phpMyAdmin runs the statements and reports success or the first error. For large files the PHP
upload_max_filesize limit applies — check it in phpinfo() if imports fail silently. For very large databases, use mysql bookshop < dump.sql from the command line instead.Create / manage MySQL users
Server level → User accounts tab
Click "Add user account" to get the full CREATE USER form: username, host, password strength indicator, and a privilege matrix where you check the privileges to grant. Click "Go" to create the user — phpMyAdmin runs CREATE USER and GRANT automatically. To edit an existing user, click their "Edit privileges" link.
The SQL Tab — Tips for Power Use
# ── Bookmarked / favourite queries ────────────────────────────────
# After running any query, tick "Bookmark this SQL query" before clicking Go
# Saved bookmarks appear in the dropdown below the SQL editor
# Useful for monitoring queries from Chapter 7 you run regularly
# ── Multi-statement execution ──────────────────────────────────────
# phpMyAdmin runs multiple SQL statements separated by semicolons
CREATE TABLE IF NOT EXISTS authors (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(200) NOT NULL
);
INSERT INTO authors (name) VALUES ('Frank Herbert'), ('Isaac Asimov');
SELECT * FROM authors;
# All three statements run in sequence — results for SELECT appear below
# ── EXPLAIN from phpMyAdmin ───────────────────────────────────────
# Write any SELECT, then tick "Show query execution plan" before clicking Go
# phpMyAdmin runs EXPLAIN automatically and shows the execution plan inline
Cloudflare Access — A Better Auth Layer for Tunnel Users
If your server is behind a Cloudflare Tunnel, Cloudflare Access provides a more elegant authentication layer than HTTP Basic Auth. Instead of an htpasswd prompt, users see a Cloudflare-branded one-time passcode or Google/GitHub OAuth login before reaching phpMyAdmin.
Cloudflare Access setup (free tier, Zero Trust dashboard): in the Cloudflare dashboard, go to Zero Trust → Access → Applications → Add an application. Choose "Self-hosted". Set the domain to your phpMyAdmin subdomain (e.g.
dbadmin.yoursite.com). Create an Access Policy: allow your email address, or a Google Workspace domain, or one-time PIN. The tunnel proxies the request, Cloudflare validates the identity, and then (and only then) the request reaches Apache. Your Apache IP restriction can then be set to Cloudflare's gateway IP ranges — or kept as localhost if the tunnel connects locally.
The advantage: Cloudflare Access logs every access attempt, supports MFA, and blocks bots entirely before they reach your server. The free tier allows unlimited users for up to 50 applications. If you're already using Cloudflare Tunnel for the rest of your site, this is the lowest-friction way to protect phpMyAdmin.
Keeping phpMyAdmin Updated
phpMyAdmin has had several serious security vulnerabilities over the years, including XSS and SQL injection flaws in the admin interface itself. Keeping it updated is not optional.
# Update phpMyAdmin with the rest of the system
$ sudo apt update && sudo apt upgrade phpmyadmin
# Check installed version
$ dpkg -l phpmyadmin | grep phpmyadmin
ii phpmyadmin 4:5.2.1+dfsg-1 all MySQL web administration tool
# Check the version in the UI: phpMyAdmin → About in the top navigation bar
# The Debian/Ubuntu packaged version often lags behind upstream releases.
# For the latest phpMyAdmin on Ubuntu, use their APT repo or download directly:
# https://www.phpmyadmin.net/downloads/
# (The apt package is fine for most home/self-hosted use cases.)
# Add unattended-upgrades for security patches (if not already done)
$ sudo apt install unattended-upgrades
$ sudo dpkg-reconfigure -plow unattended-upgrades
# Select "Yes" to enable automatic security updates
Troubleshooting
http://yourserver/phpmyadmin returns 404 after install
The Apache config wasn't linked during installation. Fix: 1) Check if the config exists:
ls /etc/apache2/conf-available/phpmyadmin.conf. 2) Enable it manually: sudo a2enconf phpmyadmin && sudo systemctl reload apache2. If the file doesn't exist in conf-available, the installer didn't create it — re-run: sudo dpkg-reconfigure phpmyadmin and select apache2 when prompted.
Warning: "The configuration file now needs a secret passphrase (blowfish_secret)"
The
blowfish_secret in config.inc.php is empty or too short. Generate one: php -r "echo bin2hex(random_bytes(16)) . PHP_EOL;" — copy the 32-character output and paste it into $cfg['blowfish_secret'] = 'your32chars'; in /etc/phpmyadmin/config.inc.php. The warning disappears on the next page load (no Apache restart needed).
Cannot log in — "Access denied for user"
The MySQL user exists but can't connect via socket. Check: 1) The user must be
'username'@'localhost' (not '%') since phpMyAdmin on the same machine connects via the Unix socket. 2) Run: sudo mysql -e "SELECT user, host, plugin FROM mysql.user WHERE user='youruser';" — confirm the host is localhost. 3) If the plugin is auth_socket, phpMyAdmin can't authenticate with a password — change it: ALTER USER 'youruser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'pass';
403 Forbidden after adding IP restriction
Your current IP isn't in the
Require ip list. Your public IP may have changed (common with home broadband). Check your current IP: curl -s https://ifconfig.me (run this on your home machine, not the server). Update the Require ip line in /etc/apache2/conf-enabled/phpmyadmin.conf and reload Apache. Consider using a CIDR range for your ISP's block instead of a single IP.
Import fails for large SQL files — no error, just stops
PHP's upload and execution limits are too low for large files. Check the limits phpMyAdmin shows on its Import page (it lists the current values). Increase them in
/etc/php/8.X/apache2/php.ini: set upload_max_filesize = 128M, post_max_size = 128M, and max_execution_time = 300. Restart Apache. For files over 100 MB, skip phpMyAdmin entirely and use the command line: sudo mysql bookshop < /path/to/dump.sql — no size limits there.
Quick Reference — Chapter 9
| Task | How |
|---|---|
| Install | sudo apt install phpmyadmin — select apache2, yes to dbconfig, set a password |
| Enable Apache config | sudo a2enconf phpmyadmin && sudo systemctl reload apache2 |
| Change URL alias | Edit /etc/apache2/conf-enabled/phpmyadmin.conf — change Alias line, reload Apache |
| IP restriction | Add Require ip <your-ip> inside the Directory block in phpmyadmin.conf |
| HTTP Basic Auth | sudo htpasswd -c /etc/phpmyadmin/.htpasswd user, add Auth* directives to Directory block |
| blowfish_secret | php -r "echo bin2hex(random_bytes(16));" → paste into /etc/phpmyadmin/config.inc.php |
| Disable root login | $cfg['Servers'][$i]['AllowRoot'] = false; in config.inc.php |
| Create database | Server → Databases tab → Create database — choose utf8mb4_unicode_ci |
| Run SQL | Click database → SQL tab → type query → Go (or Ctrl+Enter) |
| Export backup | Database → Export → Quick → Go → downloads as .sql |
| Import SQL file | Database → Import → Choose file → Go (size limited by PHP upload_max_filesize) |
| Manage users | Server level → User accounts tab → Add user account |
| Update phpMyAdmin | sudo apt update && sudo apt upgrade phpmyadmin |
| Cloudflare Tunnel users | Restrict to localhost + use Cloudflare Access, or access via SSH tunnel (ssh -L 8080:127.0.0.1:80 user@server -N) |