Split A Large MySQL Table Into Multiple SQL Files

If you have a large database table that is causing your import to fail, you can split the table out into smaller more manageable chunks to import.

Here’s a PHP script to create multiple SQL files:


$servername = ''; // IP or Host name
$username = 'username';
$password = 'password';
$dbname = 'databasename';

$chunkSize = 10000; // Number of rows per SQL file

try {
    // Connect to the database
    $pdo = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);

    // Get the total number of rows in the table
    $stmt = $pdo->query("SELECT COUNT(*) FROM $table");
    $totalRows = $stmt->fetchColumn();

    $numFiles = ceil($totalRows / $chunkSize);

    for ($i = 0; $i < $numFiles; $i++) {
        $offset = $i * $chunkSize;
        $sqlFileName = "export_chunk_{$i}.sql";
        $sqlFile = fopen($sqlFileName, 'w');

        if ($sqlFile) {
            $stmt = $pdo->prepare("SELECT * FROM $table LIMIT :limit OFFSET :offset");
            $stmt->bindValue(':limit', $chunkSize, PDO::PARAM_INT);
            $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);

            // Fetch data and write to the SQL file
            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                $columns = implode(", ", array_keys($row));
                $values = implode(", ", array_map(function($value) {
                    return "'" . addslashes($value) . "'";
                }, array_values($row)));
                $sql = "INSERT INTO $table ($columns) VALUES ($values);\n";
                fwrite($sqlFile, $sql);

            echo "Created $sqlFileName\n";
        } else {
            throw new Exception("Unable to open file $sqlFileName for writing.");

    echo "All files created successfully.\n";

} catch (PDOException $e) {
    echo "Database error: " . $e->getMessage() . "\n";
} catch (Exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
Disclaimer: The code on this website is provided "as is" and comes with no warranty. The author of this website does not accept any responsibility for issues arising from the use of code on this website. Before making any significant changes, ensure you take a backup of all files and do not work directly on a live/production website without thoughly testing your changes first.

Leave a Reply

Your email address will not be published. Required fields are marked *