Database Archive Management

This project automates the backup and management of database archives for different EverQuest expansions. It ensures that database versions are tracked, and only the latest archives are kept while older ones are deleted. If a new database version is detected, the previous version is protected and not deleted.

Features

• Dumps specific tables from multiple databases.
• Compresses and uploads the database dumps to a web server.
• Keeps track of the database version and marks new versions as protected.
• Automatically cleans up old archives, retaining only the latest 30 days of backups.
• Protects weekly (Sunday) backups and any backups with a version change.

Screenshot - Home Page

Screenshot - Protected Archive

Configuration

Process Script (process.sh)

This script dumps tables from multiple databases, compresses them, and uploads them to the web server.

Configuration Variables

• DB_USER: Database username.
• DB_PASSWORD: Database password.
• DB_HOST: Database host address.
• DB_PORT: Database port (e.g., 4406).
• DB_IDENTIFIERS: Array of database identifiers (e.g., Classic, Kunark).
• BACKUP_DIR: Directory where backups are temporarily stored.
• WEB_SERVER: Web server URL.
• UPLOAD_URL: Full URL for the upload handler on the web server.
• UPLOAD_KEY: Secure key for authenticating the upload request.
• SCHEMA_URL: URL to download the latest database_schema.h file.
• SCHEMA_FILE: Local path to store the downloaded database_schema.h file.

Upload Handler (upload_handler.php)

This PHP script handles the uploaded database archives, marks versions as protected if needed, and cleans up old archives.

Configuration Variables

• uploadDir: Directory where uploaded files are stored.
• maxAgeDays: Maximum number of days to keep unprotected archives.
• uploadKey: Secure key for authenticating the upload request (defined in config.php).

Database Schema

The archive_logs table should have the following structure:

CREATE TABLE `archive_logs` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `archive` varchar(255) NOT NULL,
    `size` bigint(20) NOT NULL,
    `created` datetime NOT NULL,
    `db_identifier` varchar(255) NOT NULL,
    `db_version` varchar(255) NOT NULL,
    `protected` tinyint(1) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
);

Usage

1. Download and Configure Scripts

   • Clone the repository.
   • Update the configuration variables in process.sh.
   • Update the configuration variables in upload_handler.php and config.php.

2. Run the Process Script

   Ensure process.sh is executable and run it manually or set up a cron job to run it automatically.

   chmod +x process.sh
   ./process.sh

3. Set Up the Web Server

   • Place upload_handler.php and config.php on your web server.
   • Ensure the uploadDir directory is writable by the web server.
   • Ensure your web server is configured to handle file uploads.

4. Database Cleanup

The upload_handler.php script will automatically clean up old, unprotected archives older than 30 days. Protected archives (weekly and version changes) will be retained.

Security

• Ensure the UPLOAD_KEY is kept secret and not exposed in public repositories.
• Use HTTPS to encrypt data transferred between the process script and the web server.

Contributing

Feel free to submit issues or pull requests if you have suggestions for improvements or bug fixes.

License

This project is licensed under the MIT License. See the LICENSE file for details.