Chapter 5: Perform database maintenance

5.1 Database maintenance schedule preparation

Database maintenance is the regular work that keeps a database available, correct, fast and recoverable after it has gone into use. A database does not stay healthy by itself. Data grows, indexes become uneven, logs fill the disk, backups fail without anyone noticing, and software needs patches. A maintenance schedule turns this work into a written timetable, so that each task is done on time by a named person and nothing depends on someone remembering.

Maintenance work falls into three kinds. Preventive maintenance stops problems before they happen, for example by taking backups and checking disk space. Corrective maintenance repairs a fault that has already happened, such as restoring a damaged table. Adaptive maintenance changes the database to suit new conditions, such as a new version of the DBMS or a new business requirement. A schedule is mostly made of preventive tasks, which is why it saves effort in the long run.

Example: a hardware wholesaler keeps its stock and sales in one MySQL database that is used from seven in the morning to seven in the evening. Its administrator cannot run heavy maintenance during those hours, so the schedule places each task at a time when it will disturb nobody, and records what was done.

5.1.1 Maintenance plans

A maintenance plan is the written document that lists the maintenance tasks for a database, how often each is done, who does it, which tool is used and how success is checked. It is the foundation of the schedule: the plan says what must be done, and the schedule says when.

Objectives of a maintenance plan

• Availability: The database is working whenever users need it.
• Integrity: The data is complete and correct, with no damaged tables.
• Performance: Queries finish in an acceptable time as the data grows.
• Recoverability: If the system fails, it can be restored within an agreed time and with an agreed amount of lost data at most.
• Security: Accounts, patches and settings remain in good condition.
• Capacity: Disk, memory and processor are enough for the next period.

What a maintenance plan contains

Tools that run planned tasks

• cron on Linux: Runs a command at fixed times written in a text file.
• Windows Task Scheduler: Runs programs and scripts at set times on Windows servers.
• MySQL Event Scheduler: Runs SQL statements stored in the database on a timetable.
• SQL Server Agent and pgAgent: The job schedulers of SQL Server and PostgreSQL.
• Monitoring tools: Check that the tasks ran and warn when they did not.

5.1.2 Daily tasks

Daily tasks are the short checks and routine jobs that make sure the database is working and protected from one day to the next. They take little time but catch most serious problems early. Many of them are automated, and the administrator's part is to read the result each morning.

• Confirm the backup: Check that last night's backup finished, that the file exists and that its size is close to the previous day's.
• Check that the DBMS is running: Confirm that the service is up and that the application can connect.
• Read the error log: Look for new errors, warnings, crashed tables and failed logins.
• Check free disk space: Make sure the drives holding data, logs and backups are not nearly full.
• Check scheduled jobs: Confirm that the previous night's jobs finished without error.
• Glance at performance: Look at the number of connections and any long-running queries.
• Handle user requests: Deal with password resets and access requests recorded since the previous day.

# Linux cron: run a backup of the hardware database every night at 01:30.
# The backup account's password is kept in /etc/mysql/backup.cnf, not in this line.
30 1 * * * /usr/bin/mysqldump --defaults-extra-file=/etc/mysql/backup.cnf --single-transaction --routines --events hardware > /backups/hardware_$(date +\%F).sql

-- MySQL 8: how long the server has been running, in seconds
SHOW GLOBAL STATUS LIKE 'Uptime';

# Check free disk space and read the last 50 lines of the MySQL error log (Ubuntu)
df -h
sudo tail -n 50 /var/log/mysql/error.log

5.1.3 Weekly tasks

Weekly tasks are the jobs that need more time or more attention than a daily check, and that do not change fast enough to be done every day. They keep the database's statistics and backups trustworthy.

• Full backup: If daily backups are partial, take a full backup of the whole database each week and store a copy away from the server.
• Verify a backup: Restore a recent backup into a separate test database to prove that it can be read.
• Update statistics: Refresh the information the query optimiser uses to choose how to run queries.
• Review slow queries: Read the slow query log and list the queries that took longest.
• Check table growth: Find the tables that have grown most and compare with the previous week.
• Review failed logins: Look at the pattern of failed logins for signs of guessing attempts.
• Check log rotation: Make sure old log files are being closed and removed so that they do not fill the disk.

-- MySQL 8: refresh optimiser statistics for the busiest table
ANALYZE TABLE stock_movement;

-- List the ten largest tables of the hardware database in megabytes
SELECT table_name,
       ROUND((data_length + index_length) / 1024 / 1024, 1) AS size_mb
FROM information_schema.tables
WHERE table_schema = 'hardware'
ORDER BY (data_length + index_length) DESC
LIMIT 10;

# Restore last night's backup into a test database to prove it works
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS hardware_test"
mysql -u root -p hardware_test < /backups/hardware_2026-11-05.sql

5.1.4 Monthly tasks

Monthly tasks deal with slower changes: fragmentation, growth, access and patches. They are larger jobs and are best done in the quietest period of the month.

• Rebuild busy tables: Tables with many updates and deletes become fragmented. Rebuilding them recovers space and can improve speed.
• Check table integrity: Run the DBMS's check command on important tables.
• Test a full restore: Restore a full backup onto a separate machine and time how long it takes.
• Review capacity: Compare disk, memory and processor use with the previous months and estimate when each will run out.
• Apply tested patches: Install security fixes and minor updates, after testing them on a copy of the database.
• Review accounts: Remove accounts of staff who have left and review accounts that are no longer used.
• Review indexes: Look for indexes that are never used and cost time on every write.
• Archive old data: Move records that are no longer needed day to day to an archive, as explained under database performance optimisation.
• Prepare the monthly report: Collect the figures for the maintenance report.

-- MySQL 8: check a table for errors, then rebuild it and refresh its statistics
CHECK TABLE stock_movement;
OPTIMIZE TABLE stock_movement;

-- MySQL 8 sys schema: indexes that have not been used since the server started
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'hardware';

OPTIMIZE TABLE on an InnoDB table rebuilds the table, so it is run in the maintenance window. The list of unused indexes is trustworthy only after the server has been running through a full business cycle.

5.1.5 Quarterly tasks

Quarterly tasks, done every three months, test whether the database could survive a serious failure and whether the plan itself is still suitable.

• Recovery drill: Rebuild the database on a clean machine using only the backups and written instructions. Record how long it takes and how much data would be lost, and compare with the targets agreed with the business.
• Full access review: List every account and its rights, and ask the owners of each system to confirm they are still correct.
• Hardware health check: Check disk health reports, test the UPS battery and look at temperatures and fans.
• Performance baseline review: Compare the main performance figures with the previous quarter and decide whether tuning is needed.
• Review of thresholds and alerts: Confirm that alert limits still suit the size of the data and the number of users.
• Patch and upgrade planning: Review which patches and versions are due and plan their testing.
• Archive and purge: Apply the data retention rules to remove or archive records that have passed their retention period.
• Review the schedule: Adjust the timetable if the business hours or workload have changed.

-- MySQL 8: list the accounts and then the rights of one of them
SELECT user, host FROM mysql.user ORDER BY user;
SHOW GRANTS FOR 'shop_app'@'localhost';

5.1.6 Annual tasks

Annual tasks look at the year ahead: whether the software and hardware will still be supported, whether the policies are right and whether the budget is enough.

• Software support check: Confirm that the DBMS and operating system versions are still supported by their makers, and plan any major upgrade.
• Major upgrade test: Test a new major version of the DBMS on a copy of the database before it is used live.
• Hardware replacement plan: List the servers, disks and power units that are near the end of their useful life and budget for replacing them.
• Licence and support renewals: Renew licences, support contracts and domain or hosting agreements before they expire.
• Security audit and policy review: Review security settings, the password policy and the backup and retention policies against current needs and legal duties.
• Full disaster recovery exercise: Run a complete exercise, including the people who depend on the system, from the loss of the server to its return to service.
• Update documentation: Bring the data dictionary, diagrams and procedures up to date.
• Capacity plan for the next year: Use the year's growth to estimate the storage and processing needed.
• Year-end archiving: Archive the records of the closed financial year as the organisation's rules require.
• Training refresh: Retrain staff who run or use the system, and train their deputies.

5.1.7 Database maintenance schedule preparation

Preparing the schedule means turning the maintenance plan into a calendar that fits the way the business works. A schedule that is too heavy is ignored, and one that clashes with business hours harms users. The administrator builds it from the tasks listed for each period, places them in suitable time windows and records who is responsible.

Steps in preparing a maintenance schedule

1. List the databases, servers and applications that are covered and the people who depend on them.
2. Find the business hours and the quiet periods when users can be affected least, and agree maintenance windows with the business owner.
3. Collect the tasks for each period: daily, weekly, monthly, quarterly and annual.
4. Estimate how long each task takes by running it on a copy of the database.
5. Place each task in a window, put the heavy tasks in the quietest time and avoid running two heavy tasks at once.
6. Allocate an owner and a deputy to every task, and choose the tool or script that carries it out.
7. Decide how success is checked and who is told if the task fails.
8. Take the draft schedule to the business owner for approval and tell the users when maintenance will happen.
9. Put the schedule into the scheduling tool, record the results of every run, and review the schedule at the end of each quarter.

An extract from a schedule for the hardware wholesaler is shown below. The times are examples chosen to fall outside business hours.

5.1.7.1 Key elements in preparation of maintenance schedule

Every maintenance schedule needs the same key elements. If one is missing, tasks are forgotten, clash with business hours or cannot be repeated.

• Scope: Which databases, servers and environments the schedule covers.
• Tasks and frequency: Each task listed with how often it is done.
• Maintenance window: The agreed time for each task, chosen so that users are least affected.
• Responsibility: A named owner and a deputy for each task.
• Tools and scripts: The exact command, script or job that carries out the task.
• Order and dependencies: Tasks that must come before others, for example a backup before a rebuild.
• Success criteria: The check that shows the task worked.
• Risk and rollback: A backup taken before risky work, and the steps to undo it.
• Communication: How users and management are told in advance and afterwards.
• Records: A log of each run, with date, person, result and problems.
• Review date: The date when the schedule itself is reviewed and updated.