Beautiful Utilities for Ugly Hosts

[shawnb61]

This was initially written out of desperation, but to this day it remains my primary tool for restoring large MySQL DBs.

When I first started using an ISP, while learning unix & MySQL, their phpmyadmin would choke on large DB restores.  Their terminal window for loading via command line was tweaky, and I always forgot the syntax...  I could open a ticket and they would help, but it always took them hours to respond, and they always seemed to find new & clever ways to bork it. (Or worse, my production DB...)

I really wanted to do it at will, on my own, and quickly.  (I was mean to my DBs while testing & broke 'em a lot...)

Looking at the .sql file, it is of course just a bunch of .sql commands...  So...  Why not just parse it & execute it?  I gave it a shot, and voilà, it worked. 

Now I just ftp the file wherever I want (hard for a host to bork ftp {he says, knocking on wood...}) and use this to load.  I just update the DB credentials & file location info & run it via the browser.  Runs on unix & windows.

What I like about this is, first of all that it's fast, but more importantly IT SHARES EVERYTHING...  YOU SEE EVERY LITTLE WARNING AND CONCERN MYSQL HAS WITH YOUR BACKUP.... 

It's very eye opening.  Lots of early pre-deprecation notices developers should take note of...  Try using this to restore a 2.0 SMF DB into MySQL 8.4...

If you had to split a backup into smaller portions, just give it the list of filenames in the array provided.

Anyway, here it is, warts & all, actually one of the very first things I ever wrote in php:

Code Select Expand

<?php 
header( 'Content-type: text/html; charset=utf-8' );
// 
// A dumb utility to load your mysql file because you don't have command line access...
//
//     by Shawn Bulen
// 

// Config section
$dbhost = 'localhost';
$dbid = 'dbid';
$dbpw = 'dbpw';
$db = 'dbname';
$folder = 'backupfolder\\';
$dbport = 3306;
$files = array(
    'dbbu-1.sql',
    'dbbu-2.sql',
    'dbbu-3.sql',
    'dbbu-4.sql',
    'dbbu-5.sql',
    );
// End config section

echo('<br>*************************************<br>');
echo('******** MySQL database loader ********<br>');
echo('*************************************<br><br>');

echo(' Host: ' . $dbhost . '<br>');
echo(' Database: ' . $db . '<br><br>');

@ob_flush();
@flush();

// Connect....
$con = mysqli_connect($dbhost, $dbid, $dbpw, $db, $dbport);
if(!$con) {echo 'Cannot connect to the database!!!'; die();};

// Make sure autocommit is off...
mysqli_autocommit($con, false);

// Main file loop...
foreach ($files as $file)
{
    $handle = fopen($folder . $file, 'r');
    if(!$handle) { echo 'Cannot open file: ' . $file . '!!!'; die();};

    // treat each file as a transaction (only works for innodb)
    mysqli_begin_transaction($con);
    
    echo(' Processing file: ' . $file);
    $sql = '';
    $dots = 0;
    while (($buffer = fgets($handle)) !== false)
    {
        // You have content, & it's not a comment line...
        if (!empty(trim($buffer)) && substr($buffer, 0, 2) != '--' && substr($buffer, 0, 8) != 'Warning:')
        {
            $sql .= $buffer;
            // if at the end of a statement, execute...
            if (substr($sql, -2, 2) == ";\n")
            {
                if (!mysqli_query($con, $sql))
                {
                    echo '<br><br>   DB error: ' . mysqli_error($con) . '<br><br>';
                    echo '   Sql: ' . $sql . '<br><br>';
                }
                $warning = mysqli_get_warnings($con);
                if (!empty($warning))
                {
                    echo '<br>WARNING:<br>';
                    echo print_r($warning, true);
                    echo '<br>sql:' . $sql . '<br>';
                }
                echo '.';
                $dots++;
                if ($dots > 150)
                {
                    echo ('<br>');
                    $dots = 0;
                }
                $sql = '';
                @flush();
                @ob_flush();
            }
        }
    }
    if (!feof($handle))
    {
        echo 'Unexpected file read error!!!<br>';
    }
    fclose($handle);
    echo('done.<br>');
    mysqli_commit($con);

    @flush();
    @ob_flush();
};

echo('<br> Database Load Completed!<br><br>');

return;


And just because...  Here is the cover for A Cotton Candy Autopsy, one of my favorite works of fine literature:

You cannot view this attachment.

[shawnb61]

One more for good measure...

I despise working on the command line...  I like being able to do most if not all administration from within the File Manager. 

On Windows, I have a large collection of .bat files to do things like restore an old 1.0.23 DB and run the current upgrader against it.  I just navigate there & click on it.

It didn't really work that way on my unix-based ISP.

But...  I figured out a way to get pretty darned close.  For each .bat I have on Windows, I have an equivalent .sh file on Unix.  But I wanted an easy way to just run them from File Manager, just like I do within Windows.  (I realize this is easy on some hosts, but others require you to run a cron or via command line.)

My crude but effective solution was to create an "autoexec" folder, and to run a cron job that monitors that folder.  Any .sh scripts in there, run 'em and delete them.  If it's empty do nothing. 

So, when I want 5 scripts run, I just copy them from my scripts folder into autoexec.  I know they're done when autoexec is empty.  Most of my mealy repetitive adhoc admin stuff is done by dropping tasks into the autoexec folder.

Yes, long running jobs require special treatment; I may pause the cron temporarily. 

Run this via cron every 10 minutes or so:

Code Select Expand

#!/bin/sh
#
# first define some vars
TARGETDIR=/home4/yadayada/autoexec
#
# head over to target dir to capture all output in one place
cd $TARGETDIR
#
# loop through any shell scripts in the autoexec folder
# for each, execute & delete the file
echo "Script started"
shopt -s nullglob
for FILENAME in *.sh
do
echo "Executing $FILENAME"
$TARGETDIR/$FILENAME
echo "Deleting $FILENAME"
rm $TARGETDIR/$FILENAME
done
echo "Script completed"
#

[Antechinus]

Excellent stuff.

[shawnb61]

Excellent little blurb describing BSFUC (& hints at why it comes to mind whenever I use these utilities 😆):

Beautiful Stories for Ugly Children is an anthology of contradictions. Absurd and touching, poignant and discomforting, each story is about the search for happiness, and the sometimes extreme measures taken to find it. Despite their lyricism, each story mines a very particular vein of black humor.

Source: https://www.amazon.com/Beautiful-Stories-Ugly-Children-Autopsy/dp/B000FWE3II

Don't spend $400.  Amazon is nuts.  I think actual comic stores still have these gems used for $8-$20.  Highly recommend.

[Oldiesmann]

Blame third party sellers who think "rare and/or out of print" means "people will pay whatever ridiculous price we can think of for this". If you want it, mycomicshop.com has it for $8. https://www.mycomicshop.com/search?TID=99701