By the end of this course, you should be able to inspect, back up, optimise, secure, troubleshoot and document a website database safely.

• Explain why regular database maintenance matters.
• Identify databases, tables, rows, columns, indexes and relationships.
• Understand the differences between MySQL and MariaDB.
• Access and navigate phpMyAdmin.
• Review database names, structures, engines and character sets.
• Identify unusually large or rapidly growing tables.
• Create a complete backup before maintenance.
• Export databases in SQL format.
• Create compressed backups.
• Restore a database from SQL.
• Verify that a backup can be imported.
• Compare full, incremental and scheduled backups.
• Create a backup-retention schedule.
• Inspect tables for errors or corruption.
• Use table checking and repair tools where supported.
• Optimise tables and recover unused space.
• Understand table fragmentation.
• Review indexes and query performance.
• Identify missing, duplicate or unnecessary indexes.
• Use SELECT, WHERE, ORDER BY and LIMIT safely.
• Use COUNT() and aggregate functions.
• Identify duplicate, incomplete and invalid records.
• Remove obsolete data cautiously.
• Clean sessions, logs, caches and temporary data.
• Understand DELETE, UPDATE, DROP and TRUNCATE risks.
• Use transactions where supported.
• Understand primary and foreign keys.
• Identify orphaned records.
• Review character sets and collations.
• Identify multilingual encoding problems.
• Monitor storage growth.
• Review slow queries and bottlenecks.
• Use EXPLAIN.
• Avoid unnecessary columns and excessive result sets.
• Review database users and privileges.
• Apply least privilege.
• Remove unused accounts and rotate exposed credentials.
• Protect configuration files.
• Avoid risky work without a tested backup.
• Test significant changes in staging.
• Use website maintenance mode when needed.
• Troubleshoot connection and authentication errors.
• Resolve import size, timeout and compatibility issues.
• Document maintenance and structural changes.
• Create a recurring maintenance checklist.
• Verify website functionality after maintenance.

Why Maintenance Matters

• Protects important information from accidental loss.
• Reduces unnecessary storage usage.
• Helps maintain query performance.
• Detects corruption and inconsistent records.
• Improves security and account hygiene.
• Supports predictable recovery.

phpMyAdmin Areas to Review

• Database list
• Table names
• Structure view
• Browse view
• SQL tab
• Export and Import
• Operations
• User accounts and privileges

Useful Inspection Query

SELECT
  table_name,
  engine,
  table_rows,
  ROUND(data_length / 1024 / 1024, 2) AS data_mb,
  ROUND(index_length / 1024 / 1024, 2) AS index_mb
FROM information_schema.tables
WHERE table_schema = 'your_database'
ORDER BY data_length + index_length DESC;

What to Look For

• Tables much larger than expected
• Fast-growing log or session tables
• Unusual storage-engine differences
• Indexes larger than table data
• Unexpected character sets or collations
• Tables with very high row counts

phpMyAdmin Export Workflow

1. Select the correct database.
2. Open the Export tab.
3. Choose SQL format.
4. Select all required tables.
5. Include structure and data.
6. Choose compression where appropriate.
7. Download the export.
8. Record the date, source and purpose.

Command-Line Example

mysqldump \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  -u backup_user \
  -p website_database \
  > website_database_2026-08-06.sql

Restore Workflow

1. Create an empty test database.
2. Confirm the required character set and collation.
3. Open the Import tab.
4. Select the SQL or compressed backup file.
5. Start the import.
6. Review errors and warnings.
7. Compare table and row counts.
8. Test the restored application where possible.

Command-Line Example

mysql \
  -u restore_user \
  -p restored_database \
  < website_database_2026-08-06.sql

Example Retention Plan

• Daily backups retained for 14 days
• Weekly backups retained for 8 weeks
• Monthly backups retained for 12 months
• Pre-change backups retained until validation completes
• At least one protected off-server copy

Check a Table

CHECK TABLE articles;
CHECK TABLE users;
CHECK TABLE transactions;

Repair Where Supported

REPAIR TABLE legacy_table;

Good Practice

• Back up before repair operations.
• Review server logs.
• Confirm storage health.
• Do not repeatedly repair without identifying the cause.
• Test the application after recovery.

Optimise Example

OPTIMIZE TABLE sessions;
OPTIMIZE TABLE application_logs;

Why Tables Become Fragmented

• Large numbers of deleted records
• Frequent updates to variable-length fields
• Temporary and session data churn
• Log rotation and cleanup
• Major data imports followed by deletion

Basic Inspection Queries

SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC
LIMIT 20;

SELECT COUNT(*) AS total_users
FROM users;

SELECT status, COUNT(*) AS total
FROM orders
GROUP BY status
ORDER BY total DESC;

Find Potential Duplicate Values

SELECT email, COUNT(*) AS total
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Common Cleanup Targets

• Expired sessions
• Temporary records
• Old application logs
• Expired tokens
• Database-backed cache entries
• Stale import or queue records

Preview Before Delete

SELECT id, expires_at
FROM sessions
WHERE expires_at < NOW()
ORDER BY expires_at
LIMIT 100;

Controlled Delete Example

DELETE FROM sessions
WHERE expires_at < NOW()
LIMIT 1000;

Find Orphaned Records

SELECT comments.id, comments.article_id
FROM comments
LEFT JOIN articles
  ON comments.article_id = articles.id
WHERE articles.id IS NULL;

Relationship Checks

• Missing parent records
• Duplicate primary keys
• Unexpected null foreign keys
• Incorrect cascade rules
• Relationships not enforced by constraints

Key Concepts

• Character set defines how text is encoded.
• Collation defines comparison and sorting behaviour.
• Database, table and column settings may differ.
• Application connection settings also affect text.
• Modern multilingual applications commonly use utf8mb4.

Review Table Collation

SELECT
  table_name,
  table_collation
FROM information_schema.tables
WHERE table_schema = 'your_database'
ORDER BY table_name;

Review Indexes

SHOW INDEX FROM articles;
SHOW INDEX FROM users;

Indexes Commonly Support

• Primary-key lookups
• Foreign-key joins
• Frequently filtered columns
• Frequently sorted columns
• Unique constraints

Index Risks

• Duplicate indexes waste storage.
• Too many indexes slow inserts and updates.
• Indexes on low-value columns may not help.
• Incorrect column order may reduce usefulness.

EXPLAIN Example

EXPLAIN
SELECT id, title, created_at
FROM articles
WHERE category_id = 5
ORDER BY created_at DESC
LIMIT 20;

Common Performance Improvements

• Select only required columns.
• Use LIMIT when reviewing a small result set.
• Filter with indexed columns where appropriate.
• Avoid unnecessary wildcard searches.
• Reduce repeated queries inside application loops.
• Review joins and sort operations.

Security Review Checklist

• List database users and allowed hosts.
• Identify unused accounts.
• Review application-user privileges.
• Remove unnecessary administrative rights.
• Rotate exposed or shared passwords.
• Restrict remote access where possible.
• Protect configuration files.
• Avoid storing credentials in public repositories.

Review Grants

SHOW GRANTS FOR 'application_user'@'localhost';

Transaction Example

START TRANSACTION;

UPDATE users
SET status = 'inactive'
WHERE last_login < '2024-01-01';

SELECT ROW_COUNT() AS affected_rows;

ROLLBACK;

Safe Change Workflow

1. Create and test a current backup.
2. Reproduce the database in staging.
3. Test the SQL change.
4. Measure impact and duration.
5. Schedule a maintenance window.
6. Enable maintenance mode if users may be affected.
7. Apply the change.
8. Verify the website.

Structured Troubleshooting

1. Record the exact error message.
2. Confirm the environment and database name.
3. Verify the database service is running.
4. Check network connectivity and port access.
5. Verify credentials and allowed host.
6. Review server and application logs.
7. Test with a minimal connection.
8. Make one controlled change and retest.

Maintenance Record

• Date and time
• Database and server name
• Backup file and location
• Tables reviewed
• SQL statements executed
• Rows affected
• Structural changes
• Issues discovered
• Verification results
• Administrator responsible

Recurring Checklist

• Review backup success.
• Test a restore.
• Record database size.
• Review large and fast-growing tables.
• Inspect errors and slow queries.
• Review users and privileges.
• Clean approved temporary data.
• Verify application functionality.

Post-Maintenance Website Tests

• User sign-in
• Article or product display
• Search
• Forms and transactions
• Administration interface
• Scheduled tasks
• Error logs

Project Requirements

• Identify the database platform and version.
• Record the database name and size.
• Review table names, engines and collations.
• Identify the five largest tables.
• Create a complete SQL backup.
• Create a compressed copy.
• Restore the backup into a test database.
• Verify table and row counts.
• Check table health.
• Review fragmentation and optimisation candidates.
• Inspect indexes on one large table.
• Use SELECT queries to analyse data.
• Identify duplicate or incomplete records.
• Identify one approved cleanup target.
• Preview affected rows.
• Perform cleanup in staging only.
• Review primary and foreign keys.
• Search for orphaned records.
• Review character sets and collations.
• Run EXPLAIN on one query.
• Review database-user privileges.
• Document all actions.
• Verify application functions.
• Create a recurring checklist.

Recommended Project Workflow

• Inspect
• Back Up
• Restore Test
• Check
• Clean
• Optimise
• Secure
• Verify

Final Verification Checklist

• A current backup exists.
• The backup was successfully restored.
• Table health checks completed.
• No unapproved destructive statement was run.
• Cleanup targets were confirmed before deletion.
• Indexes and slow queries were reviewed.
• Character sets and collations were documented.
• Unused or excessive privileges were identified.
• Website functions were tested.
• Maintenance records were completed.
• A recurring schedule was created.