Deployment

Personal Catalogue: PHP & MySQL

Chapter 9 · Deployment

Every chapter so far has run against a local install. This chapter moves the finished catalogue onto a real server — reusing the Debian/Apache-or-nginx groundwork this site's own Setting Up a Web Server on Debian and Securing Your Web Server courses already cover, rather than re-deriving general web-server setup here.

Where This Actually Lives

A personal catalogue like this doesn't need its own dedicated domain — the natural real home is a subdirectory or subdomain on a server already running (this project's own eventual home, per Chapter 10, is exactly this: a corner of the user's existing site). For the purposes of this chapter, the deployment target is a plain document root such as /var/www/catalogue/, served by whichever web server is already configured per those two courses.

Config Never Travels With the Code

Chapter 1's config.php has real database credentials sitting directly in it. That was fine for local development, but it must never be the file that gets copied or deployed to a server the same way the rest of the codebase is — especially if this project's own code ever ends up in a Git repository, where a committed credentials file stays in the project's history forever, even if it's deleted later.

.gitignore

config.php

In its place, a config.example.php file is committed instead — the same structure, with placeholder values, serving as a template for whoever sets the project up next (including, on a fresh server, the current user themselves):

config.example.php

<?php
$dbHost = 'localhost';
$dbName = 'catalogue';
$dbUser = 'REPLACE_ME';
$dbPass = 'REPLACE_ME';

$pdo = new PDO("mysql:host={$dbHost};dbname={$dbName};charset=utf8mb4", $dbUser, $dbPass, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false,
]);

The real config.php, holding the actual production credentials, is created directly on the server itself (via cp config.example.php config.php then editing it in place) — it never passes through version control at all.

Migrating the Schema to the Production Database

Rather than re-typing every CREATE TABLE statement from Chapter 2 by hand on the server, the local schema is exported once and imported directly:

# On the local machine, export structure and data together:
mysqldump -u root -p catalogue > catalogue_export.sql

# Copy the file to the server (adjust user/host/path as needed):
scp catalogue_export.sql user@server:/tmp/

# On the server, create an empty database with the right charset first...
mysql -u root -p -e "CREATE DATABASE catalogue CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

# ...then import the exported structure and data into it:
mysql -u root -p catalogue < /tmp/catalogue_export.sql

Creating the database with the correct charset explicitly, before importing, matters for the exact same reason Chapter 1's own Exercise 3 covered — a server's own default charset can't be assumed to already be utf8mb4.

A Dedicated, Least-Privilege Database User

Using MySQL's own root account as the application's own connection user (even locally, in Chapter 1's own example) is a shortcut worth deliberately dropping before this goes anywhere real. A dedicated account, scoped to only this one database, limits the real damage a bug or a compromised credential could do:

CREATE USER 'catalogue_app'@'localhost' IDENTIFIED BY 'a-real-strong-password-here';
GRANT SELECT, INSERT, UPDATE, DELETE ON catalogue.* TO 'catalogue_app'@'localhost';
FLUSH PRIVILEGES;

That user's own real password is what goes into the server's real config.php — never the root password, and never a copy of whatever was used locally.

Deliberately Not Granting DROP or CREATE

GRANT SELECT, INSERT, UPDATE, DELETE gives the application account exactly what the running app actually needs to do its job — every query this course has built since Chapter 3 is one of those four operations. It deliberately omits schema-altering privileges like DROP or ALTER: if a bug, or a successful SQL injection attempt somehow slipping past the prepared-statement discipline built since Chapter 1, ever tried to run something destructive against the schema itself, this account genuinely couldn't do it.

Production Error Display

A local dev setup benefits from seeing PHP errors directly in the browser. On a real, publicly reachable server, that's a real information leak — a stack trace can reveal file paths, and in a worse case, a poorly-written error message could even leak a database query. Production PHP should show nothing to the visitor, while still recording everything to a log file only the server administrator can read:

display_errors = Off
log_errors = On
error_log = /var/log/php/catalogue_errors.log

These are real php.ini directives — set either in the server's own global php.ini, or scoped to just this site via an Apache .htaccess (php_flag display_errors Off) or an nginx-fpm pool's own configuration, depending on which of this site's own web-server courses' setup is actually in use.

HTTPS Is Not Optional Here

This project's own admin pages accept real form submissions and, indirectly, real MySQL credentials flow through the same connection during setup. Serving any of it over plain HTTP would send that traffic unencrypted. This site's own HTTPS/TLS Fundamentals course covers obtaining and configuring a certificate in full — that setup is a real prerequisite for this project going live, not an optional hardening step to add later.

A Simple, Real Backup Habit

A personal catalogue with no other copy of its own data is one dropped table away from losing months of manual entry. A single cron-scheduled command covers the realistic case:

# Add to crontab -e, runs daily at 2am:
0 2 * * * mysqldump -u catalogue_app -p'the-real-password' catalogue > /home/user/backups/catalogue_$(date +\%Y\%m\%d).sql

A Real, Honest Scope Note

A cron-scheduled mysqldump writing to the same server's own disk isn't a complete backup strategy — a real disk failure would take the backups down with the live data. For a small personal project, it's still a genuine, meaningful improvement over no backup at all, and copying the resulting .sql files somewhere off that same machine occasionally (even just downloading them by hand once in a while) closes most of the realistic gap without needing a dedicated backup service.

Hands-On Exercises

Exercise 1

Create config.example.php with placeholder credentials, add config.php to a real .gitignore file, and confirm (with git status, assuming the project is a Git repository) that config.php genuinely does not appear as a trackable file once .gitignore is in place.

📄 View solution

Exercise 2

Create the catalogue_app MySQL user with exactly the privileges shown in this chapter, then confirm — by trying and expecting it to fail — that a query like DROP TABLE items; run as that user is correctly rejected by MySQL with a permissions error.

📄 View solution

Exercise 3

Set display_errors = Off and log_errors = On in a local PHP test setup, deliberately trigger a real PHP error (e.g. calling an undefined function), and confirm the error is genuinely absent from the browser output while still appearing in the configured log file.

📄 View solution

Chapter 9 Quick Reference

config.php — gitignored, created directly on the server; config.example.php (placeholder values) is what actually gets committed
Schema migration — mysqldump locally, then imported into a freshly-created, correctly-charset database on the server
Dedicated MySQL user — SELECT/INSERT/UPDATE/DELETE only, no DROP/ALTER, never the app's own connection running as root
Production error handling — display_errors = Off, log_errors = On, errors visible only in a server-side log file
HTTPS — a real prerequisite, not optional, per this site's own HTTPS/TLS Fundamentals course
Backups — a scheduled mysqldump cron job, honestly scoped as "better than nothing" rather than a complete disaster-recovery plan
Next chapter: Capstone — integrating the catalogue into the existing Astro site