MySQL Database Snapshot to CSV Exporter

snapshot.php

<?php

/**
 * This PHP script creates CSV snapshots of all tables in a specified MySQL database.
 * Useful for database analysis and backup purposes. The script connects to the MySQL
 * database using PDO, retrieves the list of all tables (excluding specified ones),
 * and then exports each table's contents to a CSV file. The CSV files are saved in a
 * directory named 'snapshots' inside the script's directory, with each run generating
 * a new timestamped subdirectory for organization.
 *
 * Key Features:
 * - Excludes specified tables (e.g., log tables) to focus on relevant data.
 * - Generates a separate CSV file for each table with column headers.
 * - Organizes CSV files in timestamped directories for easy tracking.
 * - The $orderBy array specifies the preferred order of columns for sorting table data.
 *   The script will use the first available column from this array for sorting each table's data.
 *   If none of the specified columns are found in a table, no specific ordering is applied.
 * - Ensures compatibility with various data types and special characters in database fields.
 *
 * Usage Notes:
 * - Configure database connection settings ($host, $dbname, $user, $pass) before running.
 * - Modify the $skippedTables array to exclude specific tables from the export.
 * - Update the $orderBy array to define preferred sorting columns.
 * - Ensure PHP PDO extension is installed and enabled for MySQL.
 */

$host = '127.0.0.1';
$dbname = '';
$user = '';
$pass = '';
$orderBy = ['id', 'created_at', 'executed_at'];

$dir = __DIR__ . '/snapshots';
$skippedTables = ['change_log'];

if (!is_dir($dir)) {
    mkdir($dir, 0777, true);
}

$dateDir = $dir . '/' . date('Y-m-d_H-i-s');
if (!is_dir($dateDir)) {
    mkdir($dateDir, 0777, true);
}

try {
    $pdo = new PDO("mysql:dbname=$dbname;host=$host", $user, $pass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $tablesQuery = $pdo->prepare(
        "SELECT table_name FROM information_schema.tables WHERE table_schema = '$dbname'"
    );
    $tablesQuery->execute();
    $tables = $tablesQuery->fetchAll(PDO::FETCH_COLUMN);
    $tables = array_filter($tables, function ($table) use ($skippedTables) {
        return !in_array($table, $skippedTables);
    });

    foreach ($tables as $table) {
        $fieldsQuery = $pdo->prepare("
            SELECT column_name
            FROM information_schema.columns
            WHERE table_schema = '$dbname' AND table_name = :table
            ORDER BY ordinal_position
        ");
        $fieldsQuery->bindParam(':table', $table);
        $fieldsQuery->execute();
        $fields = $fieldsQuery->fetchAll(PDO::FETCH_COLUMN);

        $orderByField = null;
        foreach ($orderBy as $field) {
            if (in_array($field, $fields)) {
                $orderByField = $field;
                break;
            }
        }

        $csvFile = fopen($dateDir . "/$table.csv", 'w');
        if ($csvFile === false) {
            throw new Exception("Cannot create file: $table.csv");
        }

        fputcsv($csvFile, $fields);

        $queryStr = "SELECT * FROM $table";
        if ($orderByField !== null) {
            $queryStr .= " ORDER BY $orderByField";
        }

        $dataQuery = $pdo->prepare($queryStr);
        $dataQuery->execute();
        $row = $dataQuery->fetch(PDO::FETCH_ASSOC);
        while ($row) {
            fputcsv($csvFile, $row);
            $row = $dataQuery->fetch(PDO::FETCH_ASSOC);
        }

        fclose($csvFile);
    }

    echo "CSV export completed.\n";
} catch (PDOException $e) {
    die('Database connection error: ' . $e->getMessage());
} catch (Exception $e) {
    die('Error: ' . $e->getMessage());
}